4장 PL/SQL 프로그램에서 SQL 문과 상호 작용

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에서 조건에 맞는게 둘 이상 나올 경우 중간에 하나씩 하나씩 끊어갈 수 있다.