做一個，記一篇 — Java調用Oracle預存程序

標籤：jdb   url   varchar   ring   final   catch   result   create   except   

一、需求

        傳入一個參數，返回多條記錄（列表）。

 

二、實現步驟

　　1. 編寫Oracle預存程序。

-- 聲明包和包體的語句應該分兩次執行，即使聲明在前定義在後，如果一起執行依然會編譯出錯-- 調用能夠返回多條記錄的預存程序需要定義在包內--聲明包及包內的遊標、預存程序CREATE OR REPLACE PACKAGE pack_myPackage IS    TYPE myCursor IS REF CURSOR;    PROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, myCursor OUT myCursor);END myPackage;--定義包體CREATE OR REPLACE PACKAGE BODY pack_myPackage ISPROCEDURE proc_querySomeInfo(pNo IN VARCHAR2, outcursor OUT myCursor) ISBEGIN    OPEN outcursor FOR        SELECT * FROM tableA WHERE P_NO = pNo;END proc_querySomeInfo;END pack_myPackage;

　　2. 編寫Java代碼

package vip.yaocn.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;/** * 預存程序測試 * * @author yacon */public class ProcedureTest {    public static void main(String[] args) throws Exception {        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();            }        }    }}

　　3. 拓展知識

　　（1）

做一個，記一篇 — Java調用Oracle預存程序