一、需求
傳入一個參數，返回一條或多條記錄(列表)。
二、實現步驟
1. 編寫Oracle存儲過程。
--?聲明包和包體的語句應該分兩次執行，即使聲明在前定義在后，如果一起執行依然會編譯出錯
--調用能夠返回多條記錄的存儲過程需要定義在包內
--聲明包及包內的游標、存儲過程
CREATE OR REPLACE PACKAGE pack_myPackage ISTYPE myCursorIS REF CURSOR;PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, myCursor OUT myCursor);ENDpack_myPackage;--定義包體
CREATE OR REPLACE PACKAGE BODY pack_myPackage IS
PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, outcursor OUT myCursor) IS
BEGIN
OPEN outcursor FORSELECT * FROM tableA WHERE P_NO =pNo;ENDproc_querySomeInfo;END pack_myPackage;
2. 編寫Java代碼
packagevip.yaocn.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;/*** 存儲過程測試
*
*@authoryacon*/
public classProcedureTest {public static void main(String[] args) throwsException {
String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String driverName= "oracle.jdbc.driver.OracleDriver";
String username= "scott";
String password= "tiger";
Connection conn= null;
CallableStatement cstmt= null;
ResultSet rs= null;try{
Class.forName(driverName);
conn=DriverManager.getConnection(url, username, password);
String callSql= "{call pack_myPackage.proc_getSomeInfo(?, ?) }";
cstmt=conn.prepareCall(callSql);
cstmt.setString(1, "1001");
cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs= (ResultSet) cstmt.getObject(2);if (rs != null) {while(rs.next()) {
System.out.println(rs.getString(1));
}
}
}catch(Exception e) {
e.printStackTrace();
}finally{try{if (rs != null) {
rs.close();
}
}catch(Exception e) {
e.printStackTrace();
}try{if (cstmt != null) {
cstmt.close();
}
}catch(Exception e) {
e.printStackTrace();
}try{if (conn != null) {
conn.close();
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
三、 其他需求
1. Java調用有返回值的Oracle存儲過程(返回一個簡單類型的值)
(1)定義存儲過程：
CREATE OR REPLACE PROCEDURE proc_A(param1 IN VARCHAR2, param2 OUT VARCHAR2)AS
BEGIN
SELECT name INTO param2 FROM T_A WHERE id =param1;END;
(2)編寫Java代碼：
packagevip.yaocn.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;import java.sql.ResultSet;
/*** 存儲過程測試
*
*@authoryacon*/
public classProcedureTest {public static void main(String[] args) throwsException {
String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String driverName= "oracle.jdbc.driver.OracleDriver";
String username= "scott";
String password= "tiger";
Connection conn= null;
CallableStatement cstmt= null;
ResultSet rs= null;try{
Class.forName(driverName);
conn=DriverManager.getConnection(url, username, password);
String callSql= "{call proc_A(?, ?) }";
cstmt=conn.prepareCall(callSql);
cstmt.setString(1, "1001");
cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cstmt.execute();
System.out.println("Output is:" + cstmt.getString(2));}catch(Exception e) {
e.printStackTrace();
}finally{try{if (rs != null) {
rs.close();
}
}catch(Exception e) {
e.printStackTrace();
}try{if (cstmt != null) {
cstmt.close();
}
}catch(Exception e) {
e.printStackTrace();
}try{if (conn != null) {
conn.close();
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
2. Java調用無返回值的Oracle存儲過程(無返回值)
(1)定義存儲過程：
CREATE OR REPLACE PROCEDURE proc_B(param1 IN VARCHAR2, param2 IN VARCHAR2)AS
BEGIN
INSERT INTO T_B VALUES(param1, param2);END;
(2)編寫Java代碼：
packagevip.yaocn.test;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;import java.sql.ResultSet;
/*** 存儲過程測試
*
*@authoryacon*/
public classProcedureTest {public static void main(String[] args) throwsException {
String url= "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String driverName= "oracle.jdbc.driver.OracleDriver";
String username= "scott";
String password= "tiger";
Connection conn= null;
CallableStatement cstmt= null;
ResultSet rs= null;try{
Class.forName(driverName);
conn=DriverManager.getConnection(url, username, password);
String callSql= "{call proc_B(?, ?) }";
cstmt=conn.prepareCall(callSql);
cstmt.setString(1, "1001");
cstmt.setString(2, "TEST");
cstmt.execute();
}catch(Exception e) {
e.printStackTrace();
}finally{try{if (rs != null) {
rs.close();
}
}catch(Exception e) {
e.printStackTrace();
}try{if (cstmt != null) {
cstmt.close();
}
}catch(Exception e) {
e.printStackTrace();
}try{if (conn != null) {
conn.close();
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
四、相關知識
1. 在plsql developer中測試存儲過程時可以在存儲過程名上右擊選擇 “測試” ，如果需要提供參數可以在下方填入參數，如果沒有參數則點擊執行按鈕即可。
 詳解)
