Oracle中创建游标之前先创建表

发布于 2024-12-22 19:39:10 字数 110 浏览 2 评论 0原文

我有一个 PL/SQL 过程，它创建一个临时表，然后使用游标从该临时表中提取数据，处理数据，然后删除临时表。然而，如果数据库中不存在该表，Oracle 不允许使用游标。

请帮我处理这个问题。

原文

分享到QQ

分享到微博

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

发布评论

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

清晰传感 2024-12-29 19:39:10

你的说法不太正确。您可以使用游标进行几乎任意的查询。请参阅下文：（

create or replace procedure fooproc
IS
    type acursor is ref cursor;
    mycur  acursor;
    mydate date;
BEGIN
    execute immediate 'create global temporary table footmp (bar date) on commit delete rows';
    execute immediate 'insert into footmp values (SYSDATE)';
    open mycur for 'select * from footmp';
    loop
        fetch mycur into mydate;
        exit when mycur%notfound;
        dbms_output.put_line(mydate);
    end loop;
    close mycur;
    execute immediate 'drop table footmp';
END fooproc;
/

它（相当）有点难看，我并不是建议您使用它 - 相反，您应该考虑是否需要该特定于过程的临时表。

请参阅另一篇文章 :

不要动态创建它们[临时表]，请不要动态创建它们——不要动态创建它们。

不能使用全局临时表吗？你真的需要一个临时表吗？（即，在用于填充该表的 select 语句上不使用游标吗？）

Your statement is not quite correct. You can use a cursor for pretty much arbitrary queries. See below:

create or replace procedure fooproc
IS
    type acursor is ref cursor;
    mycur  acursor;
    mydate date;
BEGIN
    execute immediate 'create global temporary table footmp (bar date) on commit delete rows';
    execute immediate 'insert into footmp values (SYSDATE)';
    open mycur for 'select * from footmp';
    loop
        fetch mycur into mydate;
        exit when mycur%notfound;
        dbms_output.put_line(mydate);
    end loop;
    close mycur;
    execute immediate 'drop table footmp';
END fooproc;
/

(More details here - especially this short proc is not safe at all since the table name is fixed and not session-dependent).

It is (quite) a bit ugly, and I'm not suggesting you use that - rather, you should be thinking whether you need that procedure-specific temporary table at all.

See this other article:

DO NOT dynamically create them [temp tables], DO NOT dynamically create them, please -- do NOT dynamically create them.

Couldn't you use a global temporary table? Do you actually need a temporary table at all? (i.e. doesn't using a cursor on the select statement you'd use to fill that table work?)

回复收藏 0 原文