问题2:编写一个过程,可以输入雇员的编号,返回雇员的姓名、工资、岗位
--首先编写过程
create procedure sp_pro9(spNo in number,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spNo;
end;
java调用过程的代码:
package com.sw;
import java.sql.*;
public class Test3 {
/*
* 无返回值的过程实例
*/
public static void main(String[] args) {
//变量
Connection conn = null;
CallableStatement cs = null;
try {
//声明驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到链接
conn = DriverManager
.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
"scott","Oracle1");
//创建CallableStatement,调用过程必须使用
cs = conn.prepareCall("{call sp_pro9(?,?,?,?)}");
//给?赋值
cs.setInt(1, 7788);
//给其余的变量关联值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值
String name = cs.getString(2);
Number sal = cs.getInt(3);
String job = cs.getString(4);
System.out.println("7788的名字是:"+name);
System.out.println("7788的工资是:"+sal);
System.out.println("7788的工作是:"+job);
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭资源
try {
cs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}