东软学习，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;



SELECT * FROM dept;