我可以在程序中传递光标吗？

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.

我该怎么做。

4 个解决方案

#1

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 any cursor to the procedure then you'd need to look at using the DBMS_SQL package (and that's not trivial!)

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

#2

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关键字。您可以使用游标数据类型指定多个输出参数。

#3

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的过程的上下文中，但是在传递游标的过程的上下文中处理的行。

#4

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.

有几次我想这样做。它将支持更模块化的架构。我可以有一个proc，它在数据集上执行一个公共过程，但能够将光标从各种其他过程传递给该数据集。