21장 저장 프로시저, 저장 함수, 커서, 트리거 개념 및 예제 (SQL 오라클)

✔  저장 프로시저 (STORED PROCEDURE)?

오라클은 사용자가 만든 PL/SQL 문을 데이터베이스에 저장할 수 있도록 저장 프로시저(stored procedure)라는 것을 제공합니다.

 

이렇게 저장 프로시저를 사용하면 복잡한 DML 문들 필요할 때마다 다시 입력할 필요 없이 간단하게 호출만 해서 복잡한 DML 문의 실행 결과를 얻을 수 있고, 

하나의 스크립트마다 해석 할 필요가 없고, 미리 구문을 저장을 했기 때문에 저장 프로시저를 사용하면 성능도 향상되고, 호환성 문제도 해결됩니다.

 

호환성: MySQL, Oracle...등등 신텍스가 서로 다릅니다.

 

-- 형식
CREATE [OR REPLACE ] PROCEDURE procedure_name (
   argument1 [mode] data_taye,
   --변수            변수 타입
   argument2 [mode] data_taye, . . .)
IS
   local_variable declaration
BEGIN
   statement1;
   statement2;
. . .
END;
/
-- 실행
EXECUTE procedure_name (argument1, argument2, ...);

 

위형식으로 PROCEDURE를 미리 만들고

실행할 때에 PROCEDURE_name을 치면 됩니다.

 

 

✔  저장 프로시저 (STORED PROCEDURE) 오라클 예제

EMP01 테이블 생성

 

CREATE TABLE EMP01
AS
SELECT * FROM EMP;
SELECT * FROM EMP01;

 

저장 프로시져 등록 및 실행

--✔  저장 프로시저 (STORED PROCEDURE) 생성하기
CREATE PROCEDURE DEL_ALL
IS
BEGIN
    --실행내용은 테이블01을 지워라!!
    DELETE FROM EMP01;
    
END;
/

--✔  저장 프로시져 실행
EXECUTE DEL_ALL;
SELECT * FROM EMP01;

EXCUTE DEL_ALL;을 통해서 미리 만들어 컴파일해 놓은 메서드를 실행합니다.

 

 

 

✔  저장 프로시저 (STORED PROCEDURE) _ CREATE OR REPLACE

CREATE OR REPLACE PROCEDURE DEL_ALL
IS
BEGIN
DELETE FROM EMP01;
END;
/

DELL_ALL 이라는 프로시저가 이미 있으면 REPLACE 하고 없으면 새로 CREATE 하는 구문입니다.

 

 

 

✔  저장 프로시저 (STORED PROCEDURE) 조회하기

이미 있는 프로시저를 보려면 아래 구문을 검색하시면 됩니다.

 

예제

--✔  저장 프로시저 조회하기

SELECT NAME, TEXT FROM USER_SOURCE;

결과

 

 

✔  저장 프로시저 (STORED PROCEDURE)_매개변수 저장

--매개변수 있는 프로시저 생성하기
CREATE OR REPLACE PROCEDURE
--없으면 생성하고 있으면 바꿔라
DEL_ENAME(VENAME EMP01.ENAME%TYPE)
--프로시저 이름: DEL_ENAME / 매개변수 VENAME변수는 EMP01테이블의 ENAME%TYPE타입이다.
IS
BEGIN
    --실행문 => 삭제 ENP01 테이블에서 이름이 VENAME인것만
    DELETE FROM EMP01 WHERE ENAME=VENAME;
END;
/


--프로시져 실행 = EXECUTE로 실행
--EXECUTE DEL_ENAME('실행할 매개변수 넣기');
EXECUTE DEL_ENAME('SMITH');

 

결과는 SMITH가 삭제됩니다~!!

 

 

 

✔  저장 프로시저 (STORED PROCEDURE)_ IN, OUT, INOUT 매개 변수

 CREATE PROCEDURE로 프로시저를 생성할 때 MODE를 지정하여 매개변수를 선언할 수 있는데 MODE에 IN, OUT, INOUT 세 가지를 기술할 수 있습니다.
 IN 데이터를 전달 받을 때 쓰고 OUT은 수행된 결과를 받아갈 때 사용합니다.
 INOUT은 두 가지 목적에 모두 사용됩니다.

 

 

IN 매개 변수
 앞선 예제 중에서 매개변수로 사원의 이름을 전달받아서 해당 사원을 삭제하는 프로시저인 DEL_ENAME를 작성해 보았다.
 DEL_ENAME 프로시저에서 사용된 매개변수는 프로시저를 호출할 때 기술한 값을 프로시저 내부에서 받아서 사용하고 있다.

CREATE PROCEDURE DEL_ENAME(VENAME IN EMP01.ENAME%TYPE)

 

OUT 매개 변수

DROP PROCEDURE SEL_EMPNO;
CREATE OR REPLACE PROCEDURE SEL_EMPNO
( VEMPNO IN EMP.EMPNO%TYPE,
VENAME OUT EMP.ENAME%TYPE,
VSAL OUT EMP.SAL%TYPE,

 

 

< IN  /  OUT 매개 변수 예제 >

--IN, OUT 매개변수 활용
DROP PROCEDURE SEL_EMPNO;
CREATE OR REPLACE PROCEDURE SEL_EMPNO
    ( VEMPNO IN EMP.EMPNO%TYPE,
    --매개변수 안에 in이있으니, 값을 받아줍니다.
    VENAME OUT EMP.ENAME%TYPE,
    --매개변수 안에 out이있으니, 값을 받아갈때 외부로 줄때 사용
    VSAL OUT EMP.SAL%TYPE,
    --매개변수 안에 out이있으니, 값을 받아갈때 외부로 줄때 사용
    VJOB OUT EMP.JOB%TYPE
    --매개변수 안에 out이있으니, 값을 받아갈때 외부로 줄때 사용
    )
IS
BEGIN
--실행문
    SELECT ENAME, SAL, JOB INTO VENAME, VSAL, VJOB
    --out으로 된것들이 다 변수에 들어갑니다.
    FROM EMP
    WHERE EMPNO=VEMPNO;
    --첫번째 in으로 받은 입력값을 넣는다.
END;
/

--실행을 하기위해 일반 콘솔에 변수를 저장하는 것입니다.
-- 바인드 변수
-- ':'를 덧붙여주는 변수는 미리 선언되어 있어야 한다.
    VARIABLE VAR_ENAME VARCHAR2(15);
    VARIABLE VAR_SAL NUMBER;
    VARIABLE VAR_JOB VARCHAR2(9);
-- OUT 매개변수에서 값을 받아오기 위해서는 프로시저 호출 시 변수 앞에 ':'를 덧붙인다.


EXECUTE SEL_EMPNO(7788, :VAR_ENAME, :VAR_SAL, :VAR_JOB);
--                                콜론, :매개변수   
--out은 외부에 사영한 변수에 VAR_ENAME, VAR_SAL, VAR_JOB에 값을 전달합니다.
PRINT VAR_ENAME;
PRINT VAR_SAL;
PRINT VAR_JOB;
--각각 저장된 값 print
SELECT ENAME, SAL FROM EMP
WHERE EMPNO=7788;

결과

위의 출력문은

PRINT VAR_ENAME;

PRINT VAR_SAL;

PRINT VAR_JOB;

을 프린트 했을때 나온 결과입니다.

 

프로시저를 만들 때

(CREATE OR REPLACE PROCEDURE SEL_EMPNO)

내가 실행을 시키는 매개값으로 EMPNO를 주면

(VEMPNO IN EMP.EMPNO%TYPE)

그에 맞는 EMP.ENAME, EMP.SAL, EMP.JOB을 출력할 수 있도록 

 (VENAME OUT EMP.ENAME%TYPE)
 (VSAL OUT EMP.SAL%TYPE)
 (VJOB OUT EMP.JOB%TYPE)

 

하고,

실행할 때

 

IS
BEGIN
--실행문
    SELECT ENAME, SAL, JOB INTO VENAME, VSAL, VJOB
    --ENAME, SAL, JOB이 다 VENAME, VSAL, VJOB 변수에 들어갑니다.
    FROM EMP
    WHERE EMPNO=VEMPNO;

END;
/

 

 

 

이상으로 저장 프로시저에 대한 설명을 마치겠습니다.

 

감사합니다.