如何使用Oracle SQL developer来运行存储过程?

* EDIT6: * This is what ended up working for me (from accepted answer):

* EDIT6: *这是我为我工作的结果(从接受的回答):

var ret1 number
var tran_cnt number
var msg_cnt number
var rc refcursor
exec :tran_cnt := 0
exec :msg_cnt := 123
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => :rc)
print :tran_cnt
print :msg_cnt
print :rc

---

The SQL Developer makes this super-hard / impossible?. I do not care if the utility is command-line-based; I just want to be able to run and view it quickly. It would be nice if it captured errors well as well. it would be nice to be able to log in gradually(interactively), as well as specifying everything at once (similar to how a typical ftp / sftp cmd-based client works).

SQL开发人员让这个超级难/不可能?我不关心这个实用程序是否基于命令行;我只是想能够运行并快速查看它。如果它也能捕获错误，那就太好了。可以逐渐地(交互地)登录，并同时指定所有内容(类似于典型的基于ftp / sftp的基于cmd的客户机的工作方式)。

My platform is Windows Server 2008 + Cygwin.

我的平台是Windows Server 2008 + Cygwin。

EDIT: Perhaps you would know how to script this using Python?

编辑:也许您会知道如何使用Python编写脚本?

EDIT 2: In MSFT SQL server I can simply type this:

编辑2:在MSFT SQL server中，我可以简单地输入以下内容:

get_user 1;

then highlight it and hit F5, and I get:

然后选中它，点击F5，我得到:

login   name    
NULL    Somename

printed to the output window. The Oracle SQL developer does not help with this at all. I am not sure how to pass in a 1, I am not sure how to see the actual rows/records that come back.

打印到输出窗口。Oracle SQL开发人员对此毫无帮助。我不知道如何传入1，我不知道如何查看返回的实际行/记录。

EDIT3: When I type just var rc refcursor; and select it and run it, I get this error (GUI):

当我输入var rc refcursor时;选择它并运行它，我得到这个错误(GUI):

An error was encountered performing the requested operation:

ORA-00900: invalid SQL statement
00900.00000 - "invalid SQL statement"
* Cause:
* Action:
Vendor code 900Error at Line: 2

EDIT4:

标:

I am trying to run a procedure the definition of which starts like this:

我试着运行一个定义如下的程序

create or replace procedure get_account
(
    Vret_val out number,
    Vtran_count in out number,
    Vmessage_count in out number,
    Vaccount_id     IN NUMBER
    , rc1 in out sys_refcursor
)as
begin
...

I get an error:

我得到一个错误:

Error starting at line 2 in command:
exec :rc := get_account(1) 
Error report:
ORA-06550: line 1, column 24:
PLS-00306: wrong number or types of arguments in call to 'GET_ACCOUNT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
rc
------

I am so close ... please help.

我很亲密……请帮助。

* EDIT 5: *

*编辑5:*

Script that I am running (functionally the same), the error was always the same:

我正在运行的脚本(功能相同)，错误总是相同的:

var ret1 number
var tran_cnt number
var msg_cnt number
var rc refcursor
exec :tran_cnt := 0
exec :msg_cnt := 123
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => rc)

Script Output (on F5) (Could be several messages from several runs.):

脚本输出(在F5上)(可能是来自多个运行的多个消息):

Error report:
ORA-06550: line 1, column 134:
PLS-00201: identifier 'RC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
anonymous block completed
anonymous block completed

Error starting at line 7 in command:
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => rc)
Error report:
ORA-06550: line 1, column 134:
PLS-00201: identifier 'RC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
anonymous block completed
anonymous block completed

Error starting at line 7 in command:
exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => rc)
Error report:
ORA-06550: line 1, column 134:
PLS-00201: identifier 'RC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Why does it say line 1, column 134? No line stretched that far ...

为什么是第1行，第134列?没有一条线延伸到那远……

5 个解决方案

#1

Not only is there a way to do this, there is more than one way to do this (which I concede is not very Pythonic, but then SQL*Developer is written in Java ).

不仅有一种方法可以做到这一点，还有不止一种方法可以做到这一点(我承认这种方法不是很python化，但是SQL*Developer是用Java编写的)。

I have a procedure with this signature: get_maxsal_by_dept( dno number, maxsal out number).

我有一个带有这个签名的过程:get_maxsal_by_dept(dno number, maxsal out number)。

I highlight it in the SQL*Developer Object Navigator, invoke the right-click menu and chose Run. (I could use ctrl+F11.) This spawns a pop-up window with a test harness. (Note: If the stored procedure lives in a package, you'll need to right-click the package, not the icon below the package containing the procedure's name; you will then select the sproc from the package's "Target" list when the test harness appears.) In this example, the test harness will display the following:

我在SQL*Developer对象导航器中突出显示它，调用右键单击菜单并选择Run。(我可以用ctrl + F11)。这将生成一个带有测试约束的弹出窗口。(注意:如果存储过程驻留在包中，则需要右键单击包，而不是包含过程名称的包下面的图标;然后，当测试工具出现时，您将从包的“目标”列表中选择sproc。在本例中，测试工具将显示以下内容:

DECLARE
  DNO NUMBER;
  MAXSAL NUMBER;
BEGIN
  DNO := NULL;

  GET_MAXSAL_BY_DEPT(
    DNO => DNO,
    MAXSAL => MAXSAL
  );
  DBMS_OUTPUT.PUT_LINE('MAXSAL = ' || MAXSAL);
END;

I set the variable DNO to 50 and press okay. In the Running - Log pane (bottom right-hand corner unless you've closed/moved/hidden it) I can see the following output:

我将变量DNO设置为50，然后按ok。在运行日志窗格(右下角，除非您关闭/移动/隐藏它)中，我可以看到以下输出:

Connecting to the database apc.
MAXSAL = 4500
Process exited.
Disconnecting from the database apc. 

To be fair the runner is less friendly for functions which return a Ref Cursor, like this one: get_emps_by_dept (dno number) return sys_refcursor.

公平地说，对于返回Ref游标的函数，运行者不太友好，比如这个:get_emps_by_dept (dno number)返回sys_refcursor。

DECLARE
  DNO NUMBER;
  v_Return sys_refcursor;
BEGIN
  DNO := 50;

  v_Return := GET_EMPS_BY_DEPT(
    DNO => DNO
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

However, at least it offers the chance to save any changes to file, so we can retain our investment in tweaking the harness...

但是，至少它提供了保存文件的任何更改的机会，这样我们就可以保留我们的投资来调整这个工具……

DECLARE
  DNO NUMBER;
  v_Return sys_refcursor;
  v_rec emp%rowtype;
BEGIN
  DNO := 50;

  v_Return := GET_EMPS_BY_DEPT(
    DNO => DNO
  );

  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('name = ' || v_rec.ename);
  end loop;
END;

The output from the same location:

相同位置的输出:

Connecting to the database apc.
name = TRICHLER
name = VERREYNNE
name = FEUERSTEIN
name = PODER
Process exited.
Disconnecting from the database apc. 

---

Alternatively we can use the old SQLPLus commands in the SQLDeveloper worksheet:

或者，我们可以使用SQLDeveloper工作表中的旧SQLPLus命令:

var rc refcursor 
exec :rc := get_emps_by_dept(30) 
print rc

In that case the output appears in Script Output pane (default location is the tab to the right of the Results tab).

在这种情况下，输出出现在Script output窗格中(默认位置是Results选项卡右侧的选项卡)。

The very earliest versions of the IDE did not support much in the way of SQL*Plus. However, all of the above commands have been supported since 1.2.1. Refer to the matrix in the online documentation for more info.

IDE的早期版本对SQL*Plus的支持并不多。但是，从1.2.1开始就支持所有上述命令。更多信息请参考在线文档中的矩阵。

---

"When I type just var rc refcursor; and select it and run it, I get this error (GUI):"

"当我输入var rc refcursor时;选择并运行它，我得到这个错误(GUI):"

There is a feature - or a bug - in the way the worksheet interprets SQLPlus commands. It presumes SQLPlus commands are part of a script. So, if we enter a line of SQL*Plus, say var rc refcursor and click Execute Statement (or F9 ) the worksheet hurls ORA-900 because that is not an executable statement i.e. it's not SQL . What we need to do is click Run Script (or F5 ), even for a single line of SQL*Plus.

工作表解释SQLPlus命令的方式中有一个特性(或一个bug)。它假定SQLPlus命令是脚本的一部分。因此，如果我们输入一行SQL*Plus，比如var rc refcursor，然后单击Execute语句(或F9)，工作表就会抛出ORA-900，因为它不是可执行语句，也就是说，它不是SQL。我们需要做的是点击Run Script(或F5)，即使是一条SQL*Plus。

---

"I am so close ... please help."

“我太亲密了……”请帮。”

You program is a procedure with a signature of five mandatory parameters. You are getting an error because you are calling it as a function, and with just the one parameter:

您的程序是一个带有五个强制参数签名的过程。你会得到一个错误，因为你将它作为一个函数调用，并且只有一个参数:

exec :rc := get_account(1)

What you need is something like the following. I have used the named notation for clarity.

您需要的是以下内容。我已经使用了命名符号来表示清楚。

var ret1 number
var tran_cnt number
var msg_cnt number
var rc refcursor

exec :tran_cnt := 0
exec :msg_cnt := 123

exec get_account (Vret_val => :ret1, 
                  Vtran_count => :tran_cnt, 
                  Vmessage_count => :msg_cnt, 
                  Vaccount_id   => 1,
                  rc1 => :rc )

print tran_count 
print rc

That is, you need a variable for each OUT or IN OUT parameter. IN parameters can be passed as literals. The first two EXEC statements assign values to a couple of the IN OUT parameters. The third EXEC calls the procedure. Procedures don't return a value (unlike functions) so we don't use an assignment syntax. Lastly this script displays the value of a couple of the variables mapped to OUT parameters.

也就是说，您需要为每个OUT或IN OUT参数设置一个变量。在参数中可以作为常量传递。前两个EXEC语句为两个IN OUT参数赋值。第三个执行程序调用这个过程。过程不返回值(不像函数)，所以我们不使用赋值语法。最后，这个脚本显示了几个映射到OUT参数的变量的值。

#2

I am not sure how to see the actual rows/records that come back.

我不确定如何查看返回的实际行/记录。

Stored procedures do not return records. They may have a cursor as an output parameter, which is a pointer to a select statement. But it requires additional action to actually bring back rows from that cursor.

存储过程不返回记录。它们可能有一个游标作为输出参数，它是指向select语句的指针。但是它需要额外的操作才能从游标中返回行。

In SQL Developer, you can execute a procedure that returns a ref cursor as follows

在SQL Developer中，您可以执行以下步骤来返回ref游标。

var rc refcursor
exec proc_name(:rc)

After that, if you execute the following, it will show the results from the cursor:

在此之后，如果您执行以下操作，它将显示光标的结果:

print rc

#3

My recommendation is TORA

我的建议是托

#4

Have you heard of "SQuirreL SQL Client"?

你听说过“SQuirreL SQL Client”吗?

http://squirrel-sql.sourceforge.net/

http://squirrel-sql.sourceforge.net/

#5

There are two possibilities, both from Quest Software, TOAD & SQL Navigator:

有两种可能性，都来自Quest Software, TOAD & SQL Navigator:

Here is the TOAD Freeware download: http://www.toadworld.com/Downloads/FreewareandTrials/ToadforOracleFreeware/tabid/558/Default.aspx

以下是蟾蜍免费软件下载:http://www.toadworld.com/Downloads/FreewareandTrials/ToadforOracleFreeware/tabid/558/Default.aspx

And the SQL Navigator (trial version): http://www.quest.com/sql-navigator/software-downloads.aspx

和SQL Navigator(试用版):http://www.quest.com/sql- navigator/softwaredownloads.aspx