SQL 我可以在过程中传递游标吗？

Can I pass a cursor in a procedure?

提问by Tony Andrews

can I pass a cursor in a procedure?

我可以在过程中传递一个游标吗？

CURSOR  BLT_CURSOR IS
SELECT  BLT.sol_id,
        BLT.bill_id,
        BLT.bank_id
FROM BLT;

Is my cursor.

是我的光标。

Procedure abc(i want to pass the cursor here)

How do I do it.

我该怎么做。

回答by Tony Andrews

I am assuming you are using Oracle (it would appear so).

我假设您正在使用 Oracle（看起来是这样）。

You can do this:

你可以这样做：

PROCEDURE abc( p_cursor IN SYS_REFCURSOR) IS
   v_sol_id blt.sol_id%TYPE;
   v_bill_id blt.bill_id%TYPE;
   v_bank_id blt.bank_id%TYPE;
BEGIN
   LOOP
      FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;
      EXIT WHEN p_cursor%NOTFOUND;
      ...
   END LOOP;
END;

Then use it:

然后使用它：

DECLARE
   v_cursor SYS_REFCURSOR;
BEGIN
   OPEN v_cursor FOR
      SELECT  BLT.sol_id,
              BLT.bill_id,
              BLT.bank_id
      FROM BLT;
   abc (v_cursor);
   CLOSE v_cursor;
END;

However, note that the procedure abc needs to know the structure of the cursor, i.e. that it returns 3 columns of particular types. If you wanted to be able to pass anycursor to the procedure then you'd need to look at using the DBMS_SQL package (and that's not trivial!)

但是，请注意过程 abc 需要知道游标的结构，即它返回特定类型的 3 列。如果您希望能够将任何游标传递给过程，那么您需要查看使用 DBMS_SQL 包（这不是微不足道的！）

回答by Rockcoder

From MSDN(SQL Server 2008): The cursor data type can be used only on OUTPUT parameters. When you specify a cursor data type, the VARYING and OUTPUT keywords must also be specified. You can have multiple output parameters specified with the cursor data type.

来自MSDN(SQL Server 2008)：游标数据类型只能用于 OUTPUT 参数。指定游标数据类型时，还必须指定 VARYING 和 OUTPUT 关键字。您可以使用游标数据类型指定多个输出参数。

回答by Sylwester Santorowski

Regarding MS-SQL you cannot pass the cursor as the input parametre. You can pass the cursor as the output parametre only.
What you can do is to pass the cursor select clause text as an input parametre. You might find the working piece of code below useful.

关于 MS-SQL，您不能将游标作为输入参数传递。您只能将光标作为输出参数传递。
您可以做的是将光标选择子句文本作为输入参数传递。您可能会发现下面的工作代码很有用。

--Create the table and fill it with data
DROP   TABLE    dbo.StackOverflow_MyTable
GO
CREATE TABLE    dbo.StackOverflow_MyTable (
                MyChar      varchar(10),
                MyDate      datetime,
                MyNum       numeric(10,2)
                PRIMARY KEY (MyChar))
GO
INSERT INTO dbo.StackOverflow_MyTable SELECT 'A1', '2018-01-13', 123.45
INSERT INTO dbo.StackOverflow_MyTable SELECT 'B2', '2018-01-14', 123.46
INSERT INTO dbo.StackOverflow_MyTable SELECT 'C3', '2018-01-15', 123.47
GO
/* Create the procedure which returns the cursor variable based on select statement
   The cursor must be opened here. Otherwise it throws an Error:
   The variable '@MyCursorVar' does not currently have a cursor allocated to it
*/
DROP   PROCEDURE dbo.StackOverflow_OpenCursor
GO
CREATE PROCEDURE dbo.StackOverflow_OpenCursor @SelectSQL nvarchar(128), @MyCursorVar CURSOR VARYING OUTPUT
    AS
    DECLARE @SQL nvarchar(256)
    SET @SQL='  SET     @MyCursorVar = CURSOR FOR '+@SelectSQL+'
                OPEN    @MyCursorVar'

    EXEC sp_executesql @SQL, N'@MyCursorVar CURSOR OUTPUT', @MyCursorVar OUTPUT
GO

--Create the procedure which browses the table using the cursor variable
DROP   PROCEDURE dbo.StackOverflow_BrowseCursor
GO
CREATE PROCEDURE dbo.StackOverflow_BrowseCursor @SelectSQL nvarchar(128)
    AS
    --Create the cursor variable based on select statement and OPEN the cursor
    DECLARE @MyCursorVar CURSOR
    EXEC dbo.StackOverflow_OpenCursor @SelectSQL, @MyCursorVar OUTPUT

    --Declare the variables corresponding to table column
    DECLARE @MyChar varchar(10), @MyDate datetime, @MyNum numeric(10,2)
    --Browse record by record
    WHILE 1=1
    BEGIN
        FETCH NEXT FROM @MyCursorVar INTO @MyChar, @MyDate, @MyNum
        IF @@FETCH_STATUS <> 0 BREAK
        PRINT @MyChar   --Here you might call any other procedure or dataset update
        PRINT @MyDate
        PRINT @MyNum
    END
    --release the cursor resources
    CLOSE       @MyCursorVar
    DEALLOCATE  @MyCursorVar
GO

--How to call the cursor browsing 
DECLARE @SelectSQL nvarchar(128)
SET     @SelectSQL = 'SELECT MyChar, MyDate, MyNum FROM dbo.StackOverflow_MyTable ORDER BY MyChar'
EXEC dbo.StackOverflow_BrowseCursor @SelectSQL

回答by Steve Fosdick

The answer that suggests maybe using a view assumes the intention is to be able to execute two different algorithms with the results of the same query. Of course this is one use of passing a cursor but for me I wanted to execute the same algorithm with the results of two different queries. I had two procedures each with their own purpose and a meaningful name that happened to share the same algorithm based around different queries so each would open the cursor with the appropriate query and then call a common procedure to execute the algorithm.

建议使用视图的答案假设其意图是能够使用相同查询的结果执行两种不同的算法。当然，这是传递游标的一种用途，但对我来说，我想用两个不同查询的结果执行相同的算法。我有两个过程，每个过程都有自己的目的和一个有意义的名称，它们碰巧共享基于不同查询的相同算法，因此每个过程都会使用适当的查询打开游标，然后调用一个通用过程来执行算法。

So, why not pass in the SQL of the query as text have have the inner procedure open the cursor there? Because if you do that and the SQL has any bound variables you would need to pass them in along with the text which then means the two (or more) queries need not only to return the same set of columns they must also take the same number and types of bound variables. If you pass the cursor instead the cursor is opened and the variables bound in the context of the procedure where the SQL is declared but the rows processed in the context of the procedure to which the cursor is passed.

那么，为什么不将查询的 SQL 作为文本传入，让内部过程在那里打开游标呢？因为如果你这样做并且 SQL 有任何绑定变量，你需要将它们与文本一起传递，这意味着两个（或更多）查询不仅需要返回相同的列集，它们还必须采用相同的数字和绑定变量的类型。如果您改为传递游标，则会打开游标，并且变量绑定在声明 SQL 的过程的上下文中，但在游标传递到的过程的上下文中处理行。

回答by Bob Probst

It's not possible in MSSQL2005. I don't know about 2008 or other platforms.

在 MSSQL2005 中这是不可能的。我不知道 2008 或其他平台。

There's been several times I'd like to do it. It would support a more modular architecture. I could have a proc that performs a common process on a dataset but be able to pass a cursor to that dataset from a variety of other procedures.

有好几次我想做。它将支持更加模块化的架构。我可以有一个对数据集执行通用过程的过程，但能够将游标从各种其他过程传递到该数据集。