3장 실행문 작성

 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.