PLSQL(Procedure Language Structured Query Language)
Why PLSQL?
아래를 보고 왜 PL/SQL을 사용하는지 이해해보자.
--DBA SESSION
alter system flush shared_pool;
SELECT sql_id, sql_text, parse_calls, executions
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND sql_text NOT LIKE '%v$sql%';
--HR SESSION
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;
SELECT * FROM employees WHERE employee_id = 102;
--DBA SESSION
SELECT sql_id, sql_text, parse_calls, executions
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND sql_text NOT LIKE '%v$sql%';
실행계획을 확인하면 parse_calls와 executions가 증가됐음을 알 수 있다. 비슷한 sql문인데 실행계획을 공유하지 않았다. 변수처리를 하여 실행계획을 해보자
--sql+에서 실행
--HR SESSION
SQL> conn hr/hr
Connected.
SQL> var b_id number --bind변수 선언(SIZE 선언하지 않는다)
SQL> print b_id
B_ID
----------
--참고: var, print 등의 명령어는 sql+ 명령어
SQL> execute :b_id := 100 --할당연산자 사용 시 반드시 COLON(:)을 사용해야함
PL/SQL procedure successfully completed.
SQL> print b_id
B_ID
----------
100
SQL> select * from employees where employee_id = :b_id;
--DBA SESSION
SELECT sql_id, sql_text, parse_calls, executions
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND sql_text NOT LIKE '%v$sql%';
--:b_id:로 새로운 실행계획이 만들어졌음을 확인할 수 있다.
--HR SESSION
SQL> exec :b_id := 200
PL/SQL procedure successfully completed.
SQL> print b_id --변수는 overwrite됨
B_ID
----------
200
SQL> select * from employees where employee_id = :b_id; --동일한 sql문 사
--DBA SESSION
SELECT sql_id, sql_text, parse_calls, executions
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND sql_text NOT LIKE '%v$sql%';
--:b_id: 실행계획이 재사용 됐음을 알 수 있다.
PL/SQL을 사용하는 이유 중 하나는 변수선언을 하여 실행계획을 공유할 수 있게 된다.
Bind 변수의 단점
bind변수는 tool(developer/sql+ 등)에 종속된다. package에서 선언된 변수는 public 변수가 된다. 이는 package를 사용하는 이유 중 하나이다.
PL/SQL 개요
PL/SQL은 부분적인 SQL 기능과 4가지 기능을 합쳐논 것과 같다.
PL/SQL = SQL + α
SQL:
PL/SQL에서 SQL은 DQL/DML/TCL을 수행 할 수 있다.(DDL과DCL문 사용 불가)
α:
1.변수
- bind변수(하나만 입력)
- 스칼라(단일값만 보유하는 변수)
- 조합 (레코드 타입/배열/2차원 배열(레코드+배열))
2. Logic
- 조건문
- 반복문
3. Cursor
- 암시적 커서
- 반드시 한개의 ROW만 Fetch해야 한다.
- SELECT INTO, DML(Fetch절)
- 명시적 커서
- 한개 이상의 row를 fetch해야 할 때 사용
4. 예외 사항 처리
Exception handling(객체지향언어는 예외처리를 할 수 있지만 r은 불가능)
블록구조
블락은 object 권한과 session 접근 권한만 있으면 익명블록구조를 만들 수 있고, Shared Pool의 library cache에 위치한다. 오라클의 PL/SQL 프로그램은 블록 구조형식이다. 익명블록의 단점은 share가 되지 않고 object단위가 아니라 reparsing이 되지 않는다.
익명블록구조의 단점은 다음과 같다.
1. object단위가 아니라 reparsing 불가(매번 수행시 마다 compiling)
2. 또한 share가 되지 않는다. (oracle db 안에 이 프로그램이 저장되어 있지 않기 때문)
3. 접속해있는 tool에서 지원하는 bind variable밖에 사용 못 함(declare절에서 글로벌 변수 선언 불가)
object의 관리는 dba가 하고, 왠만하면 object를 생성하지 않고 익명블록구조를 사용하는 것이 좋다. 다음 예시를 보자.
>Syntax
declare (선언부) 선택
-변수 선언
-명시적 커서 선언
-사용자가 정의한 예외사항 선언
begin(실행부) 필수
-로직구현
-쿼리문 처리(DQL문/DML문/TCL문)
exception(예외사항처리) 선택
-실행부분에서 발생한 오류를 처리
end;(프로그램 종료) 필수
/
예문을 만들어보자
set serveroutput on --sql+에서 입력해야
--sql developer에는 보기->DBMS출력
begin
dbms_output.put_line('안녕');
dbms_output.put_line('오늘도 행복하자!');
end;
/
declare
/* scalar data type(단일값만 보유하는 변수) */
v_a number(7);
v_b number(3) := 100; --PLSQL에는 하나씩만 선언 가능
begin
dbms_output.put_line('v_a 변수에 있는 값은 ' || v_a);
dbms_output.put_line('v_b 변수에 있는 값은 ' || v_b);
end;
/
변수 선언 시 주의할 점
- 변수 이름은 문자로 시작해야한다.
- 문자, 숫자, 특수문자(_, $, #)만 포함할 수 있다.
- 30자 이하의 문자,숫자,특수문자로만 사용해야한다.
- 예약어는 사용할 수 없다. (declare, begin, end exception, select, insert 등)
- 변수를 선언시에
not null,constant로 지정된 변수에는 초기값을 무조건 할당해야 한다. (할당연산자::=혹은default)
Procedure문에서 제한되는 함수
Procedure문에서 사용할 수 없는 함수는 다음과 같다.
DECODE(CASE는 가능)AVG,SUM,MAX,MIN,COUNT,STDDEV,VARIANCE
declare
v_name varchar2(20);
begin
v_name := upper('james'); --Procedure 문
--v_flag := deocde(...) --Error
--v_max := max(v_sal --Error
end;
/
변수 범위
지역변수
declare
/* local variable, private variable 지역변수 */
/* scalar data type(단일값만 보유하는 변수) */
--숫자
v_a number(7);
v_b number(3) := 100; --PLSQL에는 하나씩만 선언 가능
--문자
v_c varchar2(10) NOT NULL := 'PL/SQL'; --변수에 제약조건을 걸 수 있음(not null만)
--날짜
v_d constant date default sysdate; --default는 할당연산자(:=)와 같음 /
--상수값 선언 (한번 받은 값만 계속 사용/초기값 할당 필수)
begin
dbms_output.put_line('v_a 변수에 있는 값은 ' || v_a);
dbms_output.put_line('v_b 변수에 있는 값은 ' || v_b);
dbms_output.put_line('v_b 변수에 있는 값은 ' || (v_b+100));
--우선순위때문에 괄호로 묶어서 연산해야함
dbms_output.put_line('v_c 변수에 있는 값은 ' || v_c);
dbms_output.put_line('v_d 변수에 있는 값은 ' || v_d);
v_a := 200; --변수는 실행부에서 다른 값으로 사용가능
dbms_output.put_line('v_a 변수에 있는 값은 ' || v_a);
--v_b := sysdate+1; --constant(상수)를 선언하여 error 발생
end;
/
로컬변수는 블록이 끝나는 순간(/) 풀린다. 즉, 사용하는 프로그램 외에는 쓸수 없다. 다른 프로그램에서 사용하고 싶다면 전역변수로 사용해야한다.
전역변수
Bind(Host) variable(= global variable, public variable, 전역변수)라고도 한다. 전역변수는 SERVER PROCESS의 SESSION DATA에 저장되기 때문에 SESSION창이 종료될 때 까지 사용할 수 있다.
아래 문장을 보고 전역변수를 이해해보자
declare
v_sal number := 1000;
v_comm number := 100;
v_total number;
begin
:b_total := v_sal + v_comm; --colon을 써야 프로그램 밖에서 변수를 찾음
dbms_output.put_line(:b_total);
end;
/
select * from employees where salary > :b_total; --ERROR
위 문장은 프로그램 안에서만 쓰는 변수이기 때문에 오류가 난다. 프로그램이 끝나면 변수는 사라지기 때문에 변수를 계속 사용하려면 전역변수로 설정해야 한다.
var b_total number
declare
v_sal number := 1000;
v_comm number := 100;
begin
:b_total := v_sal + v_comm; --colon을 써야 프로그램 밖에서 변수를 찾음
dbms_output.put_line(:b_total);
end;
/
select * from employees where salary > :b_total;
--b_total은 session이 종료될 때 까지 사용 가능
그러므로 다음과 같이 프로그램 외에서 bind변수를 선언하여야 한다. 그리고 이를 다음과 같이도 사용할 수 있다.
var b_total number --bind 변수는 number size를 설정하지 않는다.
var b_sal number
var b_comm number
var b_name varchar2(20)
exec :b_sal := 1000;
exec :b_comm := 100;
declare
v_sal number := :b_sal;
v_comm number := :b_comm;
begin
:b_total := v_sal + v_comm;
dbms_output.put_line(:b_total);
end;
/
select * from employees where salary > :b_total;
SUB-BLOCK
한 블락 안에 또 다른 블락을 표현할 수 있다.
declare
v_sal number(8,2) := 60000;
v_comm number(8,2) := v_sal * 0.20;
v_message varchar2(100) := 'eligible for commision';
begin
declare
v_sal number(8,2) := 50000;
v_comm number(8,2) := 0;
v_total number(8,2) := v_sal + v_comm;
begin
dbms_output.put_line(v_total);
end;
end;
/
메인블락에서 선언한 변수와 서브블락에서 선언한 변수가 같은 이름이더라도 블락이 다르기 때문에 같은 것이 아니다.
declare
v_sal number(8,2) := 60000;
v_comm number(8,2) := v_sal * 0.20;
v_message varchar2(100) := 'eligible for commision';
begin
declare
--v_sal number(8,2) := 50000;
--v_comm number(8,2) := 0;
v_total number(8,2) := v_sal + v_comm;
begin
dbms_output.put_line(v_total);
end;
end;
/
서브블락안의 해당 변수가 없다면 메인블락의 변수를 참조한다. 반대로 서브블락에 사용한 변수를 메인블락에 호출하였다면 사용할 수 없다. 그 이유는 서브블락이 끝나면 그 즉시 변수의 수명이 다했기 때문이다. 다음 예시를 보고 출력 결과를 예상해보자
출력 우선순위
declare
v_sal number(8,2) := 60000;
v_comm number(8,2) := v_sal * 0.20;
v_message varchar2(100) := 'eligible for commision';
begin
declare
v_sal number(8,2) := 50000;
v_comm number(8,2) := 0;
v_total number(8,2) := v_sal + v_comm;
begin
dbms_output.put_line(v_message);
v_message := 'clerk not ' || v_message;
dbms_output.put_line(v_total);
dbms_output.put_line(v_sal);
dbms_output.put_line(v_comm);
dbms_output.put_line(v_message);
end;
dbms_output.put_line(v_sal);
dbms_output.put_line(v_comm);
dbms_output.put_line(v_message);
--dbms_output.put_line(v_total); --ERROR: 이미 변수가 사라짐
end;
/
출력 결과는 다음과 같다.
eligible for commision
50000
50000
0
clerk not eligible for commision
60000
12000
clerk not eligible for commision --이전에 서브블락에서 사용한 값이 출력
LABEL
각 블락에서 같은 이름으로 선언된 변수 사용시에, 서브블락에서 메인블락의 변수를 사용하고자 하면 어떻게 해야할까? 레이블을 사용해야한다.
<<outer>> --label, 이름은 원하는대로 사용 가능
declare
v_sal number(8,2) := 60000;
v_comm number(8,2) := v_sal * 0.20;
v_message varchar2(100) := 'eligible for commision';
begin
declare
v_sal number(8,2) := 50000;
v_comm number(8,2) := 0;
v_total number(8,2) := v_sal + v_comm;
begin
dbms_output.put_line(v_sal);
dbms_output.put_line(outer.v_sal); --label이 있는 블락의 변수 사용
end;
dbms_output.put_line(v_sal);
dbms_output.put_line(v_comm);
end;
/
>결과
50000 --자기블락내의 변수가 우선순위가 높으므로
60000
60000 --이전에 서브블락에서 사용하였음
12000 --v_comm은 메인블락에서 머저 선언되었음
Implicit Cursor (SELECT INTO)
SELECT문을 프로그램에 사용하려면 SELECT INTO를 사용하여야 한다. 그 이유는 프로그램에서는 바로 화면에 출력을 하지 못한다. 그래서 fetch시점에서 cursr에다가 담아 논 data를 변수에 저장시켜 출력시켜야한다.
주의
암시적 커서는 반드시 1개의 ROW만 FETCH할 수 있도록 조건절을 만들어주어야 한다.
SELECT ...
INTO ...(반드시 1개의 ROW만 FETCH해야한다.)
- 0개의row 출력 시: no_date_found error 발생
- 1개를 초과한 row 출력 시: too_many_rows error 발생 -> (명시적커서로 해결 가능)
- DML문(
INSERT,UPDATE,DELETE,MERGE)은 0건 2건이든 신경을 쓰지 않는다.
예문을 만들어보자
declare
v_lname varchar2(20);
v_fname varchar2(20);
v_sal number;
--변수는 컬럼에 맞춰서 사용(HARD CODING: MODIFY 시 변경해주어야 하는 단점이 있음)
begin
SELECT last_name, first_name, salary
INTO v_lname, v_fname, v_sal --Fetch 절
FROM employees
WHERE employee_id = 100;
dbms_output.put_line(v_lname || ' ' || v_fname);
dbms_output.put_line(v_sal);
end;
/
Soft coding(%type)
위 쿼리는 hard coding로 변수를 설정했다. hard coding은 컬럼의 타입과 size를 변경하지 않을 경우에 사용하면 되지만 변경하게 되면 프로그램을 바꾸어야 한다. 이를 해결하기 위해 soft coding을 해서 후에 컬럼의 타입과 size가 변경되더라도 수정할 필요가 없도록 할 수 있다. (%type은 타입과 size만 참조하고 제약조건은 이어받지 않는다.)
declare
v_lname employees.last_name%type;
v_fname v_lname%type; --변수의 타입을 사용 가능
v_sal employees.salary%type;
begin
SELECT last_name, first_name, salary
INTO v_lname, v_fname, v_sal --Fetch 절
FROM employees
WHERE department_id = 20;
dbms_output.put_line(v_lname || ' ' || v_fname);
dbms_output.put_line(v_sal);
end;
/
BIND 변수 처리(실행계획 공유)
100번 사원과 101번 사원을 출력하려 한다.
--100번 사원 출력
declare
v_lname employees.last_name%type;
v_fname v_lname%type;
v_sal employees.salary%type;
begin
SELECT last_name, first_name, salary
INTO v_lname, v_fname, v_sal --Fetch 절
FROM employees
WHERE employee_id = 100;
dbms_output.put_line(v_lname || ' ' || v_fname);
dbms_output.put_line(v_sal);
end;
/
--101번 사원 출력
declare
v_lname employees.last_name%type;
v_fname v_lname%type;
v_sal employees.salary%type;
begin
SELECT last_name, first_name, salary
INTO v_lname, v_fname, v_sal --Fetch 절
FROM employees
WHERE employee_id = 101;
dbms_output.put_line(v_lname || ' ' || v_fname);
dbms_output.put_line(v_sal);
end;
/
--실행계획 공유 확인
SELECT sql_id, sql_text, parse_calls, executions
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND sql_text NOT LIKE '%v$sql%';
위 두 문장은 같아 보이지만 실행계획을 공유하고 있지 않다. bind 변수를 사용하여 실행계획을 공유하도록 하
var b_id number
exec :b_id := 100
declare
v_lname employees.last_name%type;
v_fname v_lname%type;
v_sal employees.salary%type;
begin
SELECT last_name, first_name, salary
INTO v_lname, v_fname, v_sal --Fetch 절
FROM employees
WHERE employee_id = :b_id;
dbms_output.put_line(v_lname || ' ' || v_fname);
dbms_output.put_line(v_sal);
end;
/
exec :b_id := 101
declare
v_lname employees.last_name%type;
v_fname v_lname%type;
v_sal employees.salary%type;
begin
SELECT last_name, first_name, salary
INTO v_lname, v_fname, v_sal --Fetch 절
FROM employees
WHERE employee_id = :b_id;
dbms_output.put_line(v_lname || ' ' || v_fname);
dbms_output.put_line(v_sal);
end;
/
--실행계획 공유 확인
SELECT sql_id, sql_text, parse_calls, executions
FROM v$sql
WHERE sql_text LIKE '%employees%'
AND sql_text NOT LIKE '%v$sql%';
실행계획을 공유했음을 알 수 있다.
암시적 커서에서의 DML
프로그램에서 DML문을 수행할 수 있다. 참고로, DML문이 실행되면 Transaction이 발생하므로 TCL을 사용하여 Transaction을 끝내주는 습관을 갖자.(Transaciton 실행 시 lock이 걸려 부하가 생길 수 있다)
--table 생성
CREATE TABLE test(id number, name varchar2(20), day date);
--프로그램 생성
begin
INSERT INTO test(id, name, day)
VALUES(1,'홍길동',TO_DATE('2018-01-01','yyyy-mm-dd'));
end;
/
select * from test;
rollback;
--bind 변수 활용
var b_id number
var b_name varchar2(20)
var b_day varchar2(30) --date타입이 없어 문자로 선언
exec :b_id := 1
exec :b_name := '홍길동'
exec :b_day := '20180101'
print b_id b_name b_day
begin
insert into test(id, name, day)
values(:b_id,:b_name,to_date(:b_day,'yyyymmdd'));
end;
/
select * from test;
rollback;
--insert subquery
begin
insert into test(id,name,day)
select employee_id, last_name, hire_date
from employees;
end;
/
select * from test;
--update
begin
update test
set name = '박찬호'
where id = 1;
end;
/
select * from test;
rollback;
프로그램 실행 결과 익명 블록이 완료되었습니다.의 메시지만 나올 뿐 몇 개의 row가 생성되었는지, 제대로 수행이 되었는지 메시지가 나오지 않는다. 암시적 커서의 속성을 사용하여 이를 해결해보자.
암시적 커서의 속성
암시적 커서에서 DML문의 결과를 판단하는 3가지 속성이 있다. 바로 이전에 수행된 DML문에 종속되어 사용된다. 그러므로 SELECT INTO에 사용하는 것은 의미없는 행동이므로 SELECT INTO절에는 사용하지 말고 DML문에 만 사용하도록 하자.
sql%rowcount: 바로 이전에 수행된 DML문에 영향을 받은 ROW의 갯수
sql%found: 이전 쿼리의 영향을 받으면 TRUE, 아니면 FALSE
sql%notfound: found의 반대로 이전 쿼리의 영향을 받지 않으면 TRUE, 받으면 FALSE
rowcount
CREATE TABLE emp
AS SELECT * FROM employees;
begin
delete from emp where department_id = 20;
dbms_output.put_line(sql%rowcount || '행이 수정');
update emp
set salary = salary * 1.1
where department_id = 30;
dbms_output.put_line(sql%rowcount || '행이 수정');
end;
/
rollback;
found
begin
update emp
set salary = salary * 1.1
where employee_id = 100;
if sql%found then
dbms_output.put_line('수정됨');
else
dbms_output.put_line('수정안됨');
end if;
rollback;
end;
/
notfound
begin
update emp
set salary = salary * 1.1
where employee_id = 100;
if sql%notfound then
dbms_output.put_line('수정안됨');
else
dbms_output.put_line('수정됨');
end if;
rollback;
end;
/
RETURNING (DML+FETCH)
암시적 커서에서 PL/SQL을 사용하면 DML문을 작성하면서 FETCH를 같이 할 수 있다. 아래 문장을 보고 이해하자.
CREATE TABLE emp
AS SELECT * FROM employees;
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';
ALTER TABLE emp ADD CONSTRAINT emp_emp_pk_ix PRIMARY KEY(employee_id);
var b_id number
exec :b_id := 100
declare
v_sal emp.salary%type;
v_rate number(5,2) := 1.1;
begin
SELECT salary
INTO v_sal
FROM emp
WHERE employee_id = :b_id;
dbms_output.put_line('수정 전 월급 : ' || v_sal);
UPDATE emp
SET salary = v_sal * v_rate
WHERE employee_id = :b_id;
SELECT salary
INTO v_sal
FROM emp
WHERE employee_id = :b_id;
dbms_output.put_line('수정 전 월급 : ' || v_sal);
rollback;
end;
/
위 문장에서 똑같은 SELECT INTO절을 사용했다. RETURNING구문을 사용하여 하나의 문장에서 DML과 FETCH를 같이 실행해보자
var b_id number
exec :b_id := 100
declare
v_sal emp.salary%type;
v_rate number(5,2) := 1.1;
begin
SELECT salary
INTO v_sal
FROM emp
WHERE employee_id = :b_id;
dbms_output.put_line('수정 전 월급 : ' || v_sal);
UPDATE emp
SET salary = v_sal * v_rate
WHERE employee_id = :b_id
RETURNING salary --변환된 salary값
INTO v_sal;
dbms_output.put_line('수정 전 월급 : ' || v_sal);
rollback;
end;
/
쳐다보고 있는 ROW를 UPDATE를 시키면서 FETCH시켜주기때문에 I/O를 줄일 수 있는 장점이 있다. 하지만 암시적 커서이기때문에 1개의 ROW만 FETCH가 가능하다. (참고, TRIGGER를 사용하면 문장을 더 줄일 수 있다.)
조건 제어문
IF/END IF
BOOLEAN형식으로 TRUE 아니면 FALSE를 나타낸다.
declare
v_flag boolean := true;
begin
if v_flag then
dbms_output.put_line('참');
else
dbms_output.put_line('거짓');
end if;
end;
/
기본 구조는 다음과 같다.
>If문의 Syntax
if 조건 then
참값
elsif 조건 then
참값
elsif 조건 then
참값
else
기본값
end if;
조건 부분은 비교연산자(=,!=,>,>=,<,<=), 논리연산자(and,or,not), null 비교(is null,is not null) 등으로 사용할 수 있다.
CASE 표현식
CASE 표현식은 다음과 같이 사용할 수 있다.
declare
v_grade char(1) := upper('c');
v_appraisal varchar2(30);
begin
v_appraisal :=
case v_grade
when 'A' then '참잘했어요'
when 'B' then '잘했어요'
when 'C' then '다음에 잘해요'
else '니가 사람이야!!'
end;
dbms_output.put_line('등급은 ' || v_grade || '. 평가는 ' || v_appraisal);
end;
/
CASE 문
CASE문을 IF문처럼 사용하고 싶다면?
case 조건
when 비교1 then 참값1
when 비교2 then 참값2
when 비교3 then 참값3
else
기본값
end case;
예시를 살펴보자.
var b_id number
exec :b_id := 100
declare
v_mon number;
begin
select months_between(sysdate,hire_date)
into v_mon
from emp
where employee_id = :b_id;
case
when v_mon >= 150 then
update emp
set salary = salary * 1.2
where employee_id = :b_id;
dbms_output.put_line(:b_id || ' 사원은 근무개월수가 ' || trunc(v_mon) || '입니다.' || '급여는 20% 수정되었습니다.');
when v_mon < 150 and v_mon >= 100 then
update emp
set salary = salary * 1.1
where employee_id = :b_id;
dbms_output.put_line(:b_id || ' 사원은 근무개월수가 ' || trunc(v_mon) || '입니다.' || '급여는 10% 수정되었습니다.');
else
dbms_output.put_line(:b_id || ' 사원은 근무개월수가 ' || trunc(v_mon) || '입니다.' || '100 개월 미만이므로 급여 수정 안됩니다');
end case;
rollback;
end;
/
반복문
LOOP문
계속 반복하니 exit를 걸어주지 않으면 무한 루프에 빠질 수 있다.
declare
i number := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
if i > 10 then
exit;
end if;
end loop;
end;
/
WHILE LOOP문
WHILE은 조건을 먼저 평가하고 LOOP를 돌린다. 조건에 맞게 LOOP를 수행하고 싶을 때 사용하면 좋다.
declare
i number := 1;
begin
while i <= 10
loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
/
FOR문
반복 횟수를 알고 있을 때 사용하면 좋다. FOR문은 카운트 변수를 먼저 선언안해주어도 사용할 수 있다.
begin
for i in 1..10 --범위에 null은 사용 불가
loop
dbms_output.put_line(i); --i는 다른 곳에서 할당하면 안된다.
end loop;
end;
/
reverse를 사용하여 큰 값부터 작은 값까지 LOOP를 돌릴 수 있다. reverse는 for ... in뒤에다가 사용한다
declare
v_start number := 1;
v_end number := 10;
begin
for i in reverse v_start..v_end
loop
dbms_output.put_line(i);
end loop;
end;
/
EXIT WHEN
loop문에서 조건에 부합했을 때 loop를 중단시키려면 exit when을 사용하면 된다.
declare
i number := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when i>10;
end loop;
end;
/
위 문장은 아래와 같다.
declare
i number := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
if i > 10 then
exit;
end if;
end loop;
end;
/
CONTINUE WHEN
continew when 조건이 true면 아래 logic을 수행하지 않고 main loop로 올라간다.
declare
v_total number := 0;
begin
for i in 1..10 loop
v_total := v_total + i;
dbms_output.put_line('Total is : ' || v_total);
continue when i > 5;
v_total := v_total + i;
dbms_output.put_line('Out of loop total is : ' || v_total);
end loop;
end;
/
위 문장은 아래와 같다.
declare
v_total number := 0;
begin
for i in 1..10 loop
v_total := v_total + i;
dbms_output.put_line('Total is : ' || v_total);
loop
exit when i > 5;
v_total := v_total + i;
dbms_output.put_line('Out of loop total is : ' || v_total);
exit;
end loop;
end loop;
end;
/
레이블을 사용하여 메인 루프로 올라갈 수 있다.
declare
v_total number := 0;
begin
<<toploop>>
for i in 1..10 loop
v_total := v_total + i;
dbms_output.put_line('Total is : ' || v_total);
for j in 1..10 loop
continue toploop when i+j > 5;
v_total:= v_total + j;
dbms_output.put_line(v_total);
end loop;
end loop;
end;
/
조합데이터 유형
레코드 타입
서로 다른 데이터 유형의 값을 저장하는 방식이다. 스칼라데이터 타입을 모와둔 형태라 볼 수 있다. 그래서 스칼라 유형과는 달리 다중값을 보유할 수 있다. 아래 세 문장을 보고 이해해보자.
declare
v_dept_id departments.department_id%type;
v_dept_name departments.department_name%type;
v_mgr_id departments.manager_id%type;
v_loc_id departments.location_id%type;
begin
SELECT *
INTO v_dept_id, v_dept_name, v_mgr_id, v_loc_id
FROM departments
WHERE department_id = 10;
dbms_output.put_line(v_dept_id || ' ' || v_dept_name || ' ' || v_mgr_id || ' ' ||v_loc_id);
end;
/
변수에 내부 구성요소를 가질 수 있는 변수를 선언하고자 레코드 타입을 사용한다.
declare
--user define type
type dept_record_type is record
(dept_id number,
dept_name varchar2(30),
dept_mgr departments.manager_id%type,
dept_loc departments.location_id%type);
v_rec dept_record_type;
begin
SELECT *
INTO v_rec
FROM departments
WHERE department_id = 10;
dbms_output.put_line(v_rec.dept_id);
dbms_output.put_line(v_rec.dept_name);
dbms_output.put_line(v_rec.dept_mgr);
dbms_output.put_line(v_rec.dept_loc);
end;
/
근데 레코드 변수를 만든 것이나 각자 로컬 변수를 선언해야 하는 고생은 똑같지 않을까? 그래서 아래와 같이 오라클은 쉽게 레코드 변수를 만들 수 있게 하였다.
declare
v_rec departments%rowtype;
begin
SELECT *
INTO v_rec
FROM departments
WHERE department_id = 10;
dbms_output.put_line(v_rec.department_id);
dbms_output.put_line(v_rec.department_name);
dbms_output.put_line(v_rec.manager_id);
dbms_output.put_line(v_rec.location_id);
end;
/
%rowtype을 사용하여 손쉽게 레코드 타입을 만들 수 있다. 레코드 이름은 컬럼명을 따라간다. 또한 아래와 같이 필드 안에 레코드타입을 사용할 수 있다.
declare
--user define type
type dept_record_type is record
(dept_id number,
dept_name varchar2(30),
dept_mgr number,
dept_loc number,
dept_rec departments%rowtype);
v_rec dept_record_type;
begin
SELECT *
INTO v_rec.dept_rec
FROM departments
WHERE department_id = 10;
dbms_output.put_line(v_rec.dept_rec.department_id);
dbms_output.put_line(v_rec.dept_rec.department_name);
dbms_output.put_line(v_rec.dept_rec.manager_id);
dbms_output.put_line(v_rec.dept_rec.location_id);
end;
/
참고로 레코드변수를 object로 사용하여 다른 곳에서 불러 올 수 있다. 다른 방법으로는 package를 사용해서 도 가능하다.
배열
동일한 데이터 유형의 값을 쌓아놓고 사용하고 싶을 때 사용한다. 배열의 종류는 다음과 같다.
Index by table(연관 배열)
Nested table(중첩테이블)
Varray
begin
update employees
set salary = salary * 1.1
where employee_id = 100;
update employees
set salary = salary * 1.1
where employee_id = 200;
rollback;
end;
/
위 문장은 실행계획을 공유하지 못한다. 배열타입을 만들어서 해결해보자.
declare
type table_id_type is table of number index by binary_integer;
--배열 type은 하나의 타입(여기서 number)만 가질 수 있다.(방번호)
--index by: 요소번호
/*
요소 번호의 타입 3가지
-binary_integer: -2^31~2^31(2g)로 총 4g를 가질 수 있음 (plsql의 배열 전용 타입)
-pls_integer: 10g version부터 사용 가능, 숫자 연산 가장 빠름 (plsql의 배열 전용 타입)
-varchar2(32767): 문자 방번호
*/
v_tab table_id_type;
begin
v_tab(1) := 100;
v_tab(2) := 200;
--()안의 수는 요소번호(배열방번호)
--100과 200은 배열 값
update employees
set salary = salary * 1.1
where employee_id = v_tab(1);
update employees
set salary = salary * 1.1
where employee_id = v_tab(2);
rollback;
end;
/
loop를 사용하여 하나의 문장으로 바꾸어보자.
declare
type table_id_type is table of number index by binary_integer;
v_tab table_id_type;
begin
v_tab(1) := 100;
v_tab(2) := 200;
for i in 1..2 loop
update employees
set salary = salary * 1.1
where employee_id = v_tab(i);
end loop;
rollback;
end;
/
for문을 사용하여 하나의 문장으로 바꾸었다. method를 사용하여 더 깔끔하게 작업해보자.
declare
type table_id_type is table of number index by binary_integer;
v_tab table_id_type;
begin
v_tab(1) := 100;
v_tab(2) := 200;
for i in v_tab.first..v_tab.last loop
update employees
set salary = salary * 1.1
where employee_id = v_tab(i);
end loop;
rollback;
end;
/
first는 가장 작은 방 번호, last는 가장 큰 번호를 나타내는 요소이다. (먼저 쓴 번호가 아님을 알아두자)
다음과 같이 배열의 방 번호가 연속된 순서가 아닐 때는 어떨까?
declare
type table_id_type is table of number index by binary_integer;
v_tab table_id_type;
begin
v_tab(1) := 100;
v_tab(3) := 200;
for i in v_tab.first..v_tab.last loop
update employees
set salary = salary * 1.1
where employee_id = v_tab(i);
end loop;
rollback;
end;
/
없는 배열값을 참조하려고 하면 무조건 오류가 발생한다. (예외사항:no data found) 이 문제를 해결해보자.
declare
type table_id_type is table of number index by binary_integer;
v_tab table_id_type;
begin
v_tab(1) := 100;
v_tab(3) := 200;
for i in v_tab.first..v_tab.last loop
if v_tab.exists(i) then
update employees
set salary = salary * 1.1
where employee_id = v_tab(i);
else
dbms_output.put_line( i || '요소가 없습니다.');
end if;
end loop;
rollback;
end;
/
exists()라는 method를 사용하여 해결할 수 있다.
배열 method 정리
declare
type tab_char_type is table of varchar2(10) index by pls_integer;
v_city tab_char_type;
begin
v_city(1) := '서울';
v_city(2) := '대전';
v_city(3) := '부산';
v_city(4) := '광주';
--count: 배열안에 몇 건이 있는지 알려줌
dbms_output.put_line(v_city.count);
--first: 가장 작은 방 번호
dbms_output.put_line(v_city.first);
--last: 가장 큰 방 번호
dbms_output.put_line(v_city.last);
--작은 방부터 큰 방까지 출력
for i in v_city.first..v_city.last loop
dbms_output.put_line(v_city(i));
end loop;
--next: 다음 방 번호
dbms_output.put_line('next' || v_city.next(1));
--prior: 이전 방 번호
dbms_output.put_line(v_city.prior(2));
--방 삭제
v_city.delete(3);
--1번부터 3번방 삭제
v_city.delete(2,3);
--모든 방 삭제
--v_city.delete
--exists: 존재여부 확인
for i in v_city.first..v_city.last loop
if v_city.exists(i) then
dbms_output.put_line(v_city(i));
else
dbms_output.put_line(i || '번 요소는 존재하지 앖습니다.');
end if;
end loop;
--extend: 배열 확장(nested table)
--v_city.extend(1);
end;
/
1차원 배열
기본적인 배열의 형태이다. 단일 데이터 타입만 쌓아놓고 조작한다.
declare
type num_type is table of number index by pls_integer;
v_num num_type;
begin
for i in 100..110 loop
v_num(i) := i;
end loop;
for i in v_num.first..v_num.last loop
dbms_output.put_line(v_num(i));
end loop;
end;
/
2차원 배열
배열에서 언급한대로 PLSQL에서의 배열은 type은 하나의 타입(여기서 number)만 가질 수 있어서 1차원 배열만 만들 수 있다. 2차원 배열을 만들기 위해서는 레코드 타입을 먼저 선언하고 그 타입을 배열 변수에 지정하여 사용하여야 한다.
declare
type dept_rec_type is record
(id number, name varchar2(30), mgr number, loc number);
v_rec dept_rec_type;
type dept_tab_type is table of v_rec%type index by pls_integer;
v_tab dept_tab_type;
begin
for i in 1..5 loop
SELECT *
INTO v_tab(i)
FROM departments
WHERE department_id = i * 10;
end loop;
for i in v_tab.first..v_tab.last loop
dbms_output.put_line(v_tab(i).id || ' ' || v_tab(i).name);
--2차원배열부터는 배열(요소번호).필드명(v_tab(i).name) 을 써주어야 한다.
end loop;
end;
/
레코드 타입을 먼저 선언하고 그 타입을 배열타입에 사용해도 된다.
declare
type dept_rec_type is record
(id number, name varchar2(30), mgr number, loc number);
type dept_tab_type is table of dept_rec_type index by pls_integer;
v_tab dept_tab_type;
테이블의 rowtype을 사용하고 싶을 땐 다음과 같이 사용한다.
declare
type dept_tab_type is table of departments%rowtype index by pls_integer;
v_tab dept_tab_type;
begin
for i in 1..5 loop
SELECT *
INTO v_tab(i)
FROM departments
WHERE department_id = i * 10;
end loop;
for i in v_tab.first..v_tab.last loop
dbms_output.put_line(v_tab(i).department_id || ' ' || v_tab(i).department_name);
--rowtype을 참조하였을 때 필드 이름은 column이름을 따라가기에 수정해야함
end loop;
end;
/
만약 loop문이 끝나기 전에 바로 사용할 때는 굳이 배열을 사용안해도 레코드타입이나 스칼라데이터 변수으로 사용해도 된다. 배열은 overwirte되지 않아 loop문에서 쌓아놓고, loop문이 끝나고도 사용한다면 그 때 배열을 사용된다.
배열의 종류
index by
지금까지 했던 방식은 모두 index by 방식이다. index by는 가변배열이라 계속 배열값을 추가 할 수 있지만 배열 번호는 배열 값을 하나씩 넣어주어야 하는 불편함이 있다. 하지만 nested table은 변수 선언할 때 동시에 변수를 넣어줄 수 있다. 변수 type 선언 시 index by절이 없으면 nested table 배열이 된다.
nested table
문장을 보면서 이해하자.
declare
type tab_char_type is table of varchar2(10);
v_city tab_char_type := tab_char_type('서울','대전','부산','광주');
begin
v_city.delete(2,3);
for i in v_city.first..v_city.last loop
if v_city.exists(i) then
dbms_output.put_line(v_city(i));
else
dbms_output.put_line(i || '번 요소는 존재하지 앖습니다.');
end if;
end loop;
end;
/
nested table은 2G까지 사용 가능한 반면 index by는 4G까지 사용할 수 있다.
값 목록을 미리 알고 있으면 nested table이 편할 수 있다. 하지만 선언했을 시 용량확장이 안되어 배열값을 추가할 수 가 없다. 그래서 for문으로 값을 넣을 때(select into)는 index by를 사용하는 것이 좋다.
declare
type tab_char_type is table of varchar2(10);
v_city tab_char_type := tab_char_type('서울','대전','부산','광주');
begin
--확장시켜야 오류가 나지 않음
v_city.extend(1);
v_city(5) := '대구';
--삭제된 곳에는 extend를 시키지 않아도 됌
v_city.delete(4);
v_city(4) := '여수';
for i in v_city.first..v_city.last loop
if v_city.exists(i) then
dbms_output.put_line(v_city(i));
else
dbms_output.put_line(i || '번 요소는 존재하지 앖습니다.');
end if;
end loop;
end;
/
varray
varray는 nested table이랑 쓰는 방법은 같다. (배열에 넣을 값을 미리 알고 있을 때). 차이점은 요소의 개수를 제한할 수 있다. (index by는 최대 4G, nested table은 2G)
declare
type tab_char_type is varray(5) of varchar2(10);
--최대 5개의 값만 넣을 수 있음
v_city tab_char_type := tab_char_type('서울','부산','대전');
begin
for i in v_city.first..v_city.last loop
dbms_output.put_line(v_city(i));
end loop;
end;
/
5개로 요소의 개수를 제한했지만 3개를 선언했기에 추가가 되지 않는다. 배열값을 추가 하고 싶으면 extend() method를 사용해야 한다. 하지만 선언을 5개로 했기에 extend()로 2개까지 늘릴 수 있다.
declare
type tab_char_type is varray(5) of varchar2(10);
v_city tab_char_type := tab_char_type('서울','부산','대전');
begin
v_city.extend(2);
v_city(4) := '광주';
v_city(5) := '제주';
--v_city.extent(5); -error: 사전에 5개로 선언을 했기에 3개에서 5개를 늘리는 것은 불가
for i in v_city.first..v_city.last loop
dbms_output.put_line(v_city(i));
end loop;
end;
/
Explicit Cursor(명시적 커서)
- 여러개의 row를 fetch해야 한다면 명시적커서를 사용
- 프로그래머가 커서를 생성관리해야 한다.
잠깐 복습
Cursor: 메모리 포인터, sql문 실행메모리 영역
Implicit Cursor(암시적 커서): 커서를 오라클이 생성관리한다.
- 커서를 오라클이 생성관리한다.
- select .. into ... (반드시 1개 row만 fetch해야 한다.)
- dml작업 가능
- 암시적 커서의 속성
sql%rowcount, sql%found, sql%notfound
암시적 커서는 1개의 row만 fetch가 가능해서 0개면 `no data found`, 1개초과이면 `too many rows`라는 Exception이 발생한다.
이를 해결하기 위해 명시적 커서를 사용한다.
명시적 커서 생성
커서를 만드는 방법은 다음과 같다.
우선 어떤 커서를 사용하기 전에 select문을 던져서 확인해봐야 한다.
SELECT * FROM employees WHERE employee_id = 100;
--유일키 값이므로 암시적 커서 가능
SELECT * FROM employees WHERE department_id = 20;
--유일키 값이 아니라 명시적 커서를 생성해야 함
20번 부서에 대한 사원 정보를 출력해보자. 그럼 명시적 커서를 만들어 보자.
declare
/* 1. 커서 선언 */
cursor emp_cur is
SELECT last_name
FROM employees
WHERE department_id = 20;
v_name varchar2(30);
begin
/* 2. 커서 open: 메모리할당,parse,bind,execute,fetch */
open emp_cur;
/* 3. fetch: 출력하기 전, cursor에 있는 active set 결과를 변수에 로드하는 단계 */
loop --loop가 없으면 1건만 출력됌
fetch emp_cur into v_name;
exit when emp_cur%notfound; --커서 이름이 반드시 들어가야함
dbms_output.put_line(v_name);
end loop;
/* 4. 커서 close: 메모리 해지 */
close emp_cur;
end;
/
record type을 선언 시에는 주의해야 한다. 명시적 커서에서 사용하는 record type은 table이름%rowtype 대신 cursor이름%rowtype를 사용해야 한다.
declare
/* 1. 커서 선언 */
cursor emp_cur is
SELECT *
FROM employees
WHERE department_id = 20;
v_rec emp_cur%rowtype; --커서 이름을 통해서 record 타입을 만듦(그러므로 join시에도 상관없다)
begin
/* 2. 커서 open: 메모리할당,parse,bind,execute,fetch */
open emp_cur;
/* 3. fetch: 출력하기 전, cursor에 있는 active set 결과를 변수에 로드하는 단계 */
loop --loop가 없으면 1건만 출력됌
fetch emp_cur into v_rec;
exit when emp_cur%notfound; --커서 이름이 반드시 들어가야함
dbms_output.put_line(v_rec.last_name);
end loop;
/* 4. 커서 close: 메모리 해지 */
close emp_cur;
end;
/
이제 명시적 커서를 쉽게 구현할 수 있는 방법을 알아보자. for문을 사용하면 1. record변수는 커서를 기반으로 자동으로 생성되고, 2. open, fetch, close를 자동으로 해준다.
declare
cursor emp_cur is
SELECT *
FROM employees
WHERE department_id = 20;
begin
for emp_rec in emp_cur loop --in뒤에는 커서 이름이 들어가야한다.
dbms_output.put_line(emp_rec.last_name);
end loop;
end;
/
for문을 응용해서 만든 것이지 for문은 아니다. 위의 emp_rec은 for문안에서만 사용할 수 있고, in뒤에는 cursor이름이 나와야 한다. 또한 active set결과가 없다면 for loop는 돌아가지 않으므로, 매뉴얼하게(4단계) 만들어야 한다. 주의하자!
명시적 커서 속성
명시적커서이름%rowcount
명시적커서이름%found
명시적커서이름%notfound
명시적커서이름%isopen: 명시적커서가 open이 되어있으면 true, 아니면 false
Parameter를 갖는 Cursor
실행계획을 공유하기 위해 parameter를 이용한다. 아래 문장을 보고 왜 parameter를 갖는 cursor를 사용하는지 알아보자.
declare
cursor parm_cur_80 is
SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 80
AND job_id = 'SA_MAN';
cursor parm_cur_50 is
SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 50
AND job_id = 'ST_MAN';
v_rec1 parm_cur_80%rowtype;
begin
open parm_cur_80;
loop
fetch parm_cur_80 into v_rec1;
exit when parm_cur_80%notfound;
dbms_output.put_line(v_rec1.last_name);
end loop;
close parm_cur_80;
dbms_output.new_line;
for v_rec2 in parm_cur_50 loop
dbms_output.put_line(v_rec2.last_name);
end loop;
end;
/
실행계획을 일부러 분리시키지 않은 경우일 때, 같은 실행계획을 가지지만 상수값이 달라 2번의 logic을 구현해야하고 실행계획을 공유하지 못한다. 같이 만들면 메모리를 절약할 수 있지 않을까?
실행계획을 공유하기 위해 parameter를 이용한다.
declare
cursor parm_cur(p_id number, p_job varchar2) is --형식매개변수이기에 size 설정하면 error
SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = p_id
AND job_id = p_job;
v_rec1 parm_cur%rowtype;
begin
open parm_cur(80,'SA_MAN');
loop
fetch parm_cur into v_rec1;
exit when parm_cur%notfound;
dbms_output.put_line(v_rec1.last_name);
end loop;
close parm_cur;
dbms_output.new_line;
for v_rec2 in parm_cur(50,'ST_MAN') loop
dbms_output.put_line(v_rec2.last_name);
end loop;
end;
/
변수처리(bind단계)를 하기 전에 실행계획을 만들었다(parse단계). 어떻게 만들었을까? Optimizer는 분포도가 고르다라는 전제조건으로 실행계획을 만들었다. (통계수집된 결과를 히스토그램을 보고 분포도를 파악함) 만약 분포가 고르지 않으면 실행계획을 분리해서 만드는 것이 효율적이다.
CURSOR에서의 TUNING
declare
cursor sal_cur is
SELECT e.employee_id, e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20;
begin
for emp_rec in sal_cur loop
dbms_output.put_line(emp_rec.last_name);
dbms_output.put_line(emp_rec.salary);
dbms_output.put_line(emp_rec.department_name);
dbms_output.new_line;
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = emp_rec.employee_id;
end loop;
rollback;
end;
/
UPDATE시에 발생하는 I/O(BY INDEX SCAN)를 BY ROWID SCAN으로 유도한다면 조금이라도 I/O를 줄일 수 있다. 그래서 employee_id 대신에 rowid를 CURSOR 선언 시점에서 들고와서 사용해보자.
SELECT e.rowid id, e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20;
declare
cursor sal_cur is
SELECT e.rowid, e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20;
begin
for emp_rec in sal_cur loop
dbms_output.put_line(emp_rec.last_name);
dbms_output.put_line(emp_rec.salary);
dbms_output.put_line(emp_rec.department_name);
dbms_output.new_line;
UPDATE employees
SET salary = salary * 1.1
WHERE rowid = emp_rec.id;
end loop;
rollback;
end;
/
FOR UPDATE
만약 Cursor 선언 시에 아래와 같이 되어있을 때(커서에 employee_id와 rowid를 SELECT하지 않았을 경우) UPDATE문 어떻게 구성해야할까?
SELECT e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20;
declare
cursor sal_cur is
SELECT e.rowid, e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20
FOR UPDATE; --커서가 open시점에 lock을 걸고 rowid값을 가지게 됌
begin
for emp_rec in sal_cur loop
dbms_output.put_line(emp_rec.last_name);
dbms_output.put_line(emp_rec.salary);
dbms_output.put_line(emp_rec.department_name);
dbms_output.new_line;
UPDATE employees
SET salary = salary * 1.1
WHERE current of sal_cur; --해당 커서의 rowid를 바탕으로 update문을 수행함
end loop;
rollback;
end;
/
current of 는 UPDATE와 DELETE사용한다. 그리고 for update를 먼저 선언해주어야 사용할 수 있다. 하지만 조건을 걸고 수정해야 할 시에는 rowid를 직접 사용해서 update하는 것이 좋다. 왜냐하면 for update는 open시점에 전체 row를 lock을 걸기 때문에 lock contention이 더 발생할 수 있다. (만약 모든 row를 수정한다면 OK.)
주의
자 이제 update가 되었는지 확인해보자
declare
cursor sal_cur is
SELECT e.rowid, e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20
FOR UPDATE;
v_sal number;
begin
for emp_rec in sal_cur loop
dbms_output.put_line(emp_rec.last_name);
dbms_output.put_line(emp_rec.salary);
dbms_output.put_line(emp_rec.department_name);
dbms_output.new_line;
UPDATE employees
SET salary = salary * 1.1
WHERE current of sal_cur;
end loop;
SELECT salary
INTO v_sal
FROM employees
WHERE last_name = 'Hartstein';
dbms_output.put_line(v_sal);
rollback;
end;
/
확인해보니 업데이트가 되지 않았다. 그 이유는 join이 된 결과를 들고 올때 각 테이블(employees, departments)의 rowid를 다 들고 오기 때문에 (e.rowid, d.rowid) UPDATE ... current of를 수행하지 않는다. 그래서 FOR UPDATE 뒤에 참조해야 할 테이블에 있는 컬럼을 반드시 명시해야 한다.
declare
cursor sal_cur is
SELECT e.rowid, e.last_name, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = 20
AND d.department_id = 20
FOR UPDATE OF e.salary; --employees 테이블에 있는 아무 컬럼이나 사용 가능
--lock이 걸려야 할 대상 테이블에 대한 참조 대상임
v_sal number;
begin
for emp_rec in sal_cur loop
dbms_output.put_line(emp_rec.last_name);
dbms_output.put_line(emp_rec.salary);
dbms_output.put_line(emp_rec.department_name);
dbms_output.new_line;
UPDATE employees
SET salary = salary * 1.1
WHERE current of sal_cur;
end loop;
SELECT salary
INTO v_sal
FROM employees
WHERE last_name = 'Hartstein';
dbms_output.put_line(v_sal);
rollback;
end;
/
참고, for update 시 transaction이 걸려있다면 LOCK CONTENTION이 발생한다. 그 때 얼마나 걸려있는지 대기 시간을 정해줄 수 있다.
FOR UPDATE NOWAIT;
--기다리지 않고 바로 나옴
FOR UPDATE OF e.employee_id wait 3;
--3초만 기다리고 나옴
문맥전환 성능 항샹
PLSQL 사용시 SQL엔진을 사용해야 할 때 문맥전환이 발생한다. 아래 문장을 개선시키면서 알아보자.
declare
cursor emp_cur is
SELECT * FROM employees WHERE department_id = 20;
v_rec emp_cur%rowtype;
begin
open emp_cur;
fetch emp_cur into v_rec;
dbms_output.put_line(v_rec.last_name);
fetch emp_cur into v_rec;
dbms_output.put_line(v_rec.last_name);
close emp_cur;
end;
/
일단 이 문장은 같은 fetch문을 2번 사용하였기에 LOOP를 사용하여 문장을 심플하게 만들었다.
declare
cursor emp_cur is
SELECT * FROM employees WHERE department_id = 20;
v_rec emp_cur%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into v_rec; --sql엔진이 만들어 놓은 active set을 plsql엔진으로 넘겨서 변수에 fetch 시킴
exit when emp_cur%notfound;
dbms_output.put_line(v_rec.last_name);
end loop;
close emp_cur;
end;
/
여기서 fetch시점에서 PLSQL엔진과 SQL엔진사이에서 문맥 전환이 발생하였다. 20번 부서의 사원이 2건이지만 혹시 2건이 아니라 수천건의 데이터가 있었다면 문맥전환이 과도하게 일어나서 성능상 문제를 일으킬 수 있다. 그래서 한 건씩 데이터를 fetch시키는 것이 아니라 배열과 BULK COLLECT INTO를 사용하여 fetch하고자 하는 모든 데이터를 한번에 fetch시켜보자.
BULK COLLECT INTO (SELECT 시)
우선 변수를 Scalar data type이나 record type으로 선언하지 말고 배열로 만들어서 한번에 많은 데이터를 받을 수 있도록 한다. 그 다음 BULK COLLECT INTO절에 그 배열을 사용하여 문맥 전환을 줄이도록 한다.
SELECT절의 BULK COLLECT INTO
declare
type tab_type is table of employees%rowtype;
v_tab tab_type;
begin
SELECT *
BULK COLLECT INTO v_tab
FROM employees
WHERE department_id = 20;
for i in v_tab.first..v_tab.last loop
dbms_output.put_line(v_tab(i).last_name);
end loop;
end;
/
MANUAL한 BULK COLLECT INTO
declare
cursor emp_cur is
SELECT * FROM employees WHERE department_id = 20;
type tab_type is table of emp_cur%rowtype;
v_tab tab_type;
begin
open emp_cur;
fetch emp_cur bulk collect into v_tab;
close emp_cur;
for i in v_tab.first..v_tab.last loop
dbms_output.put_line(v_tab(i).last_name);
end loop;
end;
/
RETURNING절의 BULK COLLECT INTO
RETURNING INTO 절은 1건의 ROW만 받을 수 있다. 여러 건의 ROW를 받을려면 BULK COLLECT INTO를 사용해야 한다.
declare
type name_sal_rec is record(
name varchar2(30),
sal number);
type tab_type is table of name_sal_rec;
v_tab tab_type;
begin
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20
RETURNING last_name, salary
BULK COLLECT INTO v_tab;
for i in v_tab.first..v_tab.last loop
dbms_output.put_line(v_tab(i).name || ' ' || v_tab(i).sal);
end loop;
end;
/
만약 1건의 데이터를 받으려면 오류가 나니 주의하자. 1건의 데이터면 암시적커서에서 사용했듯이 Scalar data type의 변수를 선언하고 RETURNING INTO를 사용해야 한다.
--10번 부서의 사원은 1명이다.
declare
type name_sal_rec is record(
name varchar2(30),
sal number);
v_rec name_sal_rec;
begin
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10
RETURNING last_name, salary
INTO v_rec;
dbms_output.put_line(v_rec.name || ' ' || v_rec.sal);
end;
/
FORALL (DML 시)
FORALL문 반복문이 아니라, 배열 변수안에 있는 값을 가지고 delete작업을 수행 시 문맥 전환을 줄이게 한다. 우선 프로그램에서 단순한 DELETE 작업을 해보며 FORALL을 사용해보겠다.
drop table emp purge;
CREATE TABLE emp
AS SELECT *
FROM employees;
begin
delete from emp
where department_id = 10;
delete from emp
where department_id = 20;
delete from emp
where department_id = 30;
rollback;
end;
/
위 문장은 실행계획을 공유하지 못하니 변수처리를 하여 실행계획을 공유하도록 해보자
declare
type numlist is table of number;
v_num numlist := numlist(10,20,30);
begin
delete from emp
where department_id = v_num(1);
dbms_output.put_line(sql%rowcount || ' rows deleted');
delete from emp
where department_id = v_num(2);
dbms_output.put_line(sql%rowcount || ' rows deleted');
delete from emp
where department_id = v_num(3);
dbms_output.put_line(sql%rowcount || ' rows deleted');
rollback;
end;
/
nested table 배열을 선언하여 실행계획을 공유하게 하였다. 하지만 같은 문장이 많으므로 이를 개선해보자.
declare
type numlist is table of number;
v_num numlist := numlist(10,20,30);
begin
for i in v_num.first..v_num.last loop
delete from emp
where department_id = v_num(i);
dbms_output.put_line(sql%rowcount || ' rows deleted');
end loop;
rollback;
end;
/
DML작업도 마찬가지로 실행계획을 공유했지만 plsql엔진과 sql엔진 사이에서 3번의 문맥전환이 발생한다. FORALL을 사용하여 문맥전환을 줄여보자.
declare
type numlist is table of number;
v_num numlist := numlist(10,20,30);
begin
forall i in v_num.first..v_num.last
delete from emp
where department_id = v_num(i);
for i in v_num.first..v_num.last loop
dbms_output.put_line(sql%bulk_rowcount(i) || ' rows deleted');
--1번방의 배열값으로 삭제된 건수, 2번방의 배열값으로 삭제된 건수...
end loop;
rollback;
end;
/
배열 안의 값을 가지고 한번에 DELETE작업을 수행하여서 문맥전환이 1번 발생했다. 또한, 한번에 몇 건을 처리한지 알기 위해서는 bulk_rowcount()의 속성을 사용해야 한다.
declare
type numlist is table of number;
v_num numlist := numlist(10,20,30);
begin
forall i in v_num.first..v_num.last
delete from emp
where department_id = v_num(i);
for i in v_num.first..v_num.last loop
dbms_output.put_line(sql%bulk_rowcount(i) || ' rows deleted');
end loop;
rollback;
end;
/
UPDATE 작업 시에도 위와 비슷하게 수행하면 된다.
declare
type numlist is table of number;
v_num numlist := numlist(10,20,30);
begin
forall i in v_num.first..v_num.last
update emp
set salary = salary * 1.1
where department_id = v_num(i);
dbms_output.put_line(sql%rowcount); --전체 건 수가 나옴
for i in v_num.first..v_num.last loop
dbms_output.put_line(sql%bulk_rowcount(i) || ' rows deleted');
end loop;
rollback;
end;
/
Exception(예외처리)
- 실행중에 발생한 pl/sql 오류이다.
- 오류에 따른 exception처리를 각각 해야 한다.
- 오류 번호 대신 오류 이름으로 exception처리를 해주어야 한다.
- oracle에 의해 암시적(자동)으로 발생할 때, 유저가 의도하여 프로그램에 의해 명시적으로 발생시키고 싶을 때 exception 처리를 한다.
없는 사원번호를 입력하여서 예외사항을 발생시켜보겠다.
declare
v_rec employees%rowtype;
begin
SELECT *
INTO v_rec
FROM employees
WHERE employee_id = 300;
dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
end;
/
ORA-01403(단일행 SELECT 데이터를 반환하지 않을 때, 없는 배열 요소를 참조하려고 할 때 발생하는 오류)가 발생하였다. 오류 번호에 따른 이름으로 exception처리를 해준다.
declare
v_rec employees%rowtype;
begin
SELECT *
INTO v_rec
FROM employees
WHERE employee_id = 300;
dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
exception
when no_data_found then --오류 번호(ORA-01403)에 따른 이름(no data found)
dbms_output.put_line('해당 사원은 존재하지 않습니다.');
end;
/
다른 오류를 발생시켜 exception처리를 해보자.
declare
v_rec employees%rowtype;
begin
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 20;
dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
end;
/
오류에 따른 exception처리를 추가해주어야 한다.
declare
v_rec employees%rowtype;
begin
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 20;
dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
exception
when no_data_found then
dbms_output.put_line('해당 사원은 존재하지 않습니다.');
when too_many_rows then
dbms_output.put_line('부서에 사원들은 여러명이니 명시적커서를 사용');
end;
/
begin절에서 오류가 발생하면 exception으로 trap이 발생한다. 공통적으로 수행하고 싶은 logic이 있다면 sub-block에서 exception처리를 해주고 해당 logic은 main-block에서 수행하도록 한다.
예외처리에서의 transaction
Transaction가 발생했을 때 에러로 비정상적인 종료가 된다면 transaction은 자동 rollback이 된다.
SELECT salary
FROM employees
WHERE department_id = 20;
declare
v_rec employees%rowtype;
begin
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;
--오류발생으로 자동 rollback
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 20;
dbms_output.put_line(v_rec.last_name);
rollback;
end;
/
오류가 발생했지만 exception handling으로 transaction은 살아있게 된다. 그래서 TCL문을 사용하여 transaction을 끝내주어야 한다.
SELECT salary
FROM employees
WHERE department_id = 20;
declare
v_rec employees%rowtype;
begin
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;
--오류발생으로 자동 rollback
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 20;
dbms_output.put_line(v_rec.last_name);
exception
when too_many_rows then
rollback;
dbms_output.put_line('여러 건의 row를 fetch할 수 없습니다.');
end;
/
에러 확인(sqlcode/sqlerrm)
예외처리 시 어떤 에러가 발생할지 예측하는 것은 어렵다. sqlcode와 sqlerrm를 사용하여 어떤 에러가 발생했는지를 알 수 있다.
sqlcode의 return값에 따른 상황은 다음과 같다.
sqlcode: 0 정상적 수행
sqlcode: 1 유저가 정의한 예외사항
sqlcode: 100 no data found
sqlcode: 음수 error 발생
아래 문장을 보고 확인해보자.
declare
v_rec employees%rowtype;
begin
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 0;
dbms_output.put_line(v_rec.last_name);
exception
when too_many_rows then
rollback;
dbms_output.put_line('여러 건의 row를 fetch할 수 없습니다.');
when others then
dbms_output.put_line('에러 발생');
dbms_output.put_line(sqlcode);
--오류 번호를 return
dbms_output.put_line(sqlerrm);
--오류 코드+메시지를 return
end;
/
오류 이름 생성
오라클은 오류 번호는 있지만 오류 이름이 없다. 그럴 경우에는 직접 만들어 주어야한다. 아래의 문장을 보고 이해해보자
begin
DELETE FROM departments
WHERE department_id = 20;
end;
/
오류 보고:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
ORA-06512: at line 2
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause: attempted to delete a parent key value that had a foreign
dependency.
*Action: delete dependencies first then parent or disable constraint.
에러가 발생하면서 프로그램이 비정상적으로 종료되었다. when others then절을 사용해서 프로그램을 정상적으로 종료되도록 해보자
begin
DELETE FROM departments
WHERE department_id = 20;
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
/
프로그램은 정상적으로 종료됐지만 해당 에러에 대해서만 예외처리를 한 것은 아니다. ORA-02292 오류가 발생했을 때는 따로 예외처리를 해주려면 따로 exception 이름을 만들어서 제어해야 한다. 그래서 exception 이름을 만들어 주어서 예외처리를 해보자.
declare
/* 1. exception 이름 선언 */
pk_error exception;
/* 2. 오류 번호와 exception 이름을 연결 */
pragma exception_init(pk_error,-2292); --선행되는 0은 안써도 무방함
begin
DELETE FROM departments
WHERE department_id = 20;
exception
/* 3. 오류 이름으로 예외처리 */
when pk_error then
dbms_output.put_line('이 값을 참조하는 row들이 있습니다.');
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
/
사용자가 정의한 예외사항
에러는 발생하지 않았지만 예외처리를 하고 싶은 경우 사용자가 직접 정의할 수 있다. 예를들어 조회하면 안되는 사원번호(CEO 등)을 수행한다는지, 작업수행을 하지 않았는데 오류가 발생하지 않은 경우에 사용한다. 이제 아래의 문장을 보고 이해해보자
begin
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 300;
end;
/
300번 사원은 없다. 하지만 오류는 발생하지 않았다. 이 전에 배운 암시적 커서의 속성을 사용하여 수정작업이 되었는지 알아보자.
begin
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 300;
if sql%notfound then
dbms_output.put_line('수정된 값이 없습니다.');
end if;
end;
/
하지만 수정작업을 했지만 영향을 입은 row가 없을 경우 exception처리를 하려한다. raise문을 사용해보자.
declare
/* 1. exception 이름 선언 */
e_invalid exception;
begin
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 300;
if sql%notfound then
/* 2. raise문 사용 */
raise e_invalid;
end if;
exception
/* 3. 오류 이름으로 예외처리 */
when e_invalid then
dbms_output.put_line('수정된 데이터가 없습니다.');
end;
/
raise문을 만나는 순간 바로 exception절로 trap시켜 예외처리를 하고 프로그램을 정상종료 시킨다.
사용자가 정의한 비정상적인 종료
raise_application_error의 procedure를 만나면 바로 비정상적인 종료를 한다.
begin
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 300;
if sql%notfound then
raise_application_error(-20000,'수정된 데이터가 없습니다');
--숫자는 -20000부터 -20999까지 사용 가능
--message는 2000글자까지 사용 가능
end if;
end;
/
raise_application_error은 exception절에도 사용할 수 있다. 또한 true/false값을 넣어 사용자가 설정한 메세지와 oracle의 오류번호를 함께 체크할 수 있다.
--false 일 경우(default값)
declare
v_rec employees%rowtype;
begin
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 300;
exception
when no_data_found then
raise_application_error(-20000,'수정된 데이터가 없습니다');
end;
/
ERROR at line 1:
ORA-20000: 수정된 데이터가 없습니다
ORA-06512: at line 12
--true 일 경우
declare
v_rec employees%rowtype;
begin
SELECT *
INTO v_rec
FROM employees
WHERE department_id = 300;
exception
when no_data_found then
raise_application_error(-20000,'수정된 데이터가 없습니다',true);
end;
/
ERROR at line 1:
ORA-20000: 수정된 데이터가 없습니다
ORA-06512: at line 12
ORA-01403: no data found
Procedure
들어가기 앞서, 익명블록구조의 단점을 다시 알아보자.
1. reparsing 불가
2. share 불가
3. global variable 생성 불가
익명블록구조는 object가 아니기에 제약이 생긴다. 이런 단점을 해결한 것이 object 단위 프로그램이다. 테이블을 생성하는 것 처럼 oracle db안에 객체로 저장할 수 있다. 그 중 하나인 Procedure을 만들어 보자. 만들기 전에 object를 만들 수 있는 권한이 있는지 확인해야 한다.
select * from user_sys_privs;
-- user가 가지고 있는 권한 확인
select * from user_tab_privs;
-- 내가 부여한/부여받은 object 권한 같이 볼 수 있음
select * from session_privs;
-- 권한 리스트 확인, CREATE PROCEDURE 권한만 있으면 함수, 패키지, procedure 만들 수 있음
Procedure에는 3가지 형식매개변수의 모드가 있다.
- IN MODE
- OUT MOD
- IN/OUT MODE
IN MODE
바인드 변수와 다르게 입력값을 처리하는 모드이다. 다음 문장을 보고 IN MODE에 대해 이해해보자.
create or replace procedure emp_proc(p_id in number)
--형식매개변수 선언시 SIZE 선언하면 안된다. (선언 시 complie error 발생)
--mode의 default값은 in이므로 in을 생략해도 무방하다.
--bind 변수 사용 불가
--p_id는 local variable 처럼 작동하며 프로그램 내에서 상수로 사용되기에 초기값을 반드시 입력해주어야 한다.
--or replace은 기존에 같은 procedure가 있을 경우, 이미 있는 걸 지우고 새로 생성하는 것을 의미한다.
is
--익명 블록의 declare절과 같은 기능
--declare절은 필수가 아니지만, is절은 필수
--is 대신 as 사용 가능
v_rec employees%rowtype;
begin
--p_id := 200; -Error: 한번 받은 값에 대해서만 사용(상수이기 때문)
SELECT *
INTO v_rec
FROM employees
WHERE employee_id = p_id;
dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
exception
when no_data_found then
dbms_output.put_line(p_id || ' 사원은 존재하지 않습니다.');
end;
/
만든 프로그램을 수행해보자. exec emp_proc(실질매개변수) 와 같이 사용하면 된다.
exec emp_proc(100)
100 King
실질매개변수(100)를 형식매개변수(p_id)에 집어넣었다.
exec emp_proc(300)
300 사원은 존재하지 않습니다.
exec emp_proc()
BEGIN emp_proc(); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'EMP_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IN MODE는 상수로 동작해야 하기 때문에 초기값을 넣어주어야 한다. 그래서 오류가 났다. 만약 선언시에 default값을 선언하였다면 error가 발생하지 않았을 것이다. default값은 p_id in number default 0과 같이 사용하면 된다.
begin
emp_proc(100); -- exec 명령어는 제외
end;
/
위와 같이 프로그램에서 procedure을 호출 할 수도 있다.
OUT MODE
프로그램 내에서 특정 변수를 프로그램 바깥으로 전달하는 기능이다. IN MODE는 특정 값을 넣어줘야 상수로 작동하지만 OUT MODE는 변수로 동작하기에 값을 선언된 변수를 받아서 그 변수에 RETURN해준다.
create or replace procedure emp_proc(
p_id in number default 0,
p_name out varchar2,
p_sal out number)
--out mode: 변수를 밖에서도 쓸 수 있게 만듦
is
begin
select last_name, salary
into p_name, p_sal
-- 이 두 변수를 프로그램 외부로 전달하고 싶은 경우 사용(cf. 익명 블록은 bind variable을 쓰면 됨)
from employees
where employee_id = p_id;
dbms_output.put_line(p_name || ' ' || p_sal);
exception
when no_data_found then
dbms_output.put_line(p_id || ' 사원은 존재하지 않습니다');
end;
/
프로시저를 생성했으면 실행해보자.
var b_name VARCHAR2(30)
var b_sal NUMBER
--바깥에서 변수를 받을 때 bind variable 사용(실질매개변수에 상수 말고 bind variable 입력하면 된다.)
exec emp_proc(200, :b_name, :b_sal) -- ()안의 변수는 실질매개변수
--200(IN MODE)을 프로그램에 집어넣어서, p_name, p_sal(OUT MODE)을 추출한 다음, 밖에서 :b_name, :b_sal로 받음
print b_name b_sal
SELECT * FROM employees WHERE salary >b_sal;
출력을 익명블록해서도 해보자.
declare
v_id number := 100;
v_name varchar2(30);
v_sal number;
begin
emp_proc(v_id, v_name, v_sal);
dbms_output.put_line(v_id || ' ' || v_name || ' ' || v_sal);
end;
/
IN OUT MODE
IN MODE와 OUT MODE의 혼합 기능이다. 프로그램을 보고 이해해보자.
create or replace procedure format_phone
(p_phone_no in out varchar2)
is
begin
p_phone_no := substr(p_phone_no, 1, 3) || '-' || substr(p_phone_no, 4, 4) || '-' || substr(p_phone_no, 8);
dbms_output.put_line(p_phone_no);
end;
/
IN OUT MODE로 간단한 프로그램을 만들었다. 실행을 시켜보자.
var b_phone_no varchar2(30)
exec :b_phone_no := '01012345678'
print b_phone_no
B_PHONE_NO
-----------
01012345678
프로그램을 호출하면 위 형식이 아래처럼 바뀐다.
exec format_phone(:b_phone_no)
print b_phone_no
B_PHONE_NO
---------------
010-1234-5678
아래의 형태처럼 쓰는 것을 주의하자
exec format_phone('01012345678')
--error: 상수값은 받을 수 없다. IN OUT MODE의 실질매개변수는 초기값이 있는 변수형태이여야 한다.
Return
Procedure에서 RETURN 문을 만나는 순간 무조건 프로그램 종료된다. 이때 종료는 정상적인 종료이다. (참고로 함수의 return은 종료가 아니라 값 반환이라는 것을 알아두자)(문제 37번)
Procedure 정보 확인
DESC로 procedure의 정보(변수의 이름, 타입, 모드)를 확인할 수 있다.
desc emp_proc
Source 확인
작성한 procedure의 소스는 다음과 같이 확인할 수 있다.
SELECT text
FROM user_source
WHERE NAME = 'EMP_PROC';
user_source 라는 디렉토리 안에 있는 우리가 만든 프로시저를 확인 가능하다.
Procedure error 확인
프로시저 문 에러가 났을 때는 다음과 같은 코드만 발생하지 정확한 오류를 알려주지 않는다.
Warning: Procedure created with compilation errors
그래서 아래 코드로 error를 확인해야 한다. 이는 익명블록구조에서가 아닌 오브젝트 단위 프로그램에서만 사용할 수 있다. 오류 발생시에는 컴파일이 실패가 되든 실행이 되든 소스코드는 저장된다. 성공적으로 실행 됐을 때 db에 parse된 code값이 저장되고, 실패했을 때는 parse된 code값은 저장이 안된다. error 확인 코드는 다음과 같다.
show error
참고로 만약 sql developer를 사용 중이라면 컴파일 오류가 발생했을 때, 왼쪽의 사용중인 user의 프로시저에 들어가보면 x 표가 되어 있다. 표시된 프로시저를 클릭하면 프로시저 편집창이 뜨는데 톱니바퀴 아이콘 누르면 compiling이 된다.
Procedure 삭제
삭제는 drop procedure을 사용하여 할 수 있다.
drop procedure emp_proc;
Procedure 권한(간접/직접 액세스)
다른 유저에게 Procedure에 대한 권한을 부여하는 방법은 다음과 같다.
간접 ACCESS
grant execute on query_emp to happy;
--grant execute on 프로시저이름 to 유저;
select * from user_tab_privs;
--권한을 부여 받았는 지 확인 가능
exec hr.query_emp(101)
/*
간접 ACCESS: 다른 유저가 hr 유저의 procedure 사용 가능 (ex. 은행 창구 직원)
보통 간접 ACCESS는 보안 문제 등으로 procedure를 통해 간접 access 유도한다.
사용시, 사용자명(hr) 꼭 명시해주어야 한다.
*/
직접 ACCESS
grant select on employees to happy;
--table에 대한 select문의 직접 access 권한 부여
grant select, insert, update, delete on employees to happy;
--select문과 dml문의 직접 access 권한 부여
revoke execute on query_emp from happy;
--권한 회수(Obj.에 대한 execute 권한을 revoke)
Procedure Exception의 3가지 Senario
Procedure에서 exception 처리를 어디에 하는 가에 따라 출력물은 달라진다. 3가지 시나리오를 통해 확인해보자
--환경 설정
drop table emp purge;
drop table dept purge;
create table emp
as select * from employees;
create table dept
as select * from departments;
alter table emp add constraint empid_pk primary key(employee_id);
alter table dept add constraint deptid_pk primary key(department_id);
alter table dept
add constraint dept_mgr_id_fk
foreign key (manager_id)
references emp(employee_id);
select * from user_constraints where table_name in ('EMP','DEPT');
select * from user_cons_columns where table_name in ('EMP','DEPT');
1번 시나리오
Exception절을 쓰지 않았을 경우이다.
create or replace procedure add_dept (
p_name in varchar2,
p_mgr in number,
p_loc number)
is
v_max number;
begin
SELECT max(department_id)
INTO v_max
FROM dept;
INSERT INTO dept (
department_id, department_name, manager_id,location_id)
VALUES(v_max+10, p_name, p_mgr, p_loc);
end add_dept;
/
begin
add_dept('경영지원',100,1800);
add_dept('데이터분석',99,1800);
add_dept('자금관리',101,1500);
end;
/
select * from dept;
begin
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.DEPT_MGR_ID_FK) violated - parent key not
found
ORA-06512: at "HR.ADD_DEPT", line 12
ORA-06512: at line 3
99번 사원이 없어서 제약조건을 만족하지 못한다.(parent key not found) 그렇게 되면 출력 한 곳과 호출 된 곳에 exception이 없어서 자동 롤백된다.
2번 시나리오
호출한 곳에만 Exception문을 작성하였다.
create or replace procedure add_dept (
p_name in varchar2,
p_mgr in number,
p_loc number)
is
v_max number;
begin
SELECT max(department_id)
INTO v_max
FROM dept;
INSERT INTO dept (
department_id, department_name, manager_id,location_id)
VALUES(v_max+10, p_name, p_mgr, p_loc);
end add_dept;
/
begin
add_dept('경영지원',100,1800);
add_dept('데이터분석',99,1800);
add_dept('자금관리',101,1500);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
select * from dept;
첫번째 구문(경영지원)은 들어가고 문제 있는 구문(데이터분석) 이후부터는 들어가지 않았다. 호출 하는 곳에서 exception handling을 했기 때문에 ‘경영지원’ 부서가 insert 된 것이다. 그리고 transaction이 계속 진행중이다.
3번 시나리오
main procedure에 exception절 표현하였다. 호출하는 곳에 표현(2번 시나리오)하지 말고 호출되는 곳에 exception절을 표현하는 것이 좋다. 이유는 아래를 보고 이해를 하자.
create or replace procedure add_dept (
p_name in varchar2,
p_mgr in number,
p_loc number)
is
v_max number;
begin
SELECT max(department_id)
INTO v_max
FROM dept;
INSERT INTO dept (
department_id, department_name, manager_id,location_id)
VALUES(v_max+10, p_name, p_mgr, p_loc);
exception
when others then
dbms_output.put_line('error : ' || p_name);
dbms_output.put_line(sqlerrm);
end add_dept;
/
begin
add_dept('경영지원',100,1800);
add_dept('데이터분석',99,1800);
add_dept('자금관리',101,1500);
end;
/
select * from dept;
호출 되는 곳에서 예외처리를 했기 때문에 호출하는 곳의 프로그램은 모두 정상적으로 실행이 되어 insert작업은 예외처리 되지 않은 곳을 제외하고 모두 이루어진다. (호출 되는 곳에서 exception처리를 하는 것이 좋다.)
Procedure와 Function의 차이
Procedure와 Function은 호출하는 방식과 Return값의 유무에 따라 다르다.
Procedure은 sql문장에서 호출 불가하다.
Function은 SELECT문에 사용하거나 변수에 할당을 해서 사용해야 한다.(표현식의 일부로서 사용)
또한 Function은 RETRUN값이 반드시 필요한 반면, Procedure은 필요 없다.
호출방식을 어떻게 해야 할지에 따라 Procedure로 해야할지 Function으로 해야할지 정한다.
Procedure는 매번 수행할 때 마다 매번 comfile해야 하는데 호출단위프로그램을 만들면 그러지 않아도 된다. 이 부분에서는 호출방식의 함수가 효율적이다.
Function
함수는 RETURN을 반드시 써야하고 RETURN 할 타입을 반드시 써주어야한다. 하지만 RETURN 타입의 사이즈는 선언하면 안된다. (cf. Procedure에서의 RETURN은 프로그램이 종료된다.) 보통 함수는 IN MODE를 사용한다. RETURN을 해주니 OUT MODE를 굳이 쓰지 않아도 된다.
create or replace function get_sal(
p_id in number)
return number --사이즈 선언, 세미콜론 사용하면 안됨
is
v_sal number := 0;
begin
SELECT salary
INTO v_sal
FROM employees
WHERE employee_id = p_id;
return v_sal;
--return 문을 여러 개 쓸 수 있지만, 그 중 하나만 return
exception
when no_data_found then
return v_sal;
end get_sal;
/
함수의 호출 방법
3가지 방법으로 함수를 실행해보겠다.
exec dbms_output.put_line(get_sal(100))
--표현식의 일부로서 호출(procedure는 이렇게 사용 못 함)
declare
v_sal number;
begin
v_sal := get_sal(100);
dbms_output.put_line(v_sal);
end;
/
SELECT employee_id, get_sal(employee_id)
FROM employees
WHERE employee_id = 100;
아래의 경우는 error가 발생한다. get_sal이 function이아니라 Procedure일 때는 error가 발생하지 않는다.
begin
get_sal(100);
end;
/
Package
Package는 관련성있는 서브프로그램(프로시저,함수), 변수(global), 타입을 모아놓은 프로그램이다. Package에서 선언한 변수를 글로벌 변수처럼 프로그램 밖에서 사용이 가능한 이점이 있다. Package는 크게 Spec(public)과 Body(private)로 이루어 진다. 다음 코드를 보면서 이해해보자
- Spec(public)
Package는 Spec을 먼저 만들어야 되고, 선언만 해야 한다. 그래서 begin절이 없이 is와 end로 끝난다. 그리고 Spec에서 만들어진 생성자들은 글로벌 생성자로 사용할 수 있다.
create or replace package comm_pkg
is
g_comm number := 0.1; --글로벌 생성자
procedure reset_comm(p_comm in number); --글로벌 생성자
--procedure 프로시져 이름 형식 매개변수
end comm_pkg;
/
exec dbms_output.put_line(comm_pkg.g_comm);
-- 글로벌 변수이기에 프로그램 바깥에서도 불러올 수 있다,
- body(private)
Package의 실제 소스는 body에 기록한다. 오픈시키지 말아야할 logic을 body에다가 기록한다. 왜냐하면 Spec에 선언하지 않아서 바깥에서 사용을 할 수 없기 때문이다.
create or replace package body comm_pkg
is
function validate_comm(v_comm in number)
return boolean
is
v_max_comm number;
begin
SELECT max(commission_pct)
INTO v_max_comm
FROM employees;
if v_comm > v_max_comm then
return FALSE;
else
return TRUE;
end if;
end validate_comm;
--validate_comm은 숨겨진 logic (바깥에서 사용 불가)
procedure reset_comm(p_comm in number)
is
begin
if validate_comm(p_comm) then
dbms_output.put_line('old : ' || g_comm);
g_comm := p_comm;
dbms_output.put_line('new : ' || g_comm);
else
raise_application_error(-20000,'invalid');
end if;
end reset_comm;
end comm_pkg;
/
패키지는 session에 종속된다. 그래서 현재 접속한 세션에서 사용하는 것은 다른 세션에 영향을 주지 않는다.
만약 body안의 reset_comm과 validate_comm의 위치를 바꾸게 되면 어떻게 될까? error가 발생한다. 왜냐하면 reset_comm은 validate_comm을 참조하고 있는데, reset_comm이 호출됐을 때 validate_comm이 선언 되지 않았기 때문이다. 그럴 경우에는 function return문을 reset_comm앞에 다가 두면 사용할 수 있다.
create or replace package body comm_pkg
is
function validate_comm(v_comm in number)
return boolean;
procedure reset_comm(p_comm in number)
is
begin
if validate_comm(p_comm) then
dbms_output.put_line('old : ' || g_comm);
g_comm := p_comm;
dbms_output.put_line('new : ' || g_comm);
else
raise_application_error(-20000,'invalid');
end if;
end reset_comm;
function validate_comm(v_comm in number)
return boolean
is
v_max_comm number;
begin
SELECT max(commission_pct)
INTO v_max_comm
FROM employees;
if v_comm > v_max_comm then
return FALSE;
else
return TRUE;
end if;
end validate_comm;
end comm_pkg;
/
--is절 위의 있는 함수와 리턴값만 복사해서 procedure 앞에 붙여넣으면 사용 가능하다.
Package 이점
그럼 패키지를 사용하는 이유는 무엇일까?
body안의 프로그램을 나누어서 사용해도 된다. 그럼에도 패키지를 사용하는 이유는? 유지관리의 편의성때문이다. 위 프로그램에서 예를 들면 reset_comm은 늘상 validate_comm을 참조하고 있다. 그래서 validate_comm에서 comfile error가 발생하면 반드시 reset_comm도 comfile error가 나니 2번 error가 나는걸 package로 사용하면 1번 error로 끝난다. 또한, 따로 사용했을 경우 g_comm을 local variable로만 사용해야하므로 global variable로 사용할 수 없는 불편함이 있다.
Overloading
Overloading을 이용하여 동일한 프로시저, 함수를 만들 수 있다. Overloading은 to_char를 예로 들어 생각하면 이해하기 쉽다. to_char를 풀어쓰면 to_char(입력변수, 입력변수)와 같이 쓸 수 있다. 첫번째 입력변수에는 날짜와 숫자, 2가지 타입이 올 수 있다. 하지만 함수나 프로시저를 만들 때 하나의 변수에 2가지 타입을 쓸 수 있었던가? 아니다. 이는 같은 이름으로 다른 타입의 패키지를 만들 수 있음을 뜻한다.
오버로드화를 하려면 다음의 조건을 만족해야 한다.
1. 형식 매개변수의 갯수가 달라야 한다.
2. 모드(in/out)가 달라야 한다.
3. Type이 달라야 한다.
이는 객체지향프로그램의 장점이다. 예로 들면 만약 새로운 타입의 변수가 등장했을 때, 새로운 패키지를 만들지 않고 기존의 패키지에 추가만하면 되는 이점이 있다. 프로그램을 보면서 이해해보자.
--Spec
create or replace package pack_over
is
type date_tab_type is table of date index by pls_integer;
type num_tab_type is table of number index by pls_integer;
procedure init(tab out date_tab_type, n number);
procedure init(tab out num_tab_type, n number);
end pack_over;
/
--Body
create or replace package body pack_over
is
procedure init(tab out date_tab_type, n number)
is
begin
for i in 1..n loop
tab(i) := sysdate;
end loop;
end;
procedure init(tab out num_tab_type, n number)
is
begin
for i in 1..n loop
tab(i) := i;
end loop;
end;
end pack_over;
/
만든 패키지를 사용해보자
declare
date_tab pack_over.date_tab_type;
num_tab pack_over.num_tab_type;
begin
pack_over.init(date_tab,5); --날짜타입이니 날짜타입의 procedure에서 돌아감
pack_over.init(num_tab,5); --숫자타입이니 숫자타입의 procedure에서 돌아감
for i in 1..5 loop
dbms_output.put_line(date_tab(i));
dbms_output.put_line(num_tab(i));
end loop;
end;
/
2018/07/17
1
2018/07/17
2
2018/07/17
3
2018/07/17
4
2018/07/17
5
init이라는 같은 이름의 procedure를 사용하여 date type의 변수와 number type의 변수를 처리할 수 있다.
Package cursor의 지속 상태
패키지에서의 커서는 close하기 전까지 open상태로 유지된다. 만약 cursor를 따로 개발할 일이 있다면 다음을 참고하여 개발하도록 하자.
CREATE OR REPLACE PACKAGE pack_cur
IS
PROCEDURE open;
PROCEDURE next(p_num number);
PROCEDURE close;
END pack_cur;
/
CREATE OR REPLACE PACKAGE BODY pack_cur
IS
--Private하게 cursor 선언
CURSOR c1 IS
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id DESC;
v_empno NUMBER;
v_ename VARCHAR2(10);
PROCEDURE open
IS
BEGIN
IF NOT c1%isopen then
OPEN c1;
dbms_output.put_line('c1 cursor open');
END IF;
END open;
/*
다른 프로그램에서는 cursor open 후 프로그램이 끝나면 cursor가 자동 close되지만,
패키지에서는 한번 open한 cursor는 close하기 전까지 닫혀지지 않는다.
*/
PROCEDURE next(p_num number)
IS
BEGIN
LOOP
EXIT WHEN c1%ROWCOUNT >= p_num;
FETCH c1 INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE('Id :' ||v_empno||' Name :' ||v_ename);
END LOOP;
END next;
--cursor를 닫히기 전까지 %rowcount는 누적으로 쌓인다.
PROCEDURE close
IS
BEGIN
IF c1%isopen then
close c1;
dbms_output.put_line('c1 cursor close');
END IF;
END close;
END pack_cur;
/
Package를 실행해보자
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE pack_cur.open
c1 cursor open
PL/SQL procedure successfully completed.
SQL> EXECUTE pack_cur.next(3)
Id :206 Name :Gietz
Id :205 Name :Higgins
Id :204 Name :Baer
PL/SQL procedure successfully completed.
SQL> EXECUTE pack_cur.next(6)
Id :203 Name :Mavris
Id :202 Name :Fay
Id :201 Name :Hartstein
/*
next(6)을 값을 넣으면 cursor포인터가 있어서 이전값(pack_cur.next(3)) 뒤의 있는 값부터 출력한다.
그리고, %rowcount의 누적된 값이 3이고 입력값은 6이니, 조건에 따라 3건만 출력하게 된다.
*/
PL/SQL procedure successfully completed.
SQL> EXECUTE pack_cur.close
c1 cursor close
PL/SQL procedure successfully completed.
위 cursor에서 문제가 있다. 바로 fetch 시점에서 문맥전환이 발생한다는 것이다. 문맥전환에 드는 비용을 줄이기 위해 BULK COLLECT INTO를 사용해서 fetch를 하려한다. 하지만 fetch하는 data의 양이 많다면 전부 불러들이는 것도 비용이니(만약 4g이상의 data를 fetch한다고 할 때 index by 배열의 용량을 초과하게 된다.) LIMIT를 써서 fetch의 양을 정해보자.
BULK - LIMIT
LIMIT는 SELECT절의 BULK COLLECT INTO에서는 불가능하고 fetch시에만 가능함을 유의하자.
CREATE OR REPLACE PACKAGE pack_cur
IS
PROCEDURE open;
PROCEDURE next(p_num number);
PROCEDURE close;
END pack_cur;
/
CREATE OR REPLACE PACKAGE BODY pack_cur
IS
CURSOR c1 IS
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id DESC;
PROCEDURE open
IS
BEGIN
IF NOT c1%isopen then
OPEN c1;
dbms_output.put_line('c1 cursor open');
END IF;
END open;
PROCEDURE next(p_num number)
IS
type tab_type is table of c1%rowtype;
v_tab tab_type;
BEGIN
IF c1%notfound THEN
dbms_output.put_line('데이터가 없습니다.');
return;
--함수와 다르게 procedure에서의 return은 procedure을 끝낸다.
ELSE
FETCH c1 BULK COLLECT INTO v_tab limit p_num;
--limit: bulk해야되는 low의 수
--fetch에서만 limit 가능
END IF;
FOR i IN v_tab.first..v_tab.last LOOP
DBMS_OUTPUT.PUT_LINE('Id :' ||v_tab(i).employee_id||' Name :' ||v_tab(i).last_name);
END LOOP;
END next;
PROCEDURE close IS
BEGIN
IF c1%isopen then
close c1;
dbms_output.put_line('c1 cursor close');
END IF;
END close;
END pack_cur;
/
Package 실행.
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE pack_cur.open
c1 cursor open
PL/SQL procedure successfully completed.
SQL> EXECUTE pack_cur.next(3)
Id :206 Name :Gietz
Id :205 Name :Higgins
Id :204 Name :Baer
PL/SQL procedure successfully completed.
SQL> EXECUTE pack_cur.next(3)
Id :203 Name :Mavris
Id :202 Name :Fay
Id :201 Name :Hartstein
PL/SQL procedure successfully completed.
SQL> EXECUTE pack_cur.close
c1 cursor close
PL/SQL procedure successfully completed.
Trigger
이벤트(DML)성 프로그램을 의미한다. 이떤 이벤트가 발생하면 자동으로 실행되는 프로그램이다. 트리거를 만들려면 시스템권한(session_privs테이블에서 create trigger)이 필요하다. 트리거에서 타이밍(before/after)과 DML행위, BEGIN절은 필수이다. 하지만 TCL(commit/rollback)은 사용할 수 없다.(사용하려면 독립 TRANSACTION 처리를 해야한다) 또한 선언을 해야할 필요가 있으면 DECLARE를 사용한다. 트리거를 확인하는 방법은 다음과 같다.
SELECT * FROM user_triggers;
트리거는 크게 문장트리거와 행트리거가 있다. 문장트리거는 영향을 입은 row가 입든 없든 무조건 돌아가는 트리거이다. 행트리거는 영향을 입은 row가 있을 경우에만 돌아간다. 간단한 문장을 보고 이해해보자.
BEFORE/AFTER의 문장트리거
SELECT * FROM session_privs; --시스템 권한 확인
drop table dept purge;
CREATE TABLE dept
AS SELECT * FROM departments;
desc dept
--BEFORE 문장 트리거
--insert on 하기 전에 trigger를 수행하라는 의미
CREATE OR REPLACE TRIGGER dept_before
BEFORE --프로그램 수행 시점을 의미
INSERT ON dept
BEGIN
dbms_output.put_line('insert하기 전에 문장트리거수행');
end;
/
--AFTER 문장트리거
--insert on한 후 trigger를 수행하라는 의미
CREATE OR REPLACE TRIGGER dept_after
AFTER --프로그램 수행 시점을 의미
INSERT ON dept
BEGIN
dbms_output.put_line('insert한 후에 문장트리거수행');
end;
/
INSERT INTO dept(department_id, department_name, manager_id, location_id)
VALUES(300,'it',100,1500);
insert하기 전에 문장트리거수행
insert한 후에 문장트리거수행
BEFORE/AFTER의 행트리거
...
BEFORE 행트리거
CREATE OR REPLACE TRIGGER dept_row_before
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
dbms_output.put_line('insert하기 전에 행트리거수행');
end;
/
AFTER 행트리거
CREATE OR REPLACE TRIGGER dept_row_after
AFTER INSERT ON dept
FOR EACH ROW
BEGIN
dbms_output.put_line('insert한 후에 행트리거수행');
end;
/
INSERT INTO dept(department_id, department_name, manager_id, location_id)
VALUES(300,'it',100,1500);
insert하기 전에 문장트리거수행
insert하기 전에 행트리거수행
insert한 후에 행트리거수행
insert한 후에 문장트리거수행
출력되는 순서를 확인해서 언제 출력이 되는지 타이밍을 익히자.
문장트리거
14시부터 15시사이의 insert 작업을 불가하는 등과 같은 경우에 쓴다.
CREATE OR REPLACE TRIGGER secure_dept
BEFORE INSERT ON dept
BEGIN
if to_char(sysdate,'hh24:mi') between '14:00' and '15:00' then
raise_application_error(-20000,'insert 시간이 아닙니다.');
end if;
end;
/
INSERT INTO dept(department_id, department_name, manager_id, location_id)
VALUES(300,'it',100,1500);
ERROR at line 1:
ORA-20000: insert 시간이 아닙니다.
ORA-06512: at "HR.SECURE_DEPT", line 3
ORA-04088: error during execution of trigger 'HR.SECURE_DEPT'
dept 테이블에 trigger를 걸었기 때문에 insert작업이 불가능하다. 이 트리거는 insert문을 수행하고 있는 session에서 돌아간다. 그래서 insert문을 수행하는 순간(수행하기 직전에) 트리거(secure_dept에서의 raise_application_error가 발생하여 자동 rollback 된다.
DML작업에 따른 제한
타이밍(BEFORE) 뒤에 OR로 DML을 연결시키고, inserting,updating,deleting이란 키워드를 사용하여 조건부 술어에 사용할 수 있다.
CREATE OR REPLACE TRIGGER secure_dept
BEFORE INSERT OR UPDATE OR DELETE ON dept
BEGIN
if to_char(sysdate,'hh24:mi') between '14:00' and '16:00' then
if inserting then --trigger내에서는 inserting.(insert는 sql문)
raise_application_error(-20000,'insert 시간이 아닙니다.');
elsif updating then
raise_application_error(-20001,'update 시간이 아닙니다.');
elsif deleting then
raise_application_error(-20002,'delete 시간이 아닙니다.');
end if;
end if;
end;
/
INSERT INTO dept(department_id, department_name, manager_id, location_id)
VALUES(300,'it',100,1500);
행트리거
DML작업에 영향을 입은 row가 있을 경우에만 돌아간다. 그래서 데이터에 대한 감사, 복제 행위를 할 경우 행트리거를 사용하면 좋다. 아래 트리거를 보고 이해해보자.
CREATE TABLE copy_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM employees;
CREATE OR REPLACE TRIGGER copy_emp_trigger
BEFORE DELETE OR INSERT OR UPDATE OF salary ON copy_emp
--OF 컬럼(salary)를 사용하여 필드단위로 조작하려고 한다.
FOR EACH ROW
--행트리거로 가겠다.(영향을 입은 row가 있을 경우에만 trigger사용)
WHEN (new.department_id = 20 OR old.department_id = 10)
--when이라는 조건절을 사용하여 제한하겠다.(행트리거에서만 사용 가능)
--when절에는 수식자 앞에 콜론(:) 사용 불가(begin절에서는 반드시 사용)
--old 는 이전, new는 새로운 값을 의미
--20번사원을 insert시, 10번사원에 대한 delete시, 10번과 20번사원의 update시에 트리거 작동
DECLARE
v_sal number;
BEGIN
if deleting then
dbms_output.put_line('old salary : ' || :old.salary);
--delete시에는 이후 값은 의미가 없으므로 이전값인 old를 사용
elsif inserting then
dbms_output.put_line('new salary : ' || :new.salary);
--insert시에는 이전 값은 의미가 없으므로 이후값인 new를 사용
else
v_sal := :new.salary - :old.salary;
dbms_output.put_line('사원번호 : ' || :new.employee_id ||
' 이전급여 : ' || :old.salary ||
' 수정된급여 : ' || :new.salary ||
' 급여 차이 : ' || v_sal);
end if;
END;
/
만약 OF salary를 쓰지 않았으면, 다음과 같이 if절에서 salary를 제한에서 사용해야 한다. 또한 다른 컬럼들도 조건부 술어에 기술하여서 제한할 수 있다.
CREATE TABLE copy_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM employees;
CREATE OR REPLACE TRIGGER copy_emp_trigger
BEFORE DELETE OR INSERT OR UPDATE ON copy_emp
--OF 컬럼(salary) 삭제
FOR EACH ROW
WHEN (new.department_id = 20 OR old.department_id = 10)
DECLARE
v_sal number;
BEGIN
if deleting then
dbms_output.put_line('old salary : ' || :old.salary);
elsif inserting then
dbms_output.put_line('new salary : ' || :new.salary);
elsif updating('salary') then
--updating에 컬럼(salary)를 제한시켜 주어야한다.
v_sal := :new.salary - :old.salary;
dbms_output.put_line('사원번호 : ' || :new.employee_id ||
' 이전급여 : ' || :old.salary ||
' 수정된급여 : ' || :new.salary ||
' 급여 차이 : ' || v_sal);
end if;
END;
/
DELETE 수행
DELETE FROM copy_emp
WHERE department_id = 10;
old salary : 4400
1 row deleted.
10번 부서의 사원을 삭제하니 trigger가 작동한다.
DELETE FROM copy_emp
WHERE department_id = 20;
1 row deleted.
20번 부서의 사원을 삭제하니 trigger는 작동하지 않았다.
INSERT 수행
INSERT INTO copy_emp(employee_id, last_name, salary, department_id)
VALUES(300,'James',1000,20);
new salary : 1000
1 row created.
20번 부서의 사원을 INSERT하니 trigger가 작동한다.
INSERT INTO copy_emp(employee_id, last_name, salary, department_id)
VALUES(300,'James',1000,10);
1 row created.
ERROR: MUTATING
정상적으로 함수/트리거는 만들어지나 실행 시 발생하는 side effect 중 하나이다. 이를 주의하여 함수/트리거를 만들어야 한다. 다음 문장을 보고 이해해보자.
CREATE OR REPLACE FUNCTION query_call_sql(
p_a in number)
RETURN number
is
v_sal number;
begin
SELECT salary
INTO v_sal
FROM employees
WHERE employee_id = 170;
RETURN p_a + v_sal;
end query_call_sql;
/
update employees
set salary = query_call_sql(10000)
where employee_id = 101;
set salary = query_call_sql(10000)
*
ERROR at line 2:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.QUERY_CALL_SQL", line 8
함수/트리거를 만들 때는 문제는 없지만 DML작업을 할 때 오류가 발생한다. 그 이유는 같은 테이블에 대해서는 동시에 write/read를 못한다. 왜냐하면 타이밍 시점을 못잡는다. UPDATE를 먼저해야할지 SELECT를 해야할지 모른다. 그래서 함수/트리거를 만들 때 주의해야 한다.
독립 TRANSACTION 처리
호출단위 프로그램에서 TCL문을 수행하면 TRANSACTION이 종료된다. 하지만 때에 따라 호출하는 PROCEDURE에서 TRANSACTION에서 시작하고 끝내고 싶은 경우가 있다. 이때 독립 TRANSACTION 처리(pragma autonomous_transaction)를 해준다.
/* 테이블 생성 */
CREATE TABLE log_table (
username varchar2(30),
day timestamp,
message varchar2(100));
CREATE TABLE temp_table(
n number);
/* 프로시저 생성 */
CREATE OR REPLACE PROCEDURE log_message(
p_message in varchar2)
is
begin
INSERT INTO log_table(username, day, message)
VALUES(user, current_date, p_message);
commit;
end log_message;
/
/* 테이블 생성 확인 */
SELECT * FROM log_table;
SELECT * FROM temp_table;
SELECT * FROM user_procedures WHERE object_name = 'LOG_MESSAGE';
/* 익명블락구조 실행 */
begin
INSERT INTO temp_table(n)
VALUES(100);
log_message('불금하자');
end;
/
/* 확인 */
ROLLBACK;
SELECT * FROM log_table;
SELECT * FROM temp_table;
temp_table은 commit을 하지 않았지만 temp_table까지 commit이 되었다. 문제가 생길 수 있다. 아래와 같이 해결하자.
/* 데이터 삭제 */
delete from temp_table;
delete from log_table;
commit;
/* 프로시저 수정 */
CREATE OR REPLACE PROCEDURE log_message(
p_message in varchar2)
is
pragma autonomous_transaction;
--TRANSACTION 지시어: 독립 TRANSACTION 처리
--PROCEDURE에서만 영향을 준다.
/* INSERT 작업 */
begin
INSERT INTO log_table(username, day, message)
VALUES(user, current_date, p_message);
commit;
end log_message;
/
/* 익명블락구조 실행 */
begin
INSERT INTO temp_table(n)
VALUES(100);
log_message('불금하자');
end;
/
/* 확인 */
rollback;
SELECT * FROM log_table;
SELECT * FROM temp_table;
/