oracle触发器函数,oracle函数与触发器

--1.无参函数

CREATE OR REPLACE FUNCTION func1 RETURN VARCHAR2

IS

BEGIN

RETURN '欢迎你:'||USER||',现在是:'||to_char(SYSDATE,'yyyy-mm-dd');

END func1;

--调用测试

BEGIN

dbms_output.put_line(func1);

END;

--有参函数

SELECT * FROM emp;

CREATE OR REPLACE FUNCTION getWorkTime(hiredate DATE) RETURN NUMBER

IS

BEGIN

RETURN trunc(months_between(SYSDATE,hiredate)/12);

END getWorkTime;

SELECT ename,job,getWorkTime(hiredate) FROM emp;

--触发器

--语句触发器(select/insert/update/delete)

CREATE OR REPLACE TRIGGER trg_dept BEFORE INSERT OR UPDATE OR DELETE ON dept

DECLARE

BEGIN

dbms_output.put_line(USER||','||to_char(SYSDATE,'yyyy-mm-dd')||',对dept进行操作。');

END trg_dept;

UPDATE dept SET dname='测试' WHERE deptno=60;

INSERT INTO dept VALUES(23,'触发器','数据库');

--行触发器(FOR EACH ROW)

--before触发器

--伪记录 :NEW  :OLD

--3个条件谓词：INSERTING  UPDATING  DELETING

CREATE OR REPLACE TRIGGER trg_dept_before

BEFORE INSERT OR UPDATE OR DELETE ON dept

FOR EACH ROW

DECLARE

v_now VARCHAR2(50);

BEGIN

v_now:=to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss');

CASE

WHEN INSERTING  THEN

dbms_output.put_line(v_now||'对dept表进行了添加操作');

dbms_output.put_line('添加的数据:'||:new.deptno||','||:new.dname||','||:new.loc);

WHEN UPDATING THEN

dbms_output.put_line(v_now||'对dept表进行了修改操作');

dbms_output.put_line('修改之前的数据:'||:old.deptno||','||:old.dname||','||:old.loc);

dbms_output.put_line('修改之后的数据:'||:new.deptno||','||:new.dname||','||:new.loc);

WHEN DELETING THEN

dbms_output.put_line(v_now||'对dept表进行了删除操作');

dbms_output.put_line('删除的数据:'||:old.deptno||','||:old.dname||','||:old.loc);

END CASE;

END trg_dept_before;

INSERT INTO dept VALUES(24,'触发器4','数据库4');

UPDATE dept SET dname='测试' WHERE deptno=24;

DELETE FROM dept WHERE deptno=24;

COMMIT;

--实现SQL Server中标识列效果

CREATE SEQUENCE seq_dept_deptno INCREMENT BY 1 START WITH 100 MAXVALUE 999999 NOCYCLE;

--触发器(从序列里面取值，并且在insert语句进行使用

CREATE OR REPLACE TRIGGER trg_dept_before_increment

BEFORE INSERT ON dept FOR EACH ROW

BEGIN

SELECT seq_dept_deptno.nextval INTO :new.deptno FROM dual;

END trg_dept_before_increment;

INSERT INTO dept (dname,loc) VALUES('序列','序列');

--INSERT INTO dept (deptno,dname,loc) VALUES(seq_dept_deptno.nextval,'序列','序列');

COMMIT;

--使用after触发器记录数据操作日志 CREATE TABLE dept_log(    uname VARCHAR2(40),    oper_time DATE,    oper_type VARCHAR2(20),    info VARCHAR2(100) ); CREATE OR REPLACE TRIGGER trg_dept_after        AFTER INSERT OR DELETE ON dept FOR EACH ROW DECLARE        v_info VARCHAR2(100); BEGIN   CASE      WHEN inserting THEN       v_info:= :new.deptno||','||:new.dname||','||:new.loc;             INSERT INTO dept_log VALUES(USER,SYSDATE,'insert',v_info);     WHEN deleting THEN       v_info:= :old.deptno||','||:old.dname||','||:old.loc;             INSERT INTO dept_log VALUES(USER,SYSDATE,'delete',v_info);          END CASE; END trg_dept_after; INSERT INTO dept VALUES(35,'sales','青岛'); DELETE FROM dept WHERE deptno=35; COMMIT; SELECT * FROM dept_log; SELECT * FROM emp; --根据列进行触发update of CREATE OR REPLACE TRIGGER trg_emp_sal_update        BEFORE UPDATE OF sal ON emp FOR EACH ROW BEGIN   IF :new.sal<:old.sal then dbms_output.put_line raise_application_error end if trg_emp_sal_update update emp set sal="10000" where empno="7369;" select from create or replace trigger trg_emp_when insert delete on for each row and new.deptno declare v_info varchar2 begin when inserting v_info:=":new.ename||','||:new.job||','||:new.sal;" into dept_log values deleting case commit alter disable enable user_triggers table_name="DEPT" exp scott buffer="64000" file="d:/scott.dmp" imp full="y" ignore="y</p">