PL/SQL 에서 변수를 사용하는 이유
* 변수는 임시 저장 영역이다.
* 저장된 값을 조작하기 위해
* 저장된 값을 반복해서 재사용할 수 있다.
PL/SQL 에서 사용될 변수 규칙
* 반드시 문자로 시작
* 문자나 숫자, 특수문자를 포함할 수 있다.
* 변수명은 30 BYTES 이하여야 한다
* 예약어를 포함하면 안된다.
변수 처리 순서
* 선언부에서 선언되고 원한다면 특정 값으로 초기화도 가능하다.
일반적으로 PL/SQL 에서는 선언부에서 사용될 변수를 선언한다.
변수를 선언한다는 의미는
해당 변수에 들어올 값에 대한 메모리 공간을 미리 확보하고,
해당 데이터 유형을 지정하고, 참조하도록 저장 공간 이름을 지정하는 의미가 있다.
선언 시에 원한다면 특정 값으로 초기화도 할 수 있고 NOT NULL 제약 조건도 지정할 수 있다.
변수는 반드시 참조되기 전에 선언되어야만 한다.
* 실행부에서 실행되면서 값이 할당된다.
실행부에서 해당 변수에 적당한 값을 할당하게 되며 PL/SQL 에서는 할당 연산자로 ':=' 사용한다.
* 서브 프로그램의 파라미터로 전달되기도 하며 출력 결과를 저장하기도 한다.
일반적으로 변수의 주기는 해당 변수가 선언된 블록 내부다.
만약 중첩으로 블록을 포함하는 블록의 경우에는 바깥쪽 블록에서 선언된 변수는 포함된 블록 내부에서 참조될 수 있다.
그리고 만약 내부 블록에 바깥쪽 블록에서 선언된 변수와 동일명의 변수가 있었다면, 이 때 변수의 참조는 해당 블록 내의 변수가 우선 참조된다.
그리고 중첩 블록일 경우 내부 블록의 변수는 바깥쪽 블록에서 참조할 수 없다.
2. 주요 변수의 종류
변수 - 비 PL/SQL 변수 - BIND 변수
ㄴ PL/SQL 변수 - 단순 변수 - 스칼라 변수
ㄴ참조 변수 - %TYPE 변수
ㄴ%ROWTYPE 변수
ㄴ LOB
ㄴ 복합 변수 - RECORD TYPE
ㄴTABLE TYPE
(1) 단순 변수 (SCALAR 변수와 Reference 변수)
Scalar 변수란 단일 값을 가지는 변수의 데이터 형을 직접 지정해주는 변수를 말한다.
반면 Reference 변수란 변수의 데이터 형을 다른 컬럼에서 참조 후 지정하는 방식을 말한다.
1) SCALAR 변수
문법
Identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr] ;
Identifier
변수의 이름으로 다른 변수와 구별되는 식별자 역할을 하며 블록 내에서는 유일해야 한다.
CONSTANT
읽기전용 변수, 상수로 선언하기 위한 키워드로 기본적으로 초기값이 반드시 지정되어야 한다.
예: V_RATE CONSTANT NUMBER := 0.2 ;
Datatype
오라클에서 허용하는 데이터 타입은 모두 지원한다.
%TYPE 을 사용하여 테이블 내의 컬럼과 동일한 데이터 형을 선언할 수도 있고,
%ROWTYPE 을 사용하여 테이블의 레코드 구조와 동일 형태의 레코드를 선언할 수도 있다.
NOT NULL
이 변수는 항상 값을 가지도록 제약을 주는 키워드로 이 값을 줄 경우 초기값이 반드시 지정되어야 하며 없을 경우 생략이 가능하다.
예: V_NAME VARCHAR2(14) NOT NULL := '이순신' ;
:= 또는 DEFAULT
변수에 기본값을 부여하기 위한 키워드
예: V_DATE DATE DEFAULT SYSDATE ;
expr
변수에 부여할 기본값을 의미하며 단순한 값에서부터 다른 변수, 수식, 함수가 올 수 있다.
기타 변수
기타 변수는 PL/SQL 블록이 실행되는 환경 (예를 들면, Precomplier, iSQLPlus, ...) 에서 선언한 변수로서 실행 환경과 블록 내부에서 참조된다.
주요 스칼라 변수 선언 예
Vno number(5,3) ← 숫자를 저장하는 변수로 총 5자리이며 소수점 이하 3자리를 의미
Vname varchar2(10) ← 문자를 저장하는 변수로 총 10바이트의 길이를 저장
Vday date ← 날짜를 저장하는 변수
참고: 주요 SCALAR 변수의 데이터 타입
CHAR[(최대길이)]
이 타입은 고정 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장한다. 기본값은 1
VARCHAR2(최대길이)
이 타입은 가변 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장한다. 기본값은 없다.
NUMBER[(전체 자릿수, 소수점 이하 자릿수)]
이 타입은 전체 자릿수와 소수점 이하 자릿수를 가진 숫자이다.
전체 자릿수의 범위는 1~38, 소수점 이하 자릿수의 범위는 -84~127이다.
BINARY_INTEGER
PLS_INTEGER
BOOLEAN
BINARY_FLOAT
BANARY_DOOUBLE
DATA
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
2) Reference 변수 (참조 변수)
SCALAR 변수는 데이터 타입이 정확히 지정되지만 이 타입의 변수는 저장되어야 정확한 데이터 형태를 모를 경우 해당 데이터가 들어있는 컬럼의 정보를 참조하게 설정하는 선언방법이다.
Vno emp.empno%TYPE ← emp 테이블의 empno 와 동일한 데이터형으로 선언함
Vname emp.ename%TYPE ← emp 테이블의 ename 과 동일한 데이터형으로 선언함
Vrow emp%ROWTYPE ← emp 테이블의 여러 컬럼을 한꺼번에 저장할 변수로 선언함
위 Reference 변수에서 ROWTYPE 변수가 있는데 이 변수는 하나의 테이블에 여러 컬럼의 값을 한꺼번에 저장할 수 있는 변수를 의미한다.
예를 들어, tno number, tname varchar(10), tday date 로 이루어진 test 테이블에 있을 경우 Test%ROWTYPE 로 선언되면 하나의 변수에
위의 세 가지
예제1. TYPE 변수를 사용하여 데이터 조회하기
CREATE TABLE emp5
AS
SELECT empno, ename, sal
FROM emp ;
SET SERVEROUTPUT ON ;
DECLARE
v_no emp5.empno%TYPE ;
v_name emp5.ename%TYPE ;
v_sal emp5.sal%TYPE ;
BEGIN
SELECT empno, ename, sal
INTO v_no, v_name, v_sal
FROM emp5
WHERE empno = 7900 ;
DBMS_OUTPUT.PUT_LINE(v_no||' '||v_name||' '||v_sal) ;
END ;
/
예제2. ROWTYPE 변수를 활용하여 데이터 출력하기
DECLARE
v_row emp5%ROWTYPE ;
BEGIN
SELECT *
INTO v_row
FROM emp5
WHERE empno = 7900 ;
DBMS_OUTPUT.PUT_LINE(v_row.empno||' '||v_row.ename||' '||v_row.sal) ;
END ;
/
예제3. ROWTYPE 변수를 활용한 데이터 입력
CREATE TABLE row_test
(
no NUMBER,
name VARCHAR2(10),
hdate DATE
) ;
CREATE TABLE row_test2
AS SELECT * FROM row_test ;
INSERT INTO row_test
VALUES (1, 'AAA', SYSDATE) ;
INSERT INTO row_test
VALUES (2, 'BBB', SYSDATE) ;
INSERT INTO row_test
VALUES (3, 'CCC', SYSDATE) ;
COMMIT ;
DECLARE
v_record row_test%ROWTYPE ;
BEGIN
SELECT *
INTO v_record
FROM row_test
WHERE no = 1 ;
INSERT INTO row_test2
VALUES v_record ;
END ;
/
SELECT *
FROM row_test2 ;
예제4. ROWTYPE 변수를 활용한 데이터 변경
DECLARE
v_record row_test%ROWTYPE ;
BEGIN
SELECT *
INTO v_record
FROM row_test
WHERE no = 1 ;
v_record.name := 'DDD' ;
UPDATE row_test2
SET row = v_record
WHERE no = 1 ;
END ;
/
SELECT *
FROM row_test2 ;
3) 변수 사용 예제 및 설명
예제1. %TYPE 변수를 사용하여 emp, dept 테이블을 조인하여 empno = 7900 인 사람의 정보를 4개의 변수에 넣은 후 empno, ename, deptno, dname 을 출력하세요.
SET SERVEROUTPUT ON ;
DECLARE
v_empno emp.empno%TYPE ;
v_ename emp.ename%TYPE ;
v_deptno dept.deptno%TYPE ;
v_dname dept.dname%TYPE ;
BEGIN
SELECT e.empno, e.ename, d.deptno, d.dname
INTO v_empno, v_ename, v_deptno, v_dname
FROM emp e, dept d
WHERE e.empno = 7900
AND e.deptno = d.deptno ;
DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename||' '||v_deptno||' '||v_dname) ;
END ;
/
예제2. 사용자로부터 2개의 숫자를 입력받아서 합을 구하세요.
SET VERIFY OFF
SET SERVEROUTPUT ON ;
DECLARE
v_no1 NUMBER := &no1 ;
v_no2 NUMBER := &no2 ;
v_sum NUMBER ;
BEGIN
v_sum := v_no1 + v_no2 ;
DBMS_OUTPUT.PUT_LINE(v_no1||' + '||v_no2||' = '||v_sum) ;
END ;
/
예제3.
(2) 복합 변수 (조합 변수)
복합 변수는 변수 하나 안에 여러 가지 다른 유형의 데이터를 포함할 수 있다.
Record Type 변수와 Table Type (컬렉션 타입) 변수로 나눌 수 있다.
레코드 타입 컬렉션 타입
profno name birthday integer name
number varchar2 date 1 varchar2(10)
... ... ... 2 ...
주로 동일한 데이터타입의 여러 건의 데이터를 저장하고 싶을 경우 컬렉션 타입 (Table 타입) 을 많이 사용하고
다른 유형의 데이터 타입을 사용할 경우 레코드 타입의 변수를 많이 사용한다.
1) PL/SQL RECORD Type 변수
a. TYPE type_name IS RECORD
( field_declaration[, field_declaration]... ) ;
b. Identifier type_name
a. 정의 부분
type_name 은 RECORD 유형의 이름으로 일반적인 프로그래밍 언어에서 사용되는 구조체(C언어의 STRUCTURE)와 비슷한 유형이다.
여러가지 유형의 변수가 하나의 레코드(구조체) 단위로 처리되며, 레코드 내의 변수(필드)를 참조할 경우에는 type_name.field_name 과 같은 방식으로 사용된다.
(참고로 테이블 내의 행 구조와 동일하게 레코드 변수를 선언하고자 할 경우에는 %ROWTYPE 키워드를 사용한다.)
Field_declaration 은 일반 변수의 선언과 동일한 문법 형태를 사용한다.
b. 선언 부분
기본적으로 복합형의 데이터는 우선 정의하고 해당 정의를 통해 실제 복합 변수를 선언하는 단계로 구성된다.
즉, 정의 부분에서 원하는 형태의 새로운 데이터 형을 생성한 후 선언 부분에서 별도로 선언해야만 사용이 가능하며 이 선언 부분에서 실제 복합 변수에 대한 기억공간이 확보되는 시점인 것이다.
(정의 부분에서는 메모리에 공간이 확보되지는 않으며 단지 복합 데이터 형에 대한 정의만이 이루어지는 부분이다.)
사용 예1. Record Type 변수를 활용하여 부서번호가 30번인 부서의 부서번호와 부서명과 지역명을 Record Type 변수에 저장한 후 출력하세요.
Record Type 데이터 타입명은 dept_record_type
SET SERVEROUTPUT ON
DECLARE
TYPE dept_record_type IS RECORD
(
deptno dept.deptno%TYPE,
dname dept.dname%TYPE,
loc dept.loc%TYPE
) ;
v_dept dept_record_type ;
BEGIN
SELECT deptno, dname, loc
INTO v_dept
FROM dept
WHERE deptno = 30 ;
DBMS_OUTPUT.PUT_LINE('부서번호 부서명 위치') ;
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc) ;
END ;
/
Record Type 변수와 %ROWTYPE 변수가 아주 비슷하게 보이지만 차이점이 있다.
* PL/SQL Record Type 은 유저가 필요한 컬럼들을 마음대로 정의할 수 있지만,
%ROWTYPE 은 해당 테이블의 모든 레코드를 전부 포함한다. 즉, 어떤 컬럼만 빼고 선언할 수 없다는 의미다.
* PL/SQL Record Type 변수는 변수 선언 중에 필드와 데이터 유형을 지정할 수 있지만
%ROWTYPE 을 사용할 경우에는 필드를 지정할 수 없다.
* 유저가 정의한 레코드는 정적이지만, %ROWTYPE 레코드는 동적이며 테이블 구조를 기반으로 한다. 테이블 구조가 변경되면 레코드 구조도 해당 변경사항을 적용한다. 즉, PL/SQL Record Type 변수는 한번 선언되면 테이블 구조가 변경된다 하더라도 변수 구조는 변하지 않는다. 그래서 정적이라고 표현한다.
그러나 %ROWTYPE 변수는 선언 후 테이블 구조가 변경되면 자동(동적)으로 변경 사항이 반영된다.
사용예2. emp2 테이블을 사용하여 사용자로부터 사원번호를 입력받은 후 사원번호, 이름, 직급, 생일, 연락처, 급여를 출력하세요.
직급이 없는 사원은 '사원' 으로 표시
DECLARE
TYPE e2_rec_type IS RECORD
(
vempno emp2.empno%TYPE,
vname emp2.name%TYPE,
vposition emp2.position%TYPE,
vbirth emp2.birthday%TYPE,
vtel emp2.tel%TYPE,
vpay emp2.pay%TYPE
) ;
v_e2_record e2_rec_type ;
v_empno emp2.empno%TYPE := '&empno' ;
BEGIN
SELECT empno, name, NVL(position, '사원'), birthday, tel, pay
INTO v_e2_record
FROM emp2
WHERE empno = v_empno ;
DBMS_OUTPUT.PUT_LINE('사원번호: '||v_e2_record.vempno) ;
DBMS_OUTPUT.PUT_LINE('사원명: '||v_e2_record.vname) ;
DBMS_OUTPUT.PUT_LINE('직급: '||v_e2_record.vposition) ;
DBMS_OUTPUT.PUT_LINE('생일: '||v_e2_record.vbirth) ;
DBMS_OUTPUT.PUT_LINE('연락처: '||v_e2_record.vtel) ;
DBMS_OUTPUT.PUT_LINE('급여: '||v_e2_record.vpay) ;
END ;
/
2) PL/SQL Table Type 변수 (컬렉션)
컬렉션 타입의 변수는 PL/SQL Table Type 이라고도 많이 부른다.
이 변수는 Record Type 과 같이 여러가지 유형의 데이터 컬럼을 가질 수 도 있다.
컬렉션은 주로 아래의 3가지 종류로 분류된다.
* 연관 배열
* 중첩 테이블
* VARRAY
연관배열
KEY NAME
1 AAA
2 BBB
3 CCC
4 DDD
연관배열은 두 개의 컬럼으로 이루어진 형태이다. KEY 부분이 Primary Key 가 되어서 인덱스로 사용하게 되며 데이터를 구분하게 된다.
KEY 컬럼의 값은 임의로 조정할 수 없다. 그래서 오라클 8i 버전에서는 연관 배열을 다른 말로 INDEX BY TABLE 이라고도 한다.
* (UNIQUE) KEY 열: 이 열에 들어가는 데이터 유형은 아래 두가지이다.
- 숫자일 경우: BINARY_INTEGER 또는 PLS_INTEGER
이 두가지 숫자 데이터 유형은 NUMBER 보다 적은 저장 영역이 필요하며 해당 데이터 유형에 대한 산술 연산은 NUMBER 산술보다 빠르다.
- 문자일 경우: VARACHAR2 또는 하위 유형 중 하나
* 값 (VALUE) 열: VALUE 열은 실제 값이 들어가는 곳으로 입력되는 데이터의 종류에 따라 스칼라 데이터 유형 또는 레코드 데이터 유형일 수 있다.
스칼라 데이터 유형의 열은 해당 하나의 값만 보유할 수 있지만, 레코드 데이터 유형의 열은 행당 여러 값을 보유할 수 있다.
연관 배열의 주요 특성
* 연관 배열은 변수 선언 당시 채워지지 않으며 키나 값을 포함하지 않으므로 선언에서 연관 배열을 초기화할 수 없다.
* 연관 배열을 채우려면 명시적 실행문이 필요하다.
* 데이터베이스 테이블의 크기와 마찬가지로 연관배열의 크기에도 제약이 없다. 새 행이 추가됨에 따라 연관 배열이 증가하도록 행 수 가 동적으로 늘어날 수 있다. 키는 순차적이 아닐 수 있으며 양수 및 음수일 수 있다.
PL/SQL TABLE(컬렉션 타입) 정의와 선언
문법
TYPE type_name IS TABLE OF
{column_type|variable%type|table.column%type} [NOT NULL]
table%ROWTYPE
[INDEX BY BINARY_INTEGER] ;
a. 정의 부분
type_name 은 PL/SQL Table 유형의 이름으로 일반적인 프로그래밍 언어에서의 배열과 비슷한 의미이다.
위 Record Type 과 다른 부분은 Record Type은 다른 유형의 데이터 타입을 사용하지만 이 Table Type 형은
동일한 유형의 데이터 (또는 데이터 구조) 들을 하나의 연속적인 메모리 공간에 확보하기 위해 사용한다는 점이다.
INDEX BY 절은 그 배열 내의 요소 (element)에 접근하기 위한 첨자(위치) 값으로 사용되며,
범위는 BINARY_INTEGER 의 범위 (-2,147,483,647 ~ 2,147,483,647 사이의 정수) 에 속한다.
b. 선언 부분
기본적으로 복합형의 데이터는 우선 정의하고 해당 정의를 통해 실제 복합 변수를 선언하는 단계를 구성된다.
위의 Record Type 형태와 사용 방법은 동일하며 이 선언 부분에서 실제 복합 변수에 대한 기억공간이 확보된다.
(정의 부분에서는 메모리에 공간이 확보되지는 않으며 단지 복합 데이터 형에 대한 기술이 이루어지는 부분이다.)
사용 예1. TABLE TYPE 변수를 사용하여 사원번호가 7499인 사원의 이름을 조회해서 해당 변수에 저장한 후 출력하세요.
단, TABLE TYPE 데이터 타입명은 tbl_emp_name 으로 하세요.
DECLARE
t_name VARCHAR2(20) ;
TYPE tbl_emp_name IS TABLE OF
emp.ename%TYPE
INDEX BY BINARY_INTEGER ;
v_name tbl_emp_name ;
BEGIN
SELECT ename
INTO t_name
FROM emp
WHERE empno = 7499 ;
v_name(0) := t_name ;
DBMS_OUTPUT.PUT_LINE(v_name(0)) ;
END ;
/
위의 예는 Table Type 의 변수를 선언해서 데이터를 입력한 후 출력하는 것이다.
4, 5, 6 번 라인에서 tbl_emp_name 이라는 이름으로 Table Type 의 변수를 생성한 후
8번 라인에서 v_name 이라는 변수를 Table Type 으로 선언하였다.
11, 12, 13 번 라인에서 empno가 7499 번인 사원의 ename 을 가져와서 t_name 에 입력하고
15번 라인에서 t_name 에 있던 사원의 ename을
Table Type 변수인 v_name 의 1번째 칸에 할당했다. v_name(0)
참조 변수의 Record Type 변수와 Table Type 변수는 여러 건의 데이터를 저장하기 위해 사용한다.
사용 예2. FOR 문을 사용하여 변수에 여러건의 데이터를 입력하는 방법
DECLARE
TYPE e_table_type IS TABLE OF
emp.ename%TYPE
INDEX BY BINARY_INTEGER ;
tab_type e_table_type ;
a BINARY_INTEGER := 0 ;
BEGIN
FOR i IN (SELECT ename FROM emp) LOOP
a := a+1 ;
tab_type(a) := i.ename ;
END LOOP ;
FOR j IN 1..a LOOP
DBMS_OUTPUT.PUT_LINE(tab_type(j)) ;
END LOOP ;
END ;
/
3. 비 PL/SQL 변수 (바인드 변수)
바인드 변수는 호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 한다.
VARIABLE 키워드를 사용하여 생성되며 SQL 문과 PL/SQL 블록에서 사용된다.
또한 PL/SQL 블록이 실행된 후에도 액세스 할 수 있다.
앞에 콜론을 사용하여 참조하며 PRINT 명령을 사용하여 값을 출력할 수 있다.
단, 치환변수와는 구분해야 한다.
치환 변수는 사용자에게 어떤 값을 입력 받아서 치환하며 접두 문자로 &(앤퍼샌트) 를 사용한다.
VARIABLE v_bind NUMBER ;
BEGIN
SELECT (pay*12) + NVL(bonus, 0) INTO :v_bind
FROM professor
WHERE profno = 1001 ;
END ;
/
PRINT v_bind ← 바인드 변수에 담긴 값을 출력한다.
PRINT 문장을 매번 실행하기 번거로우면 SET AUTOPRINT ON 이라고 설정한 후 사용하면 자동으로 바인드 변수 값을 출력해서 보여준다.
SET AUTOPRINT ON ;
BEGIN
SELECT (pay*12) + NVL(bonus, 0) INTO :v_bind
FROM professor
WHERE profno = 1001 ;
END ;
/
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저