oracle存储过程

存储过程

1、创建

create procedure 过程名（变量名 in 变量类型...变量名 out 变量类型...）is

//定义变量  注：变量类型后不需要指定大小

begin

//执行的语句

end

例：项目中所用的：

CREATE OR REPLACE PROCEDURE PROC_CBBS_FILES

------存储过程说明

 --

//看看如何调用有返回值的过程

//创建 CallableStatement

18. //看看如何调用有返回值的过程

19. //创建 CallableStatement

20. /*CallableStatement cs = ct.prepareCall("{callsp_pro8(?,?,?,?)}");

22. //给第一个？赋值

23. cs.setInt(1,7788);

24. //给第二个？赋值

25. cs.reGISterOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

26. //给第三个？赋值

27. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);

28. //给第四个？赋值

29. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);

31. //5.执行

32. cs.execute();

33. //取出返回值,要注意？的顺序

34. String name=cs.getString(2);

35. String job=cs.getString(4);

36. System.out.println("7788 的名字"+name+" 工作："+job);

37. } catch(Exception e){

38. e.printStackTrace();

39. } finally{

40. //6.关闭各个打开的资源

41. cs.close();

42. ct.close();

43. }

44. }

45.}

③有返回值的存储过程（列表[结果集]）

案例：编写一个过程，输入部门号，返回该部门所有雇员信息。

由于 oracle 存储过程没有返回值，它的所有返回值都是通过 out 参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：

返回结果集的过程

1.建立一个包，在该包中，定义类型 test_cursor,是个游标。 如下：

sql 代码

create or replace package testpackage as

TYPE test_cursor is ref cursor;

end testpackage;

2.建立存储过程。如下：

Sql 代码

1. create or replace procedure sp_pro9(spNo in number,p_cursor outtestpackage.test_cursor) is

2. begin

3. open p_cursor for select * from emp where deptno = spNo;

5. end sp_pro9;

3.如何在 java 程序中调用该过程

Java 代码

1. import java.sql.*;

2. public class Test2{

3. public static void main(String[] args){

5. try{

6. //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. //看看如何调用有返回值的过程

12. //3.创建 CallableStatement

13. /*CallableStatement cs = ct.prepareCall("{callsp_pro9(?,?)}");

15. //4.给第？赋值

16. cs.setInt(1,10);

17. //给第二个？赋值

18. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

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. } finally{

30. //6.关闭各个打开的资源

31. cs.close();

32. ct.close();

33. }

34. }

35.}

运行，成功得出部门号是 10 的所有用户

④编写分页过程

例：编写一个存储过程，要求可以输入表名、每页显示记录数、当前

页。返回总记录数，总页数，和返回的结果集。

Sql 代码

1. select t1.*, rownum rn from (select * from emp) t1 whererownum<=10;

2. --在分页时，大家可以把下面的 sql 语句当做一个模板使用

3. select * from

4. (select t1.*, rownum rn from (select * from emp) t1 whererownum<=10)

5. where rn>=6;

建立一个包，在该包中，我定义类型 test_cursor,是个游标。如下：

Sql 代码

1. create or replace package testpackage as

2. TYPE test_cursor is ref cursor;

3. end testpackage;

 --开始编写分页的过程

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 * from'||tableName||') t1 where rownum<='||v_end||') where rn>='||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 inmediate 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 测试

//测试分页

Java 代码

import java.sql.*;

public class FenYe{

public static void main(String[] args){

  try{

  //1.加载驱动

   Class.forName("oracle.jdbc.driver.OracleDriver");

  //2.得到连接

  Connection ct =DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

 //3.创建 CallableStatement

 CallableStatement cs = ct.prepareCall("{callfenye(?,?,?,?,?,?)}");

 //4.给第？赋值

 cs.seString(1,"emp");

 cs.setInt(2,5);

 cs.setInt(3,2);

 //注册总记录数

 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

 //注册总页数

 cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

 //注册返回的结果集

 cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

 //5.执行

 cs.execute();

 //取出总记录数 /这里要注意，getInt(4)中 4，是由该参数的位置决定的

 int rowNum=cs.getInt(4);

 int pageCount = cs.getInt(5);

 ResultSet rs=(ResultSet)cs.getObject(6);

 //显示一下，看看对不对

 System.out.println("rowNum="+rowNum);

 System.out.println("总页数="+pageCount);

 while(rs.next()){

 System.out.println("编号： "+rs.getInt(1)+" 名字："+rs.getString(2)+"工资："+rs.getFloat(6));

 }

 } catch(Exception e){

 e.printStackTrace();

 } finally{

 //6.关闭各个打开的资源

 cs.close();

 ct.close();

 }

 }

}

运行，控制台输出：

rowNum=19

总页数：4

编号：7369 名字：SMITH 工资：2850.0

编号：7499 名字：ALLEN 工资：2450.0

编号：7521 名字：WARD 工资：1562.0

编号：7566 名字：JONES 工资：7200.0

编号：7654 名字：MARTIN 工资：1500.0

--新的需要，要求按照薪水从低到高排序，然后取出 6-10

过程的执行部分做下改动，如下：

Sql 代码

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;

重新执行一次 procedure,java 不用改变，运行，控制台输出：

rowNum=19

总页数：4

编号：7900 名字：JAMES 工资：950.0

编号：7876 名字：ADAMS 工资：1100.0

编号：7521 名字：WARD 工资：1250.0

编号：7654 名字：MARTIN 工资：1250.0

编号：7934 名字：MILLER 工资：1300.0

2、㈠在oracle客户端调用过程有两种方法：

   ①exec 过程名（参数值...）

   ②call 过程名（参数值...）

实际begin

     存储过程名;

end;（或者右击存储过程名并点击测试）

 　㈡在JAVA中调用存储过程方法：

　①加载驱动：Class.forName("oracle.jdbc.driver.OracleDriver");

　②得到链接：ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:数据库名","用户名","密码")

　③创建CallableStatement接口对象：cs=ct.prepareCall("{call过程名（？，？）}");

　④给？赋值：cs.setString(1,"haoxiaoli");//？为你插入的字段个数，与SQL语句有关

　⑤执行我们的语句：cs.execute();

注：如果卡了记得在oracle中先提交commit

3、显示错误：

show error;