在oracle中动态创建和执行sql命令 [英] Dynamically creating and executing sql commands in oracle

问题描述

我采取一个数据库类，在实验室部分的开始，我们通常必须删除以前创建的数据库中的所有表。我想要能够运行一个动态执行这个脚本，但似乎不能让它工作。这是我到目前为止的代码。

I am taking a database class and at the beginning of the lab section of the class we usually have to drop all the tables in the database created previously. I wanted to be able to run a script that does this dynamically, but cannot seem to get it to work. Here is the code I have so far.

declare tname string(50);
cursor ctable is select table_name from user_tables;

begin
 open ctable;
 LOOP
   FETCH ctable into tname;
   if tname != ''  then
     execute immediate 'drop table ' || tname;
   END if;
   EXIT WHEN ctable%NOTFOUND;
 END LOOP;
 close ctable;
end;

如果有人能指出我正确的方向，我做错了，感谢。

If someone could point me in the right direction as to what I am doing wrong that would great. Thanks.

推荐答案

Oracle的 VARCHAR2 将空字符串视为 NULL 。
如此

Oracle's VARCHAR2 treats empty strings as NULL.
So

if tname != '' then

与

if tname != NULL then

/ code>而不是 TRUE ，因为它没有定义。

which will return NULL instead of TRUE since it is not defined.

您可以检查 NULL 由 tname IS NOT NULL 。

还有两件事：

1. 检查％NOTFOUND


2. 如果可能，请使用变量声明的列引用（ user_tables.table_name％TYPE ）

1. Check for %NOTFOUND immediately after fetching
2. Use column-references for variable declarations if possible ( user_tables.table_name%TYPE)

因此，您的代码可能如下所示：

So your code could look like that:

DECLARE
  tname user_tables.table_name%TYPE;
  CURSOR ctable IS SELECT table_name FROM user_tables;
BEGIN
  OPEN ctable;
  LOOP
    FETCH ctable INTO tname;
    EXIT WHEN ctable%NOTFOUND;
    EXECUTE IMMEDIATE 'drop table ' || tname;
  END LOOP;
  CLOSE ctable;
END;

您还可以使用隐式游标来提高可读性：

You could also use an implicit cursor for better readability:

BEGIN
  FOR cur IN ( SELECT table_name FROM user_tables ) LOOP
    EXECUTE IMMEDIATE 'drop table ' || cur.table_name;
  END LOOP;
END;

这篇关于在oracle中动态创建和执行sql命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！