使用 Pro*C 中的 PL/SQL 包游标的最佳方法

发布于 2024-09-05 11:02:10 字数 711 浏览 3 评论 0原文

我在 PL/SQL 中定义了一个游标，我想知道从 ProC 使用它的最佳方法是什么。通常，对于 ProC 中定义的游标，您会这样做：

EXEC SQL DECLARE curs CURSOR FOR SELECT 1 FROM DUAL;
EXEC SQL OPEN curs;
EXEC SQL FETCH curs INTO :foo;
EXEC SQL CLOSE cusr;

我希望相同（或类似）的语法适用于打包游标。例如，我有一个包 MyPack，带有声明

type MyType is record (X integer);
cursor MyCurs(x in integer) return MyType;

现在我在 Pro*C 代码中有一段相当不令人满意的嵌入式 PL/SQL，它打开游标、执行获取等，因为我无法获得第一个样式的语法来工作。使用示例

EXEC SQL EXECUTE
  DECLARE
    XTable is table of MyPack.MyType;
  BEGIN
    OPEN MyPack.MyCurs(:param);
    FETCH MyPack.MyCurs INTO XTable;
    CLOSE MyPack.MyCurs;
  END;
END-EXEC;

有谁知道是否有更“纯粹”的 Pro*C 方法？

原文

I have a cursor defined in PL/SQL, and I am wondering what the best way to use it from ProC is. Normally for a cursor defined in ProC you would do:

EXEC SQL DECLARE curs CURSOR FOR SELECT 1 FROM DUAL;
EXEC SQL OPEN curs;
EXEC SQL FETCH curs INTO :foo;
EXEC SQL CLOSE cusr;

I was hoping that the same (or similar) syntax would work for a packaged cursor. For example, I have a package MyPack, with a declaration

type MyType is record (X integer);
cursor MyCurs(x in integer) return MyType;

Now I have in my Pro*C code a rather unsatisfying piece of embedded PL/SQL that opens the cursor, does the fetching etc., as I couldn't get the first style of syntax to work.
Using the example

EXEC SQL EXECUTE
  DECLARE
    XTable is table of MyPack.MyType;
  BEGIN
    OPEN MyPack.MyCurs(:param);
    FETCH MyPack.MyCurs INTO XTable;
    CLOSE MyPack.MyCurs;
  END;
END-EXEC;

Does anyone know if there is a more "Pure" Pro*C approach?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独孤求败 2024-09-12 11:02:10

它实际上与您的第一个示例没有太大不同，只需确保您的光标定义位于包规范中而不是包主体中。不要“声明”它，只需使用类似以下内容：

数据库对象：

create or replace package mypkg as 
  cursor mycur is 
    SELECT 1 FROM DUAL; 
end;

在 pro*c 中：

EXEC SQL OPEN schema.mypkg.mycur; 
EXEC SQL FETCH schema.mypkg.mycur INTO :foo; 
EXEC SQL CLOSE schema.mypkg.mycur;

当然，您正在连接的 oracle 用户需要可以访问该包，等等。如果该包由用户拥有连接，或者有一个同义词，“模式”。 pro*c 调用中不需要。

It actually would not be much different than your first example, just make sure your cursor definition is in the package spec and not the package body. Don't "declare" it and just go with something like:

Database object:

create or replace package mypkg as 
  cursor mycur is 
    SELECT 1 FROM DUAL; 
end;

In pro*c:

EXEC SQL OPEN schema.mypkg.mycur; 
EXEC SQL FETCH schema.mypkg.mycur INTO :foo; 
EXEC SQL CLOSE schema.mypkg.mycur;

Of course the package would need to be accessible to the oracle user you are connecting with, etc. If the package is owned by the user connecting, or there is a synonym in place, the "schema." is not required in the pro*c calls.

回复收藏 0 原文