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 1.v_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:5The current page NO.is:26 7521 WARD7 7654 MARTIN8 7499 ALLEN9 2 WANG10 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 REFCURSORSQLDECLARE 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:10The current page NO.is:2The totalPages is:2The totalRecordCount is:16SQL PRINT v_cursorRN 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 2013 7788 SCOTT ANALYST 7566 19-4月-87 3250 2014 7902 FORD ANALYST 7566 03-12月-81 3250 2015 7839 KING PRESIDENT 17-11月-81 5200 1016 7844 TURNER SALESMAN 7698 08-9月-81 6300 0 30,17.1.3 分页查询在Java开发中的应用,import java.sql.*;import.*;public class Oracle public static void main(String args)Connection conn=null;CallableStatement proc=null;ResultSet rs=null;try);conn=DriverManager.getConnection(jdbc:oracle:thin:172.23.5.60: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 数据库设计,员工基本信息表 员工基本信息表主要描述员工的个人情况，如姓名、性别、出生日期、身份证号、所属部门等，其中员工编号作为员工基本信息表的主码。员工工资信息表员工工资信息表主要描述每个员工所对应的工资情况，如工资编号、基本工资、岗位工资、出勤费等，其中工资编号作为员工工资信息表的主码。而其中的员工编号是外码，它的取值参照于员工基本信息表的主码取值。同时，每名员工均只有一个工资编号和一个员工编号，即员工实体与员工工资实体之间是一对一的联系。,员工请假信息表员工请假信息表主要描述每个员工所对应的请假情况，如请假总天数、请假开始时间、请假结束时间、请假原因等，其中请假编号作为员工请假信息表的主码。而其中的员工编号是外码，它参照于员工基本信息表的主码取值。同时，每名员工可以有多次请假记录，即员工实体与员工请假实体之间是一对多的联系。管理员表管理员表主要描述本系统中的管理员账户情况，包括编号、管理员名、密码，其中编号作为管理员表的主码。,员工基本信息表（emp）,员工工资信息表（pay）,员工请假信息表leave）,管理员表（admin）,17.2.2 重要界面的设计与实现,管理员登录界面增加人员基本信息查询人员基本信息员工请假信息员工工资信息维护与管理,管理员登录界面,人员信息添加界面,员工信息查询界面,员工请假信息添加界面,员工工资信息管理界面,17.2.3 主要代码的实现,系统采用JDBC的数据库连接 try).newInstance();/加载数据库连接的驱动程序String url=jdbc:oracle:thin:localhost:1521:orcl;/连接的字符串，其中orcl为你的数据库的SIDString user=scott;String password=tiger;Connection conn=DriverManager.getConnection(url,user,password);/与数据库建立连接Statement stmt=conn.createStatement();/产生Statement对象，用于执行SQL语句,员工工资信息管理代码显示员工信息添加员工的个人工资信息处理个人工资信息的添加,总结,分页查询的实现数据库连接的实现基于Oracle数据库的应用程序开发过程,
