농부지기

[ Oracle - Procedure 함수_NULLIF.COALESCE ]

NULLIF와 COALESCE는 CASE 수식의 축약된 형태입니다.

NULLIF는 NVL 함수의 역입니다.

COALESECE는 DECODE 함수와 유사합니다.

예: NULLIF)

CREATE OR REPLACE PROCEDURE get_comm_pct (p_comm NUMBER) is

        v_result VARCHAR2(10);  

BEGIN

        v_result := NULLIF(p_comm, 0);

        DBMS_OUTPUT.PUT_LINE ('The commission percent is '|| v_result*100||'%');

END;

SQL> Exec get_comm_pct;

The commission percent is 20%

PL/SQL procedure successfully completed.

예: COALESCE)

CREATE OR REPLACE PROCEDURE get_comm_pct (p_comm NUMBER) is

            v_result number;

BEGIN

    v_result := COALESCE(p_comm, 0);    

    DBMS_OUTPUT.PUT_LINE ('The commission percent is '||v_result*100||'%');

END;

SQL> Exec get_comm_pct;

The commission percent is 20%

PL/SQL procedure successfully completed.

[ Oracle - Procedure 함수_NULL ]

1. IF 문에서 NULL 비교 방법

   * Oracle 에서는 '' 도 null 이고,    null 도 null 이다.

 정상 :

    1.   if :n.deptno is not null then

    2.   if  nvl(:new.qty_do,0) != 0  then

    3.   if  nvl(:new.qty_do,0) != 0  then

오류 :

     1.  if :n.deptno   =   null   then    ..

         pl-sql에서는 위와 같이 하면  null 이라고 비교가 되지 않는다.(즉, null 이여도 null 로 인식 하지 않는다).  물론 Error 도 없고. )

    2.   DECLARE    sYYMM   char(5);    -- 라고 정의 를 하면

         ..

          Script에서는    sYYMM = '' 이것도 아니고,       sYYMM  is Null    도 아니고

                                 sYYMM = ' '  이다. (즉, space  한 칸으로 들어간다.)

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