ruby调用oracle存储过程实例之直接赋值参数调用_Ruby_编程开发_程序员俱乐部

class="java">
#请求存储过程，其中option为页面传过来的参数选项。
db_read_str = 'BEGIN WLAN_ALARM_QRY(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); END;'
stat_alarm_procedure(db_read_str,option)

 def stat_alarm_procedure(db_read_str, option = nil)
    date_format = '%Y-%m-%d'
    start_date = option["startdate"]
    end_date = option["enddate"]
    with_db_ora do |dbh|
      sth_db = dbh.prepare(db_read_str)
      sth_db.bind_param(1, option["netloc_gran"])
      sth_db.bind_param(2, option["netloc"])
      sth_db.bind_param(3, option["device_class"])
      sth_db.bind_param(4, option["dic_group"])
      sth_db.bind_param(5, start_date.strftime(date_format)) #"to_date('#{start_date.strftime(date_format)}','yyyy-mm-dd')"
      sth_db.bind_param(6, end_date.strftime(date_format)) #"to_date('#{end_date.strftime(date_format)}','yyyy-mm-dd')"
      sth_db.bind_param(7, option["cityid"])
      sth_db.bind_param(8, option["page"])
      sth_db.bind_param(9, option["size"])
      sth_db.bind_param(10, 9)
      sth_db.bind_param(11, 3)
      sth_db.bind_param(12, DBI::StatementHandle)#to bind ref cursor as DBI::StatementHandle
      sth_db.execute
      
      @total = sth_db.func(:bind_value, 10)
      sth = sth_db.func(:bind_value, 12)
      @schema = sth.column_info
      @results = sth.fetch_all
    end
    schema_info = Array.new
    @schema.each do |s|
      schema_info << s["name"] unless s["name"].downcase == 'r'
    end
    {:total => @total, :data => {:schema => schema_info, :result => @results}}
  end