[Oracle/PLSQL]7.Oracle지원 패키지

● DBMS_PIPE 패키지

 

내부 세션 통신을 위한 알고리즘을 제공한다.(보안은 되지 않는다.)

 

- 패키지에 포함된 일부 프로시저/함수

  → PACK_MESSAGE

  → SEND_MESSAGE

  → RECEIVE_MESSAGE

  → UNPACK_MESSAGE

 

함수 또는 프로시저	설명
PACK_MESSAGE  (프로시저)	SEND_MESSAGE 함수로 보낼 항목(VARCHAR2, NUMBER 또는  DATE 유형 항목)을 로컬 메시지 버퍼에 압축합니다.
SEND_MESSAGE  (함수)	로컬 메시지 버퍼에 들어 있는 메시지를 명명된 파이프로  보냅니다.
RECEIVE_MESSAGE  (함수)	명명된 파이프에서 메시지를 검색하여 로컬 메시지 버퍼에 넣고  UNPACK 프로시저로 압축을 풉니다.
UNPACK_MESSAGE	로컬 메시지 버퍼에서 항목 (VARCHAR2, NUMBER 또는 DATE  유형 항목)의 압축을 풉니다.

 

Ex. DBMS_PIPE 사용 예제.

 

* DBMS_PIPE 사용이 되지 않는다면, SYS계정으로 접속하여 사용할 스키마에 권한을 주어야한다.

 

  GRANT EXECUTE ON DBMS_PIPE TO 스키마명

 

- Client

 

CREATE OR REPLACE PROCEDURE send_message
    (v_message IN VARCHAR2)
IS s INTEGER;
BEGIN
    DBMS_PIPE.PACK_MESSAGE(v_message);
    s := DBMS_PIPE.SEND_MESSAGE ('DEMO_PIPE');
    IF s<> 0
    THEN RAISE_APPLICATION_ERROR (-20200,
                                  'Error ' || TO_CHAR(s) || ' sending on pipe.');
    END IF;
END send_message;

 

- Server

 

CREATE OR REPLACE PROCEDURE receive_message
IS s INTEGER;
   v_message VARCHAR2(50);
BEGIN
   s := DBMS_PIPE.RECEIVE_MESSAGE('DEMO_PIPE');
   IF s<> 0
   THEN RAISE_APPLICATION_ERROR (-20201,
                                 'Error ' || TO_CHAR(s) || ' reading pipe.');
   END IF;
   DBMS_PIPE.UNPACK_MESSAGE(v_message);
   DBMS_OUTPUT.PUT_LINE(v_message);
END receive_message;

 

● DBMS_SQL 패키지

 

동적 SQL을 사용하는 PL/SQL 블록을 작성할 수 있다.

(서브프로그램 호출 시 입력하는 매개변수에 따라 다른 SQL문 구현 가능.)

 

 

함수 또는 프로시저	설명
OPEN_CURSOR	새 커서를 열고 커서 ID 번호를 할당합니다.
PARSE	DDL 또는 DML 문의 구문을 분석합니다. 명령문 구문을 검사하여 열린 커서에 연결합니다. (DDL 문은 구문이  분석된 후 바로 실행됩니다.)
BIND_VARIABLE	지정된 커서의 구문 분석 명령문에서 이름으로 식별되는 변수에 지정된 값을 바인드 합니다.
EXECUTE	SQL 문을 실행하고 처리된 행 수를 반환합니다.
FETCH_ROWS	지정된 커서의 행을 검색합니다. (여러 행의 경우 루프에서 호출합니다.)
CLOSE_CURSOR	지정된 커서를 닫습니다.

 

Ex.

 

IN 매개변수를 사용하여 프로시저에 테이블 이름이 전달되고, 동적 SQL의 성공적인 실행 결과로서 삭제된 행 수가 OUT

매개변수를 사용하여 호출 환경에 전달됩니다.

 

CREATE OR REPLACE PROCEDURE delete_all_rows
    (tab_name IN VARCHAR2,
     rows_del OUT NUMBER)
IS
    cursor_name INTEGER;
BEGIN
    cursor_name := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE (cursor_name, 'DELETE FROM ' || tab_name,
                    DBMS_SQL.NATIVE);
    rows_del := DBMS_SQL.EXECUTE (cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;   

 

실행하기.

 

VARIABLE deleted NUMBER
EXECUTE delete_all_rows('emp', :deleted)

 

☞ emp 테이블의 모든 행이 삭제된 것을 확인할 수 있다.

 

- 동적 DML 처리 방법.

 

1. OPEN_CURSOR를 사용하여 메모리에 SQL문을 처리할 영역을 설정합니다.

2. PARSE를 사용하여 SQL문의 유효성을 검사합니다.

3. EXECUTE 함수를 사용하여 SQL문을 실행합니다. 이 함수는 처리된 행 수를 반환합니다.

4. CLOSE_CURSOR를 사용하여 커서를 닫습니다.
                

● EXECUTE IMMEDIATE

 

DBMS_SQL처럼 동적 SQL을 처리하는 구문이다.

 

Ex.

CREATE PROCEDURE del_rows
    (v_table_name IN  VARCHAR2,
     rows_deld    OUT NUMBER)
IS
BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM ' || v_table_name;
    rows_deld := SQL%ROWCOUNT;
END;

 

실행구문.

 

VARIABLE deleted NUMBER
EXECUTE del_rows('emp', :deleted)

 

● DBMS_DDL 패키지

 

...