학원 48일차 - Oracle

·2021년 7월 10일
0

2021.06.03

저장프로시저

  1. 결과셋의 레코드가 1개일때 대응
  2. 결과셋의 레코드가 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;
-- 자식 테이블 찾기!!!
-- 1. ERD 확인(FM, 권장)
--    - ERD 없을 수도 있음..
--    - ERD 갱신 안했을 수도 있음..

-- 2. 조회
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;


-- 3. ERD 도구 -> 역공학

-- 4. 강제 삭제 -> FK 걸려있어도 무시하고 삭제
-- 그다지 좋은 방법은 아님.
-- 자식이 있는 부모테이블을 삭제하는 방법(부모를 강제로 삭제, 자식 테이블은 그대로 유지)
drop table tblInsa cascade constraints purge; -- Table TBLUSER이(가) 삭제되었습니다.
-- 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;
profile
모르면 괴롭고 알면 즐겁다.

0개의 댓글