基于Oracle数据库的应用开发

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

3、 deptno 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 PROCE

4、DURE sp_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_totalRecor

5、ds FROM emp;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_cur

6、PageNo :=v_totalPages;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 e

7、mp p WHERE sal IS NOT NULL 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_curPage

8、No INT:=2; p v_emplist emppkg.emplist; 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(

9、i).empno| |p v_emplist(i).ename);p END LOOP;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 REPL

10、ACE PACKAGE curspkg ISp TYPE refCursorType IS REF 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; Oracl

11、e10g 数据库基础教程2009pCREATE OR REPLACE PACKAGE BODY CURSPKG 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

12、INT;p v_sql VARCHAR2(4000);p BEGINp v_countSql:=p SELECT to_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_pageS

13、ize;p ELSEp p_totalPages := floor(p_totalRecords/p_pageSize)+ 1;p END 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 ROW

14、NUM rn ,A.* FROM ( | p_sqlSelect | ) A p WHERE rownum = | to_char(v_endRownum);p OPEN p_outCursor 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_totalRecord

15、Count NUMBER;p BEGINp v_sql:=pSELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal; pcurspkg.sp_page(v_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_O

16、UTPUT.PUT_LINE(p The totalPages is :|v_totalPages);p DBMS_OUTPUT.PUT_LINE(p The totalRecords is :| v_totalRecordCount);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 HIREDAT

17、E SAL COMM DEPTNOp-p11 7698 BLAKE MANAGER 7839 01-5月 -81 3150 30 p12 7566 JONES MANAGER 7839 02-4月 -81 3225 20p13 7788 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

18、数据库基础教程200917.1.3 分页查询在分页查询在Java开发中的应用开发中的应用pimport java.sql.*;pimport oracle.jdbc.driver.*;ppublic class Oraclepp public static void main(String args)p Connection conn = null;p CallableStatement proc = null;p ResultSet rs = null;p tryp p Class.forName(oracle.jdbc.driver.OracleDriver);p conn=DriverM

19、anager.getConnection(pjdbc:oracle:thin: 0: 1521:orcl, scott,tiger);p String sql = call CURSPKG.SP_PAGE(?,?,?,?,?,?); Oracle10g 数据库基础教程2009p proc = conn.prepareCall(sql);p proc.setInt(1, 10); p proc.registerOutParameter(1,OracleTypes.INTEGER);p proc.setInt(2, 2)；p proc.registerOutParameter(

20、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(6, OracleTypes.CURSOR)；p proc.execute()； p int pages=(OracleCallableStatement)proc).getInt(4

21、)；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 rs=(OracleCallableStatement)proc).getCursor(6)； Oracle10g 数据库基础教程2009pwhile(rs.next() pSys

22、tem.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 proc.close()； p conn.close()；p catch (SQLException e) e.printStackTrace()；p p p Oracle10

23、g 数据库基础教程200917.2 基于基于Oracle数据库的人事管理数据库的人事管理系统开发系统开发p人事管理系统介绍p数据库设计p重要界面的设计与实现p主要代码的实现Oracle10g 数据库基础教程2009人事管理系统介绍人事管理系统介绍p功能：录入人事的基本资料，在操作上能够完成诸如添加、修改、删除、按各种条件进行查询、新用户的设置及密码修改等方面的工作，基本满足人事日常业务的需要。p实用的/S结构，后台Oracle数据库Oracle10g 数据库基础教程200917.2.1 数据库设计数据库设计员工员工编号姓名性别出生日期身份证号员工请假请假天数员工工资拥有1工资编号员工编号基本工

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

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

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

27、表的主码。码。 Oracle10g 数据库基础教程2009p员工基本信息表（emp）字 段 名名 称类 型字 段 名名 称类 型empnum员工编号VARCHAR2(16)address地址VARCHAR2(40)empname姓名VARCHAR2(16)policy政治面貌NUMBERSex性别NUMBERphone电话VARCHAR2(16)birthday出生日期DATEdegree学历NUMBERnation民族VARCHAR2(10)college毕业院校VARCHAR2(40)nativeplace户籍VARCHAR2(40)duty职务VARCHAR2(16)Ident身份证号V

28、ARCHAR2(16)title职称VARCHAR2(16)department所属部门VARCHAR2(16)sort在职类别NUMBERmarriage婚姻状况NUMBERremark备注VARCHAR2(400)Oracle10g 数据库基础教程2009p员工工资信息表（pay） 字 段 名名 称类 型字 段 名名 称类 型Id工资编号NUMBERtax个人所得税NUMBERempnum员工编号VARCHAR2(16)insure_shiye失业保险NUMBERbasepay基本工资NUMBERinsure_yanglao养老保险NUMBERPost岗位工资NUMBERinsure_yi

29、liao医疗保险NUMBERworkprice出勤费NUMBERshouldpay应发工资NUMBERMess伙食补贴NUMBERshoulddeduct应扣工资NUMBERtraffic交通补贴NUMBERpay实发工资NUMBERPrice物价补贴NUMBEROracle10g 数据库基础教程2009p员工请假信息表leave） 字 段 名名 称类 型字 段 名名 称类 型Id请假编号NUMBERleavepass请假批准人VARCHAR2(16)empnum员工编号CHAR(16)reason请假原因VARCHAR2(400)startdate请假开始时间DATEapplydate申请日期DATEenddate请假终止时间DATEcanceldate销