7. 이름을 입력받아 그 직원의 부서명과 급여를 검색하는 프로시저
CREATE OR REPLACE PROCEDURE usp_search(
p_ename IN emp.ename%type,
p_ename OUT dept.dname%type
p_sal OUT emp.sal%type)
IS
BEGIN
SELECT dname, sal
FROM dept INNER JOIN emp
ON dept.deptno = emp.edptno AND upper (ename) = upper (p_ename);
END;
/
var g_dname varchar2(14)
var g_sal number
SELECT dname, sal
FROM dept INNER JOIN emp
ON dept.deptno = emp.edptno AND upper (ename) = 'scott;
exec usp_search('scott',:g_dname,:g_sal)
print :g_dname
print :g_sal
--------------------------------------------------------------------
8. 전화번호를 입력받아 다시 전화번호를 리턴하는 프로시저
CREATE OR REPLACE PROCEDURE usp_tel(p_tel in out varchar2)
IS
BEGIN
p_tel := substr(p_tel,1,3) || '-' || substr(p_tel,4);
END;
/
var_g_tel varchar2(10);
BEGIN
:g_tel :=1234567;
END
/
exec usp _ tel(:g_tel)
print : g_tel
---------------------------------------------------------------------
=====================================================================
*******트리거 TRIGGER (콜백 메서드)
발단 : 이벤트가 자동적으로 호출되서 사용
1. 이벤트에 의해 자동으로 호출되는 프로시저
-DML (insert, update, delete)
2. 문법
CREATE [OR REPLACE] TRIGGER 트리거명 {BEFORE|AFTER}
트리거 이벤트 ON 테이블명
[반복문]
BEGIN
END;
3. DD(data dictionary) : user_triggers
4. 트리거는 기본적으로 2개의 임시테이블을 가지고 있다.
OLD(:old), NEW(:new)
insert into member values(4, '권율', '수원', '444-4444');
delete from member where id=1;
update member set addr='제주' where id =20
insert 는 new 테이블 사용
delete 는 old 테이블 사용
update 는 old, new 두개의 테이블 다 사용
=====================================================================
*** 실습
1. emp 테이블 에서 급여를 수정할 ‹š 현재의 값보다 적게 수정할 수 없고, 현재 값보다 10% 이상 높게 수정할 수 없도록 제한하는 트리거 작성
CREATE OR REPLACE TRIGGER tri_sal_update
BEFORE update ON emp
FOR EACH ROW
WHEN(NEW.sal < OLD.sal or NEW.sal > OLD.sal*1.1)
BEGIN
raise_application_error(-20506,'수정된 값이 범위에 맞지 않음');
END;
/
-------
update emp set sal = 3000 where ename='KING';
drop trigger tri_sal_update;ed;;/
2. emp테이블을 사용할 수 있는 시간은 월요일부터 금요일까지 09시부터 18시까지만 사용할 수 있도록 하는 트리거 작성
create or replace trigger tri_resource
before update OR insert OR delete on emp
begin
if to_char(sysdate, 'dy') in('토', '일') or to_number(to_char(sysdate, 'HH24')) not between 9 and 10
then
raise_application_error(-20506, '사용시간이 아닙니다.');
end IF;
END;
/
show recyclebin
insert into emp(empno, ename) values(1000, 'test100');
3. emp 테이블에서 insert, update, delete문장이 하루에 몇건이나 발생하는지 조사하려고 한다. 조사 내용은 emp_audit라는 테이블에 저장하도록 한다. 조사항목은 사용자 이름, 작업구분, 작업시간으로 처리한다.
create table emp_audit(
e_id number(5),
e_name varchar2(5),
e_gubun varchar2(30),
e_date date,
constraint pk_id primary key(e_id)
);
create or replace trigger tri_audit
after insert or update or delete on emp
begin
if inserting then
insert into emp_audit values(seq_empno.nextval, user, 'insert작업', sysdate);
elsif updating then
insert into emp_audit values(seq_empno.nextval, user, 'update작업', sysdate);
elsif deleting then
insert into emp_audit values(seq_empno.nextval, user, 'delete작업', sysdate);
end if;
end;
insert into emp(empno, ename) values(1000, 'test1000');
insert into emp(empno, ename) values(1001, 'test1001');
insert into emp(empno, ename) values(1002, 'test1002');
delete from emp where empno between 1000 and 1002;
select * from emp_audit;
