2006年01月19日

--过程重载
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;