调用oracle存储过程(详解).ppt

第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.减少网络传输量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 块的结构和实例,需要在屏幕上显示出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 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 的一个附带品。举一个简单案例：编写一个存储过程，该过程可以删除某表记录。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 编程，我们可以轻松地完成非常复杂的查询要求。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 块介绍介绍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 开始的，该部分是可选的；执行部分是从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 块的结构和实例,pl/sql 块的实例(2)实例2-包含定义部分和执行部分的pl/sql 块nSql 代码1.declare2.v_ename varchar2(5);-定义字符串变量3.begin4.select ename into v_ename from emp where empno=相关说明：&表示要接收从控制台输入的变量,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 number(7,2);5.begin6.-执行部分7.select ename,sal into v_ename,v_sal from emp where empno=15./,PL/SQL 块的结构和实例,pl/sql分类-过程，函数，包，触发器 过程过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus 中可以使用create procedure 命令来建立过程。,PL/SQL 块的结构和实例,实例如下：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=newSal 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.CallableStatement 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 中提供了三种条件分支语句ifthen，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 then9.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_comm 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 就给他的工资增加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=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 procedure 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.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.end 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 语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用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 语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高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=,PL/SQL 块的结构和实例,PL/SQL分页编写分页过程介绍分页是任何一个网站(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.begin3.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=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 块的结构和实例,有返回值的存储过程（非列表）再看如何处理有返回值的存储过程：案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。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=DriverManager.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 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 块的结构和实例,案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。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.加载驱动 Class.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,人民出版社);*/看看如何调用有返回值的过程/创建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,oracle.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 块的结构和实例,案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题分析如下：由于oracle 存储过程没有返回值，它的所有返回值都是通过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 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.得到连接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./给第二个？赋值URSOR);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 块的结构和实例,编写分页过程有了上面的基础，相信大家可以完成分页存储过程了。要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。如果大家忘了oracle 中如何分页，请参考第三天的内容。先自己完成，老师在后面给出答案，并讲解。-oracle 的分页Sql 代码1.select t1.*,rownum 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.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*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 myrows%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 块的结构和实例,1.import java.sql.*;2.public class FenYe3.public static void main(String args)4.5.try6./1.加载驱动7.Class.forName(oracle.jdbc.driver.OracleDriver);8./2.得到连接9.Connection ct=DriverManager.getConnection(jdbc:oracle:thin127.0.0.1:1521:MYORA1,scott,m123);10.11./3.创建CallableStatement12.CallableStatement cs=ct.prepareCall(call fenye(?,?,?,?,?,?);13.14./4.给第？赋值15.cs.seString(1,emp);16.cs.setInt(2,5);17.cs.setInt(3,2);18.19./注册总记录数NTEGER);21./注册总页数NTEGER);23./注册返回的结果集URSOR);25.26./5.执行27.cs.execute();28.29./取出总记录数/这里要注意，getInt(4)中4，是由该参数的位置决定的30.int rowNum=cs.getInt(4);31.32.int pageCount=cs.getInt(5);33.ResultSet rs=(ResultSet)cs.getObject(6);34.35./显示一下，看看对不对36.System.out.println(rowNum=+rowNum);37.System.out.println(总页数=+pageCount);38.39.while(rs.next()40.System.out.println(编号：+rs.getInt(1)+名字：+rs.getString(2)+工资：+rs.getFloat(6);41.42.catch(Exception e)43.e.printStackTrace();44.finally45./6.关闭各个打开的资源46.cs.close();47.ct.close();48.49.50.,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 不用改变，运行,