STORED FUNCTION
리턴값이 반드시 하나 존재하는 프로시져 객체
※매개변수타입, 리턴타입에는 자료형의 크기를 작성하지 않는다.
형태
create [or replace] function 함수명 (매개변수명1 타입, 매개변수명2 타입, .... )
return 리턴타입
is
-- 지역변수선언 (없으면 is쓰고 바로 begin)
begin
--실행부
return 리턴값;
exception
--예외처리부
return 예외발생시 리턴값;
end;
/
ex ) 주민번호를 받아서 나이를 리턴하는 함수 get_age생성
create or replace function get_age(p_emp_no employee.emp_no%type)
return number
is
v_birth_year number;
v_age number;
begin
-- 현재년도 - 출생년도 + 1
case
when substr(p_emp_no, 8, 1) in ('1', '2') then
v_birth_year := 1900 + substr(p_emp_no, 1, 2);
else
v_birth_year := 2000 + substr(p_emp_no, 1, 2);
end case;
v_age := extract(year from sysdate) - v_birth_year + 1;
return v_age;
end;
/select
emp_name,
emp_no,
get_age(emp_no) age
from
employee;
select문으로 확인해보니 age가 제대로 추가된 것을 알 수 있다.
STORED PROCEDURE
일련의 작업절차를 작성한 PL/SQL 객체. 함수와 같이 호출해서 사용한다.
STORED FUNCTION과 달리 리턴값이 없다.
호출부로의 값전달은 out모드의 매개변수를 사용할 수 있다.
저장 프로시저는 일련의 작업을 미리 컴파일해두고, 즉시 사용가능한 상태로 보관하므로 db서버의 처리성능 향상 효과가 있다.
일반 DQL에서는 호출할 수 없다. 익명블럭/다른 프로시져에서 호출 가능
형태
create or replace procedure 프로시저명[(
매개변수명 [모드] 타입,
매개변수명 [모드] 타입,
....
)]
is
--지연변수 선언
begin
--실행부
exception
--예외처리부
end;
/
모드
- in (기본값) 프로시저로 값을 전달할때
- out 프로시저에서 호출부로 값을 전달할때 (호출시 전달한 변수의 주소값)
- inout in과 out 두가지 겸용
매개변수가 없는 프로시져
ex ) employee_ex 사원을 모두 삭제
create or replace procedure proc_delete_all_employee_ex
is
begin
delete from
employee_ex;
commit;
dbms_output.put_line('employee_ex의 모든 사원 사원 레코드가 삭제되었습니다.');
end;
/
begin
-- 매개변수 없으므로 () 생략
proc_delete_all_employee_ex;
end;
/select * from employee_ex; 로 확인해보면 진짜 없어져있다.
생성된 procedure를 확인해보자.
select
*
from
user_procedures
where
object_type = 'PROCEDURE';
2. PROC_DELETE_EMPLOYEE_EX가 생긴 것을 확인할 수 있다.
실제로 functions는 procedures 아래에 있기 때문에 from user_procedures로 확인한다.
매개변수가 있는 프로시져
ex ) 사원 1명을 삭제하는 프로시져 proc_delete_employee_ex
create or replace procedure proc_delete_employee_ex (
p_emp_id in employee_ex.emp_id%type
)
is
begin
delete from
employee_ex
where
emp_id = p_emp_id;
commit;
dbms_output.put_line(p_emp_id || '번 사원데이터를 삭제했습니다.');
end;
/
begin
proc_delete_employee_ex('&사번');
end;
/
select * from employee_ex; 를 실행하면 실제로 205번이 삭제 된 것을 확인할 수 있다.
upset예제 (insert + update)
레코드가 이미 존재하면 update처리, 레코드가 존재하지 않으면 insert 처리
create table job_ex
as
select
*
from
job;
select * from job_ex;
-- 제약조건 다시 추가
alter table job_ex
add constraint pk_job_ex_job_code primary key(job_code)
modify job_code varchar2(5)
modify job_name not null;
-- 프로시져 proc_upsert_job_ex
begin
proc_upsert_job_ex('&직급코드', '&직급명');
end;
/
-- 프로시져 proc_upsert_job_ex
begin
proc_upsert_job_ex('&직급코드', '&직급명');
end;
/
select * from job_ex;
create or replace procedure proc_upsert_job_ex(
p_job_code job_ex.job_code%type,
p_job_name job_ex.job_name%type
)
is
v_cnt number;
begin
-- 레코드 존재여부 확인
select
count(*)
into
v_cnt
from
job_ex
where
job_code = p_job_code;
-- 분기처리
if v_cnt > 0 then
-- update
update
job_ex
set
job_name = p_job_name
where
job_code = p_job_code;
dbms_output.put_line(p_job_code || '의 직급명을 ' || p_job_name || '으로 변경했습니다.');
else
-- insert
insert into
job_ex
values (
p_job_code, p_job_name
);
dbms_output.put_line('직급코드 ' || p_job_code || ', 직급명 ' || p_job_name || ' 레코드를 추가했습니다.');
end if;
-- transaction
commit;
end;
/다음과 같이 job_ex를 만들고, 예제를 실행해본다.
ex ) 프로시져 proc_upsert_job_ex
다음과 같이 있던 이름은 변경되고, 새로운 직급코드의 경우 추가된다고 뜨는 것을 확인할 수 있다.
CURSOR
자료형
SQL실행결과 Result Set(private sql 메모리 영역)에 접근할 수 있는 포인터.
여러행인 경우, 한 행씩 순차적으로 접근할 수 있다.
커서 속성
- %rowcount - 최근 fetch된 행의 수(DQL), 처리된 행의 수 (DML)
- %notfound - open/fetch전에는 null -> fetch된 행이 존재하면 false, 존재하지 않으면 true로 변환
- %round - open/fetch 전에는 null -> fetch된 행이 존재하면 true, 존재하지 않으면 false로 변환
- %isopen - open된 상태이면 true, close된 상태라면 false
종류
- 암묵적 커서
- 명시적 커서
암묵적 커서 : 모든 SQL 실행과 동시에 암묵적 커서로 생성되어 처리된다. 커서의 생명주기는 Open - Fetch - Close.
declare
erow employee%rowtype;
begin
-- 실행결과에 접근할수 있는 암묵적 커서 생성되고, open ~ fetch ~ close 처리된다.
select
*
into
erow
from
employee
where
emp_id = '&사번';
if sql%found then
dbms_output.put_line(sql%rowcount || '행이 조회됨');
end if;
end;
/
명시적 커서 : PL/SQL안에서 sql의 result set에 직접 접근하기 위해 선언한다. 여러행을 처리할 수 있다.
생명주기는 선언 - Open - Fetch - Close.
매개변수가 없는 커서
declare
cursor emp_cursor
is
select
*
from
employee;
erow employee%rowtype;
begin
open emp_cursor; -- 쿼리실행
loop
fetch emp_cursor into erow; -- 한행씩 fetch해서 erow변수에 대입
exit when emp_cursor%notfound; -- 탈출 조건
dbms_output.put_line('사번 : ' || erow.emp_id);
dbms_output.put_line('사원명 : ' || erow.emp_name);
dbms_output.put_line('전화번호 : ' || erow.phone);
dbms_output.new_line;
end loop;
close emp_cursor;
end;
/
한 행씩 출력됨
매개변수가 있는 커서
declare
cursor emp_cursor_by_dept (p_dept_code employee.dept_code%type)
is
select
*
from
employee
where
dept_code = p_dept_code;
erow employee%rowtype;
begin
open emp_cursor_by_dept('&부서코드');
loop
fetch emp_cursor_by_dept into erow;
exit when emp_cursor_by_dept%notfound;
dbms_output.put_line('사번 : ' || erow.emp_id);
dbms_output.put_line('사원명 : ' || erow.emp_name);
dbms_output.put_line('부서코드 : ' || erow.dept_code);
dbms_output.new_line;
end loop;
close emp_cursor_by_dept;
end;
/
D5입력시 D5사원 모두 출력.
* 커서를 for in loop로 실행할 경우 open-fetch-close를 for in loop가 처리한다.
fetch된 행을 대입할 변수 선언이 불필요하다. 모든 행을 fetch하면 자동으로 exit 처리된다.
매개변수 없는 커서
declare
cursor emp_cursor
is
select
*
from
employee;
begin
-- for 행변수 in 커서명 loop ~ end loop
for erow in emp_cursor loop
dbms_output.put_line('사번 : ' || erow.emp_id);
dbms_output.put_line('사원명 : ' || erow.emp_name);
dbms_output.put_line('전화번호 : ' || erow.phone);
dbms_output.new_line;
end loop;
end;
/매개변수 있는 커서
-- 직급코드를 전달받아서 해당직급 사원을 출력하는 프로시져 proc_emp_info_by_job_code
create or replace procedure proc_emp_info_by_job_code (
p_job_code employee.job_code%type
)
is
cursor emp_cursor(pc_job_code employee.job_code%type)
is
select
*
from
employee
where
job_code = pc_job_code;
begin
for erow in emp_cursor(p_job_code) loop
dbms_output.put_line('사번 : ' || erow.emp_id);
dbms_output.put_line('사원명 : ' || erow.emp_name);
dbms_output.put_line('직급코드 : ' || erow.job_code);
dbms_output.new_line;
end loop;
end;
/
-- 실행
begin
proc_emp_info_by_job_code('J5');
end;
/
TRIGGER
방아쇠 객체. 한 액션 일어났을때, 연쇄적으로 일어나야 할 작업을 작성해 둔 객체.
DML | DDL | Log on / off
- DML Trigger 예시
- 회원테이블의 레코드를 delete하면, 해당 레코드 정보를 탈퇴회원테이블에 insert처리한다.
- 게시글 정보를 update하면, 게시글 로그 테이블에 insert처리한다.
형태
create [or replace] trigger 트리거명
before | after
insert or update or delete on 테이블
[ for each row ]
declare
-- 지역변수 선언부
begin
-- 실행부
exception
-- 예외처리부
end;
/
🔽
각각에 대한 설명
before | after
: 원DML 이전에 실행(before), 원래DML 이후에 실행(after), fk 제약조건이 걸린 경우 유효하다.
insert on member : member테이블에 insert가 일어나면 (원DML) , 트리거 실행
insert or delete on member : member테이블에 insert 또는 delete가 일어나면 (원DML), 트리거 실행
update of 컬럼1, 컬럼2 on member : member 테이블 컬럼1 또는 컬럼2 에서 update가 일어나면 트리거 실행
for each row
- 작성하면 행 레벨 트리거. 행단위로 트리거가 실행. update 시 6행이 변경(원DML), 트리거 각 행별로 6번 실행
- 작성하지 않으면 문장레벨로 트리거 실행. update시 6행이 변경 (원DML), 트리거 1번 실행
의사 레코드 Pseudo Record
|
|
DML 처리전 : old
|
DML 처리후 : new
|
|
insert
|
null
|
삽입된 행 정보
|
|
update
|
수정 전 행 정보
|
수정 후 행 정보
|
|
delete
|
삭제 전 행 정보
|
null
|
예시
select * from tb_member;
create table tb_member_log (
no number,
member_id varchar2(20),
log varchar2(2000) not null,
log_date date default sysdate,
constraint pk_tb_member_log_no primary key(no)
);
create sequence seq_tb_member_log_no;
이러한 tb_member가 있을 때
멤버 insert , update, delete 하는 트리거 생성 🔽
create or replace trigger trig_member_log
before
insert or update or delete on tb_member
for each row
--declare
-- 지역변수 없을시 생략
begin
-- inserting, updating, deleting booolean속성을 가진다.
if inserting then
-- insert발생
insert into
tb_member_log (no, member_id, log)
values(
seq_tb_member_log_no.nextval,
:new.id,
:new.id || ' 회원가입!'
);
dbms_output.put_line('tb_member에 insert가 발생했습니다.');
elsif updating then
-- update발생
insert into
tb_member_log (no, member_id, log)
values(
seq_tb_member_log_no.nextval,
:new.id,
:old.id || '에서 ' || :new.id || '로 변경!'
);
dbms_output.put_line('tb_member에 update가 발생했습니다.');
elsif deleting then
-- delete 발생
insert into
tb_member_log (no, member_id, log)
values(
seq_tb_member_log_no.nextval,
:old.id,
:old.id || ' 회원탈퇴!'
);
dbms_output.put_line('tb_member에 delete가 발생했습니다.');
end if;
-- trigger에서는 트랜잭션처리하지 않는다. 원DML문의 transaction과 함꼐 처리.
end;
/-- tb_member 행추가
insert into
tb_member
values(
seq_tb_member_no.nextval,
'abcde'
);
update
tb_member
set
id = 'xyz'
where
no = 42;
delete from
tb_member
where
no = 42;select * from tb_member;
select * from tb_member_log;
변경된 것을 확인 가능
log도 확인 가능!