PL/SQL （三）

-----------------------------------------------------------------------------------------------------------------------------------------------------

--有返回值的存储过程,且返回值是一个结果集
--案例:输入部门编号（deptno）,返回该部门所有员工的信息


--创建一个包（此处用as）,在包中定义了一个游标类型test_cursor，
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;


--创建存储过程
create or replace procedure sp_pro55(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;


--在java中调用
/*
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro55(?,?)}");
//给第一个？赋值
cs.setInt(1, 10);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);//游标
//执行
cs.execute();
//取出返回值,结果集
ResultSet rs = (ResultSet) cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt("empno")+","+rs.getString("ename"));//写1或2也可以
}
*/

===============================================================================

案例：

--将emp表中所有数据查询出来，返回结果集(使用游标)
create or replace package sp_package1 as
type sp_cursor is ref cursor;
end sp_package1;


--创建存储过程
create or replace procedure sp_pro10
(sp_table_cursor out sp_package1.sp_cursor) is
begin
open sp_table_cursor for select * from emp; 
end;

---------java代码------------

package test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;/** * java调用存储过程将,返回结果集(使用游标) */public class TestOracle7 {private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";private static String username = "scott";private static String password = "jsd161102";public static void main(String[] args) {List<Emp> list = getList();for(Emp e : list){System.out.println(e);}}public static List<Emp> getList() {Connection conn = null;List<Emp> emps = null;try{Class.forName("oracle.jdbc.driver.OracleDriver");conn = DriverManager.getConnection(url,username,password);CallableStatement cs = conn.prepareCall("{call sp_pro10(?)}");cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);cs.execute();ResultSet rs = (ResultSet) cs.getObject(1);emps = new ArrayList<Emp>();while(rs.next()){Emp emp = new Emp();emp.setComm(rs.getDouble("comm"));emp.setDeptno(rs.getInt("deptno"));emp.setEmpno(rs.getInt("empno"));emp.setEname(rs.getString("ename"));emp.setHiredate(rs.getDate("hiredate"));emp.setJob(rs.getString("job"));emp.setMgr(rs.getInt("mgr"));emp.setSal(rs.getDouble("sal"));emps.add(emp);}}catch(Exception e){e.printStackTrace();}finally{if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}return emps;}}

测试结果：

Emp [empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=1980-12-17, sal=210.0, comm=0.0, deptno=20]Emp [empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=1981-02-20, sal=1600.0, comm=300.0, deptno=30]Emp [empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=1981-02-22, sal=1250.0, comm=500.0, deptno=30]Emp [empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=1981-04-02, sal=3475.0, comm=0.0, deptno=20]Emp [empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=1981-09-28, sal=1250.0, comm=1400.0, deptno=30]Emp [empno=7698, ename=BLAKE, job=MANAGER, mgr=7839, hiredate=1981-05-01, sal=2850.0, comm=0.0, deptno=30]Emp [empno=7782, ename=CLARK, job=MANAGER, mgr=7839, hiredate=1981-06-09, sal=2450.0, comm=0.0, deptno=10]Emp [empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=1987-04-19, sal=110.0, comm=11.0, deptno=20]Emp [empno=7839, ename=KING, job=PRESIDENT, mgr=0, hiredate=1981-11-17, sal=6000.0, comm=0.0, deptno=10]Emp [empno=7844, ename=TURNER, job=SALESMAN, mgr=7698, hiredate=1981-09-08, sal=1500.0, comm=0.0, deptno=30]Emp [empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=1987-05-23, sal=1100.0, comm=0.0, deptno=20]Emp [empno=7900, ename=JAMES, job=CLERK, mgr=7698, hiredate=1981-12-03, sal=950.0, comm=0.0, deptno=30]Emp [empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=1981-12-03, sal=3000.0, comm=0.0, deptno=20]Emp [empno=7934, ename=MILLER, job=CLERK, mgr=7782, hiredate=1982-01-23, sal=1300.0, comm=0.0, deptno=10]

================================================================================

分页：

案例：

存储过程如下：

-----------------------------------------------------------
--开发一个包，定义游标(使用上述包)


---------------编写分页的过程---------------
create or replace procedure fenye(
  tableName in varchar2,--表名字
  PageSize in number, --每页显示条数
  pageNow in number,--当前是第几页
  myRows out number, --总记录数
  myPageCount out number,--总页数
  p_cursor out sp_package1.sp_cursor--返回的记录集
) is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1) * PageSize + 1;
v_end number:=pageNow * PageSize;
begin
v_sql:='select * from (select rownum rw,t.* from (select * from '|| tablename
||' order by sal)t ) where rw >='||v_begin||' and rw<='||v_end;
--打开游标,关联sql
open p_cursor for v_sql;
--计算myRows(总记录数)
--组织一个sql，并把返回值赋值给myRows
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myRows;
--计算myPageCount(每页显示条数)
if mod(myRows,PageSize)=0 then
myPageCount:=myRows/PageSize;
else
myPageCount:=myRows/PageSize + 1;
end if;
--关闭游标
--close p_cursor;
end;

-------------------------------------------------------------------------------------

java代码如下：

package test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;/** * 调用有返回值的存储过程,返回值为结果集 */public class TestOracle8 {public static void main(String[] args) {Connection conn = null;try{Class.forName("oracle.jdbc.driver.OracleDriver");conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");//创建CallableStatementCallableStatement cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");/** *  tableName in varchar2,--表名字  PageSize in number, --每页显示条数  pageNow in number,--当前是第几页  myRows out number, --总记录数  myPageCount out number,--总页数  p_cursor out sp_package1.sp_cursor--返回的记录集 *///给第一个？赋值cs.setString(1, "emp");cs.setInt(2, 5);//每页5条cs.setInt(3, 2);//显示第2页//注册总记录数cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);//注册总页数cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);//注册返回的结果集cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);//执行cs.execute();//取出返回值int rowNum = cs.getInt(4);int pageCounts = cs.getInt(5);ResultSet rs = (ResultSet) cs.getObject(6);System.out.println("总记录数:"+rowNum);System.out.println("总页数:"+pageCounts);while(rs.next()){System.out.println("编号:"+rs.getInt("empno")+" \t姓名:"+rs.getString("ename")+" \t薪水:"+rs.getDouble("sal"));//写1或2也可以}}catch(Exception e){e.printStackTrace();}finally{if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}}

测试结果如下（未关闭游标）：

总记录数:14
总页数:3
编号:7654 姓名:MARTIN 薪水:1250.0
编号:7934 姓名:MILLER 薪水:1300.0
编号:7844 姓名:TURNER 薪水:1500.0
编号:7499 姓名:ALLEN 薪水:1600.0
编号:7782 姓名:CLARK 薪水:2450.0