第七章 使用pl/sql编程
初识pl/sql（procedure language & structured query language）
pl/sql是oracle在标准sql语言上的过程性扩展，允许嵌入sql语句，定义变量和常量允许过程语言结构（条件分支语句和循环语句）允许使用异常来处理oracle错误 可以用于创建存储过程、触发器和程序包等，也可以用于处理业务 规则、数据库事件或给sql命令的执行添加程序逻辑
普通sql语言只能实现访问，操作数据；
pl/sql可以实现流程控制，异常处理，创建可存储的代码块；
pl/sql块
所有的pl/sql程序都以块作为基本单位
块中包含过程化语句和sql的dml语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）
块的分类
1. 无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。
2. 命名块（named）：是带有名称的匿名块，这个名称就是标签。
3. 子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。
4. 触发器（trigger）：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
5. 程序包/包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。
pl/sql块的结构
示例1：
declare
v_name varchar2(20) := ‘郭老师’;
begin
dbms_output.put_line(v_name||’的第一条pl/sql语句’);
end;
与用户交互输入参数
declare
v_name varchar2(20):= ‘&input_name’;
begin
dbms_output.put_line(v_name||’的第一条pl/sql语句’);
end;
示例2：
declare
v_name1 varchar2(32);
v_name2 varchar2(32) := ‘abc’;
v_num1 number := 13;
v_num2 number;
begin
dbms_output.put_line(v_name1);
dbms_output.put_line(v_name1 || v_name2);
dbms_output.put_line(v_num1);
dbms_output.put_line(v_num1 + v_num2);
end;
注意：
1. 声明变量时，必须要指定类型
2. 变量名需要先赋值，后使用
3. 变量名没有默认值（表现是空，无意义）
示例3：
constant 定义常量值，定义后无法修改
declare
v_number1 number := 13;
v_number2 constant number := 3.14;
begin
dbms_output.put_line(v_number1 * v_number2);
end;
示例4：
变量与指定的列的类型一致 采用%type
declare
v_num1 employees.salary%type := 13.234;
v_num2 employees.manager_id%type := 2;
begin
dbms_output.put_line(v_num1 / v_num2);
end;
示例5：
dml结果装载入plsql变量
declare
v_salary employees.salary%type;
begin
select salary into v_salary from employees where employee_id=198;
dbms_output.put_line(‘v_salary:’||v_salary);
end;
示例6：
%rowtype表示数据类型是一行数据
declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where employee_id = 100;
dbms_output.put_line(‘部门编号:’||v_emp.department_id ||
‘ 员工姓名:’|| v_emp.first_name);
end;
insert操作，接收返回值
示例6：
返回值拼接
update操作，接收返回值
示例7：
delete操作，接收返回值
示例8：
数组类型
题：通过用户输入的员工号，查询一行记录
异常处理
|
异常名称 |
异常编码 |
异常解释 |
|
timeout_on_resource |
ora-0051 |
发生超时 |
|
too_many_rows |
ora-1422 |
select into命令返回的多行 |
|
transaction_backed_out |
ora-006 |
由于死锁提交被退回 |
|
value_error |
ora-6502 |
转换或者裁剪错误 |
|
zero_divide |
ora-1476 |
试图被零除 |
|
login_denied |
ora-1017 |
无效的用户名或者口令 |
|
no_data_found |
ora-1403 |
查询未找到数据 |
|
not_logged_on |
ora-1012 |
还未连接就试图数据库操作 |
|
program_error |
ora-6501 |
内部错误 |
|
rowtype_mismatch |
ora-6504 |
主变量和光标的类型不兼容 |
|
storage_error |
ora-6500 |
内部错误 |
|
access_into_null |
ora-6530 |
试图访问一个未初始化的对象时出现 |
|
case_not_found |
ora-6592 |
case语句中的选项与用户输入数据不匹配时出现 |
|
cursor_already_open |
ora-6511 |
试图打开一个已打开的光标 |
|
dup_val_on_index |
ora-0001 |
试图破坏一个唯一性限制 |
|
invalid_cursor |
ora-1001 |
试图使用一个无效的光标 |
|
invalid_number |
ora-1722 |
试图对非数字值进行数字操作 |
流程控制
if
if <布尔表达式> then
pl/sql 和 sql语句
else
其它语句
end if;
case
case 条件表达式
when 条件表达式结果1 then
语句段1
when 条件表达式结果2 then
语句段2
……
when 条件表达式结果n then
语句段n
[else 条件表达式结果]
end;
loop 三种循环方法
loop
要执行的语句;
exit when <条件语句>; –条件满足，退出循环语句
end loop;
while
while <布尔表达式> loop
要执行的语句;
end loop;
for 推荐
for 循环计数器 in [ reverse ] 下限 .. 上限 loop
要执行的语句;
end loop ;
in 每次循环加一；
in reverse 每次循环减一；
exit 退出循环；
数组的输出采用for循环方式，打印出来