一，PL SQL

1）介绍

PL/SQL 块是在sql 语句中语言之上发展起来的一种应用，可以集中的处理各种复杂的SQL 操作。

语句格式：

DECLARE：声明部分BEGIN编写主题EXCEPTION捕获异常END/

2）do…while 循环

PL/SQL 之中也包含了：循环、分支等条件控制语句。

语句格式：

LOOP循环的语句EXIT WHEN 终止条件循环条件必须更改END LOOP ;

循环输出 1~10：

DECLAREcou NUMBER ;BEGIN-- 必须给一个初始值cou := 1;LOOPDBMS_OUTPUT.put_line('cou = ' || cou);EXIT WHEN cou > 10;cou := cou + 1;END LOOP;END ;/--此循环会先执行一次再进行循环

3）while 循环

格式：

while(判断循环的条件) loop循环的语句;循环条件的改变;END loop;

循环输出 1~10：

DECLAREcou NUMBER ;BEGIN-- 必须给一个初始值cou := 1;WHILE(cou < 10) LOOPDBMS_OUTPUT.put_line('cou = ' || cou);cou := cou + 1;END LOOP;END;/--此循环会先判断再执行语句

4）for 循环

格式：

FOR 变量名 in 变量的初始值..结束值 lOOP循环语句;END loop;

循环输出 1~10：

DECLAREcou NUMBER ;BEGIN--此语句会自动将1到10赋值给couFOR cou in 1..10 loopDBMS_OUTPUT.put_line('cou = ' || cou);END LOOP;END ;/

5）if 条件判断

格式：

IF 条件 THEN满足条件时, 执行此语句END IF;

案例：

DECLAREcou NUMBER ;BEGINcou := 11;IF cou > 10 THENDBMS_OUTPUT.put_line('cou = ' || cou);end if;END ;/--输出11

6）if…else 条件判断

格式：

IF 条件 THEN满足条件时, 执行此语句END IF;

案例：

DECLAREcou NUMBER ;BEGINcou := 8;IF cou > 10 THENDBMS_OUTPUT.put_line('cou = ' || cou);ELSEDBMS_OUTPUT.put_line('cou = ' || cou);end if;END ;/--输出8

7）if…elsif…else 条件判断

格式：

IF 条件 THEN满足条件时, 执行此语句ELSIF 条件 THEN满足此条件时，执行此语句ELSEEND IF;

案例：

DECLAREcou NUMBER ;BEGINcou := 8;IF cou > 10 THENDBMS_OUTPUT.put_line('cou = ' || cou);ELSIF cou < 5 THENDBMS_OUTPUT.put_line('cou = ' || cou);ELSEDBMS_OUTPUT.put_line('cou = ' || cou);end if;END ;/--输出8

8）GOTO 语句

格式：

IF 条件 THEN满足条件时, 执行此语句ELSIF 条件 THEN满足此条件时，执行此语句ELSEEND IF;

案例：

DECLAREemo EMPLOYEE.EMP00%TYPE;name EMPLOYEE.EMP01%TYPE;BEGINemo := '70443BA4-7174-46CC-8A7D-6DA04627B770';SELECT EMP01 INTO name FROM EMPLOYEE WHERE EMP00 = emo;IF name='周康' THENGOTO po1;ELSIF name='白安' THENGOTO po2;ELSEGOTO po3;end if;<<po1>>DBMS_OUTPUT.put_line('po1');<<po2>>DBMS_OUTPUT.put_line('po2');<<po3>>DBMS_OUTPUT.put_line('po3');END ;/

二，游标、函数

1）游标

游标是一种PL/SQL控制结构，可以对SQL 语句的处理进行显示控制，便于对表的行数据进行逐条处理。游标并不是一个数据库对象，只是存留在内存中。

操作步骤：

声明游标打开游标取出结果，此时的结果取出是一整行数据关闭游标使用ROWTYPE类型，此类型表示可以把一整行的数据都装进来

DECLAREemp EMPLOYEE.EMP00%TYPE; --定义一个参数empInfo EMPLOYEE%rowtype; --定义一个rowType, 用来保存一整行数据BEGINemp := '7350F9DE-F5BC-461A-B499-F86680D2DA4E';SELECT * INTO empInfo FROM EMPLOYEE WHERE EMP00 = emp;DBMS_OUTPUT.PUT_LINE('姓名=' || empInfo.EMP01); --可以通过emoInfo. 的形式来访问这一行中的所有字段end;

2）使用for循环操作游标（常用）

DECLARECURSOR mycur IS SELECT * FROM EMPLOYEE; --List(EMPLOYEE)empInfo EMPLOYEE%rowtype;BEGIN--游标操作使用循环, 但是在操作之前必须先将游标打开OPEN mycur;--使游标下移一行FETCH mycur INTO empInfo;--判断此行是否有数据被发现WHILE (mycur%FOUND) LOOPDBMS_OUTPUT.put_line('姓名=' || empInfo.EMP01);--修改游标往下FETCH mycur INTO empInfo;end loop;end;/--表中所有人的姓名都被输出了

3）游标案例练习

现要给所有部门的员工上涨工资，根据他们所在的部门涨工资，规则如下：

10 部门上涨10%20 部门上涨20%30 部门上涨30%所有部门的员工上涨工资最多不能超过 5k，如果超过则按 5k 计算。

DECLAREcursor mycur is SELECT * FROM emp;empInfo emp%rowtype;s emp.sal%TYPE;BEGINfor empInfo in mycur loopif empInfo.deptno = 10 thens := empInfo.sal * 1.1;elsif empInfo.deptno = 20 thens := empInfo.sal * 1.2;elsif empInfo.deptno = 30 thens := empInfo.sal * 1.3;end if;if s > 5000 thens = 5000;end if;UPDATE emp SET sal = s WHERE emp00 = empInfo.emp00;end loop;end;/

4）函数

函数就是一个有返回值的过程。定义一个函数，此函数可以根据传进来的员工编号查出该员工目前的岗位。

CREATE OR REPLACE FUNCTION "function"(emp IN varchar2) RETURN VARCHAR2ASDMCPT VARCHAR2(1024);BEGINSELECT DMCPT INTO DMCPT FROM EMPLOYEE LEFT JOIN DM07 ON EMPLOYEE.EMP11 = DM07.DMCOD WHERE EMP00 = emp;RETURN DMCPT;end;/

SELECT "function"('7350F9DE-F5BC-461A-B499-F86680D2DA4E') FROM DUAL;

函数可以用来处理一些复杂的逻辑业务，相当于把代码用sql 的方式给写出来，同时也可以将一些通用的转格式之类的代码抽象成一个函数。可参考：返回指定日期与当前日期的时间差函数

三，存储过程

与过程相比，存储过程是存在数据库中的一个对象。如果编译错误，可以用show errors or show errors procedure myproc。

定义一个简单的存储过程：

CREATE OR REPLACE PROCEDURE myproc1 --PROCEDURE n.过程, 程序ASi NUMBER;BEGINi := 100;DBMS_OUTPUT.PUT_LINE('i=' || i); --调用存储过程, 直接输出100END;/

如果是命令行窗口的话，exec myproc1就可以调用存储过程了，如果是sql 窗口下的话需要用到PLSQL语句

DECLARE定义参数;BEGINmyproc1; --如果存储过程有参, 则需要传参END;

编写一个存储过程，可以传入人员的编号、姓名、性别（0，1代替），之后调用此存储过程就可以完成人员的增加操作。

CREATE OR REPLACE PROCEDURE myproc2(ENO EMPLOYEE.EMP00%TYPE, NAME EMPLOYEE.EMP01%TYPE, XB EMPLOYEE.EMP02%TYPE)AScou NUMBER;BEGIN--首先判断该编号是否被使用, 如果被使用了就不能添加SELECT COUNT(EMP00) INTO cou FROM EMPLOYEE WHERE EMP00 = ENO;IF cou = 0 THEN--可以添加INSERT INTO EMPLOYEE(EMP00, EMP01, EMP02) VALUES (ENO, NAME, XB);DBMS_OUTPUT.PUT_LINE('人员插入成功!');ELSEDBMS_OUTPUT.PUT_LINE('该人员编号已存在, 无法插入!');END IF;END;/beginmyproc2('7350F9DE-F5BC-461A-B499-F86680D2DA4F', '阴阳人', '1');end;

存储过程的参数类型，在指定参数类型时添加，类似myproc2(ENO IN OUT EMPLOYEE.EMP00%TYPE)：

IN：值传递，默认值IN OUT：带值进，带值出OUT：不带值进，带值出

查看所有存储过程：

SELECT * FROM USER_SOURCE;

删除存储过程：

DROP PROCEDURE myproc2;

四，触发器

存放在数据库中，并被隐含执行的存储过程。在Oracle 8i之前，只允许给予表和视图的DML操作，从 8i 之后，不仅可以支持DML触发器，也允许给予系统事件和DDL的操作。

1）语句触发器

BEFORE 语句触发器，可以控制对表的INSERT OR UPDATE OR DELETE操作前进行过滤：

--禁止在休息日改变员工信息CREATE OR REPLACE TRIGGER TR_SRC_EMP --TRIGGER n.触发器BEFORE INSERT OR UPDATE OR DELETEON EMPBEGINIF TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT', 'SUN') THENRAISE_APPLICATION_ERROR(-20001, 'CAN‟T MODIFY USER INFORMATION IN WEEKEND');END IF;END;/

--可以监听此处操作是什么类型CREATE OR REPLACE TRIGGER TR_SRC_EMPBEFORE INSERT OR UPDATE OR DELETEON EMPBEGINIF TO_CHAR(SYSDATE, 'DY') IN ('星期六', '星期天') THENCASEWHEN INSERTING THENRAISE_APPLICATION_ERROR(-20001, 'FAIL TO INSERT');WHEN UPDATING THENRAISE_APPLICATION_ERROR(-20001, 'FAIL TO UPDATE');WHEN DELETING THENRAISE_APPLICATION_ERROR(-20001, 'FAIL TO DELETE');END CASE;END IF;END;/

AFTER语句触发器，在表进行增删改操作之后进行的操作：

--为了统计在EMP表中增、删、改的次数，建立一张统计表CREATE TABLE AUDIT_TABLE(NAMEVARCHAR2(20),INS INT,UPD INT,DEL INT,STARTTIME DATE,ENDTIME DATE);

--建立触发器, 分类统计增删改的次数CREATE OR REPLACE TRIGGER TR_AUDIT_EMPAFTER INSERT OR UPDATE OR DELETEON EMPDECLAREV_TEMP INT;BEGINSELECT COUNT(*) INTO V_TEMP FROM AUDIT_TABLE WHERE NAME ='EMP';IF V_TEMP = 0 THENINSERT INTO AUDIT_TABLE VALUES ('EMP', 0, 0, 0, SYSDATE, NULL); --如果没有EMP表的记录, 那就新建一条END IF;CASEWHEN INSERTING THENUPDATE AUDIT_TABLE SET INS=INS + 1, ENDTIME=SYSDATE WHERE NAME = 'EMP'; --如果是新增操作, 就将INS+1, 其他类型同理WHEN UPDATING THENUPDATE AUDIT_TABLE SET UPD=UPD + 1, ENDTIME=SYSDATE WHERE NAME = 'EMP';WHEN DELETING THENUPDATE AUDIT_TABLE SET DEL= DEL + 1, ENDTIME=SYSDATE WHERE NAME = 'EMP';END CASE;END;/

2）行触发器，执行DML操作时，每作用一行就触发一次触发器

BEFORE 行触发器

--更新员工工资时, 新工资不能低于原来的工资CREATE OR REPLACE TRIGGER TR_EMP_SALBEFORE UPDATE OF SAL --工资字段的更新触发器ON EMPFOR EACH ROW --为每行建立行触发器BEGINIF :NEW.SAL < :OLD.SAL THEN --如果新工资小于旧工资RAISE_APPLICATION_ERROR(-20010, 'SAL SHOULD NOT BE LESS');END IF;END;/

AFTER 行触发器

--新建一张过程表, 统计员工的工资变化CREATE TABLE AUDIT_EMP_CHANGE(NAME VARCHAR2(10),OLDSAL NUMBER(6, 2),NEWSAL NUMBER(6, 2),TIME DATE);

--建立触发器, 统计员工工资变化CREATE OR REPLACE TRIGGER TR_SAL_SALAFTER UPDATE OF SAL --工资更新之后触发ON EMPFOR EACH ROWDECLAREV_TEMP INT;BEGINSELECT COUNT(*) INTO V_TEMP FROM AUDIT_EMP_CHANGE WHERE NAME = :OLD.ENAME;IF V_TEMP = 0 THENINSERT INTO AUDIT_EMP_CHANGE VALUES (:OLD.ENAME, :OLD.SAL, :NEW.SAL, SYSDATE);ELSEUPDATE AUDIT_EMP_CHANGESET OLDSAL=:OLD.SAL,NEWSAL=:NEW.SAL,TIME=SYSDATEWHERE NAME = :OLD.ENAME;END IF;END;/

触发器添加限制行：

CREATE OR REPLACE TRIGGER TR_SAL_SALAFTER UPDATE OF SALON EMPFOR EACH ROWWHEN (OLD.JOB ='SALESMAN') --只有当员工的JOB='SALESMAN' 时, 更新工资才会触发这个触发器DECLAREV_TEMP INT;BEGINSELECT COUNT(*) INTO V_TEMP FROM AUDIT_EMP_CHANGE WHERE NAME = :OLD.ENAME;IF V_TEMP = 0 THENINSERT INTO AUDIT_EMP_CHANGE VALUES (:OLD.ENAME, :OLD.SAL, :NEW.SAL, SYSDATE);ELSEUPDATE AUDIT_EMP_CHANGESET OLDSAL=:OLD.SAL,NEWSAL=:NEW.SAL,TIME=SYSDATEWHERE NAME = :OLD.ENAME;END IF;END;/

3）触发器注意事项

编写DML触发器时，触发器代码不能从触发器所对应的基表中读取数据。

如果基于EMP 表建立触发器，那么该触发器的执行代码不能包含对EMP 表的查询操作。

CREATE OR REPLACE TRIGGER TR_EMP_SALBEFORE UPDATE OF EMP01ON EMPLOYEEFOR EACH ROWDECLARENAME VARCHAR2(8);BEGINSELECT EMP01 INTO NAME FROM EMPLOYEE;IF :NEW.EMP01 = NAME THENRAISE_APPLICATION_ERROR(-21000, 'ERROR');END IF;END;/--创建的时候不会报错， 但是更新表数据就会报错UPDATE EMPLOYEE SET EMP01='周康' WHERE EMP00 = '7350F9DE-F5BC-461A-B499-F86680D2DA4E';

4）系统事件触发器

触发器可以帮我们做一些数据校验、数据记录等工作，在Oracle 中有一些系统事件触发器是内置的，基于Oracle 的系统事件（LOGON，STARTUP）所建立的触发器。通过使用系统触发器，提供了跟踪系统或数据库变化的机制，一些内置的系统触发器如下：

5）建立系统事件触发器

有了上述的系统事件触发器，就可以建立一些特殊的触发器了。

记录用户登录和退出事件，可以分别建立登录和退出触发器：

--建立登录退出信息表CREATE TABLE log_table(USERNAME VARCHAR2(20),LOGON_TIME DATE,LOGOFF_TIME DATE,ADDRESSVARCHAR2(20));

建立了LOG_TABLE 表之后，就可以建立触发器对这张表进行操作了，登录触发器和退出触发一定要使用特权用户身份建立，并且登录触发器只能使用 AFTER 关键字，而退出触发器只能使用 BEFORE 关键字：

CREATE OR REPLACE TRIGGER tr_logonAFTER LOGON ON DATABASEBEGININSERT INTO LOG_TABLE(USERNAME, LOGON_TIME, ADDRESS)VALUES(ORA_LOGIN_USER, SYSDATE, ORA_CLIENT_IP_ADDRESS);END;/CREATE OR REPLACE TRIGGER tr_logoffBEFORE LOGOFF ON DATABASEBEGININSERT INTO LOG_TABLE(USERNAME, LOGOFF_TIME, ADDRESS)VALUES(ORA_LOGIN_USER, SYSDATE, ORA_CLIENT_IP_ADDRESS);END;/

DDL 触发器，为了记录系统所发生的DDL 事件（create、alter、drop）等，可以建立DDL 触发器，首先建立存放DDL 信息的表：

CREATE TABLE EVENT_DDL(EVENT VARCHAR2(20),USERNAME VARCHAR2(20),OWNER VARCHAR2(10),OBJNAME VARCHAR2(20),OBJTYPE VARCHAR2(10),TIMEDATE);

建立触发器，使用AFTER关键字，记录表中发生的DDL 操作：

CREATE OR REPLACE TRIGGER tr_ddlAFTER DDL ON SCHEMABEGININSERT INTO event_ddl1 VALUES (ORA_SYSEVENT, ORA_LOGIN_USER, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;/DROP TABLE TEST;

6）管理触发器

查看所有触发器

SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS;

禁止触发器，禁止触发器指使触发器临时失效，当触发器处于ENABLE 状态时，如果在表上执行DML 操作，则会触发对应的触发器。如果基于INSERT 操作建立了触发器，当使用SQL*Loader 装载大量数据时，为了加快效率，应暂时禁止触发器：

ALTER TRIGGER TR_CHECK_SAL DISABLE;

激活触发器

ALTER TRIGGER TR_CHECK_SAL ENABLE;

批量禁止、激活触发器

ALTER TABLE EMPLOYEE DISABLE ALL TRIGGERS; --批量操作一张表所有的触发器ALTER TABLE EMPLOYEE ENABLE ALL TRIGGERS;

重新编译触发器，当使用ALTER TABLE命令修改表结构（添加删除列）时，会使其触发器转变为INVALID状态，这种情况下，为了使触发器继续生效，需要重新编译触发器：

ALTER TRIGGER TR_DDL COMPILE;

删除触发器

DROP TRIGGER TR_EMP_SAL;