概述
PL/SQL基础语法
游标
例外
PL/SQL简介
pl/sql（procedural language/sql）是Oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入Sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
pl/sql是面向过程的语言。
不同数据库的SQL扩展
- PL/SQL 是Oracle数据的SQL扩展。
- SQL/PL 是DB2数据库的SQL扩展。
- T-SQL 是SQL Server数据库的SQL扩展。
PL/SQL的必要性：
1、提高应用程序的运行性能。
2、模块化的设计思想。
3、减少网络传输量。
4、提高安全性。
PL/SQL编写规范
1、注释
单行注释–
sql>select * from emp where empno=7788; –取得员工信息
多行注释
/………………../
2、标识符号的命名规范
（1）当定义变量时，建议用v_作为前缀 v_sal。
（2）当定义常量时，建议用c_作为前缀 c_rate。
（3）当定义游标时，建议用_cursor作为后缀emp_cursor。
（4）当定义例外时，建议用e_作为前缀 e_error。
PL/SQL块
块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上 就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql块。
块结构示意图
pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。
declear：定义部分是从declare开始的，这部分是可选的。定义常量、变量、游标例外、复杂数据类型。
begin：执行部分是从begin开始的，这部分是必须的。
exception：例外处理部分是exception开始的，该部分可选的。
第一个PL/SQL程序
PLSQL中的命令窗口
SQL> set serveroutput on ;--打开输出
SQL> declare
--定义部分 ，如果没有定义，declare可以省略。 可选 ，定义常量、变量、游标、例外、复杂数据类型
2 begin
--程序
3 dbms_output.put_line('66666666');
4 end;
5 /
66666666
PL/SQL procedure successfully completed
SQL> /
--表示执行上一个PL/SQL块。
66666666
PL/SQL procedure successfully completed
PL/SQL基础语法
程序结构
declare
说明部分（变量说明\游标声明\例外说明）
begin
语句序列(DML语句)
exception
例外处理语句
end ;
/基本变量类型
定义
基本变量类型： char 、 varchar2、date、number、boolean、long
举例：
var1 char(20);
married boolean := false ;
psal number(7,2);栗子
SQL> set serveroutput on ;
SQL>
SQL> declare
2 -- 定义基本变量类型
3 v_name varchar2(20);--varchar2 字符串类型
4 v_num number(7,2);--number 数字类型
5 v_date date ;--date 日期类型
6
7 begin
8
9 v_name :='小工匠';
10 dbms_output.put_line('name:'||v_name);
11
12 v_num :=999;
13 dbms_output.put_line('num:'||v_num);
14
15 v_date :=sysdate;
16 dbms_output.put_line('数据库时间:'||v_date);
17
18 dbms_output.put_line('明天的时间:'||(v_date+1));
19 end ;
20 /
name:小工匠
num:999
数据库时间:22-JUN-16
明天的时间:23-JUN-16
PL/SQL procedure successfully completed
SQL> 引用型变量 %type
定义
使用%TYPE类型的变量
举例
v_name emp.ename%type ;栗子
打印7369员工的姓名和薪水信息
SQL> set serveroutput on ;
SQL> declare
2 --打印 7369 员工的姓名和薪水信息
3 /* 可以使用基本类型来定义变量的类型，推荐使用引用型变量来定义变量类型*/
4 --vname varchar2(20);
5 --v_sal number;
6
7 --定义引用变量
8 v_name emp.ename%type ;
9 v_sal emp.sal%type;
10 begin
11 --业务操作，变量赋值 (两种赋值方式 1. v_sal := 20 第二种 into的方式 )
12 select ename, sal into v_name, v_sal from emp e where e.empno = 7369;
13 --输出信息
14 dbms_output.put_line(v_name||'的薪水是'||v_sal);
15 end;
16 /
SMITH的薪水是800
PL/SQL procedure successfully completed
SQL> 
记录型变量 %rowtype
定义
%rowtype 记录型变量 代表表中的一行，而一行中有很多列。
举例
emp_rec emp%rowtype记录型变量分量的引用
手工赋值
emp_rec.ename := 'ADMIN'
或者
into 赋值之后， 使用 emp_rec.enmae获取栗子
SQL> set serveroutput on ;
SQL> declare
2 --打印 7369 员工的姓名和薪水信息
3
4 --定义记录型变量 ，得到 7369一行的所有列的信息
5 v_emp_rec emp%rowtype ;
6 begin
7 --业务sql
8 select * into v_emp_rec from emp a where a.empno=7369;
9 --获取姓名和薪水，并打印
10 dbms_output.put_line(v_emp_rec.ename||'的薪水是'||v_emp_rec.sal);
11 end ;
12 /
SMITH的薪水是800
PL/SQL procedure successfully completed
SQL> 
if语句的使用
形式一
if 条件 then 语句1;
语句2；
end if;形式2
if 条件 then 语句1;
else 语句2;
end if;形式3 注意elsif
elsif没有e 且是连在一起的
if 条件 then 语句;
elsif 条件 then 语句;
else 语句;
end if;栗子
/*
判断用户从键盘输入的数字
1、如何使用if语句
2、接收一个键盘输入（从键盘上获取的都是字符串）
*/
set serveroutput on ;
--接收一个键盘输入
--num ：地址值，含义是：在该地址上保存了输入的值 ，获取地址值上对应的值，需要使用 &
accept num prompt'请输入一个数字';
declare
--定义变量保存用户从键盘输入的数字 这里并没有对异常进行处理，输入字符串，会抛异常
pnum number := #
begin
--执行if 语句进行条件判断 提示信息 不能使用双引号，必须使用单引号，否则报错
if pnum = 0 then dbms_output.put_line('输入的数字是'||pnum);
elsif pnum = 1 then dbms_output.put_line('输入的数字是'||pnum);
elsif pnum = 2 then dbms_output.put_line('输入的数字是'||pnum);
else dbms_output.put_line('其他数字'||pnum);
end if;
end;
/
循环语句的使用
形式1
while 条件 loop
.....
end loop;当条件满足时，执行循环体，不满足时，退出循环体。
set serveroutput on ;
declare
--定义循环变量 初始值为1 记得加上变量的类型 否则报错
v_num number :=1 ;
begin
while v_num <=5 loop
--打印 v_num
dbms_output.put_line(v_num);
--变量+1 不能使用v_num++的形式，oracle不支持这种写法
v_num :=v_num+1 ;
end loop;
end;
/
形式2
loop
exit [when 条件];
.....
end loop;条件成立时，退出循环体，不成立时执行循环体。
set serveroutput on ;
declare
--定义循环变量
v_num number :=1 ;
begin
loop
exit when v_num>10 ; --记得加 标点符号
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop ;
end;
/
推荐使用第二种，因为在控制游标的时候比较方便。
形式3
for i in 1 .. 10 loop
语句;
end loop;set serveroutput on ;
declare
v_num number :=1 ;
begin
for i in 1 .. 5 loop -- .. 前后可以有空格 也可以没有
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop;
end;
/
游标
游标的概念
游标（游标）：一个结果集
不能把一个集合赋值给一个基本型变量，否则会抛出 too many rows的异常。
可带参数 ，可不带参数。
游标的语法
cursor 游标名 [(参数名 数据类型[,参数名，数据类型]...)]
is select 语句;比如
cursor c1 is select enama from emp;操作游标的步骤
打开游标
open c1;(打开游标，执行查询 即执行游标的查询语句)取一行游标的值
fetch c1 into v_name;( 取一行到变量中)最开始时，游标指向集合的第一条记录，记录返回后，指针移动到下一条记录。
关闭游标
close c1 ;(关闭游标释放资源)栗子
使用游标查询员工姓名和工资，并打印。
Loop循环游标
推荐写法
....
Loop
Fetch 游标名 InTo 临时记录或属性类型变量；
Exit When 游标名%NotFound；
End Loop；
.....
/*使用游标查询员工姓名和工资，并打印*/
set serveroutput on ;
declare
--定义 游标 推荐将游标和游标对应的变量写在一块，比较好维护
cursor cemp is select e.ename , e.sal from emp e ;
--定义游标对应的变量 这里使用引用型变量
v_name emp.ename%type ;
v_sal emp.sal%type;
begin
--打开游标
open cemp ;
--循环获取游标中的值
loop
--取一条数据
fetch cemp into v_name,v_sal ; -- into 后变量的顺序一定要和定义游标时select的字段对应
exit when cemp%notfound ;
--打印
dbms_output.put_line(v_name||'的工资是'||v_sal);
end loop;
--关闭游标
if cemp%isopen then
close cemp;
dbms_output.put_line('Closing...');
end if;
end;
/
或者
For 循环游标
循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
For 变量名 In 游标名
Loop
数据处理语句；
End Loop；/*使用游标查询员工姓名和工资，并打印*/
set serveroutput on ;
declare
--定义 游标 推荐将游标和游标对应的变量写在一块，比较好维护
cursor cemp is select e.ename , e.sal from emp e ;
--定义游标对应的变量 这里使用引用型变量
v_name emp.ename%type ;
v_sal emp.sal%type;
begin
--循环获取游标中的值
for c in cemp LOOP
select c.ename , c.sal into v_name,v_sal from dual;
dbms_output.put_line(v_name||'的工资是'||v_sal);
end loop;
end;
/
实例：给员工涨工资
/*
给员工涨工资。总裁涨1000，经理涨800，普通员工涨400；
*/
declare
--定义游标
cursor c_emp is select empno,empjob from emp;
--定义游标对应的变量
p_empno emp.empno%type;
p_empjob emp.empjob%type;
begin
--打开游标
open c_emp;
--取出一个员工
loop
fetch c_emp into p_empno,p_empjob;
exit when c_emp%notfound;
--判断职位
if p_empjob = 'PRESIDENT' then
update emp set sal=sal+1000 where empno=p_empno;
elsif p_empjob = 'MANAGER' then
update emp set sal= sal+800 where empno = p_empno;
else
update emp set sal=sal+400 where empno=p_empno;
end if;
end loop;
--关闭游标
if c_emp%isopen then
close c_emp;
end if ;
--提交事务，oracle默认的隔离级别是read committed ，不同连接只能读取提交之后的；
commit;
dbms_output.put_line('涨工资完成');
end;
/游标的属性
游标的4个属性
- %Found ：Fetch语句（获取记录）执行情况 True or False
- %NotFound : 最后一条记录是否提取出 True or False
- %ISOpen ： 游标是否打开True or False
- %RowCount ：游标当前提取的行数
游标数的限制
Oracle默认的一个会话最多可以打开300个游标.
可以通过使用 show parameter cursors; （表示模糊查询 %cursors%）
查看包含cursors的参数设置
修改游标数的限制
使用DBA权限的用户
alter system set open_cursors=400 scope = both;其中scope的取值：both，memory，spfile
- memory：表示只更改当前实例，不更改参数文件
- spfile：表示只更改参数文件，不更改当前示例，数据库服务需要重启
- both：表示上边两个同时更改
带参数的游标
注意 定义（带参数） 和打开游标（传递参数）时的区别。其余的和无参的游标一样。
set serveroutput on ;
declare
--定义带参数的游标
cursor cemp (dno number) is select ename from emp where deptno =dno;
--定义游标中对应的变量
v_name emp.ename%type;
begin
--打开游标 传入对应的入参
open cemp(10);
--loop循环 遍历游标
loop
fetch cemp into v_name;
exit when cemp%notfound;
dbms_output.put_line(v_name);
end loop;
close cemp;
end;
/
显式游标和隐式游标
上面介绍的是显式游标，下面说下隐式游标
DML操作和单行SELECT语句会使用隐式游标，它们是：
- 插入操作：INSERT
- 更新操作：UPDATE
- 删除操作：DELETE
- 单行查询操作：SELECT … INTO …
隐式游标的名字为SQL，这是由ORACLE 系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。
格式调用为： SQL%
隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
游标的属性有四种，如下所示。
隐式游标的属性 返回值类型 意 义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = 'AD_VP';
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--删除指定雇员；如果部门中没有雇员，则删除部门
DELETE FROM employees WHERE department_id=v_deptno;
IF SQL%NOTFOUND THEN
DELETE FROM departments WHERE department_id=v_deptno;
END IF;
END; 例外
例外的概念
在oracle中错误被叫做例外：分为系统例外和自定义例外。
系统例外 比如：
- No_data_found 没有找到数据
- Too_many_rows select..into语句匹配多个行
- Zero_Divide 被零除
- Value_error 算数或转换错误，算术错误比如说负数开平方
- Timeout_on_resource 在等待资源时发生超时，常见于分部署数据库。
系统例外之no_data_found
/*系统例外 no_data_found*/
set serveroutput on ;
--查询empno为222的姓名
declare
--定义引用型变量
v_name emp.ename%type;
begin
--业务sql
select ename into v_name from emp where empno=222;
exception
when no_data_found then dbms_output.put_line('no data found');
when others then dbms_output.put_line('others exception');
end;
/
系统例外之too_many_rows
/*系统例外 too_many_rows */
set serveroutput on ;
--查询 10号部门的员工
declare
--定义引用型变量
v_name emp.ename%type;
begin
--业务sql
select ename into v_name from emp where deptno=10;
exception
when too_many_rows then dbms_output.put_line('too_many_rows');
when others then dbms_output.put_line('others exception');
end;
/
系统例外之zero_divide
/*系统例外 zero_divide */
set serveroutput on ;
declare
--定义引用型变量
v_num number;
begin
--业务sql
v_num := 1/0;
exception
when zero_divide then dbms_output.put_line('zero_divide');
dbms_output.put_line('0不能做除数');
when others then dbms_output.put_line('others exception');
end;
/
系统例外之value_error
/*系统例外 value_error */
set serveroutput on ;
declare
--定义引用型变量
v_num number;
begin
--业务sql
v_num := 'xiaogongjiang';
exception
when value_error then dbms_output.put_line('value_error');
when others then dbms_output.put_line('others exception');
end;
/
自定义例外
步骤
- 定义变量，类型是exception
- 使用raise抛出自定义意外
栗子
--自定义例外：（没有查找到的例外no_emp_found）
declare
cursor c_emp is select ename from emp where deptno=50;
p_ename emp.ename%type;
--定义一个例外
no_emp_found exception;
begin
open c_emp;
--获取一条记录
fetch c_emp into p_ename;
--如果没有查到则抛出自定义例外
if c_emp%notfound then
raise no_emp_found;
end if;
--此处，当前一句抛出例外执行完exception后，
--oracle会自动启动一个pmon(process monitor)的一个进程
--将pl/sql程序中未关闭的资源释放
--所以 close c_emp; 还是会执行的
close c_emp;
--捕获例外
exception
when no_emp_found then dbms_output.put_line('没有该部门下的员工');
when others then dbms_output.put_line('其他例外');
end;
/
PL/SQL调测
可以在pl/sql工具中 新建测试窗口，调测过程和调测存过的方式一样，可以一步一步的跟踪sql执行的过程。
运用瀑布模型完成PLSQL程序的设计
瀑布模型
- 1.需求分析
- 2.设计
- 2.1概要设计
- 2.2详细设计
- 3.编码coding
- 4.测试Testing
- 5.上线（部署）
拿到一个需求后，不找着急写程序，先分析明白了
- sql语句
- 变量初始值
- 变量如何获取
- ….
案例：统计每年入职的员工人数
分析过程：
每年入职的员工人数
1.所有的年份集合–>定义cursor保存
2.每个员工的入职年份–>定义v_hiredate保存
3.每年入住的人数总和–>定义v_count_XX保存
先把大框架打起来
declare
---定义变量balabala
begin
---业务逻辑
end;
/然后再一步一步的填充业务逻辑。
set serveroutput on ;
declare
--入职年份的游标
cursor hiredate_cursor is select to_char(hiredate,'yyyy') from emp;
--定义入职年份
v_hiredate varchar2(4);
--定义每个年份入职人员总数
v_count_80 number :=0;
v_count_81 number :=0;
v_count_82 number :=0;
v_count_87 number :=0;
begin
--打开游标
open hiredate_cursor ;
--loop循环遍历游标
loop
--取值
fetch hiredate_cursor into v_hiredate ;
exit when hiredate_cursor%notfound ;
if v_hiredate = '1980' then v_count_80 :=v_count_80+1 ;
elsif v_hiredate = '1981' then v_count_81 :=v_count_81+1 ;
elsif v_hiredate ='1982' then v_count_82 :=v_count_82+1;
elsif v_hiredate ='1987' then v_count_87 :=v_count_87+1;
end if ;
end loop;
--关闭游标
close hiredate_cursor;
dbms_output.put_line('总共入职人数:'||(v_count_80+v_count_81+v_count_82+v_count_87));--加括号
dbms_output.put_line('80入职的:'||v_count_80);
dbms_output.put_line('81入职的:'||v_count_81);
dbms_output.put_line('82入职的:'||v_count_82);
dbms_output.put_line('87入职的:'||v_count_87);
end;
/
案例：员工涨工资问题
案例2：涨工资问题，从最低工资的员工开始涨起，没人涨10%，工资总额不能超过50000，返回涨工资的人数和涨后的工资总额.
/*
分析：
1、用到的sql语句：
select empno,sal from emp order by sal;
select sum(sal) into totalsal from emp;
2、需要声明的变量：
工资总额：totalsal 涨工资人数：count
3、循环推出的条件：
工资总额>5W or 全部员工都涨完工资*/
declare
cursor cemp is select empno,sal from emp order by sal;
p_no emp.empno%type;
p_sal emp.sal%type;
countemp number:=0;--涨工资人数
totalsal emp.sal%type;
begin
--获取初始工资总额
select sum(sal) into totalsal from emp;
open cemp;
--判断当前工资总额是否大于5W
if totalsal<50000 then
loop
fetch cemp into p_no,p_sal;
exit when cemp%notfound;
--获取当前员工涨工资后的工资总额
--如果工资总额超过5W直接退出循环
exit when (totalsal+p_sal*0.1)>50000;
update emp set sal=sal*1.1 where empno=p_no;
--涨工资人数加1
countemp:=countemp+1;
end loop;
end if;
close cemp;
commit;
dbms_output.put_line('共有'countemp'人涨工资，工资总额为：'totalsal);
end;
/案例：涉及两张表的员工涨工资问题
declare
--获取所有部门
cursor c_dept is select deptno from dept;
--各部门编号
p_dno dept.deptno%type;
--各部门总金额
p_totalsal number;
--各工资分段人数：
num1 number;
num2 number;
num3 number;
--定义一个游标存放该部门下所有员工(带参数)
cursor c_emp(dno number) is select sal from emp where deptno = dno;
--员工的薪水
p_sal number;
begin
--打开部门游标
open c_dept;
loop
--部门循环
fetch c_dept
into p_dno;
exit when c_dept%notfound;
--初始化变量：
p_totalsal := 0;
num1 := 0;
num2 := 0;
num3 := 0;
-- --获取本部门下所有员工,打开员工游标
open c_emp(p_dno);
loop
--员工循环
fetch c_emp into p_sal;
exit when c_emp%notfound;
if p_sal < 3000 then
num1 := num1 + 1;
elsif p_sal >= 3000 and p_sal <= 6000 then
num2 := num2 + 1;
elsif p_sal > 6000 then
num3 := num3 + 1;
end if;
--获取总金额
p_totalsal := p_totalsal + p_sal;
end loop;
close c_emp;
--保存统计结果到sal_msg
insert into sal_msg values (p_dno, num1, num2, num3, p_totalsal);
end loop;
close c_dept;
commit;
dbms_output.put_line('统计完成');
end;
/
