调用oracle存储过程详解ppt课件.ppt

《调用oracle存储过程详解ppt课件.ppt》由会员分享，可在线阅读，更多相关《调用oracle存储过程详解ppt课件.ppt（34页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第7讲 PL/SQL 块的结构和实例PL/SQL 块的结构和实例介绍学习目标理解oracle 的pl/sql 概念掌握pl/sql 编程技术（包括编写过程、函数、触发器.）pl/sql 的介绍pl/sql 是什么pl/sql(procedural language/sql)是oracle 在标准的sql 语言上的扩展。pl/sql 不仅允许嵌入sql 语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。PL/SQL 块的结构和实例学习必要性1.提高应用程序的运行性能2.模块化的设计思想【分页的过程，订单的过程，转账的过程。】3.减少网

2、络传输量4.提高安全性（sql 会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会）为什么PL/SQL 会快呢？不好的地方：移植性不好（换数据库就用不了） PL/SQL 块的结构和实例 sqlplus 开发工具sqlplus 是oracle 公司提供的一个工具，这个因为我们在以前介绍过的：举一个简单的案例：编写一个存储过程，该过程可以向某表中添加记录。一个简单的存储过程CREATE OR REPLACE PROCEDURE proc_helloworldISBEGIN DBMS_OUTPUT.put_line (Hello World!);END;/PL/SQL 块的结构和实例

3、 需要在屏幕上显示出DBMS_OUTPUT.put_line的输出字符串，需要做一个小小的设置SQL show serveroutputserveroutput OFF SQL set serveroutput on SQL show serveroutput ON SIZE 10000 FORMAT WORD_WRAPPED1.创建一个简单的表Sql 代码1. create table mytest(name varchar2(30),passwd varchar2(30); PL/SQL 块的结构和实例 2.创建过程Sql 代码1. create or replace procedure

4、sp_pro1 is2. begin-执行部分3. insert into mytest values(杨世顺,m1234)；4. end;5. /replace:表示如果有sp_pro1，就替换如何查看错误信息：show error;如何调用该过程：1）exec 过程名（参数值1，参数值2.）；SQL执行2）call 过程名（参数值1，参数值2.）；程序调用 PL/SQL 块的结构和实例 pl/sqln developer开发工具pl/sql developer 是用于开发pl/sql 块的集成开发环境(ide)，它是一个独立的产品，而不是oracle 的一个附带品。举一个简单案例：编写一个

5、存储过程，该过程可以删除某表记录。Sql 代码1. create or replace procedure sp_pro2 is2. begin-执行部分3. delete from mytest where name=杨世顺；4. end; PL/SQL 块的结构和实例 pl/sql 基础pl/sql 介绍开发人员使用pl/sql 编写应用模块时，不仅需要掌握sql 语句的编写方法，还要掌握pl/sql 语句及语法规则。pl/sql 编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql 编程，

6、我们可以轻松地完成非常复杂的查询要求。pl/sql 可以做什么简单分类过程(存储过程)函数块(编程)触发器包PL/SQL 块的结构和实例 编写规范编写规范n1.注释单行注释 -Sql 代码1. select * from emp where empno=7788; -取得员工信息多行注释 /*.*/来划分2.标志符号的命名规范1).当定义变量时，建议用v_作为前缀v_sal2).当定义常量时，建议用c_作为前缀c_rate3).当定义游标时，建议用_cursor 作为后缀emp_cursor4).当定义例外时，建议用e_作为前缀e_error PL/SQL 块的结构和实例 pl/sql 块介绍

7、介绍n块(block)是pl/sql 的基本程序单元，编写pl/sql 程序实际上就是编写pl/sql 块，要完成相对简单的应用功能，可能只需要编写一个pl/sql 块，但是如果想要实现复杂的功能，可能需要在一个pl/sql 块中嵌套其它的pl/sql 块。块n结构示意图pl/sql 块由三个部分构成：定义部分，执行部分，例外处理部分。如下所示：declare/*定义部分定义常量、变量、游标、例外、复杂数据类型*/begin/*执行部分要执行的pl/sql 语句和sql 语句*/exception/*例外处理部分处理运行的各种错误*/end;定义部分是从declare 开始的，该部分是可选的；

8、执行部分是从begin 开始的，该部分是必须的；例外处理部分是从exception 开始的，该部分是可选的。可以和java 编程结构做一个简单的比较。 PL/SQL 块的结构和实例 pl/sql 块的实例(1)实例1-只包括执行部分的pl/sql 块nSql 代码1. set serveroutput on -打开输出选项2. begin3. dbms_output.put_line(hello);4. end;相关说明：dbms_output 是oracle 所提供的包(类似java 的开发包)，该包包含一些过程，put_line 就是dbms_output 包的一个过程。 PL/SQL 块

9、的结构和实例 pl/sql 块的实例(2)实例2-包含定义部分和执行部分的pl/sql 块nSql 代码1. declare2. v_ename varchar2(5); -定义字符串变量3. begin4. select ename into v_ename from emp where empno=&aa;5. dbms_output.put_line(雇员名：|v_ename);6. end;7. /如果要把薪水也显示出来，那么执行部分就应该这么写：Sql 代码1. select ename,sal into v_ename,v_sal from emp where empno=&aa;

10、相关说明：& 表示要接收从控制台输入的变量 PL/SQL 块的结构和实例 pl/sql 块的实例(3)实例3-包含定义部分，执行部分和例外处理部分为了避免pl/sql 程序的运行错误，提高pl/sql 的健壮性，应该对可能的错误进行处理，这个很有必要。1.比如在实例2 中，如果输入了不存在的雇员号，应当做例外处理。2.有时出现异常，希望用另外的逻辑处理我们看看如何完成1 的要求。相关说明：oracle 事先预定义了一些例外，no_data_found 就是找不到数据的例外。Sql 代码1. declare2. -定义变量3. v_ename varchar2(5);4. v_sal numbe

11、r(7,2);5. begin6. -执行部分7. select ename,sal into v_ename,v_sal from emp where empno=&aa;8. -在控制台显示用户名9. dbms_output.put_line(用户名是：|v_ename| 工资：|v_sal);10. -异常处理11.exception12.when no_data_found then13. dbms_output.put_line(朋友，你的编号输入有误！)；14.end;15./PL/SQL 块的结构和实例 pl/sql分类 - 过程，函数，包，触发器PL/SQL 块的结构和实例 实

12、例如下：1.请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资2.如何调用过程有两种方法； exec call3.如何在java 程序中调用一个存储过程问题：如何使用过程返回值？特别说明：对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。create procedure sp_pro3(spName varchar2, newSal number) is-不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java 写方法时的参数一样Sql 代码1. begin2. -执行部分，根据用户名去修改工资3. update emp set sal=newSa

13、l where ename=spName;4. end;5. /PL/SQL 块的结构和实例 java 程序中调用一个存储过程/演示java 程序去调用oracle 的存储过程案例5. try6. /1.加载驱动7. Class.forName(oracle.jdbc.driver.OracleDriver);8. /2.得到连接9. Connection ct = DriverManager.getConnection(jdbc:oracle:thin:127.0.0.1:1521:MYORA1,scott,m123);11. /3.创建CallableStatement12. Callab

14、leStatement cs = ct.prepareCall(call sp_pr3(?,?);13. /4.给?赋值14. cs.setString(1,SMITH);15. cs.setInt(2,10);17. cs.execute();18. /关闭19. cs.close();20. ct.close();21. catch(Exception e)22. e.printStackTrace();23. 24. 25.PL/SQL 块的结构和实例 1.使用各种if 语句2.使用循环语句3.使用控制语句goto 和null；条件分支语句pl/sql 中提供了三种条件分支语句ifthe

15、n，if then else，if then elsif then这里我们可以和java 语句进行一个比较简单的条件判断 if thenn问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。Sql 代码1. create or replace procedure sp_pro6(spName varchar2) is2. -定义3. v_sal emp.sal%type;4. begin5. -执行6. select sal into v_sal from emp where ename=spName;7. -判断8. if v_sal2000 th

16、en9. update emp set sal=sal+sal*10% where ename=spName;10. end if;11.end;12./PL/SQL 块的结构和实例 二重条件分支 if then else问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0 就在原来的基础上增加100；如果补助为0 就把补助设为200；Sql 代码1. create or replace procedure sp_pro6(spName varchar2) is2. -定义3. v_comm m%type;4. begin5. -执行6. select comm into v_com

17、m from emp where ename=spName;7. -判断8. if v_comm0 then9. update emp set comm=comm+100 where ename=spName;10. else11. update emp set comm=comm+200 where ename=spName;12. end if;13.end;14./PL/SQL 块的结构和实例 多重条件分支 if then elsif thenn问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER 就给他

18、的工资增加500，其它职位的雇员工资增加200。Sql 代码1. create or replace procedure sp_pro6(spNo number) is2. -定义3. v_job emp.job%type;4. begin5. -执行6. select job into v_job from emp where empno=spNo;7. if v_job=PRESIDENT then8. update emp set sal=sal+1000 where empno=spNo;9. elsif v_job=MANAGER then10. update emp set sal

19、=sal+500 where empno=spNo;11. else12. update emp set sal=sal+200 where empno=spNo;13. end if;14.end;15./16.PL/SQL 块的结构和实例 循环语句 loop是pl/sql 中最简单的循环语句，这种循环语句以loop 开头，以end loop 结尾，这种循环至少会被执行一次。案例：现有一张表users，表结构如下：用户id | 用户名请编写一个过程，可以输入用户名，并循环添加10 个用户到users 表中，用户编号从1 开始增加。Sql 代码1. create or replace proc

20、edure sp_pro6(spName varchar2) is2. -定义 :=表示赋值3. v_num number:=1;4. begin5. loop6. insert into users values(v_num,spName);7. -判断是否要退出循环8. exit when v_num=10;9. -自增10. v_num:=v_num+1;11. end loop;12.end;13./PL/SQL 块的结构和实例 循环语句 while 循环n基本循环至少要执行循环体一次，而对于while 循环来说，只有条件为true时，才会执行循环体语句，while 循环以while.

21、loop 开始，以end loop 结束。案例：现有一张表users，表结构如下：用户id 用户名问题：请编写一个过程，可以输入用户名，并循环添加10 个用户到users 表中，用户编号从11 开始增加。Sql 代码1. create or replace procedure sp_pro6(spName varchar2) is2. -定义 :=表示赋值3. v_num number:=11;4. begin5. while v_num=20 loop6. -执行7. insert into users values(v_num,spName);8. v_num:=v_num+1;9. en

22、d loop;10.end;11./PL/SQL 块的结构和实例 循环语句 for 循环n基本for 循环的基本结构如下Sql 代码1. begin2. for i in reverse 1.10 loop3. insert into users values (i, xiaoming);4. end loop;5. end;PL/SQL 块的结构和实例 我们可以看到控制变量i，在隐含中就在不停地增加。顺序控制语句 goto，null1.goto 语句goto 语句用于跳转到特定符号去执行语句。注意由于使用goto 语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议

23、大家不要使用goto 语句。基本语法如下 goto lable，其中lable 是已经定义好的标号名，Sql 代码1. declare2. i int := 1;3. begin4. loop5. dbms_output.put_line(输出i= | i);6. if i = 1 then7. goto end_loop;8. end if;9. i := i + 1;10. end loop;11. 12. dbms_output.put_line(循环结束);13.end;PL/SQL 块的结构和实例 2.nullnull 语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用nu

24、ll语句的主要好处是可以提高pl/sql 的可读性。Sql 代码1. declare2. v_sal emp.sal%type;3. v_ename emp.ename%type;4. begin5. select ename, sal into v_ename, v_sal from emp where empno =&no;6. if v_sal 3000 then7. update emp set comm = sal * 0.1 where ename = v_ename;8. else9. null;10. end if;11.end;PL/SQL 块的结构和实例 PL/SQL分页编

25、写分页过程介绍分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。无返回值的存储过程储过程：案例：现有一张表book，表结构如下：书号 书名 出版社请写一个过程，可以向book 表添加书，要求通过java 程序调用该过程。-in:表示这是一个输入参数，默认为in-out:表示一个输出参数Sql 代码1. create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is2. begin

26、3. insert into book values(spBookId,spbookName,sppublishHouse);4. end;5. /PL/SQL 块的结构和实例 JAVA 代码try/1.加载驱动Class.forName(oracle.jdbc.driver.OracleDriver); /2.得到连接Connection ct = DriverManager.getConnection(jdbc:oracle:thin127.0.0.1:1521:MYORA1,scott,m123);/3.创建CallableStatementCallableStatement cs =

27、ct.prepareCall(call sp_pro7(?,?,?);/4.给?赋值 cs.setInt(1,10);cs.setString(2,笑傲江湖); cs.setString(3,人民出版社); /5.执行cs.execute(); catch(Exception e) e.printStackTrace(); finally/6.关闭各个打开的资源 cs.close(); ct.close();PL/SQL 块的结构和实例 有返回值的存储过程（非列表）再看如何处理有返回值的存储过程：案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。案例扩张：编写一个过程，可以输入雇员的编

28、号，返回该雇员的姓名、工资和岗位。Sql 代码1. -有输入和输出的存储过程2. create or replace procedure sp_pro83. (spno in number, spName out varchar2) is4. begin5. select ename into spName from emp where empno=spno;6. end;7. /PL/SQL 块的结构和实例 try /1.加载驱动Class.forName(oracle.jdbc.driver.OracleDriver);/2.得到连接 Connection ct = DriverManag

29、er.getConnection(jdbc:oracle:thin127.0.0.1:1521:MYORA1,scott,m123); /3.创建CallableStatementCallableStatement cs = ct.prepareCall(call sp_pro7(?,?,?); /4.给?赋值 cs.setInt(1,10); cs.setString(2,笑傲江湖); cs.setString(3,人民出版社);*/ /看看如何调用有返回值的过程/创建CallableStatement /*CallableStatement cs = ct.prepareCall(call

30、 sp_pro8(?,?); /给第一个？赋值cs.setInt(1,7788);/给第二个？赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); /5.执行 cs.execute();/取出返回值,要注意？的顺序String name=cs.getString(2);System.out.println(7788 的名字+name); catch(Exception e)e.printStackTrace(); finally/6.关闭各个打开的资源 cs.close(); ct.close(); PL/SQL 块的结构

31、和实例 案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。Sql 代码1. -有输入和输出的存储过程2. create or replace procedure sp_pro83. (spno in number, spName out varchar2,spSal out number,spJob outvarchar2) is4. begin5. select ename,sal,job into spName,spSal,spJob from emp whereempno=spno;6. end;7. /PL/SQL 块的结构和实例 try /1.加载驱动 Cla

32、ss.forName(oracle.jdbc.driver.OracleDriver); /2.得到连接 Connection ct = DriverManager.getConnection(jdbc:oracle:thin127.0.0.1:1521:MYORA1,scott,m123);/3.创建CallableStatement/*CallableStatement cs = ct.prepareCall(call sp_pro7(?,?,?); /4.给?赋值 cs.setInt(1,10);cs.setString(2,笑傲江湖); cs.setString(3,人民出版社);*/

33、 /看看如何调用有返回值的过程 /创建CallableStatement /*CallableStatement cs = ct.prepareCall(call sp_pro8(?,?,?,?); /给第一个？赋值cs.setInt(1,7788);/给第二个？赋值cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);/给第三个？赋值 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE); /给第四个？赋值cs.registerOutParameter(4,oracl

34、e.jdbc.OracleTypes.VARCHAR); /5.执行cs.execute(); /取出返回值,要注意？的顺序String name=cs.getString(2); String job=cs.getString(4);System.out.println(7788 的名字+name+ 工作：+job); catch(Exception e) e.printStackTrace(); finally /6.关闭各个打开的资源cs.close(); ct.close(); PL/SQL 块的结构和实例 案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题分析如下：由于o

35、racle 存储过程没有返回值，它的所有返回值都是通过out 参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage 了。所以要分两部分：返回结果集的过程1.建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下：Sql 代码1. create or replace package testpackage as2. TYPE test_cursor is ref cursor;3. end testpackage;2.建立存储过程。如下：Sql 代码1. create or replace procedure sp_pro9(spNo in

36、 number,p_cursor outtestpackage.test_cursor) is2. begin3. open p_cursor for4. select * from emp where deptno = spNo;5. end sp_pro9;PL/SQL 块的结构和实例 1. import java.sql.*;2. public class Test23. public static void main(String args)4.5. try6. /1.加载驱动7. Class.forName(oracle.jdbc.driver.OracleDriver);8. /2

37、.得到连接9. Connection ct = DriverManager.getConnection(jdbc:oracle:thin127.0.0.1:1521:MYORA1,scott,m123);10.11. /看看如何调用有返回值的过程12. /3.创建CallableStatement13. /*CallableStatement cs = ct.prepareCall(call sp_pro9(?,?);14.15. /4.给第？赋值16. cs.setInt(1,10);17. /给第二个？赋值18. cs.registerOutParameter(2,oracle.jdbc.

38、OracleTypes.CURSOR);19.20. /5.执行21. cs.execute();22. /得到结果集23. ResultSet rs=(ResultSet)cs.getObject(2);24. while(rs.next()25. System.out.println(rs.getInt(1)+ +rs.getString(2);26. 27. catch(Exception e)28. e.printStackTrace();29. finally30. /6.关闭各个打开的资源31. cs.close();32. ct.close();33. 34. 35.PL/SQL

39、 块的结构和实例 编写分页过程有了上面的基础，相信大家可以完成分页存储过程了。要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。如果大家忘了oracle 中如何分页，请参考第三天的内容。先自己完成，老师在后面给出答案，并讲解。-oracle 的分页Sql 代码1. select t1.*, rownum rn from (select * from emp) t1 where rownum=10;2. -在分页时，大家可以把下面的sql 语句当做一个模板使用3. select * from4. (select t1.*, rownum

40、 rn from (select * from emp) t1 where rownum=6;-开发一个包-建立一个包，在该包中，我定义类型test_cursor,是个游标。 如下：Sql 代码1. create or replace package testpackage as2. TYPE test_cursor is ref cursor;3. end testpackage;4. -开始编写分页的过程5. create or replace procedure fenye6. (tableName in varchar2,7. Pagesize in number,-一页显示记录数8.

41、 pageNow in number,9. myrows out number,-总记录数10. myPageCount out number,-总页数11. p_cursor out testpackage.test_cursor-返回的记录集12. ) is13.-定义部分14.-定义sql 语句 字符串15.v_sql varchar2(1000);16.-定义两个整数17.v_begin number:=(pageNow-1)*Pagesize+1;18.v_end number:=pageNow*Pagesize;19.begin20.-执行部分21.v_sql:=select *

42、from (select t1.*, rownum rn from (select * from |tableName|) t1 where rownum=|v_begin;22.-把游标和sql 关联23.open p_cursor for v_sql;24.-计算myrows 和myPageCount25.-组织一个sql 语句26.v_sql:=select count(*) from |tableName;27.-执行sql,并把返回的值，赋给myrows；28.execute immediate v_sql into myrows;29.-计算myPageCount30.-if my

43、rows%Pagesize=0 then 这样写是错的31.if mod(myrows,Pagesize)=0 then32. myPageCount:=myrows/Pagesize;33.else34. myPageCount:=myrows/Pagesize+1;35.end if;36.-关闭游标37.-close p_cursor;38.end;39./-使用java 测试/测试分页PL/SQL 块的结构和实例 -新的需要，要求按照薪水从低到高排序，然后取出6-10过程的执行部分做下改动，如下：Sql 代码1. begin2. -执行部分3. v_sql:=select * from (select t1.*, rownum rn from (select * from |tableName| order by sal) t1 where rownum=|v_begin;重新执行一次procedure,java 不用改变，运行