ruby调用oracle存储过程实例之存储过程实例化

控制层请求：

@radius = Procedure::AuthUserStatQry.new.query_user_stat(params, sort, page)

模型层：

class Procedure::AuthUserStatQry < Procedure::Base
  #存储过程名称
  set_procedure_name "WLAN_AUTH_USER_STAT_QRY"
  #存储过程参数
  set_procedure_params [:netloc_gran, :startdate, :enddate,
    :time_gran, :hourlist, :top_n, :p_order,
    :p_curpage, :p_pagesize, :p_totalrecords, :p_totalpages, :cursor]

  def initialize(options={})
    super options
    set_param_values({:top_n => "", :p_order => "", :p_curpage => 0, :p_pagesize => 30, :p_totalrecords => 0, :p_totalpages => 0})
    self.cursor = DBI::StatementHandle
  end

  def query_user_stat(params, sort, page)
    set_param_values({
          :netloc_gran => 0,
          :startdate => params[:begin_time],
          :enddate => params[:end_time],
          :time_gran => params[:time_gran],
          :p_pagesize => page[:page_size]})
    attach_sort(sort)
    self.p_curpage = page[:page]
    self.execute
  end
end

存储过程Procedure::Base连接封装：

require 'rubygems'
require 'dbi'
module Procedure
end
class Procedure::Base
  include DbQuery
  attr_reader :results, :column_info, :total_count
  def initialize(options)    
    set_param_values options
    @attributes = {}
  end

  def query(options)
    set_param_values options
    execute
  end

  def attach_sort(sort)
    if(!sort.nil? && sort[:name])
      self.p_order = "order by \"#{sort[:name]}\" #{sort[:direction]}"
    end
  end

  def execute1
    records = []
    with_db_ora do |dbh|
      sth_db = dbh.prepare query_str
      bind_params sth_db, query_options
      sth_db.execute
      sth = sth_db.func(:bind_value, ":cursor")
      unless sth.nil?
        column_info = sth.column_info
        results = sth.fetch_all
        @total_count = sth_db.func(:bind_value, ":p_totalpages")
        results.each do |r|
          records << instantiate(column_info, r)
        end
      end
    end
    records
  end

  def execute
    with_db_ora do |dbh|
      sth_db = dbh.prepare query_str
      bind_params sth_db, query_options
      ActiveRecord::Base.logger.info("Procedure[#{query_str}]")
      ActiveRecord::Base.logger.info("Execute procedure[#{procedure_name}]:{#{query_options.join(",")}}")
      sth_db.execute
      sth = sth_db.func(:bind_value, ":cursor")
      unless sth.nil?
        @column_info = sth.column_info
        @results = fetch_result sth_db, sth
        @total_count = sth_db.func(:bind_value, ":p_totalrecords")
      else
        @results = fetch_result sth_db, sth
      end
    end
    self
  end

  def set_param_values(params)
    params.each{|k, v|
      self.send((k.to_s + "=").to_sym, v)
    }
  end

  def param_values
    values = []
    options = query_options
    self.params.each do |param|
      values << options[param]
    end
    values
  end

  def method_missing(m, *args, &block)
    @attributes[m.to_s]
  end

  def [](attr_name)
    @attributes[attr_name.to_s]
  end

  protected
  def instantiate(column, record)
    object = self.class.new
    attributes = {}
    column.each_index do |i|
      attributes[column[i].name.downcase] = record[i]
    end
    object.instance_variable_set("@attributes", attributes)
    object
  end

  def fetch_result(sth_db, sth)
    sth.fetch_all unless sth.nil?
  end

  def set_query_collection_params
    self.index_sys = 1
    self.time_gran = 7
    self.cursor = DBI::StatementHandle
  end

  def query_options
    options = {}
    self.params.each do |param|
      options[param] = self.send(param.to_sym)
    end
    options
  end

  def query_str
    param_str = ""
    self.params.each do |param|
      param_str = param_str + ":" + param.to_s + ","
    end
    "BEGIN #{procedure_name}(#{param_str[0, param_str.length - 1]});END;"
  end

  class << self
    def set_procedure_name(value)
      define_method(:procedure_name) { value }
    end

    def set_procedure_params(value)
      define_method(:params) { value }
      value.each{|param| attr_accessor param.to_sym }
    end
  end
end

需要添加DBI gem包
gem install dbi
DBI连接池创建模块include DbQuery相关代码：

require 'rubygems'
require 'dbi'

module DbQuery
  def bind_params(sth_db, options)
    return if(sth_db.nil? || options.nil?)
    options.each_key { |key| sth_db.bind_param(":#{key.to_s}", options[key]) }
  end

  def with_db_ora
    #$CFG为全局常量，指向databases.yml相关oracle配置参数
    userName = $CFG['username']
    password = $CFG['password']
    database = $CFG['database']
    database = "DBI:OCI8:#{database}"

    dbh = DBI.connect(database,userName,password)
    begin
      yield dbh
    ensure
      dbh.disconnect
    end
  end
end

最后一点小贴士：通过DBI可以直接创建多数据库的连接

DbStyleHashs = {"mysql"=>"DBI:Mysql","oracle"=>"DBI:OCI8"} #由dbi扩充支持其他库类
  #数据库参数获取
  def db_connects(style=nil,d=nil,u=nil,p=nil) 
       @userName = u
      @password = p
      database =  d
      @database = "#{DbStyleHashs[style]}:#{database}"
      @dbh = DBI.connect(@database,@userName,@password)
  end