如何使用动态 SQL 声明从表名派生的列名？

发布于 2024-09-14 05:20:22 字数 522 浏览 5 评论 0原文

如果我想做这样的事情，

CREATE PROCEDURE A(tab IN VARCHAR2) IS
tab.col_name <column> --static declaration (column name always remains the same)
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)';
END A;

怎样才能我在上述情况下使用动态 SQL？

原文

Building on Tony's answer on this question:

If I want to do something like this,

CREATE PROCEDURE A(tab IN VARCHAR2) IS
tab.col_name <column> --static declaration (column name always remains the same)
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)';
END A;

How can I use Dynamic SQL in the above case?

分享到QQ

分享到微博

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

发布评论

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

韬韬不绝 2024-09-21 05:20:22

此示例传递一个表名和一个列名：

CREATE PROCEDURE A
  ( tab IN VARCHAR2
  , col_name IN VARCHAR2
  ) IS
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
END A;

您需要意识到 EXECUTE IMMEDIATE 之后的所有内容都必须是包含一些有效 SQL 的字符串。验证这一点的一个好方法是将其设置在变量中并将其打印到屏幕上：

CREATE PROCEDURE A
  ( tab IN VARCHAR2
  , col_name IN VARCHAR2
  ) IS
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
   DBMS_OUTPUT.PUT_LINE('SQL='||v_sql);
   EXECUTE IMMEDIATE v_sql;
END A;

然后，这应该在 SQL Plus 中显示如下内容：

SQL=INSERT INTO mytable(mycolumn)
值(123)

（前提是服务器输出已打开）。

编辑：由于您希望列名是始终具有相同值的局部变量，因此可以这样做：

CREATE PROCEDURE A (tab IN VARCHAR2)
IS
   col_name VARCHAR2(30) := 'MYCOLUMN';
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
   DBMS_OUTPUT.PUT_LINE('SQL='||v_sql);
   EXECUTE IMMEDIATE v_sql;
END A;

This example passes in a table name and a column name:

CREATE PROCEDURE A
  ( tab IN VARCHAR2
  , col_name IN VARCHAR2
  ) IS
BEGIN
   EXECUTE IMMEDIATE 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
END A;

You need to realise that everything after EXECUTE IMMEDIATE must be a string that contains some valid SQL. A good way to verify this is to set it up in a variable and print it to the screen:

CREATE PROCEDURE A
  ( tab IN VARCHAR2
  , col_name IN VARCHAR2
  ) IS
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
   DBMS_OUTPUT.PUT_LINE('SQL='||v_sql);
   EXECUTE IMMEDIATE v_sql;
END A;

This should then display something like the following in SQL Plus:

SQL=INSERT INTO mytable(mycolumn)
VALUES(123)

(provided server output is turned on).

EDIT: Since you want the column name to be a local variable that always has the same value, this could be done as:

CREATE PROCEDURE A (tab IN VARCHAR2)
IS
   col_name VARCHAR2(30) := 'MYCOLUMN';
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'INSERT INTO ' || tab || '(' || col_name || ') VALUES(123)';
   DBMS_OUTPUT.PUT_LINE('SQL='||v_sql);
   EXECUTE IMMEDIATE v_sql;
END A;

回复收藏 0 原文

~没有更多了~