java程序調用存儲過程

    PL/SQL子程序，很多情況下是給應用程序來調用的，所有我們要掌握使用其他編程語言來調用我們寫好的存儲過程。下面我們介紹下使用java調用Oracle的存儲過程。

  準備代碼:

package com.mscncn.plsql.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    static{
        try {
            Class.forName("oracle.jdbc.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConntection(){
        Connection ct=null;
        try {
            ct = DriverManager.getConnection(
                    "jdbc:oracle:thin:@192.168.0.25:1521:oracle",
                    "scott", 
                    "scott");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ct;
    }
}

create or replace package pro_pk is
   type pager_cursor is ref cursor;
   procedure add_dept(dept_no in number,dept_name in varchar2,location in varchar2);
   procedure delete_dept(dept_no in number,num out number);
end pro_pk;

create or replace package body pro_pk is
   procedure add_dept(dept_no in number,dept_name in varchar2,location in varchar2)
     is
         exp_remaining exception;
         pragma exception_init(exp_remaining,-1);/*非預定義錯誤，-1是違反唯一約束*/
     begin 
       insert into dept values(dept_no,dept_name,location);
       if sql%found then /*隱式游標，sql*/
         return 1;
       else 
         return 0;
       end if;
     exception 
       when exp_remaining then
         dbms_output.put_line('違反唯一約束.');
     end add_dept;
       
     procedure delete_dept(dept_no in number,num out number)
       is
     begin 
         delete from dept where deptno=dept_no;
         if sql%found then 
           num:=1;
         else 
           num:=1;
         end if;
     end delete_dept;
end pro_pk;

 create or replace package pageUtil is
 
  type page_cursor is ref cursor;--定義一個游標類型
  
  procedure pager(
        tName in varchar2, --表名
        pageNum in number, --頁數
        pageSize in number,--每頁記錄數
        totalRecord out number,--總記錄數
        totalPage out number,--總頁數
        p_cursor out page_cursor);
        
end pageUtil;

create or replace package body pageUtil is 
       
    procedure pager(
        tName in varchar2, --表名
        pageNum in number, --頁數
        pageSize in number,--每頁記錄數
        totalRecord out number,--總記錄數
        totalPage out number,--總頁數
        p_cursor out page_cursor) is
        
        --定義sql語句字符串
        v_sql varchar2(1000);
        --分頁開始位置，與結束位置
        v_begin number:=(pageNum-1)*pageSize+1;
        v_end number:=pageNum*pageSize;
    begin
      v_sql:='select * from ( select t.*,rownum rn from '
        ||tName||' t where rownum<='||v_end||') where rn>='||v_begin;
      --把游標和sql關聯
      dbms_output.put_line(v_sql);
      open p_cursor for  v_sql;
      --計算totalRecord與totalPage
      v_sql:='select count(*) from '||tName;
      --
      execute immediate v_sql into totalRecord;
      if mod(totalRecord,pageSize)=0 then 
        totalPage:=totalRecord/pageSize;
      else
        totalPage:=totalRecord/pageSize+1;
      end if;
      --關閉游標,這兒需要注意，如果我們在java程序中使用cursor,那么就一定不能關閉cursor
      --否則關閉cursor后，java程序中返回的結果集就是null
      --close p_cursor;
    end pager;
end pageUtil;

1. java調用沒有返回值的存儲過程。

/**
     * java調用沒有返回值的存儲過程
     */
    @Test
    public void proNoReulstTest(){
        Connection ct=DBUtil.getConntection();
        try {
            CallableStatement cs=ct.prepareCall("{call pro_pk.add_dept(?,?,?)}");
            cs.setInt(1, 13);
            cs.setString(2, "java開發部");
            cs.setString(3, "中國信陽");
            cs.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

2. java程序調用有返回值的存儲過程

/**
     * java調用有返回值的存儲過程(返回值類型為number)
     */
    @Test
    public void proHasReulstTest(){
        Connection ct=DBUtil.getConntection();
        try {
            CallableStatement cs=ct.prepareCall("{call pro_pk.delete_dept(?,?)}");
            cs.setInt(1, 13);
            //注冊第二個參數為存儲過程的返回值
            cs.registerOutParameter(2, OracleType.STYLE_INT);
            cs.execute();
            //通過參數的索引，來獲取存儲過程的返回值，索引從1開始
            int num=cs.getInt(2);
            System.out.println(num==1?"刪除成功":"刪除失敗");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

3. java程序調用存儲過程返回值為游標

/**
     * 存儲過程返回一個游標
     */
    @Test
    public void proReturnCursorTest(){
        Connection ct=DBUtil.getConntection();
        try {
            CallableStatement cs=ct.prepareCall("{call pageUtil.pager(?,?,?,?,?,?)}");
            cs.setString(1, "emp");
            cs.setInt(2, 2);
            cs.setInt(3, 5);
            cs.registerOutParameter(4, OracleTypes.NUMBER);
            cs.registerOutParameter(5, OracleTypes.NUMBER);
            cs.registerOutParameter(6, OracleTypes.CURSOR);
            cs.execute();
            //通過參數的索引，來獲取存儲過程的返回值，索引從1開始
            int totalRecord=cs.getInt(4);
            int totalPage=cs.getInt(5);
            ResultSet rs=(ResultSet)cs.getObject(6);
            System.out.println("總記錄數為:"+totalRecord+",總頁數為:"+totalPage);
            while(rs.next()){
                System.out.println("雇員編號:"+rs.getInt("empno")+",雇員姓名:"+rs.getString("ename"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }