SQL/ORACLE함수
오라클 프로시져 기본 문법
nineDeveloper
2014. 6. 11. 15:11
오라클 프로시져 기본 문법
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; |