2021.06.03
저장프로시저
- 결과셋의 레코드가 1개일때 대응
- 결과셋의 레코드가 N개일때 대응
결과셋의 레코드가 1개일때 대응
-- 저장프로시저
-- out parameter
-- 1. 결과셋의 레코드가 1개일때 대응
-- 2. 결과셋의 레코드가 N개일때 대응
create or replace procedure procTest (
pnum in number, --직원 번호
pname out varchar2, --직원 이름
pjikwi out varchar2, --직위
pbuseo out varchar2 --부서
)
is
begin
select name, jikwi, buseo into pname, pjikwi, pbuseo from tblInsa
where num = pnum;
end procTest;
-- 호출
set serverout on;
declare
vname tblInsa.name%type;
vjikwi tblInsa.jikwi%type;
vbuseo tblInsa.buseo%type;
begin
procTest(1001, vname, vjikwi, vbuseo);
dbms_output.put_line(vname);
dbms_output.put_line(vjikwi);
dbms_output.put_line(vbuseo);
end;
결과셋의 레코드가 N개일때 대응 - cursor
-- 부서 -> 목록 반환
-- 1. 저장 프로시저 -> 단일값 반환(커서 반환 가능, ANSI-SQL 자료형 + PL/SQL자료형(커서))
-- 2. 저장 함수 -> 단일값 반환 (커서 반환 불가능, ANSI-SQL 자료형만 반환 가능(number, string, date)
create or replace procedure procTest (
pbuseo in varchar2, -- 부서(조건)
pcursor out sys_refcursor -- sys_refcursor: cursor 자료형과 동일한 자료형. 반환값 타입으로 사용.★
)
is
begin
-- cursor pcusor is select문..
open pcursor
for select * from tblInsa where buseo = pbuseo;
end procTest;
-- 호출
set serveroutput on;
declare
-- cursor pcursor is select * from tblInsa where buseo = pbuseo;
pcursor sys_refcursor;
vrow tblInsa%rowtype;
begin
procTest('개발부', pcursor);
-- pcursor + opened..
loop
fetch pcursor into vrow;
exit when pcursor%notfound;
dbms_output.put_line(vrow.name);
end loop;
end;
-- 직원 번호 전달 -> 어디 거주? -> 같은 지역 직원 명단 반환
create or replace procedure procTest (
pnum number, -- 직원 번호
pcursor out sys_refcursor -- 직원명단
)
is
vcity tblInsa.city%type;
begin
-- 어디거주?
select city into vcity from tblInsa where num = pnum;
-- 같은 지역 직원 명단 반환
open pcursor for
select * from tblInsa where city = vcity;
end procTest;
-- 호출
declare
vcursor sys_refcursor;
vrow tblInsa%rowtype;
begin
procTest(1030, vcursor);
loop
fetch vcursor into vrow;
exit when vcursor%notfound;
-- 업무
dbms_output.put_line(vrow.name || '-' || vrow.city);
end loop;
end;
트리거, Trigger
- DB Object
- 프로시저의 한 종류(**)
- 개발자의 호출이 아닌, 미리 지정한 특정 사건이 발생하면 자동으로 실행되는 프로시저(예약+이벤트)
- 사람이 24시간 지켜볼 수 없기 때문에 > 일종의 cctv를 달아서 감시 > 특정 사건이 나타나면 자동으로 실행된다.
- 특정 테이블 지정 -> 감시 -> insert or update or delete -> 미리 준비해놓은 프로시저가 자동 실행
- 트리거가 많아지면 시스템 속도가 느려진다.(감시비용이 급증) > 필요한 경우만 사용
트리거 구문
create or replace trigger 트리거명
- 트리거 옵션
before|after
insert|update|delete on 테이블명
[for each row]
declare
선언부;
begin
실행부;
exception
예외처리부
end;
-- 테이블 생성
create table tblLog (
seq number primary key,
num number not null references tblInsa(num), -- FK
regdate date default sysdate not null
)
create sequence seqLog;
create table tblBoard (
seq number primary key,
num number not null references tblInsa(num), -- FK
subject varchar2(1000) not null
)
create sequence seqBoard;
-- 트리거 객체 생성 + 트리거 작동 시작 (트리거는 만드는 순간 실행된다.)
-- 직원들 -> tblBoard 글 작성 -> 관리자 확인 + 모니터링 + tblLog
create or replace trigger trgBoard
after -- 사건 전(before)/후(after) -> 언제 트리거가 개입할지 정함
insert -- 감시 사건
on tblBoard -- 감시 대상
declare
begin
dbms_output.put_line('직원이 글을 작성했습니다.'); -- tblBoard 글 작성 > 호출
end;
-- 트리거 삭제
drop trigger trgBoard;
-- 글쓰기(사건발생) -> 트리거 실행(예약을 걸어둔 메서드)
insert into tblBoard (seq, num, subject)
values (seqBoard.nextVal, (select num from tblInsa where name = '홍길동'), '테스트입니다.');
create or replace trigger trgBoard
after
insert
on tblBoard
declare
vnum number; -- 직원번호를 저장하는 변수
begin
-- 누가 글을 썼는지 tblLog 테이블에 기록
-- 누가 썼는지?? -> 마지막 글을 쓴 사람 알아내기
select num into vnum from tblBoard where seq = (select max(seq) from tblBoard); -- 글번호가 가장 큰 글의 직원번호
-- 로그 기록
insert into tblLog (seq, num, regdate) values (seqLog.nextVal, vnum, default);
end;
insert into tblBoard (seq, num, subject)
values (seqBoard.nextVal, (select num from tblInsa where name = '홍길동'), '테스트입니다.');
insert into tblBoard (seq, num, subject)
values (seqBoard.nextVal, (select num from tblInsa where name = '이순신'), '테스트입니다.');
insert into tblBoard (seq, num, subject)
values (seqBoard.nextVal, (select num from tblInsa where name = '아무개'), '테스트입니다.');
-- 테이블 확인
select * from tblLog;
-- inner join으로 테이블 확인
select
i.name as 직원명,
to_char(l.ragdate, 'hh24:mi:ss') as 시각
from tblLog l
inner join tblInsa i
on i.num = l.num;
-- 상관 서브 쿼리로 테이블 확인
select
(select name from tblInsa where num = tblLog.num) as 직원명,
to_char(l.regdate, 'hh24:mi:ss') as 시각
from tblLog;
select * from tblBoard;
select * from tblLog;
SELECT fk.owner, fk.constraint_name , fk.table_name
FROM all_constraints fk, all_constraints pk
WHERE fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
AND fk.CONSTRAINT_TYPE = 'R'
AND pk.TABLE_NAME = 'TBLINSA'
ORDER BY fk.TABLE_NAME;
drop table tblBonus;
drop table tblLog;
drop table tblBoard;
drop table tblInsa cascade constraints purge;
-- delete하기 전에 트랜잭션이 언제 시작됐는지 확인할 것 > create, alter, drop한 부분 확인 > commit 확인
commit;
rollback;
-- tblInsa. 직원 퇴사
-- 특정 요일(목)에는 퇴사를 할 수 없다.
delete from tblInsa where num = 1001;
-- 퇴사
-- ORA-02292: integrity constraint (HR.SYS_C008485) violated - child record found -> 자식테이블이 엮여있음
delete from tblBoard;
commit;
create or replace trigger trgInsa
before -- 사건이 일어나기 전에 행동
delete -- 사건
on tblInsa
begin
-- dbms_output.put_line('트리거 발생');
-- 업무 : 오늘 요일이 목요일이면 퇴사할 수 없음
if to_char(sysdate, 'dy') = '목' then
-- 현재 실행되려는 delete 작업을 없었던 걸로 만들기 -> 강제로 예외 발생!! (throw new Exception)
-- -20000 ~ 29999 : 사용자 정의번호
raise_application_error(-20001, '목요일에는 퇴사가 불가능합니다.');
end if;
end trgInsa;
-- 로그 트리거
-- tblMen 테이블에서 발생하는 모든 변화(insert, update, delete)를 기록하는 로그 테이블
create table tblLogMen (
seq number primary key,
message varchar2(1000) not null,
regdate date default sysdate not null
);
create sequence seqLogMen;
create or replace trigger trgLogMen
after -- 확실히 일이 일어난 다음에 기록하기 때문에 로그를 기록하는 테이블은 대부분 after
insert or update or delete -- 세개를 동시에 감시 가능
on tblMen
declare
vmessage varchar2(1000);
begin
-- 어떤 사건? insert or update or delete
-- dbms_output.put_line('사건 발생');
-- insert or update or delete 사건 구분 > 예약 상수
if inserting then
vmessage := '새 인원이 추가되었습니다.';
elsif updating then
vmessage := '특정 인원의 정보가 수정되었습니다.';
elsif deleting then
vmessage := '특정 인원이 삭제되었습니다.';
end if;
-- 로그테이블에 기록
insert into tblLogMen (seq, message, regdate)
values (seqLogMen.nextVal, vmessage, default);
end trgLogMen;
select * from tblMen;
delete from tblMen where name = '아무개';
update tblMen set weight = 80 where name = '하하하';
select * from tblLogMen;