[Oracle/기본]16. PL-SQL 변수 선언

 

 

● PL/SQL

프로그램을 논리적인 블록으로 나누게 하는 구조화된 블록 언어이다.

 

DECLARE (선택적)

- Variables

- Cursors

- User-defined exceptions

 

BEGIN (필수적)

- SQL statements

- PL/SQL statements

 

EXCEPTION (선택적)

- Actions to perform when errors occur

 

END; (필수적)

 

Ex. DECLARE

       v_variable VARCHAR2(5);

    BEGIN

       SELECT column_name

         INTO v_variable

        FROM table_name;

    EXCEPTION

        WHEN exception_name THEN

        ...

    END;

 

● 블록 유형

 

- 익명블록 : 이름이 없는 블록

  ☞ Anonymous

- 서브프로그램 : 매개변수를 취할 수 있다.

                      호출할 수 있는 이름이 있다.

  ☞ Procedure, Function

 

1. Anonymous

이름이 없는 블록

 

[DECLARE]

 

BEGIN

   --statements

 

[EXCEPTION]

 

END;

 

2. Procedure

어떤 작업을 수행하기 위해 사용한다.

리턴값이 없다.

 

PROCEDURE name

IS

 

BEGIN

   --statements

 

[EXCEPTION]

 

END;

 

3. Function

값을 계산하기 위해 함수를 사용한다.

리턴값이 있다.

 

FUNCTION name

RETURN datatype

IS

BEGIN

   --statements

   RETURN value;

[EXCEPTION]

 

END;

 

● 3가지 매개변수 모드

 

1. IN(디폴트) : 호출되는 서브프로그램으로 값을 전달하기 위해 사용한다.

2. OUT : 서브프로그램의 호출자에게 값을 반환하기 위해 OUT 매개변수를 사용한다.

3. INOUT : 호출되는 서브프로그램에 초기값을 전달하고 호출자에게 갱신된 값을 반환하기 위해 INOUT 매개변수를 사용한다.

 

● PL/SQL의 데이터형

1. Scalar

  단일 값을 보유한다.

  주요 데이터형은 오라클 서버 테이블의 열 유형에 대응하는 것들이다.

  숫자, 문자, 날짜, 부울 4개의 범주로 분류될 수 있다.

  

  VARCHAR2

  NUMBER

  DATE

  CHAR

  LONG

  LONG RAW

  BOOLEAN : true, false, null 3가지 유형이 있다. 

  BINARY_INTEGER : 정수 기본형

  PLS_INTEGER : signed 정수에 대한 기본형. NUMBER, BINARY_INTEGER 값보다 적은 기억장치를 필요로 한다.

 

  signed 정수 : 부호 있는 정수

  unsigned 정수 : 부어 없는 정수

 

 

2. Composite (조합)

   레코드 같은 조합 데이터형은 PL/SQL블록에서 조작되고 정의되는 필드 그룹을 허용한다.

 

3. Reference (참조)

   참조 데이터형은 pointer라 불리며, 다른 프로그램 항목을 지시하는 값을 보유한다.

 

4. LOB (large objects)

   LOB 데이터형은 locator라 불리며, 라인의 밖에서 저장된 큰 객체(예를 들면 그래픽 이미지)의 위치를 지정하는

   값을  보유한다.

   4기가 바이트까지 저장 가능하다.

 

☞ CLOB(character large object) : 데이터베이스 내의 단일 바이트 문자 데이터의 대형 블록을 저장하기 위해 사용된다.

    Ex. Recipe

    BLOB(binary large object) : 데이터베이스 내의 대형 이진 객체를 저장하기 위해 사용된다.

    Ex. Photo

    BFILE(binary file) : 데이터베이스 외부의 운영 시스템 파일의 대형 이진 객체를 저장하기 위해 사용된다.

    Ex. Movie

    NCLOB(national language character large object)

  

● PL/SQL 변수선언

 

identifier [CONSTANT] datatype [NOT NULL] [:= / DEFAULT expr];

 

Ex. DECLARE

        v_hiredate DATE;

        v_deptno  NUMBER(2) NOT NULL := 10;

        v_location VARCHAR2(13) := 'Atlanta';

        c_comm   CONSTANT NUMBER := 1400; 

 

* :=의 뜻 : 지정연산자, 일반적인 언어에서 '=' 과 같다.

 

- 이름 지정 규약을 사용할 수 있다. 예를 들면, v_name은 변수, c_name은 상수 변수 등이 있다.

- 지정연산자 (:=) 또는, 같은 의미의 예약어 DEFAULT를 사용하는 표현식에 대한 변수를 초기화한다.

  초기값을 지정하지 않으면, 새 변수는 후에 그것이 지정될 때까지 디폴트로 NULL을 포함한다.

- NOT NULL 제약조건을 사용한다면, 값을 지정해야 한다.

- 상수 선언에서, 키워드 CONSTANT는 형 지정자보다 먼저 쓰여져야 한다.

- 두 변수는 다른 블록에서라면 동일 이름을 가질 수 있다.

 

● 변수 초기화와 키워드

 

변수는 블록이나 서브프로그램 안으로 들어갈 때마다 초기화 된다.

 

- 특정 값이 없는 변수에 대해 지정연산자 (:=) 를 사용한다.

  v_hiredate := TO_DATE('1999-09-15', 'YYYY-MM-DD')

 

- 변수 초기화를 위한 지정 연산자(:=)대신에 DEFAULT 키워드를 사용할 수 있다.

  g_mgr NUMBER(4) DEFAULT 7839;

 

- NOT NULL

  v_location VARCHAR2(13) NOT NULL := 'CHICAGO';

 

Ex.  v_job        VARCHAR2(9);

     v_count      BINARY_INTEGER := 0;

     v_total_sal   NUMBER(9,2) := 0;

     v_orderdate DATE := SYSDATE + 7;

     c_tax_rate    CONSTANT NUMBER(3,2) := 8.25;

     v_valid        BOOLEAN NOT NULL := TRUE;

 

● %TYPE 속성과 변수 선언

 

⊙ %TYPE 데이터형은 기술한 데이터베이스 테이블의 컬럼 데이터 타입을 모를 경우 사용할 수 있고,

⊙ 또. 코딩이후 데이터베이스 컬럼의 데이터 타입이 변경될 경우 다시 수정할 필요가 없습니다.

⊙ 이미 선언된 다른 변수나 데이터베이스 컬럼의 데이터 타입을 이용하여 선언합니다.

⊙  데이터 베이스 테이블과 컬럼 그리고 이미 선언한 변수명이 %TYPE앞에 올수 있습니다.

%TYPE 속성을 이용하여 얻을 수 있는 장점

   - 기술한 DB column definition을 정확히 알지 못하는 경우에 사용할 수 있다.

   - 기술한 DB column definition이 변경 되어도 다시 PL/SQL을 고칠 필요가 없다.

예제

v_empno  emp.empno%TYPE := 7900 ;

v_ename emp.ename%TYPE;

 예제 프로시져..

SQL>CREATE OR REPLACE PROCEDURE Emp_Info

      /* IN Parameter */

      ( p_empno IN emp.empno%TYPE )

        IS

        /* %TYPE 데이터형 변수 선언 */

        v_empno emp.empno%TYPE;

        v_ename emp.ename%TYPE;

        v_sal   emp.sal%TYPE;

        BEGIN

        DBMS_OUTPUT.ENABLE;

        /* %TYPE 데이터형 변수 사용 */

        SELECT empno, ename, sal

        INTO v_empno, v_ename, v_sal 

        FROM emp

        WHERE empno = p_empno ;

        /* 결과값 출력 */

        DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_empno );

        DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_ename );

        DBMS_OUTPUT.PUT_LINE( '사원급여 : ' || v_sal );

        END;

       /

프로시져가 생성되었습니다.

SQL>SET SERVEROUTPUT ON;     --  DBMS_OUTPUT 결과값을 화면에 출력 하기위해

실행 결과

SQL> EXECUTE Emp_Info(7369);

사원번호 : 7369

사원이름 : SMITH

사원급여 : 880

PL/SQL 처리가 정상적으로 완료되었습니다.

 

[출처] http://psh85a.tistory.com/entry/oracle-TYPE%EA%B3%BC-ROWTYPE

 

● 부울 변수 선언

 

Ex. v_sal1 := 50000 이고, v_sal2 := 60000 일때,

    

    v_comm_sal BOOLEAN := (v_sal1 < v_sal2); 는

 

    v_comm_sal 변수를 TRUE로 초기화 시킨다.

 

   

● 바인드 변수

 

호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 함. VARIABLE 키워드로 생성.

화면에 출력하여 확인하기위해 사용.

 

Ex. "My PL/SQL Block Works" 문구를 화면에 출력하기 위해 익명의 블록을 생성한다.

 

VARIABLE g_message VARCHAR2(30)

BEGIN

:g_message := 'My PL/SQL Block Works';

END;

/

PRINT g_message

 

Ex. 교수번호가 1001인 교수의 연봉을 계산하여 바인드 변수에 할당 한 후 출력

 

VARIABLE v_bind NUMBER

BEGIN

SELECT (pay*12) + NVL(bonus, 0)

   INTO :v_bind

 WHERE profno = 1001;

END;

/

PRINT v_bind;

 

Ex.

 

VARIABLE G_CHAR VARCHAR2(30)
VARIABLE G_NUM NUMBER

-- VARCHAR2는 길이가 있는데, NUMBER는 없음.
DECLARE
    V_CHAR VARCHAR2(30);
    V_NUM NUMBER(11,2);
BEGIN
    V_CHAR := '42 IS THE ANSWER';
    v_num := TO_NUMBER(SUBSTR(V_CHAR,1,2));
    :G_CHAR := V_CHAR;
    :G_NUM := V_NUM;
END;
/
PRINT G_CHAR
PRINT G_NUM

 

● VARIABLE 명령 사용시 유의사항 (호스트변수 사용시 유의사항)

 

- NUMBER 는 크기를 지정하지 않는다.

- CHAR 또는 VARCHAR2인 호스트 변수는 값을 괄호로 묶지 않으면 기본값으로 한 자리만 지정된다.