2006年01月19日

最新推荐文章于 2024-07-09 22:46:05 发布

原创最新推荐文章于 2024-07-09 22:46:05 发布 · 798 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #date #object #application #user #each

Oralce培训笔记专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一个PL/SQL包内过程重载的实现方式，包括数值和日期类型的变量交换，并展示了不同类型的触发器示例，如插入前检查工作时间和更新前后的销售佣金百分比计算。

--过程重载
create or replace package utilities as
procedure swap(p1 in out number, p2 in out number);
procedure swap(p1 in out date, p2 in out date);
end;
/

create or replace package body utilities as
procedure swap(p1 in out number, p2 in out number) is
l_temp number;
begin
l_temp := p1;
p1     := p2;
p2     := l_temp;
--dbms_output.put_line(p1||' 'p2);
end swap;
procedure swap(p1 in out date, p2 in out date) is
l_temp date;
begin
l_temp := p1;
p1     := p2;
p2     := l_temp;
--dbms_output.put_line(p1||' 'p2);
end swap;

end;
/

set serveroutput on
declare
l1 number:=100;
l2 number:=200;
d1 date:=sysdate;
d2 date:=sysdate+1;
begin
utilities.swap(l1,l2);
dbms_output.put_line('l1 = '|| l1);
dbms_output.put_line('l2 = '|| l2);
utilities.swap(d1,d2);
dbms_output.put_line('d1 = '|| d1);
dbms_output.put_line('d2 = '|| d2);
end;
/

select object_name, object_type
from user_objects
where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

--查看用户代码：
desc user_source

select text from user_source where name=’INSERT_INTO_T’ order by line;

--触发器
create table emp_d as select * from emp where 1=0;

create or replace trigger trg_emp
before insert on emp_d
begin
IF ((TO_CHAR(sysdate, 'DY') IN ('星期六', '星期日'))) or
   (TO_CHAR(sysdate, 'HH24') NOT BETWEEN '08' AND '18')   THEN
    RAISE_APPLICATION_ERROR(-20500,
                            'You may only insert into EMP during normal hours.');
END IF;
end;
/
INSERT INTO emp_d (empno, ename, deptno) VALUES(7701, 'BAUWENSa', 30);

CREATE OR REPLACE TRIGGER derive_commission_pct
   BEFORE INSERT OR UPDATE OF sal ON emp_test
   FOR EACH ROW
   WHEN (new.job = 'SALESMAN')
   BEGIN
   IF INSERTING THEN :new.comm := 1;
     ELSE         /* UPDATE of salary */
      IF :old.comm IS NULL THEN
        :new.comm :=0;
     ELSE
       :new.comm := :old.comm * (:new.sal/:old.sal);
     END IF;
   END IF;
END;
/
INSERT INTO emp_test (empno, ename, job,deptno) VALUES(7701, 'BAUWENSa','SALESMAN', 30);
commit;
update emp_test set comm=250 where empno=7701;
update emp_test set sal=1000 where empno=7701;
commit;

CREATE OR REPLACE TRIGGER cascade_updates
    AFTER UPDATE OF deptno on DEPT_test
    FOR EACH ROW
    BEGIN
    UPDATE emp_test emp
    SET    emp.deptno = :new.deptno
    WHERE emp.deptno = :old.deptno;
    END;
/
update dept_test set deptno=60 where deptno=10;