농부지기

[ Oracle - Procedure DBMS_JOB]

[ Oracle - Procedure 예제_BULK COLLECT INTO ]

DECLARE CURSOR all_depts IS SELECT deptno, dname FROM dept ORDER BY dname; TYPE dept_id IS TABLE OF dept.deptno%TYPE; TYPE dept_name IS TABLE OF dept.dname%TYPE; dept_ids dept_id; dept_names dept_name; inx1 PLS_INTEGER; v_InsertStmt VARCHAR2(2000); rows_processed number:=0; net_processed number:=0; last_value number:=0; BEGIN OPEN all_depts; FETCH all_depts BULK COLLECT INTO dept_ids, dept_names; -- 한꺼번에 로딩 CLOSE all_depts; last_value := SQL%ROWCOUNT; -- 받아온 데이터 ROW 수 /** Load한 데이터의 변형 **/ while (rows_processed < last_value ) loop begin FOR inx1 IN 1..dept_ids.count LOOP dept_names(inx1) := UPPER(dept_names(inx1) || '+'); DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) || ' ' || dept_names(inx1)); END LOOP; FORALL x IN dept_ids.first..dept_ids.last insert into dept(deptno,dname) values (dept_ids(i),dept_names(i)); rows_processed:=rows_processed+sql%ROWCOUNT; net_processed:=net_processed+sql%rowcount; exception when others then begin rows_processed:=rows_processed+sql%ROWCOUNT+1; net_processed:=net_processed+sql%rowcount; commit; end; end; end loop; END;

[ Oracle - Procedure 예제_외부파일_읽고_쓰기 ]

외부파일(txt)를 읽고  쓰기

◈ 테이블의 데이터를 txt파일로 생성하는 예제입니다.(UTL_FILE package)

 Oracle 7.3 부터 UTL_FILE package를 이용해서 OS파일에 대한 read/write를 수행할 수 있습니다.

◈ UTL_FILE package의 설명

     Function/Procedure

     Description

      FOPEN                Input이나 Output을 위해 file을 연다.

                                file이 존재하지 않을 경우 file을 생성한다.

      IS_OPEN             file handler를 이용해 file이 open되었는지 여부를 return한다.

      FCLOSE               file을 닫는다.

      FCLOSE_ALL       열려 있는 모든 file을 닫는다.

      GET_LINE           open된 file로부터 한 line을 읽는다.

      PUT                    open된 file에 한 line을 write한다.

                               ( Line terminator를 붙이지 않는다.)

      PUT_LINE            open된 file에 한 line을 write한다. ( Line terminator를 붙인다.)

      PUTF                   string을 formatting에 의해 write한다. (printf처럼)

      NEW_LINE            open된 file에 line terminator을 write한다.

      FFLUSH                open된 모든 file의 내용을 file에 physical하게 write한다.

◈ Oracle 7.3 부터 오라클 9i R1까지는  %ORACLE_HOME%\admin\<SID>\init<SID>.ora 파일에

    txt파일이 생성될 디렉토리를 지정해야 합니다.

   1) %ORACLE_HOME%\admin\<SID>\init<SID>.ora파일을 열어서 아래의

      파라미터를 추가합니다.

      UTL_FILE_DIR=C:\temp

   2) db shutdown 후 restartup함.

   3) 만약 UTL_FILE package를 인식하지 못하는 경우에 아래의 script를 실행시키면 됩니다.

        -- sys유저로 접속

           SQLPLUS sys/manager

       SQL> @%ORACLE_HOME%\rdbms\admin\utlfile.sql

       SQL> @%ORACLE_HOME%\rdbms\admin\prvtfile.plb

◈ Oracle 9i R2 에서는 CREATE DIRECTORY명령으로 DIRECTORY를 생성하면 됩니다.

  1) SYS 나 SYSTEM USER 로 접속 합니다.

     C:\>SQLPLUS /NOLOG

     SQL> conn sys/manager

  2) DIRECTORY를 생성 합니다.

     SQL> CREATE DIRECTORY LOG_DIR AS 'C:\temp';

     DIRECTORY CREATED.

  3) 생성된 DIRECTORY에 대한 read권한을 부여합니다. .

     SQL> GRANT READ ON DIRECTORY log_dir TO PUBLIC;

◈ 테이블 데이터를 파일로 생성하는 예제(Oracle 9i R2에서 했습니다.)

CREATE OR REPLACE PROCEDURE PL_WriteFile(fname varchar2) IS

    v_output    UTL_FILE.FILE_TYPE;

    v_result     VARCHAR2(4000);

    CURSOR   sql_cur IS

    SELECT    empno, ename, deptno

    FROM       emp;

    BEGIN

     v_output := UTL_FILE.FOPEN('LOG_DIR', fname, 'A');

        FOR v_cur IN sql_cur LOOP

            v_result := v_cur.empno||'  '||v_cur.ename||'  '||v_cur.deptno;

            UTL_FILE.PUT_LINE(v_output, v_result);

        END LOOP;

        UTL_FILE.FCLOSE(v_output);

     EXCEPTION

        WHEN UTL_FILE.INVALID_PATH THEN

            DBMS_OUTPUT.PUT_LINE('INVALID PATH');

        WHEN UTL_FILE.INVALID_MODE THEN

            DBMS_OUTPUT.PUT_LINE('INVALID MODE');

        WHEN UTL_FILE.INVALID_OPERATION THEN

            DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');

    END;

/

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

SQL> exec PL_WriteFile('output.txt');

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

 C:\temp 디렉토리에 파일이 생성되었는지 확인해 보세요..

◈ 위에서 생성한 파일을 읽어와서 출력하는 예제(Oracle 9i R2에서 했습니다.)

   CREATE OR REPLACE PROCEDURE PL_ReadFile(fname varchar2)  IS

       v_input UTL_FILE.FILE_TYPE;              

       retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file

  BEGIN

      -- 파일을 Read

      v_input :=  UTL_FILE.FOPEN('LOG_DIR',fname,'R');

      LOOP

         UTL_FILE.GET_LINE (v_input, retrieved_buffer);  

         DBMS_OUTPUT.PUT_LINE(retrieved_buffer);

     END LOOP;

     -- CLose the file.

     UTL_FILE.FCLOSE(v_input);

     EXCEPTION

        WHEN NO_DATA_FOUND THEN

             DBMS_OUTPUT.PUT_LINE('no_data_found');

             UTL_FILE.FCLOSE(v_input);

        WHEN UTL_FILE.INVALID_PATH THEN

             DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');

             UTL_FILE.FCLOSE(v_input);

        WHEN UTL_FILE.READ_ERROR THEN

             DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');

             UTL_FILE.FCLOSE(v_input);           

        WHEN OTHERS THEN

             DBMS_OUTPUT.PUT_LINE('other stuff');

             UTL_FILE.FCLOSE(v_input);

        END;

/

 SQL> SET SERVEROUTPUT ON;

 SQL> EXEC PL_ReadFile('output.txt');

7369  SMITH  20

7499  ALLEN  30

7521  WARD  30

7566  JONES  20

7654  MARTIN  30

7698  BLAKE  30

....

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

◈ 참고사항

 - v9.2 이전에는 utl_file function 에 대한 directory 를 access 하기 위해서는

   UTL_FILE_DIR parameter 를 init<sid>.ora file 에 기술하여야만 했습니다.

   그러나, UTL_FILE_DIR 을 access 하는 것에 대해 더이상 recommand 하지 않으며,

   UTL_FILE_DIR 대신에 CREATE DIRECTORY 기능을 recommand 합니다.

   DIRECTORY object는 application 관리자에서 보다 유연하고 융통성 있는

   제어를 제공하며, db의 shutdown 없는 dynamic 한 유지관리을 할 수 있게 합니다.

   CREATE DIRECTORY 에 대한 권한은 default 로 SYS 와 SYSTEM user 에 대해서만

   부여되어 있습니다.

 - UTL_FILE.FOPEN('LOG_DIR',fname,'R')에서

  'R'은 read를,'W'는 write를, 'A'는 append를 의미합니다.

[ Oracle - Procedure 예제1 ]

Stored Procedure Sample CREATE OR REPLACE PROCEDURE DP_BUYER_DTCONV IS V_YY TMP_AC.MKR_3%TYPE; V_MM TMP_AC.MKR_3%TYPE; V_DD TMP_AC.MKR_3%TYPE; V_3 TMP_AC.MKR_3%TYPE; V_4 TMP_AC.MKR_4%TYPE; V_9 TMP_AC.MKR_9%TYPE; V_10 TMP_AC.MKR_10%TYPE; V_11 TMP_AC.MKR_11%TYPE; V_CD TMP_AC.MKR_1%TYPE; CURSOR CUR1 IS SELECT MKR_1, MKR_3, MKR_4, MKR_9, MKR_10, MKR_11 FROM tmp_ac; BEGIN OPEN CUR1; LOOP FETCH CUR1 INTO V_CD, V_3, V_4, V_9, V_10, V_11; EXIT WHEN CUR1%NOTFOUND; IF SUBSTR(V_3 ,5,2) < '00' THEN V_3 := SUBSTR(V_3,1,4)||'01'; ELSIF SUBSTR(V_3 ,5,2) > '31' THEN IF SUBSTR(V_3 ,3,2) IN ('01','03','05','07','08','10','12') THEN V_3 := SUBSTR(V_3,1,4)||'31'; ELSE V_3 := SUBSTR(V_3,1,4)||'30'; END IF; END IF; IF SUBSTR(V_4 ,5,2) < '00' THEN V_4 := SUBSTR(V_4,1,4)||'01'; ELSIF SUBSTR(V_4 ,5,2) > '31' THEN IF SUBSTR(V_4 ,3,2) IN ('01','03','05','07','08','10','12') THEN V_4 := SUBSTR(V_4,1,4)||'31'; ELSE V_4 := SUBSTR(V_4,1,4)||'30'; END IF; END IF; IF SUBSTR(V_9 ,5,2) < '00' THEN V_9 := SUBSTR(V_9,1,4)||'01'; ELSIF SUBSTR(V_9 ,5,2) > '31' THEN IF SUBSTR(V_9 ,3,2) IN ('01','03','05','07','08','10','12') THEN V_9 := SUBSTR(V_9,1,4)||'31'; ELSE V_9 := SUBSTR(V_9,1,4)||'30'; END IF; END IF; IF SUBSTR(V_10 ,5,2) < '00' THEN V_10 := SUBSTR(V_10,1,4)||'01'; ELSIF SUBSTR(V_10,5,2) > '31' THEN IF SUBSTR(V_10,3,2) IN ('01','03','05','07','08','10','12') THEN V_10 := SUBSTR(V_10,1,4)||'31'; ELSE V_10 := SUBSTR(V_10,1,4)||'30'; END IF; END IF; IF SUBSTR(V_11 ,5,2) < '00' THEN V_11 := SUBSTR(V_11,1,4)||'01'; ELSIF SUBSTR(V_11,5,2) > '31' THEN IF SUBSTR(V_11,3,2) IN ('01','03','05','07','08','10','12') THEN V_11 := SUBSTR(V_11,1,4)||'31'; ELSE V_11 := SUBSTR(V_11,1,4)||'30'; END IF; END IF; BEGIN UPDATE TMP_AC SET MKR_3 = V_3, MKR_4 = V_4, MKR_9 = V_9, MKR_10 = V_10, MKR_11 = V_11 WHERE MKR_1 = V_CD; END; COMMIT; END LOOP; CLOSE CUR1; END; / 발주처 : http://myhome.netsgo.com/mino70/main_03_oracle03.htm

[ Oracle - Procedure 문법_EXCEPTION_Bulk_Bind ]

Bulk Bind

- Oracle9i에서는 Bulk bind가 보다 효율적이고 사용하기에 편리하도록 개선되었습니다.

   . bulk bind 연산의 실패에 대한 에러 핸들링 기능이 제공됩니다.

   . 동적 SQL에서 Bulk 연산을 수행할 수 있습니다.

Bulk Bind를 위한 예외 처리)

- 이전 릴리즈에서는 FORALL 연산이 수행되는 도중에 오류가 발생하면, 프로그램의 실행은 즉시 중단되고 예외가 발생했습니다.

- 새롭게 제공되는 예외 처리 메커니즘은 오류에 관한 정보를 기억하고 수행을 계속할 수 있도록 합니다.

- 프로그램이 수행을 마친 후에 그 동안 발생되었던 모든 오류가 한꺼번에 반환됩니다.

예: 예외 핸들링)

    DECLARE

    TYPE numtab IS table of number;

    v_discount numtab:= numtab(10,0,15,20,25,0,60,80,34,0);

    total_error NUMBER;

    BEGIN

        FORALL i IN v_discount.first..v_discount.last

            SAVE EXCEPTIONS

            UPDATE products

            set    min_price = list_price - list_price/v_numtab(i);

        Total_error := SQL%BULK_EXCEPTIONS.COUNT;   

예: 예외 핸들링)

      dbms_output.put_line('Total number of errors is ' || total_error);

      For i in 1 .. Total_error LOOP

           dbms_output.put_line('Error '||i||'occurred at iteration' ||SQL%BULK_EXCEPTIONS(i) );

          dbms_output.put_line('SQL Error Code is '||

                   SQL%BULK_EXCEPTIONS(i).error|| '[Error:'|| SQLERRM

                   (SQL%BULK_EXCEPTIONS(i).error)||']');

       END LOOP;

      END;

동적 SQL에서의 Bulk 연산)

    Oracle8i에서 소개된 동적 SQL은 Oracle9i부터는 다음과 같은 Bulk 연산을  지원합니다.

- BULK FETCH

- BULK EXECUTE IMMEDIATE

- FORALL과 함께 COLLECT INTO 및 RETURNING INTO

예: 동적 SQL과 Bulk Bind)

   DECLARE

        TYPE num_tab IS TABLE OF NUMBER;

        ids num_tab;

   BEGIN

     EXECUTE IMMEDIATE 'SELECT employee_id FROM employees'

        BULK COLLECT INTO ids;

   END;

예: 입력 변수를 위한 Bulk Bind)

    DECLARE

         TYPE num_tab IS TABLE OF NUMBER;

         TYPE char_tab is TABLE OF VARCHAR2(30);

       ids num_tab := num_tab (12,13,14,15);

       names char_tab := char_tab('R/D','IT','GL','PR');

    BEGIN

        FORALL iter IN 1..4 EXECUTE IMMEDIATE

                                 'INSERT INTO departments(department_id,

                                  department_name)VALUES(:1, :2)'

                               USING ids(iter), names(iter);

       END;

예: 출력 변수를 위한 Bulk Bind)

   DECLARE

        TYPE num_tab IS TABLE OF NUMBER;

        sql_str VARCHAR2(200);

        v_sal NUMBER := 10000;

        saltab num_tab;

   BEGIN

        sql_str := 'UPDATE employees SET salary = :1                                                      WHERE department_id = 10

                  RETURNING salary INTO :2';

        EXECUTE IMMEDIATE sql_str

                USING v_sal RETURNING BULK COLLECT INTO saltab;

   END;

[ Oracle - Procedure 문법_EXCEPTION_020 ]

Error Handling

1. Predefined & Nonpredefined

Define

 Error번호

 exception_name

Predefined

 ORA_01403

ORA_01422

 NO_DATA_FOUND

TOO_MANY_ROWS

Nonpredefined

 ORA_02292

 e_products_remaining

2. Predefined Exception

  ex) EXCEPTION

        WHEN NO_DATA FOUND THEN

          v_message := TO_CHAR (v_product_id)||' Is invalid.';

        WHEN TOO_MANY_ROWS THEN

          v_message := 'Data corruption in S_PRODUCT';

        WHEN OTHERS THEN

          v_message := 'Other error occurred.';

        END;

3. NonPredefined Error

 exception EXCEPTION;

 ...

PRAGMA EXCEPTION_INIT (exception, error_number);

  ex) [DECLARE}

         e_products_remaining EXCEPTION;

       PRAGMA EXCEPTION_INIT (

         e_products_remaining, -2292);

         ...

       BEGIN

         ...

       EXCEPTION

       WHEN e_products_remaining THEN

          v_message := 'Referential integrity constraint violated.';

          ...

       END;

4. User_Defined Exception

exception EXCEPTION;

...

RAISE exception;

  ex) [DECLARE]

          e_amount-remaining EXCEPTION;

          ...

       BEGIN

          ...

       RAISE e_amount_remaining;

          ...

       EXCEPTION

       WHEN e_products_remaining THEN

         v_message := 'There is still an amount in stock.';

         ...

       END;

발주처:http://myhome.netsgo.com/mino70/main_03_oracle02.htm

[ Oracle - Procedure 문법_EXCEPTION_010 ]

시스템 EXCEPTION

1. 변수정의 :

   문법 : SQL_ERROR_RTN   EXCEPTION;

   설명 : 모름???

2. SOURCE에서 사용법

    문법 : BEGIN

                         SELECT A.item_mon

                         INTO      nItem_mon

                         FROM   py_change A

                         WHERE  A.input_ymd = 'value' ;

              EXCEPTION      WHEN OTHERS THEN

                         nItem_mon  := 0;

              END;

     설명 : - select, insert, delete, update문이 실행 중 오류발생가능성 때문에 begin ~ exception~end; 문장을 기술한다.

               - select시 : others 는 주로 notfound 일 경우에 발생하므로  INTO 변수에 0 또는 space 값을 setting 한다.

               - insert, delete, update시 : others는 주로 오류가 발생한다.

                 이때, sqlcode 와 sqlerrm 시스템변수값을 활용하여 오류의 원인을 파악한다.

3.   EXCEPTION

      EXCEPTION

        WHEN NO_DATA_FOUND THEN

            i_errmsg := SQLCODE;

        WHEN OTHERS THEN

            i_errmsg := SQLCODE;

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

* 사용자 EXCEPTION

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

사용자 정의 예외처리는 오라클 서버에 의해 발생될 수 없는 예외 상황에 대해서 사용자가 임의로 정의하여 강제로 발생되게 하는 방법이다.

예외명은 DECLARE절에서 선언하고, 예외가 발생해야할 조건은 실행부분에 정의하면 된다. 예외 상황 발생시 처리해야할 루틴은 미리 정의된 Oracle Server 예외에서와 같이 명시해주면 된다. 단지 사용자가 정의한 예외명을 적어주면 된다.

[ 구문법 ]

    DECLARE

           Exception_name EXCEPTION;

           ......

     BEGIN

           ......

      RAISE Exception_name;

             ......

      EXCEPTION

      WHEN Exception_name THEN

               Statement-1;

               ......

[ 예제]

         DECLARE

                row_cnt int :=0;

               output_line varchar2(80);

               exception_row_over_5 EXCEPTION;

         BEGIN

               SELECT COUNT(*) INTO row_cnt FROM test_table;

               IF row_cnt > 5 THEN

                     RAISE exception_row_over_5;

               END IF;

               EXCEPTION

                     WHEN exception_row_over_5 THEN

                               output_line := 'Row_Count= ' || row_cnt;

                               dbms_output.put_line(output_line);

                END;

                /

[ Oracle - Procedure Package_DBMS_SQL_사용법2 ]

▣ DBMS_SQL package를 이용한 프로 시져 생성의 예

SQL>CREATE OR REPLACE PROCEDURE test(txt varchar2)  AS

                c integer;

                rows integer;

        BEGIN

                c := DBMS_SQL.OPEN_CURSOR;

                DBMS_SQL.PARSE(c,'create or replace '|| txt ,DBMS_SQL.NATIVE);

                rows := DBMS_SQL.EXECUTE(c);

                DBMS_SQL.CLOSE_CURSOR(c);

        END;

SQL>exec test('procedure ttum(aa  number) AS  kk number; BEGIN kk:= 0 ; kk := kk + aa ; END ; ');

SQL> exec test ('procedure ttt1(aa number) as kk varchar2(10); begin select 1 into kk from dual; end;');

[ Oracle - Procedure Package_DBMS_SQL_사용법1 ]

Dynamic SQL

1.  Using The DBMS_SQL Package To Execute DDL Statements:　　　

 < Example 1 >

 　　 Table을 Create하는 Procedure로 Table Name, Column Name과 그Type을Parameter로 받는다.　　　

 CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS　　　

 　 cursor1 INTEGER;　　　

 BEGIN　　　

 　 cursor1 := dbms_sql.open_cursor;　　　

 　 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);　　　

 　 dbms_sql.close_cursor(cursor1);　　　

 end;　　　

 /　　　

 SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');

 < Example 2 >

 CREATE or REPLACE PROCEDURE rows_greater_than (low_value  number) AS　　　

 　 cursor1　 integer;　　　

 　 rows_processed　 integer;　　　

 　 myempno number;　　　

 　 myename varchar2(20);　　　

 BEGIN　　　

 　 cursor1 := dbms_sql.open_cursor;　　　

 　 dbms_sql.parse (cursor1, 'select empno, ename from emp where empno > :x', dbms_sql.v7);　　　

 　 dbms_sql.bind_variable(cursor1, 'x', low_value);　　　

 　 dbms_sql.define_column (cursor1, 1, myempno);　　　

 　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　

 　 rows_processed := dbms_sql.execute (cursor1);　　　

 　 loop　　　

 　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　

 　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　

 　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　

 　　　 else　　　

 　　　　　 exit;　　　

 　　　 end if;　　　

 　 end loop;　　　

 　 dbms_sql.close_cursor (cursor1);　　　

 EXCEPTION　　　

 　 WHEN OTHERS THEN　　　

 　　　 dbms_output.put_line(sqlerrm);　　　

 　　　 if dbms_sql.is_open (cursor1) then　　　

 　　　　　 dbms_sql.close_cursor (cursor1);　　　

 　　　 end if;　　　

 END;　　　

 /

[ Oracle - Procedure 값_시스템변수 ]

     SQL%ROWCOUNT -> 가장 최근의 SQL문이 row갯수에 영향을 줄때(정수값을 갖는다)

     SQL%FOUND -> 가장 최근의 SQL문이 한개 이상의 row에 영향을 준다면 TRUE값을 갖는다.

     SQL%NOTFOUND -> 가장 최근의 SQL문이 어떤 row에도 영향을 주지 않는다면 TRUE값을 갖는다.

     SQL%ISOPEN -> 항상 FALSE에 대한 값을 구한다.

     USER : 현재 접속 사용자 ID

     DUP_VAL_ON_INDEX : insert 중 index에 의해 중복오류 발생

     NO_DATA_FOUND    : select 중 원하는 결과가 없을 경우 발생

    DUP_VAL_ON_INDEX             유일값 중복 Error

    INVALID_NUMBER                  Data type 불일치 Error

    NO_DATA_FOUND                  데이터를 반환하지 않은 SELECT 문

    TOO_MANY_ROWS                두 행 이상을 반환한 SELECT 문

    VALUE_ERROR                      대입되는 값이 길이 초과

    ZERO_DEVIDE                       0으로 나누기 시도