将显式游标转换为ref游标，而无需重命名ATTR_n列 [英] Convert explicit cursor to ref cursor without renaming columns ATTR_n

问题描述

我想在游标上运行一些代码，但还要报告游标的内容，这样我就可以预览一下它要做什么.预览是Excel中的查询表，需要REF CURSOR表示{Call}语法.在PL/SQL中，用于进行编码的自然语法是显式游标.在此问题中@XING的帮助下，我开发了此软件包

CREATE OR REPLACE PACKAGE MyPackage
AS

  -- This could be a complex query with many paramters and columns
  CURSOR curMyCursor(pParam1 VARCHAR2) IS
  SELECT pParam1 hello, 'ColNames' goodbye
  FROM DUAL;

  -- I could just let GiveMyCursor return a SYS_REFCURSOR but wanted to try max clues
  TYPE refMyCursor IS REF CURSOR RETURN curMyCursor%ROWTYPE;
  -- The TABLE function in GiveMyCursor needs the row type to be externalized
  TYPE typMyCursor IS TABLE OF curMyCursor%ROWTYPE;

  PROCEDURE RunMyCursor(pParam1 IN VARCHAR2);
  PROCEDURE GiveMyCursor(pCursor OUT refMyCursor, pParam1 IN VARCHAR2);

END MyPackage;
/

CREATE OR REPLACE PACKAGE BODY MyPackage
AS

  PROCEDURE RunMyCursor(pParam1 IN VARCHAR2) IS
  BEGIN
    FOR recMyCursor IN curMyCursor(pParam1) LOOP
      NULL; -- Do normal cursor loop processing
    END LOOP;
  END RunMyCursor;

  PROCEDURE GiveMyCursor(pCursor OUT refMyCursor, pParam1 IN VARCHAR2) IS
    tabMyCursor typMyCursor;
  BEGIN
    OPEN curMyCursor(pParam1);
    -- Load the entire contents of the cursor into memory and pray it fits
    FETCH curMyCursor BULK COLLECT INTO tabMyCursor;  
    CLOSE curMyCursor;

    -- PROBLEM: The TABLE function renames the columns ATTR_1, ATTR_2, etc
    OPEN pCursor FOR 
    SELECT ATTR_1 HELLO, ATTR_2 GOODBYE 
    FROM TABLE(tabMyCursor);
  END GiveMyCursor;

END MyPackage;
/

对于我的先睹为快，我想给最终用户一个简单的表，其中过滤了他们可以刷新的排序.在不重复查询的情况下，提供了一个简单的Excel查询表，该表具有返回REF CURSOR的过程，例如{call MyPackage.GiveMyCursor('World')}给出:

HELLO | GOODBYE
------+---------
World | ColNames

如果我没有在OPEN pCursor中重新指定列名，

ATTR_1| ATTR_2
------+---------
World | ColNames

因此OPEN pCursor FOR SELECT *本来可以允许我增强/修复查询而没有其他维护方面的问题，但是最终用户将丢失列说明.

GiveMyCursor中有某种方法可以添加一些代码，以防止必须重新指定所有列名.一些内置的DBMS程序包，我可以查询游标或一种类型的游标来构建字符串，或保持其通用性的东西?

我的数据库是Oracle 12.1.0.1 SE.

解决方案

我不清楚为什么当可以传递curMyCursor时，为什么需要第二个REF CURSOR作为OUT参数.

在您的代码注释中，

-将游标的全部内容加载到内存中，并祈祷它合适.

您是否可能假设将记录存储在内存中会产生更好的性能?实际上，当您使用CURSOR时，Oracle实际上会打开一个未命名的工作区，用于存储处理信息.那么，为什么不直接传递第一个CURSOR并在调用块中使用它呢?

在您的情况下，您已经说过了

FETCH curMyCursor BULK COLLECT INTO tabMyCursor

除非在声明集合时已正确定义它，否则不进行列到列的映射.

您可以做的是，声明具有预期列数据类型的RECORD.

 TYPE MyrecordType is RECORD 
(
 hello VARCHAR2(10),
 goodbye VARCHAR2(10)
 );
TYPE typMyCursor IS TABLE OF MyrecordType ;

代替

TYPE typMyCursor IS TABLE OF curMyCursor%ROWTYPE;

现在，您将对集合中记录元素的所有列都使用适当的名称.然后，您应该能够直接使用SELECT *而不是指定元素.

OPEN pCursor FOR 
    SELECT *
    FROM TABLE(tabMyCursor);

I want run some code over a cursor but also report the contents of the cursor so I can have a sneak preview of what it is about to do. The preview is a query table in Excel and wants a REF CURSOR for the{Call} syntax. In PL/SQL the natural syntax for coding against is an explicit cursor. With help from @XING in this question I developed this package.

CREATE OR REPLACE PACKAGE MyPackage
AS

  -- This could be a complex query with many paramters and columns
  CURSOR curMyCursor(pParam1 VARCHAR2) IS
  SELECT pParam1 hello, 'ColNames' goodbye
  FROM DUAL;

  -- I could just let GiveMyCursor return a SYS_REFCURSOR but wanted to try max clues
  TYPE refMyCursor IS REF CURSOR RETURN curMyCursor%ROWTYPE;
  -- The TABLE function in GiveMyCursor needs the row type to be externalized
  TYPE typMyCursor IS TABLE OF curMyCursor%ROWTYPE;

  PROCEDURE RunMyCursor(pParam1 IN VARCHAR2);
  PROCEDURE GiveMyCursor(pCursor OUT refMyCursor, pParam1 IN VARCHAR2);

END MyPackage;
/

CREATE OR REPLACE PACKAGE BODY MyPackage
AS

  PROCEDURE RunMyCursor(pParam1 IN VARCHAR2) IS
  BEGIN
    FOR recMyCursor IN curMyCursor(pParam1) LOOP
      NULL; -- Do normal cursor loop processing
    END LOOP;
  END RunMyCursor;

  PROCEDURE GiveMyCursor(pCursor OUT refMyCursor, pParam1 IN VARCHAR2) IS
    tabMyCursor typMyCursor;
  BEGIN
    OPEN curMyCursor(pParam1);
    -- Load the entire contents of the cursor into memory and pray it fits
    FETCH curMyCursor BULK COLLECT INTO tabMyCursor;  
    CLOSE curMyCursor;

    -- PROBLEM: The TABLE function renames the columns ATTR_1, ATTR_2, etc
    OPEN pCursor FOR 
    SELECT ATTR_1 HELLO, ATTR_2 GOODBYE 
    FROM TABLE(tabMyCursor);
  END GiveMyCursor;

END MyPackage;
/

For my sneak preview I want to give the end user a simple table with filtering an sorting that they can refresh. Without duplicating the query, a simple Excel query table with a procedure that returns a REF CURSOR e.g. {call MyPackage.GiveMyCursor('World')} gives:

HELLO | GOODBYE
------+---------
World | ColNames

If I hadn't re-specified the column names in the OPEN pCursor I would have got

ATTR_1| ATTR_2
------+---------
World | ColNames

So OPEN pCursor FOR SELECT * would have allowed me enhance/fix my query with no other maintenance ramifications, but the end user would lose the column descriptions.

Is there some way in GiveMyCursor I can add some code to prevent having to re-specify all column names. Some built-in DBMS package I can interagate the cursor or one of the types to build a string or something to keep it generic?

My database is Oracle 12.1.0.1 SE.

解决方案

It is unclear to me why do you require a second REF CURSOR as an OUT parameter, when the curMyCursor itself could be passed.

In your code comment,

-- Load the entire contents of the cursor into memory and pray it fits

are you probably assuming that storing the records in memory would yield better performance? As a matter of fact, When you use a CURSOR, Oracle actually opens an unnamed work area that stores processing information. So, why not directly pass the first CURSOR and use it in the calling block?

Having said that, in your case, the statement

FETCH curMyCursor BULK COLLECT INTO tabMyCursor

doesn't do a column to column mapping, unless you have properly defined it while declaring the collection.

What you could do is, declare a RECORD with expected column data types.

 TYPE MyrecordType is RECORD 
(
 hello VARCHAR2(10),
 goodbye VARCHAR2(10)
 );
TYPE typMyCursor IS TABLE OF MyrecordType ;

instead of

TYPE typMyCursor IS TABLE OF curMyCursor%ROWTYPE;

Now, you will have proper names to all columns of the record elements in the collection. You should then be able to directly use SELECT * instead of specifying the elements.

OPEN pCursor FOR 
    SELECT *
    FROM TABLE(tabMyCursor);

这篇关于将显式游标转换为ref游标，而无需重命名ATTR_n列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！