PL/SQL 에서 변수를 사용하는 이유
- 변수는 데이터의 임시 저장 영역이다.
- 저장된 값을 조작하기 위해 사용
- 저장된 값을 반복해서 재 사용할 수 있다.
PL/SQL 에서 사용될 변수의 규칙
- 반드시 문자로 시작해야만 한다.
- 문자나 숫자, 특수문자를 포함할 수 있다.
- 변수명은 30 bytes 이하여야 한다.
- 예약어를 포함하면 안된다
주요 변수들의 종류
단순 변수
-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 : 변수에 부여할 기본값을 의미하며 단순한 ㄱ밧에서부터 다른 변수, 수식, 함수가 올 수 있다.
주요 스칼라 변수 선언 예
Vno number(5,3) <- 숫자를 저장하는 변수로 총 5자리이며 소수점이하 3자리를 의미
Vname varchar2(10) <- 문자를 저장하는 변수로 총 10바이트의 길이를 저장할 수 있다
Vday date <- 날짜를 저장하는 변수
(2) 주요 Scalar 변수의 데이터 타입
- CHAR[(최대길이)] : 고정 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장. 기본 최소값=1
- VARCHAR2 (최대길이) : 가변 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장. 기본값 X
- NUMBER [(전체자리수, 소수점이하 자리수)] : 전체 자리수와 소수점 이하의 자리수를 가진 숫자
전체 자리수의 범위는 1부터 38까지, 소수점 이하 자리수 범위는 -84 부터 127 까지
- BINARY_INTEGERR : -2,147,483,647 ~ 2,147,483,647 사이의 정수를 저장하는 타입
- PLS_INTERGER : -2,147,483,647 ~ 2,147,483,647 사이의 부호 있는 정수에 대한 기본 유형
NUMBER 값보다 저장 공간이 적게 필요하고 연산 속도가 더 빠름
- BOOLEAN : 논리적 계산에 사용 가능한 세 가지 값(TRUE, FALSE, NULL) 중 하나를 저장하는 기본 유형
- BINARY_FLOAT : IEEE 754 형식의 부동 소수점 수를 나타냄. 값을 저장하기 위해 5바이트가 필요
- BINARY_DOUBLE : IEEE 754 형식의 부동 소수점 수를 나타냄. 값을 저장하기 위해 9바이트가 필요
- DATE : 날짜 및 시간에 대한 기본 유형
- TIMESTAMP : DATE 데이터 유형을 확장하고 연도, 월, 일, 시, 분, 초 및 소수로 표시되는 초 단위를 저장
구문 : TIMESTAMP[(precision)]으로 나타내고 선택적 파라미터인 precision 은 초 필드의 소수부분 자릿수를 지정
자릿수를 지정하려면 0-9 범위의 정수를 사용해야 함. 기본값은 6
Reference 변수 (참조 변수)
변수가 지정되어야 할 정확한 데이터 형태를 모를 경우 해당 데이터가 들어 있는 컬럼의 정보를 참조하게끔 설정
예 ) Vno emp.empno%TYPE <- emp 테이블의 empno와 동일한 데이터형으로 선언
Vname emp.empno%TYPE <- emp 테이블의 ename 과 동일한 데이터형으로 선언
Vrow emp%ROWTYPE <- emp 테이블의 여러 컬럼을 한꺼번에 저장할 변수로 선언
ROWTYPE 변수는 하나의 테이블에 여러 컬럼의 값을 한꺼번에 저장할 수 있는 변수를 의미
(1) TYPE 변수를 사용하여 데이터 조회
emp3 테이블에서 empno가 7900 번인 사원의 empno, ename, sal 을 조회하여 화면에 출력
-실습을 위해 emp테이블을 복사해서 emp3 테이블을 생성
SCOTT>CREATE TABLE emp3
2 AS
3 SELECT empno,ename,sal
4 FROM emp ;
SCOTT>SET SERVEROUTPUT ON ;
SCOTT>DECLARE
2 vno emp3.empno%TYPE ;
3 vname emp3.ename%TYPE ;
4 vsal emp3.sal%TYPE ;
5 BEGIN
6 SELECT empno,ename,sal
7 INTO vno,vname,vsal
8 FROM emp3
9 WHERE empno=7900 ;
10 DBMS_OUTPUT.PUT_LINE(vno||' '||vname||' '||vsal) ;
11 END ;
12 /
-> 7900 JAMES 950
(2) ROWTYPE 변수를 사용하여 데이터 출력
SCOTT>DECLARE
2 v_row emp3%ROWTYPE ;
3 BEGIN
4 SELECT * INTO v_row
5 FROM emp3
6 WHERE empno=7900 ;
7 DBMS_OUTPUT.PUT_LINE(v_row_empno||'**'||v_row.ename||'**'||v+row.sal_ ;
8 END ;
9 /
-> 7900**JAMES**950
(3) ROWTYPE 변수를 활용한 데이터의 입력
SCOTT>CREATE TABLE row_test
2 (no NUMBER,
3 name VARCHAR2(10),
4 hdate DATE) ;
SCOTT>CREATE TABLE row_test2
2 AS SELECT * FROM row_test ;
SCOTT>INSERT INTO row_test VALUES (1,'AAA',SYSDATE) ;
SCOTT>INSERT INTO row_test VALUES (2,'BBB',SYSDATE) ;
SCOTT>INSERT INTO row_test VALUES (3,'CCC',SYSDATE) ;
SCOTT>COMMIT ;
SCOTT>DECLARE
2 v_record row_test%ROWTYPE ;
3 BEGIN
4 SELECT * INTO v_record
5 FROM row_test
6 WHERE no=1 ;
7 INSERT INTO row_test2
8 VALUES v_record ;
9 END ;
10 /
SCOTT>SELECT * FROM row_test2 ;
NO NAME HDATE
--- ------ -------
1 AAA 27-MAR-12
(4) ROWTYPE 변수를 활용한 데이터의 변경
SCOTT>DECLARE
2 v_record row_test%ROWTYPE ;
3 BEGIN
4 SELECT * INTO v_record
5 FROM row_test
6 WHERE no=1 ;
7 UPDATE row_test2
8 SET row=v_record
9 WHERE no=1 ;
10 END;
11 /
SCOTT>SELECT * FROM row_test2 ;
NO NAME HDATE
--- ------ ----------
1 DDD 27-MAR-12 <- 원래 'AAA' 에서 'DDD' 로 바뀜
변수 예제 1
%TYPE 변수를 사용하여 emp, dept 테이블을 조인하여 empno=7900인 사람의 정보를 4개의 변수에 넣은 후 empno, ename, deptno, dname을 출력
SCOTT>DELCARE
2 v_empno emp.empno%TYPE ;
3 v_ename emp.ename%TYPE ;
4 v_deptno dept.deptno%TYPE ;
5 v_dname dept.dname%TYPE ;
6 BEGIN
7 SELECT e.empno, e.ename, d.deptno, d.dname
8 INTO v_empno, v_ename, v_deptno, v_dname
9 FROM emp e,dept d
10 WHERE e.empno=7900
11 AND e.deptno=d.deptno ;
12 DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename||' '||v_deptno||' '||v_dname) ;
13 END ;
14 /
-> 7900 JAMES 30 SALES
변수 예제 2
사용자로부터 두 개의 숫자를 입력 받아서 합을 구하시오
SCOTT>DECLARE
2 v_no1 NUMBER := &no1 ;
3 v_no2 NUMBER := &no2 ;
4 v_sum NUMBER ;
5 BEGIN
6 v_sum := v_no1 + v_no2 ;
7 DBMS_OUTPUT.PUT_LINE('첫 번째 수:'||v_no1||', 두번째 수 :'||v_no2||', 합은 :'||v_sum||' 입니다') ;
8 END ;
9 /
Enter value for no1 : 20
Enter value for no2 : 40
-> 첫 번째 수 : 20, 두번째 수 : 40, 합은 : 60 입니다
복합 변수 ( 조합 변수)
변수 하나 안에 사용자가 원하는 여러 가지 다른 유형의 데이터를 포함해서 사용자가 원하는 새로운 형태로 만듬
복합변수 - Record Type 변수 와 Table Type (컬렉션 타입) 변수로 나눌 수 있음
Record Type 변수 : 데이터 타입이 다른 여러 건을 저장할 경우
Table Type (컬렉션 타입) 변수 : 동일한 데이터 타입의 데이터를 여러 건 저장하고 싶을 경우
(1) Record Type 복합 변수
- 크게 %ROWTYPE 을 사용하는 테이블 기반의 Record Type과 사용자가 원하는 컬럼만을 지정해서 만드는
프로그래머 정의 기본 Record Type 으로 구분
PL/SQL Record 정의와 선언
1. TYPE type_name IS RECORD ( field_declaration[, field_declaration]...) ;
2. Identifier type_name
Record Type 변수의 선언 및 사용 예 )
프로그래머 정의 Record Type 변수 사용 예 1 :
Record type 변수를 활용하여 부서번호가 30 번인 부서의 부서번호와 부서명과 지역명을 Record type 변수에 저장한 후 출력. 단 record type 의 type 명은 dept_record_type 로 지정하겠음.
SCOTT>DECLARE
2 TYPE dept_record_type IS RECORD
3 (deptno dept.deptno%TYPE,
4 dname dept.dname%TYPE,
5 loc dept.loc%TYPE) ;
6 v_dept dept_record_type ;
7 BEGIN
8 SELECT deptno, dname, loc
9 INTO v_dept
10 FROM dept
11 WHERE deptno = 30 ;
12 DBMS_OUTPUT.PUT_LINE('부서번호 부서명 위치') ;
13 DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||
14 v_dept.dname||' '||
15 v_dept.loc) ;
16 END ;
17 /
부서번호 부서명 위치
------- ------ --------
30 SALES CHICAGO
프로그래머 정의 Record Type 변수 사용 예 2 :
emp2 테이블을 사용하여 사용자로부터 사원 번호를 입력 받은 후 아래와 같이 사원번호, 사원이름, 직급, 생일, 연락처, 급여를 출력, 단 직급이 없는 사원은 직급을 사원으로 표시해서 출력
Enter value for empno : 20000102
사원번호 : 20000102
사원명 : 김설악
직급 : 사원
생일: 22-MAR-83
연락처 : 031)234-5678
급여 : 30000000
SCOTT>DECLARE
2 TYPE e2_rec_type IS RECORD (
3 vempno emp2.empno%TYPE,
4 vname emp2.name%TYPE,
5 vposition emp2.position%TYPE,
6 vbirth emp2.birthday%TYPE,
7 vtel emp2.tel%TYPE,
8 vpay emp2.pay%TYPE) ;
9 v_e2_record e2_rec_type ;
10 v_empno emp2.empno%TYPE := '&empno' ;
11 BEGIN
12 SELECT empno,name,NVL(position,'사원'),birthday,tel,pay
13 INTO v_e2_record
14 FROM emp2
15 WHERE empno=v_empno ;
16 DBMS_OUTPUT.PUT_LINE('사원번호:'||v_e2_record.vempno) ;
17 DBMS_OUTPUT.PUT_LINE('사 원 명:'||v_e2_record.vname) ;
18 DBMS_OUTPUT.PUT_LINE('직 급:'||v_e2_record.vposition) ;
19 DBMS_OUTPUT.PUT_LINE('생 일:'||v_e2_record.vbirth) ;
20 DBMS_OUTPUT.PUT_LINE('연 락 처:'||v_e2_record.vtel) ;
21 DBMS_OUTPUT.PUT_LINE('급 여:'||v_e2_record.vpay) ;
22 END ;
23 /
(2) Table Type 변수 (컬렉션 타입)
대부분의 경우 1 개의 컬럼만 저장을 하지만 Row Type 을 사용하여 Record Type 과 같이 여러 가지 유형의 데이터 컬럼을 가질 수도 있음. 그러나 권장방법은 아님
컬렉션 타입의 종류
- 연관 배열
- 중첩 테이블
- VARRAY
연관 배열의 특성
- 연관 배열은 변수 선언 당시 채워지지 않으며 키나 값을 포함하지 않으므로 선언에서 연관 배열을 초기화 할 수 없습니다.
- 연관 배열을 채우려면 명시적 실행문이 필요
- 연관 배열의 크기에 제약이 없음. 따라서 새 행이 추가됨에 따라 연관 배열이 증가하도록 행 수가 동적으로 늘어날 수 있다.
컬렉션 타입 정의와 선언
문법 :
1. TYPE type_name IS TABLE OF {column_type|variable%type|table.column%type}[NOT NULL]|table%ROWTYPE[INDEX BY BINARY_INTEGER] ;
2. Identifier type_name
- Table Type 변수 사용 예 1 :
Table Type 변수를 사용하여 사원번호가 7499 인 사원의 이름을 조회해서 해당 변수에 저장 한 후 출력.
SCOTT>DECLARE
2 t_name VARCHAR2(20) ;
3 TYPE tbl_emp_name IS TABLE OF
4 emp.ename%TYPE
5 INDEX BY BINARY_INTEGER ;
6 v_name tbl_emp_name ;
7 BEGIN
8 SELECT ename INTO t_name
9 FROM emp
10 WHERE empno=7499 ;
11 v_name(0) := t_name ;
12 DBMS_OUTPUT.PUT_LINE(v_name(0)) ;
13 END ;
14 /
- Table Type 변수 사용 예2 : 커서를 사용해서 생성하는 방법
professor 테이블의 rowtype 을 가지는 prof_table_type 이라는 이름으로 새로운 타입을 생성 한 후 v_prof_row 라는 변수에 할당. 그리고 v_prof_row 변수의 첫 번째 행 (0번 행) 에 데이터를 입력 한 후 화면에 출력
SCOTT>DECLARE
2 TYPE prof_table_type IS TABLE OF professor%rowtype
3 INDEX BY binary_integer ;
4 v_prof_row prof_table_type ;
5 BEGIN
6 v_prof_row(0).profno := 9000 ;
7 v_prof_row(0).name := 'imsy' ;
8 v_prof_row(0).id := 'imsy_id' ;
9 v_prof_row(0).position := 'imsy' ;
10 v_prof_row(0).pay := '000' ;
11 v_prof_row(0).hiredate := sysdate ;
12 DBMS_OUTPUT.PUT_LINE(v_prof_row(0).profno||' '||v_prof_row(0).name||' '||v_prof_row(0).id||' '||
13 v_prof_row(0).position||' '||v_prof_row(0).pay||' '||v_prof_row(0).hiredate) ;
14 END ;
15 /
-> 9000 imsy imsy_id imsy 0 01-JUL-13
- Table Type 변수 사용 예 3 : For 반복문을 사용하여 변수에 여러 건의 데이터를 입력하는 방법
SCOTT>DECLARE
2 TYPE e_table_type IS TABLE OF
3 emp.ename%TYPE
4 INDEX BY BINARY_INTEGER ;
5 tab_type e_table_type ;
6 a BINARY_INTEGER := 0 ;
7 BEGIN
8 FOR i IN (SELECT ename FROM emp) LOOP
9 a := a+1 ;
10 tab_type(a) := i.ename ;
11 END LOOP ;
12 FOR J IN 1..a LOOP
13 DBMS_OUTPUT.PUT_LINE(tab_type(j)) ;
14 END LOOP ;
15 END ;
16 /
-> SMITH
ALLEN
WARD
JONES
....
비 PL/SQL 변수
PL/SQL 안에서만 사용되는 것이 아니라 외부에서도 사용될 수 있는 변수
SQL 환경에서도 쓸 수 있고 PL/SQL 에서도 사용 할 수 있음
- 바인드 변수
호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 함. VARIABLE 키워드를 사용하여 생성
사용 예 ) 교수번호가 1001 인 교수의 연봉을 계산하여 바인드 변수에 할당 한 후 출력
SCOTT>VARIABLE v_bind NUMBER ; --SQL 에서 변수를 선언
SCOTT>BEGIN
2 SELECT (pay*12)+NVL(bonus,0) INTO :v_bind
3 FROM professor
4 WHERE profno=1001 ;
5 END ;
6 /
SCOTT>PRINT v_bind ; --SQL에서 바인드 변수에 담긴 값을 출력
V_BIND
------
6700
PRINT 문장으로 매번 확인이 번거로울 경우 SET AUTOPRINT ON ; 명령어를 사용하면 자동으로 바인드 변수값을 출력
PL/SQL 과 SQL 에서 사용되는 주요 데이터 타입 정리
