--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;
SELECT * FROM dept;
--使用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(-20009,'职工工资不能向低调整.');
END IF;
END trg_emp_sal_update;
UPDATE emp SET sal=10000 WHERE empno=7369;
SELECT * FROM emp WHERE empno=7369;
--按行触发（所有进行触发）when
CREATE OR REPLACE TRIGGER trg_emp_when
AFTER INSERT OR DELETE ON emp FOR EACH ROW
WHEN (new.deptno=10 AND new.deptno<=30)
DECLARE
v_info VARCHAR2(100);
BEGIN
CASE
WHEN inserting THEN
v_info:= :new.ename||','||:new.job||','||:new.sal;
INSERT INTO dept_log VALUES(USER,SYSDATE,'insert',v_info);
WHEN deleting THEN
v_info:= :old.ename||','||:old.job||','||:old.sal;
INSERT INTO dept_log VALUES(USER,SYSDATE,'delete',v_info);
END CASE;
END trg_emp_when;
INSERT INTO emp VALUES(1111,'test1','test',1111,SYSDATE,1111,0,10);
INSERT INTO emp VALUES(1112,'test2','test',1112,SYSDATE,1111,0,50);
COMMIT;
SELECT * FROM dept_log;
ALTER TRIGGER trg_emp_when DISABLE;
ALTER TRIGGER trg_emp_when ENABLE;
SELECT * FROM user_triggers WHERE table_name='DEPT';
--cmd 命令窗口 切换到C:\oracle\product\10.2.0\db_1\bin目录
--导出：
EXP scott/tiger BUFFER=64000 FILE=d:/scott.dmp
--导入
imp scott/tiger FILE=d:/scott.dmp FULL=y IGNORE=y