[PL/SQL] 2. 변수

2017. 5. 7. 22:24

PL/SQL 변수 종류

1. Simple Variable - 1건의 데이터만 저장하는 변수

Scalar 변수
Reference 변수
- %type 변수
- %rowtype 변수

(1) Scalar 변수

확정된 형태의 하나의 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수. (숫자, 문자, 날짜등)

v_no    NUMBER(5,3)  -- 총5자리, 소수점 이하 3자리를 의미하는 숫자형 데이터를 저장하는 변수
v_name  VARCAHR2(10)  -- 총10byte길이의 문자형 데이터를 저장할수 있는 변수
v_day   DATE          -- 9999년 12월 31일까지의 날짜를 저장할수 있는 날짜형 변수

V_RATE CONSTANT NUMBER := 1.15 ;      -- 상수로 선언하기 위한 키워드로 기본적으로 초기값이 반드시 지정되어야 함
V_NAME VARCHAR2(14) NOT NULL := ‘이순신’;
V_DATE DATE DEFAULT SYSDATE;          -- 기본값 할당

Scalar 변수의 데이터 타입

CHAR [(최대길이)]
이 타입은 고정 길이 문자 형으로 저장하며 최대 32,767 바이트값을 저장.
기본 값은 1로 설정되어 있다.
VARCHAR2 (최대길이)
이 타입은 가변 길이 문자 형으로 저장하며 최대 32,767 바이트 값을 저장.
기본 값은 없음.
NUMBER [(전체자리수, 소수점이하 자리수)]
전체 자리수의 범위는 1부터 38까지, 소수점 이하 자리수의 범위는 -84 부터 127 까지.
BINARY_INTEGER
이 타입은 -2,147,483,647 - 2,147,483,647 사이의 정수를 저장하는 타입.
PLS_INTEGER
이 타입은 -2,147,483,647 - 2,147,483,647 사이의 부호 있는 정수에 대한 기본 유형.
PLS_INTEGER 값은 NUMBER 값보다 저장 공간이 적게 필요하고 연산 속도가 더 빠름.
Oracle Database 11g에서는 PLS_INTEGER 및 BINARY_INTEGER 데이터 유형은 동일.
PLS_INTEGER 및 BINARY_INTEGER 값의 산술 연산은 NUMBER 값보다 빠름.
BOOLEAN
이 타입은 논리적 계산에 사용 가능한 세 가지 값(TRUE, FALSE, NULL)중 하나를 저장.
BINARY_FLOAT
이 타입은 IEEE 754 형식의 부동 소수점 수.
값을 저장하기 위해 5바이트가 필요.
BINARY_DOUBLE
이 타입은 IEEE 754 형식의 부동 소수점 수.
값을 저장하기 위해 9바이트가 필요.

(2) Reference 변수

데이터 형을 직접 지정하지 않고 원본 데이터의 데이터형을 참조하게끔 설정해주는 변수.

v_no        emp.empno%TYPE     -- emp테이블의 empno와 동일한 데이터형으로 선언함
v_rowtype   emp%ROWTYPE        -- emp테이블의 여러컬럼을 한꺼번에 저장할 변수로 선언함

%ROWTYPE 참조변수

예를들어 tno NUMBER , tname VARCHAR2(10) ,tday DATE 로 이루어진 test 테이블이 있을 경우 test%ROWTYPE 으로 선언되면 하나의 변수에 위의 세가지(NUMBER, VARCHAR2(10) ,DATE )를 모두 한번에 저장 할 수 있는 변수로 선언이 된다는 뜻.

%TYPE 형태의 변수로 만들어야 할 경우 변수를 3 개를 만들어야 하지만 %ROWTYPE 으로 만들 경우 1개의 변수만 있어도 됨.

참조 변수를 쓸 경우에는 원본 데이터 타입이 변경이 될 경우 참조 변수의 데이터 타입도 함께 변경이 되기 때문에 사용자가 수동으로 변경을 해 주지 않아도 된다는 장점이 있다.

하지만 데이터 타입이 변경이 될 경우 원본 테이블의 데이터 형이 변경되거나 컬럼이 변경되면(참조하는 컬럼 말고 다른 컬럼이 변경될 경우에도) 해당 컬럼에 엑세스하는 프로시저는 사용불능(Invalid) 상태가 될 수 있으므로 조심해야한다.

ex1) dept 테이블에서 deptno가 10 번인 부서의 deptno, dname , loc 를 출력하세요

SCOTT>SET SERVEROUTPUT ON;
SCOTT>DECLARE
2 vno   dept.deptno%TYPE ;
3 vname dept.dname%TYPE ;
4 vloc  dept.loc%TYPE ;
5
6 BEGIN
7    SELECT deptno , dname , loc INTO vno , vname , vloc
9    FROM dept
10   WHERE deptno=10 ;
11
12   DBMS_OUTPUT.PUT_LINE(vno||' / '||vname||' / '||vloc);
13 END;
14 /

SCOTT>DECLARE
2 v_row    dept%ROWTYPE ;
3
4 BEGIN
5     SELECT * INTO v_row
6     FROM dept
7     WHERE deptno=10 ;
8
9   DBMS_OUTPUT.PUT_LINE(v_row.deptno||' / '||v_row.dname||' / '||v_row.loc);
10 END ;
11 /

%ROWTYPE 의 경우는 원본 테이블의 모든 컬럼의 데이터를 다 조회할 경우에만 사용 가능함. 만약 원본 테이블의 컬럼 중 일부만 가져와야 한다면 %ROWTYPE 변수를 사용할 수 없고 각 컬럼을 %TYPE 변수로 선언 한 후 데이터를 가져와야 한다.

ex2) %ROWTYPE 변수를 활용한 데이터의 입력 및 변경

SCOTT>select * from t_rowtype1;
 
        NO NAME                 HIREDATE
---------- -------------------- ------------
        10 APPLE                07-MAY-17
        20 BANANA               07-MAY-17
        30 BERRY                07-MAY-17
 
SCOTT>select * from t_rowtype2;
 
no rows selected
 
SCOTT>
SCOTT>
SCOTT>
SCOTT>
SCOTT>DECLARE
  2   v_record   t_rowtype1%ROWTYPE;
  3  BEGIN
  4     SELECT * INTO v_record
  5     FROM t_rowtype1
  6     WHERE no = 10;
  7
  8     INSERT INTO t_rowtype2
  9     VALUES v_record;
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SCOTT>SELECT * FROM t_rowtype2;
 
        NO NAME                 HIREDATE
---------- -------------------- ------------
        10 APPLE                07-MAY-17
 
SCOTT>DECLARE
2 v_record t_rowtype1%ROWTYPE;
3 BEGIN
4     SELECT * INTO v_record
5     FROM t_rowtype1
6     WHERE no=10 ;
7
8     v_record.name := 'ORANGE' ;
9
10     UPDATE t_rowtype2
11     SET row=v_record
12     WHERE no=10;
13 END ;
14 /
 
PL/SQL procedure successfully completed.
 
SCOTT>SELECT * FROM t_rowtype2 ;
 
    NO NAME     HIREDATE
------ ------ ----------
    10 ORANGE 06-AUG-13

2. Complex Variable - 여러 건의 데이터를 저장할 수 있는 변수

Record Type : 여러가지 데이터타입의 데이터를 여러 건을 저장할 때
Table Type(컬렉션 타입) : 동일한 데이터타입의 데이터를 여러건 저장할 때

하나의 변수를 만들 때 사용자가 원하는 여러 가지 다른 유형의 데이터를 포함해서 사용자가 원하는 새로운 형태로 만들 수 있다.

마치 뷰 처럼 사용자가 원하는 컬럼을 모아서 하나의 테이블처럼 만들어서 여러 가지 데이터를 한꺼번에 관리 할 수 있다.

(1) Record Type 변수

사용자가 원하는 컬럼들을 직접 지정해 만들어 프로그래머 정의 기반 Record Type 이라고 함.

예를 들어 반찬으로 김치, 멸치, 콩나물을 담아 도시락을 싼다고 하자.
이럴 경우 김치를 담기 위해 김치통을 준비하고 멸치는 멸치통에, 콩나물은 콩나물통에 따로 담아 가는 것이 %TYPE 변수를 활용하는 방법이다.
반찬이 많을 경우 통이 많아져서 불편하므로 하나의 통에 칸막이를 쳐서 반찬들을 한꺼번에 다 담아 간편하게 싸는 방법이 이 Record Type 복합변수라고 생각하면 된다.

문법

정의부분
TYPE type_name IS RECORD
( field_declaration[, field_declaration]...);
선언부분
Identifier type_name

예시1

SCOTT>DECLARE
  2    TYPE dept_record IS RECORD           -- 정의
  3    (  deptno    dept.deptno%TYPE,
  4       dname     dept.dname%TYPE   );    
  5
  6     v_dept  dept_record;                -- 선언
  7
  8  BEGIN
  9     SELECT deptno, dname INTO v_dept
 10     FROM dept
 11     WHERE deptno = 10;
 12
 13     DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' / '||v_dept.dname);
 14  END;
 15  /
 
10 / ACCOUNTING
 
PL/SQL procedure successfully completed.

위의 예시에서 RECORD TYPE (dept_record) 변수의 형태

DEPTNO	DNAME

두개의 컬럼이 하나의 변수처럼 사용됨.

예시2
사용자에게 교수 번호를 입력 받은 후 교수번호, 교수이름, 직급, 입사일, 급여를 출력하시오. (단 직급이 없는 사원은 직급을 사원으로 표시해서 출력)

SCOTT>DECLARE
  2    TYPE prof_info IS RECORD         
  3    (  vprofno       professor.profno%TYPE,
  4       vname         professor.name%TYPE,    
  5       vposition     professor.position%TYPE,
  6       vhiredate     professor.hiredate%TYPE,    
  7       vpay          professor.pay%TYPE    );
  8  
  9     v_prof    prof_info;
 10  
 11     v_profno  professor.profno%TYPE := &PROFNO;
 12 BEGIN
 13      SELECT profno, name, position, hiredate, pay INTO v_prof
 14      FROM professor
 15      WHERE profno = v_profno;
 16            DBMS_OUTPUT.PUT_LINE('PROFNO : '||v_prof.vprofno);
 17            DBMS_OUTPUT.PUT_LINE('NAME : '||v_prof.vname);
 18            DBMS_OUTPUT.PUT_LINE('POSITION : '||v_prof.vposition);
 19            DBMS_OUTPUT.PUT_LINE('HIREDATE : '||v_prof.vhiredate);
 20            DBMS_OUTPUT.PUT_LINE('PAY : '||v_prof.vpay);
 21 END;
 22 /
 
 Enter value for profno: 1001
 
 PROFNO : 1001
 NAME : Audie Murphy
 POSITION : a full professor
 HIREDATE : 23-JUN-80
 PAY : 550
 
 PL/SQL procedure successfully completed.

(2) Table Type 변수

대부분의 경우 1개의 컬럼만 저장을 하지만 Row Type을 사용하여 Record Type 처럼 여러 가지 유형의 데이터 컬럼을 가질 수도 있다. 만약 테이블의 모든 컬럼을 다 가지고 와서 변수에 담을 경우 %ROWTYPE 을 사용하여 변수를 생성하면 편하게 사용할 수 있다.

연관 배열
중첩 테이블
VARRAY

위 3가지 종류중 연관배열이 가장 많이 사용됨.

연관배열

KEY	VALUE

두 개의 컬럼으로 이루어진 형태로 Key 컬럼은 가상의 Primary Key가 되어 인덱스로 사용되고 데이터를 구분하게 된다.
Key컬럼의 값은 사람이 임의로 조정 할 수 없다.(pl/sql이 자동으로 생성함)

Key 컬럼에 들어가는 데이터 유형은 아래 두가지이다.

숫자 : BINARY_INTEGER 또는 PLS_INTEGER
이 두가지 숫자 데이터 유형은 NUMBER 보다 적은 저장 영역이 필요하며 해당 데이터 유형에 대한 산술 연산은 NUMBER로 하는 산술 연산보다 빠르다. (시작은 0번부터)
문자 : VARCHAR2 또는 하위 유형 중 하나가 올 수 있다.

VALUE 컬럼은 실제 값이 들어가는 곳으로 입력되는 데이터의 종류에 따라 스칼라 데이터유형 또는 레코드 데이터유형 일 수 있다.

문법

정의부분
TYPE type_name IS TABLE OF
{ column_type | variable%type | table.column%type } [NOT NULL] | table%ROWTYPE
[INDEX BY BINARY_INTEGER];
선언부분
Identifier type_name

동일한 유형의 데이터(또는 데이터구조)들을 하나의 연속적인 메모리 공간에 확보하기 위해 사용한다.

Index by 절은 그 배열내의 요소에 접근하기 위한 첨자(위치) 값으로 사용됨.

예시
Table Type 변수를 사용하여 교수번호가 2001 인 교수의 이름을 조회해서 Table Type 변수에 저장 한 후 출력하시오. (단 Table Type 명은 tbl_prof)

SCOTT>DECLARE
  2   v_name VARCHAR2(20);
  3   TYPE tbl_prof IS TABLE OF
  4     professor.name%TYPE
  5   INDEX BY BINARY_INTEGER;
  6
  7   profname  tbl_prof;
  8
  9  BEGIN
 10      SELECT name INTO v_name
 11      FROM professor
 12      WHERE profno = 2001;
 13
 14     profname(0) := v_name||'_0';
 15     profname(1) := v_name||'_1';
 16
 17     DBMS_OUTPUT.PUT_LINE(profname(0));
 18     DBMS_OUTPUT.PUT_LINE(profname(1));
 19  END;
 20  /
 
Winona Ryder_0
Winona Ryder_1
 
PL/SQL procedure successfully completed.

Non PL/SQL 변수

PL/SQL 안에서만 사용되는 것이 아니라 외부에서도 사용될 수 있는 변수

Bind Variable

호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 하며, VARIABLE 키워드를 사용해 생성되고 SQL문과 PL/SQL 블록에서 사용된다.

콜론(:) 을 사용해 값을 참조하며 PRINT 명령을 사용해 값을 출력한다.

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

SCOTT>VARIABLE v_bind NUMBER;      --SQL에서 변수 선언
SCOTT>BEGIN
  2    SELECT (pay*12)+NVL(bonus,0) INTO :v_bind    -- PL/SQL에서 값 입력
  3    FROM professor
  4    WHERE profno = 2001;
  5  END;
  6  /
 
PL/SQL procedure successfully completed.
 
SCOTT>PRINT v_bind;         --SQL에서 바인드 변수에 담긴 값 출력
 
    V_BIND
----------
      3000

PRINT 문장을 매번 실행하기 번거로우면 SET AUTOPRINT ON 설정 후 사용하면 자동으로 바인드 변수 값을 출력 해서 보여준다.

변수의 적용 범위

외부 블록에서 선언한 변수는 내부 블록에서도 사용할 수 있지만 내부블록에서 선언된 변수는 외부 블록에서 사용할 수 없다.

저작자표시 비영리 변경금지

'Oracle > plsql' 카테고리의 다른 글

[PL/SQL] 3. CURSOR (1259)	2017.05.14
[PL/SQL] 1. 개요 (792)	2017.05.07

justdoit