[Oracle] 35.저장 프로시저

in DB on Oracle


저장 프로시저



우리가 만든 PL/SQL을 저장해 놓고 필요한 경우 호출하여 사용할 수 있으면 할 때가 있습니다.

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

이렇게 저장 프로시저를 사용하면 복잡한 DML 문들 필요할 때마다 다시 입력할 필요 없이

간단하게 호출만 해서 복잡한 DML 문의 실행 결과를 얻을 수 있습니다.

저장 프로시저를 사용하면 성능도 향상되고, 호환성 문제도 해결됩니다.



저장 프로시저의 구조는 다음과 같습니다. 


CREATE [OR REPLACE ] PROCEDURE 프로시져명
( 매개변수1 [mode] data_taye, 
  매개변수2 [mode] data_taye . . .
)
IS 
	-- 변수 선언부
	local_variable declaration
BEGIN
	-- 실행부
statement1;
statement2;
. . .
EXCEPTION
	-- 예외처리부
END;
/




프로시저 생성과 실행




  • emp 테이블의 모든 데이터 삭제하는 프로시저




CREATE OR REPLACE PROCEDURE del_all
    IS
BEGIN
    DELETE FROM emp;

END;
/



프로시저는 다음과 같이 호출합니다. 


EXECUTE del_all;




  • 해당 사원번호를 가진 사원만 삭제




CREATE OR REPLACE PROCEDURE del_empid(
    p_empid emp.employee_id%type --매개변수
)
IS
BEGIN
    dbms_output.put_line('사원번호'||p_empid||'번 사원을 삭제했습니다');
    DELETE FROM emp
    WHERE
        employee_id = p_empid;
END;
/





EXECUTE del_empid(150)




image




저장프로시저를 조회하려면 다음과 같이합니다. 


-- 저장 프로시저 조회

SELECT
    *
FROM
    user_source;




PL/SQL 문에서 저장 프로시저 호출




  • 사원번호를 받아서 이름, 급여, 직업을 OUT 파라미터에 넘겨주는 프로시저를 PL / SQL에서 호출




CREATE OR REPLACE PROCEDURE sel_empno (
    p_empno   IN NUMBER, --IN 매개변수
    p_ename   OUT VARCHAR2, --OUT 매개변수
    p_sal     OUT NUMBER, --OUT 매개변수
    p_job     OUT VARCHAR2 --OUT 매개변수
)
IS
BEGIN
    SELECT
        first_name|| ' '|| last_name, salary, job_id
    INTO
        p_ename, p_sal, p_job
    FROM
        emp
    WHERE
        employee_id = p_empno;

END;
/





--PL/SQL 문

DECLARE
    var_ename   VARCHAR2(15);
    var_sal     NUMBER;
    var_job     VARCHAR2(9);
BEGIN
    --익명 블록에서 프로시져 실행 EXEC/EXECUTE를 붙이지 않는다.
    sel_empno(101,var_ename,var_sal,var_job);
    dbms_output.put_line('조회결과:'|| var_ename|| '/'|| var_sal|| '/'|| var_job);
END;
/




image




  • 해당 사원번호를 가진 사원의 정보를 OUT 파라미터로 넘겨주는 프로시저를 PL / SQL 문에서 호출




-- 사원의 정보를 조회해오는 프로시져

CREATE OR REPLACE PROCEDURE emp_info(
    pempid IN NUMBER,
    pemp_n OUT VARCHAR2,
    pdept_n OUT VARCHAR2,
    pjob_t OUT VARCHAR2,
    pcity OUT VARCHAR2)
IS
BEGIN
    SELECT first_name||' '||last_name, department_name, j.job_title, city
    INTO pemp_n, pdept_n, pjob_t, pcity
    FROM emp e, departments d, jobs j, locations l
    WHERE e.employee_id=PEMPID AND e.department_id=d.department_id AND d.location_id=l.location_id AND e.job_id=j.job_id;
END;
/





DECLARE
    vemp_n  VARCHAR2(20);
    vdept_n VARCHAR2(20);
    vjob_t  VARCHAR2(20);
    vcity   VARCHAR2(20);
BEGIN
    dbms_output.put_line('속성:'|| '사원명'|| '/'||'부서명'|| '/'||'직함명'||'/'||'지역명');
    dbms_output.put_line('============================================================');
    emp_info(140,vemp_n,vdept_n,vjob_t,vcity);
    dbms_output.put_line('조회결과:'|| vemp_n|| '/'|| vdept_n|| '/'||vjob_t||'/'||vcity);
END;
/




image




바인드 변수



OUT 파라미터가 있는 프로시저를 PL / SQL 환경말고 일반 SQL 환경에서도 변수를 선언하여 사용할 수 있는데요!

프로시저의 OUT 파라미터 값을 SQL 환경에서 받기 위해서는 바인드 변수가 필요합니다!

바인드 변수는 호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 합니다.

바인드 변수는 VARIABLE 키워드를 사용하여 생성합니다!


아까 정의한 프로시저 emp_info를 SQL 환경에서 호출해보겠습니다! 


-- 바인드 변수 생성
VARIABLE pemp_n VARCHAR2(50);
VARIABLE pdept_n VARCHAR2(50);
VARIABLE pjob_t VARCHAR2(50);
VARIABLE pcity VARCHAR2(50);

-- 프로시저 호출
EXEC emp_info(140,:pemp_n,:pdept_n,:pjob_t,:pcity);




image