농부지기

[ Oracle - Procedure BULK BIND(BULK COLLECT INTO문) ]

SELECTS문에서 한번에 대량의 레코들 취득 하는 경우, BULK COLLECT구를 사용하면

한번에 여러개의 레코드를 취득할수 있으므로 퍼포먼스 향상

Patten 1

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

DECLARE
  TYPE empno_tbl_type IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
  empno_tbl  empno_tbl_type;
BEGIN
  SELECT EMPNO BULK COLLECT INTO empno_tbl FROM EMP;
  IF empno_tbl.COUNT > 0 THEN
    FOR i IN empno_tbl.FIRST..empno_tbl.LAST LOOP
      UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = empno_tbl( i );
    END LOOP;
  END IF;
END;
/

Patten 2

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

DECLARE
  TYPE emp_tbl_type IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tbl  emp_tbl_type;
BEGIN
  SELECT * BULK COLLECT INTO emp_tbl FROM EMP;
  IF emp_tbl.COUNT > 0 THEN
    FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
      UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
    END LOOP;
  END IF;
END;
/

Patten 3 커서 이용

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

DECLARE
  CURSOR emp_cur IS
    SELECT * FROM EMP;
  TYPE emp_tbl_type IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tbl  emp_tbl_type;
BEGIN
  OPEN emp_cur;
  FETCH emp_cur BULK COLLECT INTO emp_tbl;
  CLOSE emp_cur;
  IF emp_tbl.COUNT > 0 THEN
    FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
      UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
    END LOOP;
  END IF;
END;
/

즉,커서를 이용할시 취득할 데이터 수가 많을듯하면 Limit를 사용하여 일정 레코드 단위로

Fetch하는 것이 성능면에서 좋다.

FETCH emp_cur BULK COLLECT INTO emp_tbl LIMIT 100;

[ Oracle - Procedure 문법_FOR_SELECT문 ]

[ Oracle - Procedure 문법_Cursor_SubQuery ]

[ Oracle - Procedure 문법_Cursor_For ]

Cursor FOR문

--장점 : open, fetch 등을 하지 않아도 됨

--단점 : ??

--질문 : 만약, 한건도 없을 경우 for loop 가 수행 되는지 궁금??

CREATE OR REPLACE PROCEDURE HRMS.Op_Odm_Shift_Work_Daily_Update (

    in_yyyymm                           IN  VARCHAR2,   -- 작업년월

    in_emp_id           IN  VARCHAR2,   -- 사번

    in_apply_ym_m       IN  VARCHAR2,   -- 소급계산 처리시 수정처리년월

    in_mod_emp_id                    IN  VARCHAR2,   -- 작업자사번

    out_return                            OUT VARCHAR2    -- return 변수

)

IS

    CURSOR cur1 IS

        SELECT a.apply_ymd,

              a.shift_work_cd,

              a.data_type,

              a.sta_hm, a.end_hm

         FROM  ODM_SHIFT_WORK_DAILY a,

                      ODM_SHIFT_WORK_CODE b,

                    ODM_CALENDAR c

         WHERE a.shift_work_cd = b.cd

           AND     a.apply_ymd = c.cal_ymd

           AND    b.idx_id = '1109'

           AND    a.emp_id = in_emp_id

           AND    a.apply_ymd LIKE in_yyyymm || '%' ;

BEGIN

   ......

FOR eItem IN  cur1  LOOP

    ..........

   -- 이때 eItem 변수는 정의하지 않아도 사용가능하다.

    변수1 := eItem.apply_ymd;

    변수2 := eItem.shift_work_cd;

     .........

END LOOP;

    .........

END;

[ Oracle - Procedure 문법_Cursor_Fetch ]

CREATE OR REPLACE PROCEDURE HRMS.Op_Odm_Shift_Work_Daily_Update (

    in_yyyymm                           IN  VARCHAR2,   -- 작업년월

    in_emp_id           IN  VARCHAR2,   -- 사번

    in_apply_ym_m       IN  VARCHAR2,   -- 소급계산 처리시 수정처리년월

    in_mod_emp_id                    IN  VARCHAR2,   -- 작업자사번

    out_return                            OUT VARCHAR2    -- return 변수

)

IS

    CURSOR cur1 IS

        SELECT a.apply_ymd,

              a.shift_work_cd,

              a.data_type,

              a.sta_hm, a.end_hm

         FROM  ODM_SHIFT_WORK_DAILY a,

                      ODM_SHIFT_WORK_CODE b,

                       ODM_CALENDAR c

         WHERE a.shift_work_cd = b.cd

            AND     a.apply_ymd = c.cal_ymd

           AND    b.idx_id = '1109'

           AND    a.emp_id = in_emp_id

           AND    a.apply_ymd LIKE in_yyyymm || '%' ;

BEGIN

   --이때 cur1에 있는  in_emp_id 변수를 활용해서 sql 문이 실행 된다.

    OPEN cur1;

    LOOP

    FETCH cur1 INTO     v_f_apply_ymd,

            v_f_shift_work_cd,

            v_f_data_type,

            v_f_sta_hm,

            v_f_end_hm;

    EXIT WHEN cur1%NOTFOUND;

        ............... business logic ..........

    END LOOP;

    CLOSE cur1;

END;

[ Oracle - Procedure 문법 Cursor_Dynamic Fetch ]

설명 : - sql 문은 string 으로 생성 후 dynamic  loop를 돌린다.

         - dynamic cursor 에 대한 변수 선언 필요

문법 :

CREATE OR REPLACE procedure                HRMS.op_payroll_detail_mm_create(

    aRtn OUT       VARCHAR2 ) IS

    -- Cursor 선언

    TYPE   DYN_CURSOR_TYPE   IS REF CURSOR;

    DYN_CUR         DYN_CURSOR_TYPE;

BEGIN

   v_sqlsyntax := 'select a.pay_ymd, a.pay_type_cd, a.sta_ymd, a.end_ymd, a.pay_ym, a.duty_ym, a.give_ymd, a.calc_limit_yn

            from py_pay_ymd a,

            py_retroact b

            where a.pay_ymd = b.retro_pay_ymd

           and a.pay_type_cd = b.retro_pay_type_cd

           and b.emp_id = ''' || aEmp_id || ''' ';

   OPEN DYN_CUR FOR v_sqlsyntax;

   LOOP

   FETCH DYN_CUR INTO  sPay_ymd,

                sPay_type_cd,

                sSta_ymd,

                sEnd_ymd,

                sPay_ym,

                sDuty_ym,

                sGive_ymd,

                sCalc_limit_yn;

     EXIT WHEN DYN_CUR%NOTFOUND;

END;

[ Oracle - Procedure 문법_Cursor_02 ]

CURSOR 문

Explicit Cursors

1. Cursor

① Implicit cursor : PL/SQL이 정해 주는 것.

     SQL%NOTFOUND

     SQL%ISOPEN

     SQL%FOUND

     SQL%ROWCOUNT

 ② Expicit cursor : 응용 프로그래머에 의해서 어떤 동작을 수행하고자 할때 Explict cursor 를 사용하면

    buffer에 저장된다.

     Cursor_name%NOTFOUND

     Cursor_name%ISOPEN

     Cursor_name%FOUND

     Cussor_name%ROWCOUNT

2. Controlling Explicit cursors

  DECLARE OPEN FETCH EMPTY CLOSE

① Declaring the Cursor

   DECLARE

   CURSOR cursor_name IS

   select_statement;

② Opening the Cursor

      OPEN cursor_name;

③ Fetching Data from the Cursor

      FETCH cursor_name INTO variable1, variable2,....;

④ Closing the Cursor

     CLOSE cursor_name;

   ex) CREATE OR REPLACE PROCEDURE ord_process

           (v_ord_id IN s_item.ord_id%TYPE,

            v_message OUT VARCHAR2)

         IS

            v_product_id    s_item.product_id%TYPE;

            v_item_total    NUMBER(11,2);

            v_order_total   NUMBER(11,2) := 0;

            CURSOR item_cursor IS

              SELECT product_id, price * quantity

              FROM   s_item

              WHERE  ord_id = v_ord_id;

         BEGIN

            OPEN item_cursor;

            LOOP

               FETCH item_cursor INTO v_product_id, v_item_total;

               EXIT WHEN item_cursor%ROWCOUNT > 5

                 OR item_cursor%NOTFOUND;

               v_order_total := v_order_total + v_item_total;

               v_message := 'Product ID '||

                 TO_CHAR(v_product_id)||' has a total of '||

                 TO_CHAR(v_order_total,'$999,999,999.99');

            END LOOP;

            CLOSE item_cursor;

            COMMIT;

         END ord_process;

3. Cursor and Records (구조체?)

  ex) CURSOR emp_cursor IS

       SELECT id, salary, start_date, rowid

       FROM s_emp

       WHERE dept_id = 41;

          emp_record emp_cursor%ROWTYPE;

       BEGIN

      OPEN emp_cursor;

          ....

       FETCH emp_cursor INTO emp_record;

4. Cursor FOR Loops (open,fetch,close가 자동으로 수행됨)

    FOR record_name IN cursor_name LOOP

    statement1;

    statement2;

    ...

    END Loop;

  ex) FOR item_record IN item_cursor LOOP

           v_order_total := v_order_total +

           (item_record.price * item_record.quantity);

           i := i + 1;

           product_id_talble(1) := item_record.product_id;

           order_total_table(1) := v_order_total;

       END LOOP;

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

[ Oracle - Procedure 문법_Cursor_01 ]

declare

    x_sa_code    number;

    x_tax_io     number;

    x_io_date    date;

    x_io_no      number;

    x_tax_sno    number;

    CURSOR c_aaa is select distinct SA_CODE,TAX_IO,IO_DATE from ACC.TAX;

    CURSOR c_bbb is select TAX_SNO

                      from ACC.TAX

                     where SA_CODE=x_sa_code and TAX_IO=x_tax_io and IO_DATE=x_io_date;

begin

    open c_aaa;

    loop

        fetch c_aaa into x_sa_code,x_tax_io,x_io_date;

        exit when c_aaa%notfound;

        x_io_no:=0;

        open c_bbb;

        loop

                      c_bbb into x_tax_sno;

                      when c_bbb%notfound;

                      x_io_no:=x_io_no+1;

                      update ACC.TAX

                      set    IO_NO=x_io_no

              where  TAX_SNO=x_tax_sno;

        end loop;

        close c_bbb;

    commit;

    end loop;

    close c_aaa;

  exception

      when others then

         rollback;

         raise_application_error(-20001,sqlErrm(SqlCode));

end;

[ Oracle - Procedure 문법_DBMS_OUTPUT ]

1. SET SERVEROUTPUT ON ;    -- DBMS 출력하기 위해서~~

   (or set serveroutput on size 1000000;)

   1.1 procedure 실행하기 전에 먼저 위 명령어 실행

   1.2 pl/sql 문장내 맨위에  [DBMS_OUTPUT.ENABLE;] 도 해줘야 한다.

   1.3 DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_emp.empno );

WHILE문

1. 형식

WHILE condition LOOP

statement;

...

END LOOP;

2. 예제

  ex) ...

          v_ord_id s_item.ord_id%TYPE := 101;

          v_counter NUMBER(2) := 1;

        BEGIN

          ...

        WHILE v-counter <= 10 LOOP

            INSERT INTO s_item (ord_id, item_id)

            VALUES (v_ord_id, v_counter);

                          v_counter := v_counter + 1;

        END LOOP;

          ...