검색결과 리스트
ORACLE /PL/SQL에 해당되는 글 8건
- 2014.02.16 8장 .예외처리
- 2014.02.16 7장. 명시적 커서 사용
- 2014.02.16 6장 . 조합데이터 유형
- 2014.02.16 5장. 제어 구조 작성
- 2014.02.16 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용
- 2014.02.16 3장 실행문 작성
- 2014.02.16 2장 PL/SQL 변수 선언
- 2014.02.16 1장. PL/SQL 소개
글
8. 예외 처리
SQL> ALTER TABLE emp
ADD CONSTRAINT emp_ck CHECK ( sal > 0 ) ; --제약조건 추가하고 실습시작.
SQL>
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
--------------------------------
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ; --정상 수행 가능한 문장.
UPDATE emp
SET sal = 0
WHERE empno = 7934 ; --제약조건 위배, Error문장.
END ;
/
ERROR at line 1:
ORA-02290: check constraint (ORA1.EMP_CK) violated
ORA-06512: at line 6
→ PL/ SQL 블럭중에서 하나라도 실패하는게 있다면, 정상 수행되는 문장도 수행되지 않는다.
SQL> SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
#TX-0 OPEN
UPDATE emp
SET sal = 9999
WHERE empno = 7782 ;
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ; --정상 수행 가능한 문장.
UPDATE emp
SET sal = 0
WHERE empno = 7934 ; --제약조건 위배, Error문장.
END ;
/
SQL> SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 9999
7839 KING 5000
7934 MILLER 1300
→ PL/SQL블럭이 성공이 되든 실패가 되든 사용자가 COMMIT/ ROLLBACK 하지 않으면
트랜잭션은 종료되어있지 않다. update문은 성공하고 PL/SQL블럭은 실패한 결과를 보여준다.
Ex1) 모든 ERROR상황은 OTHERS를 통해 잡을 수 있다.
BEGIN
UPDATE EMP
SET SAL = 3000
WHERE EMPNO = 7782;
UPDATE EMP
SET SAL = 0
WHERE EMP = 7934 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/
EX2)
SET SERVEROUTPUT ON
BEGIN --암시적으로 세이브 포인트가 생긴다.
UPDATE EMP
SET SAL = 3000
WHERE EMPNO = 7782; --실행됨.
UPDATE EMP
SET SAL = 0
WHERE EMP = 7934 ; --실행안됨 Exception 절로넘어가서 필요한 처리기로간다.
UPDATE EMP
SET SAL = 3000
WHERE EMPNO = 7782; --시도조차 하지 않는다.
EXCEPTION
WHEN NO_DATA_FOUND THEN
DMBS_OUTPUT.PUT_LINE('NO DATA');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR'); --에러처리를 한다.
END;
/
#에러를 핸들링 하지 못한다면 해당 PL/SQL블럭은 취소가 된다.
EX)오라클 서버로부터 만들어진 암시적 에러
SET SERVEROUTPUT ON
DECLARE
V_SAL NUMBER ;
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO= 1234 ;
END ;
/
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Ex) SQLERRMM 을 이용한 에러 확인.
SQL> SET SERVEROUTPUT ON
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ;
UPDATE emp
SET sal = 0
WHERE empno = 7934 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( SQLERRM ) ;
END ;
/
ORA-02290: check constraint (ORA1.EMP_CK) violated
PL/SQL procedure successfully completed.
※ 미리 정의된 예외 처리
ex)
SET SERVEROUTPUT ON
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.sal ) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('TOO_MANY_ROWS') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('OTHERS') ;
END ;
/
Enter value FOR name: RYU OLD 6: WHERE ename = UPPER('&name') ;
NEW 6: WHERE ename = UPPER('RYU') ;
NO_DATA_FOUND PL/SQL
PROCEDURE successfully completed.
※ 미리 정의되지 않은 예외 처리
ex)
SET SERVEROUTPUT ON
DECLARE
emp_rec emp%ROWTYPE ;
e_ck EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_ck , -2290) ; --예외생성.
BEGIN
SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;
IF emp_rec.sal < 2000 THEN
UPDATE emp SET sal = &salary WHERE empno = emp_rec.empno ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO DATA') ;
WHEN E_CK THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Salary') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
/
※ 사용자 정의 예외
SET SERVEROUTPUT ON
v_deptno NUMBER := 50 ;
v_name VARCHAR2(20) := 'Testing' ;
e_invalid_department EXCEPTION;
BEGIN
UPDATE dept SET dname = v_name WHERE deptno = v_deptno ;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department ;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
/
No such department id. PL/SQL
PROCEDURE successfully completed.
※ RASE_APPLICATION_ERROR 사용
– 마치 오라클 서버에서 만들어진것 처럼 ERROR코드와 ERROR메세지가 나온다.
– 비정상종료를 강제로 유발시키고자할때 사용한다(암시적인 에러와 동일하다)
– 이전에 정상실행된 SQL문이 있더라도 ROLLBACK된다.
UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;
END IF;
END;
/
ERROR at line 1:
ORA-20001: No such department id.
ORA-06512: at line 7
ex2) 사용자 정의 에러문장은 보여주지만, 정상종료된다.
BEGIN
UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END;
/
ORA-20001: No such department id. PL/SQL
PROCEDURE successfully completed.
Ex3)
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR ( -20001, 'Too Many Rows', TRUE ) ;
--FALSE 로 변경하면, 원래에러메시지만 나온다.
END ;
/
ERROR at line 1: ORA-20001: Too Many Rows ORA-06512: at line 9 ORA-01422: exact
FETCH returns more than requested NUMBER OF rows
※ 예외 전달
ex1)
UPDATE emp SET sal = 7777 WHERE empno = 7788 ;
BEGIN
UPDATE emp SET sal = 9999 WHERE empno = 7566 ;
UPDATE emp SET sal = 0 WHERE empno = 7839 ;
UPDATE emp SET sal = 9999 WHERE empno = 7499 ;
END ; – ERROR1
UPDATE emp SET sal = 7777 WHERE empno = 7369 ;
END ;
/
ERROR1은 그 호출한 바깥으로 에러를 전달한다, 바깥쪽 블럭에도 EXCEPTION이 없기때문에 예외처리 되지 않으며, 어떠한 결과도 변경되지 않는다.
ERROR at line 1:
ORA-02290: check constraint (ORA1.EMP_CK) violated
ORA-06512: at line 11
SQL> SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369, 7499) ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7566 JONES 2975
7788 SCOTT 3000
7839 KING 5000
EX2)
SQL> SET SERVEROUTPUT ON
BEGIN
UPDATE emp SET sal = 7777 WHERE empno = 7788 ;
BEGIN
UPDATE emp SET sal = 9999 WHERE empno = 7566 ;
UPDATE emp SET sal = 0 WHERE empno = 7839 ; → Exception절로 이동한다.
UPDATE emp SET sal = 9999 WHERE empno = 7499 ; → 시도하지 않는다.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
UPDATE emp SET sal = 7777 WHERE empno = 7369 ; → 예외가 처리되었으므로 실행된다.
END ;
/
ORA-02290: CHECK CONSTRAINT (ORA1.EMP_CK) violated PL/SQL
PROCEDURE successfully completed. SQL>
SELECT empno,
ename,
sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 7777 7499 ALLEN 1600 7566 JONES 9999 7788 SCOTT 7777 7839 KING 5000 SQL> ROLLBACK ;
EX3)
SQL>
SET SERVEROUTPUT ON
BEGIN
UPDATE emp SET sal = 7777 WHERE empno = 7788 ;
BEGIN
UPDATE emp SET sal = 9999 WHERE empno = 7566 ;
UPDATE emp SET sal = 0 WHERE empno = 7839 ; → 에러 발생.
UPDATE emp SET sal = 9999 WHERE empno = 7499 ;
END ;
UPDATE emp SET sal = 7777 WHERE empno = 7369 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ; → 에러처리.
END ;
/
ORA-02290: check constraint (ORA1.EMP_CK) violated
PL/SQL procedure successfully completed.
SQL> SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7566 JONES 9999
7788 SCOTT 7777
7839 KING 5000
#정리다시해야함.
중첩된 데이터의 피엘에스큐엘을 사용하면 정상 적으로 되다가 서브블록이 호출되면
자기불럭의 익셉션 절을 찾고, 핸들링했다면 메인블럭으로 돌아와서 나머지명령을 실행한다.
서브블록에서 에러를 핸들링 하지못했다면(익셉션절이 없거나, 있지만 처리기가 제대로 되지않을경우)
메인절의 익셉션 절로 넘어가고 그 사이에있는 것은 실행조차 되지 않는다.
메인절에서 처리기를 정상 처리해줬다면 서브블럭은 정상처리된다.
BEGIN
SQL 1
SQL 2 - ERROR EXCEPTION절로 넘어간다.
SQL 3 -실행조차 하지않는다.
EXCEPTION
END ;
Q> 만약 SQL 3번을 꼭 사용해야한다면. 중첩된 블록을 이용한다.
BEGIN
SQL 1
BEGIN
SQL 2
EXCEPTION
END ;
SQL 3
EXCEPTION
END ;
'ORACLE > PL/SQL' 카테고리의 다른 글
| 7장. 명시적 커서 사용 (0) | 2014.02.16 |
|---|---|
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
설정
트랙백
댓글
글
7. 명시적 커서 사용
ex1)많은 행을 가져오므로 ERROR
SQL>
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END ;
/
ERROR at line 1: ORA-01422: exact
FETCH returns more than requested NUMBER OF rows ORA-06512: at line 4
EX2) 커서 생성을 통한 여러 행 불러오기.
CURSOR emp_cur --Cursor 생성.
IS
SELECT * FROM emp WHERE deptno = 10 ;
emp_rec emp%ROWTYPE ;
BEGIN
OPEN emp_cur ; --open이 되어야 문장이 실행된다.
FETCH emp_cur INTO emp_rec ; --첫번째 행만 FETCH.
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
FETCH emp_cur INTO emp_rec ; --그 다음행 선별하여 꺼내온다.
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
CLOSE emp_cur ; --메모리 할당 해제.
END ;
/
7782 CLARK 7839 KING PL/SQL
PROCEDURE successfully completed.
Ex3) LOOP를 통한 반복.
DECLARE
CURSOR emp_cur
IS
SELECT * FROM emp WHERE deptno = 10 ;
emp_rec emp%ROWTYPE ;
BEGIN
OPEN emp_cur ;
LOOP
FETCH emp_cur INTO emp_rec ;
EXIT
WHEN emp_cur%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
CLOSE emp_cur ;
END ;
/
7782 CLARK 7839 KING 7934 MILLER PL/SQL
PROCEDURE successfully completed.
Ex3) For Loop문과 결합된 Cursor : 암시적으로 OPEN / FETCH / CLOSE가 수행된다.
CURSOR emp_cur
IS
SELECT * FROM emp WHERE deptno = 10 ;
BEGIN
FOR emp_rec IN emp_cur --emp_rec선언 하지않아도 자동적으로 선언됨.
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
7782 CLARK 7839 KING 7934 MILLER PL/SQL
PROCEDURE successfully completed.
Ex4)
FOR emp_rec IN
( SELECT * FROM emp WHERE deptno = 10
)
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
7782 CLARK
7839 KING
7934 MILLER
PL/SQL procedure successfully completed.
Ex4) Parameter가 포함된 커서.
DECLARE
CURSOR emp_cur ( p_deptno NUMBER )
IS
SELECT * FROM emp WHERE deptno = p_deptno ;
BEGIN
FOR emp_rec IN emp_cur (10)
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.deptno || ' : ' || emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
FOR emp_rec IN emp_cur (20)
LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.deptno || ' : ' || emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
SELECT empno, ename, sal, deptno FROM emp
WHERE deptno = 10 FOR UPDATE ; --검색된 행에대해 LOCK이 걸린다.
※ WHERE CURRENT OF 절 사용
Ex1)현재 내가 FETCH 해온 그 행에대해 직접적인 UPDATE를 한다.
DECLARE
CURSOR emp_cur
IS
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE ;
BEGIN
FOR emp_rec IN emp_cur
LOOP
IF emp_rec.sal < 2000 THEN
UPDATE emp SET sal = sal * 1.1 WHERE CURRENT OF emp_cur ;
/* WHERE empno = emp_rec.empno */ --최선이아니다!.
END IF ;
END LOOP ;
END ;
/
전재 조건으로 반드시 해당 커서는 선언될 때 FOR UPDATE 절이 있어야한다.
LOCK이 많이 발생해 자주 쓸 순 없다.
* 해결방안. #음성178 확인50분 부터
DECLARE
CURSOR emp_cur
IS
SELECT a.*, rowid rid from a FROM emp a WHERE deptno = 10 FOR UPDATE ;
BEGIN
FOR emp_rec IN emp_cur
LOOP
IF emp_rec.sal < 2000 THEN
UPDATE emp SET sal = sal * 1.1 WHERE emp_rec.rid;
END IF ;
END LOOP ;
END ;
/
'ORACLE > PL/SQL' 카테고리의 다른 글
| 8장 .예외처리 (0) | 2014.02.16 |
|---|---|
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
설정
트랙백
댓글
글
6. 조합 데이터 유형
※ PL/SQL Record
DECLARE
TYPE emp_rec_typ IS RECORD --레코드 구조체. 필드는 둘 이상.
( ename VARCHAR2(10),
sal emp.sal%TYPE,
job emp.job%TYPE := 'NONE' ) ;
emp_rec EMP_REC_TYP ;
BEGIN
SELECT ename, sal, job INTO emp_rec
FROM emp
WHERE empno = 7788 ;
END ;
/
PL/SQL procedure successfully completed.
※ %ROWTYPE 사용
emp_rec emp%ROWTYPE ; --테이블 emp의 이름 및 유형을 가져온다.
BEGIN
SELECT * INTO emp_rec FROM emp WHERE empno = 7788 ;
END ;
/
PL/SQL procedure successfully completed.
emp_rec emp%ROWTYPE ; --테이블,뷰이름%ROWTYPE
BEGIN
SELECT * INTO emp_rec FROM emp WHERE empno = 7788 ;
INSERT INTO copy_emp VALUES emp_rec ;
SELECT * INTO emp_rec FROM emp WHERE empno = 7782 ;
emp_rec.sal := emp_rec.sal * 1.2 ;
emp_rec.hiredate := SYSDATE ;
UPDATE copy_emp SET ROW = emp_rec WHERE empno = 7782 ;
END ;
/
PL/SQL
PROCEDURE successfully completed.
#추가해야함.
'ORACLE > PL/SQL' 카테고리의 다른 글
| 8장 .예외처리 (0) | 2014.02.16 |
|---|---|
| 7장. 명시적 커서 사용 (0) | 2014.02.16 |
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
설정
트랙백
댓글
글
1.IF 문
ex1)
SET SERVEROUTPUT ON
DECLARE
v_myage NUMBER := 10 ;
BEGIN
IF v_myage < 11 THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
END IF;
END;
/
I am a child
PL/SQL procedure successfully completed.
ex2)
DECLARE
v_myage NUMBER := 31 ;
BEGIN
IF v_myage < 11 THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;
/
I am not a child
PL/SQL procedure successfully completed.
Ex3) 초기화가 되지 않은 변수에대한 IF문
DECLARE
v_myage NUMBER ;
BEGIN
IF v_myage < 11 THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am not a child ');
END IF;
END;
/
I am not a child
PL/SQL procedure successfully completed.
-> NULL은 비교가 불가 하므로 ELSE로 넘어가서 I am not a child 출력.
Ex4) ELSIF
DECLARE
v_myage NUMBER := 31 ;
BEGIN
IF v_myage < 11 THEN
DBMS_OUTPUT.PUT_LINE(' I am a child ');
ELSIF v_myage < 20 THEN
DBMS_OUTPUT.PUT_LINE(' I am young ');
ELSIF v_myage < 30 THEN
DBMS_OUTPUT.PUT_LINE(' I am in my twenties');
ELSIF v_myage < 40 THEN
DBMS_OUTPUT.PUT_LINE(' I am in my thirties');
ELSE
DBMS_OUTPUT.PUT_LINE(' I am always young ');
END IF;
END;
/
I am in my thirties
PL/SQL procedure successfully completed.
2. CASE 표현식 : 값을 할당할때, 또 다른 명령을 실행시킬때 사용
ex1)값을 할당할 때
DECLARE
v_grade CHAR(1) := UPPER('&grade') ;
v_appraisal VARCHAR2(20) ;
BEGIN
v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ' Appraisal ' || v_appraisal);
END;
/
Enter value for grade: B
old 2: v_grade CHAR(1) := UPPER('&grade') ;
new 2: v_grade CHAR(1) := UPPER('B') ;
Grade: B Appraisal Very Good
PL/SQL procedure successfully completed.
ex2)또 다른 명령어를 실행하기 위한 CASE문
DECLARE
v_sum NUMBER ;
v_deptno NUMBER := &deptid ;
BEGIN
CASE v_deptno
WHEN 10 THEN
SELECT SUM(sal) INTO v_sum
FROM emp
WHERE deptno = 10 ;
WHEN 20 THEN
SELECT SUM(sal) INTO v_sum
FROM emp
WHERE deptno = 20 ;
WHEN 30 THEN
SELECT SUM(sal) INTO v_sum
FROM emp
WHERE deptno = 30 ;
ELSE
SELECT SUM(sal) INTO v_sum FROM emp ;
END CASE ;
DBMS_OUTPUT.PUT_LINE ( v_sum ) ;
END ;
/
Enter value for deptid: 30
old 3: v_deptno NUMBER := &deptid ;
new 3: v_deptno NUMBER := 30 ;
9400
PL/SQL procedure successfully completed.
3. Loop 문 : 반복수행
EX1) Basic Loop
SQL> SET SERVEROUTPUT ON
DECLARE
v_count NUMBER(2) := 1 ;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('count: '||to_char(v_count)) ;
v_count := v_count + 1 ;
EXIT WHEN v_count = 4 ;
END LOOP ;
END;
/
count: 1
count: 2
count: 3
PL/SQL procedure successfully completed.
Ex2) While Loop
BEGIN
WHILE v_count <= 3
LOOP
DBMS_OUTPUT.PUT_LINE ('count: '||to_char(v_count)) ;
v_count := v_count + 1 ;
END LOOP ;
END ;
/
count: 1
count: 2
count: 3
PL/SQL procedure successfully completed.
Ex3) For Loop, 반복 횟수를 알고있을 때 사용
BEGIN
FOR i IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;
END LOOP ;
END ;
/
count: 1
count: 2
count: 3
PL/SQL procedure successfully completed
Ex4)For Loop는 암시적으로 카운터가 생긴다.
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;
END LOOP ;
END ;
/
count: 1
count: 2
count: 3
PL/SQL procedure successfully completed
Ex5) Reverse For Loop 거꾸로연산.
BEGIN
FOR i IN REVERSE 1..3
LOOP
DBMS_OUTPUT.PUT_LINE ('count: '||to_char(i)) ;
END LOOP ;
END ;
/
count: 3
count: 2
count: 1
PL/SQL procedure successfully completed.
※ Nested Loops 중첩된 Loop를 빠져나갈때 Exit
DECLARE
x NUMBER := 3 ;
y NUMBER ;
BEGIN
<<OUTER_LOOP>>
LOOP
y := 1 ;
EXIT WHEN x > 5 ;
<<INNER_LOOP>>
LOOP
DBMS_OUTPUT.PUT_LINE ( x || ' * ' || y || ' = ' || x * y ) ;
y := y + 1 ;
EXIT WHEN y > 5 ;
END LOOP INNER_LOOP ;
x := x + 1 ;
END LOOP OUTER_LOOP ;
END ;
/
3 * 1 = 3
3 * 2 = 6
3 * 3 = 9
3 * 4 = 12
3 * 5 = 15
4 * 1 = 4
4 * 2 = 8
4 * 3 = 12
4 * 4 = 16
4 * 5 = 20
5 * 1 = 5
5 * 2 = 10
5 * 3 = 15
5 * 4 = 20
5 * 5 = 25
PL/SQL procedure successfully completed.
DECLARE
x NUMBER := 3 ;
y NUMBER ;
BEGIN
<<OUTER_LOOP>>
LOOP
y := 1 ;
EXIT WHEN x > 5 ;
<<INNER_LOOP>>
LOOP
DBMS_OUTPUT.PUT_LINE ( x || ' * ' || y || ' = ' || x * y ) ;
EXIT OUTER_LOOP WHEN x*y > 15 ;
y := y + 1 ;
EXIT WHEN y > 5 ;
END LOOP INNER_LOOP ;
x := x + 1 ;
END LOOP OUTER_LOOP ;
END ;
/
4. CONTINUE 문 :
11g부터 생긴것. Continue에 만족하는 사항이 생기면, 밑에 있는 것은 싫행하지말고첫번째 시퀀스부터 다시 시작함.
ex1)
SQL> SET SERVEROUTPUT ON
DECLARE
v_total SIMPLE_INTEGER := 0;
BEGIN
FOR i IN 1..5 LOOP
v_total := v_total + i;
DBMS_OUTPUT.PUT_LINE ('Total is: '|| v_total) ;
CONTINUE WHEN i > 3 ;
v_total := v_total + i;
DBMS_OUTPUT.PUT_LINE ('Out of Loop Total is: '|| v_total);
END LOOP;
END;
/
Total is: 1 <= 0 + 1 (i)
Out of Loop Total is: 2 <= 1 + 1 (i)
Total is: 4 <= 2 + 2 (i)
Out of Loop Total is: 6 <= 4 + 2 (i)
Total is: 9 <= 6 + 3 (i)
Out of Loop Total is: 12 <= 9 + 3 (i)
Total is: 16 <= 12 + 4 (i)
Total is: 21 <= 16 + 5 (i)
PL/SQL procedure successfully
ex2)
SQL> SET SERVEROUTPUT ON
v_total NUMBER := 0;
BEGIN
<<BeforeTopLoop>>
FOR i IN 1..5
LOOP
v_total := v_total + 1;
DBMS_OUTPUT.PUT_LINE ('Outer Total is: ' || v_total) ;
FOR j IN 1..5
LOOP
CONTINUE BeforeTopLoop
WHEN i + j > 5 ;
v_total := v_total + 1;
DBMS_OUTPUT.PUT_LINE ('Inner Total is: ' || v_total) ;
END LOOP;
END LOOP;
END ;
/
Outer Total is: 1 <= 0 + 1 (i=1)
Inner Total is: 2 <= 1 + 1 (i=1 , j=1)
Inner Total is: 3 <= 2 + 1 (i=1 , j=2)
Inner Total is: 4 <= 3 + 1 (i=1 , j=3)
Inner Total is: 5 <= 4 + 1 (i=1 , j=4)
Outer Total is: 6 <= 5 + 1 (i=2)
Inner Total is: 7 <= 6 + 1 (i=2 , j=1)
Inner Total is: 8 <= 7 + 1 (i=2 , j=2)
Inner Total is: 9 <= 8 + 1 (i=2 , j=3)
Outer Total is: 10 <= 9 + 1 (i=3)
Inner Total is: 11 <= 10 + 1 (i=3 , j=1)
Inner Total is: 12 <= 11 + 1 (i=3 , j=2)
Outer Total is: 13 <= 12 + 1 (i=4)
Inner Total is: 14 <= 13 + 1 (i=4 , j=1)
Outer Total is: 15 <= 14 + 1 (i=5)
PL/SQL procedure successfully completed.
'ORACLE > PL/SQL' 카테고리의 다른 글
| 7장. 명시적 커서 사용 (0) | 2014.02.16 |
|---|---|
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
| 2장 PL/SQL 변수 선언 (0) | 2014.02.16 |
설정
트랙백
댓글
글
4장. PL/SQL 프로그램에서 SQL 문과 상호 작용
# SQL/PLSQL 호출범위
SQL
- QUERY : SELECT
- DML : Insert / Update / Delete / Merge
- TCL : COMMIT / ROLLBACK / SAVEPOINT PL/SQL은 여기까지만 허용한다.
------------------------------------------------------------------------------------------------------------------------------------
- DCL : GRANT / REVOKE /
- DDL : CREATE / DROP / ALTER / TRUNBCATE / RENAME PL/SQL에서 호출 불가능.
DML( INSERT, UPDATE, DELETE ) 문
사용자가 명시적으로 COMMIT을 하지 않는다면 COMMIT되지 않는다.
실제 PL/SQL을 업무에서 사용한다면 PL/SQL 블럭 내에 COMMIT ; 을 넣어준다.
ex1) INSERT
BEGIN
INSERT INTO emp(empno, ename, sal, deptno)
VALUES (1234, 'RYU', 3000, 20) ;
END ;
/
PL/SQL procedure successfully completed.
BEGIN
UPDATE emp
SET sal = 4000
WHERE empno = 1234 ;
END ;
/
PL/SQL procedure successfully completed.
Ex2) DELETE
BEGIN
DELETE emp
WHERE empno = 1234 ;
END ;
/
PL/SQL procedure successfully completed.
DELETE사용시 완료가 되었다는 결과 확인만으로 1234가 있어서 영향을 받았는지, 아니면 0행이 지워졌는지 애매하다.
BEGIN
UPDATE emp
SET sal = 4000
WHERE empno = 7788 ;
UPDATE emp
SET sal = 3500
WHERE empno = 7566 ;
END ;
/
PL/SQL procedure successfully completed.
SELECT empno, sal FROM emp
WHERE empno IN (7788, 7566) ;
EMPNO SAL
---------- ----------
7566 3500
7788 4000
SQL> ROLLBACK ;
SQL>
SELECT empno, sal FROM emp
WHERE empno IN (7788, 7566) ;
EMPNO SAL
---------- ----------
7566 2975
7788 3000
#영향받은 행이 있는지 확인하는 방법.
SQL> SET SERVEROUTPUT ON
BEGIN
UPDATE emp
SET sal = 4000
WHERE empno = 7788 ;
DBMS_OUTPUT.PUT_LINE ( SQL%ROWCOUNT || ' rows updated') ;
--직전 명령 수행에 영향받은 행의 건수 count
DELETE emp
WHERE deptno = 10 ;
DBMS_OUTPUT.PUT_LINE ( SQL%ROWCOUNT || ' rows deleted') ;
END ;
/
1 rows updated
3 rows deleted
PL/SQL procedure successfully completed.
SQL> ROLLBACK ;
※ SELECT 문 : - FETCH가 필요하므로 SELECT에 INTO절이 필요하다.
- INTO 절의 변수에 값을 넣는다.
Ex 1)
SQL> SET SERVEROUTPUT ON
DECLARE
v_ename VARCHAR2(10) ;
v_sal emp.sal%TYPE ; --참조속성 선언.
BEGIN
SELECT ename, sal INTO v_ename, v_sal
FROM emp
WHERE empno = 7788 ; --v_ename과 v_sal은 스칼라변수기 떄문에, where 절을 사용하여 하나의 행을 저장해야한다.
DBMS_OUTPUT.PUT_LINE ( v_ename || ' : ' || v_sal ) ;
END ;
/
SCOTT : 3000
PL/SQL procedure successfully completed
Ex 2) 여러개의 행을 저장해야하므로 Error
SET SERVEROUTPUT ON
DECLARE
v_ename VARCHAR2(10) ;
v_sal emp.sal%TYPE ;
BEGIN
SELECT ename, sal INTO v_ename, v_sal
FROM emp
WHERE deptno = 10 ;
DBMS_OUTPUT.PUT_LINE ( v_ename || ' : ' || v_sal ) ;
END ;
/
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
Ex 3)검색된 행이 없는 Error
SQL> SET SERVEROUTPUT ON
DECLARE
v_ename VARCHAR2(10) ;
v_sal emp.sal%TYPE ;
BEGIN
SELECT ename, sal INTO v_ename, v_sal
FROM emp
WHERE empno = 1111 ;
DBMS_OUTPUT.PUT_LINE ( v_ename || ' : ' || v_sal ) ;
END ;
/
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
※ DDL, DCL 문 PL/SQL에서 호출 불가능.
BEGIN
DROP TABLE emp ;
END ;
/
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma raise return select update while with <an
identifier> <a double-quoted delimited-identifier> <a bind variable> <<continue close current delete
fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
#참고
SYS@ORCL>show parameter cursor;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 50
--동시에 오픈 할 수 있는 커서 50개. (명시적커서)
session_cached_cursors integer 50
--50개저장.
내 세션에서 만약 SQL이 실행 되었다면 별도의 공간(Closed Cuosor)에 실행계획을 저장하여 Library Cache를 들어가지 않고 확인한다.
명시적 커서를 이용하면 PL/SQL에서 조건에 맞는게 둘 이상 나올 경우 중간에 하나씩 하나씩 끊어갈 수 있다.
'ORACLE > PL/SQL' 카테고리의 다른 글
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
|---|---|
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
| 2장 PL/SQL 변수 선언 (0) | 2014.02.16 |
| 1장. PL/SQL 소개 (0) | 2014.02.16 |
설정
트랙백
댓글
글
1. PL/SQL 에서의 함수 사용
ex1)단일행 함수 사용.
SET SERVEROUTPUT ON
DECLARE
v_desc_size INTEGER(5);
v_prod_description VARCHAR2(70) := 'You can use this product with your radios for higher frequency';
BEGIN
v_desc_size := LENGTH(v_prod_description) ;
DBMS_OUTPUT.PUT_LINE (v_desc_size) ;
END ;
/
62
PL/SQL procedure successfully completed.
ex2)그룹함수는 scalar type에 사용될 수 없다.
DECLARE
v_desc_size INTEGER(5);
v_prod_description VARCHAR2(70) := 'You can use this product with your radios for higher frequency';
BEGIN
v_desc_size := MAX(v_prod_description) ;
DBMS_OUTPUT.PUT_LINE (v_desc_size) ;
END ;
/
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00204: function or pseudo-column 'MAX' may be used inside a SQL statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ex3)그룹함수가 SELECT 명령어와 함께 쓰이면 사용가능하다.
DECLARE
v_sum NUMBER ;
BEGIN
SELECT SUM(sal) INTO v_sum
FROM emp ;
DBMS_OUTPUT.PUT_LINE (v_sum) ;
END ;
/
29025
PL/SQL procedure successfully completed.
2. PL/SQL 에서 SEQUENCE 사용
ex1) 11g부터 제공됨.
SQL> CREATE SEQUENCE empno_seq START WITH 1000 ;
SQL> SET SERVEROUTPUT ON
DECLARE
v_num NUMBER := empno_seq.NEXTVAL ;
BEGIN
DBMS_OUTPUT.PUT_LINE ( v_num ) ;
END ;
/
1000
ex2)
DECLARE
v_num NUMBER ;
BEGIN
SELECT empno_seq.NEXTVAL INTO v_num
FROM dual ;
DBMS_OUTPUT.PUT_LINE ( v_num ) ;
END ;
/
1001
PL/SQL procedure successfully completed.
중첩 블록에서 변수의 범위
ex1) 상위 블럭에서 선언된 변수는 하위변수에서 참조가 가능하다.
SET SERVEROUTPUT ON
DECLARE
v_outer VARCHAR2(100) := 'Outer Variable' ;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Outer : ' || v_outer ) ;
DECLARE
v_inner VARCHAR2(100) := 'Inner Variable' ;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Outer : ' || v_outer ) ;
DBMS_OUTPUT.PUT_LINE ('Inner : ' || v_inner) ;
END ;
DBMS_OUTPUT.PUT_LINE ('Outer : ' || v_outer ) ;
END ;
/
Outer : Outer Variable
Outer : Outer Variable
Inner : Inner Variable
Outer : Outer Variable
ex2)하위 블록에 선언된 변수는 상위변수에서 출력 불가능하다.
SQL> SET SERVEROUTPUT ON
DECLARE
v_outer VARCHAR2(100) := 'Outer Variable' ;
BEGIN
DECLARE
v_inner VARCHAR2(100) := 'Inner Variable' ;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Outer : ' || v_outer ) ;
DBMS_OUTPUT.PUT_LINE ('Inner : ' || v_inner) ;
END ;
DBMS_OUTPUT.PUT_LINE ('Outer : ' || v_outer ) ;
DBMS_OUTPUT.PUT_LINE ('Inner : ' || v_inner) ;
END ;
/
ERROR at line 11:
ORA-06550: line 11, column 39:
PLS-00201: identifier 'V_INNER' must be declared
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
Ex3) 같은 변수이름으로 만들 경우, 레이블을 통한 구분.
BEGIN <<outer>> --테이블에 별칭을 붙이듯 레이블을지정.
DECLARE
v_father_name VARCHAR2(20):='Patrick';
v_date_of_birth DATE := TO_DATE('20-APR-1972','DD-MON-YYYY') ;
BEGIN
DECLARE
v_child_name VARCHAR2(20):='Mike';
v_date_of_birth DATE := TO_DATE('12-DEC-2002','DD-MON-YYYY') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||outer.v_date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
END;
END outer;
/
Father's Name: Patrick
Date of Birth: 20-APR-72
Child's Name: Mike
Date of Birth: 12-DEC-02
PL/SQL procedure successfully completed.
'ORACLE > PL/SQL' 카테고리의 다른 글
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
|---|---|
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 2장 PL/SQL 변수 선언 (0) | 2014.02.16 |
| 1장. PL/SQL 소개 (0) | 2014.02.16 |
설정
트랙백
댓글
글
변수
1.스칼라 데이터 (단일값 변수) :한번에 하나의 행이 하나의 컬럼의 값.( 보통 “V_컬럼의 이름” 이용.)
DECLARE -- 선언부
V_EMPNO NUMBER(4);
V_ENAME VARCHAR2(10);
V_SAL NUMBER(7,2) ;
V_DEPTNO NUMBER (2);
BEGIN
SELECT EMPNO,ENAME,SAL,DEPTNO
INTO V_EMPNO,V_ENAME,V_SAL,V_DEPTNO
FROM EMP
-- WHERE EMPNO = 7788;
END ;
/
→ 이 경우 ERROR 발생한다. 단일행 변수는 하나의 행만 저장 가능하다.
A. 적절한 where절을 통해 반드시 한개의 행만 검색되도록 해야한다.
Q)
V_EMPNO NUMBER(4);
V_ENAME VARCHAR2(10);
V_SAL NUMBER(7,2) ;
V_DEPTNO NUMBER (2);
BEGIN
SELECT EMPNO,ENAME,SAL,DEPTNO
INTO V_EMPNO,V_ENAME,V_SAL,V_DEPTNO
FROM EMP
WHERE EMPNO = 1234;
END ;
/
A..조건에 맞는 1234 행이 없으므로 ERROR발생한다.
V_EMPNO NUMBER(4) := 1234; -- 할당 연산자 := 를 통한 변수에 값 할당.
V_ENAME VARCHAR2(10) DEAFULT 'ABCD'; -- DEFAULT 를 통한 할당.
V_SAL NUMBER(7,2) ;
V_DEPTNO NUMBER (2);
BEGIN
V_EMPNO :=1234 ;
V_ENAME :='ABCD';
END ;
/
# 변수선언
SQL>
DECLARE
v_hiredate DATE ; -- 초기값이 없다면 기본 NULL을 가지고 시작한다.
-- (초기값이 없는것에 NOT NULL제약조건 사용할 수 없다.)
v_deptno NUMBER(2) NOT NULL := 10 ; -- NOT NULL 제약조건
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400 ; -- CONSTANT를 통해 상수선언. 실행부에서 값을 수정불가.
BEGIN
DBMS_OUTPUT.PUT_LINE ( v_hiredate ) ;
DBMS_OUTPUT.PUT_LINE ( v_deptno ) ;
DBMS_OUTPUT.PUT_LINE ( v_location ) ;
DBMS_OUTPUT.PUT_LINE ( c_comm ) ;
END ;
/
# PL/SQL 에서 Date type의 주의 사항
SQL> ALTER SESSION SET nls_date_format = 'DD-MON-RR' ;
SQL> SET SERVEROUTPUT ON
DECLARE
v_hiredate DATE := '09-DEC-13' ;
BEGIN
DBMS_OUTPUT.PUT_LINE ( v_hiredate ) ;
END ;
/
09-DEC-13
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET nls_date_format = 'RR/MM/DD' ;
SQL> SET SERVEROUTPUT ON
DECLARE
v_hiredate DATE := '09-DEC-13' ;
BEGIN
DBMS_OUTPUT.PUT_LINE ( v_hiredate ) ;
END ;
/
09/12/13 << 올바른 값인가?
13년 12월 9일인지, 09년 12월 13일인지 애매하다.
PL/SQL procedure successfully completed.
A.해결: TO_DATE를 통한 형변환
SQL> ALTER SESSION SET nls_date_format = 'RR/MM/DD' ;
SQL> SET SERVEROUTPUT ON
DECLARE
v_hiredate DATE := TO_DATE('09-DEC-13','DD-MON-RR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE ( v_hiredate ) ;
END ;
/
13/12/09
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET nls_date_format = 'DD-MON-RR' ;
→ 날짜는 날짜의 포멧의 영향을 받는다. TO_DATE를 통해 형변환을 하여 엉뚱한 값이 나오지 않게 한다.
# BINARY_FLOAT, BINARY_DOUBLE type 확인
SQL> SET SERVEROUTPUT ON
DECLARE
bf_var binary_float ;
bd_var binary_double ;
BEGIN
bf_var := 270/35 ;
bd_var := 140/0.35 ;
DBMS_OUTPUT.PUT_LINE ('bf: ' || bf_var );
DBMS_OUTPUT.PUT_LINE ('bd: ' || bd_var );
END ;
/
bf: 7.71428585E+000
bd: 4.0E+002
PL/SQL procedure successfully completed.
SQL> SET SERVEROUTPUT ON
DECLARE
bf_var NUMBER ;
bd_var NUMBER ;
BEGIN
bf_var := 270/35 ;
bd_var := 140/0.35 ;
DBMS_OUTPUT.PUT_LINE ('bf: ' || bf_var );
DBMS_OUTPUT.PUT_LINE ('bd: ' || bd_var );
END ;
/
bf: 7.71428571428571428571428571428571428571
bd: 400
PL/SQL procedure successfully completed
# %TYPE 사용
–이점: 참조하는 테이블/컬럼의 구조적인 변경이 있을경우 수정을 해야하는데, %TYPE을 사용하면 변경하지 않아도 된다.
SQL> SET SERVEROUTPUT ON
DECLARE
v_sal emp.sal%TYPE ;
BEGIN
SELECT sal INTO v_sal
FROM emp
WHERE empno = 7788 ;
DBMS_OUTPUT.PUT_LINE ( v_sal ) ;
END ;
/
3000
PL/SQL procedure successfully completed.
# Bind Variable 사용
--호출환경에서 선언된 변수. Sqlplus, sqldeveloper 에서 선언.
SQL> VARIABLE b_sal NUMBER
SQL> PRINT b_sal
B_SAL
----------
BEGIN
SELECT sal INTO :b_sal -- 호출함수에서 선언된 변수를 출력할 때 :을 붙인다.
FROM emp
WHERE empno = 7788 ;
END ;
/
PL/SQL procedure successfully completed.
SQL> PRINT b_sal
B_SAL
----------
3000
'ORACLE > PL/SQL' 카테고리의 다른 글
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
|---|---|
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
| 1장. PL/SQL 소개 (0) | 2014.02.16 |
설정
트랙백
댓글
글
1장
1. SQL 과 PL/SQL 의 차이
SQL : 조건 분기 불가능, 루핑 불가능, 변수처리 불가능, 단문 형(화면에 가져와야 그 다음명령실행)
PL/SQL : ORACLE DBMS에서 SQL문장과 제어문을 함게 사용할 수 있다. 코드 실행단위에 블록 구조를 제공한다.
변수, 상수, 데이터타입, 조건문,루프,DB의 객체로 저장가능.
PL/SQL Fundamentals p1-6그림 중요
2. PL/SQL 블럭
SQL> SET SERVEROUTPUT ON --DBMS_OUT_PUT을 통해 보여주기위함.
DECLARE --선언부( 데이터 이름과 타입 정의)
emp_rec emp%ROWTYPE ; --테이블 구조를 통채로 참조.
sum_rec emp_sum%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec -- 검색결과를 emp_rec에 저장.
FROM emp
WHERE empno = 7788 ;
DELETE emp
WHERE empno = 7788 ;
UPDATE emp_sum
SET sum_sal = sum_sal - emp_rec.sal
WHERE deptno = emp_rec.deptno ;
SELECT * INTO sum_rec
FROM emp_sum
WHERE deptno = emp_rec.deptno ;
DBMS_OUTPUT.PUT_LINE ( 'SUM Salary : ' || sum_rec.sum_sal ) ;
END ;
/
SUM Salary : 7875
PL/SQL procedure successfully completed.
SQL식
--PL/SQL에서 수행된 SQL결과는 PL/SQL 블럭으로 리턴된다(보통은 SQL명령을쓴 호출환경에 출력됨.)
사용자에게 보여주는것은 전체 PL/SQL의 최종결과 즉, 수행 되었는지 안되었는지만 보여준다.
→ 이와같은 행동을 반복할 때 귀찮으므로 PROCEDURE 라는 것이 있다.
3. Procedure 생성 – 마치 뷰,테이블 등을 만드는 것 처럼 DB의 객체를 생성하는것이다.
CREATE OR REPLACE PROCEDURE delete_emp
( p_empno NUMBER) AS
emp_rec emp%ROWTYPE ;
sum_rec emp_sum%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec
FROM emp
WHERE empno = p_empno ;
DELETE emp
WHERE empno = p_empno ;
UPDATE emp_sum
SET sum_sal = sum_sal - emp_rec.sal
WHERE deptno = emp_rec.deptno ;
SELECT * INTO sum_rec
FROM emp_sum
WHERE deptno = emp_rec.deptno ;
DBMS_OUTPUT.PUT_LINE ( 'SUM Salary : ' || sum_rec.sum_sal ) ;
END ;
/
Procedure created.
SQL> SET SERVEROUTPUT ON -- DBMS.OUTPUT은 기본값이 OFF이므로 켜야한다.
SQL> EXECUTE delete_emp (7788)
SUM Salary : 7875
PL/SQL procedure successfully completed.
SQL> ROLLBACK ;
'ORACLE > PL/SQL' 카테고리의 다른 글
| 6장 . 조합데이터 유형 (0) | 2014.02.16 |
|---|---|
| 5장. 제어 구조 작성 (0) | 2014.02.16 |
| 4장 PL/SQL 프로그램에서 SQL 문과 상호 작용 (0) | 2014.02.16 |
| 3장 실행문 작성 (0) | 2014.02.16 |
| 2장 PL/SQL 변수 선언 (0) | 2014.02.16 |
RECENT COMMENT