Oracle數(shù)據(jù)庫中存儲過程的學(xué)習(xí)實(shí)例

1、測試用表student:1.實(shí)例1: Oracle存儲過程返回結(jié)果集-聲明一個 packagecreateor replace packagepackage_pro_testastype cursor_rs is ref cursor;Procedureproc_test(p_rsout cursor_rs);end package_pro_test;-實(shí)現(xiàn)package體，創(chuàng)建存儲過程-IN 和OUT參數(shù)-java 將IN 參數(shù)傳給 CallableStatement對象是通過 setXXX 方法完成-的。該方法繼承自-PreparedStatement 。所傳入?yún)?shù)的類型決定了所用的set

2、XXX方法-(例如，用 setFloat 來傳入float 值等)。CREATE OR REPLACE PackageBody PACKAGE_PRO_TESTIsProcedureproc_test(p_rsout cursor_rs)isbegi nopen p_rs for 'select * from student；end proc_test;end PACKAGE_PRO_TEST;/java中調(diào)用存儲過程import java.sql.CallableStateme nt;import java.sql.C onn ecti on;import java.sql.Driv

3、erMa nager;import java.sql.ResultSet;import java.sql.Stateme nt;import oracle.jdbc.OracleResultSet;調(diào)用存儲過程獲取查詢結(jié)果集author gwy*/ public classTestl publicstatic void main(String args) Conn ecti on conn 二 null;Statement stmt =null;ResultSet rs =null;try Class.forName('oracle.jdbc.driver.OracleDriver&q

4、uot;);conn 二 DriverMa nager.getC onn ecti on( "jdbc:oracle:thi n:localhost:1521:orcl", "scott", "tiger");System.out.pr intln("連接成功!");核心代碼：通過call調(diào)用PACKAGE_PRO_TEST.proc_te存儲過程。通過數(shù)據(jù)庫連接對象 conn調(diào)用 prepareCall()方法創(chuàng)建 CallableStatement 對象。存儲過程調(diào)用形式：conn. prepareCall(“

5、call存儲過程名(?,？，)”) conn. prepareCall( “call 包名. 存儲過程名 (?,?, , ) ”)*/ CallableStatement cs = conn.prepareCall("call PACKAGE_PRO_TEST.proc_test(?)");/注冊輸出參數(shù)類型cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);cs.execute(); /獲取存儲過程返回的結(jié)果集 ResultSet ors = (ResultSet)cs.getObject(1);/迭代結(jié)果

6、集while(ors.next()System.out.println("學(xué)生編號："+ors.getlnt("stu_id")+"學(xué)生姓名:"+ors.getString("stu_name')+"學(xué)生年齡:"+ors.getlnt("age"); catch (Exception e) e.printStackTrace(); finally if (rs != null) rs = null;if (stmt != null) stmt = null;if (conn 匸

7、 null ) conn 二 null2.實(shí)例2 :通過變量列表獲取單條記錄值/創(chuàng)建存儲過程：-in關(guān)鍵詞修飾的v_stuname為輸入?yún)?shù)，out關(guān)鍵詞修飾的out_stuid, out_st un ame,out_age, out_result_stateme為輸出參數(shù) createor replaceprocedurepro_test(v_stunamein varchar,out_stuid out nu mber,out_st un ameout varchar;out_ageout nu mber,out_result_statme nt out varchar) is-聲明標(biāo)志標(biāo)

8、量flag nu mber;begi nflag:=0;begi n-stud；stu _n ame,ag為 stude n表中的字段，-out_stuid,out_stu name,out_ag為 返回參數(shù)select stud；stu_name；agento out_stuid,out_stu name,out_ageromstude ntwhere stu_ name二v_stu name;excepti onwhe n no _data_fo un dthe nflag:= 1;end;if flag>0 thenout_result_statme門上=查詢數(shù)據(jù)異常!'

9、elseout_result_statment:=成功獲取結(jié)果!' en dif;end;/java中調(diào)用該存儲過程import java.sql.CallableStateme nt;import java.sql.C onn ecti on;import java.sql.DriverMa nager;import java.sql.ResultSet;import java.sql.Stateme nt;import oracle.jdbc.OracleResultSet:/*調(diào)用存儲過程獲取單條記錄* author gwy*/public classTest2 publicst

10、atic void main(String args) Connection conn = null;Statement stmt =null;ResultSet rs =null;try /加載Oracled驅(qū)動Class.forName("oracle.jdbc.driver.OracleDriver");/創(chuàng)建數(shù)據(jù)庫連接對象conn = DriverManager.getConnection("jdbc:oracle:thin:localhost:1521:orcl", "scott" , "tige r"

11、);System.out.println(" 連接成功！ ");/ 創(chuàng)建 CallableStatemen 對象CallableStatement cs = conn.prepareCal"l(call pro_test(?,?,?,?,?)");/給存儲過程的對應(yīng)輸入?yún)?shù)傳值 cs.setString(1,"tom1");/注冊存儲過程的輸出參數(shù)類型 (例:索引為 2的參數(shù)類型為 NUMBER 類型 )cs.registerOutParameter(2,oracle.jdbc.OracleTypes.NUMBE);cs.regist

12、erOutParameter(3,oracle.jdbc.OracleTypes.VARCHA R);cs.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER ); cs.registerOutParameter(5, oracle.jdbc.OracleTypes.VARCHAR);cs.execute();System.out.println("學(xué)生編號:"+cs.getlnt(2)+"學(xué)生姓 名:"+cs.getString(3)+"學(xué)生年齡:"+cs.getInt(4);

13、System.out.pri ntln("異常:"+cs.getStri ng(5); catch (Excepti on e) e.pri ntStackTrace(); finally if (rs != null) rs = null;if (stmt != null) stmt = null;if (conn != null) conn 二 null;3.實(shí)例3:存儲過程的參數(shù)既作為輸入?yún)?shù)也作為輸出參數(shù)/創(chuàng)建存儲過程參數(shù)v_stuname被in和out關(guān)鍵字修飾，即 v_stuname既作為輸入?yún)?shù)，也作為輸出參createor replaceprocedurep

14、ro_test2(v_stunamen outvarchar) isbegi nselect stu_name into v_stu namefrom stude nt wherestu_n ame=v_st un ame;excepti onwhe n n o_data_fo un dthe ndbms_output.put_li neC 沒有找到數(shù)據(jù)！');when othersthendbms_output.put_li neC 發(fā)生其它異常！');end;/java調(diào)用存儲過程importjava.sql.CallableStateme nt;importjava.sq

15、l.C onn ecti on;importjava.sql.DriverMa nager;importjava.sql.ResultSet;importjava.sql.Stateme nt;publicclass Test3 public static void main( Stri ng args) Conn ecti on conn 二n ull ;Stateme nt stmt =n ull ;ResultSet rs =nulltry Class.forName(.OracleDriver"oracle.jdbc.driver);conn = DriverManager.getConnection( "jdbc:oracle:thin:localhost:1521:orcl" "scott" , "tiger" );System.out.println(" 連接成功！ ");CallableStatementcs = conn.prepareCall( "callpro_test2(?)"cs.setString(1,"tom1" );cs.registerOu