视图是一种虚拟表，是实际表的一种映射，主要针对一些复杂查询的一个封装，实际表数据修改，视图数据自动更改
CREATE OR REPLACE VIEW V_TONYASSELECT A.EMPNO AS EMPNO,A.EMPNO AS EMPNO1,A.ENAME AS ENAME,A.JOB AS JOB,B.DNAME AS DNAME,B.LOC AS LOCFROM SCOTT.EMP A,SCOTT.DEPT BWHERE A.DEPTNO=B.DEPTNO AND A.DEPTNO=10SELECT * FROM V_TONY;
函数必须有返回值且只有一个，存储过程不需要返回值，如果需要返回多个值或者不需要返回值，就使用存储过程
函数主要用于计算，用来返回特定的数据，而存储过程主要是执行一系列的操作
--创建函数，获取员工薪水CREATE OR REPLACE FUNCTION FUN_GETSAL(ENO NUMBER) RETURN NUMBER ASRES_SAL NUMBER;BEGINSELECT SAL INTO RES_SAL FROM EMP WHERE EMPNO = ENO;RETURN RES_SAL;EXCEPTIONWHEN OTHERS THENRAISE_APPLICATION_ERROR(-20012, '不存在的员工编号');END;SELECT FUN_GETSAL(7499) FROM DUAL; --可以调用函数，但不可调用存储过程--调用上面的FUN_GETSAL函数DECLARERES_SAL NUMBER;EX EXCEPTION;PRAGMA EXCEPTION_INIT(EX, -20012);BEGINRES_SAL := FUN_GETSAL(7499);DBMS_OUTPUT.PUT_LINE('薪水:' || RES_SAL);EXCEPTIONWHEN EX THENDBMS_OUTPUT.PUT_LINE('不存在此员工');END;
存储过程，返回用户是否存在，IN输入，OUT输出
CREATE OR REPLACE PROCEDURE PROC_ISEXIST(PID IN EMP.EMPNO%TYPE,RET OUT NUMBER) ASFLAG NUMBER;BEGINSELECT COUNT(1) INTO FLAG FROM EMP WHERE EMPNO = PID;IF FLAG = 1 THENDBMS_OUTPUT.PUT_LINE(PID || '用户存在');RET := 1;ELSEDBMS_OUTPUT.PUT_LINE(PID || '用户不存在');RET := 0;END IF;END;DECLARENUM VARCHAR(50) := ('&输入编号');RET NUMBER;BEGINPROC_ISEXIST(NUM, RET);DBMS_OUTPUT.PUT_LINE(RET);END;
触发器，强制数据一致性，启用复杂的业务逻辑
--禁止星期日的时候，对表进行写操作CREATE TABLE A(ID NUMBER,NAME VARCHAR(10));INSERT INTO A VALUES(1,'Tony');INSERT INTO A VALUES(2,'Tony2');INSERT INTO A VALUES(3,'Tony3');SELECT * FROM A;--创建触发器CREATE OR REPLACE TRIGGER TRI_NOTWRITEBEFORE INSERT OR UPDATE OR DELETE ON ABEGINIF (TO_CHAR(SYSDATE, 'DY') = '星期日') THENRAISE_APPLICATION_ERROR(-20600, '星期日禁止对A表进行写操作');END IF;END;--触发器实现序号自增CREATE TABLE B(ID NUMBER(10) PRIMARY KEY,UNAME VARCHAR(10));--定义序列(001,002,003,...)CREATE SEQUENCE MYSEQ INCREMENT BY 1 START WITH 001 NOMAXVALUE NOCYCLE CACHE 20;--创建触发器CREATE OR REPLACE TRIGGER TRI_INCBEFORE INSERT ON BFOR EACH ROW --语句级触发，每一行触发一次DECLARENEXTID NUMBER;BEGINSELECT MYSEQ.NEXTVAL INTO NEXTID FROM DUAL; --返回上述定义好的序列号:NEW.ID := NEXTID; --:NEW表示新插入的记录END;--测试数据INSERT INTO B(UNAME) VALUES('TONY1');INSERT INTO B(UNAME) VALUES('TONY2');INSERT INTO B(UNAME) VALUES('TONY3');COMMIT;--触发器记录对表写操作的一些日志--创建对B表操作的B_LOG日志表CREATE TABLE B_LOG(LUSER VARCHAR2(10),LTYPE VARCHAR2(20),LDATE DATE);--创建触发器CREATE OR REPLACE TRIGGER B_TRIGGERAFTER INSERT OR DELETE OR UPDATE ON BDECLAREV_TYPE B_LOG.LTYPE%TYPE;BEGINIF INSERTING THENV_TYPE := 'INSERT';DBMS_OUTPUT.PUT_LINE('记录成功插入！');ELSIF DELETING THENV_TYPE := 'DELETE';DBMS_OUTPUT.PUT_LINE('记录成功删除！');ELSIF UPDATING THENV_TYPE := 'UPDATE';DBMS_OUTPUT.PUT_LINE('记录成功更新！');END IF;INSERT INTO B_LOG VALUES (USER, V_TYPE, SYSDATE);END;--测试数据INSERT INTO B(UNAME) VALUES('LESLIE');UPDATE B SET UNAME='LESLIE1' WHERE ID=001;DELETE FROM B WHERE ID=002;COMMIT;
可以使用触发器来实现一些统计数据表的自动更新
--触发器来统计各个部门的人数和薪水--创建EMP的统计映射表：EMP_TOTALCREATE TABLE EMP_TOTAL ASSELECT DEPTNO, COUNT(EMPNO) AS TOTAL_NUMS, SUM(SAL) AS TOTAL_SAL FROM EMP GROUP BY DEPTNO;--创建触发器CREATE OR REPLACE TRIGGER EMP_TRIGGERAFTER INSERT OR DELETE OR UPDATE ON EMPDECLARECURSOR CUR_EMP ISSELECT DEPTNO, COUNT(EMPNO) AS TOTAL_NUMS, SUM(SAL) AS TOTAL_SALFROM EMPGROUP BY DEPTNO;BEGINDELETE EMP_TOTAL; --插入统计数据之前先清空原来数据FOR V_EMP IN CUR_EMP LOOPDBMS_OUTPUT.PUT_LINE(V_EMP.DEPTNO || V_EMP.TOTAL_NUMS ||V_EMP.TOTAL_SAL);INSERT INTO EMP_TOTALVALUES(V_EMP.DEPTNO, V_EMP.TOTAL_NUMS, V_EMP.TOTAL_SAL);END LOOP;END;--测试数据DELETE FROM EMP WHERE EMPNO=8888;INSERT INTO EMP (EMPNO, DEPTNO, SAL) VALUES (8888, 10, 1000);INSERT INTO EMP (EMPNO, DEPTNO, SAL) VALUES (9999, 10, 1000);SELECT * FROM EMP;SELECT * FROM EMP_TOTAL;--触发器启用与禁用ALTER TRIGGER trigger_name DISABLE;ALTER TRIGGER trigger_name ENABLE;
通过触发器来自动记录被删除的数据
CREATE TABLE PEOPLE(ID VARCHAR2(10),UNAME VARCHAR2(10),AGE NUMBER);INSERT INTO PEOPLE VALUES(100,'TONY',30);INSERT INTO PEOPLE VALUES(101,'LESLIE',20);INSERT INTO PEOPLE VALUES(102,'CHYICHIN',50);--创建一张保存被删除的数据的表CREATE TABLE PEOPLE_DELDATA AS SELECT * FROM PEOPLE WHERE 1=2;--创建触发器CREATE OR REPLACE TRIGGER PEOPLE_TRIGGERAFTER DELETE ON PEOPLEFOR EACH ROW --语句级触发，每行都触发一次BEGININSERT INTO PEOPLE_DELDATA VALUES (:OLD.ID, :OLD.UNAME, :OLD.AGE); --:OLD表示删除的值END;SELECT * FROM PEOPLE;DELETE FROM PEOPLE WHERE ID=100;SELECT * FROM PEOPLE_DELDATA;--这张表将会通过触发器自动保存删除过的数据
