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");
			//创建CallableStatement
			CallableStatement 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