调用oracle存储过程详解.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,第,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_WRAPPED,1.,创建一个简单的表,Sql,代码,1.create table mytest(name varchar2(30),passwd varchar2(30);,PL/SQL,块的结构和实例,2.,创建过程,Sql,代码,1.,create,or,replace,procedure,sp_pro1,is,2.,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 is,2.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,块的结构和实例,编写规范,编写规范,n,1.,注释,单行注释,-,Sql,代码,1.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,块的结构和实例,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,块,n,Sql,代码,1.set serveroutput on-,打开输出选项,2.begin,3.dbms_output.put_line(hello);,4.end;,相关说明：,dbms_output,是,oracle,所提供的包,(,类似,java,的开发包,),，该包包含一些过程，,put_line,就是,dbms_output,包的一个过程。,PL/SQL,块的结构和实例,pl/sql,块的实例,(2),实例,2-,包含定义部分和执行部分的,pl/sql,块,n,Sql,代码,1.declare,2.v_ename varchar2(5);-,定义字符串变量,3.begin,4.select ename into v_ename from emp where empno=,5.dbms_output.put_line(,雇员名：,|v_ename);,6.end;,7./,如果要把薪水也显示出来，那么执行部分就应该这么写：,Sql,代码,1.select ename,sal into v_ename,v_sal from emp where empno=,相关说明：,&,表示要接收从控制台输入的变量,PL/SQL,块的结构和实例,pl/sql,块的实例,(3),实例,3-,包含定义部分，执行部分和例外处理部分,为了避免,pl/sql,程序的运行错误，提高,pl/sql,的健壮性，应该对可能的错误,进行处理，这个很有必要。,1.,比如在实例,2,中，如果输入了不存在的雇员号，应当做例外处理。,2.,有时出现异常，希望用另外的逻辑处理,我们看看如何完成,1,的要求。,相关说明：,oracle,事先预定义了一些例外，,no_data_found,就是找不到数据的例外。,Sql,代码,1.declare,2.-,定义变量,3.v_ename varchar2(5);,4.v_sal number(7,2);,5.begin,6.-,执行部分,7.select ename,sal into v_ename,v_sal from emp where empno=,8.-,在控制台显示用户名,9.dbms_output.put_line(,用户名是：,|v_ename|,工资：,|v_sal);,10.-,异常处理,11.exception,12.when no_data_found then,13.dbms_output.put_line(,朋友，你的编号输入有误！,),；,14.end;,15./,PL/SQL,块的结构和实例,pl/sql,分类,-,过程，函数，包，触发器,过程,过程用于执行特定的操作，当建立过程时，既可以指定输入参数,(in),，也可以指,定输出参数,(out),，通过在过程中使用输入参数，可以将数据传递到执行部分；,通过使用输出参数，可以将执行部分的数据传递到应用环境。在,sqlplus,中可以,使用,create procedure,命令来建立过程。,PL/SQL,块的结构和实例,实例如下：,1.,请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资,2.,如何调用过程有两种方法；,exec call,3.,如何在,java,程序中调用一个存储过程,问题：如何使用过程返回值？,特别说明：,对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。,create procedure sp_pro3(spName varchar2,newSal number)is,-,不要写成,number(3,2),表明类型就可以了，不需要大小。就好像,Java,写方法,时的参数一样,Sql,代码,1.begin,2.-,执行部分，根据用户名去修改工资,3.update emp set sal=newSal where ename=spName;,4.end;,5./,PL/SQL,块的结构和实例,java,程序中调用一个存储过程,/,演示,java,程序去调用,oracle,的存储过程案例,5.try,6./1.,加载驱动,7.Class.forName(oracle.jdbc.driver.OracleDriver);,8./2.,得到连接,9.Connection ct=DriverManager.getConnection(jdbc:o,racle:thin:127.0.0.1:1521:MYORA1,scott,m123);,11./3.,创建,CallableStatement,12.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,中提供了三种条件分支语句,if,then,，,if,then,else,，,if,then,elsif,then,这里我们可以和,java,语句进行一个比较,简单的条件判断,if,thenn,问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于,2000,，就,给该员工工资增加,10%,。,Sql,代码,1.create or replace procedure sp_pro6(spName varchar2)is,2.-,定义,3.v_sal emp.sal%type;,4.begin,5.-,执行,6.select sal into v_sal from emp where ename=spName;,7.-,判断,8.if v_sal2000 then,9.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)is,2.-定义,3.v_comm m%type;,4.begin,5.-执行,6.select comm into v_comm from emp where ename=spName;,7.-判断,8.if v_comm0 then,9.update emp set comm=comm+100 where ename=spName;,10.else,11.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)is,2.-定义,3.v_job emp.job%type;,4.begin,5.-执行,6.select job into v_job from emp where empno=spNo;,7.if v_job=PRESIDENT then,8.update emp set sal=sal+1000 where empno=spNo;,9.elsif v_job=MANAGER then,10.update emp set sal=sal+500 where empno=spNo;,11.else,12.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)is,2.-定义:=表示赋值,3.v_num number:=1;,4.begin,5.loop,6.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)is,2.-定义:=表示赋值,3.v_num number:=11;,4.begin,5.while v_num,12.dbms_output.put_line(循环结束);,13.end;,PL/SQL,块的结构和实例,2.null,null 语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null,语句的主要好处是可以提高pl/sql 的可读性。,Sql 代码,1.declare,2.v_sal emp.sal%type;,3.v_ename emp.ename%type;,4.begin,5.select ename,sal into v_ename,v_sal from emp where empno=,6.if v_sal 3000 then,7.update emp set comm=sal*0.1 where ename=v_ename;,8.else,9.null;,10.end if;,11.end;,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,spbookNa,me in varchar2,sppublishHouse in varchar2)is,2.begin,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:o,racle: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,人民出版社,);,/5.,执行,cs.execute();,catch(Exception e),e.printStackTrace();,finally,/6.,关闭各个打开的资源,cs.close();,ct.close();,PL/SQL,块的结构和实例,有返回值的存储过程（非列表）,再看如何处理有返回值的存储过程：,案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。,案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗,位。,Sql,代码,1.-,有输入和输出的存储过程,2.create or replace procedure sp_pro8,3.(spno in number,spName out varchar2)is,4.begin,5.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:o,racle: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);,/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_pro8,3.(spno in number,spName out varchar2,spSal out number,spJob out,varchar2)is,4.begin,5.select ename,sal,job into spName,spSal,spJob from emp where,empno=spno;,6.end;,7./,PL/SQL,块的结构和实例,try,/1.加载驱动,Class.forName(oracle.jdbc.driver.OracleDriver);,/2.得到连接,Connection ct=DriverManager.getConnection(jdbc:o,racle: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 as,2.TYPE test_cursor is ref cursor;,3.end testpackage;,2.,建立存储过程。如下：,Sql,代码,1.create or replace procedure sp_pro9(spNo in number,p_cursor out,testpackage.test_cursor)is,2.begin,3.open p_cursor for,4.select*from emp where deptno=spNo;,5.end sp_pro9;,PL/SQL,块的结构和实例,1.import java.sql.*;,2.public class Test2,3.public static void main(String args),4.,5.try,6./1.,加载驱动,7.Class.forName(oracle.jdbc.driver.OracleDriver);,8./2.,得到连接,9.Connection ct=DriverManager.getConnection(jdbc:o,racle:thin127.0.0.1:1521:MYORA1,scott,m123);,10.,11./,看看如何调用有返回值的过程,12./3.,创建,CallableStatement,13./*CallableStatement cs=ct.prepareCall(call sp_p,ro9(?,?);,14.,15./4.,给第？赋值,16.cs.setInt(1,10);,17./,给第二个？赋值,18.cs.registerOutParameter(2,oracle.jdbc.OracleTypes.C,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.getStrin,g(2);,26.,27.catch(Exception e),28.e.printStackTrace();,29.finally,30./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,=10;,2.-,在分页时，大家可以把下面的,sql,语句当做一个模板使用,3.select*from,4.(select t1.*,rownum rn from(select*from emp)t1 where row,num=6;,-,开发一个包,-,建立一个包，在该包中，我定义类型,test_cursor,是个游标。如下：,Sql,代码,1.create or replace package testpackage as,2.TYPE test_cursor is ref cursor;,3.end testpackage;,4.-,开始编写分页的过程,5.create or replace procedure fenye,6.(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.)is,13.-,定义部分,14.-,定义,sql,语句 字符串,15.v_sql varchar2(1000);,16.-,定义两个整数,17.v_begin number:=(pageNow-1)*Pagesize+1;,18.v_end number:=pageNow*Pagesize;,19.begin,20.-,执行部分,21.v_sql:=select*from(select t1.*,rownum rn from(select*fr,om|tableName|)t1 where rownum=|,v_begin;,22.-,把游标和,sql,关联,23.open p_cursor for v_sql;,24.-,计算,myrows,和,myPageCount,25.-,组织一个,sql,语句,26.v_sql:=select count(*)from|tableName;,27.-,执行,sql,并把返回的值，赋给,myrows,；,28.execute immediate v_sql into myrows;,29.-,计算,myPageCount,30.-if myrows%Pagesize=0 then,这样写是错的,31.if mod(myrows,Pagesize)=0 then,32.myPageCount:=myrows/Pagesize;,33.else,34.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 FenYe,3.public static void main(String args),4.,5.try,6./1.,加载驱动,7.Class.forName(oracle.jdbc.driver.OracleDriver);,8./2.,得到连接,9.Connection ct=DriverManager.getConnection(jdbc:o,racle:thin127.0.0.1:1521:MYORA1,scott,m123);,10.,11./3.,创建,CallableStatement,12.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./,注册总记录数,20.cs.registerOutParameter(4,oracle.jdbc.OracleTypes.I,NTEGER);,21./,注册总页数,22.cs.registerOutParameter(5,oracle.jdbc.OracleTypes.I,NTEGER);,23./,注册返回的结果集,24.cs.registerOutParameter(6,oracle.jdbc.OracleTypes.C,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.finally,45./6.,关闭各个打开的资源,46.cs.close();,47.ct.close();,48.,49.,50.,PL/SQL,块的结构和实例,-,新的需要，要求按照薪水从低到高排序，然后取出,6-10,过程的执行部分做下改动，如下：,Sql,代码,1.begin,2.-,执行部分,3.v_sql:=select*from(select t1.*,rownum rn from(select*fr,om|tableName|order by sal)t1 where rownum=|v_begin;,重新执行一次,procedure,java,不用改变，运行,