Duritz's Fun Life

자료출처 : http://blog.daum.net/liberalis/13185553 

--- ○ Trigger
-- 트리거의 종류
-- 1. before trigger : DML이 발생하기 이전에 검사할때 사용
-- 2. after trigger (가장많이 쓰임) : DML이 발생한후 
-- 3. 문장 trigger
-- 4. 행 trigger (가장많이 쓰임)
create table workchart
(no number
,nalja date default sysdate
,work varchar2(300)
)tablespace users;
--// workchart테이블에 DML작업은 업무시간에만 가능하도록 Before & 문장 trigger를 작성
--   업무시간대란? 월-금, 09:00 - 16:00 이전까지
create or replace trigger btri_workchart
before
insert or update or delete on workchart
declare
    worknottime exception; -- exception : 사용자가 정의하는 오류메세지 임을 정의

begin
  if to_char(sysdate,'d') in ('1','7') or
   to_number(to_char(sysdate,'hh24')) < 9 or
   to_number(to_char(sysdate,'hh24')) > 15
  then raise worknottime; -- raise 오류내용 변수 : 사용자가 정의한 오류메세지를 띄운다.
  -- then 다음에 사용자가 정의하는 오류내용과 DML작업을 할 수 없게 한다.
  end if;
  exception
      when worknottime then
          raise_application_error
       (-20001,'월요일부터 금요일 오전9시부터 오후4시까지만 작업이 가능합니다.');
          -- 오류번호는 -20001~-20999까지만 사용가능하다.
  --insert or update or delete를 하기전에 if문 조건에 맞으면 해당 오류번호와 오류 메세지를 출력한다.
end;
/
select * from workchart;
insert into workchart(no,work) values(1,'일을 열심히 했음')
-- 위트리거 생성 조건에 맞으면 DML문이 실행이 안되고 아니면 실행이 된다.
alter trigger btri_workchart disable; -- 비활성화 시키면 트리거 조건에 상관없이 입력된다.
----------------------------------------------------------------------------------------------
--// After & 행 트리거
create table member
(id varchar2(20),
passwd varchar2(20) constraint member_passwd_nn not null,
name varchar2(20) constraint member_name_nn not null,
hp varchar2(20),
constraint member_id_pk primary key(id) using index tablespace indx
) tablespace users;

create table login
(id varchar2(20),
passwd varchar2(20),
constraint login_id_pk primary key(id) using index tablespace indx,
constraint login_id_fk foreign key(id)
     references member(id) on delete cascade
) tablespace users;

select * from member;
select * from login;
---- member 테이블 입력값중 특정행을  1:1 관계에 있는 login테이블에 자동으로 입력이 되도록
---- 하는 것을 after & 행 trigger라고 한다.
create or replace trigger atri_member_ins
after
insert on member -- 멤버 테이블에서 입력이 되어진 다음에 
for each row     -- 특정행을 잡아낸다. (행트리거일때 항시 사용)

begin
  insert into login values(:new.id, :new.passwd);
  -- trigger내에서 방금 입력된값은 :new에 저장이 되어진다.
end;
/
insert into member values('superman','pass123','홍길동','010-6647-6348');
insert into member values('eom','junghwa','엄정화','019-8897-1567');
-- member 테이블에 값을 입력하면 자동적으로 login테이블에 입력이 된다.
-- update = 기존date delete + 새로운date insert
create or replace trigger atri_member_upd
after
update of id,passwd on member -- update 다음에 변경되어질 컬럼을 항시 명시해 주어야 한다.
             -- 변경되어지기 이전값은 (:old)에 저장이 되어진다.
for each row
begin
  update login set id = :new.id, passwd = :new.passwd
  where id = :old.id;
end;
/
update member set id='batman', passwd='pass007'
where id='superman';

update member set passwd='pass007'
where id='superman';
commit;
rollback;

delete member
where id='superman';
-----------------------------------------------------------------------------------------
select * from DEPT;
select * from emp;
----- 1:다 관계의 두 테이블에서 1:1처럼 on delete cascade로 자동 삭제가 되지 않지만
--    트리거를 이용하여 참조하는 pk,fk를 자동으로 변경시켜줄 수 있다.
create or replace trigger aftri_upd
after  update of deptno on dept
for each row
begin
  update emp set deptno = :new.deptno
  where deptno = :old.deptno;
end;
/
update dept set deptno=11
where deptno = 10
--- 1:다 관계의 테이블에서 부모테이블의 pk가 변경되면
--    자식테이블의 fk가 변경되는 트리거를 만들어주면 좋다.
-----------------------------------------------------------------------------------------
select * from user_source
where type='TRIGGER' and name='AFTRI_UPD'; -- 유저가 가진 소스를 본다.
-----------------------------------------------------------------------------------------
select * from user_triggers; -- 유저가 가진 트리거에대한 내용을 볼수 있다.
-----------------------------------------------------------------------------------------

--- ○ Cursor
create or replace procedure p_empinfo4
(v_jik in emp.job%type)
is
  cursor cur_empinfo
  -- cursor 커서명 is 쿼리문(select문) : 쿼리문조건에 맞는 값을 커서명으로 정의한다.
  is
  select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
  from EMP
  where upper(job) = upper(v_jik);
 
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_job emp.job%type;
  v_yearpay number;
begin
  dbms_output.put_line('----------------------------');
  dbms_output.put_line('사원번호 사원명  직종  연봉');
  dbms_output.put_line('----------------------------');
 
  open cur_empinfo;
     loop
       fetch cur_empinfo into v_empno, v_ename, v_job, v_yearpay;
       exit when cur_empinfo%notfound;
       dbms_output.put_line(v_empno || ' ' ||
          v_ename || ' ' ||
         v_job   || ' ' ||
         v_yearpay);   
     end loop;
  close cur_empinfo;
  -- open커서명으로 커서를 열고 close커서명으로 커서를 닫는다.
  -- fetch 커서명 into 변수는 한행한행의 값을 변수에 기억시킨다
  -- loop로 반복해서 행값을 계속 변수에 기억만 하면 무한 loop에 빠지니
  -- exit when 조건, 으로 반복문에서 빠져나올 조건을 만든다.
  -- exit when 커서명%notfound는 커서명으로 정의한것의 값이 행이 찾아지지 않는
  -- 즉, 행이 끝나면 반복문을 나오게 된다.
end;
/
----------------------------------------------------------------------------------------
--- ○ For loop Cursor
create or replace procedure p_empinfo5
(v_jik in emp.job%type)
is
  cursor cur_empinfo
  is
  select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
  from EMP
  where upper(job) = upper(v_jik);
 
begin
  for result in cur_empinfo loop
    dbms_output.put_line(result.empno || ' ' ||
              result.ename || ' ' ||
              result.job   || ' ' ||
              result.yearpay);
  end loop;
  -- for loop cursor문은 해당 커서가 행이 끝날때까지 반복되어진다.
  -- 그냥 cursor처럼 open,end,fetch 할 필요가 없다. (프로시져가 간단해진다.)
end;
/
----------------------------------------------------------------------------------------
select * from sawon;
select * from v_sung;
---|| 연습문제 : exec p_sawon('남'); 를 실행하면 ('여'를 치면 여자만)
-- 남자의 (사원번호 사원명 현재나이 입사일자 정년일자)가 나오게 프로시져를 만들어라.
create or replace view v_sung
as
select sano, case when substr(Y.minbun,7,1) in ('1','3') then '남'
             else '여' end sex,
       name, age, hiredate,
    add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-age)) jung   
from (select sano, name, minbun,
     case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end age,
             to_char(hiredate,'yyyy-mm-dd') hiredate
      from SAWON) Y;

create or replace procedure p_sex
(v_sung in varchar2)
is
  cursor cur_sexgubun
  is
  select sano, name, age, hiredate, jung
  from v_sung
  where sex = v_sung
begin
  dbms_output.put_line('----------------------------------');
  dbms_output.put_line('사원번호 사원명 나이 입사일 정년일');
  dbms_output.put_line('----------------------------------');
 
  for result in cur_sexgubun loop
  dbms_output.put_line(result.sano       || ' ' ||
          result.name       || ' ' ||
          result.age        || ' ' ||
          result.hiredate   || ' ' ||
          result.jung);
  end loop;
end;
/
exec p_sex('남')
----------------------------------------------------------------------------------------
--|| 위문제에서 '남', '여'가 아닌 0(남여모두), 1(남자), 2(여자) 이경우를 출력하여라
--  완 료 못 했 음 나 중 에 해 보 기 --
create or replace view v_sung
as
select sano, case when substr(Y.minbun,7,1) in ('1','3') then '남'
    else '여' end sex,
       name, age, hiredate,
    add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-age)) jung   
from (select sano, name, minbun,
     case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end age,
             to_char(hiredate,'yyyy-mm-dd') hiredate
      from SAWON) Y;

create or replace procedure p_sex
(v_sung in number)
is
  cursor cur_sexgubun
  is
  select sano, name, age, hiredate, jung
  from v_sung
  where sex = v_sung;
    
begin
  dbms_output.put_line('----------------------------------');
  dbms_output.put_line('사원번호 사원명 나이 입사일 정년일');
  dbms_output.put_line('----------------------------------');
 
  for result in cur_sexgubun loop
  dbms_output.put_line(result.sano       || ' ' ||
          result.name       || ' ' ||
          result.age        || ' ' ||
          result.hiredate   || ' ' ||
          result.jung);
  end loop;
end;
/
-------------------------------------------------------------------------------------------------
select *
from user_source;  -- 유저가 만든 피로시져, 함수등의 소스를 보여준다.
-------------------------------------------------------------------------------------------------

--- ○ 배 열 (테이블 타입형 변수 - oracle에서)
--|| 배열을 쓰기위해서는 출력값이 저장될 변수를 테이블 타입형 변수로 정의를 해주어야 한다.
--|| 반복문 안에 들어갈 쿼리문을 미리 view로 생성해주면 좋다.
create or replace view v_emppay
as
select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
from EMP;