java 調用oracle中預存程序與調用function函數兩例

在java中調用預存程序與調用function函數類似但不相同

1.調預存程序

有幾個參數，用幾個預留位置在預存程序的（）中

 public static ArrayList Prc_Page(PageInfo page) {
        ArrayList list = new ArrayList();
        Map mp;
        Session s = null;
        Connection conn = null;
        ResultSet rs = null;
        CallableStatement proc = null;
        try {
            s = HibernateSessionFactory.getSession();
            conn = s.connection();
            proc = conn.prepareCall("{call pages.prc_page(?,?,?,?,?,?,?,?,?)}");
            proc.setString(1, page.getP_tableName());
            proc.setString(2, page.getP_strWhere());
            proc.setString(3, page.getP_orderColumn());
            proc.setString(4, page.getP_orderStyle());
            proc.setInt(5, page.getP_curPage());
            proc.setInt(6, page.getP_pageSize());
            proc.registerOutParameter(7, OracleTypes.NUMBER);
            proc.registerOutParameter(8, OracleTypes.NUMBER);
            proc.registerOutParameter(9, OracleTypes.CURSOR);
            proc.execute();
            // page.setP_totalRecords(proc.getInt("p_totalRecords"));
            // page.setP_totalPages(proc.getInt("p_totalPages"));
            // list = (ArrayList) proc.getObject("v_cur");
            page.setP_totalRecords(proc.getInt(7));
            page.setP_totalPages(proc.getInt(8));
            rs = ((OracleCallableStatement) proc).getCursor(9); // 得到輸出結果集參數
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
            while (rs.next()) {
                mp = new HashMap(numberOfColumns);
                for (int r = 1; r < numberOfColumns; r++) {
                    mp.put(rsmd.getColumnName(r), rs.getObject(r));
                }
                list.add(mp);
            }
            return list;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return list;
        } catch (Exception ex2) {
            ex2.printStackTrace();
            return list;
        } finally {
            try {
                if (proc != null) {
                    proc.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex1) {
                ex1.printStackTrace();
            }
        }
    }

2.調用函數

第一個參數為遊標，用預留位置代替，。= 而且，call 必須小寫，不能為CALL ，，函數（）中的預留位置表示的是函數所需要傳入的參數

public String getQynsxx(int start, int limit, Map<String, Object> param) {

        String pBA_ID = (String) param.get("pBA_ID");
        String pFRDM = (String) param.get("pFRDM");
        String prkrq_q = (String) param.get("prkrq_q");
        String prkrq_z = (String) param.get("prkrq_z");
        String pzsxm_dm = (String) param.get("pzsxm_dm");
        System.out.println("---------日期起------"+prkrq_q.toString()+"----------------");
        System.out.println("--------日期止-------"+prkrq_z.toString()+"----------------");
        System.out.println("--------BA_ID-------"+pBA_ID+"----------------");
        System.out.println("--------法人代碼-------"+pFRDM+"----------------");
        System.out.println("--------徵收項目代碼-------"+pzsxm_dm+"----------------");
        Connection conn = null;
        ResultSet rs = null;
        CallableStatement proc = null;
        try {
            //獲得串連
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= dao.getMyDataSource().getConnection();

           proc = conn.prepareCall("{? = call FUC_SWN_GET_QYNSXX(?,?,?,?,?)}");
            
           proc.registerOutParameter(1, OracleTypes.CURSOR);
            proc.setString(2, pBA_ID);//BA_ID
            proc.setString(3, pFRDM);          
            proc.setString(4, prkrq_q);    
            proc.setString(5, prkrq_z);    
            proc.setString(6, pzsxm_dm);    
            proc.execute();
            // page.setP_totalRecords(proc.getInt("p_totalRecords"));
            // page.setP_totalPages(proc.getInt("p_totalPages"));
            // list = (ArrayList) proc.getObject("v_cur");
//            String testSql = proc.getString(5);
            rs=(ResultSet)proc.getObject(1);
            int i =0;
            StringBuffer result = new StringBuffer();
            StringBuffer rjson = new StringBuffer();
            while(rs.next()){
                i++;
        //        System.out.println(rs.getString("FRDM")+"---"+rs.getString("TYSHXYDM")+"--"+rs.getString("QYMC")+"--"+rs.getString("QYZT"));
                rjson.append("{");
                rjson.append("\"ND\":\""+rs.getInt("ND")+"\",");
                rjson.append("\"NSE\":\""+rs.getString("NSE")+"\",");
                rjson.append("\"ZSXM\":\""+rs.getString("ZSXM")+"\"");
                rjson.append("},");
            }
            if(i!=0){
                rjson.deleteCharAt(rjson.length()-1);
            }
            result.append("{\"totalcount\":\""+i+"\",\"list\":[");
            result.append(rjson);            
            result.append("]}");
            
            return result.toString();
        }
            catch(Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                if (proc != null) {
                    try
                    {
                        proc.close();
                    } catch (SQLException e)
                    {
                        e.printStackTrace();
                    }
                }
               
                if (conn != null) {
                    try
                    {
                        conn.close();
                    } catch (SQLException e)
                    {
                        e.printStackTrace();
                    }
                }
            }
        return "wrong";
    }