如何使用out sys_refcursor参数执行oracle过程? [英] How to execute an oracle procedure with an out sys_refcursor parameter?

问题描述

我的包体内有一个proc:

I have a proc in my package body:

create or replace package body MYPACKAGE is

    procedure "GetAllRules"(p_rules     out sys_refcursor)
    is
    begin
        open p_rules for
        select * from my_rules;

    end "GetAllRules";

-- etc

我要在我的包装规格中公开它.

And I'm exposing this in my package spec.

如何在PL SQL Developer(或类似版本)的新SQL窗口中执行此过程?

How do I execute this procedure in a new SQL Window in PL SQL Developer (or similar)?

推荐答案

您可以相对轻松地执行该过程

You can execute the procedure relatively easily

DECLARE 
  l_rc sys_refcursor;
BEGIN
  mypackage."GetAllRules"( l_rc );
END;

当然，这只是将光标返回到调用应用程序.它不执行任何操作来从游标中获取数据，对该数据执行任何操作或关闭游标.假设您的目标是向dbms_output中写入一些数据(有时对于原型制作很有用，但生产代码不应该依赖该数据)，则可以执行类似

Of course, that simply returns the cursor to the calling application. It doesn't do anything to fetch the data from the cursor, to do something with that data, or to close the cursor. Assuming that your goal is to write some data to dbms_output (which is useful sometimes for prototyping but isn't something that production code should be relying on), you could do something like

DECLARE 
  l_rc sys_refcursor;
  l_rec my_rules%rowtype;
BEGIN
  mypackage."GetAllRules"( l_rc );
  LOOP
     FETCH l_rc INTO l_rec;
     EXIT WHEN l_rc%NOTFOUND;

     dbms_output.put_line( <<print data from l_rec>> );
   END LOOP;

   CLOSE l_rc;
END;

如果您确实在PL/SQL中使用游标执行了这样的操作，我强烈建议您返回一个强类型的ref游标，而不是弱类型的ref游标，以便您可以根据游标的%rowtype而不是强迫调用者确切知道要声明的类型，并希望过程中的查询不变.这还需要您显式地编写代码来显示令人讨厌的数据.

If you're really doing something like this with the cursor in PL/SQL, I'd strongly suggest returning a strongly-typed ref cursor rather than a weakly-typed one so that you can declare a record in terms of the cursor's %rowtype rather than forcing the caller to know exactly what type to declare and hoping that the query in the procedure doesn't change. This also requires you to explicitly write code to display the data which gets annoying.

如果您使用的是SQL * Plus(或支持某些SQL * Plus命令的东西)，则可以简化一些事情

If you're using SQL*Plus (or something that supports some SQL*Plus commands), you can simplify things a bit

VARIABLE rc REFCURSOR;
EXEC mypackage."GetAllRules"( :rc );
PRINT :rc;

顺便说一句，我不喜欢使用区分大小写的标识符.每次要调用"GetAllRules"之类的标识符时都必须用双引号引起来，这已经很老了.除非您真的有令人信服的理由，否则建议您使用标准的不区分大小写的标识符.在代码中合理地大写标识符是完全合理的，当然，在数据字典中强制将它们区分大小写只是没有多大意义.

As an aside, I'm not a fan of using case-sensitive identifiers. It gets very old to have to surround identifiers like "GetAllRules" with double-quotes every time you want to call it. Unless you have really compelling reasons, I'd suggest using standard case-insensitive identifiers. It's perfectly reasonable to capitalize identifiers reasonably in your code, of course, it just doesn't make a lot of sense to go to the effort of forcing them to be case-sensitive in the data dictionary.

这篇关于如何使用out sys_refcursor参数执行oracle过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！