條件分支結(jié)構(gòu)、循環(huán)控制結(jié)構(gòu)和存儲(chǔ)過(guò)程--分頁(yè)存儲(chǔ)過(guò)程oracle

1、條件分支結(jié)構(gòu)、循環(huán)控制結(jié)構(gòu)和存儲(chǔ)過(guò)程-分頁(yè)存儲(chǔ)過(guò)程oracle條件分支結(jié)構(gòu)案例1、編寫(xiě)一個(gè)過(guò)程，可以輸入一個(gè)雇員姓名，如果該雇員的工資低于2000，就給該雇員工資增加10%。/*結(jié)構(gòu)：IF 條件 THEN執(zhí)行語(yǔ)句;END IF;*/CREATE OR REPLACE PROCEDURE add_sal_prc(p_name IN VARCHAR2) ISn_sal myemp.sal%TYPE;BEGINSELECT sal INTO n_sal FROM myemp WHERE ename = p_name;IF n_sal < 2000 THENUPDATE myemp SET sa

2、l = sal * (1 + 0.1) WHERE ename = p_name;END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.put_line('未找到相關(guān)信息.');END add_sal_prc;/2、編寫(xiě)一個(gè)過(guò)程，在myemp表中，如果某個(gè)雇員的工資低于2000，就給該雇員工資增加10%。CREATE OR REPLACE PROCEDURE update_myemp_sal_prc ISCURSOR c_test ISSELECT * FROM myemp FOR UPDATE;BEGINFOR var_data

3、 IN c_test LOOPIF var_data.sal < 2000 THENUPDATE myemp SET sal = sal * 1.1 WHERE CURRENT OF c_test;END IF;END LOOP;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.put_line('未找到相關(guān)信息.');END update_myemp_sal_prc;/3、編寫(xiě)一個(gè)過(guò)程，可以輸入一個(gè)雇員姓名，如果該雇員的獎(jiǎng)金不是0，就在原來(lái)的根底上增加100，如果獎(jiǎng)金是0，就把該獎(jiǎng)金設(shè)置為200；/*結(jié)構(gòu)：IF 條件 THEN執(zhí)行語(yǔ)

4、句;ELSE 執(zhí)行語(yǔ)句;END IF;*/CREATE OR REPLACE PROCEDURE update_myemp_user_comm_prc(p_name IN VARCHAR2) IScomm_tmp myemp m%TYPE;BEGINSELECT NVL(comm, 0) comm INTO comm_tmpFROM myempWHERE ename = p_name;IF comm_tmp != 0 THENUPDATE myemp SET comm = comm + 100 WHERE ename = p_name;ELSEUPDATE myemp SET comm = 2

5、00 WHERE ename = p_name;END IF;END update_myemp_user_comm_prc;/4、編寫(xiě)一個(gè)過(guò)程，在myemp表中，如果該雇員的獎(jiǎng)金不是0，就在原來(lái)的根底上增加100，如果獎(jiǎng)金是0，就把該獎(jiǎng)金設(shè)置為200；CREATE OR REPLACE PROCEDURE update_myemp_comm_prc ISCURSOR c_test ISSELECT ename, sal, NVL(comm, 0) comm FROM myemp FOR UPDATE;BEGINFOR var_data IN c_test LOOPIF var_data m !

6、= 0 THENUPDATE myemp SET comm = comm + 100WHERE CURRENT OF c_test;ELSEUPDATE myemp SET comm = 200 WHERE CURRENT OF c_test;END IF;END LOOP;END update_myemp_comm_prc;/5、編寫(xiě)一個(gè)過(guò)程，可以輸入一個(gè)雇員編號(hào)，如果該雇員的職位是PRESIDENT就給他的工資增加1000，如果該雇員的職位是MANAGER就給他的工資的增加500，其它職位的雇員的工資增加200;/*結(jié)構(gòu)：IF 條件 THEN執(zhí)行語(yǔ)句；ELSIF 條件 THEN執(zhí)行語(yǔ)句；.

7、ELSE 執(zhí)行語(yǔ)句；END IF;*/CREATE OR REPLACE PROCEDURE modify_sal_prc(p_empno IN INTEGER) ISv_job myemp.job%TYPE;BEGINSELECT job INTO v_job FROM myemp WHERE empno = p_empno;IF v_job = 'PRESIDENT' THENUPDATE myemp SET sal = sal + 1000 WHERE empno = p_empno;ELSIF v_job = 'MANAGER' THENUPDATE m

8、yemp SET sal = sal + 500 WHERE empno = p_empno;ELSEUPDATE myemp SET sal = sal + 200 WHERE empno = p_empno;END IF;END modify_sal_prc;/擴(kuò)展：如果是對(duì)所有雇員的工資進(jìn)行修改，應(yīng)該使用游標(biāo)，請(qǐng)參考第2或第4題。循環(huán)語(yǔ)句1、循環(huán)語(yǔ)句LOOP案例，LOOP循環(huán)語(yǔ)句至少會(huì)被執(zhí)行一次；/*結(jié)構(gòu) ： LOOP循環(huán)體；退出循環(huán)條件； END LOOP;LOOP循環(huán)至少會(huì)被執(zhí)行一 次*/CREATE OR REPLACE PROCEDURE loop_demo_prc(p_name

9、 IN VARCHAR2) ISi_id INTEGER := 1;BEGINLOOPINSERT INTO tb_login VALUES (i_id, p_name);i_id := i_id + 1;EXIT WHEN i_id = 11;END LOOP;END loop_demo_prc;2、循環(huán)語(yǔ)句while案例/*結(jié)構(gòu) ： while 循環(huán)條件 LOOP循環(huán)體END LOOP;*/CREATE OR REPLACE PROCEDURE while_demo_prc(p_name IN VARCHAR2) ISi_id INTEGER := 11;BEGINwhile i_id &

10、lt; 21 LOOPINSERT INTO tb_login VALUES (i_id, p_name);i_id := i_id + 1;END LOOP;END while_demo_prc;3、循環(huán)語(yǔ)句for案例/*結(jié)構(gòu) ： for 變量 IN num1.num2 LOOP循環(huán)體END LOOP;說(shuō)明：num1和num2必須是大于0的整數(shù)，且num2>num1*/CREATE OR REPLACE PROCEDURE for_demo_prc(p_name IN VARCHAR2) ISi_id INTEGER := 21;BEGINFOR i IN 21 . 31 LOOPIN

11、SERT INTO tb_login VALUES (i_id, p_name);i_id := i_id + 1;END LOOP;END for_demo_prc;/4、循環(huán)語(yǔ)句GOTO案例建議盡量不要使用/*結(jié)構(gòu)：LOOPIF 條件 THENGOTO 標(biāo)識(shí);END IF;改變條件的語(yǔ)句;其它執(zhí)行語(yǔ)句;END LOOP;<<標(biāo)識(shí)>>*/DECLAREi_num INT := 1;BEGINLOOPIF i_num > 10 THENGOTO flg;END IF;DBMS_OUTPUT.put_line('i_num = ' | i_num);

12、i_num := i_num + 1;END LOOP;<<flg>>DBMS_OUTPUT.put_line('循環(huán)結(jié)束');END;/順序控制語(yǔ)句1、順序控制語(yǔ)句N(xiāo)ULL案例;/*說(shuō)明：NULL語(yǔ)句不會(huì)執(zhí)行任何操作，使用NULL語(yǔ)句的好處主要是提高PL/SQL的可讀性*/DECLAREv_name myemp.ename%TYPE;n_sal myemp.sal%TYPE;BEGINSELECT ename, salINTO v_name, n_sal FROM myemp WHERE empno = &empno;IF n_sal <

13、 3000 THENUPDATE myemp SET comm = sal * 0.1 WHERE ename = v_name;ELSENULL;END IF;END;/分頁(yè)儲(chǔ)存過(guò)程無(wú)返回值的存儲(chǔ)過(guò)程：1、現(xiàn)有一張表books，表結(jié)構(gòu)如下：-表booksDROP TABLE books PURGE;CREATE TABLE books(book_id NUMBER(4),book_name VARCHAR2(50),press VARCHAR2(50),CONSTRAINT PK_bookID PRIMARY KEY(book_id);請(qǐng)編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，可以向表books添加書(shū)，要求通過(guò)j

14、ava程序調(diào)用該過(guò)程。存儲(chǔ)過(guò)程：add_book_prc()-存儲(chǔ)過(guò)程add_book_prcDROP PROCEDURE add_book_prc;CREATE PROCEDURE add_book_prc(p_bookID IN NUMBER,p_bookName IN VARCHAR2,p_press IN VARCHAR2) ISBEGININSERT INTO books VALUES(p_bookID,p_bookName,p_press);END add_book_prc;/輸入內(nèi)容過(guò)濾package org.lxh.addbook;import java.io.Buffered

15、Reader;import java.io.IOException;import java.io.InputStreamReader;public class InputData private BufferedReader buf = null;public InputData() / 將字節(jié)輸入流轉(zhuǎn)換為字符流存放在緩沖區(qū)中this.buf = new BufferedReader(new InputStreamReader(System.in);public String getString(String info) / 讀取輸入的數(shù)據(jù)String str = null;System.ou

16、t.print(info);/ 打印提示輸入的信息try str = this.buf.readLine();/ 按行讀取 catch (IOException e) System.out.println("讀取數(shù)據(jù)失?。?quot;);return str;/ 將判斷輸入的數(shù)據(jù)是否為整數(shù)public int getInt(String info, String err) boolean flag = true;int temp = 0;while (flag) String str = this.getString(info);if (str.matches(""

17、;) flag = false;temp = Integer.parseInt(str); else System.out.print(err);return temp;調(diào)用過(guò)程函數(shù)，向數(shù)據(jù)表books中增加數(shù)據(jù)package org.lxh.addbook;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;public class AddBook / 加載驅(qū)動(dòng)程序之前在工程屬性中配置的jdbc的驅(qū)動(dòng)程序的jar包中public static final Stri

18、ng DBDRIVER = "oracle.jdbc.driver.OracleDriver"/ 連接地址是由各個(gè)數(shù)據(jù)庫(kù)生產(chǎn)商單獨(dú)提供的，所以需要單獨(dú)記住public static final String DBURL = "jdbc:oracle:thin:192.168.1.12:1521:mldn"/ 連接數(shù)據(jù)庫(kù)的用戶名public static final String DBUSER = "scott"/ 連接數(shù)據(jù)庫(kù)的用戶名的密碼public static final String DBPWD = "tiger&qu

19、ot;public static void main(String args) throws Exception / 加載oracle驅(qū)動(dòng)Class.forName(DBDRIVER);/ 得到連接Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD);/ 調(diào)用存儲(chǔ)過(guò)程CallableStatement cs = con.prepareCall("call add_book_prc(?,?,?)");InputData input = new InputData();int bookID = in

20、put.getInt("請(qǐng)輸入書(shū)本編號(hào)：", "書(shū)本編號(hào)必須是4位整數(shù),");String bookName = input.getString("請(qǐng)輸入書(shū)本名稱：");String press = input.getString("請(qǐng)輸入出版社：");/ 設(shè)置輸入?yún)?shù)的值cs.setInt(1, bookID);cs.setString(2, bookName);cs.setString(3, press);/ 執(zhí)行SQL操作cs.execute();/ 關(guān)閉數(shù)據(jù)庫(kù)資源cs.close();con.close()

21、;有返回值的存儲(chǔ)過(guò)程：1、案例：輸入雇員的編號(hào)，返回該雇員的姓名有輸入輸出的存儲(chǔ)過(guò)程-有輸入和輸出的存儲(chǔ)過(guò)程CREATE OR REPLACE PROCEDURE get_name_prc(p_empno IN INTEGER,p_name OUT VARCHAR2) ISBEGINSELECT ename INTO p_name FROM emp WHERE empno = p_empno;END get_name_prc;調(diào)用回值的存儲(chǔ)過(guò)程方法如下：-執(zhí)行有輸入和輸出的存儲(chǔ)過(guò)程DECLAREn_name emp.ename%Type;BEGINget_name_prc(&empno

22、, n_name);DBMS_OUTPUT.put_line(n_name);END;Java程序調(diào)用有輸入和輸出的存儲(chǔ)過(guò)程輸入內(nèi)容過(guò)濾，同上。package org.lxh.getname;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Types;import org.lxh.addbook.InputData;public class GetName / 加載驅(qū)動(dòng)程序之前在工程屬性中配置的jdbc的驅(qū)動(dòng)程序的jar包中pub

23、lic static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"/ 連接地址是由各個(gè)數(shù)據(jù)庫(kù)生產(chǎn)商單獨(dú)提供的，所以需要單獨(dú)記住public static final String DBURL = "jdbc:oracle:thin:192.168.1.12:1521:mldn"/ 連接數(shù)據(jù)庫(kù)的用戶名public static final String DBUSER = "scott"/ 連接數(shù)據(jù)庫(kù)的用戶名的密碼public static final String D

24、BPWD = "tiger"public static void main(String args) throws Exception / 加載oracle驅(qū)動(dòng)Class.forName(DBDRIVER);/ 得到連接Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD);/ 調(diào)用存儲(chǔ)過(guò)程CallableStatement cs = con.prepareCall("call get_name_prc(?,?)");cs.registerOutParameter(2, Typ

25、es.VARCHAR);InputData input = new InputData();int empno = input.getInt("請(qǐng)輸入雇員編號(hào)：", "雇員編號(hào)必須是4位整數(shù),");cs.setInt(1, empno);cs.execute();String str = cs.getString(2);cs.close();con.close();System.out.println("雇員編號(hào)" + empno + "的姓名是：" + str);存儲(chǔ)過(guò)程返回多個(gè)值2、編寫(xiě)一個(gè)過(guò)程，可以輸入雇員的

26、編號(hào)，返回該雇員的姓名、工資和職位。CREATE OR REPLACE PROCEDURE get_info_prc(p_empno IN INTEGER,p_name OUT VARCHAR2,p_sal OUT NUMBER,p_job OUT VARCHAR2) ISBEGINSELECT ename, job, salINTO p_name, p_job, p_salFROM empWHERE empno = p_empno;END get_info_prc;Java程序輸入內(nèi)容過(guò)濾同上package org.lxh.getinfo;import java.sql.CallableSt

27、atement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Types;import org.lxh.addbook.InputData;public class GetInfo / 加載驅(qū)動(dòng)程序之前在工程屬性中配置的jdbc的驅(qū)動(dòng)程序的jar包中public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver"/ 連接地址是由各個(gè)數(shù)據(jù)庫(kù)生產(chǎn)商單獨(dú)提供的，所以需要單獨(dú)記住public static

28、final String DBURL = "jdbc:oracle:thin:192.168.1.12:1521:mldn"/ 連接數(shù)據(jù)庫(kù)的用戶名public static final String DBUSER = "scott"/ 連接數(shù)據(jù)庫(kù)的用戶名的密碼public static final String DBPWD = "tiger"public static void main(String args) throws Exception / 加載oracle驅(qū)動(dòng)Class.forName(DBDRIVER);/ 得到連接Co

29、nnection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD);/ 調(diào)用存儲(chǔ)過(guò)程CallableStatement cs = con.prepareCall("call get_info_prc(?,?,?,?)");cs.registerOutParameter(2, Types.VARCHAR);cs.registerOutParameter(3, Types.NUMERIC);cs.registerOutParameter(4, Types.VARCHAR);InputData input = new

30、InputData();int empno = input.getInt("請(qǐng)輸入雇員編號(hào)：", "雇員編號(hào)必須是4位整數(shù),");cs.setInt(1, empno);cs.execute();String name = cs.getString(2);double sal = cs.getDouble(3);String job = cs.getString(4);cs.close();con.close();System.out.println("雇員編號(hào)" + empno + "的姓名是：" + name

31、+ ",職業(yè)：" + job+ ",工資：" + sal);有返回值的存儲(chǔ)過(guò)程(列表結(jié)果集)由于oracle存儲(chǔ)過(guò)程返回值是由OUT指定的參數(shù)代替，只能返回單值的內(nèi)容。而對(duì)于列表、集合，不能用一般的參數(shù)，必須要使用package包。3、案例：編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，輸入部門(mén)編號(hào)，返回該部門(mén)所有雇員信息。建立包test_pkgCREATE OR REPLACE PACKAGE test_pkg ISTYPE test_curtype IS REF CURSOR;END test_pkg;存儲(chǔ)過(guò)程：test_prcCREATE OR REPLACE PROCEDU

32、RE test_prc(p_deptno IN INTEGER,c_test OUT test_pkg.test_curtype) ISBEGINOPEN c_test FORSELECT * FROM emp WHERE deptno = p_deptno;END test_prc;用oracle塊測(cè)試集合DECLAREc_testprc test_pkg.test_curtype;v_data emp%ROWTYPE;BEGINtest_prc(&deptno, c_testprc);LOOPFETCH c_testprcINTO v_data;EXIT WHEN c_testpr

33、c%NOTFOUND;DBMS_OUTPUT.put_line(v_data.empno | '/' | v_data.ename);END LOOP;CLOSE c_testprc;END;Java程序輸入內(nèi)容過(guò)濾package org.lxh.testcursor;import java.io.BufferedReader;import java.io.IOException;import java.io.InputStreamReader;public class InputData private BufferedReader buf = null;public Inp

34、utData() / 將字節(jié)輸入流轉(zhuǎn)換為字符流存放在緩沖區(qū)中this.buf = new BufferedReader(new InputStreamReader(System.in);public String getString(String info) / 讀取輸入的數(shù)據(jù)String str = null;System.out.print(info);/ 打印提示輸入的信息try str = this.buf.readLine();/ 按行讀取 catch (IOException e) System.out.println("讀取數(shù)據(jù)失??！");return str

35、;/ 將判斷輸入的數(shù)據(jù)是否為整數(shù)public int getInt(String info, String err) boolean flag = true;int temp = 0;while (flag) String str = this.getString(info);if (str.matches("") flag = false;temp = Integer.parseInt(str); else System.out.print(err);return temp;通過(guò)設(shè)置存儲(chǔ)過(guò)程的輸出參數(shù)為OracleTypes.CURSOR類(lèi)型，獲取結(jié)果集package o

36、rg.lxh.testcursor;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import oracle.jdbc.OracleTypes;public class TestCursor / 加載驅(qū)動(dòng)程序之前在工程屬性中配置的jdbc的驅(qū)動(dòng)程序的jar包中public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver&

37、quot;/ 連接地址是由各個(gè)數(shù)據(jù)庫(kù)生產(chǎn)商單獨(dú)提供的，所以需要單獨(dú)記住public static final String DBURL = "jdbc:oracle:thin:192.168.1.12:1521:mldn"/ 連接數(shù)據(jù)庫(kù)的用戶名public static final String DBUSER = "scott"/ 連接數(shù)據(jù)庫(kù)的用戶名的密碼public static final String DBPWD = "tiger"public static void main(String args) throws Excep

38、tion / 加載oracle驅(qū)動(dòng)Class.forName(DBDRIVER);/ 得到連接Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPWD);/ 調(diào)用存儲(chǔ)過(guò)程CallableStatement cs = con.prepareCall("call test_prc(?,?)");/ 設(shè)置存儲(chǔ)過(guò)程參數(shù)的類(lèi)型cs.registerOutParameter(2, OracleTypes.CURSOR);/ 設(shè)置輸入?yún)?shù)的值InputData input = new InputData();int

39、 deptno = input.getInt("請(qǐng)輸入部門(mén)編號(hào)：", "部門(mén)編號(hào)必須是2位整數(shù),");cs.setInt(1, deptno);/ 執(zhí)行SQL操作cs.execute();/ 取得結(jié)果集ResultSet rs = (ResultSet) cs.getObject(2);/ 輸出結(jié)果集的局部?jī)?nèi)容while (rs.next() System.out.println("雇員編號(hào)：" + rs.getInt(1) + ",姓名："+ rs.getString(2) + ",職業(yè)："

40、+ rs.getString("JOB");/ 關(guān)閉數(shù)據(jù)庫(kù)資源rs.close();cs.close();con.close();分頁(yè)存儲(chǔ)過(guò)程請(qǐng)編寫(xiě)一個(gè)存儲(chǔ)過(guò)程，要求可以輸入表名、每頁(yè)顯示記錄數(shù)、當(dāng)前頁(yè)。返回總記錄數(shù)，總頁(yè)數(shù)、和返回的結(jié)果集。-分頁(yè)存儲(chǔ)過(guò)程/*輸入?yún)?shù)：p_table_name：表名p_records_per_page：每頁(yè)顯示的記錄p_row_amount：表中總的記錄數(shù)p_page_amount：表示總頁(yè)數(shù)c_test：REF CURSOR游標(biāo)變量，no return typec_test屬于以下的游標(biāo)類(lèi)型CREATE OR REPLACE PACKAG

41、E test_pkg ISTYPE test_curtype IS REF CURSOR;END test_pkg;v_sql：專門(mén)處理的SQL語(yǔ)句i_current_page_first_record：表示當(dāng)前頁(yè)的第一條記錄EXECUTE IMMEDIATE 'SQL語(yǔ)句' INTO 變量 ：表示執(zhí)行SQL語(yǔ)句，可以把查詢結(jié)果存在變量中,該變量只能存儲(chǔ)單行或單個(gè)數(shù)據(jù)，不能存儲(chǔ)多行數(shù)據(jù)，如果確實(shí)要存儲(chǔ)多行數(shù)據(jù)，請(qǐng)使用顯示游標(biāo)或REF游標(biāo)。*/CREATE OR REPLACE PROCEDURE test_paging_prc(p_table_name IN VARCHAR2,

42、p_records_per_page IN INTEGER,p_current_page IN INTEGER,p_row_amount OUT INTEGER,p_page_amount OUT INTEGER,c_test OUT test_pkg.test_curtype) ISv_sql VARCHAR2(1000);i_current_page_first_record INTEGER;BEGINi_current_page_first_record := (p_current_page - 1) * p_records_per_page + 1;v_sql := 'SELE

43、CT COUNT(*) FROM ' | p_table_name;-取得總記錄數(shù)EXECUTE IMMEDIATE v_sqlINTO p_row_amount; -取得總頁(yè)數(shù)IF (MOD(p_row_amount,p_records_per_page) = 0) THEN p_page_amount := p_row_amount / p_records_per_page; ELSE p_page_amount := trunc(p_row_amount / p_records_per_page) + 1; END IF;IF p_current_page = p_page_am

44、ount THENv_sql := 'SELECT *FROM (SELECT ROWNUM i_rowid, temp.*FROM (SELECT * FROM ' | p_table_name |') tempWHERE ROWNUM <= ' | p_row_amount | ')tmpWHERE tmp.i_rowid >=' | i_current_page_first_record;OPEN c_test FOR v_sql;ELSEv_sql := 'SELECT *FROM (SELECT ROWNUM i_rowid, temp.*FROM (SELECT * FROM ' | p_table_name |') tempWHERE ROWNUM <=' | p_current_page * p_records_per_page |