농부지기

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

          ...

[ Oracle - Procedure 문법_LOOP문 ]

1. 문법

LOOP

statement;

...

EXIT [WHEN condition];

END LOOP;

2.예제

   ex) ...

          v_ord_id s_item.ord_id%TYPE := 101;

          v_counter NUMBER(2) := 1;

        BEGIN

          ...

        LOOP

          INSERT INTO s_item (ord_id, item_id)

          VALUES (v_ord_id, v_counter);

                        v_counter := v_counter + 1;

        EXIT WHEN v_counter > 10;

        END LOOP;

         ...

[ Oracle - Procedure 문법_IF문 ]

1. 기본 문법

2. if문에 in 사용법

3. if문에 exists 사용법

4. if문에 between 사용법

[ Oracle - Procedure 문법_FOR_LOOP문 ]

1.문법

    FOR index in [REVERSE] lower_bound..upper_bound LOOP

          statement;

    END LOOP;

2.예제

  ex) 우선 set serveroutput on을 한다.

       CREATE OR REPLACE PROCEDURE iterate

          (v_lower NUMBER,    v_upper NUMBER)

       IS

           v_counter NUMBER(10) := 0;

           v_output NUMBER(10);

       BEGIN

           FOR i IN v_lower..v_upper LOOP

                  v_counter := v_counter + 1;

                  v_output := i;

           END LOOP;

           DBMS_OUTPUT.PUT_LINE('Last value is '||TO_CHAR(v_output)  ||'. Total loops = '||TO_CHAR(v_counter));

      END iterate;

3. 예제 2

    설명 : - 1~150까지 1씩 증가 하면서 LOOP를 돈다.

    문법 :

          FOR nLoop IN 1..150 LOOP

                 ............

          END LOOP;

[ Oracle - Procedure 문법_CASE문 ]

CASE의 유형

- CASE 수식은 다음과 같이 사용할 수 있습니다.

    . 임의의 SQL 수식과 유사한 방법으로 쓰입니다.

    . 대입문의 변수로서, 그리고 SQL 문 또는 논리 연산의 일부로서 사용할 수 있습니다.

    . 키워드 CASE로 시작하여 END로 끝이 납니다.

- CASE 문은 다음과 같이 사용할 수 있습니다.

    . IF…THEN…ELSE 구문과 유사하게 독립적인 문장으로 쓸 수 있습니다.

    . 키워드 CASE로 시작하여 END CASE로 끝이 납니다.

- 단순 CASE :

    . 단일의 변수/수식에 대하여 복수의 값에 대해 적용됩니다.

    . WHEN 절 내에 비교 연산자를 쓸 수 없습니다.

-  검색 CASE :

    .  보다  유연하게  쓸  수  있습니다.

    .  각각의 WHEN  절은

        o  필요하다면  서로  다른  변수들을  사용할  수  있습니다.

        o  비교  연산자를  쓸  수도  있습니다.

예: 단순 CASE 수식)

    CREATE OR REPLACE FUNCTION get_comm (p_comm NUMBER)

     RETURN VARCHAR2 IS

               v_comm VARCHAR2(10);

    BEGIN

             v_comm :=    CASE p_comm

                                 WHEN .15 THEN 'fifteen percent'

                                 WHEN .2  THEN 'twenty percent'

                                 ELSE 'thirty percent'

                                 END;

              RETURN v_comm;

    END;

예: 단순 CASE 문)

    CREATE OR REPLACE FUNCTION get_comm (p_comm NUMBER)

        RETURN VARCHAR2 IS

              v_comm VARCHAR2(10);

    BEGIN

             CASE p_comm

                      WHEN .15 THEN

                             v_comm := 'fifteen percent';

                      WHEN .2 THEN

                             v_comm := 'twenty percent';

                      ELSE  

                             v_comm := 'thirty percent';

            END CASE;

            RETURN v_comm;

     END;

예: 검색 CASE 수식)

    CREATE OR REPLACE FUNCTION get_sal_grade (p_sal NUMBER)

        RETURN VARCHAR2 IS

           v_grade VARCHAR2(25);

   BEGIN

           v_grade :=  CASE

                             WHEN p_sal < 10000 THEN

                                       'salary is low'

                             WHEN p_sal BETWEEN 10000 AND 50000 THEN

                                       'salary is average'

                             ELSE

                                      'salary is high'

                             END;

            RETURN v_grade;

    END;

예: 검색 CASE 문)

    CREATE OR REPLACE FUNCTION get_raise (p_sal NUMBER)

    RETURN VARCHAR2 is

                v_raise VARCHAR2(10);

    BEGIN

               CASE

                          WHEN p_sal < 10000 THEN

                                     v_raise :='raise is 10%';

                          WHEN p_sal >= 10000 THEN

                                    v_raise := 'raise is 15%';  

                         END CASE;

             RETURN v_raise;

    END;

[ Oracle - Procedure 문법_선언문(Declare) ]

1) 테이블의 열의 type으로 변수선언

   %type은 열의 구조만 가져온다.

2) %rowtype : 해당 테이블의 모든 컬럼과 type으로 변수선언

    테이블이나 뷰 내부의 컬럼 데이터형, 크기, 속석등을 그대로 사용할수 있습니다.

    ⊙  %ROWTYPE 앞에 오는 것은 데이터 베이스 테이블 이름입니다.

    ⊙  지정된 테이블의 구조와 동일한 구조를 갖는 변수를 선언할수 있습니다.

    ⊙  데이터베이스 컬럼들의 수나 DATATYPE을 알지 못할 때 편리 합니다.

    ⊙  테이블의 데이터 컬럼의 DATATYPE이 변경될 경우 프로그램을 재수정할 필요가 없습니다

    주의) not null 제한은 포함안됨.

      예)   변수명  TABLE_NAME%ROWTYPE;

3) 배열선언

    형식) TYPE 정의명 IS TABLE OF 타입 INDEX BY BINARY_INTEGER;

          배열정  정의명;

    예)   TYPE t_val IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

          arr_val  t_val;

    사용예)   arr_val(0)   := '1234';

              arr_val(100) := 'asdf';

              괄호()를 안에 integer값(변수)를 사용해서 배열에 값을 넣거나 사용할 수 있다.

4) 변수 선언

   declare

       cnt    number(4);

       qty    number(11,3);

       errnum char(6):='-20999';                  --> 초기 할당 방법

5) 모든 변수 TYPE 선언

    BINARY_INTEGER

    NUMBER [(전체자리수, 소수점이하자릿수)]

    CHAR : 한문자

    CHAR(n)

    LONG

    LONG RAW

    VARCHAR2 (n) : 문자형중에서 가장 많이 사용

    DATE

    BOOLEAN : TRUE, FALSE

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

1. 테이블 변수의 type으로 변수 선언

   문법 :  v_f_apply_ymd   ODM_SHIFT_WORK_DAILY.apply_ymd%TYPE := '';

   설명 :  - ODM_SHIFT_WORK_DAILY 은 테이블 명

              - apply_ymd 은 ODM_SHIFT_WORK_DAILY  테이블의 컬럼 명

2. 배열 선언

    문법 : TYPE arr_holiday_work_hour  IS TABLE OF ODM_SHIFT_WORK_LIST.appl_holiday_work_hour%TYPE

                    INDEX BY BINARY_INTEGER;

              holiday_work_hour_arr arr_holiday_work_hour;

    설명 : - ODM_SHIFT_WORK_LIST : 테이블명

             - appl_holiday_work_hour  : ODM_SHIFT_WORK_LIST 테이블의 컬럼명

             - arr_holiday_work_hour   : 배열변수명

                                                        ODM_SHIFT_WORK_LIST.appl_holiday_work_hour 컬럼의 type으로 배열변수 정의

             - 이유는 모르겠지만 위에서 생성된  배열변수를 다시 아래에서 재 정의 후 아래

                 holiday_work_hour_arr 를  source에서 사용 된다.

      * 참고: source에서 사용법

                문법 :  holiday_work_hour_arr(숫자변수) := value;

3. error 처리 변수 선언

   문법 : SQL_ERROR_RTN   EXCEPTION;

   설명 : 모름???

4. 테이블 컬럼 모두를 한번에 변수 정의

   문법 : v_py  py_payroll%ROWTYPE;

   설명 : - 만약 py_payroll 테이블의 컬럼이 pay_ymd, pay_type, p001, p002, p003 이 존재 할 경우

             - v_py.pay_ymd,    v_py.pay_type,     v_py.p001,     v_py.p002,     v_py.p003  변수를 모두 사용 할 수 있다.

             - 보통 package정의 시 많이 사용 되며, package정의 시에  정의 되었을 경우

                종속된 procedure에서는   package_name.v_py.컬럼명     과 같이 사용 된다.

[ Oracle - Procedure를 바로 실행하기 ]

[ Oracle - PROCEDURE 개념 ]