本文为在安农大授课备课笔记。
高级数据库编程
1、PL/SQL 简介：
PL/SQL 代表 Procedural Language/SQL（Procedural Language：过程语言）
它是对 SQL 的扩展
严密的安全性
对客户机和服务器之间的应用程序逻辑进行分隔，客户端只执行存储过程
2、PL/SQL 引擎
处理 PL/SQL 块并将其分离为 SQL 语句及过程语句：
a.将过程语句发送到过程语句执行器以进行处理
b.将 SQL 语句发送到 SQL 语句执行器以进行处理
3.PL/SQL块的结构
a.PL/SQL 的各个组成部分:
声明部分
可执行部分
异常处理部分
b.PL/SQL 块的结构:
DECLARE
declarations
BEGIN
executable statements
EXCEPTION
handlers
END;
其中，declarations 是声明，executable statements 是可执行语句，handlers 是处理程序
4.输出：DBMS_OUTPUT.PUT_LINE();
5.如何定义变量:
格式：declare 变量名1 数据类型1：=初始值1;
declare mynum varchar2(20):='abc';
6.对变量赋值有两种,分别见下面的例子
a、:=进行赋值
b、 select into 变量名进行赋值
第一种赋值:
Declare
n number;str varchar2(20);
begin
n:=100*3;
str:='&abc';--弹出对话框，获取输入的赋值给变量str
dbms_output.put_line(n||‘,’||str);--其中||是连接字符串的意思，并打印到界面
end;
第二种赋值:
select tname into tnames from temp where tid=2;
--打印到界面
dbms_output.put_line('类型名称:'|| tnames);
7、条件控制语句：
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
IF <条件> THEN
语句;
END IF;
IF <条件> THEN
语句;
ELSE
语句;
END IF;
IF <条件1> THEN
语句;
ELSIF <条件2> THEN
语句;
ELSIF <条件3> THEN
语句;
ELSE
语句;
END IF;
8.循环控制语句：
使用loop,无条件循环,从1累加到100，采用exit配合if退出
declare
i number:=1; --定义循环次数变量i
total number:=0; --定义累加结果变量total
begin
loop
total:=total+i;
i:=i+1;
if i>100 then
exit;
end if;
end loop;
dbms_output.put_line('最终结果：'||total);
end;
9、异常
当在 PL/SQL 程序中出现错误时，将引发异常
在出现错误时，正常执行将停止，控制权转移到异常处理部分
declare
i number:=0; n number:=0;
begin
i:=100/n;--这里会报错，除数为0
dbms_output.put_line ('结果：'||i);
Exception
--当不知道是什么错误的时候就写others
when others then
--sqlerrm这个会打印错误信息
dbms_output.put_line('异常：'||sqlerrm);
end;
10、存储过程的定义和使用
实际开发中通常把复杂的业务封装在过程中。
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration> 声明变量declare关键字
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
注：通过处理我们一般都想得到一个或者多个结果，如何通过存储过程带出一个或多个结果呢？
过程参数的三种模式：
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值
执行存储过程可以使用EXEC命令或者在其它过程中调用。
11、存储函数的定义和使用
我们已经很多ORACLE内置的函数，那么能不能自己定义函数呢？
函数是可以返回值的命名的 PL/SQL 子程序。
创建函数的语法：
CREATE [OR REPLACE] FUNCTION
function name [(param1,param2)]
RETURN datatype IS|AS -- 返回值类型
[local declarations]
BEGIN
Executable Statements;
RETURN result; -- 记得要返回结果
EXCEPTION
Exception handlers;
END;
类比java 语言中的方法定义
删除函数 drop function fun_name;
12.触发器的定义和使用
触发器是当特定事件出现时自动执行的存储过程
特定事件可以是执行更新的DML语句和DDL语句
触发器不能被显式调用
触发器的功能：
自动生成数据
自定义复杂的安全权限
提供审计和日志记录
启用复杂的业务逻辑
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;
注意：
:new 和 :old 是oracle触发器里的行处理时的新值和旧值的行记录。
是固定用法。
可以
UPDATE E1 SET DEPTNO = ‘ABC’ || :NEW.DEPTNO WHERE DEPTNO =:OLD.DEPTNO;
也可以在触发器中定义一个变量 v_aaa，然后赋值：
v_aaa := :NEW.DEPTNO;