如何使用 PL/SQL 打印多行结果？

发布于 2024-10-02 06:43:11 字数 135 浏览 1 评论 0原文

PROCEDURE A(
...
BEGIN
stmt := 'select * from '||src;
execute immediate stmt;
dbms_output.put_line(??);
END A;

原文

PROCEDURE A(
...
BEGIN
stmt := 'select * from '||src;
execute immediate stmt;
dbms_output.put_line(??);
END A;

分享到QQ

分享到微博

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

发布评论

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

柳若烟 2024-10-09 06:43:11

如果您在编写代码时知道“src”中命名的表的结构，那么您可以这样做：

PROCEDURE A IS
...
  l_cur sys_refcursor;
BEGIN
  stmt := 'select * from '||src;
  open l_cur for stmt;
  loop
    fetch l_cur into ??; -- record or list of variables that matches columns of "src"
    exit when l_cur%notfound;
    dbms_output.put_line(??);
  end loop;
  close l_cur;
END A;

如果您直到运行时才知道该结构，那么您将需要使用 DBMS_SQL 包，功能非常强大但并不简单。

If you know the structure of the table named in "src" when writing the code then you can do this:

PROCEDURE A IS
...
  l_cur sys_refcursor;
BEGIN
  stmt := 'select * from '||src;
  open l_cur for stmt;
  loop
    fetch l_cur into ??; -- record or list of variables that matches columns of "src"
    exit when l_cur%notfound;
    dbms_output.put_line(??);
  end loop;
  close l_cur;
END A;

If you will not know the structure until run time then you will need to use the DBMS_SQL package, which is very powerful but not simple.

回复收藏 0 原文

蹲在坟头点根烟 2024-10-09 06:43:11

我不确定这是否适用于您的“立即执行 stmt”方法，但对于静态 Sql，以下对我有用：

  for my_result in
  (
    select * from my_table tbl
     where ...
     order by tbl.my_id_col
  ) loop

    dbms_output.put_line(my_result.field1 || ', ' || my_result.field2 || ...);

  end loop;

I'm not sure wether this is working with your "execute immediate stmt" approach, but with static Sql, following is working for me:

  for my_result in
  (
    select * from my_table tbl
     where ...
     order by tbl.my_id_col
  ) loop

    dbms_output.put_line(my_result.field1 || ', ' || my_result.field2 || ...);

  end loop;

回复收藏 0 原文

~没有更多了~