调用oracle中的过程，详解

--动态执行DDL
/*declare
  sqlType varchar2(500);
  begin
      sqlType:='create table houseType(tid number(11),typeName varchar2(50))';
      execute immediate sqlType;   
  end;*/
 
  --创建包头
 /* create or replace package p_house
         as
        
         --增加
        procedure p_insert(sid  number,tname varchar2);
         --更新
        procedure p_update(sid number,tname varchar2);
         --删除
         procedure p_delete(sid number);
         --查询所有
         procedure p_selectAll(r_result out sys_refcursor);
    end p_house;*/
   
   
--创建包体

  create or replace package body p_house
         as
        
         --增加
         procedure p_insert(sid  number,tname varchar2)
         is  
         begin     
           execute immediate
            'insert into houseType values(:sid,:tname)'
             using sid,tname;  
             commit;
         
         end p_insert;
        
         --更新
         procedure p_update(sid number,tname varchar2)
                   is
                   begin
                      
                      execute immediate
                              'update houseType set typeName=:tname where tid=:tid'
                              using tname,sid;
                            commit; 
                         
                   end p_update;
        
        
         --删除
        procedure p_delete(sid number)
         is
         begin
                  execute immediate
                          'delete from houseType where tid=:tid'
                          using sid;
                          commit;
         end p_delete;
        
         --查询所有
         procedure p_selectAll(r_result out sys_refcursor)
                   as
                   select_all varchar2(200):='select *from houseType';
                   begin
                          open r_result for select_all;
                   end;
                  
    end p_house;
   
   
  
   
        

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//实体类

 

package org.entity;

import java.io.Serializable;

public class HouseType implements Serializable {

 private Integer tid;
 private String typeName;
 public Integer getTid() {
  return tid;
 }
 public void setTid(Integer tid) {
  this.tid = tid;
 }
 public String getTypeName() {
  return typeName;
 }
 public void setTypeName(String typeName) {
  this.typeName = typeName;
 }
 
 public HouseType() {
  // TODO Auto-generated constructor stub
 }
 public HouseType(Integer tid, String typeName) {
  super();
  this.tid = tid;
  this.typeName = typeName;
 }
}

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

package org.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.entity.HouseType;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

//以下方法均是调用oracle中的过程方法
public class HouseDao {
 // 获得链接
 private Connection getConnection() throws ClassNotFoundException,
   SQLException {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  return DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:ORCL",
    "system",
    "accp");
 }

 // 更新
 public void update(int tid, String typeName) throws ClassNotFoundException,
   SQLException {

  Connection con = getConnection();
  //调用oracle中的过程 
  CallableStatement cs = con.prepareCall("{call p_house.p_update(?,?)}");
  cs.setInt(1, tid);
  cs.setString(2, typeName);
  cs.executeUpdate();
 }

 // 添加
 public void insert(int tid, String tname) throws ClassNotFoundException,
   SQLException {
  Connection con = getConnection();
  CallableStatement cs = con.prepareCall("{call p_house.p_insert(?,?)}");
  cs.setInt(1, tid);
  cs.setString(2, tname);
  cs.executeUpdate();
 }

 // 删除
 public void delete(int tid) throws ClassNotFoundException, SQLException {
  Connection con = getConnection();
  CallableStatement cs = con.prepareCall("{call p_house.p_delete(?)}");
  cs.setInt(1, tid);
  cs.executeUpdate();
 }

 // 查询所有
 public List<HouseType> selectAll() throws ClassNotFoundException,
   SQLException {
  Connection con = getConnection();
  //调用过oracle程中的方法
  CallableStatement cs = con.prepareCall("{call p_house.p_selectAll(?)}");
  
  /*
   * 替换预编译的参数，OracleTypes是oracle自带的
  * registerOutParameter是设置oralce过程中参数是输出参数OUT
  *
  */
  cs.registerOutParameter(1, OracleTypes.CURSOR);
  //执行查询
  cs.executeQuery();
  /**
   * OracleCallableStatement是oracle中自带的包
   * 需要将其强制转换
   * .getCursor(1)获得对应参数 这里指的是游标相当于resultSet结果集
   */
  ResultSet rs = ((OracleCallableStatement) cs).getCursor(1);
  List<HouseType> list = new ArrayList<HouseType>();
  //封装结果集
  while (rs.next()) {
   HouseType t = new HouseType(rs.getInt("tid"), rs
     .getString("typeName"));
   list.add(t);
  }
  return list;

 }
}