서브 프로그램 - 프로시져(PROCEDURE)

PL/SQL은 재사용을 못한다.

서브 프로그램은 된다.

종류는 프로시저, 함수, 패키지 ,트리거 등이다.

 

A>서브 프로그램은

1. 이름이 지정된 PL/SQL 블록이다.

2. 최초 실행될때 한번만 컴파일 된다.

3. 데이터 베이스에 저장이 된다.

4. 다른 응용프로그램에서 호출이 가능하다.

5. 함수일 경우 리턴값이 존재할 수 있다.

6. 파라미터를 사용가능하다.

 

B>프로시져 

1.생성 문법

CREATE[OR REPLACE] PROCEDURE  프로시져 이름

IS|AS PL/SQL블록

 

2.파라미터

IN, OUT, INOUT 모드로 나뉘어 진다.

선언시 데이터 타입은 명시하되 크기는 명시하지 않는다.

선언부의 파라미터 = 형식 파라미터

실행시 파라미터 = 실행 파라미터

선언시 default 사용해 기본값 부여 가능

 

3.사용 예시

1) 예시1

CREATE OR REPLACE PROCEDURE update_20  --프로시져 이름갖고 선언
IS 
--프로시져의 내용부
BEGIN 
    UPDATE emp 
    SET job = 'clerk'
    WHERE deptno =20;
END;

--프로시져 실행시키기

EXECUTE update_20;

주의! 프로시져 만든 소유자가 아니면 EXECUTE 권한을 가져야 한다.

 

2)예시2 

CREATE OR REPLACE PROCEDURE up_sal (vempno IN emp.empno%TYPE)

--프로시져의 파라미터 정의 , 모드는 기본값인 IN

IS

BEGIN

UPDATE emp SET sal=5000

WHERE empno = vempno;

END;

/

EXEC up_sal(7902); --실행은 EXEC으로도 할수 있다. EXECUTE와 같음

 

3)예시3

CREATE OR REPLACE PROCEDURE info_prof

( v_profno IN professor.profno%TYPE, v_name OUT professor.name%TYPE, v_pay OUT professor.pay%TYPE )

IS

BEGIN

SELECT name,pay INTO v_name,v_pay

FROM professor WHERE profno = v_profno;

END info_prof;  --이런식으로 END를 할 수도 있다.

/

DECLARE

v_name professor.name%TYPE;

v_pay professor.pay%TYPE;

BEGIN

info_prof(1001,v_name,v_pay); -- IN, OUT, OUT

DBMS_OUTPUT.PUT_LINE(v_name ||' '|| v_pay);

END;

 

파라미터의 사용 예1 

info_prof(  --  =>로 지정을 하면, 위치를 맞추지 않아도 된다.
    v_name=>v_name,
    v_profno=>1001,
    v_pay=>v_pay
 );

----------------------------

파라미터의 사용 예2

info_prof(  -- =>로 지정을 하면, 이런것도 가능하다.
    1001,   --첫번째는 위치를 맞추고

    v_pay=>v_pay, --두번째부터는 직접 지정한다.

    v_name=>v_name
 );

-----------------------------

 

4)예시4 - 바인드 변수 사용예

VARIABLE name VARCHAR2(10)

VARIABLE pay NUMBER

EXEC info_prof(1001,:name,:pay);

PRINT name pay

 

4.USER_SOURCE 딕셔너리 활용

SELECT text FROM USER_SOURCE WHERE nam='프로시져 이름';