Java代码调用存储过程和存储函数要使用CallableStatement接口
查看API文档:
上代码:
java代码调用如下的存储过程和函数:
查询某个员工的姓名 月薪 职位
1 create or replace procedure queryEmpinfo(eno in number, 2 pename out varchar2, 3 psal out number, 4 pjob out varchar2) 5 as 6 begin 7 select ename,sal,empjob into pename,psal,pjob from emp where empno=eno; 8 end;
1 --查询某个员工的年收入 2 create or replace function queryEmpIncome(eno in number) 3 return number 4 as 5 psal emp.sal%type; 6 pcomm emp.comm%type; 7 begin 8 select sal,comm into psal,pcomm from emp where empno=eno; 9 10 --返回年收入 11 return psal*12+nvl(pcomm,0); 12 13 end;
1 --在out参数中使用光标 2 查询某个部门中所有员工的所有信息 3 4 5 包头 6 CREATE OR REPLACE PACKAGE MYPACKAGE AS 7 8 type empcursor is ref cursor; 9 procedure queryEmpList(dno in number,empList out empcursor); 10 11 END MYPACKAGE; 12 13 14 包体 15 CREATE OR REPLACE 16 PACKAGE BODY MYPACKAGE AS 17 18 procedure queryEmpList(dno in number,empList out empcursor) AS 19 BEGIN 20 open empList for select * from emp where deptno=dno; 21 END queryEmpList; 22 23 END MYPACKAGE;
1 import java.sql.CallableStatement; 2 import java.sql.Connection; 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 6 import oracle.jdbc.driver.OracleCallableStatement; 7 import oracle.jdbc.driver.OracleTypes; 8 9 import org.junit.Test; 10 11 public class TestOracle { 12 13 /* 14 * CallableStatement 接口 15 * 调用存储函数,等号左边有一个返回值 16 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 17 * 调用存储过程. 没有返回值 18 {call <procedure-name>[(<arg1>,<arg2>, ...)]} 19 20 * 21 */ 22 23 /*存储过程 查询某个员工的姓名 月薪 职位 24 * create or replace procedure queryEmpinfo(eno in number, 25 pename out varchar2, 26 psal out number, 27 pjob out varchar2) 28 */ 29 30 @Test 31 public void testProcedure(){ 32 //{call <procedure-name>[(<arg1>,<arg2>,...)]} 33 String sql = "{call queryEmpinfo(?,?,?,?)}";//4个问号中,第一个是输入参数,其余是输出参数 34 Connection conn = null; 35 //要用CallableStatement这个接口,用于执行 SQL 存储过程的接口 36 CallableStatement call = null; 37 38 try { 39 conn = JDBCUtils.getConnection(); 40 call = conn.prepareCall(sql); 41 //对于in参数,需要赋值 42 call.setInt(1,7839); 43 //对于out参数,需要声明 44 call.registerOutParameter(2, OracleTypes.VARCHAR);//第二个是字符串 45 call.registerOutParameter(3, OracleTypes.NUMBER);//第三个是数字 46 call.registerOutParameter(4, OracleTypes.VARCHAR);//第四个是字符串 47 48 call.execute(); 49 //取出结果 50 String name = call.getString(2); 51 double sal = call.getDouble(3); 52 String job = call.getString(4); 53 System.out.println(name+"\t"+sal+"\t"+job+"\t"); 54 } catch (SQLException e) { 55 e.printStackTrace(); 56 }finally{ 57 JDBCUtils.release(conn, call, null);//没有最后一个参数就传入null 58 } 59 } 60 61 /*存储函数 查询某个员工的姓名,月薪和职位 62 * create or replace function queryEmpIncome(eno in number) 63 return number 64 */ 65 @Test 66 public void testFunction(){ 67 //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 68 //第一个问号是函数的返回值,第二个问号是输入参数. 返回值的作用和输出参数是一样的. 69 String sql = "{?=call QUERYEMPINCOME(?)}";//这个call后面的存储过程名或者是存储函数名大写或者是小写是没有要求的. 70 Connection conn = null; 71 //要用CallableStatement这个接口,用于执行 SQL 存储过程的接口 72 CallableStatement call = null; 73 74 try { 75 conn = JDBCUtils.getConnection(); 76 call = conn.prepareCall(sql); 77 78 //对于in参数,赋值 79 call.setInt(2,7839); 80 81 //对于out参数，申明 82 call.registerOutParameter(1, OracleTypes.NUMBER); 83 call.execute(); 84 //取出结果 85 //取出结果 86 double income = call.getDouble(1); 87 System.out.println(income); 88 } catch (SQLException e) { 89 e.printStackTrace(); 90 }finally{ 91 JDBCUtils.release(conn, call, null);//没有最后一个参数就传入null 92 } 93 94 95 } 96 97 /* 98 查询某个部门中所有员工的所有信息 99 包头 100 CREATE OR REPLACE PACKAGE MYPACKAGE AS 101 102 type empcursor is ref cursor; 103 procedure queryEmpList(dno in number,empList out empcursor); 104 105 END MYPACKAGE; 106 107 108 包体 109 CREATE OR REPLACE 110 PACKAGE BODY MYPACKAGE AS 111 112 procedure queryEmpList(dno in number,empList out empcursor) AS 113 BEGIN 114 open empList for select * from emp where deptno=dno; 115 END queryEmpList; 116 117 END MYPACKAGE; 118 */ 119 @Test 120 public void testCursor(){ 121 //{call <procedure-name>[(<arg1>,<arg2>, ...)]} 122 String sql = "{call MYPACKAGE.queryEmpList(?,?)}"; 123 124 Connection conn = null; 125 CallableStatement call = null; 126 //有游标,就有结果集 127 ResultSet rest = null; 128 try { 129 conn = JDBCUtils.getConnection(); 130 call = conn.prepareCall(sql); 131 132 //对于in参数，赋值 133 call.setInt(1, 20); 134 135 //对于out参数，申明 136 call.registerOutParameter(2, OracleTypes.CURSOR); 137 call.execute(); 138 //取出集合 139 //这个地方要强转!!!OracleCallableStatement是抽象类,继承了CallableStatement 140 //不强转没有getCursor()方法... 141 rest = ((OracleCallableStatement)call).getCursor(2); 142 while(rest.next()){ 143 String name = rest.getString("ename"); 144 double sal = rest.getDouble("sal"); 145 System.out.println(name+"\t"+sal); 146 } 147 }catch (Exception e) { 148 e.printStackTrace(); 149 }finally{ 150 JDBCUtils.release(conn, call, rest);//上面打开了光标,再这个地方关闭结果集rest,也就关闭了光标 151 } 152 } 153 }
关于Oracle中的包对象:
之前的存储函数中查询的是某一个员工的信息:
1 create or replace procedure queryEmpinfo(eno in number, 2 pename out varchar2, 3 psal out number, 4 pjob out varchar2) 5 as 6 begin 7 select ename,sal,empjob into pename,psal,pjob from emp where empno=eno; 8 end;
但是①如果要查询一个员工的所有信息,而这个员工的信息对应的有几百列
在存储函数中括号的函数要把这几百列都声明出来?
②如果要查询某个部门中所有员工的所有信息...这个信息对应的是一个集合.
第二个问题解决了第一个问题也就解决了.
怎么在存储过程或者存储函数中返回一个集合.
学到现在有多少种方式可以代表一个集合?
第一个是表,第二个是select语句也可以.第三个是光标.
在out参数中使用光标.但是有一个要求,必须要声明一个包,包分为包头和包体.也是数据库的对象.跟表,视图,等是一样的是数据库的对象.
包头只负责声明,包体只负责实现.
1 --在out参数中使用光标 2 查询某个部门中所有员工的所有信息 3 4 5 包头 6 CREATE OR REPLACE PACKAGE MYPACKAGE AS 7 8 type empcursor is ref cursor; 9 procedure queryEmpList(dno in number,empList out empcursor); 10 11 END MYPACKAGE; 12 13 14 包体 15 CREATE OR REPLACE 16 PACKAGE BODY MYPACKAGE AS 17 18 procedure queryEmpList(dno in number,empList out empcursor) AS 19 BEGIN 20 open empList for select * from emp where deptno=dno; 21 END queryEmpList; 22 23 END MYPACKAGE;
分析图:
参看包:
包无法在plsqldeveloper和sqldeveloper等工具中右键运行....必须通过java代码应用程序来调用执行(代码在上面)
本文转自SummerChill博客园博客，原文链接：http://www.cnblogs.com/DreamDrive/p/6239021.html，如需转载请自行联系原作者