游标内的动态 SQL

发布于 2024-09-15 14:29:35 字数 375 浏览 5 评论 0原文

下面是我的动态 sql 来更改表和根据查询的输出创建列会出现错误。

查询：

DECLARE
   CURSOR c1 is select distinct WP_NO from temp;
   cnum VARCHAR2(255);

BEGIN

  FOR cnum in c1 
  LOOP
    EXECUTE IMMEDIATE 'Alter table temp_col add (:1 varchar2(255))' using cnum;
  END LOOP;  

  COMMIT;

END;

错误：

PLS-00457：表达式必须是 SQL 类型

原文

My dynamic sql below to alter a table & create columns based on the output of a query is giving error.

Query :

DECLARE
   CURSOR c1 is select distinct WP_NO from temp;
   cnum VARCHAR2(255);

BEGIN

  FOR cnum in c1 
  LOOP
    EXECUTE IMMEDIATE 'Alter table temp_col add (:1 varchar2(255))' using cnum;
  END LOOP;  

  COMMIT;

END;

Error :

PLS-00457: expressions have to be of SQL types

分享到QQ

分享到微博

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

发布评论

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

我要还你自由 2024-09-22 14:29:35

发生这种情况是因为 DDL 语句中不允许绑定变量。

考虑尝试不使用绑定变量：

DECLARE
  CURSOR c1 is select distinct WP_NO from temp;
  cnum VARCHAR2(255);
BEGIN
  FOR cnum in c1 
  LOOP
    EXECUTE IMMEDIATE 'Alter table temp_col add ('|| cnum ||' varchar2(255))';
  END LOOP;  

  COMMIT;
END;

This is happening because bind variables are not allowed in DDL statements.

Consider trying it without using the bind variable:

DECLARE
  CURSOR c1 is select distinct WP_NO from temp;
  cnum VARCHAR2(255);
BEGIN
  FOR cnum in c1 
  LOOP
    EXECUTE IMMEDIATE 'Alter table temp_col add ('|| cnum ||' varchar2(255))';
  END LOOP;  

  COMMIT;
END;

回复收藏 0 原文

暖阳 2024-09-22 14:29:35

您与 cnum 符号发生冲突，该符号既用作局部变量又用于光标的当前行。

您可能想要这样：

DECLARE
   CURSOR c1 is select distinct WP_NO from temp;

BEGIN

  FOR current_row in c1 
  LOOP
    EXECUTE IMMEDIATE 'Alter table temp_col add (:1 varchar2(255))' using current_row.WP_NO;
  END LOOP;  

  COMMIT;

END;

如您所见，您不需要声明在 for 循环中使用的 current_row 变量。

You have a conflict with the cnum symbol, which you use both as a local variable and for the current row of the cursor.

You probably want this:

DECLARE
   CURSOR c1 is select distinct WP_NO from temp;

BEGIN

  FOR current_row in c1 
  LOOP
    EXECUTE IMMEDIATE 'Alter table temp_col add (:1 varchar2(255))' using current_row.WP_NO;
  END LOOP;  

  COMMIT;

END;

As you can see, you don't need to declare the current_row variable that you use in the for loop.

回复收藏 0 原文

~没有更多了~