在SQL Developer中运行存储过程? [英] Run Stored Procedure in SQL Developer?

问题描述

我正在尝试运行具有多个输入输出参数的存储过程.该过程只能在连接"面板中通过导航其他用户" |用户"来查看. |套餐| |

I am trying to run a stored procedure that has multiple in and out paramaters. The procedure can only be viewed in my Connections panel by navigating Other Users | | Packages | |

如果我单击鼠标右键，则菜单项为订购成员依据..."和创建单元测试"(显示为灰色).当用户访问该过程时，似乎无法运行"该过程.

If I right click , the menu items are "Order Members By..." and "Create Unit Test" (greyed out). The ability to "Run" the procedure does not seem possible when it's accessed by user.

我一直在尝试找到一个示例，该示例说明如何创建匿名块，以便可以将过程作为SQL文件运行，但没有找到任何有效的方法.

I have been trying to find an example of how to create an anonymous block so that I can run the procedure as a SQL file, but haven't found anything that works.

有人知道我如何从SQL Developer执行此过程吗?我正在使用2.1.1.64版.

Does anyone know how I can execute this procedure from SQL Developer? I am using Version 2.1.1.64.

提前谢谢！

我要调用的过程具有以下签名:

The procedure I want to call has this signature:

user.package.procedure(
   p_1 IN  NUMBER,
   p_2 IN  NUMBER,
   p_3 OUT VARCHAR2,
   p_4 OUT VARCHAR2,
   p_5 OUT VARCHAR2,
   p_6 OUT NUMBER)

如果我这样写我的匿名代码块:

If I write my anonymous block like this:

DECLARE
   out1 VARCHAR2(100);
   out2 VARCHAR2(100);
   out3 VARCHAR2(100);
   out4 NUMBER(100);
BEGIN
   EXECUTE user.package.procedure (33,89, :out1, :out2, :out3, :out4);
END;

我得到了错误:

Bind Varialbe "out1" is NOT DECLCARED
anonymous block completed

我尝试初始化out *变量:

I've tried initializing the out* variables:

   out1 VARCHAR2(100) := '';

但出现相同的错误:

根据亚历克斯的回答，我尝试从参数前面移除结肠并得到:

Based on Alex's answer, I tried removing the colons from in front of the params and get this:

Error starting at line 1 in command:
DECLARE
   out1 VARCHAR2(100);
   out2 VARCHAR2(100);
   out3 VARCHAR2(100);
   out4 NUMBER(100);
BEGIN
   EXECUTE user.package.procedure (33,89, out1, out2, out3, out4);
END;
Error report:
ORA-06550: line 13, column 17:
PLS-00103: Encountered the symbol "USER" when expecting one of the following:

   := . ( @ % ; immediate
The symbol ":=" was substituted for "USER" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

推荐答案

使用简单的参数类型(即非refcursors等)，您可以执行以下操作:

With simple parameter types (i.e. not refcursors etc.) you can do something like this:

SET serveroutput on;
DECLARE
    InParam1 number;
    InParam2 number;
    OutParam1 varchar2(100);
    OutParam2 varchar2(100);
    OutParam3 varchar2(100);
    OutParam4 number;
BEGIN
    /* Assign values to IN parameters */
    InParam1 := 33;
    InParam2 := 89;

    /* Call procedure within package, identifying schema if necessary */
    schema.package.procedure(InParam1, InParam2,
        OutParam1, OutParam2, OutParam3, OutParam4);

    /* Display OUT parameters */
    dbms_output.put_line('OutParam1: ' || OutParam1);
    dbms_output.put_line('OutParam2: ' || OutParam2);
    dbms_output.put_line('OutParam3: ' || OutParam3);
    dbms_output.put_line('OutParam4: ' || OutParam4);
END;
/

---

已编辑以使用OP的规范，以及使用:var绑定变量的另一种方法:

---

Edited to use the OP's spec, and with an alternative approach to utilise :var bind variables:

var InParam1 number;
var InParam2 number;
var OutParam1 varchar2(100);
var OutParam2 varchar2(100);
var OutParam3 varchar2(100);
var OutParam4 number;

BEGIN
    /* Assign values to IN parameters */
    :InParam1 := 33;
    :InParam2 := 89;

    /* Call procedure within package, identifying schema if necessary */
    schema.package.procedure(:InParam1, :InParam2,
        :OutParam1, :OutParam2, :OutParam3, :OutParam4);
END;
/

-- Display OUT parameters
print :OutParam1;
print :OutParam2;
print :OutParam3;
print :OutParam4;

这篇关于在SQL Developer中运行存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！