Java代码调用存储过程和存储函数要使用CallableStatement接口
查看API文档:
上代码:
java代码调用如下的存储过程和函数:
查询某个员工的姓名 月薪 职位
create or replace procedure queryEmpinfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
--查询某个员工的年收入
create or replace function queryEmpIncome(eno in number)
return number
as
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno; --返回年收入
return psal*12+nvl(pcomm,0); end;
--在out参数中使用光标
查询某个部门中所有员工的所有信息 包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor); END MYPACKAGE; 包体
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList; END MYPACKAGE;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException; import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes; import org.junit.Test; public class TestOracle { /*
* CallableStatement 接口
* 调用存储函数,等号左边有一个返回值
* {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
* 调用存储过程. 没有返回值
{call <procedure-name>[(<arg1>,<arg2>, ...)]} *
*/ /*存储过程 查询某个员工的姓名 月薪 职位
* create or replace procedure queryEmpinfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
*/ @Test
public void testProcedure(){
//{call <procedure-name>[(<arg1>,<arg2>,...)]}
String sql = "{call queryEmpinfo(?,?,?,?)}";//4个问号中,第一个是输入参数,其余是输出参数
Connection conn = null;
//要用CallableStatement这个接口,用于执行 SQL 存储过程的接口
CallableStatement call = null; try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//对于in参数,需要赋值
call.setInt(1,7839);
//对于out参数,需要声明
call.registerOutParameter(2, OracleTypes.VARCHAR);//第二个是字符串
call.registerOutParameter(3, OracleTypes.NUMBER);//第三个是数字
call.registerOutParameter(4, OracleTypes.VARCHAR);//第四个是字符串 call.execute();
//取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name+"\t"+sal+"\t"+job+"\t");
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);//没有最后一个参数就传入null
}
} /*存储函数 查询某个员工的姓名,月薪和职位
* create or replace function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction(){
//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
//第一个问号是函数的返回值,第二个问号是输入参数. 返回值的作用和输出参数是一样的.
String sql = "{?=call QUERYEMPINCOME(?)}";//这个call后面的存储过程名或者是存储函数名大写或者是小写是没有要求的.
Connection conn = null;
//要用CallableStatement这个接口,用于执行 SQL 存储过程的接口
CallableStatement call = null; try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql); //对于in参数,赋值
call.setInt(2,7839); //对于out参数，申明
call.registerOutParameter(1, OracleTypes.NUMBER);
call.execute();
//取出结果
//取出结果
double income = call.getDouble(1);
System.out.println(income);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);//没有最后一个参数就传入null
} } /*
查询某个部门中所有员工的所有信息
包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor); END MYPACKAGE; 包体
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList; END MYPACKAGE;
*/
@Test
public void testCursor(){
//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{call MYPACKAGE.queryEmpList(?,?)}"; Connection conn = null;
CallableStatement call = null;
//有游标,就有结果集
ResultSet rest = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql); //对于in参数，赋值
call.setInt(1, 20); //对于out参数，申明
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
//取出集合
//这个地方要强转!!!OracleCallableStatement是抽象类,继承了CallableStatement
//不强转没有getCursor()方法...
rest = ((OracleCallableStatement)call).getCursor(2);
while(rest.next()){
String name = rest.getString("ename");
double sal = rest.getDouble("sal");
System.out.println(name+"\t"+sal);
}
}catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rest);//上面打开了光标,再这个地方关闭结果集rest,也就关闭了光标
}
}
}
关于Oracle中的包对象:
之前的存储函数中查询的是某一个员工的信息:
create or replace procedure queryEmpinfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
但是①如果要查询一个员工的所有信息,而这个员工的信息对应的有几百列
在存储函数中括号的函数要把这几百列都声明出来?
②如果要查询某个部门中所有员工的所有信息...这个信息对应的是一个集合.
第二个问题解决了第一个问题也就解决了.
怎么在存储过程或者存储函数中返回一个集合.
学到现在有多少种方式可以代表一个集合?
第一个是表,第二个是select语句也可以.第三个是光标.
在out参数中使用光标.但是有一个要求,必须要声明一个包,包分为包头和包体.也是数据库的对象.跟表,视图,等是一样的是数据库的对象.
包头只负责声明,包体只负责实现.
--在out参数中使用光标
查询某个部门中所有员工的所有信息 包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor); END MYPACKAGE; 包体
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList; END MYPACKAGE;
分析图:
参看包:
包无法在plsqldeveloper和sqldeveloper等工具中右键运行....必须通过java代码应用程序来调用执行(代码在上面)