RoR 調用 Oracle Function/Procedure

1.簡單的例子:

 

=begin
--創建Oracle Function
create or replace function FUN_TEST(P_STR VARCHAR2) return varchar2 is
  Result varchar2(100);
begin
  Result := P_STR||'-OK! FUNCTION EXECUTED.';
  return(Result);
end FUN_TEST;
=end

require 'oci8'

conn = OCI8.new("hr","hr","192.168.0.1/sid")
cursor = conn.parse <<-EOS
BEGIN
  :return := FUN_TEST(:p_string);
END;
EOS
cursor.bind_param(':p_string',"xxx",String)
cursor.bind_param(':return',nil,String,4000)
cursor.exec
puts cursor[':return']
cursor.close

 

2.Module 的例子: 

# activerecord.rb: Using ActiveRecord, the ORM module
require 'rubygems'
require 'active_record'

# Establish a connection to Oracle
ActiveRecord::Base.establish_connection(
	:adapter => "oci",
	:host    => '192.168.0.1/sid',
	:username => 'hr',
	:password => 'password' )
	
module SQLStatement

class NoTypeResult < StandardError
end # class

class SQLProc

attr_reader :outs
attr_accessor :name, :arguments

def initialize( args = nil )
@name = args[:name] unless args[:name].nil?
@arguments = args[:arguments] unless args[:arguments].nil?
@conn = ActiveRecord::Base.connection.raw_connection
end # def initialize

def exec( args = nil )
unless args.nil? || ( !args.is_a? Hash )
args.each { |k, v| @arguments[k.to_s.intern] = args[k] }
end # unless
func_args = ''
@arguments.each { |k, v| func_args += "#{func_args == '' ? '' : ', '}#{k.to_s} => :#{k.to_s}"}
sql = "BEGIN #{@name}( #{func_args} );END;"
cursor = @conn.parse( sql )
@arguments.each do |k, v|
cursor.bind_param( ":#{k.to_s}", v)
end # each
cursor.exec()
#outs
@outs = []
@arguments.each do |k, v|
  @outs <<  cursor[":#{k.to_s}"]
end # each
cursor.close
end # def exec

end # class SQLProc

class SQLFunc < SQLProc

attr_reader :result
attr_accessor :result_type,:result_length

def initialize( args = nil )
  @result_type = args[:result_type] unless args[:result_type].nil?
  @result_length = args[:result_length] unless args[:result_length].nil?
  super( args )
end # def

def exec( args = nil )
if @result_type.nil?
raise NoTypeResult, 'No type for result setting', caller
end # if
unless args.nil? || ( !args.is_a? Hash )
args.each { |k, v| @arguments[k.to_s.intern] = args[k] }
end # unless
func_args = ''
@arguments.each { |k, v| func_args += "#{func_args == '' ? '' : ', '}#{k.to_s} => :#{k.to_s}"}
sql = "BEGIN :result := #{@name}( #{func_args} );END;"
cursor = @conn.parse( sql )
@arguments.each do |k, v|
cursor.bind_param( ":#{k.to_s}", v )
end # each
#cursor.bind_param( ":result", nil, @result_type )
#cursor.bind_param(":result",Fixnum)
#cursor.bind_param( ":result", nil, String,100 )
cursor.bind_param( ":result", nil, @result_type,@result_length )
cursor.exec()
@result = cursor[':result']
cursor.close
end # def exec

end # class

end # module SQLStatment

#Example of use:
=begin
CREATE OR REPLACE PROCEDURE pro_test(p1 in varchar2, p2 out varchar2) is
BEGIN
  select p1 || '-OK! PROCEDURE EXECUTED.' into p2 from dual;
end pro_test;
=end
puts 'Example of procedure:'
procedure= SQLStatement::SQLProc.new(
:name => 'pro_test',
:arguments => {
:p1 =>'ruby call',
:p2 =>' '*100}
)
procedure.exec
puts procedure.outs[1]

=begin
create or replace function FUN_TEST(P_STR VARCHAR2) return varchar2 is
  Result varchar2(100);
begin
  Result := P_STR||'-OK! FUNCTION EXECUTED.';
  return(Result);
end FUN_TEST;
=end
puts 'Example of function:'
function = SQLStatement::SQLFunc.new(
:name => 'fun_test',
:result_type => String,
:result_length => 100,
:arguments => {
:p_str =>'ruby call'}
)
function.exec
#raise function.result.inspect 
puts function.result

 

3.應用的例子:

第一步:創建應用(ROR)

rails app

第二步:配置資料連接(ROR)

#database.yml
development:
  adapter: oci
  host: (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = MYDB.MYDOMAIN.COM)))
  username: scott
  password: tiger

第三步:創建存儲過程(Oracle)

--Stored_proc.pks:
CREATE OR REPLACE PACKAGE stored_proc AS
  type row_type is record (
     name emp.ename%type,
     job emp.job%type
     );
TYPE table_type IS TABLE OF row_type;

procedure stored_proc(p_empno IN emp.empno%type, p_arr OUT table_type);
END stored_proc;
/

--Stored_proc.pkb:
CREATE OR REPLACE package body stored_proc as
procedure stored_proc(
          p_empno emp.empno%type
          , p_arr OUT table_type
          ) is
begin
     select 
     ename
     , job 
     BULK COLLECT 
      into p_arr
     from 
     emp 
     where emp.empno=p_empno; 
end;
end stored_proc;
/

 第四步:增加管道函數(Oracle)

create or replace function test_pipe(p_empno emp.empno%type default null) return stored_proc.table_type 
pipelined
is
v_arr stored_proc.table_type;
begin
    stored_proc.stored_proc(nvl(p_empno, 7369), v_arr);
         for i in v_arr.first..v_arr.last loop
              pipe row(v_arr(i));
         end loop;
         return;
end test_pipe;
/

 第五步:創建虛擬視圖(Oracle)

create or replace view samples(name, job)
as
select
    name
    , job 
    from
    table(test_pipe(null))
/

第六步:建構腳手架(ROR)

ruby script/generate controller sample
ruby script/generate model sample

 

第七步:修改控制器(ROR)

#/app/controllers/sample_controller 
class SampleController < ApplicationController
  scaffold :sample

  def sample
  sql="select name, job from table(test_pipe("+ params[:id] + "))" 
   unless params[:id].nil?
       samples=Sample.find_by_sql(sql)   
               @sample=samples[0]
   end
  end
end

第八步:創建模板(ROR)

app/views/sample/sample.rhtml

 

<h1> Name of party</h1>
<h2> <%= @sample.name+": "+ @sample.job %> </h2>

<FORM ACTION="" METHOD=GET>
<table>
    <tr><td>Party id:  </td><td><input type=text name=id></td></tr>
    <tr><td colspan=2><input type=submit value="go"></td></tr>
</table>
</form>

 

試一下：http://localhost:3000/sample/sample?id=7698