농부지기

[ 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;