:: 작업장

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 사용

DECLARE

  emp_rec emp%ROWTYPE ; --테이블 emp의 이름 및 유형을 가져온다.

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE empno = 7788 ;

END ;

/

PL/SQL procedure successfully completed.

DECLARE

  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.

#추가해야함.

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

DECLARE

  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.

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

 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.

변수

 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)

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 = 1234;

END ;

/

A..조건에 맞는 1234 행이 없으므로 ERROR발생한다.

DECLARE 

 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>

SET SERVEROUTPUT ON

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

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 ;