Oracle - Procedure 문법_Cursor_02

[ 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