Alice's Story

1) return cursor가 있는 패키지(package) , procedure(프로시저)를 생성한다

2) 프리시저 실행 시 REFCURSOR 변수(var)를 하나 선언하여 실행하면 결과 값이 조회된다.

1. 커서의 내용을 미리 정의 해 놓고 사용하는 방법.

DECLARE

  CURSOR C_LIST IS

    SELECT MY_ID FROM MY_TABLE WHERE 조건;

BEGIN

  FOR I_ID IN C_LIST LOOP

    DBMS_OUTPUT.put_line(I_ID);

  END LOOP;

END;

비추천 

커서의 내용을 정할 때 select 문제 동적으로 parameter가 넘어가야 할 경우 사용이 불가능 하다. 왜냐하면 BEGIN 전에 정의하기 때문이다.

2. 커서 변수를 미리 만들어 놓고 불러서 사용하는 방법.

DECLARE

I_ID   VARCHAR2(100); -- 변수 정의

  C_LIST SYS_REFCURSOR; -- 커서 정의

BEGIN

  OPEN C_LIST FOR

  SELECT MY_ID   

    FROM MY_TABLE

    WHERE 조건;

  LOOP -- LOOP 돌기.

      FETCH C_LIST

      INTO  I_ID; --  하나씩 변수에 넣기.

      EXIT WHEN C_LIST%NOTFOUND; -- 더이상 없으면 끝내기.

      DBMS_OUTPUT.put_line(I_ID);    --  출력

  END LOOP;

  CLOSE C_LIST;

END;

재사용성이 있어서 나름 괜찮음. 

커서를 정의 한 뒤 그 때 그 때 커서의 내용을 채우는 방법이다.

3. 동적으로 커서를 생성해서 사용하는 방법

DECLARE

BEGIN

  FOR C_LIST IN (SELECT MY_ID FROM MY_TABLE WHERE 조건) 

  LOOP

    DBMS_OUTPUT.put_line(C_LIST.I_ID);

  END LOOP;

END;

강추~!!

커서를 미리 정의 할 필요도 없고, 변수를 미리 만들어 놓을 필요도 없다.

1) WRAP 유틸리티
데이터베이스 조작 언어 중에 하나인 PL-SQL은 SQL언어가 가지지 못한 많은 장점들을 가지고 있습니다.
그럼에도 불구하고 가장 큰 단점은 소스 프로그램의 비효율적 관리 방법과 디스크에 남아 있는 소스 내용의 노출로 인해 보안에 지대한 문제점을 가지고 있다는 점입니다.
이와 같은 문제점을 보완하기 위해 오라클 사에서는 운영체계 상에서 작성된 PL-SQL의 소스 프로그램의 노출 방지를 위해 WRAP 유틸리티를 제공합니다.
먼저, 테스트할 프로시저를 생성하기 위한 스크립트를 작성하십시오.

### html
[C:\] EDIT  wrap_test.sql
 CREATE PROCEDURE wraptest
IS  TYPE emp_tab IS TABLE OF employees%ROWTYPE  INDEX BY PLS_INTEGER;
all_emps emp_tab;
BEGIN
        SELECT * BULK COLLECT INTO all_emps FROM employees;
        FOR i IN 1..10 LOOP
           DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id); END
LOOP;
END;
 /
C:\> WRAP iname=wrap_test.sql  oname=wrap_test.plb

Input file에는 방금 생성한 스토어드 프로시저의 이름을 정의하시고 Output file에는 암호화하고자 하는 파일 이름을 정의해 주시면 됩니다.

다음은 HR 사용자 계정으로 접속하여 암호화된 파일을 이용하여 프로시저를 생성하고 실행하십시오.

### html
C:\> SQLPLUS  HR/HR
SQL> @wrap_test.plb 암호화된 파일을 실행하면 프로시저가 생성됩니다.
프로시저가 생성되었습니다.
SQL> SET SERVEROUTPUT ON
SQL> CALL WRAPTEST();생성된 프로시저를 실행합니다.
Emp Id: 198
……………..
Emp Id: 100 출력된 결과 호출이 완료되었습니다.

2) DBMS_DDL 패키지
앞서 소개 드린 WRAP 유틸리티는 디스크에 저장되어 있는 PL-SQL의 소소 프로그램을 암호화할 수 있다면 DBMS_DDL.CREATE_WRAPPED는 데이터베이스 내에서 생성되는 PL-SQL의 소스 프로그램을 암호화시킬 수 있는 방법입니다.

### html

SQL> CONNECT  SYSTEM/MANAGER
SQL> DECLARE
package_text VARCHAR2(32767);
FUNCTION generate_spec (pkgname VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
        RETURN
'CREATE PACKAGE ' || pkgname || '
AS PROCEDURE raise_salary (emp_id NUMBER, amount
NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END ' || pkgname || ';';
END generate_spec;
BEGIN  package_text := generate_spec('emp_actions');    
SYS.DBMS_DDL.CREATE_WRAPPED(package_text);
END;

？ EMP_ACTIONS 패키지를 생성하면서 소스 프로그램을 DBMS_DDL.CREATE_WRAPPED로 암호화하는 방법입니다.

### html
(SQL) SELECT text FROM USER_SOURCE WHERE name = 'EMP_ACTIONS';
TEXT
---------------------------------------------------------------
PACKAGE emp_actions wrapped
a000000
1f

3) DBMS_CRYPTO 패키지
최근 사회적 이슈가 되고 있는 내용 중에 개인 정보의 유출로 인해 심각한 사회 문제가 되고 있는 것이 현실입니다.
이러한 문제를 해소하기 위해 오라클 사에서는 컬럼 단위와 블록 단위의 암호화 기능을 제공하고 있습니다.
다음 예제는 DBMS_CRYPTO 패키지를 이용하여 컬럼 단위의 암호화를 적용하는 예제입니다.

### html
SQL> CONNECT  /as sysdba           
SQL> DECLARE
       input_string VARCHAR2 (200) := 'Secret Message';
       output_string VARCHAR2 (200);
       encrypted_raw RAW (2000);           -- stores encrypted binary text
       decrypted_raw RAW (2000);           -- stores decrypted binary text
       num_key_bytes NUMBER := 256/8;      -- key length 256 bits (32 bytes)
       key_bytes_raw RAW (32);             -- stores 256-bit encryption key
       encryption_type PLS_INTEGER :=      -- total encryption type
DBMS_CRYPTO.ENCRYPT_AES256+DBMS_CRYPTO.CHAIN_CBC+DBMS_CRYPTO
.PAD_PKCS5;
BEGIN
      DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
      key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
      encrypted_raw := DBMS_CRYPTO.ENCRYPT (
            src => UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8'),
                      typ => encryption_type, key => key_bytes_raw );
      decrypted_raw := DBMS_CRYPTO.DECRYPT (
 src => encrypted_raw, typ => encryption_type,
                       key => key_bytes_raw );
      output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
      DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;
/