当前位置： 文江博客话题详情

在 SQL Developer 中运行存储过程？

发布于 2024-09-28 23:58:40 字数 1588 浏览 8 评论 0原文

我正在尝试运行一个具有多个输入和输出参数的存储过程。该过程只能通过导航在“连接”面板中查看

Other Users | <user> | Packages | <package> | <procedure>

。如果我右键单击，菜单项为“按...排序成员”和“创建单元测试”（灰显）。当用户访问该过程时，“运行”该过程的能力似乎不可能。

我一直在尝试寻找如何创建匿名块的示例，以便我可以将过程作为 SQL 文件运行，但尚未找到任何有效的方法。

有谁知道我如何从 SQL Developer 执行此过程？我使用的是2.1.1.64版本。

编辑1：

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

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)

如果我像这样编写匿名块：

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* 变量：

   out1 VARCHAR2(100) := '';

但收到相同的错误：

编辑2：

根据Alex的回答，我尝试删除参数前面的冒号并得到：

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:
原文

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

Other Users | <user> | Packages | <package> | <procedure>

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.

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.

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

EDIT 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:

   out1 VARCHAR2(100) := '';

but get the same error:

EDIT 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:

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（15）

缱绻入梦 2024-10-05 23:58:40

使用简单的参数类型（即不是引用游标等），您可以执行以下操作：

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 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;

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;
/

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;
回复 原文
护你周全 2024-10-05 23:58:40

执行起来很容易。获得结果可能很困难。

看看我问的这个问题 Best way/tool to get the results from an oracle package procedure

它的摘要如下像这样。

假设您有一个名为 mypackage 的包和名为 getQuestions 的过程。它返回一个引用游标并接受字符串用户名。

您所要做的就是创建新的 SQL 文件（文件新）。设置连接并粘贴以下内容并执行。

var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;

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

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.

All you have to do is create new SQL File (file new). Set the connection and paste in the following and execute.

var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;
回复 原文
瘫痪情歌 2024-10-05 23:58:40

对于那些使用 SqlDeveloper 3+ 的人，如果您错过了：

SqlDeveloper 具有直接执行存储的过程/函数的功能，并且输出以易于阅读的方式显示。

只需右键点击package/stored proc/stored function，点击Run并选择target作为你想要执行的proc/func，SqlDeveloper就会生成代码片段执行（以便您可以输入输入参数）。一旦执行，输出参数将显示在对话框的下半部分，它甚至内置了对引用光标的支持：光标的结果将显示为单独的输出选项卡。

For those using SqlDeveloper 3+, in case you missed that:

SqlDeveloper has feature to execute stored proc/function directly, and output are displayed in a easy-to-read manner.

Just right click on the package/stored proc/ stored function, Click on Run and choose target to 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.

回复 原文
我们的影子 2024-10-05 23:58:40

在 SQL Developer 中打开该过程并从那里运行它。 SQL Developer 显示它运行的 SQL。

BEGIN
  PROCEEDURE_NAME_HERE();
END;
/

Open the procedure in SQL Developer and run it from there. SQL Developer displays the SQL that it runs.

BEGIN
  PROCEEDURE_NAME_HERE();
END;
/
回复 原文
心不设防 2024-10-05 23:58:40

使用：

BEGIN

  PACKAGE_NAME.PROCEDURE_NAME(parameter_value, ...);

END;

将“PACKAGE_NAME”、“PROCEDURE_NAME”和“parameter_value”替换为您需要的内容。 OUT 参数需要事先声明。

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.

回复 原文
筱果果 2024-10-05 23:58:40

虽然这个问题已经很老了，但我不断地遇到相同的结果，而没有找到从 sql Developer 运行的简单方法。
经过几次尝试，我找到了一种从 sql Developer 本身执行存储过程的简单方法。

  • 在包下，选择所需的包，然后右键单击包名称（而不是存储过程名称）。

    在

  • 您将找到运行选项。选择它并提供所需的参数。单击“确定”，您可以在下面的输出变量部分中看到输出

我使用的是 SQL Developer 版本 4.1.3.20

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.

  • 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

回复 原文
为你拒绝所有暧昧 2024-10-05 23:58:40

这些其他答案都不适合我。以下是我在 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;

然后您必须检查表以了解您的过程应该对传入的变量执行的操作 - 输出将确认这一点变量接收到值（理论上，将其传递给过程）。

注意（与我的和其他人的差异）：

  • 变量名称之前没有 :
  • 没有放置 .package. 或 .packages。模式名称和过程名称之间的
  • 无需在变量值中放置 &。
  • 没有在任何地方使用 print
  • 没有使用 var 来声明变量

所有这些问题让我摸不着头脑，这些答案有这些严重的错误需要排除并涂上柏油和羽毛。

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:

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).

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 print anywhere
  • No using var to declare the variable

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.

回复 原文
醉城メ夜风 2024-10-05 23:58:40

不敢相信，这不会在 SQL Developer 中执行：

var r refcursor;
exec PCK.SOME_SP(:r,
 '02619857');

print r;

但是这会：

var r refcursor;
exec TAPI_OVLASCENJA.ARH_SELECT_NAKON_PRESTANKA_REG(:r, '02619857');

print r;

显然一切都必须在一行中..

Can't believe, this won't execute in 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..

回复 原文
请持续率性 2024-10-05 23:58:40

使用 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;
/

Using SQL Developer Version 4.0.2.15 Build 15.21 the following works:

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;
/
回复 原文
梦亿 2024-10-05 23:58:40

我有一个返回游标的存储过程，在我的例子中，它实际上是自定义包类型（T_CURSOR，对我来说看起来像一个约定），定义为REF CURSOR。

可能有更好的方法来做到这一点，但我为游标迭代的表的所有列定义了变量，循环游标将每一行提取到这些变量中，然后将它们打印出来。

SET serveroutput on;
DECLARE
  testvar number;
  v_cur  SYS_REFCURSOR;
  ORIGINAL_EMP_NUM  NUMBER;
  TEMPORARY_EMP_NUM NUMBER;
  ORG_UNIT_CODE VARCHAR2(2 BYTE);
  MRU_CODE  VARCHAR2(10 BYTE);
  CTRL_COMPANY_CODE VARCHAR2(10 BYTE);
  IS_TEMP_FLAG  VARCHAR2(1 BYTE);
BEGIN
  testvar := 420;
  foo.updates.get_temporary_authorisations(testvar, v_cur);
  dbms_output.enable;
  dbms_output.put_line(testvar);
  
  LOOP
    FETCH v_cur INTO ORIGINAL_EMP_NUM, TEMPORARY_EMP_NUM, ORG_UNIT_CODE, MRU_CODE, CTRL_COMPANY_CODE, IS_TEMP_FLAG;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(ORIGINAL_EMP_NUM || ',' || TEMPORARY_EMP_NUM || ',' || ORG_UNIT_CODE || ',' || MRU_CODE|| ',' || CTRL_COMPANY_CODE|| ',' || IS_TEMP_FLAG);
  END LOOP;
  CLOSE v_cur;
END;

I had a stored procedure that returned a cursor, in my case it was actually of a custom package type (T_CURSOR, looks like a convention to me) that is defined as REF CURSOR.

There may be a better way to do this, but I defined variables for all the columns of the table that the cursor was iterating, looped the cursor fetching each row into those variables, then printed them out.

SET serveroutput on;
DECLARE
  testvar number;
  v_cur  SYS_REFCURSOR;
  ORIGINAL_EMP_NUM  NUMBER;
  TEMPORARY_EMP_NUM NUMBER;
  ORG_UNIT_CODE VARCHAR2(2 BYTE);
  MRU_CODE  VARCHAR2(10 BYTE);
  CTRL_COMPANY_CODE VARCHAR2(10 BYTE);
  IS_TEMP_FLAG  VARCHAR2(1 BYTE);
BEGIN
  testvar := 420;
  foo.updates.get_temporary_authorisations(testvar, v_cur);
  dbms_output.enable;
  dbms_output.put_line(testvar);
  
  LOOP
    FETCH v_cur INTO ORIGINAL_EMP_NUM, TEMPORARY_EMP_NUM, ORG_UNIT_CODE, MRU_CODE, CTRL_COMPANY_CODE, IS_TEMP_FLAG;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(ORIGINAL_EMP_NUM || ',' || TEMPORARY_EMP_NUM || ',' || ORG_UNIT_CODE || ',' || MRU_CODE|| ',' || CTRL_COMPANY_CODE|| ',' || IS_TEMP_FLAG);
  END LOOP;
  CLOSE v_cur;
END;
回复 原文
风蛊 2024-10-05 23:58:40

要仅从 SQL 开发人员运行过程，请执行以下命令

EXECUTE PROCEDURE_NAME;

To run procedure from SQL developer-only execute following command

EXECUTE PROCEDURE_NAME;

回复 原文
流云如水 2024-10-05 23:58:40

我无法让@Alex Poole 得到有效的答案。然而，通过反复试验，我发现了以下工作（使用 SQL Developer 版本 3.0.04）。将其发布在这里以防对其他人有帮助：

SET serveroutput on;

DECLARE
    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);

    /* 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;

I wasn't able to get @Alex Poole answers working. However, by trial and error, I found the following works (using SQL Developer version 3.0.04). Posting it here in case it helps others:

SET serveroutput on;

DECLARE
    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);

    /* 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;
回复 原文
单身情人 2024-10-05 23:58:40
--for setting buffer size needed most of time to avoid `anonymous block completed` message
set serveroutput on size 30000;

-- declaration block in case output need to catch
DECLARE
--declaration for in and out parameter
  V_OUT_1 NUMBER;
  V_OUT_2 VARCHAR2(200);
BEGIN

--your stored procedure name
   schema.package.procedure(
  --declaration for in and out parameter
    V_OUT_1 => V_OUT_1,
    V_OUT_2 => V_OUT_2
  );
  V_OUT_1 := V_OUT_1;
  V_OUT_2 := V_OUT_2;
  -- console output, no need to open DBMS OUTPUT seperatly
  -- also no need to print each output on seperat line 
  DBMS_OUTPUT.PUT_LINE('Ouput => ' || V_OUT_1 || ': ' || V_OUT_2);
END;

--for setting buffer size needed most of time to avoid `anonymous block completed` message
set serveroutput on size 30000;

-- declaration block in case output need to catch
DECLARE
--declaration for in and out parameter
  V_OUT_1 NUMBER;
  V_OUT_2 VARCHAR2(200);
BEGIN

--your stored procedure name
   schema.package.procedure(
  --declaration for in and out parameter
    V_OUT_1 => V_OUT_1,
    V_OUT_2 => V_OUT_2
  );
  V_OUT_1 := V_OUT_1;
  V_OUT_2 := V_OUT_2;
  -- console output, no need to open DBMS OUTPUT seperatly
  -- also no need to print each output on seperat line 
  DBMS_OUTPUT.PUT_LINE('Ouput => ' || V_OUT_1 || ': ' || V_OUT_2);
END;
回复 原文
ぽ尐不点ル 2024-10-05 23:58:40

如果您有很多带有大量参数的过程，那么创建 Pl/SQL 块可能会很痛苦。有一个用 python 编写的 应用程序 可以为你做到这一点。
它使用过程声明解析文件并创建 Web 应用程序以方便过程调用。

Creating Pl/SQL block can be painful if you have a lot of procedures which have a lot of parameters. There is an application written on python that do it for you.
It parses the file with procedure declarations and creates the web app for convenient procedure invocations.

回复 原文
撞了怀 2024-10-05 23:58:40
var out_para_name refcursor; 
execute package_name.procedure_name(inpu_para_val1,input_para_val2,... ,:out_para_name);
print :out_para_name;

var out_para_name refcursor; 
execute package_name.procedure_name(inpu_para_val1,input_para_val2,... ,:out_para_name);
print :out_para_name;
回复 原文
~没有更多了~

关于作者

勿忘心安

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

佚名

文章 0 评论 0

羁客

文章 0 评论 0

天天爱笑的徐老师

文章 0 评论 0

星

文章 0 评论 0

夏日落

文章 0 评论 0

隐诗

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文