資源描述：
《jdbc調(diào)用存儲(chǔ)過(guò)程游標(biāo).doc》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、1.CREATE?OR?REPLACE?PROCEDURE?pro_query_dept?(??2.???p_deptno???IN???????dept.deptno%TYPE,??3.???p_dname????OUT??????dept.dname%TYPE??4.)??5.--聲明區(qū)??6.AS??7.--執(zhí)行區(qū)??8.BEGIN??9.???SELECT?dname??10.?????INTO?p_dname??11.?????FROM?dept??12.????WHERE?deptno?=?p_deptno;??13.??14
2、.???IF?p_dname?=?'SALES'??15.???THEN??16.??????p_dname?:=?'銷售部門(mén)';??17.???ELSE??18.??????p_dname?:=?'未知部門(mén)';??19.???END?IF;??20.??21.???DBMS_OUTPUT.put_line?(p_dname);??22.--異常處理??23.EXCEPTION??24.???WHEN?OTHERS??25.???THEN??26.??????DBMS_OUTPUT.put_line?(SQLCODE?
3、
4、?SQLERRM
5、);??27.END;??CREATEORREPLACEPROCEDUREpro_query_dept(p_deptnoINdept.deptno%TYPE,p_dnameOUTdept.dname%TYPE)--聲明區(qū)AS--執(zhí)行區(qū)BEGINSELECTdnameINTOp_dnameFROMdeptWHEREdeptno=p_deptno;IFp_dname='SALES'THENp_dname:='銷售部門(mén)';ELSEp_dname:='未知部門(mén)';ENDIF;DBMS_OUTPUT.put_line(p_dname);--異常處理
6、EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.put_line(SQLCODE
7、
8、SQLERRM);END;?測(cè)試存儲(chǔ)過(guò)程：Sql代碼1.--測(cè)試??2.??3.DECLARE??4.???v_dname???dept.dname%TYPE;??5.BEGIN??6.???pro_query_dept?(30,?v_dname);??7.END;??--測(cè)試DECLAREv_dnamedept.dname%TYPE;BEGINpro_query_dept(30,v_dname);END;?java類源代碼：Java
9、代碼1.package?com.sun.myjdbc;??2.??3.import?java.sql.CallableStatement;??4.import?java.sql.Connection;??5.import?java.sql.DriverManager;??6.import?java.sql.Types;??7.??8.public?class?TestC?{??9.??????10.????public?static?void?test1(){??11.????????try?{??12.????????????//?加載
10、驅(qū)動(dòng)??13.????????????Class.forName("oracle.jdbc.driver.OracleDriver");??14.????????????String?url?=?"jdbc:oracle:thin:@127.0.0.1:1521:orcl";??15.????????????String?user?=?"scott";??16.????????????String?password?=?"tiger";??17.????????????Connection?conn?=?DriverManager.get
11、Connection(url,?user,?password);??18.????????????String?sql?=?"{call?pro_query_dept(?,?)}";??19.????????????//?創(chuàng)建一個(gè)過(guò)程的分析容器??20.????????????CallableStatement?cst?=?conn.prepareCall(sql);??21.????????????//?設(shè)置輸入?yún)?shù)??22.????????????cst.setInt(1,?30);??23.????????????//?定義輸出類型
12、??24.????????????cst.registerOutParameter(2,?Types.VARCHAR);??25.????????????//?執(zhí)行存儲(chǔ)過(guò)程??26.?????
