PL/SQL和Oracle对象

zsyzhou

　　

　　
package com.my.utils;
　　

　　
import java.sql.CallableStatement;
　　
import java.sql.Connection;
　　
import java.sql.ResultSet;
　　
import java.sql.SQLException;
　　

　　
import org.junit.Test;
　　

　　
import oracle.jdbc.OracleTypes;
　　
import oracle.jdbc.driver.OracleCallableStatement;
　　
import oracle.jdbc.oracore.OracleType;
　　

　　
public class TestOracle {
　　

　　
/*
　　
* 存储过程（out参数）
　　
*
　　
查询某个员工的姓名 月薪 职位
　　
思考：
　　
1. 查询某个员工的所有信息 ---> out参数太多
　　
2. 查询某个部门中的所有员工信息  ---> 返回集合
　　
*
　　
create or replace procedure queryEmpInformation(eno in number,
　　
            pename out varchar2,
　　
            psal   out number,
　　
            pjob   out varchar2)
　　
as
　　
begin
　　
   select ename,sal,job into pename,psal,pjob from emp where empno=eno;
　　
end;
　　
/
　　
* */
　　
@Test
　　
public void testProcedure(){
　　
String sql="{call queryEmpInformation(?,?,?,?)}";
　　

　　
Connection conn=null;
　　
CallableStatement call=null;
　　

　　
try {
　　
conn=JDBCUtils.getConnection();
　　
call=conn.prepareCall(sql);
　　

　　
call.setInt(1, 7839);
　　
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);
　　
} catch (SQLException e) {
　　
e.printStackTrace();
　　
}finally {
　　
JDBCUtils.release(conn, call, null);
　　
}
　　
}
　　

　　
/*
　　
* 存储函数
　　
*
　　
* --查询某个员工的年收入
　　
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;
　　
/
　　
* */
　　

　　
@Test
　　
public void testfunction(){
　　
String sql="{?=call queryEmpIncome(?)}";
　　

　　
Connection conn=null;
　　
CallableStatement call=null;
　　

　　
try {
　　
conn=JDBCUtils.getConnection();
　　
call=conn.prepareCall(sql);
　　

　　
call.registerOutParameter(1, OracleTypes.NUMBER);
　　

　　
call.setInt(2,7839);
　　
call.execute();
　　
double income=call.getDouble(1);
　　
System.out.println(income);
　　
} catch (SQLException e) {
　　
e.printStackTrace();
　　
}finally {
　　
JDBCUtils.release(conn, call, null);
　　
}
　　
}
　　

　　
/*
　　
在out中使用光标
　　
2. 查询某个部门中的所有员工信息  ---> 返回集合
　　
包头
　　
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(){
　　
String sql="{call MYPACKAGE.QUERYEMPLIST(?,?)}";
　　

　　
Connection conn=null;
　　
CallableStatement call=null;
　　
ResultSet rs=null;
　　
try {
　　
conn=JDBCUtils.getConnection();
　　
call=conn.prepareCall(sql);
　　

　　
call.setInt(1, 10);
　　

　　
call.registerOutParameter(2,OracleTypes.CURSOR);
　　

　　
call.execute();
　　

　　
rs=((OracleCallableStatement)call).getCursor(2);
　　

　　
while(rs.next()){
　　
String name=rs.getString("ename");
　　
double sal=rs.getDouble("sal");
　　
String job=rs.getString("job");
　　
System.out.println(name+"\t"+sal+"\t"+job);;
　　
}
　　

　　

　　
} catch (SQLException e) {
　　
e.printStackTrace();
　　
}finally {
　　
JDBCUtils.release(conn, call, null);
　　
}
　　
}
　　

　　
}