oracle 在 SQL Developer 中运行存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3991721/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run Stored Procedure in SQL Developer?
提问by sdoca
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.
如果我右键单击 ，菜单项是“Order Members By...”和“Create Unit Test”（变灰）。当用户访问它时，似乎不可能“运行”该过程。
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 文件运行，但没有找到任何有效的方法。
Does anyone know how I can execute this procedure from SQL Developer? I am using Version 2.1.1.64.
有谁知道我如何从 SQL Developer 执行这个过程？我使用的是 2.1.1.64 版。
Thanks in advance!
提前致谢！
EDIT 1:
编辑 1：
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;
I get the error:
我收到错误：
Bind Varialbe "out1" is NOT DECLCARED
anonymous block completed
I've tried initializing the out* variables:
我试过初始化 out* 变量：
out1 VARCHAR2(100) := '';
but get the same error:
但得到同样的错误：
EDIT 2:
编辑2：
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:
采纳答案by Alex Poole
With simple parameter types (i.e. not refcursors etc.) you can do something like this:
使用简单的参数类型（即不是 refcursors 等），您可以执行以下操作：
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;
/
Edited编辑为使用 OP 的规范，并使用另一种方法来利用
:var:var绑定变量：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;
回答by Conrad Frix
Executing easy. Getting the results can be hard.
执行容易。获得结果可能很困难。
Take a look at this question I asked Best way/tool to get the results from an oracle package procedure
看看这个问题，我问了从 oracle 包过程中获取结果的最佳方法/工具
The summary of it goes like this.
它的总结是这样的。
Assuming you had a Package named mypackage and procedure called getQuestions. It returns a refcursor and takes in string user name.
假设您有一个名为 mypackage 的包和名为 getQuestions 的过程。它返回一个 refcursor 并接受字符串用户名。
All you have to do is create new SQL File (file new). Set the connection and paste in the following and execute.
您所要做的就是创建新的 SQL 文件（file new）。设置连接并粘贴在下面并执行。
var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;
回答by Adrian Shum
For those using SqlDeveloper 3+, in case you missed that:
对于那些使用 SqlDeveloper 3+ 的人，如果你错过了：
SqlDeveloper has feature to execute stored proc/function directly, and output are displayed in a easy-to-read manner.
SqlDeveloper 具有直接执行存储过程/函数的功能，并且输出以易于阅读的方式显示。
Just right click on the package/stored proc/ stored function, Click on Runand choose targetto be the proc/func you want to execute, SqlDeveloper will generate the code snippet to execute (so that you can put your input parameters). Once executed, output parameters are displayed in lower half of the dialog box, and it even have built-in support for ref cursor: result of cursor will be displayed as a separate output tab.
只需右键单击package/stored proc/stored 函数，单击Run并选择target要执行的proc/func，SqlDeveloper 将生成要执行的代码片段（以便您输入参数）。一旦执行，输出参数显示在对话框的下半部分，它甚至内置了对引用光标的支持：光标的结果将显示为单独的输出选项卡。
回答by Noel
Open the procedure in SQL Developer and run it from there. SQL Developer displays the SQL that it runs.
在 SQL Developer 中打开该过程并从那里运行它。SQL Developer 显示它运行的 SQL。
BEGIN
PROCEEDURE_NAME_HERE();
END;
回答by OMG Ponies
Use:
用：
BEGIN
PACKAGE_NAME.PROCEDURE_NAME(parameter_value, ...);
END;
Replace "PACKAGE_NAME", "PROCEDURE_NAME", and "parameter_value" with what you need. OUT parameters will need to be declared prior to.
将“PACKAGE_NAME”、“PROCEDURE_NAME”和“parameter_value”替换为您需要的内容。OUT 参数需要事先声明。
回答by Narasimha
Though this question is quite old, I keep stumbling into same result without finding an easy way to run from sql developer. After couple of tries, I found an easy way to execute the stored procedure from sql developer itself.
尽管这个问题已经很老了，但我一直跌跌撞撞地得出相同的结果，却没有找到从 sql developer 运行的简单方法。经过几次尝试，我找到了一种从 sql 开发人员本身执行存储过程的简单方法。
Under packages, select your desired package and right click on the package name (not on the stored procedure name).
You will find option to run. Select that and supply the required arguments. Click OK and you can see the output in output variables section below
在包下，选择所需的包并右键单击包名称（而不是存储过程名称）。
你会找到运行的选项。选择它并提供所需的参数。单击确定，您可以在下面的输出变量部分看到输出
I'm using SQL developer version 4.1.3.20
我正在使用 SQL 开发人员版本 4.1.3.20
回答by vapcguy
None of these other answers worked for me. Here's what I had to do to run a procedure in SQL Developer 3.2.20.10:
这些其他答案都不适合我。以下是我在 SQL Developer 3.2.20.10 中运行过程所必须执行的操作：
SET serveroutput on;
DECLARE
testvar varchar(100);
BEGIN
testvar := 'dude';
schema.MY_PROC(testvar);
dbms_output.enable;
dbms_output.put_line(testvar);
END;
And then you'd have to go check the table for whatever your proc was supposed to do with that passed-in variable -- the output will just confirm that the variable received the value (and theoretically, passed it to the proc).
然后你必须去检查表，看看你的 proc 应该用那个传入的变量做什么——输出只会确认变量收到了值（理论上，将它传递给了 proc）。
NOTE(differences with mine vs. others):
注意（与我的与其他人的差异）：
- No
:prior to the variable name - No putting
.package.or.packages.between the schema name and the procedure name - No having to put an
&in the variable's value. - No using
printanywhere - No using
varto declare the variable
- 没有
:在变量名之前 - 没有在模式名称和过程名称之间放置
.package.或.packages. - 不必
&在变量的值中放置一个。 - 没有使用
print任何地方 - 不使用
var声明变量
All of these problems left me scratching my head for the longest and these answers that have these egregious errors out to be taken out and tarred and feathered.
所有这些问题都让我摸不着头脑，而这些答案中包含了这些令人震惊的错误，需要被删除、涂上焦油和羽毛。
回答by Hyman
Using SQL Developer Version 4.0.2.15 Build 15.21 the following works:
使用 SQL Developer 版本 4.0.2.15 Build 15.21 进行以下工作：
SET SERVEROUTPUT ON
var InParam1 varchar2(100)
var InParam2 varchar2(100)
var InParam3 varchar2(100)
var OutParam1 varchar2(100)
BEGIN
/* Assign values to IN parameters */
:InParam1 := 'one';
:InParam2 := 'two';
:InParam3 := 'three';
/* Call procedure within package, identifying schema if necessary */
schema.package.procedure(:InParam1, :InParam2, :InParam3, :OutParam1);
dbms_output.enable;
dbms_output.put_line('OutParam1: ' || :OutParam1);
END;
/
回答by AmolRavan
To run procedure from SQL developer-only execute following command
要从 SQL developer-only 运行过程，请执行以下命令
EXECUTE PROCEDURE_NAME;
执行 PROCEDURE_NAME；
回答by Amel Music
Can't believe, this won't execute in SQL Developer:
不敢相信，这不会在 SQL Developer 中执行：
var r refcursor;
exec PCK.SOME_SP(:r,
'02619857');
print r;
BUT this will:
但这将：
var r refcursor;
exec TAPI_OVLASCENJA.ARH_SELECT_NAKON_PRESTANKA_REG(:r, '02619857');
print r;
Obviously everything has to be in one line..
显然一切都必须在一行中..
