如何从Rails中的MySQL存储过程获取输出参数?

本文介绍了如何从Rails中的MySQL存储过程获取输出参数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试从MySQL存储过程获取输出参数，让我们看下面的示例:

I'm trying to get a output parameter from MySQL stored procedure, let's have look a example below:

1在mysql中，我创建了此过程，并且可以正常工作.

1 In mysql I created this procedure and it works.

CREATE PROCEDURE sp_name (out id int) Begin select id into @id from table order by id desc limit 1; End mysql> call sp_deduct_credit_and_money(@id); Query OK, 0 rows affected (0.01 sec) mysql> select @id; +--------------+ | @id | +--------------+ | 24 | +--------------+ 1 row in set (0.00 sec)

2因此，它也可以在Rails中使用，但是不会为我返回任何值:

2 Therefore, it also works in Rails, BUT it won't return any value for me:

ActiveRecord::Base.connection.execute("call sp_name(@id)") ActiveRecord::Base.connection.execute("select @id")

这是Rails中的日志

Here is the log in Rails

(2.0ms) call sp_name(@id) (0.1ms) select @id

我的问题是，如何获取输出参数@id的返回值?

My question is that how could I get the return value of the output parameter @id?

推荐答案

我找到了一种获取输出参数，与大家共享的方法，如下所示:

I found a way to get the output parameter, to share with you guys, see below:

1创建连接

client = Mysql2::Client.new(ActiveRecord::Base.configurations[Rails.env.to_s].symbolize_keys)

2使用此连接呼叫您的sp

2 use this connection to call your sp

client.query "call sp_xxx"

3获取输出参数

result = client.query("select @output_parameter")

4返回输出

return !result.first["@output_parameter"].nil?