오라클 서버에서 사용자의 sql문장을 처리하는 모든 서버 프로세스는 sql문을 실행할 때마다 주어진 sql 문장의 처리(parse, execution) 를 위해 SGA 내부에 Context Area 라는 개별적인 메모리 공간을 사용한다.
Cursor란 Context Area라는 메모리 공간에 있는 데이터를 사람이 접근 할 수 있도록 연결해주는 일종의 연결통로이며 포인터라고 부르기도 한다.
즉 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 PL/SQL이 사용하는 개별적인 메모리공간으로 복사 해 온 후(복사 한 데이터를 Actice Set이라함) 커서를 통해 Context Area 있는 Active Set 중에서 원하는 데이터에 접근한 후 필요한 데이터를 추출해(Fetch) PL/SQL 변수에 담고 후속 작업을 하게 된다는 뜻.
커서 종류에는 두가지와 각각의 속성들이 있다.
묵시적 커서(Implicit Cursor)
오라클이 자동적으로 선언하여 사용하고 자동으로 정리된다(clean up). 사용자 입장에서는 생성 유무를 알수 없고 PL/SQL블록 내에서의 SELECT문, DML문이 실행될 때마다 묵시적 커서가 선언된다. (세션 내 단 한개만이 선언되어 사용되다가 문장이 종료됨과 동시에 정리된다)
SQL%ROWCOUNT : 해당 커서에서 실행한 총 행의 개수를 반환(가장 마지막 행이 몇 번째 행인지 카운트)
SQL%FOUND : 해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE값을 반환하고 없을 경우 FALSE값 반환
SQL%NOTFOUND : 해당 커서 안에 수행해야 할 데이터가 없을 경우 TRUE값을 반환하고 있을 경우 FALSE의값 반환
SQL%ISOPEN : 현재 묵시적 커서가 메모리에 OPEN 되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
사용자가 선언하여 생성 후 사용하고 주로 여러 개의 행을 처리하고자 할 경우 사용한다. (변수에 데이터를 담기 이전에 사용되는것이므로 복합변수와 헷갈리면 안된다)
많은 데이터를 변수에 담으려면 복합변수를 써야 하는데 이때 명시적 커서를 사용해야 성능이 더 좋아진다.
묵시적 커서와는 다르게 동시에 여러 개가 선언되어 사용될 수 있다.
커서이름%ROWCOUNT : FETCH 문에 의해 읽혀진 데이터의 총 행 수(가장 마지막에 처리된 행이 몇 번째 인지를 반환)
커서이름%FOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
커서이름%NOTFOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
커서이름%ISOPEN : 명시적 커서가 메모리에 확보(선언)되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
명시적 커서 처리단계
1. 명시적 커서 선언(Declaration)
CURSOR 커서명
IS
커서에 담고 싶은 내용을 가져오는 서브쿼리
해당 커서를 사용 하겠다 라고 PL/SQL에 알려주는 역할만 하며 실제 메모리 할당이 이뤄지는 것은 아님.
2. 명시적 커서 열기(OPEN)
OPEN 커서_이름;
커서 선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다. 실제 메모리가 할당됨. 이때, 명시적 커서 영역에 자리잡은 데이터의 첫 번째 행에 커서 포인터가 설정되고 이 포인터 위치의 데이터 행을 다음 단계인 fetch에서 읽게됨
3. 명시적 커서로부터 데이터 읽어 변수로 할당하기(FETCH)
FETCH 커서_이름 INTO 변수
명시적 커서의 데이터들(Active Set)로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용함. 읽게되는 데이터행은 포인터에 의해 지정되고 한 행에 FETCH되면 자동적으로 포인터는 다음 행으로 이동하게 됨.
보통 명시적 커서에는 데이터가 여러 건 들어 있기 대문에 많은 데이터들을 읽어 처리하기 위해 FETCH 문은 반복문과 함께 사용하는 경우가 많다.
4. 명시적 커서 닫기(CLOSE)
CLOSE 커서_이름;
작업이 끝난 메모리 공간을 반환하고 정리한다.
사용예
(emp 테이블에서 deptno 가 10번인 사람들의 empno , ename, sal 의 정보를 명시적 커서로 가져 온 후 변수에 저장하고 화면에 출력)
확정된 형태의 하나의 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수. (숫자, 문자, 날짜등)
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
%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]...);
대부분의 경우 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 설정 후 사용하면 자동으로 바인드 변수 값을 출력 해서 보여준다.
변수의 적용 범위
외부 블록에서 선언한 변수는 내부 블록에서도 사용할 수 있지만 내부블록에서 선언된 변수는 외부 블록에서 사용할 수 없다.
procedural language/SQL 의 약자로 block 구조의 언어
(Pascal -> Ada -> PL/SQL)
프로그래밍적 요소와 함께 데이터 트랜잭션 처리능력, 데이터에 대한 보안 및 예외처리 기능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원해 오라클 데이터베이스와 연관된 업무를 처리하기에 최적화된 언어.
PL/SQL 동작원리
사용자가 pl/sql BLOCK을 실행하면 해당 블록이 오라클 서버로 전달되서 오라클 서버 내 메모리에 상주해 있는 PL/SQL 엔진이 해당 블록을 받게됨.
그 후 해당 블록에 있든 모든 SQL 문장들은 분리가 되서 오라클 서버 프로세스에게 전달되 먼저 수행됨.
PL/SQL 엔진은 해당 SQL 문장이 수행되어 결과가 돌아 올 때까지 기다렸다가 그 결과를 받아서 PL/SQL 엔진에 만들어 둔 변수에 데이터를 담아 놓고 변수에 담긴 SQL 실행 결과값을 사용해 나머지 PL/SQL 문장을 실행함.
(이때 사용되는 변수는 Client가 PL/SQL Block을 작성할 때 미리 계획해서 선언해야 하며 이 변수를 잘못 선언 할 경우 데이터베이스에서 처리된 결과를 PL/SQL 엔진이 사용할 수 없게된다.)
PL/SQL 블록의 기본 구조
Anonymous Block
Named Block : procedure, function, package, trigger, object, ..
declare -- 옵션
선언부
begin -- 필수
실행부
exception -- 옵션
예외처리부
end; -- 필수
/
create or replace [procedure|function] 이름
is
begin -- 필수
exception -- 옵션
end; -- 필수
/
PL/SQL 내에서 SELECT 문장 사용하기
ex) 사원번호 입력해 사원이름과 월급 조회하기
create or replace procedure proc1(p_empno number)
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename, v_sal
from emp
where empno = p_empno;
dbms_output.put_line('ENAME : '||v_ename);
dbms_output.put_line('SAL : '||v_sal);
exception
when no_data_found then
dbms_output.put_line(p_empno||' does not exists!');
end;
/
PL/SQL 내에서 DML(insert, update, delete, merge) 문장 사용하기
INSERT
ex) 사용자로부터 번호(no) , 이름(name) , 연락처(tel) 값을 입력 받은 후 t_plsql 테이블에 입력하는 PL/SQL 문장을 작성하시오.
declare
v_no NUMBER := &no;
v_name VARCHAR2(10) := '&name';
v_tel NUMBER := &tel;
begin
INSERT INTO t_plsql VALUES(v_no, v_name, v_tel);
end;
/
SCOTT>SELECT * FROM t_plsql ;
SCOTT>commit;
UPDATE, DELETE
SQL>BEGIN
2 UPDATE t_plsql
3 SET name='CCC'
4 WHERE no = 2 ;
5 END ;
6 /
SQL>commit;
SQL>BEGIN
2 DELETE FROM t_plsql
3 WHERE no = 1 ;
4 END ;
5 /
SQL>commit;
MERGE
SQL>SELECT * FROM t_merge1;
NO FRUIT
----- -------------
1 APPLE
2 BANANA
SQL>SELECT * FROM t_merge2 ;
NO FRUIT
----- -------------
3 MELON
1 CHERRY
SQL> BEGIN
2 MERGE INTO t_merge2 t2
3 USING t_merge1 t1
4 ON(t1.no = t2.no)
5 WHEN MATCHED THEN
6 UPDATE SET
7 t2.fruit = t1.fruit
8 WHEN NOT MATCHED THEN
9 INSERT VALUES(t1.no , t1.fruit);
10 END ;
11 /
SQL>SELECT * FROM t_merge2 ;
NO FRUIT
----- ----------
3 MELON
1 APPLE <- 원래 CHERRY 였는데 UPDATE 됨
2 BANANA <- 이 줄은 추가 됨
SQL>commit;
PL/SQL 에서 사용하는 주요 용어와 연산자들
식별자: 객체에게 부여되는 이름 (테이블명, 변수명등)
구분자: 특별한 의미를 가진 기호
기호
의미
+,-,*,/,=,<>,!=
연산자
@
원격 엑세스 표시자
--
단일행 주석표시
/*, */
주석 시작과 종료
:=
할당 연산자
ll
연결 연산자
;
명령문 종료
리터럴 : 변수에 할당되는 모든 값 (식별자가 아닌 모든 문자, 숫자, 부울, 또는 날짜 값)