오라클 프로시져 기본 문법

오라클 프로시져 기본 문법

 

1. IF 제어문

IF [조건] THEN   [T-STATEMENTS;] ELSIF [조건] THEN   [F-T-STATEMENTS;] ELSE   [F-F-STATEMENTS;] END IF;

 

 

2. SELECT값 변수에 담기

V_COUNT NUMBER(10);   SELECT COUNT(*)   INTO V_COUNT FROM 테이블 ;

 

 

3. FOR ~ LOOP문

1) 형식     FOR 변수명 IN 시작값..종료값   LOOP;

 

 

4. CURSOR

1) DECLARE CURSOR [CUR_NAME] IS 쿼리 ;   2) CURSOR OPEN : 실행절에서 원하는 CURSOR를 OPEN : 실제 데이터가 저장되어 있는 FILE과 연결 3) DATA FETCH : 레코드를 메모리로 가지고 오는 역활 4) CURSOR CLOSE

 

  

4-1. CURSOR 의 속성

[CUR_NAME]%ISOPEN >> 현재 지정한 커서가 열려있는지 여부를 TRUE와 FALSE [CUR_NAME]%NOTFOUND >> FETCH할 데이터가 없으면 TRUE [CUR_NAME]%FOUND >> FETCH할 데이터가 있으면 TRUE [CUR_NAME]%ROWCOUNT >> 현재까지 반환된 모든 레코드 수

 

 

 4-2  커서 예

CURSOR C1 IS    SELECT    AA, BB, CC     FROM 테이블명;   BEGIN   FOR CUR_DT IN C1 LOOP --C1을 한번 패치하여 CUR_DT 에 넣는다.       DBMS_OUTPUT.PUT_LINE(CUR_DT.AA|| ' '||CUR_DT.BB);   END LOOP; END;          OPEN MM_CUR;       LOOP         FETCH  MM_CUR  INTO  V_YYMM, V_CALNDRDAY_CNT;         EXIT  WHEN MM_CUR%NOTFOUND;         BEGIN             INSERT INTO TB_XX                    ( AA, BB, CC )             SELECT  AA, BB, CC             FROM              .. ...                       EXCEPTION             WHEN    OTHERS    THEN                 W_ERR_MSG := ' XXX INSERT EXCEPTION ' || SUBSTRB(SQLERRM,1,150);         END;     END LOOP;         CLOSE MM_CUR;

 

4.3

CURSOR C2_COLUMNS( W_TAB_ID VARCHAR2 ) IS          SELECT  COLUMN_NAME          FROM    USER_TAB_COLUMNS          WHERE   TABLE_NAME = W_TAB_ID          ORDER   BY COLUMN_ID;                             OPEN C2_COLUMNS( W_TAB_ID );             LOOP                 FETCH  C2_COLUMNS    INTO W_COL_ID;                 EXIT  WHEN    C2_COLUMNS%NOTFOUND;                   ...                 END IF;             END LOOP;

 

 

4-4. 커서 예

CREATE OR REPLACE PROCEDURE "SP_TEST"(             P_YD_GP      In VARCHAR2 ) IS     PRAGMA AUTONOMOUS_TRANSACTION;    CURSOR c1 IS         SELECT AA,BB,MAX_ROW FROM TB_TEST         ;         rec_c1 c1%ROWTYPE;           I_MAX_LAY          NUMBER(2); BEGIN          OPEN c1;     LOOP         FETCH c1 INTO rec_c1;         EXIT WHEN c1%NOTFOUND;                 I_MAX_LAY := rec_c1.MAX_ROW ;                 FOR II IN 1..I_MAX_LAY LOOP             INSERT INTO TEST_DB             (AA,BB)             VALUES             (                 rec_c1.AA, rec_c1.BB             );         END LOOP;     END LOOP;     CLOSE c1;      COMMIT;   Exception            WHEN OTHERS THEN         BEGIN                 ROLLBACK ;         END;       END SP_TEST;

 

 

5. 동적 쿼리 생성 및 실행

W_SQL_BLOCK       VARCHAR2(4000);       W_SQL_BLOCK := 'DELETE FROM ' || W_TAB_ID || ' WHERE COL_NM1 =  :1' ;   W_SQL_BLOCK := W_SQL_BLOCK || ' AND  COL_NM2 =  :2 ';     EXECUTE IMMEDIATE W_SQL_BLOCK USING W_COL_NM1, P_COL_NM2;   begin   execute immediate ′insert into dept values (:1, :2, :3)′    using 50, l_depnam, l_loc;       commit; end;    execute immediate ′select count(1) from emp′    into l_cnt;  dbms_output.put_line(l_cnt);    execute immediate 'insert into temp(empno, ename) ' ||                    '          select empno, ename from emp ' ||                    '          where  sal > :1'     using l_sal;