Oracle-10g數(shù)據(jù)庫基礎(chǔ)教程第17章基于Oracle數(shù)據(jù)庫的應(yīng)用開發(fā).ppt

第17 基于Oracle數(shù)據(jù)庫的應(yīng)用開發(fā),本章內(nèi)容,利用PL/SQL程序?qū)崿F(xiàn)分頁查詢 基于Oracle數(shù)據(jù)庫的人事管理系統(tǒng)開發(fā),本章要求,掌握利用PL/SQL程序?qū)崿F(xiàn)分頁查詢 掌握利用JDBC連接數(shù)據(jù)庫的方法 了解基于數(shù)據(jù)庫的應(yīng)用系統(tǒng)開發(fā)過程 了解人事管理系統(tǒng)部分核心代碼的實(shí)現(xiàn),17.1 利用PL/SQL實(shí)現(xiàn)分頁查詢,利用集合實(shí)現(xiàn)分頁查詢 利用游標(biāo)變量實(shí)現(xiàn)分頁查詢 分頁查詢?cè)贘ava開發(fā)中的應(yīng)用,17.1.1 利用集合實(shí)現(xiàn)分頁查詢,如果要對(duì)特定結(jié)構(gòu)的結(jié)果集進(jìn)行分頁查詢，則可以使用集合實(shí)現(xiàn)。 利用分頁查詢實(shí)現(xiàn)對(duì)員工工資名次、員工號(hào)、員工名、工資和部門號(hào)的查詢。,CREATE OR REPLACE PACKAGE emppkg IS -定義結(jié)果集中每條記錄的類型 TYPE t_record IS RECORD( rn INT, empno emp.empno%TYPE, ename emp.ename%TYPE, sal emp.sal%TYPE, deptno emp.deptno%TYPE); -定義保存返回結(jié)果集的集合類型 TYPE emplist IS TABLE OF t_record; -定義個(gè)實(shí)現(xiàn)分頁顯示的過程 PROCEDURE sp_page( p_pageSize IN OUT INT, -每頁輸出的記錄數(shù) p_curPageNo IN OUT INT, -當(dāng)前頁碼 p_outCollection OUT emplist -查詢返回當(dāng)前頁數(shù)據(jù)的集合 ); END;,CREATE OR REPLACE PACKAGE BODY emppkg IS PROCEDURE sp_page( p_pageSize IN OUT INT, -每頁輸出的記錄數(shù) p_curPageNo IN OUT INT, -當(dāng)前頁碼 p_outCollection OUT emplist) -查詢返回當(dāng)前頁數(shù)據(jù)的集合 AS v_startRownum NUMBER; v_endRownum NUMBER; v_totalRecords NUMBER; v_totalPages NUMBER; indexValue BINARY_INTEGER :=1; BEGIN -查詢記錄總數(shù) SELECT count(*) INTO v_totalRecords FROM emp; -驗(yàn)證頁面記錄數(shù)量 IF p_pageSize 0 THEN p_pageSize := 10; END IF;,-計(jì)算總頁數(shù) 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; -驗(yàn)證頁號(hào) IF p_curPageNo v_totalPages THEN p_curPageNo :=v_totalPages; END IF;,-執(zhí)行分頁查詢 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;,通過對(duì)包中分頁查詢過程的調(diào)用，可以根據(jù)需要輸出特定頁的數(shù)據(jù)。 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;,執(zhí)行結(jié)果為 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 利用游標(biāo)變量實(shí)現(xiàn)分頁查詢,如果要對(duì)任意查詢實(shí)現(xiàn)分頁，則可以使用游標(biāo)變量來實(shí)現(xiàn)。,CREATE OR REPLACE PACKAGE curspkg IS TYPE refCursorType IS REF CURSOR; PROCEDURE sp_page( p_pageSize IN OUT INT, -每頁輸出的記錄數(shù) p_curPageNo IN OUT INT, -當(dāng)前頁碼 p_sqlSelect VARCHAR2, -查詢語句,含排序部分 p_totalPages OUT INT, -返回總頁數(shù) p_totalRecords OUT INT, -返回總記錄數(shù) p_outCursor OUT refCursorType -查詢返回當(dāng)前頁的數(shù)據(jù) ); 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;,執(zhí)行結(jié)果為： 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 分頁查詢?cè)贘ava開發(fā)中的應(yīng)用,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數(shù)據(jù)庫的人事管理系統(tǒng)開發(fā),人事管理系統(tǒng)介紹 數(shù)據(jù)庫設(shè)計(jì) 重要界面的設(shè)計(jì)與實(shí)現(xiàn) 主要代碼的實(shí)現(xiàn),人事管理系統(tǒng)介紹,功能：錄入人事的基本資料，在操作上能夠完成諸如添加、修改、刪除、按各種條件進(jìn)行查詢、新用戶的設(shè)置及密碼修改等方面的工作，基本滿足人事日常業(yè)務(wù)的需要。 實(shí)用的/S結(jié)構(gòu)，后臺(tái)Oracle數(shù)據(jù)庫,17.2.1 數(shù)據(jù)庫設(shè)計(jì),員工基本信息表 員工基本信息表主要描述員工的個(gè)人情況，如姓名、性別、出生日期、身份證號(hào)、所屬部門等，其中員工編號(hào)作為員工基本信息表的主碼。 員工工資信息表 員工工資信息表主要描述每個(gè)員工所對(duì)應(yīng)的工資情況，如工資編號(hào)、基本工資、崗位工資、出勤費(fèi)等，其中工資編號(hào)作為員工工資信息表的主碼。而其中的員工編號(hào)是外碼，它的取值參照于員工基本信息表的主碼取值。同時(shí)，每名員工均只有一個(gè)工資編號(hào)和一個(gè)員工編號(hào)，即員工實(shí)體與員工工資實(shí)體之間是一對(duì)一的聯(lián)系。,員工請(qǐng)假信息表 員工請(qǐng)假信息表主要描述每個(gè)員工所對(duì)應(yīng)的請(qǐng)假情況，如請(qǐng)假總天數(shù)、請(qǐng)假開始時(shí)間、請(qǐng)假結(jié)束時(shí)間、請(qǐng)假原因等，其中請(qǐng)假編號(hào)作為員工請(qǐng)假信息表的主碼。而其中的員工編號(hào)是外碼，它參照于員工基本信息表的主碼取值。同時(shí)，每名員工可以有多次請(qǐng)假記錄，即員工實(shí)體