如何在Oracle SQL脚本中创建过程并在脚本内使用它？

Question

我希望为我的Oracle数据库创建一个脚本，用于删除表。如果表不存在，脚本不会以失败退出，而是输出文本：“不存在”。

脚本如下：

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE mytable';
    DBMS_Output.Put_Line(' table dropped');
EXCEPTION WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
        DBMS_Output.Put_Line(' table not exists');
    ELSE
        DBMS_Output.Put_Line(' Unknown exception while dropping table');
        RAISE;
    END IF;
END;

我希望能在一个脚本中删除多张表，而且不想重复写这些语句。
有没有什么方法可以将它写成一个"过程(procedure)"或者是"函数(function)"，然后传入表名作为参数，在脚本中调用这个"过程"或"函数"呢？
可能会像这样：

drop_table_procedure('mytableA');
drop_table_procedure('mytableB');

或者可能是一个过程，它获取一个未定义大小的列表（例如在Java中：String ...表名）：

drop_tables_procedure('mytableA','mytableB');

请给我一些例子。 谢谢！

- victorio

---

创建一个过程并将table_name作为参数传递给它。然后在匿名块中执行所有的过程。 - Lalit Kumar B

Answer 1

是的，您可以在匿名 PL/SQL 块中声明一个“临时”过程：

DECLARE 

  PROCEDURE drop_if_exists(p_tablename VARCHAR)
  IS
  BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE '||p_tablename;
      DBMS_Output.Put_Line(' table dropped');
  EXCEPTION WHEN OTHERS THEN
      IF SQLCODE = -942 THEN
          DBMS_Output.Put_Line(' table not exists');
      ELSE
          DBMS_Output.Put_Line(' Unknown exception while dropping table');
          RAISE;
      END IF;
  END;

BEGIN
  drop_if_exists('TABLE_1');
  drop_if_exists('TABLE_2');
END;
/

- user330315

Answer 2

在execute immediate中，您需要添加数据库对象的名称。 以下是脚本。

create table t1 (col1 int);
create table t2 (col1 int);

create procedure drop_my_table(av_name varchar2)
as
begin
    EXECUTE IMMEDIATE 'DROP TABLE '||av_name;
    DBMS_Output.Put_Line(' table dropped');
EXCEPTION WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
        DBMS_Output.Put_Line(' table not exists');
    ELSE
        DBMS_Output.Put_Line(' Unknown exception while dropping table');
        RAISE;
    END IF;

end drop_my_table;

declare
  type array_t is varray(2) of varchar2(30);
  atbls array_t := array_t('t1', 't2');
begin
  for i in 1..atbls.count loop
       drop_my_table(atbls(i));
   end loop; 
end;

- are

Answer 3

您可以使用下面的代码：

create or replace PROCEDURE drop_if_exists(p_tablename in VARCHAR)
      IS
    v_var1 number;
    begin 
    select 1 into v_var1 from user_tables where table_name=upper(p_tablename);
    if v_var1=1
    then 
    EXECUTE IMMEDIATE 'DROP TABLE '||p_tablename;
    DBMS_Output.Put_Line(' table dropped');
    else
    DBMS_Output.Put_Line(' table not exist');
    end if;
    exception 
    when others then
     DBMS_Output.Put_Line(' Unknown exception while dropping table');
            RAISE;
    end;

调用过程

    begin 
    drop_if_exists('emp');
    end;
    /

- Tharunkumar Reddy