PL/SQL DDL 立即执行

Question

ACCEPT p_username PROMPT 'Enter Username : '
ACCEPT p_password PROMPT 'Enter New Password for Username : '
VARIABLE g_output VARCHAR2(4000)
DECLARE
   CURSOR NAME IS SELECT TABLE_NAME FROM DBA_TABLES 
      WHERE OWNER LIKE '%&p_username%';
   DDL_DROP VARCHAR2(200);
BEGIN
   FOR TNAME IN NAME
   LOOP
       BEGIN
          EXECUTE IMMEDIATE 'DROP TABLE' || ' ' || TNAME.TABLE_NAME;
         :g_output := :g_output || ' ' || TNAME.TABLE_NAME;
       END;
   END LOOP;
END;
/
PRINT g_output

您好，我是 PL/SQL 新手，正在尝试编写一个脚本来删除用户的表，并最终在删除表后更改其密码。我在使用 EXECUTE IMMEDIATE 命令时遇到困难。如果我删除 EXECUTE IMMEDIATE 行，该脚本将起作用。我通过在循环内打印表名称来测试它，并得到正确的表数量及其相应的名称。

如有任何帮助，我们将不胜感激，谢谢。

编辑代码以反映建议，但仍然不起作用。遇到同样的错误。

ACCEPT p_username PROMPT 'Enter Username : '
ACCEPT p_password PROMPT 'Enter New Password for Username : '
VARIABLE g_output VARCHAR2(4000)
DECLARE
   NAME SYS_REFCURSOR;
   DDL_WORD VARCHAR2(200);
BEGIN
   OPEN NAME FOR SELECT TABLE_NAME FROM DBA_TABLES 
      WHERE OWNER LIKE '%&p_username%';
   LOOP
         FETCH NAME INTO DDL_WORD;
         EXIT WHEN NAME%NOTFOUND;
         EXECUTE IMMEDIATE 'DROP TABLE "' || DDL_WORD || '" CASCADE CONSTRAINTS';
         :g_output := :g_output || ' ' || DDL_WORD;
   END LOOP;
   CLOSE NAME;
END;
/
PRINT g_output

Answer 1

您可能需要在 DROP 语句中指定表的所有者：

ACCEPT p_username PROMPT 'Enter Username : '
ACCEPT p_password PROMPT 'Enter New Password for Username : '
VARIABLE g_output VARCHAR2(4000)
DECLARE
   CURSOR NAME IS SELECT OWNER, TABLE_NAME FROM DBA_TABLES 
      WHERE OWNER LIKE '%&p_username%';
   DDL_DROP VARCHAR2(200);
BEGIN
   FOR TNAME IN NAME
   LOOP
       BEGIN
          EXECUTE IMMEDIATE 'DROP TABLE' || ' ' || TNAME.OWNER || '.' || TNAME.TABLE_NAME;
         :g_output := :g_output || ' ' || TNAME.OWNER || '.' || TNAME.TABLE_NAME;
       END;
   END LOOP;
END;
/
PRINT g_output

Answer 2

代码看起来不错。

你可以尝试这样的 ()

BEGIN

 EXECUTE IMMEDIATE (code_text);

END;

你可以尝试

 c SYS_REFCURSOR;
BEGIN
  OPEN c FOR 'SELECT * FROM table';
  CLOSE c;
END;