2장 PL/SQL 변수 선언

변수

 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