프로시저 생성과 실행 및 삭제

프로시저 생성과 실행 및 삭제

프로시저 생성

프로시저를 생성하는 기능을 제공한다. 프로시저 생성시 반드시 아래의 구문으로 끝나야 한다.

END;
/

생성된 프로시저는 sys_procedures_ 메타 테이블을 참조하여 확인할 수 있다.

프로시저 실행

프로시저를 실행하는 기능을 제공한다. 프로시저를 실행함으로써 다양한 쿼리를 한꺼번에 수행할 수 있다. 실행할 프로시저에 파라미터가 있는 경우 반드시 프로시저 실행전에 파라미터 개수만큼 변수가 선언되어 있어야 한다.

예제1

다음은 INSERT 문을 수행하는 프로시저 emp_proc를 생성하는 예를 보여준다. (IN 파라미터 이용)

iSQL> CREATE OR REPLACE PROCEDURE emp_proc(p1 IN INTEGER, p2 IN CHAR(20), p3 IN CHAR(20), p4 IN CHAR(1))
 AS
 BEGIN
 INSERT INTO employees(eno, e_firstname, e_lastname, sex)
 VALUES(p1, p2, p3, p4);
 END;
 /
Create success.
iSQL> SELECT * FROM system_.sys_procedures_ order by created desc limit 1;
USER_ID     PROC_OID
------------------------------------
PROC_NAME                                 OBJECT_TYPE STATUS
----------------------------------------------------------------------
PARA_NUM    RETURN_DATA_TYPE RETURN_LANG_ID RETURN_SIZE
-------------------------------------------------------------
RETURN_PRECISION RETURN_SCALE PARSE_NO    PARSE_LEN   CREATED
-------------------------------------------------------------------------
LAST_DDL_TIME
----------------
2           3208680
EMP_PROC                                  0           0
4
                        2           192         29-FEB-2012
29-FEB-2012
1 row selected.

아래는 emp_proc를 실행하는 예를 보여준다.

iSQL> VAR eno INTEGER
iSQL> VAR first_name CHAR(20)
iSQL> VAR last_name CHAR(20)
iSQL> VAR sex CHAR(1)
iSQL> EXECUTE :eno := 21;
Execute success.
iSQL> EXECUTE :first_name := 'Joel';
Execute success.
iSQL> EXECUTE :last_name := 'Johnson';
Execute success.
iSQL> EXECUTE :sex := 'M';
Execute success.
iSQL> EXECUTE emp_proc(:eno, :first_name, :last_name, :sex);
Execute success.
iSQL> SELECT eno, e_firstname, e_lastname, sex FROM employees WHERE eno = 21;
ENO         E_FIRSTNAME           E_LASTNAME            SEX  
-----------------------------------------------------------------
21          Joel                  Johnson               M
1 row selected.

예제2

다음은 SELECT 문을 수행하는 프로시저 outProc를 생성하는 예를 보여준다.

iSQL> CREATE TABLE outTbl(i1 INTEGER, i2 INTEGER);
Create success.
iSQL> INSERT INTO outTbl VALUES(1,1);
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> SELECT * FROM outTbl;
OUTTBL.I1   OUTTBL.I2   
---------------------------
1           1           
1           1           
1           1           
1           1           
1           1           
5 rows selected.
iSQL> CREATE OR REPLACE PROCEDURE outProc(a1 OUT INTEGER, a2 IN OUT INTEGER)
AS
BEGIN
  SELECT COUNT(*) INTO a1 FROM outTbl WHERE i2 = a2;
END;
/
Create success.

아래는 outProc를 실행하는 예를 보여준다.

iSQL> VAR t3 INTEGER
iSQL> VAR t4 INTEGER
iSQL> EXEC :t4 := 1;
Execute success.
iSQL> EXEC outProc (:t3, :t4);
Execute success.
iSQL> PRINT t3;
NAME                 TYPE                 VALUE
-----------------------------------------------
T3                   INTEGER              5                  

예제3

다음은 프로시저 outProc1을 생성하는 예를 보여준다.

iSQL> CREATE OR REPLACE PROCEDURE outProc1( p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER)
AS
BEGIN
  p2 := p1;
  p3 := p1 + 100;
END;
/
Create success.
iSQL> VAR v1 INTEGER
iSQL> VAR v2 INTEGER
iSQL> VAR v3 INTEGER
iSQL> EXEC :v1 := 3;
Execute success.
iSQL> EXEC outProc1(:v1, :v2, :v3);
Execute success.
iSQL> PRINT VAR;
[ HOST VARIABLE ]
-----------------------------------------------
NAME                 TYPE                 VALUE
-----------------------------------------------
..
V1                   INTEGER              3
V2                   INTEGER              3
V3                   INTEGER              103
..

예제4

다음은 SELECT 문을 수행하는 프로시저 inoutProc를 생성하는 예를 보여준다.

iSQL> CREATE TABLE inoutTbl(i1 INTEGER);
Create success.
iSQL> INSERT INTO inoutTbl VALUES(1);
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> SELECT * FROM inoutTbl;
INOUTTBL.I1
--------------
1           
1           
1           
3 rows selected.
iSQL> CREATE OR REPLACE PROCEDURE inoutProc (a1 IN OUT INTEGER)
AS
BEGIN
  SELECT COUNT(*) INTO a1 FROM inoutTbl WHERE i1 = a1;
END;
/
Create success.
iSQL> VAR t3 INTEGER
iSQL> EXEC :t3 := 1;
Execute success.
iSQL> EXEC inoutProc(:t3);
Execute success.
iSQL> PRINT t3;
NAME                 TYPE                 VALUE
-----------------------------------------------
T3                   INTEGER              3

예제5

다음은 프로시저 inoutProc1을 생성하는 예를 보여준다.

iSQL> CREATE OR REPLACE PROCEDURE inoutProc1( p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER)
AS
BEGIN
  p2 := p1 + p2;
  p3 := p1 + 100;
END;
/
Create success.

아래는 inoutProc1을 실행하는 예를 보여준다.

iSQL> VAR v1 INTEGER
iSQL> VAR v2 INTEGER
iSQL> VAR v3 INTEGER
iSQL> EXEC :v1 := 3;
Execute success.
iSQL> EXEC :v2 := 5;
Execute success.
iSQL> EXEC inoutProc1(:v1, :v2, :v3);
Execute success.
iSQL> PRINT VAR;
[ HOST VARIABLE ]
-----------------------------------------------
NAME                 TYPE                 VALUE
-----------------------------------------------
..
V1                   INTEGER              3
V2                   INTEGER              8
V3                   INTEGER              103
..

프로시저 삭제

프로시저를 삭제하는 기능을 제공한다.

다음은 emp_proc를 삭제하는 예를 보여준다.

iSQL> DROP PROCEDURE emp_proc;
Drop success