韩顺平.玩转oracle - pl/sql

pl/sql

• procedural language / sql 过程化语言
  是oracle在标准的sql语言上的扩展
  不仅允许嵌入sql语言，还可以定义变量和常量
  允许使用条件语句和循环语句，允许使用例外处理各种错误

1. 过程、函数、触发器是pl/sql编写
2. 过程、函数、触发器是在oracle中
3. pl/sql是非常强大的数据库过程语言
4. 过程、函数可以在JAVA程序中调用

• 学习必要性
  1⃣️ 提高应用程序的运行性能
  2⃣️ 模块化的设计思想
  3⃣️ 减少网络传输量
  4⃣️ 提高安全性

• 缺点
  移植性不好

用什么编写pl/sql块

• sql plus开发工具 （使用较多）
  oracle公司提供的一个工具
• pl/sql developer开发工具
  用于开发pl/sql块的集成开发环境（ide），是一个独立产品

--SQL*PLUS案例
--编写一个存储过程，该过程可以向某表中添加记录
--scott用户登录
create procedure sp_pro1 is
begin
insert into mytest('韩顺平','1234')
end;
/

--pl/sql developer案例
--编写一个存储过程，该过程可以删除某表记录
create procedure sp_pro2 is
begin
delete from mytest
where name = '韩顺平';
end;
/

介绍
pl/sql变成可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块
比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块……

pl/sql可以做什么

• 简单分类
  块（编程）是基础单位
  里面包含 过程（存储过程）、函数、触发器、包

编写规范

• 注释
  单行注释： –
  select * from emp where empno = 7788; --取得员工信息
  多行注释： /* */来划分
• 标识符号的命名规范
  1）当定义变量时，建议用v_作为前缀
  2）当定义常量时，建议用c_作为前缀
  3）当定义游标时，建议用_cursor作为后缀
  4）当定义例外时，建议用e_作为前缀

pl/sql块介绍
块（block）是pl/sql的基本程序单位
编写pl/sql程序实际上就是编写pl/sql块

• 简单的应用功能： 编写一个pl/sql块
• 复杂的应用功能：在一个pl/sql块中嵌套其它的pl/sql块

块结构示意图
由三个部分组成

1. 定义部分 （ declare） 可选部分
   定义常量、变量、游标、例外、复杂数据类型
2. 执行部分（begin） 必须部分
   要执行的pl/sql语句和sql语句
3. 例外处理部分 （exception）可选部分
   处理运行的各种错误

pl/sql分类

过程
用于执行特定的操作

当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）
通过在过程中使用输入参数，可以将数据传递到执行部分
通过使用输出参数，可以将执行部分的数据传递到应用环境

在sql plus中可以使用 create procedure命令来建立过程

--编写一个过程，可以输入雇员名，新工资 可修改雇员的工资
create procedure sp_pro1(spname varcharw,newsal number) is
--执行部分，根据用户名修改工资
begin
update emp set sal = newsal
where ename = spname;
end;
／
--两种方法调用存储过程
exec sp_pro1('scott',4678);
call sp_pro1('scott',4678);

--实例一，只包含执行部分的pl/sql块
set serveroutput on --打开输出选项
begin
dbms_output.put_line ('hello');
end;
/
/*相关说明：
dbms_output是oracle所提供的包（类似java的开发包）
该包包含一些过程，put_line就是dbms_output包的一个过程  */

--实例二，包含定义部分和执行部分的pl/sql块
declare
v_ename varchar2(5); --定义字符串变量
begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line ('雇员名：'|| v_name);
end;
/

--实例三，包含定义部分、执行部分和例外处理部分的pl/sql块
--为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理
declare
v_ename varchar2(5); --定义字符串变量
begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line ('雇员名：'|| v_name);
--如果输入了不存在雇员号，应当做例外处理
exception
when no_data_found then
dbms_output.put_line ('您的编号输入有误');
end;
/

函数

• 用于返回特定的数据
  当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据

可以使用create function来建立函数

--e.g.输入雇员姓名，返回该雇员的年薪
create function sp_fun(spname varchar2) return number is yearsal number(7,2);
-- yearsal 返回值的变量名
begin
select sal*12 + nvl(comm,0)*12 into yearsal from emp
where ename = spname
return yearsal;
end;
/

包
用于在逻辑上组合过程和函数，它由包规范和包体两部分组成

我们可以使用create package命令来创建包

create package sp_package is
--过程
procedure update_sal(name varchar2,newsal number);
--函数
function annual_income(name varchar2) return number;
end;
/

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码

• 包体用于实现包规范中的过程和函数

建立包体可以使用create package body命令

create package body sp_package is
procedure update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename=name;
return annual_salary;
end;
end;
/

如何调用包的过程或是函数
在过程和函数前需要带有包名
如果要访问其它方案的包，需要在包名前加方案名
exec sp_package.update_sal('scott',1500);

触发器
隐含的执行的存储过程

当定义触发器时，必须要指定触发的事件和触发的操作
常用的触发事件包括insert,update,delete语句
触发操作其实就是一个pl/sql块

可以使用create trigger来建立触发器

定义并使用变量

在编写pl/sql程序时，可以定义变量和常量
1⃣️标量类型（scalar）
2⃣️复合类型（composite）
3⃣️参照类型（reference）
4⃣️lob （large object）

标量（scalar） 常用类型
在编写pl/sql块时，如果要使用变量，需在定义部分定义变量
identified [constant] datatype [not null] [:=| dafault expr]

identified	名称
constant	指定常量（需要指定它的初始值，且其值是不能改变的）
datatype	数据类型
not null	指定变量值不能为null
: =	给变量或常量指定初始值
default	用于指定初始值
expr	指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等

标量定义的案例

--定义一个变长字符串
v_ename varchar2(10);
--定义一个小数，范围-9999.99～9999.99
v_sal number(6,2);
--定义一个小数并给一个初始值为5.4
v_sal2 number(6,2):=5.4;
--定义一个日期类型的数据
v-hiredate date;
--定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;

标量使用%type类型
定义变量，会按照数据库列来确定你定义的变量的类型和长度

• 标识符名： 表名.列名%type;
  v_ename emp.ename%type;

--e.g.输入员工号，显示雇员姓名、工资、个人所得税（税率为0.03）
declare
c_tax_rate number(3,2):=0.03;
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:= v_sal*c_tax_rate;
--输出
dbms_output.put_line ('姓名是：'||v_ename||'工资:'||v_sal||'交税：'||v_tax_sal);
end;
/

复合变量 （composite）
用于存放多个值的变量，主要包括以下几种：1⃣️2⃣️使用较多
1⃣️pl/sql记录
2⃣️pl/sql表
3⃣️嵌套表（nested table）
4⃣️varray 变长数组

复合类型 pl/sql记录
类似于高级语言中的结构体
需注意：当引用pl/sql记录成员时，必须要加记录变量作为前缀（记录变量.记录成员）

--定义一个pl/sql记录类型（包含3个记录）
--定义了一个变量
declare
type emp_record_type is record (
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
--emp_record_type为类型名称
sp_record emp_record_type; --sp_record为变量名称
begin
select ename,sal,job into sp_record
from emp 
where empno=7788;
dbms_output.put_line('员工名'||sp_record.name);
end;
/

复合类型 pl/sql表
相当于高级语言中的数组
需注意：在高级语言中数组的下标不能为负数，而sp/sql是可以为负数的，并且表元素的下标没有限制

--定义了一个pl/sql表类型
declare
type sp_table_type is table of emp.ename%type --sp_table_type为类型名称
index by binary_integer; --表示下标是整数
--定义了一个变量
sp_table sp_table_type; --sp_table为变量名称
begin
select ename into sp_table(0) from emp --0表示下标
where empno=7788;
dbms_output.put_line('员工名'||sp_table(0));
end;
/
/*
说明：
sp_table_type 是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table pl/sql表变量
sp_table(0) 表示下标为0的元素
*/

参照变量
用于存放数值指针的变量
通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间

• 游标变量 （ref cursor） 使用较多
• 对象类型变量 （ref obj_type）

游标变量 （ref cursor）
使用游标时，当定义游标时不需要指定相应的select语句
但是当使用游标时时（open时）需要指定select语句
这样一个游标就与一个select语句结合了

--案例：使用pl/sql编写一个块，可以输入部门号，并显示改部门所有员工姓名和对应工资
declare
--定义游标类型
type sp_emp_cursor is ref cursor
--定义一个游标变量
test_cursor sp_emp_cursor;
v_name emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor 和一个select结合
open test_cursor for
select ename,sal from emp
where deptno = &no;
--循环取出
loop
fetch test_cursor into v_name,v_sal;
--判断是否test_cursor为空
exit when test_cursor% notfound;
dbms_output.put_line('名字'||v_ename||'工资'||v_sal);
end loop;
end;
/
--在上面的案例基础上，如果员工的工资低于200元，就增加100元
--在dbms前面添加此段
if v_sal < 200 then
update emp set sal = sal+100
where ename = v_ename;
end if;