Stored Procedure
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Stored Procedure as PDF for free.
More details
- Words: 240
- Pages: 2
stored procedure 1.stored procedure is the part of backend 2.stored procedure is a collection of Precompiled sql statements. 3.stored procedure may have IN,OUT and INOUT parameters. 4.stored procedure increases the accessing Speed 5.It is providing the security for client functionality 6.we can pass group of manipulations at a time syntax. CREATE OR REPLACE PROCEDURE (VARNAME DIRECTION datatype,VARNAME DIRECTION datatype,...) IS BEGIN ....... ....... ---------->LOGIC .......; END; eg show: CREATE OR REPLACE PROCEDURE IN NUMBER,XDNAME OUT VARCHAR2, XLOC OUT VARCHAR2) IS BEGIN SELECT DNAME,LOC INTO XDNAME,XLOC DEPT WHERE DEPTNO=XDNO; END; /
SHOW(XDNO
FROM
insert: CREATE OR REPLACE PROCEDURE X(XDNO IN NUMBER,XDNAME IN VARCHAR2,XLOC IN IS BEGIN INSERT INTO DEPT VALUES(XDNO,XDNAME,XLOC); END; / update: CREATE OR REPLACE PROCEDURE NUMBER,XDNAME IN VARCHAR2, XLOC IN VARCHAR2) IS BEGIN UPDATE DEPT SET WHERE DEPTNO=XDNO; END; /
UP(XDNO IN
DNAME=XDNAME,LOC=XLOC
DELETE: CREATE OR REPLACE PROCEDURE DEL(XDNO IN NUMBER) IS BEGIN DELETE from DEPT WHERE DEPTNO=XDNO; END;
VARCHAR2)
/ INS,DEL,UPD: CREATE OR REPLACE PROCEDURE NUMBER) IS xdname varchar2(20); xloc varchar2(20); BEGIN SELECT DNAME,LOC INTO XDNAME,XLOC
IUD(XDNO IN
FROM DEPT WHERE DEPTNO=XDNO;
UPDATE EMP SET DEPTNO=NULL WHERE DEPTNO=XDNO; INSERT INTO DEPTDUP
VALUES(XDNO,XDNAME,XLOC);
DELETE DEPT WHERE DEPTNO=XDNO; --COMMIT; END; / RELATION: SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=&DNO AND E.DEPTNO=D.DEPTNO / @D:\oracle\ora90\sqlplus\demo\demobld CREATE TABLE DEPTDUP AS SELECT * FROM DEPT WHERE 1=2; TSP: >set serverout on declare CDEPTNO NUMBER:=&n; CDNAME VARCHAR2(20); CLOC VARCHAR2(20); BEGIN SELECT DNAME,LOC INTO CDNAME,CLOC FROM DEPT WHERE DEPTNO=CDEPTNO; dbms_output.put_line(cdeptno||' '||cdname||' '||cloc); END; /
SHOW(XDNO
FROM
insert: CREATE OR REPLACE PROCEDURE X(XDNO IN NUMBER,XDNAME IN VARCHAR2,XLOC IN IS BEGIN INSERT INTO DEPT VALUES(XDNO,XDNAME,XLOC); END; / update: CREATE OR REPLACE PROCEDURE NUMBER,XDNAME IN VARCHAR2, XLOC IN VARCHAR2) IS BEGIN UPDATE DEPT SET WHERE DEPTNO=XDNO; END; /
UP(XDNO IN
DNAME=XDNAME,LOC=XLOC
DELETE: CREATE OR REPLACE PROCEDURE DEL(XDNO IN NUMBER) IS BEGIN DELETE from DEPT WHERE DEPTNO=XDNO; END;
VARCHAR2)
/ INS,DEL,UPD: CREATE OR REPLACE PROCEDURE NUMBER) IS xdname varchar2(20); xloc varchar2(20); BEGIN SELECT DNAME,LOC INTO XDNAME,XLOC
IUD(XDNO IN
FROM DEPT WHERE DEPTNO=XDNO;
UPDATE EMP SET DEPTNO=NULL WHERE DEPTNO=XDNO; INSERT INTO DEPTDUP
VALUES(XDNO,XDNAME,XLOC);
DELETE DEPT WHERE DEPTNO=XDNO; --COMMIT; END; / RELATION: SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=&DNO AND E.DEPTNO=D.DEPTNO / @D:\oracle\ora90\sqlplus\demo\demobld CREATE TABLE DEPTDUP AS SELECT * FROM DEPT WHERE 1=2; TSP: >set serverout on declare CDEPTNO NUMBER:=&n; CDNAME VARCHAR2(20); CLOC VARCHAR2(20); BEGIN SELECT DNAME,LOC INTO CDNAME,CLOC FROM DEPT WHERE DEPTNO=CDEPTNO; dbms_output.put_line(cdeptno||' '||cdname||' '||cloc); END; /
Related Documents
Stored Procedure
June 2020 14
Stored Procedure
May 2020 15
Stored Procedure
November 2019 32
Stored Procedure
June 2020 15
Stored Procedure
November 2019 24
Stored Procedure
June 2020 16More Documents from ""
Sdk Debugger
June 2020 67
All Abt Emal
June 2020 63
Asp Fundamentals
June 2020 59
Basic Principles Of Fermentation
June 2020 67
