【Java EE 学习 29 上】【PL/SQL】【存储过程】【存储函数】【触发器】
一、PL/SQL简介
1.概念：PL/SQL语言是Oracle数据库专用的一种高级程序设计语言，是对标准SQL语言进行了过程化扩展的语言。
2.功能：既能够实现对数据库的操作，也能够通过过程化语言中的复杂逻辑结构完成复杂的业务逻辑。
3.特点
（1）与SQL语言紧密集成，所有的SQL语句在PL/SQL中都能够得到支持。
（2）减小网络流量，提高应用程序的运行性能。
（3）模块化的程序设计功能，提高了系统可靠性。
（4）服务器端程序设计可移植性好。
4.PL/SQL块的标准结构
DECLARE
声明部分，定义变量、常量数据类型、游标、异常、局部子程序等
BEGIN
执行部分，实现块的结构
EXCEPTION
异常处理部分，处理程序执行过程中产生的异常
END;
5.分类：
（1）匿名块：是指动态生成、只能执行一次得块。
（2）命名块：是指一次编译可以多次执行的PL/SQL程序，包括函数、存储过程、包、触发器等。他们编译后放在服务器中，由应用程序或者系统在特定条件下调用执行。
6.数据类型：
（1）SQL中使用的基本数据类型在PL/SQL中都可以使用
（2）%TYPE和%ROWTYPE
%TYPE：定义一个变量的数据类型或者数据库表中某个列的数据类型一致（不知道该变量和列的数据类型），如v_sal emp.sal%TYPE;
%ROWTYPE：定义一个与数据库中某个表结构一致的记录类型的变量，如v_emp emp%ROWTYPE。
7.注意：如果在PL/SQL块中对表进行了修改，即执行了insert/update/delte语句的话，在最后一定要提交才行，因为oracle数据库的隔离级别是read committed。
二、PL/SQL运用
1.Hello,world !
SET SERVEROUTPUT ON; DECLARE BEGIN dbms_output.put_line('HELLO,WORLD!'); END; /
2.if语句
（1）接收键盘输入：accept xxxx prompt '提示信息';
（2）使用地址符&
要求：输入数字并进行判断
SET SERVEROUTPUT ON;--开启命令行输出 SET FEEDBACK OFF; --关掉回显 ACCEPT NUM PROMPT '请输入一个数字：';--从命令行中接收数字并放到NUM变量中。 DECLARE PNUM NUMBER :=# BEGIN IF PNUM=1 THEN DBMS_OUTPUT.PUT_LINE('输入数字：1'); ELSIF PNUM=2 THEN DBMS_OUTPUT.PUT_LINE('输入数字：2'); ELSIF PNUM=3 THEN DBMS_OUTPUT.PUT_LINE('输入数字：3'); ELSE DBMS_OUTPUT.PUT_LINE('其他数字！'); END IF ; END; /
3.循环
这里只介绍一种循环的使用方法
SET SERVEROUTPUT ON; DECLARE NUM NUMBER:=1; BEGIN LOOP EXIT WHEN NUM>10; DBMS_OUTPUT.PUT_LINE(NUM); NUM:=NUM+1; END LOOP; END; /
4.异常
（1）系统异常：以除以零引发的异常为例
SET SERVEROUTPUT ON; DECLARE NUM NUMBER; BEGIN NUM:=10/0; DBMS_OUTPUT.PUT_LINE(NUM); EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('除数为零！'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其它异常！'); END; /
执行结果：
（2）自定义异常
DECLARE CURSOR CEMPT IS SELECT ENAME FROM EMP WHERE DEPTNO=40; PENAME EMP.ENAME%TYPE; MYEXCEPTION EXCEPTION; --使用EXCEPTION作为类型使用 BEGIN OPEN CEMPT; FETCH CEMPT INTO PENAME; IF CEMPT%notfound THEN RAISE MYEXCEPTION;--使用RAISE抛出异常 END IF; DBMS_OUTPUT.PUT_LINE('没有异常发生！'); CLOSE CEMPT; EXCEPTION WHEN MYEXCEPTION THEN if cempt%isopen then dbms_output.put_line('没有关闭游标！');close cempt; end if; DBMS_OUTPUT.PUT_LINE('没有查找到结果！'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其它异常！'); END; /
运行结果：
5.游标curse
（1）概念：游标是指向查询结果所在缓冲区的句柄或者指针。
（2）分类：
显示游标：由用户定义、操作。用于处理返回多行数据的select语句。
隐式游标：有系统自动进行，用于处理DML语句和返回多行数据的select语句。
（3）注意事项
只有打开游标，才能真正创建缓冲区，并从数据库检索数据。
游标一旦打开，就无法关闭，除非先关闭。
（4）对游标的几个操作命令
定义游标：cursor cursor_name is select_statement;
打开游标：open cursor_name;
关闭游标：close cursor_name;
检索游标：fetch cursor_name into variable_list|record_variable;
（5）使用完游标之后一定要关闭游标，以释放游标所占用的资源。
oracle允许同时使用多少个游标？使用show parameters cursor;命令查看，注意必须是管理员才能查看。
（6）显式游标的几个属性
%ISOPEN：检查游标是由已经打开
%FOUND：判断最近一次使用FETCH语句的时候是否从缓冲区中国检索到数据。检索到数据返回true，否则返回false。
%NOTFOUND：判断最近一次使用FETCH语句的时候是否从缓冲区中检索到数据。没有检索到返回true，否则返回false。
%ROWCOUNT：返回到目前为止从游标缓冲区检索的记录的个数。
%BULK_ROWCOUNT(i)：用于取得FORALL语句执行批绑定操作室第i个元素所影响的行数。
注意：游标属性只能在PL/SQL块中使用，不能在SQL命令中使用。
（7）使用PL/SQL，利用游标遍历emp表
set serveroutput on; declare cursor mycursor is SELECT * from emp; rowemp emp%rowtype; begin open mycursor; loop fetch mycursor into rowemp; exit when mycursor%notfound; dbms_output.put_line(rowemp.ename||'的薪水是：'||rowemp.sal); end loop; close mycursor; end; /
（8）带参数的游标
参数化游标是指定义游标的时候使用参数，当使用不同的参数值打开游标的时候，可以生成不同的结果集。参数化游标显示游标的定义语法为：
cursor cursor_name (paramete1 datatype,......) is select_statement;
在执行的时候打开参数化显示游标的语法为：
open cursor_name(parameter1,......);
（9）但参数的游标的使用举例
查询7369号员工的薪水：
--带参数的游标使用方法举例。 set serveroutput on; declare cursor cemp(pempno number) is select * from emp where pempno=empno; rowemp emp%rowtype; begin open cemp(7369); loop fetch cemp into rowemp; exit when cemp%notfound; dbms_output.put_line(rowemp.ename||'的薪水是：'||rowemp.sal); end loop; close cemp; end; /
运行结果：
三、存储过程
1.存储子程序：存储子程序是指被命名的PL/SQL块，以编译的形式存储在数据库服务器中。可以在应用程序中调用，是PL/SQL程序模块化的一种体现。存储子程序分为存储过程和存储函数两种。
2.创建存储过程的语法：
create [or replace ] procedure procedure_name(参数列表) as PL/SQL子程序体
3.存储过程的调用
（1）EXECUTE procedure_name(参数列表);
（2）CALL procedure_name(参数列表);
（3）begin
procedure(参数列表);
end
4.无参存储过程的创建和调用。
注意无参数的存储过程创建的时候不需要带有括号。
以打印hello,world为例。
create or replace procedure printHello as begin dbms_output.put_line('Hello,world!'); end; /
调用和输出：
5.in与out
参数列表中的参数都带有in或者out或者in out其中的一种，最后一种不经常用，故不赘述。
（1）in：传入参数到存储过程中。
举例：为30号部门的员工涨100元工资。
create or replace procedure pre(pdeptno in NUMBER) as begin update emp1 set sal=sal+100 where deptno=pdeptno; --dbms_output.put_line('Hello,world!'); commit; end; /
运行结果：
（2）out：从存储过程中获取一个或者多个返回值
查询某个员工的姓名、月薪、职位
create or replace procedure searchEmpByEmpno(pempno in NUMBER,pename out varchar2, pjob out varchar2,psal out number) as begin select ename,job,sal into pename,pjob,psal from emp where empno=pempno; end; /
测试代码：
set serveroutput on; declare pdeptno number; pename VARCHAR2(20); pjob VARCHAR2(20); psal NUMBER; begin pdeptno:=7369; searchEmpByEmpno(pdeptno,pename,pjob,psal); dbms_output.put_line(pename||'的工作是：'||pjob); dbms_output.put_line(pename||'的工资是：'||psal); end; /
运行结果：
6.out参数中使用游标
问题的提出：如果需要返回的是多个参数，极端情况下可能会有几百个参数，这种情况下不可能将这些所有的参数都写到out参数列表中（当然也可以，只要不怕麻烦）。能有一种方法将返回值封装到一个对象中，需要的参数再从对象中获取吗？在java中可以使用java bean进行封装，在oracle中则使用游标。
（1）新建包，声明存储过程
--声明包 CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is REF CURSOR; PROCEDURE queryEmpList(pempno in NUMBER,empList out empcursor); END MYPACKAGE;
（2）实现包体，即实现存储过程
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS PROCEDURE queryEmpList(pempno in NUMBER,empList out empcursor) AS BEGIN open empList for select * from emp where pempno=empno; END queryEmpList; END MYPACKAGE;
（3）调用存储过程：根据员工号获取所有相关信息，注意这里只有两个out参数。
--测试带有游标的out参数。 set serveroutput on; declare pempno number; empList mypackage.empcursor; empRow emp%rowtype; begin pempno:=7369; mypackage.queryemplist(pempno,empList); --open emplist;--注意一定不要再次open，因为游标只能打开一次，否则会报错。 loop fetch emplist into empRow; exit when emplist%notfound; dbms_output.put_line(empRow.ename||'的工资是：'||empRow.sal); end loop; null; close emplist; end; /
（4）调用结果
（5）注意事项：游标不能重复打开，所以调用的时候不能再使用open命令打开游标，否则会报错；但是一定要记得关闭游标。
四、存储函数
1.创建格式
create [or replace] function(参数列表) return 返回值类型 as PL/SQL子程序体
2.举例：根据员工号查找员工姓名，并返回改姓名
create or replace function myfun(fempno in number) return VARCHAR2 as fename VARCHAR2(20); begin select ename into fename from emp where empno=fempno; return fename; end; /
3.测试该函数
select myfun(7369) from dual;
运行结果：
4.函数中的参数列表规则和存储过程中参数列表规则完全相同。
5.函数和存储过程有什么区别和联系
（1）函数和存储过程都可以有返回值，而且返回值都可以有一个或者多个。
（2）默认情况下如果只是有一个返回值，则使用函数，如果有多个返回值则使用存储过程。
（3）函数和存储过程同时存在的原因是历史遗留问题。
（4）其它没有任何不同之处。
五、触发器
1.触发器的概念和作用：触发器是一种特殊类型的存储过程，变一周存储在数据库服务器中，当特定事件发生的时候，由系统自动调用执行，而不能由应用程序显式的调用执行。此外触发器不接受任何参数。
2.触发器创建的通用模板
CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER trigger_event [OF column_name] ON table_name [FOR EACH ROW] [WITH trigger_condition] DECLARE /*声明模块*/ BEGIN /*执行模块*/ EXCEPTION /*异常处理模块*/ END [trigger_name];
3.触发器1：向emp表插入一条记录之后，就自动打印“成功插入一条员工信息！”
create or replace trigger printInfo after insert on emp1 declare begin dbms_output.put_line('成功插入了一条员工信息！'); end;
运行结果：
4.触发器的应用场景1：实施复杂的安全性检查
禁止在非工作时间向emp表插入数据。
--触发器的应用场景1：实施复杂的安全性检查 --禁止在非工作时间向emp表插入数据 drop trigger empCheck; create or replace trigger empCheck before insert on emp1 declare begin if to_char(sysdate,'day') in ('星期六','星期日') or to_number(to_char(sysdate,'hh24')) not between 8 and 18 then raise_application_error(-20001,'不能在非工作时间向emp表插入数据！'); end if; end;
如果将时间调到星期六或者星期天或者任何一天的早上8点之前或者晚上6点之后，这个时候如果向emp1表中插入数据，则会报错：
5.触发器的应用场景2：涨后的薪水不能低于涨前的薪水
--触发器的应用场景2：数据确认 --涨之后的工资一定要比涨之前的工资高。 --使用:new和:old不允许在表级触发器中 create or replace trigger checkSal before update on emp1 for each row declare begin if :new.sal<:old.sal then raise_application_error(-20001,'涨之后的工资应当比涨之前的工资高！'); end if; end;
注意事项：:new和:old不能出现在表级触发器中。
测试触发器：
6.触发器的应用场景3：同步分布式数据库
http://www.cnblogs.com/kuangdaoyizhimei/p/4756674.html
|----------七、分布式数据库中
|----------5.触发器应用场景三：同步数据
