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

《Oracle-10g数据库基础教程第17章基于Oracle数据库的应用开发.ppt》由会员分享，可在线阅读，更多相关《Oracle-10g数据库基础教程第17章基于Oracle数据库的应用开发.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle10g 数据库基础教程2009第第17 基于基于Oracle数据库的应数据库的应用开发用开发Oracle10g 数据库基础教程2009本章内容本章内容p利用PL/SQL程序实现分页查询 p基于Oracle数据库的人事管理系统开发 Oracle10g 数据库基础教程2009本章要求本章要求p掌握利用PL/SQL程序实现分页查询 p掌握利用JDBC连接数据库的方法p了解基于数据库的应用系统开发过程p了解人事管理系统部分核心代码的实现Oracle10g 数据库基础教程200917.1 利用利用PL/SQL实现分页查询实现分页查询p利用集合实现分页查询p利用游标变量实现分页查询p分页查询在

2、Java开发中的应用Oracle10g 数据库基础教程200917.1.1 利用集合实现分页查询利用集合实现分页查询p如果要对特定结构的结果集进行分页查询，则可以使用集合实现。p利用分页查询实现对员工工资名次、员工号、员工名、工资和部门号的查询。Oracle10g 数据库基础教程2009pCREATE OR REPLACE PACKAGE emppkg ISp -定义结果集中每条记录的类型p TYPE t_record IS RECORD(p rn INT,p empno emp.empno%TYPE,p ename emp.ename%TYPE,p sal emp.sal%TYPE,p de

3、ptno emp.deptno%TYPE);p -定义保存返回结果集的集合类型p TYPE emplist IS TABLE OF t_record;p -定义个实现分页显示的过程p PROCEDURE sp_page(p p_pageSize IN OUT INT,-每页输出的记录数p p_curPageNo IN OUT INT,-当前页码p p_outCollection OUT emplist -查询返回当前页数据的集合p );pEND;Oracle10g 数据库基础教程2009pCREATE OR REPLACE PACKAGE BODY emppkg ISp PROCEDURE s

4、p_page(p p_pageSize IN OUT INT,-每页输出的记录数p p_curPageNo IN OUT INT,-当前页码p p_outCollection OUT emplist)-查询返回当前页数据的集合p ASp v_startRownum NUMBER;p v_endRownum NUMBER;p v_totalRecords NUMBER;p v_totalPages NUMBER;p indexValue BINARY_INTEGER:=1;p BEGINp -查询记录总数p SELECT count(*)INTO v_totalRecords FROM emp;

5、p -验证页面记录数量p IF p_pageSize 0 THENp p_pageSize:=10;p END IF;Oracle10g 数据库基础教程2009p -计算总页数p IF MOD(v_totalRecords,p_pageSize)=0 THENp v_totalPages:=v_totalRecords/p_pageSize;p ELSEp v_totalPages:=floor(v_totalRecords/p_pageSize)+1;p END IF;p -验证页号p IF p_curPageNo v_totalPages THENp p_curPageNo:=v_tota

6、lPages;p END IF;Oracle10g 数据库基础教程2009p-执行分页查询 p v_startRownum:=(p_curPageNo-1)*p_pageSize+1;p v_endRownum:=p_curPageNo*p_pageSize;p p_outCollection:=emplist();p FOR v_emp IN(SELECT*FROM (p SELECT ROWNUM rn,empno,ename,sal,deptno FROM p (SELECT empno,ename,sal,deptno FROM emp p WHERE sal IS NOT NULL

7、ORDER BY sal)A p WHERE ROWNUM=v_startRownum)LOOPp p_outCollection.extend;p p_outCollection(indexValue):=v_emp;p indexValue:=indexValue+1;p END LOOP;p END sp_page;pEND emppkg;Oracle10g 数据库基础教程2009p通过对包中分页查询过程的调用，可以根据需要输出特定页的数据。pDECLARE p v_pageSize INT:=5;p v_curPageNo INT:=2;p v_emplist emppkg.empli

8、st;pBEGINp emppkg.sp_page(v_pageSize,v_curPageNo,v_emplist);p DBMS_OUTPUT.PUT_LINE(The pagesize is:|v_pageSize);p DBMS_OUTPUT.PUT_LINE(The current page NO.is:|p v_curPageNo);p FOR i IN 1.v_emplist.COUNT LOOPp DBMS_OUTPUT.PUT_LINE(v_emplist(i).rn|p v_emplist(i).empno|p v_emplist(i).ename);p END LOOP;

9、pEND;Oracle10g 数据库基础教程2009p执行结果为nThe pagesize is:5nThe current page NO.is:2n6 7521 WARDn7 7654 MARTINn8 7499 ALLENn9 2 WANGn10 7782 CLARKOracle10g 数据库基础教程200917.1.2 利用游标变量实现分页查询利用游标变量实现分页查询p如果要对任意查询实现分页，则可以使用游标变量来实现。Oracle10g 数据库基础教程2009pCREATE OR REPLACE PACKAGE curspkg ISp TYPE refCursorType IS RE

10、F CURSOR;p PROCEDURE sp_page(p p_pageSize IN OUT INT,-每页输出的记录数p p_curPageNo IN OUT INT,-当前页码p p_sqlSelect VARCHAR2,-查询语句,含排序部分p p_totalPages OUT INT,-返回总页数p p_totalRecords OUT INT,-返回总记录数p p_outCursor OUT refCursorTypep -查询返回当前页的数据p );p END;Oracle10g 数据库基础教程2009pCREATE OR REPLACE PACKAGE BODY CURSPK

11、G ISp PROCEDURE sp_page(p p_pageSize IN OUT INT,p p_curPageNo IN OUT INT,p p_sqlSelect VARCHAR2,p p_totalPages OUT INT,p p_totalRecords OUT INT,p p_outCursor OUT refCursorType)p ISp v_countSql VARCHAR2(1000);p v_startRownum INT;p v_endRownum INT;p v_sql VARCHAR2(4000);p BEGINp v_countSql:=p SELECT t

12、o_char(count(*)FROM(|p_sqlSelect|);p EXECUTE IMMEDIATE v_countSql INTO p_totalRecords;Oracle10g 数据库基础教程2009p IF p_pageSize 0 THENp p_pageSize:=10;p END IF;p IF MOD(p_totalRecords,p_pageSize)=0 THENp p_totalPages:=p_totalRecords/p_pageSize;p ELSEp p_totalPages:=floor(p_totalRecords/p_pageSize)+1;p EN

13、D IF;p IF p_curPageNo p_totalPages THENp p_curPageNo:=p_totalPages;p END IF;Oracle10g 数据库基础教程2009p v_startRownum:=p_curPageNo*p_pageSize;p v_endRownum:=(p_curPageNo-1)*p_pageSize+1;p v_sql:=SELECT*FROM(p SELECT ROWNUM rn,A.*FROM(|p_sqlSelect|)A p WHERE rownum=|to_char(v_endRownum);p OPEN p_outCursor

14、 FOR v_sql;p END sp_page;END;Oracle10g 数据库基础教程2009pSQLVARIABLE v_cursor REFCURSORpSQLDECLAREp v_pageSize NUMBER:=10;p v_curPageNo NUMBER:=2;p v_sql VARCHAR2(1000);p v_totalPages NUMBER;p v_totalRecordCount NUMBER;p BEGINp v_sql:=pSELECT*FROM emp WHERE sal IS NOT NULL ORDER BY sal;pcurspkg.sp_page(v_

15、pageSize,v_curPageNo,v_sql,v_totalPages,p v_totalRecordCount,:v_cursor);p DBMS_OUTPUT.PUT_LINE(The pagesize is:|v_pageSize);p DBMS_OUTPUT.PUT_LINE(p The current page NO.is:|v_curPageNo);p DBMS_OUTPUT.PUT_LINE(p The totalPages is:|v_totalPages);p DBMS_OUTPUT.PUT_LINE(p The totalRecords is:|v_totalRec

16、ordCount);p END;Oracle10g 数据库基础教程2009p执行结果为：pThe pagesize is:10pThe current page NO.is:2pThe totalPages is:2pThe totalRecordCount is:16pSQL PRINT v_cursorpRN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNOp-p11 7698 BLAKE MANAGER 7839 01-5月-81 3150 30 p12 7566 JONES MANAGER 7839 02-4月-81 3225 20p13 778

17、8 SCOTT ANALYST 7566 19-4月-87 3250 20p14 7902 FORD ANALYST 7566 03-12月-81 3250 20p15 7839 KING PRESIDENT 17-11月-81 5200 10p16 7844 TURNER SALESMAN 7698 08-9月-81 6300 0 30 Oracle10g 数据库基础教程200917.1.3 分页查询在分页查询在Java开发中的应用开发中的应用pimport java.sql.*;pimport.*;ppublic class Oraclepp public static void main

18、(String args)p Connection conn=null;p CallableStatement proc=null;p ResultSet rs=null;p tryp p);p conn=DriverManager.getConnection(pjdbc:oracle:thin:172.23.5.60:1521:orcl,scott,tiger);p String sql=call CURSPKG.SP_PAGE(?,?,?,?,?,?);Oracle10g 数据库基础教程2009p proc=conn.prepareCall(sql);p proc.setInt(1,10)

19、;p proc.registerOutParameter(1,OracleTypes.INTEGER);p proc.setInt(2,2)；p proc.registerOutParameter(2,OracleTypes.INTEGER)；p proc.setString(3,select*from emp order by sal)；p proc.registerOutParameter(4,OracleTypes.INTEGER)；p proc.registerOutParameter(5,OracleTypes.INTEGER)；p proc.registerOutParameter

20、(6,OracleTypes.CURSOR)；p proc.execute()；p int pages=(OracleCallableStatement)proc).getInt(4)；p int rows=(OracleCallableStatement)proc).getInt(5)；p rs=(OracleCallableStatement)proc).getCursor(6)；p int pages=(OracleCallableStatement)proc).getInt(4)；p int rows=(OracleCallableStatement)proc).getInt(5)；p

21、 rs=(OracleCallableStatement)proc).getCursor(6)；Oracle10g 数据库基础教程2009pwhile(rs.next()pSystem.out.println(rs.getString(1)+|+rs.getString(2)+|+prs.getString(3)+|+rs.getString(4)+|+rs.getString(5)+|+prs.getString(6)+|+rs.getString(7)；ppcatch(Exception e)e.printStackTrace()；pfinallyp try rs.close()；p pr

22、oc.close()；p conn.close()；p catch(SQLException e)e.printStackTrace()；p p p Oracle10g 数据库基础教程200917.2 基于基于Oracle数据库的人事管理数据库的人事管理系统开发系统开发p人事管理系统介绍p数据库设计p重要界面的设计与实现p主要代码的实现Oracle10g 数据库基础教程2009人事管理系统介绍人事管理系统介绍p功能：录入人事的基本资料，在操作上能够完成诸如添加、修改、删除、按各种条件进行查询、新用户的设置及密码修改等方面的工作，基本满足人事日常业务的需要。p实用的/S结构，后台Oracle数据

23、库Oracle10g 数据库基础教程200917.2.1 数据库设计数据库设计员工员工编号姓名性别出生日期身份证号员工请假请假天数员工工资拥有1工资编号员工编号基本工资岗位工资出勤费拥有所属部门管理员编号姓名密码。.管理管理管理11请假编号q111mnp员工编号Oracle10g 数据库基础教程2009p员工基本信息表 n员工基本信息表主要描述员工的个人情况，如姓名、员工基本信息表主要描述员工的个人情况，如姓名、性别、出生日期、身份证号、所属部门等，其中员工性别、出生日期、身份证号、所属部门等，其中员工编号作为员工基本信息表的主码。编号作为员工基本信息表的主码。p员工工资信息表n员工工资信息表

24、主要描述每个员工所对应的工资情况，员工工资信息表主要描述每个员工所对应的工资情况，如工资编号、基本工资、岗位工资、出勤费等，其中如工资编号、基本工资、岗位工资、出勤费等，其中工资编号作为员工工资信息表的主码。而其中的员工工资编号作为员工工资信息表的主码。而其中的员工编号是外码，它的取值参照于员工基本信息表的主码编号是外码，它的取值参照于员工基本信息表的主码取值。同时，每名员工均只有一个工资编号和一个员取值。同时，每名员工均只有一个工资编号和一个员工编号，即员工实体与员工工资实体之间是一对一的工编号，即员工实体与员工工资实体之间是一对一的联系。联系。Oracle10g 数据库基础教程2009p员

25、工请假信息表n员工请假信息表主要描述每个员工所对应的请假情况，员工请假信息表主要描述每个员工所对应的请假情况，如请假总天数、请假开始时间、请假结束时间、请假如请假总天数、请假开始时间、请假结束时间、请假原因等，其中请假编号作为员工请假信息表的主码。原因等，其中请假编号作为员工请假信息表的主码。而其中的员工编号是外码，它参照于员工基本信息表而其中的员工编号是外码，它参照于员工基本信息表的主码取值。同时，每名员工可以有多次请假记录，的主码取值。同时，每名员工可以有多次请假记录，即员工实体与员工请假实体之间是一对多的联系。即员工实体与员工请假实体之间是一对多的联系。p管理员表n管理员表主要描述本系统

26、中的管理员账户情况，包括管理员表主要描述本系统中的管理员账户情况，包括编号、管理员名、密码，其中编号作为管理员表的主编号、管理员名、密码，其中编号作为管理员表的主码。码。Oracle10g 数据库基础教程2009p员工基本信息表（emp）字 段 名名 称类 型字 段 名名 称类 型empnum员工编号VARCHAR2(16)address地址VARCHAR2(40)empname姓名VARCHAR2(16)policy政治面貌NUMBERSex性别NUMBERphone电话VARCHAR2(16)birthday出生日期DATEdegree学历NUMBERnation民族VARCHAR2(10

27、)college毕业院校VARCHAR2(40)nativeplace户籍VARCHAR2(40)duty职务VARCHAR2(16)Ident身份证号VARCHAR2(16)title职称VARCHAR2(16)department所属部门VARCHAR2(16)sort在职类别NUMBERmarriage婚姻状况NUMBERremark备注VARCHAR2(400)Oracle10g 数据库基础教程2009p员工工资信息表（pay）字 段 名名 称类 型字 段 名名 称类 型Id工资编号NUMBERtax个人所得税NUMBERempnum员工编号VARCHAR2(16)insure_shi

28、ye失业保险NUMBERbasepay基本工资NUMBERinsure_yanglao养老保险NUMBERPost岗位工资NUMBERinsure_yiliao医疗保险NUMBERworkprice出勤费NUMBERshouldpay应发工资NUMBERMess伙食补贴NUMBERshoulddeduct应扣工资NUMBERtraffic交通补贴NUMBERpay实发工资NUMBERPrice物价补贴NUMBEROracle10g 数据库基础教程2009p员工请假信息表leave）字 段 名名 称类 型字 段 名名 称类 型Id请假编号NUMBERleavepass请假批准人VARCHAR2(

29、16)empnum员工编号CHAR(16)reason请假原因VARCHAR2(400)startdate请假开始时间DATEapplydate申请日期DATEenddate请假终止时间DATEcanceldate销假日期DATEalldate请假总天数NUMBEROracle10g 数据库基础教程2009p管理员表（admin）字 段 名约 束名 称类 型id主码编号NUMBERusernameNOT NULL管理员名VARCHAR2(16)passwordNOT NULL密码VARCHAR2(16)Oracle10g 数据库基础教程200917.2.2 重要界面的设计与实现重要界面的设计与

30、实现p管理员登录界面p增加人员基本信息p查询人员基本信息p员工请假信息p员工工资信息维护与管理Oracle10g 数据库基础教程2009p 管理员登录界面 Oracle10g 数据库基础教程2009p人员信息添加界面 Oracle10g 数据库基础教程2009p员工信息查询界面 Oracle10g 数据库基础教程2009p员工请假信息添加界面 Oracle10g 数据库基础教程2009p员工工资信息管理界面 Oracle10g 数据库基础教程200917.2.3 主要代码的实现主要代码的实现p系统采用JDBC的数据库连接 ntryn).nnewInstance();n/加载数据库连接的驱动程序

31、加载数据库连接的驱动程序nString url=jdbc:oracle:thin:localhost:1521:orcl;n/连接的字符串，其中连接的字符串，其中orcl为你的数据库的为你的数据库的SIDnString user=scott;nString password=tiger;nConnection conn=DriverManager.getConnection(url,user,password);n/与数据库建立连接与数据库建立连接nStatement stmt=conn.createStatement();n/产生产生Statement对象，用于执行对象，用于执行SQL语句语句n Oracle10g 数据库基础教程2009p员工工资信息管理代码p显示员工信息p添加员工的个人工资信息p处理个人工资信息的添加Oracle10g 数据库基础教程2009总结总结p分页查询的实现p数据库连接的实现p基于Oracle数据库的应用程序开发过程