问题描述
我正在尝试运行具有多个输入输出参数的存储过程.该过程只能在连接"面板中通过导航其他用户" |用户"来查看. |套餐| |
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中运行存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!