oracle入門（7）——預存程序

標籤：style   blog   color   java   os   strong   io   資料   

【本文介紹】

熟悉了PL/SQL文法後，實現java調用oracle預存程序才是主要目的。本文將介紹如何寫預存程序，java如何調用預存程序。

 

【預存程序介紹】

拋開專業的描述，預存程序就是在資料庫裡面寫了一些函數，我們在代碼（如java）裡面調用這些函數實現對資料庫的操作，避免了資料庫對SQL語句的解析，對於需要發送多條SQL語句才能完成的資料庫操作功能來說，速度上升了一個檔次。不過程式在操縱資料庫這一塊 的維護性會降低，因為預存程序是寫在資料庫，不是寫在程式裡。

 

【如何寫有能傳值 並且 有傳回值(非返回列表)預存程序】

　　非返回列表的預存程序比較容易，直接在函數名後面帶參數就好，同時賦予參數的類型。

　　下面舉一個例子：

預存程序代碼：

CREATE OR REPLACE procedure serachUserMethod(para1 IN VARCHAR2, para2 OUT VARCHAR2)asBEGINselect "user"."name" into para2 from "user" where "user"."name"=para1;end;

java代碼：

package com.zjm.www.test; import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import java.util.Date; import org.junit.After;import org.junit.Before;import org.junit.Test; public class test {         private static String driverclass="oracle.jdbc.driver.OracleDriver";      // 本地    private static String url="jdbc:oracle:thin:@localhost:1521:orcl";      private static String username="test";      private static String password="Aaa38324836";       private static String sql="";         private static Connection conn = null;    private static Statement stmt = null;    private static ResultSet rs = null;    private static CallableStatement proc = null;;      @Before    public void before(){        try {            Class.forName(driverclass).newInstance(); //載入驅動              conn=DriverManager.getConnection(url,username,password); //獲得串連            stmt=conn.createStatement();          } catch (Exception e) {            e.printStackTrace();        }             }         @After    public void after(){        try {            if(conn != null){                conn.close();            }        } catch (Exception e) {            e.printStackTrace();        }        try {            if(stmt != null){                stmt.close();            }        } catch (Exception e) {            e.printStackTrace();        }        try {            if(rs != null){                rs.close();            }        } catch (Exception e) {            e.printStackTrace();        }             }         /**     * 測試調用預存程序用時     * @throws SQLException      */    @Test    public void getDateByMethod(){                 int pre = (int) System.currentTimeMillis();                 try {
　　　　　　　// 調用預存程序，問號個數代表參數預存程序的個數，且順序要一一對應            proc = conn.prepareCall("{ call serachUserMethod(?,?) }");
　　　　　　　// 傳值            proc.setString(1, "AAAFB7E4B4D14475AD994310EF62EBA7");
　　　　　　　// 註冊傳回值            proc.registerOutParameter(2, Types.VARCHAR);
　　　　　　　// 提交            proc.execute();
　　　　　　  // 取出傳回值            System.out.println(proc.getString(2));                    } catch (SQLException e) {            e.printStackTrace();        }　finally {

　　　　　　　　proc.close();

　　　　　}　                 int post=(int) System.currentTimeMillis();                 System.out.println("測試調用預存程序用時"+(post-pre));    }     }

　　注意，這裡的proc.getString(2)中的數值2並非任意的，而是和預存程序中的 參數 列對應的，如果out是在第一個位置，那就是proc.getString(1)，如果是第三個位置，就是proc.getString(3)，當然也可以同時有多個傳回值，那就是再多加幾個out參數了。

 

【返回列表的預存程序】

　　通過上面的例子，返回普通的int ,Stirng 類型的參數還是沒問題的，可是這永遠不能滿足需求，我們一般要的是返回資料庫裡一個表的所有屬性？當然可以。

第一步：建包（遊標）。

　　預存程序是不能直接返回一個對象（這個對象有N個屬性）的，但它有另外一種方式，通過建立一個”指標“一樣的”遊標“ 指向某些資料，最後返回這個 ”遊標“ ，我們就能順著這個遊標拿到我們想要的資料了。

create or replace package testpackage as    type Test_CURSOR is ref cursor;   end testpackage; 

 

第二步：建立預存程序。

　　注意：返回參數的類型為我們剛剛建立的 ”遊標“ 類型

create or replace procedure testc(p_cursor out testpackage.Test_CURSOR)   is   begin    open p_cursor for select * from T_AP_ZA_LYT_GNLK;   end  testc;  

第三步：java代碼：

package com.zjm.www.test; import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import java.util.Date; import org.junit.After;import org.junit.Before;import org.junit.Test; public class test {         private static String driverclass="oracle.jdbc.driver.OracleDriver";      // 本地    private static String url="jdbc:oracle:thin:@localhost:1521:orcl";      private static String username="test";      private static String password="Aaa38324836";       private static String sql="";     // 記得表名要用""括起來    private static Connection conn = null;    private static Statement stmt = null;    private static ResultSet rs = null;    private static CallableStatement proc = null;;      @Before    public void before(){        try {            Class.forName(driverclass).newInstance(); //載入驅動              conn=DriverManager.getConnection(url,username,password); //獲得串連            stmt=conn.createStatement();          } catch (Exception e) {            e.printStackTrace();        }             }         @After    public void after(){        try {            if(conn != null){                conn.close();            }        } catch (Exception e) {            e.printStackTrace();        }        try {            if(stmt != null){                stmt.close();            }        } catch (Exception e) {            e.printStackTrace();        }        try {            if(rs != null){                rs.close();            }        } catch (Exception e) {            e.printStackTrace();        }             }    @Test    public void getDateByMethod2(){        int pre = (int) System.currentTimeMillis();                 try {            proc = conn.prepareCall("{ call testc(?) }");
　　　　　　　// 註冊傳回值參數，注意是遊標類型            proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);            proc.execute();
　　　　　　　// 返回的資料存放區在一個ResultSet裡面             ResultSet rs = (ResultSet)proc.getObject(1);                   while(rs.next())                     {   
　　　　　　　　　　　　　　// getString(...)裡面填的對應資料庫 表 的欄位名                      System.out.println("<tr><td>" + rs.getString("id") + "</td><td>"+rs.getString("name")+"</td></tr>");                     }              proc.close();        } catch (SQLException e) {            e.printStackTrace();        }                 int post=(int) System.currentTimeMillis();                 System.out.println("測試調用預存程序用時"+(post-pre));    }     }