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 as6 begin7 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 REPLACE16 PACKAGE BODY MYPACKAGE AS17 18 procedure queryEmpList(dno in number,empList out empcursor) AS19 BEGIN20 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 [( , , ...)]} 17 * 调用存储过程. 没有返回值 18 {call [( , , ...)]} 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 [( , ,...)]} 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 [( , , ...)]} 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 REPLACE110 PACKAGE BODY MYPACKAGE AS111 112 procedure queryEmpList(dno in number,empList out empcursor) AS113 BEGIN114 open empList for select * from emp where deptno=dno;115 END queryEmpList;116 117 END MYPACKAGE; 118 */119 @Test120 public void testCursor(){121 //{call [( , , ...)]}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是抽象类,继承了CallableStatement140 //不强转没有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 as6 begin7 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 REPLACE16 PACKAGE BODY MYPACKAGE AS17 18 procedure queryEmpList(dno in number,empList out empcursor) AS19 BEGIN20 open empList for select * from emp where deptno=dno;21 END queryEmpList;22 23 END MYPACKAGE;
分析图:
参看包:
包无法在plsqldeveloper和sqldeveloper等工具中右键运行....必须通过java代码应用程序来调用执行(代码在上面)