oracle存储过程

存储过程

1、创建

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

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

begin

//执行的语句

end

例：项目中所用的：

CREATE OR REPLACE PROCEDURE PROC_CBBS_FILES

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

 --/******************************************************

  --/*Procedure     :PROC_CBBS_FILES                                                -----存储过程

  --/*Discription   :把mv_f_xinxg_files视图中的数据依次插入mv_f_cbbs_files表中    -----存储过程描述

  --/*Version      :1.0---初始版本

  --/*Author       :郝晓利

  --/*Create Date  :2014/08/26

 --/*****************************************************

 AS

  insert_str long; ----插入表语句

BEGIN

  FOR x IN(select * frommv_f_xinxg_files)

    LOOP

    -----循环

    insert_str := 'INSERT INTOmv_f_cbbs_files(MO_ID,CAPTION,TIME,FILES,PROV_ID,PROV_NAME,TACHE_NAME,BUSI_TYPE)

               VALUES(''' ||x.MO_ID || ''',''' || x.CAPTION || ''',''' || x.TIME || ''',''' ||

               x.FILES || ''','''|| x.PROV_code || ''',''' || x.PROV_NAME || ''',''' || x.TACHE_NAME || ''','''||x.busi_type|| ''',)';

    ----执行insert语句

    EXECUTE IMMEDIATE insert_str;

    COMMIT; -----提交

  END LOOP; -----结束循环

END; -----BEGIN  END

例：①无返回值的存储过程：

请写一个过程，可以向 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. /

--在 java 中调用

Java 代码

1. //调用一个无返回值的过程

2. import java.sql.*;

3. public class Test2{

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

5.

6. try{

7. //1.加载驱动

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

9. //2.得到连接

10. Connection ct = DriverManager.getConnection("jdbc:o

racle:thin@127.0.0.1:1521:MYORA1","scott","m123");

11.

12. //3.创建 CallableStatement

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

14. //4.给?赋值

15. cs.setInt(1,10);

16. cs.setString(2,"笑傲江湖");

17. cs.setString(3,"人民出版社");

18. //5.执行

19. cs.execute();

20. } catch(Exception e){

21. e.printStackTrace();

22. } finally{

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

24. cs.close();

25. ct.close();

26. }

27. }

28.}

②有返回值的存储过程（非列表）

例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。

Sql 代码

1. --有输入和输出的存储过程

 create or replace procedure sp_pro8(spno in number, spName out varchar2)is

 begin

 select ename into spName from empwhere empno=spno;

 end;

 /

Java 代码

 import java.sql.*;

 public class Test2{

 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("{callsp_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();

}

}

}注：1、对于过程的输入值，使用setXXX,对于输出值，使用registerOutParameter,问号的顺序要对应，同时考虑类型。

2、取出过程返回值的方法是CallableStatement提供的getXXX（输出参数的位置）同时考虑输出的参数类型

案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

Sql 代码

1. --有输入和输出的存储过程

2. create or replace procedure sp_pro8

3. (spno in number, spName out varchar2,spSal out number,spJob outvarchar2) is

4. begin

5. select ename,sal,job into spName,spSal,spJob from emp where empno=spno;

6. end;

7. /

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. //3.创建 CallableStatement

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

13. //4.给?赋值

14. cs.setInt(1,10);

15. cs.setString(2,"笑傲江湖");

16. cs.setString(3,"人民出版社");*/

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;