基于Oracle數(shù)據(jù)庫(kù)的應(yīng)用開(kāi)發(fā)

1、Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009第第17 基于基于Oracle數(shù)據(jù)庫(kù)的應(yīng)數(shù)據(jù)庫(kù)的應(yīng)用開(kāi)發(fā)用開(kāi)發(fā)Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009本章內(nèi)容本章內(nèi)容p利用PL/SQL程序?qū)崿F(xiàn)分頁(yè)查詢(xún) p基于Oracle數(shù)據(jù)庫(kù)的人事管理系統(tǒng)開(kāi)發(fā) Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009本章要求本章要求p掌握利用PL/SQL程序?qū)崿F(xiàn)分頁(yè)查詢(xún) p掌握利用JDBC連接數(shù)據(jù)庫(kù)的方法p了解基于數(shù)據(jù)庫(kù)的應(yīng)用系統(tǒng)開(kāi)發(fā)過(guò)程p了解人事管理系統(tǒng)部分核心代碼的實(shí)現(xiàn)Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程200917.1 利用利用PL/SQL實(shí)現(xiàn)分頁(yè)查詢(xún)實(shí)現(xiàn)分頁(yè)查詢(xún)p利用集合實(shí)現(xiàn)分頁(yè)查詢(xún)p利用游標(biāo)變量實(shí)現(xiàn)分頁(yè)查詢(xún)p分頁(yè)查詢(xún)?cè)?/p>

2、Java開(kāi)發(fā)中的應(yīng)用Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程200917.1.1 利用集合實(shí)現(xiàn)分頁(yè)查詢(xún)利用集合實(shí)現(xiàn)分頁(yè)查詢(xún)p如果要對(duì)特定結(jié)構(gòu)的結(jié)果集進(jìn)行分頁(yè)查詢(xún)，則可以使用集合實(shí)現(xiàn)。 p利用分頁(yè)查詢(xún)實(shí)現(xiàn)對(duì)員工工資名次、員工號(hào)、員工名、工資和部門(mén)號(hào)的查詢(xún)。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009pCREATE OR REPLACE PACKAGE emppkg ISp -定義結(jié)果集中每條記錄的類(lèi)型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 -定義保存返回結(jié)果集的集合類(lèi)型p TYPE emplist IS TABLE OF t_record; p -定義個(gè)實(shí)現(xiàn)分頁(yè)顯示的過(guò)程p PROCEDURE sp_page(p p_pageSize IN OUT INT, -每頁(yè)輸出的記錄數(shù)p p_curPageNo IN OUT INT, -當(dāng)前頁(yè)碼p p_outCollection OUT emplist -查詢(xún)返回當(dāng)前頁(yè)數(shù)據(jù)的集合p );pEND;Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009pCREATE OR REPLACE PACKAGE BODY emppkg ISp PROCE

4、DURE sp_page(p p_pageSize IN OUT INT, -每頁(yè)輸出的記錄數(shù)p p_curPageNo IN OUT INT, -當(dāng)前頁(yè)碼p p_outCollection OUT emplist) -查詢(xún)返回當(dāng)前頁(yè)數(shù)據(jù)的集合p ASp v_startRownum NUMBER;p v_endRownum NUMBER;p v_totalRecords NUMBER;p v_totalPages NUMBER; p indexValue BINARY_INTEGER :=1;p BEGINp -查詢(xún)記錄總數(shù)p SELECT count(*) INTO v_totalRecor

5、ds FROM emp;p -驗(yàn)證頁(yè)面記錄數(shù)量p IF p_pageSize 0 THENp p_pageSize := 10;p END IF;Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p -計(jì)算總頁(yè)數(shù)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 -驗(yàn)證頁(yè)號(hào)p IF p_curPageNo v_totalPages THENp p_cur

6、PageNo :=v_totalPages;p END IF;Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p-執(zhí)行分頁(yè)查詢(xún) 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 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p通過(guò)對(duì)包中分頁(yè)查詢(xún)過(guò)程的調(diào)用，可以根據(jù)需要輸出特定頁(yè)的數(shù)據(jù)。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 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p執(zhí)行結(jié)果為nThe pagesize is :5nThe current page NO. is :2n6 7521 WARDn7 7654 MARTINn8 7499 ALLENn9 2 WANGn10 7782 CLARKOracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程200917.1.2 利用游標(biāo)變量實(shí)現(xiàn)分頁(yè)查詢(xún)利用游標(biāo)變量實(shí)現(xiàn)分頁(yè)查詢(xún)p如果要對(duì)任意查詢(xún)實(shí)現(xiàn)分頁(yè)，則可以使用游標(biāo)變量來(lái)實(shí)現(xiàn)。Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009pCREATE OR REPL

10、ACE PACKAGE curspkg ISp TYPE refCursorType IS REF CURSOR;p PROCEDURE sp_page(p p_pageSize IN OUT INT, -每頁(yè)輸出的記錄數(shù)p p_curPageNo IN OUT INT, -當(dāng)前頁(yè)碼p p_sqlSelect VARCHAR2, -查詢(xún)語(yǔ)句,含排序部分p p_totalPages OUT INT, -返回總頁(yè)數(shù)p p_totalRecords OUT INT, -返回總記錄數(shù)p p_outCursor OUT refCursorTypep -查詢(xún)返回當(dāng)前頁(yè)的數(shù)據(jù)p );p END; Oracl

11、e10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程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 數(shù)據(jù)庫(kù)基礎(chǔ)教程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 數(shù)據(jù)庫(kù)基礎(chǔ)教程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 數(shù)據(jù)庫(kù)基礎(chǔ)教程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 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p執(zhí)行結(jié)果為：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、數(shù)據(jù)庫(kù)基礎(chǔ)教程200917.1.3 分頁(yè)查詢(xún)?cè)诜猪?yè)查詢(xún)?cè)贘ava開(kāi)發(fā)中的應(yīng)用開(kāi)發(fā)中的應(yīng)用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 數(shù)據(jù)庫(kù)基礎(chǔ)教程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 數(shù)據(jù)庫(kù)基礎(chǔ)教程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 數(shù)據(jù)庫(kù)基礎(chǔ)教程200917.2 基于基于Oracle數(shù)據(jù)庫(kù)的人事管理數(shù)據(jù)庫(kù)的人事管理系統(tǒng)開(kāi)發(fā)系統(tǒng)開(kāi)發(fā)p人事管理系統(tǒng)介紹p數(shù)據(jù)庫(kù)設(shè)計(jì)p重要界面的設(shè)計(jì)與實(shí)現(xiàn)p主要代碼的實(shí)現(xiàn)Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009人事管理系統(tǒng)介紹人事管理系統(tǒng)介紹p功能：錄入人事的基本資料，在操作上能夠完成諸如添加、修改、刪除、按各種條件進(jìn)行查詢(xún)、新用戶(hù)的設(shè)置及密碼修改等方面的工作，基本滿(mǎn)足人事日常業(yè)務(wù)的需要。p實(shí)用的/S結(jié)構(gòu)，后臺(tái)Oracle數(shù)據(jù)庫(kù)Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程200917.2.1 數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)設(shè)計(jì)員工員工編號(hào)姓名性別出生日期身份證號(hào)員工請(qǐng)假請(qǐng)假天數(shù)員工工資擁有1工資編號(hào)員工編號(hào)基本工

24、資崗位工資出勤費(fèi)擁有所屬部門(mén)管理員編號(hào)姓名密碼。 . .管理管理管理11請(qǐng)假編號(hào)q111mnp員工編號(hào)Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p員工基本信息表 n員工基本信息表主要描述員工的個(gè)人情況，如姓名、員工基本信息表主要描述員工的個(gè)人情況，如姓名、性別、出生日期、身份證號(hào)、所屬部門(mén)等，其中員工性別、出生日期、身份證號(hào)、所屬部門(mén)等，其中員工編號(hào)作為員工基本信息表的主碼。編號(hào)作為員工基本信息表的主碼。p員工工資信息表n員工工資信息表主要描述每個(gè)員工所對(duì)應(yīng)的工資情況，員工工資信息表主要描述每個(gè)員工所對(duì)應(yīng)的工資情況，如工資編號(hào)、基本工資、崗位工資、出勤費(fèi)等，其中如工資編號(hào)、基本工資、崗位工資、

25、出勤費(fèi)等，其中工資編號(hào)作為員工工資信息表的主碼。而其中的員工工資編號(hào)作為員工工資信息表的主碼。而其中的員工編號(hào)是外碼，它的取值參照于員工基本信息表的主碼編號(hào)是外碼，它的取值參照于員工基本信息表的主碼取值。同時(shí)，每名員工均只有一個(gè)工資編號(hào)和一個(gè)員取值。同時(shí)，每名員工均只有一個(gè)工資編號(hào)和一個(gè)員工編號(hào)，即員工實(shí)體與員工工資實(shí)體之間是一對(duì)一的工編號(hào)，即員工實(shí)體與員工工資實(shí)體之間是一對(duì)一的聯(lián)系。聯(lián)系。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p員工請(qǐng)假信息表n員工請(qǐng)假信息表主要描述每個(gè)員工所對(duì)應(yīng)的請(qǐng)假情況，員工請(qǐng)假信息表主要描述每個(gè)員工所對(duì)應(yīng)的請(qǐng)假情況，如請(qǐng)假總天數(shù)、請(qǐng)假開(kāi)始時(shí)間、請(qǐng)假結(jié)束時(shí)間、請(qǐng)假如

26、請(qǐng)假總天數(shù)、請(qǐng)假開(kāi)始時(shí)間、請(qǐng)假結(jié)束時(shí)間、請(qǐng)假原因等，其中請(qǐng)假編號(hào)作為員工請(qǐng)假信息表的主碼。原因等，其中請(qǐng)假編號(hào)作為員工請(qǐng)假信息表的主碼。而其中的員工編號(hào)是外碼，它參照于員工基本信息表而其中的員工編號(hào)是外碼，它參照于員工基本信息表的主碼取值。同時(shí)，每名員工可以有多次請(qǐng)假記錄，的主碼取值。同時(shí)，每名員工可以有多次請(qǐng)假記錄，即員工實(shí)體與員工請(qǐng)假實(shí)體之間是一對(duì)多的聯(lián)系。即員工實(shí)體與員工請(qǐng)假實(shí)體之間是一對(duì)多的聯(lián)系。 p管理員表n管理員表主要描述本系統(tǒng)中的管理員賬戶(hù)情況，包括管理員表主要描述本系統(tǒng)中的管理員賬戶(hù)情況，包括編號(hào)、管理員名、密碼，其中編號(hào)作為管理員表的主編號(hào)、管理員名、密碼，其中編號(hào)作為管理員

27、表的主碼。碼。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p員工基本信息表（emp）字 段 名名 稱(chēng)類(lèi) 型字 段 名名 稱(chēng)類(lèi) 型empnum員工編號(hào)VARCHAR2(16)address地址VARCHAR2(40)empname姓名VARCHAR2(16)policy政治面貌NUMBERSex性別NUMBERphone電話(huà)VARCHAR2(16)birthday出生日期DATEdegree學(xué)歷NUMBERnation民族VARCHAR2(10)college畢業(yè)院校VARCHAR2(40)nativeplace戶(hù)籍VARCHAR2(40)duty職務(wù)VARCHAR2(16)Ident身份證號(hào)V

28、ARCHAR2(16)title職稱(chēng)VARCHAR2(16)department所屬部門(mén)VARCHAR2(16)sort在職類(lèi)別NUMBERmarriage婚姻狀況NUMBERremark備注VARCHAR2(400)Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p員工工資信息表（pay） 字 段 名名 稱(chēng)類(lèi) 型字 段 名名 稱(chēng)類(lèi) 型Id工資編號(hào)NUMBERtax個(gè)人所得稅NUMBERempnum員工編號(hào)VARCHAR2(16)insure_shiye失業(yè)保險(xiǎn)NUMBERbasepay基本工資NUMBERinsure_yanglao養(yǎng)老保險(xiǎn)NUMBERPost崗位工資NUMBERinsure_yi

29、liao醫(yī)療保險(xiǎn)NUMBERworkprice出勤費(fèi)NUMBERshouldpay應(yīng)發(fā)工資NUMBERMess伙食補(bǔ)貼NUMBERshoulddeduct應(yīng)扣工資NUMBERtraffic交通補(bǔ)貼NUMBERpay實(shí)發(fā)工資NUMBERPrice物價(jià)補(bǔ)貼NUMBEROracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程2009p員工請(qǐng)假信息表leave） 字 段 名名 稱(chēng)類(lèi) 型字 段 名名 稱(chēng)類(lèi) 型Id請(qǐng)假編號(hào)NUMBERleavepass請(qǐng)假批準(zhǔn)人VARCHAR2(16)empnum員工編號(hào)CHAR(16)reason請(qǐng)假原因VARCHAR2(400)startdate請(qǐng)假開(kāi)始時(shí)間DATEapplydate申請(qǐng)日期DATEenddate請(qǐng)假終止時(shí)間DATEcanceldate銷(xiāo)