java 使用jdbc调用 oracle 存储过程

 

Java 调用 oracle 存储过程三个实例（看前知识，需要存储过程，jdbc相关一点点）

 

1.插入，无返回值

 

oracle 的 procedure 为

 

create or replace procedure add_project(proid      varchar2,  
                                        proversion number,  
                                        proname    varchar2,  
                                        customID varchar2 default null) is  
begin  
  insert into T_LPROMIS_XMGL_GBBXMXX pro  
    (xmid, xmbbh, xmmc,pro.khid)  
  values  
    (proid, proversion, proname,customID);  
end;  

 2.查询，有一个out varchar的返回值

 

oracle 的 procedure 为

 

create or replace procedure query_project  
(proname in varchar2, proid out varchar2)  
is   
begin  
  select pro.xmid into proid from T_LPROMIS_XMGL_GBBXMXX pro  
where pro.xmmc = proname;  
end;  

 3.查询，有一个out ref cursor的返回值

 

oracle 的 procedure 为

 

create or replace package query_project_pck  
as   
type pro_ref_cursor_type is ref cursor;  
type pro_table is table of varchar(100);  
type pro_record is record(name varchar(100),id varchar(100));  
procedure pro_procedure(name varchar,id varchar);  
end query_project_pck; -- define package  
  
create or replace procedure query_pro(pro_result out query_project_pck.pro_ref_cursor_type) is  
begin  
  open pro_result for  
    select pro.xmid, pro.xmmc,pro.updatetime from T_LPROMIS_XMGL_GBBXMXX pro return pro_record_type;  
end; --create procedure using ref cursor

 以上为oracle数据库

 

下面就是jdbc call procedure 了，上代码：

 

public static void test() throws ClassNotFoundException, SQLException{  
        System.out.println("=====Test.test=====");  
        Class.forName("oracle.jdbc.driver.OracleDriver");  
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.32.227:1521:orcl","use","password");  
        //Connection conn = new Conn().getConnection();  
        CallableStatement cstmt = null;  
        ResultSet rs = null;  
        try{  
              
            // 过程调用，无返回值  
            cstmt = conn.prepareCall("call add_project(?,?,?)");  
            cstmt.setString(1,"testproject1");  
            cstmt.setInt(2, 0);  
            cstmt.setString(3, "super man project");  
            cstmt.execute();  
            cstmt.setString(1, "testproject2");  
            cstmt.setInt(2, 0);  
            cstmt.setString(3, "a worker project");  
            cstmt.execute();  
            cstmt.setString(1, "testproject3");  
            cstmt.setInt(2, 0);  
            cstmt.setString(3, "a user project");  
            cstmt.execute();  
            System.out.println("插入成功");  
              
            cstmt = conn.prepareCall("call query_project(?,?)");  
            cstmt.registerOutParameter(2,Types.VARCHAR);  
            cstmt.setString(1, "全网项目");  
            cstmt.execute();  
            System.out.println(cstmt.getString(2));  
            cstmt = conn.prepareCall("call query_pro(?)");  
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);  
            cstmt.execute();  
            rs = (ResultSet) cstmt.getObject(1);  
            while(rs.next())  
            {  
                  
                System.out.println("id= "+rs.getString(1));  
                System.out.println("name="+rs.getString(2));  
                System.out.println("msg="+rs.getString(3));  
            }  
              
            if(!rs.isClosed())  
                rs.close();  
            cstmt.close();  
            conn.close();  
        }catch(Exception e){  
            System.out.println("=====Test.test=====\n操作失败");  
            e.printStackTrace();  
        }  
    }