프로그래밍

Oracle PL/SQL - Stored Subprogram

RainIron 2021. 9. 28. 11:05
반응형

1. 종류

종류 설명
독립형 저장
서브프로그램		 함수 결괏값을 반환함
프로시저 결괏값을 반환하지 않음
패키지 타입, 상수, 변수, 예외, 커서 , 서브프로그램 등을 묶어서 모듈화한 저장 서브프로그램
트리거 특정 이벤트 발생 시 자동적으로 호출되는 서브프로그램
객체 타입 관계형 DB에서 객체 지향 프로그래밍을 가능하게 하는 저장 서브프로그램(ADT(Abstract Data Type), 사용자 정의 타입)

 

2. 컴파일

* 오류 조회

CREATE OR REPLACE FUNCTION get_wage(a_empno NUMBER)
    RETURN MUMBER
IS
    v_wage NUMBER;
BEGIN
    SELECT sal + NVL(comm, 0) comm
    INTO v_wage
    FROM emp
    WHERE empno = a_empno;
    
    RETURN v_wage;
END;

오류가 있다고 하고 컴파일이 완료가 된 모습

SHOW ERROR

 

3. 저장 함수

RETURN문을 사용하여 하나의 값을 반환하는 서브프로그램

 

* 기본 형식

CREATE [ OR REPLACE ] FUNCTION 함수명 [( 매개변수 목록 )]
RETURN 반환데이터타입
IS
	선언부
BEGIN
	실행부
    RETURN 반환값
EXCEPTION
	예외처리부
END;

 

(예시)

CREATE OR REPLACE FUNCTION get_wage(a_empno NUMBER)
    RETURN NUMBER
IS
    v_wage NUMBER;
BEGIN
    SELECT sal + NVL(comm, 0) comm
    INTO v_wage
    FROM emp
    WHERE empno = a_empno;
    
    RETURN v_wage;
END;

(예시2)

-- 매개변수가 없는 함수
CREATE OR REPLACE FUNCTION F RETURN VARCHAR2
IS
BEGIN
	NULL;
END;
/

 

* 제약사항

1) SELECT문에서 호출되는 사용자 정의 함수는 매개변수로 IN 모드만 사용할 수 있다.(함수의 매개변수로 사용된 값이 변경되는 것을 허용하지 않는다)

2) 사용자 정의 함수가 SELECT문에서 호출된 경우에는 DML을 사용할 수 없다.

3) 사용자 정의 함수 내에서는 트랜잭션 제어를 할 수 없다.

4) 사용자 정의 함수 내에서는 DDL을 실행할 수 없다.

5) CREATE TABLE문이나 ATLER TABLE문의 CHECK나 DEFAULT절에는 사용자 정의 함수를 사용할 수 없다.

 

4. 저장 프로시저

반환되는 값 없이 특정 처리만을 수행하는 서브프로그램

 

* 프로시저 실행

CALL 프로시저명;

EXECUTE 프로시저명(세미콜론이 필요 없다)
EXEC 프로시저명(세미콜론이 필요 없다)

 

* 기본 형식

CREATE [ OR REPLACE ] PROCEDURE 프로시저명 [( 매개변수 목록 )]
RETURN 반환데이터타입
IS
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END;

5. 패키지

* 패키지 명세(Specification) 기본 구조

CREATE OR REPLACE PACKAGE 패키지명
IS
	공용 타입 선언
    
    공용 상수 선언
    
    공용 변수 선언
    
    공용 커서 선언
    
    공용 예외 선언
    
    공용 서브프로그램 선언
END;
-- BEGIN없이 END만 있는 구조.

(예시)

CREATE OR REPLACE PACKAGE pkg_emp
IS
	-- 공용 타입 선언
    TYPE emp_type IS TABLE OF emp%ROWTYPE;
    
    -- 공용 상수 선언
    c_deptno_accounting CONSTANT NUMBER := 10;
    c_deptno_research   CONSTANT NUMBER := 20;
    c_deptno_sales      CONSTANT NUMBER := 30;
    c_deptno_operations CONSTANT NUMBER := 40;
    
    -- 공용 변수 선언
    v_last_wage NUMBER;
    
    -- 공용 서브프로그램 선언
    FUNCTION get_wage(a_empno NUMBER) RETURN NUMBER;
    PROCEDURE raise_bonus(a_empno NUMBER, a_amt NUMBER);
END;

 

* 패키지 본체(BODY) 구조

CREATE OR REPLACE PACKAGE BODY 패키지명
IS
	전용 타입 선언
    
    전용 상수 선언
    
    전용 변수 선언
    
    전용 커서 선언 및 정의
    
    전용 예외 선언
    
    전용 서브프로그램 선언
    
    전용 커서 정의
    
    -- 서브프로그램 정의는 패키지 초기화부 바로 위에 작성
    전용 서브프로그램 정의
    
    공용 서브프로그램 정의
BEGIN
	패키지초기화부 -- 패키지가 초기화될 때 단 한 번만 실행, 일회성 프로시저, 일종의 생성자
END;

(예시)

CREATE OR REPLACE PACKAGE BODY pkg_emp
IS
    -- 전용 상수 선언
    c_null_commission CONSTANT NUMBER := 0;
    c_failed_return_wage CONSTANT NUMBER := -1;
    
    -- 전용 커서 선언
    CURSOR emp_cursor(a_empno NUMBER) IS
        SELECT sal+NVL(comm, c_null_commission) comm
        FROM emp
        WHERE empno = a_empno;
    
    -- 공용 서브프로그램 정의
    FUNCTION get_wage(a_empno NUMBER) RETURN NUMBER
    IS
    BEGIN
        FOR rec IN emp_cursor(a_empno)
        LOOP
            v_last_wage := rec.comm;
            return rec.comm;
        END LOOP;
        RETURN c_failed_return_wage;
    END;
    
    PROCEDURE raise_bonus (a_empno NUMBER, a_amt NUMBER)
    IS
        v_ename emp.ename%TYPE;
    BEGIN
        BEGIN
            SELECT ename
            INTO v_ename
            FROM emp
            WHERE empno = a_empno;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RETURN;
        END;
        
        IF a_amt IS NOT NULL
        THEN
            MERGE INTO bonus
            USING DUAL
                ON (bonus.ename = v_ename)
                WHEN MATCHED THEN
                    UPDATE SET comm = comm + a_amt
                WHEN NOT MATCHED THEN
                    INSERT (ename, comm) VALUES(v_ename, a_amt);
        END IF;
    END;
BEGIN
    v_last_wage := -1;
END;

 

* 사용 시 주의사항

1) 생명 주기

2) 세션 의존성

3) 재초기화

4) 병렬처리

 

* 선언 및 정의 후 사용

DECLARE
    v_var NUMBER;
BEGIN
    v_var := pkg_emp.get_wage(7788);
    DBMS_OUTPUT.PUT_LINE('LAST WAGE = ' || pkg_emp.v_last_wage);
END;

* 패키지 커서

-- 명세
CREATE OR REPLACE PACKAGE pkg_emp
IS
    CURSOR c_emp_cur RETURN emp%ROWTYPE;
END;

-- 본체
CREATE OR REPLACE PACKAGE BODY pkg_emp
IS
    CURSOR c_emp_cur RETURN emp%ROWTYPE IS
    SELECT *
    FROM emp
    ORDER BY ename;
END;

-- 사용
BEGIN
    FOR cur IN pkg_emp.c_emp_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE(cur.ename);
    END LOOP;
END;

6. 자치 트랜잭션(Autonomous Transaction)

- 서브프로그램을 호출하는 상위 프로그램의 트랜잭션에서 서브프로그램의 트랜잭션을 분리시키는 기능

- 메인 트랜잭션에 영향을 주지 않고 독립적으로 커밋, 롤백, 세이브포인트 정의가 가능

- 대표적 용도: 로깅

  배치 프로그램에서 작업 중간 중간에 작업 상태를 로그 테이블에 기록하는 경우, 롤백하는 경우 로그까지도 롤백되어 버려서 로그 테이블에 기록한 작업 상태를 잃어버리게 된다. 이 문제를 해결하는 방안이 자치 트랜잭션이다.

 

프로그램 선언부에 아래를 선언

PRAGMA AUTONOMOUS_TRANSACTION;

 

7. 함수 속성

1) DETERMINISTIC: 해당 함수의 입력 값에 따라서 출력 값이 유일하게 결정된다는 것을 컴파일러에게 알려 주는 것

ex) 삼각 함수, TRIM 함수, SUBSTR 함수

 

2) PARALLEL_ENABLE: 해당 함수가 병렬 처리에 사용될 수 있음을 컴파일러에게 알려주는 것

 

3) RESULT_CACHE: 함수의 실행 결과를 데이터베이스 서버에 캐싱하고 다음 번 실행 때 재사용할 것을 지시한다.

사용하려면 다음과 같은 조건을 만족해야 한다.

- 함수의 입력이 동일하면 출력도 항상 동일하다.

- OUT이나 IN OUT 모드의 매개변수를 가지지 않는다.

- 버전 11.2 이하에서는 호출자 권한으로 정의되지 않아야 한다.

- 파이프라인된 함수가 아니다.

- LOB, REF CURSOR, Object, 레코드 타입을 입력 매개변수가 반환값으로 가지지 않는다.

 

(예시)

CREATE OR REPLACE FUNCTION factorial(a_num PLS_INTEGER)
    RETURN NUMBER
        DETERMINISTIC PARALLEL_ENABLE RESULT_CACHE
IS
BEGIN
    IF a_num <= 1 THEN
        RETURN 1;
    ELSE
        RETURN a_num * factorial(a_num-1);
    END IF;
END;

※ 출처: 개발자를 위한 PL/SQL 프로그래밍

반응형

'프로그래밍' 카테고리의 다른 글

SQL 튜닝 - 실행계획, 옵티마이저, 튜닝 절차  (0) 2021.09.29
Oracle PL/SQL - Trigger  (0) 2021.09.28
Oracle PL/SQL - 동적 SQL  (0) 2021.09.24
Oracle PL/SQL - Cursor  (0) 2021.09.24
Oracle PL/SQL - Record  (0) 2021.09.23

'프로그래밍'의 다른글

  • 현재글Oracle PL/SQL - Stored Subprogram

관련글

티스토리툴바