始自【韩顺平.玩转oracle第27讲.pl/sql编程(4)】
【期望目标】
1.掌握pl/sql的高级用法(能编写分页过程模块，下订单过程模块...)
2.会处理oracle常见的例外
3.会编写oracle各种触发器
4.理解视图的概念并能灵活使用视图
【介绍】
在任何计算机语言(c,java,pascal)都有各种控制语句(条件语句，循环结构，
顺序控制结构)在pl/sql中也存在这样的控制语句。
【期望目标】
①使用各种if语句
②使用循环语句
③使用控制语句---goto和null;
【条件分支语句】
pl/sql中提供了三种条件分支语句
if -- then,
if -- then -- else,
if -- then -- else if --- else
这里我们可以和java语句进行一个比较
java:
if() {
}
if() {
} else {
}
if() {
} else if() {
} else if() {
} else {
}
【简单的条件判断 if -- then】
？编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，
就给这雇员工资增加10%
create or replace procedure bruce_pro4(empName varchar2) is
--定义
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=empName;
--判断
if v_sal<2000 then
--加薪10%
update emp set sal=sal*1.1 where ename=empName;
end if; -- 如果不加end if 的话相当于 if语句是错误的
end;
/
【使用该过程：call bruce_pro4('ALLEN');】
【二重条件分支 if -- then --- else】
？编写一个过程，可以输入一个雇员名，如果该雇员的补助不是 0，就在原来的
基础上增加 100；如果补助为 0 就把补助设为 200；
create or replace procedure bruce_pro5(empName varchar2) is
--定义
v_comm emp.comm%type;
begin
--执行
select comm into v_comm from emp where ename=empName;
--在pl/sql中不等于是比较特殊的，像这样“<>”!
if v_comm<>0 then
--加100
update emp set comm=comm+100 where ename=empName;
else
--如果补助为0，就把补助设为200
update emp set comm=200 where ename=empName;
end if; -- 如果不加end if 的话相当于 if语句是错误的
end;
/
【多重条件分支 if --- then --- elsif --- else】【注意是 elsif】
？编写一个过程，可以输入一个雇员编号，如果该骨远端额职位是PRESIDENT，就给
他的工资增加1000，如果该雇员的职位是MANAGER，就给他的工资增加500，其他职位
的雇员工资增加200。
create or replace procedure bruce_pro6(spNo number) is
--定义
v_job emp.job%type;
begin
--执行
select job into v_job from emp where empno=spNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=spNo;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;
/
【java循环】
for(;;)
do {
} while();
while() {
}
【循环语句 --loop】
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，
这种循环至少会被执行一次，有点儿类似于 java中的 do{}while();
案例：现有一张表users，表结构如下
用户 用户名
--- -----
--- -----
请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始
增加。
create table users2(userNO number, userName varchar2(40));
create or replace procedure bruce_pro7(name varchar2) is
--定义
--:=表示赋值
v_num number:=1;
begin
--第1种循环方法(先循环在判断)
loop
insert into users2 values(v_num,name);
--判断是否要退出循环
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
--第2种循环方法(先判断再循环)
while v_num<=20 loop
--执行
insert into users2 values(v_num,name);
v_num:=v_num+1;
end loop;
--第3中循环方法(不建议使用，前面的 loop 和 while loop 就够用了)
for i in reverse 1..10 loop
dbms_output.put_line(i);
insert into users2 values(v_num,name);
end loop;
end;
/
【循环语句 -for循环】【有点儿不太符合编程人的思想】
基本for循环的基本结构如下
begin
for i in reverse 1..10 loop
insert into users values(i,'y3w');
end loop;
end;
/
【顺序控制语句-goto，null】
①goto语句
goto语句用于跳转到特定标号去执行语句。注意由于使用goto语句会增加程序的复杂性，
并使得应用程序可读性差，所以在做一般应用开发时，建议大家不要使用goto语句。
基本语法如下 goto label, 其中label是已经定义好的标号名，
【goto 案例】
declare
i int:=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
/
-----------------------------------------------------------
SQL> declare
2 i int:=1;
3 begin
4 loop
5 dbms_output.put_line('输出i='||i);
6 if i=10 then
7 goto end_loop;
8 end if;
9 i:=i+1;
10 end loop;
11 <<end_loop>>
12 dbms_output.put_line('循环结束');
13 end;
14 /
输出i=1
输出i=2
输出i=3
输出i=4
输出i=5
输出i=6
输出i=7
输出i=8
输出i=9
输出i=10
循环结束
PL/SQL procedure successfully completed
------------------------------------------------------------
②null【表示什么都不干，是一条空语句】
null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的
主要好处是可以提高pl/sql的可读性。
【null 案例】
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&n;
if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else
null;
end if;
end;
【编写分页过程】
分页时任何一个网站(bbs、网上商城、blog)都会使用到的技术，因此学习pl/sql编程
开发就一定要掌握该技术。
【无返回值的存储过程】
古人云：欲速则不达，为了让大家比较容易接受分页过程编写，我还是从简单到复杂，
循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现有一张表 book，表结构如下：
书号 书名 出版社
-- -- --
-- -- --
-- -- --
请编写一个过程，可以向book表添加书，要求通过java程序调用该过程。
【答案】
--① 创建 book表
create table book(
bookId number,
bookName varchar2(50),
publishHouse varchar2(50)
);
--② 编写存储过程
--in：表示是一个输入参数，不写的话默认为in
--out：表示是一个输出参数
create or replace procedure bruce_pro8
(bookId in number,bookName in varchar2,publishHouse in varchar2) is
begin
insert into book values(bookId,bookName,publishHouse);
end;
/
【有返回值的存储过程(非列表)】
再看如何处理有返回值的存储过程：
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
--有输入和输出地存储过程
--整死我了！不能在pl/sql中直接用exec bruce_pro9(7788)调用！
--也不能用exec bruce_pro9(7788, empName emp.ename%type); 调用！
--形参的名字不能和被查询表中的名字有冲突(也就是不能写成empNo in number)！！
create or replace procedure bruce_pro9
(empNo1 in number,empName out varchar2) is
begin
select ename into empName from emp where empno=empNo1;
end;
/
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
create or replace procedure bruce_pro9
(empNo1 in number,empName out varchar2,empSal out number,empJob out varchar2) is
begin
select ename,sal,job into empName,empSal,empJob from emp where empno=empNo1;
end;
/
【有返回值的存储过程(列表[结果集]) --优势在于可以返回多个值 】
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题分析如下：
由于oracle存储过程没有返回值，它的所有返回值都是通过 out 参数来替代的，列表同样
也不例外，但由于是集合，所以不能用一般的参数，必须要用package了。所以要分两部分：
①建一个包。如下：
--1.创建一个包，在该包中，定义了一个游标类型 test_cursor，是个游标
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/
②建立存储过程。如下：
--2.创建过程
create or replace procedure bruce_pro10
(departNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=departNo;
end;
/
--3.如何在java 中调用该过程?
【编写分页过程】
有了上面的基础，相信大家可以完成分页存储过程了。
要求，请大家编写一个存储过程，要求可以输入表名、每页显示的记录数、
当前页。返回总记录数，总页数，和返回的结果集。
【oracle的分页-- 三番数次地当成内嵌视图】
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
-- 在分页时，可以把下面的sql语句当成一个模板使用
select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
【使用到游标的时候都要先搞一个包来吗？就用上面那个也行~】
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- 开始编写分页的过程
create or replace procedure fenye
(tableName in varchar2,--表名
pageSize in number,--1页显示的记录数
pageNow in number,--当前为第几页
myrows out number, --总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义部分
--定义一个sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
||') t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--要计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '|| tableName;
--执行sql，并把返回的值，赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,pageSize)=0 then
myPageCount:=myrows/pageSize;
else
myPageCount:=myrows/pageSize+1;
end if;
--关闭游标
--close p_cursor;
end;
/
@@@@@@@@@@@@@@@@@@@@ 解决排序的问题 + order by @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- 开始编写分页的过程
create or replace procedure fenye
(tableName in varchar2,--表名
pageSize in number,--1页显示的记录数
pageNow in number,--当前为第几页
myrows out number, --总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义部分
--定义一个sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--执行部分(排序的话在最内层加条件)
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
||' order by sal) t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--要计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '|| tableName;
--执行sql，并把返回的值，赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,pageSize)=0 then
myPageCount:=myrows/pageSize;
else
myPageCount:=myrows/pageSize+1;
end if;
--关闭游标
--close p_cursor;
end;
/