[Oracle] CH6 : Stored 서브 프로그램의 개요

Stored 서브 프로그램의 장점

- 공유 사용에 의한 생산성 향상

- 성능 향상 : 컴파일된 상태로 데이터베이스에 저장되어 실행할 때마다 컴파일할 필요가 없어 성능 향상

- 메모리 영역 절약 : 프로그램이 메모리에 올라가 상주하여 다른 애플리케이션에서도 공유하여 사용되어 실행할 때마다 새로운 메모리 영역을 획득할 필요가 없어짐

- 네트워크 전송량 감소 : PL/SQL 블록을 전송하는 경우에 비해 데이터 전송량이 줄어듦

---

Stored 서브 프로그램 작성

- Stored 프로시저( Procedure )

- Stored 함수 ( Function )

 

-- DBMS_OUTPUT을 실행하는 PL/SQL 블록을 Stored 프로시저로 작성
CREATE PROCEDURE out_put -- 프로그램 실행이 아닌 생성
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('100');
END;
/

프로시저가 생성되었습니다.

-- 프로그램 이름을 지정한 뒤 실행
-- 다른 사용자도 이 프로시저를 실행하기 위해서는 프로시저를 공유해야 함
BEGIN
    out_put;
END;
/
100

PL/SQL 처리가 정상적으로 완료되었습니다.

---

Stored 서브 프로그램 공유화

Stored 서브 프로그램을 다수의 사용자가 공유하는 경우에는 다른 사용자에게 실행 권한을 부여해야 함

-- 실행 권한(EXECUTE 권한) 부여
GRANT EXECUTE ON <서브 프로그램명> TO <사용자명>;

GRANT EXECUTE ON out_put TO usr1; -- 프로시저 out_out의 EXECUTE 권한을 usr1 사용자에게 부여

권한이 부여되었습니다.

Stored 서브 프로그램의 실행 권한은 UPDATE 권한과 DELETE 권한 등의 오브젝트 권한과는 달리 조작 범위를 제한할 수 있기 때문에 보안 향상 테크닉으로도 사용됨

일반적으로 Stored 서브 프로그램은 소유자의 권한으로 실행되지만 Stored 서브 프로그램에 EXECUTE 권한을 부여 받은 실행자의 권한으로 실행 가능

---

Stored 서브 프로그램 등록 및 조회

Stored 서브 프로그램을 작성하면 Data Dictionary(데이터 딕셔너리, Oracle 관리 테이블)에 프로그램 이름과 작성자, 소스 코드 등 프로그램에 관련한 데이터가 자동으로 등록. 데이터 딕셔너리에 등록된 Stored 서브 프로그램은 데이터 딕셔너리의 USER_SOURCE 뷰에서 정보 확인 가능

SELECT text FROM user_source
WHERE name = 'OUT_PUT';

TEXT
--------------------------------------
PROCEDURE out_put
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('100');
END;

USER_SOURCE 뷰 : 사용자가 소유한 Stored 오브젝트의 소스 코드 표시

컬럼명	개요
NAME	오브젝트명
TYPE	오브젝트 타입(PROCEDURE, FUNCTION 등)
LINE	이 소스의 행 번호
TEXT	저장된 오브젝트의 소스 코드

---

Stored 서브 프로그램의 디버그

Stored 서브 프로그램 생성 시에 오류가 발생했을 경우에 원인을 파악하고 수정(Debug).

- SHOW ERRORS 커맨드에 의한 오류 정보 표시

- USER_ERRORS 뷰의 조회를 통한 오류 정보 표시

- DBMS_OUTPUT을 사용한 디버그

 

<SHOW ERRORS>

커맨드	개요
SHOW ERRORS	마지막으로 생성 또는 변경된 프로그램의 컴파일 오류를 표시
SHOW ERRORS <오브젝트 타입> <이름>	지정된 프로그램의 컴파일 오류를 표시

SHOW ERRORS -- 5행 근처에서 오류 발생
PROCEDURE OUT_PUT에 대한 오류:

LINE/COL ERROR
-------- --------------------------------------
5/1      PLS-00103: 심볼 "END"를 만났습니다 다음 중 하나가 기대될 때::=
         .(%;
         심볼이 ";" 계속하기 위하여 "END"로 치환되었습니다

SHOW ERRORS procedure out_put
오류가 없음.

 

<USER_ERRORS>

USERS_ERRORS 뷰는 컴파일이 정상적으로 이루어지면 오류 정보가 자동 삭제

SELECT line, text FROM users_errors
WHERE name = 'OUT_PUT';

LINE  TEXT
----  -------------------------------------------------------
  5   PLS-0013: 심볼 "END"를 만났습니다 다음 중 하나가 기대될 때::= .(%;
      심볼이 ";" 계속하기 위하여 "END"로 치환되었습니다

USERS_ERRORS 뷰 : 소유 오브젝트에 대한 발생 오류 확인 가능

컬럼명	개요
NAME	오브젝트명
LINE	오류가 발생한 행 번호
POSITION	오류가 발생한 행 위치
TEXT	오류 정보

 

<DBMS_OUTPUT>

PL/SQL은 블록 내의 처리 경과를 확인하는 방법으로 DBMS_OUTPUT 사용

CREATE OR REPLACE PROCEDURE debug_test
IS
    sal_col   emp.sal%TYPE;
    comm_col  emp.comm%TYPE;
    total_sal emp.sal%TYPE;
BEGIN
    SELECT sal, comm INTO sal_col, comm_col FROM emp WHERE empno = 7369;
    DBMS_OUTPUT.PUT_LINE('sal_col: ' || sal_col);
    DBMS_OUTPUT.PUT_LINE('comm_col: ' || comm_col);
    total_sal := sal_col + comm_col;
    DBMS_OUTPUT.PUT_LINE('total_sal: ' || total_sal);
    DBMS_OUTPUT.PUT_LINE(total_sal);
END;
/

프로시저가 생성되었습니다.

EXECUTE debug_test
sal_col: 800
comm_col:
total_sal:    -- 800 + NULL = NULL

PL/SQL 처리가 정상적으로 완료되었습니다.

CREATE OR REPLACE PROCEDURE debug_test
IS
    sal_col   emp.sal%TYPE;
    comm_col  emp.comm%TYPE;
    total_sal emp.sal%TYPE;
BEGIN
    SELECT sal, NVL(comm, 0) INTO sal_col, comm_col FROM emp WHERE empno = 7369;
    total_sal := sal_col + comm_col;
    DBMS_OUTPUT.PUT_LINE(total_sal);
END;
/

프로시저가 생성되었습니다.


EXECUTE debug_test
800

PL/SQL 처리가 정상적으로 완료되었습니다.

---

NULL 값

NULL 값을 포함한 산술식은 반드시 NULL로 계산됨

DECLARE
    var1 NUBMER(1); -- 초기값이 설정되어 있지 않기 때문에 NULL 값
BEGIN
    IF var1 + 10 = 10 THEN -- NULL 값이 포함되어 항상 NULL이 됨
        DBMS_OUTPUT.PUT_LINE(var1);
    END IF;
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

 

 

참조 : 책 '전문가를 위한 오라클 PL/SQL 입문'