始自【韩顺平.玩转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; /
