如何通过单击视图中的按钮在rails中调用PLSQL函数? [英] How can I call a PLSQL function in rails, by clicking a button in a view?

问题描述

我在Rails中遇到了一个无法解决的问题.我几乎是一个初学者，但我必须解决以下问题:

I have encountered a problem in Rails, which I cannot solve. I am pretty much a beginner, yet I must solve the following problem:

我需要在现有的Rails Web应用程序中构建一种仪表板页面. 通过提供2个日期并单击一个按钮，该仪表板页面应该能够从Oracle服务器启动PLSQL过程.

I need to build a type of dashboard page in an existing rails web app. This dashboard page should be able to launch PLSQL procedures from an Oracle server, by providing 2 dates and clicking a button.

我尝试在rails console中使用ruby-plsql gem，并且能够成功调用所需的Oracle存储过程.

I tried using the ruby-plsql gem in rails console, and I am able to successfully call the wanted Oracle stored procedure.

我想创建一个带有窗体的视图，该窗体接受存储过程的日期字段，然后在用户单击提交"按钮时执行存储过程. ruby-plsql方法应在提交时启动.我还需要确保在当前过程正在运行时，可以通过将提交"按钮显示为灰色，以确保不会再次启动已经运行的过程.

I would like to create a view with a form to accept the date fields for the stored procedure, and then execute the stored procedure when the user clicks the submit button. The ruby-plsql method should get launched on submit. I also need to make sure that an already running procedure is not launched again, possibly by greying out the submit button, while the current procedure is running.

如何开发?

推荐答案

将ruby-plsql和'haml'宝石添加到Gemfile:

Add the ruby-plsql and 'haml' gems to your Gemfile:

gem 'ruby-plsql'
gem 'haml'

接下来，您可以使用Rails生成器充实控制器和支持组件:

Next, you can use the Rails generator to flesh in a controller and the supporting components:

rails generate controller procedure execute

在config/routes中，将get路由更新为post路由:

In the config/routes, update the get route to be a post route:

get 'procedure.execute'
post 'procedure/execute'

您可以创建一个简单的视图(此示例使用HAML).将此代码添加到文件app/views/procedure/execute.html.haml:

You can make a simple view (this example uses HAML). Add this code to the file app/views/procedure/execute.html.haml:

%div
  %p#notice
    = "A stored procedure is currently running.  Please wait..." if @is_running

%div
  = field_set_tag "Procedure 1" do
    = form_tag procedure_execute_path, id: "form1" do
      = hidden_field_tag "proc", "stored_proc_1"
      = text_field_tag "date1", nil
      = text_field_tag "date2", nil
      = submit_tag "Execute", disabled: @is_running

%div
  = field_set_tag "Procedure 2" do
    = form_tag procedure_execute_path, id: "form2" do
      = hidden_field_tag "proc", "stored_proc_2"
      = text_field_tag "string1", nil
      = text_field_tag "number1", nil
      = submit_tag "Execute", disabled: @is_running

%div#results
  = @results if @results

%script
  $(document).on("ready", register_ajax);
  $(document).on("page:change", register_ajax);

确保向/app/assets/javascripts/application.js添加jQuery和Unobtrusive Javascript支持:

Make sure to add jQuery and Unobtrusive Javascript support to /app/assets/javascripts/application.js:

//= require jquery
//= require jquery_ujs

验证您的config/application.rb是否在Application`类中包含以下行:

Verify that your config/application.rb contains this line within the Application` class:

config.assets.enable = true

请注意，这些表单现在确定将调用哪个方法，并且它们都将带有不同参数的请求发送到同一execute动作.

Note that the forms now determine which method will be called, and they both send the request with their distinct arguments to the same execute action.

用于异步提交表单并处理结果的Ajax代码可以添加到/app/assets/javascripts/procedure.js文件中:

The Ajax code to submit the forms asynchronously and handle the results can be added to the /app/assets/javascripts/procedure.js file:

var ajax_registered = false;

function register_ajax() {
    if (ajax_registered) {
      return;
    }

    $('#form1 input[type="submit"], #form2 input[type="submit"]').click(function () {
        // First, disable all of the form buttons and put up a "running" notice
        $('form input[type="submit"]').prop("disabled", "disabled");
        $("#notice").text("A stored procedure is currently running.  Please wait...")

        var data = $(this).parent().serialize();

        // Submit the Ajax POST
        var jqxhr = $.post("/procedure/execute", data, function(data, status, xhr) {
            // Success: display the results in the #results div
            $("#results").text(data);
        }, "text")
        .fail(function(data, status, xhr) {
            // Notify the user that an error has occurred
            alert("An error has occured with the stored procedure");
        })
        .always(function(data, status, xhr) {
            // Always re-enable the submit buttons after completion
            $('form input[type="submit"]').prop("disabled", "");
            $("#notice").text("");
        });
    });

    ajax_registered = true;
};

接下来，在ProcedureController中实施execute操作，该操作应位于app/controllers/procedure_controller.rb:

Next, implement the execute action in the ProcedureController, which should be located at app/controllers/procedure_controller.rb:

class ProcedureController < ApplicationController
  @@running = false

  def execute
    if request.post?
      plsql.connection = OCI8.new("hr","hr","xe")
      notice = nil
      if @@running
        notice = "A stored procedure is currently running.  Try again later"
      else
        @@running = true
        proc_params = execute_params
        proc = proc_params.delete(:proc)
        case proc
        when "stored_proc_1"
          @results = plsql.my_stored_proc(proc_params)
        when "stored_proc_2"
          @results = plsql.my_other_stored_proc(proc_params)
        end
        @@running = false
      end
      if request.xhr?
        render text: @results.to_json and return
      else
        @is_running = @@running
        redirect_to procedure_execute_path, notice: notice
      end
    else
      @is_running = @@running
    end
  end

private

  def execute_params
    params.permit(:utf8, :authenticity_token, :proc, :date1, :date2, :string1, :number1)
  end
end

execute操作是同步运行的，但也可以使用@@ running标志来表明它当前正在处理请求，以防万一在当前运行的proc完成之前仪表板中出现了另一个请求.表单中的proc隐藏字段用于指示要执行的存储过程，并且控制器根据选择的存储过程进行调度.

The execute action runs synchronously, but also uses the @@running flag to indicate that it's currently handling a request, in case another comes in from the dashboard before the currently-running proc has finished. The proc hidden field in the forms is used to indicate which stored procedure to execute, and the controller dispatches according to which is chosen.

如果请求来自Ajax调用，则响应将是一个JSON对象，其中包含存储过程调用的结果；否则，页面将被完全渲染并包含结果.

If the request came from an Ajax call, the response will be a JSON object that contains the results of the stored procedure call; otherwise, the page will be fully rendered and the result will be included.

那应该足够了.它虽然不漂亮，但是结构已经存在，您可以改进execute方法来做其他事情，并根据自己的喜好对视图进行样式设置.

That should be enough to get going. It's not pretty, but the structure is there, and you can improve the execute method to do additional things, as well as style the view to your liking.

这篇关于如何通过单击视图中的按钮在rails中调用PLSQL函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！