测试Oracle存储过程最简单的方法 [英] Easiest method to test an Oracle Stored Procedure

问题描述

我的工作与Oracle数据库的ASP.NET项目。我们正在使用TOAD添加/管理存储过程 - 和一般我喜欢蟾蜍和Oracle。有一件事，我发现令人沮丧的是找到一种简单的方法来测试一个Oracle存储过程，如SQL Server的执行[SP_NAME]参数1，参数2，ParamN语法。

I'm working on an ASP.NET project with an Oracle Database. We are using TOAD to add/manage the stored procedures -- and in general I like TOAD and Oracle. The one thing I've found frustrating is finding a simple way to test an Oracle Stored Proc, such as SQL Server's "exec [SP_NAME] Param1, Param2, ParamN" syntax.

我们所有的存储过程的输出REF游标。下面是一个存储过程的例子：

All of our stored procedures output Ref Cursors. Here is an example of a Stored Proc:

CREATE OR REPLACE PROCEDURE APP_DB1.GET_JOB
(
    p_JOB_ID IN JOB.JOB_ID%type,
    outCursor OUT MYGEN.sqlcur
)
IS
BEGIN
    OPEN outCursor FOR
    SELECT *
    FROM JOB
    WHERE JOB_ID = p_JOB_ID;
END GET_JOB;
/

有什么建议？

推荐答案

您只需要调用存储过程和对裁判光标输出蟾蜍在编辑器窗口中网格来显示它绑定变量的脚本。

You just need a script that calls your stored procedure and has a bind variable for the ref cursor output to display it in TOAD's grid in the Editor window.

DECLARE
 type result_set is ref cursor; 
BEGIN
 APP_DB1.GET_JOB(1, :result_set);
END;

当你再运行这个蟾蜍会提示你'绑定'：result_set，只需选择从类型列表REF光标，然后结果将在网格中显示。关键是要认为自己是一个客户调用存储过程，你需要自己的参考光标移动到存储结果。

When you then run this TOAD will prompt you to 'bind' :result_set, just select ref cursor from the list of types and then the result will display in the grid. The trick is to think of yourself as a 'client' calling your stored procedure and you need your own ref cursor to store the result.

这篇关于测试Oracle存储过程最简单的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！