Oracle-10g数据库基础教程第17章基于Oracle数据库的应用开发.ppt

第17 基于Oracle数据库的应用开发,本章内容,利用PL/SQL程序实现分页查询 基于Oracle数据库的人事管理系统开发,本章要求,掌握利用PL/SQL程序实现分页查询 掌握利用JDBC连接数据库的方法 了解基于数据库的应用系统开发过程 了解人事管理系统部分核心代码的实现,17.1 利用PL/SQL实现分页查询,利用集合实现分页查询 利用游标变量实现分页查询 分页查询在Java开发中的应用,17.1.1 利用集合实现分页查询,如果要对特定结构的结果集进行分页查询，则可以使用集合实现。 利用分页查询实现对员工工资名次、员工号、员工名、工资和部门号的查询。,CREATE OR REPLACE PACKAGE emppkg IS -定义结果集中每条记录的类型 TYPE t_record IS RECORD( rn INT, empno emp.empno%TYPE, ename emp.ename%TYPE, sal emp.sal%TYPE, deptno emp.deptno%TYPE); -定义保存返回结果集的集合类型 TYPE emplist IS TABLE OF t_record; -定义个实现分页显示的过程 PROCEDURE sp_page( p_pageSize IN OUT INT, -每页输出的记录数 p_curPageNo IN OUT INT, -当前页码 p_outCollection OUT emplist -查询返回当前页数据的集合 ); END;,CREATE OR REPLACE PACKAGE BODY emppkg IS PROCEDURE sp_page( p_pageSize IN OUT INT, -每页输出的记录数 p_curPageNo IN OUT INT, -当前页码 p_outCollection OUT emplist) -查询返回当前页数据的集合 AS v_startRownum NUMBER; v_endRownum NUMBER; v_totalRecords NUMBER; v_totalPages NUMBER; indexValue BINARY_INTEGER :=1; BEGIN -查询记录总数 SELECT count(*) INTO v_totalRecords FROM emp; -验证页面记录数量 IF p_pageSize 0 THEN p_pageSize := 10; END IF;,-计算总页数 IF MOD(v_totalRecords,p_pageSize)=0 THEN v_totalPages:=v_totalRecords/p_pageSize; ELSE v_totalPages:=floor(v_totalRecords/p_pageSize)+1; END IF; -验证页号 IF p_curPageNo v_totalPages THEN p_curPageNo :=v_totalPages; END IF;,-执行分页查询 v_startRownum := (p_curPageNo-1)*p_pageSize + 1; v_endRownum := p_curPageNo * p_pageSize; p_outCollection:=emplist(); FOR v_emp IN (SELECT * FROM ( SELECT ROWNUM rn,empno,ename,sal,deptno FROM (SELECT empno,ename,sal,deptno FROM emp WHERE sal IS NOT NULL ORDER BY sal) A WHERE ROWNUM=v_startRownum ) LOOP p_outCollection.extend; p_outCollection(indexValue):=v_emp; indexValue:=indexValue+1; END LOOP; END sp_page; END emppkg;,通过对包中分页查询过程的调用，可以根据需要输出特定页的数据。 DECLARE v_pageSize INT :=5; v_curPageNo INT:=2; v_emplist emppkg.emplist; BEGIN emppkg.sp_page(v_pageSize,v_curPageNo,v_emplist); DBMS_OUTPUT.PUT_LINE(The pagesize is :|v_pageSize); DBMS_OUTPUT.PUT_LINE(The current page NO. is :| v_curPageNo); FOR i IN 1v_emplist.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_emplist(i).rn| | v_emplist(i).empno| | v_emplist(i).ename); END LOOP; END;,执行结果为 The pagesize is :5 The current page NO. is :2 6 7521 WARD 7 7654 MARTIN 8 7499 ALLEN 9 2 WANG 10 7782 CLARK,17.1.2 利用游标变量实现分页查询,如果要对任意查询实现分页，则可以使用游标变量来实现。,CREATE OR REPLACE PACKAGE curspkg IS TYPE refCursorType IS REF CURSOR; PROCEDURE sp_page( p_pageSize IN OUT INT, -每页输出的记录数 p_curPageNo IN OUT INT, -当前页码 p_sqlSelect VARCHAR2, -查询语句,含排序部分 p_totalPages OUT INT, -返回总页数 p_totalRecords OUT INT, -返回总记录数 p_outCursor OUT refCursorType -查询返回当前页的数据 ); END;,CREATE OR REPLACE PACKAGE BODY CURSPKG IS PROCEDURE sp_page( p_pageSize IN OUT INT, p_curPageNo IN OUT INT, p_sqlSelect VARCHAR2, p_totalPages OUT INT, p_totalRecords OUT INT, p_outCursor OUT refCursorType) IS v_countSql VARCHAR2(1000); v_startRownum INT; v_endRownum INT; v_sql VARCHAR2(4000); BEGIN v_countSql:= SELECT to_char(count(*)FROM(|p_sqlSelect|); EXECUTE IMMEDIATE v_countSql INTO p_totalRecords;,IF p_pageSize p_totalPages THEN p_curPageNo := p_totalPages; END IF;,v_startRownum := p_curPageNo * p_pageSize; v_endRownum := (p_curPageNo-1)*p_pageSize + 1; v_sql := SELECT * FROM ( SELECT ROWNUM rn ,A.* FROM ( | p_sqlSelect | ) A WHERE rownum = | to_char(v_endRownum); OPEN p_outCursor FOR v_sql; END sp_page; END;,SQLVARIABLE v_cursor REFCURSOR SQLDECLARE v_pageSize NUMBER :=10; v_curPageNo NUMBER:=2; v_sql VARCHAR2(1000); v_totalPages NUMBER; v_totalRecordCount NUMBER; BEGIN v_sql:= SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal; curspkg.sp_page(v_pageSize,v_curPageNo,v_sql,v_totalPages, v_totalRecordCount,:v_cursor); DBMS_OUTPUT.PUT_LINE(The pagesize is :|v_pageSize); DBMS_OUTPUT.PUT_LINE( The current page NO. is :|v_curPageNo); DBMS_OUTPUT.PUT_LINE( The totalPages is :|v_totalPages); DBMS_OUTPUT.PUT_LINE( The totalRecords is :| v_totalRecordCount); END;,执行结果为： The pagesize is :10 The current page NO. is :2 The totalPages is :2 The totalRecordCount is :16 SQL PRINT v_cursor RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO - 11 7698 BLAKE MANAGER 7839 01-5月 -81 3150 30 12 7566 JONES MANAGER 7839 02-4月 -81 3225 20 13 7788 SCOTT ANALYST 7566 19-4月 -87 3250 20 14 7902 FORD ANALYST 7566 03-12月-81 3250 20 15 7839 KING PRESIDENT 17-11月-81 5200 10 16 7844 TURNER SALESMAN 7698 08-9月 -81 6300 0 30,17.1.3 分页查询在Java开发中的应用,import java.sql.*; import oracle.jdbc.driver.*; public class Oracle public static void main(String args) Connection conn = null; CallableStatement proc = null; ResultSet rs = null; try Class.forName(“oracle.jdbc.driver.OracleDriver“); conn=DriverManager.getConnection( “jdbc:oracle:thin: 0: 1521:orcl“, “scott“,“tiger“); String sql = “call CURSPKG.SP_PAGE(?,?,?,?,?,?)“;,proc = conn.prepareCall(sql); proc.setInt(1, 10); proc.registerOutParameter(1,OracleTypes.INTEGER); proc.setInt(2, 2)； proc.registerOutParameter(2,OracleTypes.INTEGER)； proc.setString(3, “select * from emp order by sal“)； proc.registerOutParameter(4, OracleTypes.INTEGER)； proc.registerOutParameter(5, OracleTypes.INTEGER)； proc.registerOutParameter(6, OracleTypes.CURSOR)； proc.execute()； int pages=(OracleCallableStatement)proc).getInt(4)； int rows=(OracleCallableStatement)proc).getInt(5)； rs=(OracleCallableStatement)proc).getCursor(6)； int pages=(OracleCallableStatement)proc).getInt(4)； int rows=(OracleCallableStatement)proc).getInt(5)； rs=(OracleCallableStatement)proc).getCursor(6)；,while(rs.next() System.out.println(rs.getString(1)+“|“+rs.getString(2)+“|“+ rs.getString(3)+“|“+rs.getString(4)+“|“+rs.getString(5)+“|“+ rs.getString(6)+“|“+rs.getString(7)； catch(Exception e) e.printStackTrace()； finally try rs.close()； proc.close()； conn.close()； catch (SQLException e) e.printStackTrace()； ,17.2 基于Oracle数据库的人事管理系统开发,人事管理系统介绍 数据库设计 重要界面的设计与实现 主要代码的实现,人事管理系统介绍,功能：录入人事的基本资料，在操作上能够完成诸如添加、修改、删除、按各种条件进行查询、新用户的设置及密码修改等方面的工作，基本满足人事日常业务的需要。 实用的/S结构，后台Oracle数据库,17.2.1 数据库设计,员工基本信息表 员工基本信息表主要描述员工的个人情况，如姓名、性别、出生日期、身份证号、所属部门等，其中员工编号作为员工基本信息表的主码。 员工工资信息表 员工工资信息表主要描述每个员工所对应的工资情况，如工资编号、基本工资、岗位工资、出勤费等，其中工资编号作为员工工资信息表的主码。而其中的员工编号是外码，它的取值参照于员工基本信息表的主码取值。同时，每名员工均只有一个工资编号和一个员工编号，即员工实体与员工工资实体之间是一对一的联系。,员工请假信息表 员工请假信息表主要描述每个员工所对应的请假情况，如请假总天数、请假开始时间、请假结束时间、请假原因等，其中请假编号作为员工请假信息表的主码。而其中的员工编号是外码，它参照于员工基本信息表的主码取值。同时，每名员工可以有多次请假记录，即员工实体