删除表空间(如果不存在) [英] drop tablespace if do not exist

问题描述

我已经编写了pl/sql脚本(可以，但是看起来不太好):

I have written pl/sql script (works, but doesn't look nice):

DECLARE
   v_exists NUMBER;
BEGIN
   SELECT count(*) INTO v_exists FROM dba_tablespaces WHERE tablespace_name = 'hr_test';
   IF v_exists > 0 THEN
   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLESPACE hr_test INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
   END;
   END IF;
   EXECUTE IMMEDIATE 'CREATE TABLESPACE hr_RJ DATAFILE ''E:\hr_test_01.dbf'' SIZE 16M';
END;

没有EXECUTE IMMEDIATE，有没有办法重写此脚本?

Is there any way to rewrite this script without EXECUTE IMMEDIATE?

推荐答案

否.您不能在静态PL/SQL中发布DDL语句.

No. You cannot issue DDL statements in static PL/SQL.

是的，可以将本机动态SQL用于DDL:

And yes, it is perfectly fine to use native dynamic SQL for DDL purposes:

以下您需要动态SQL 情况:

You need dynamic SQL in the following situations:

您要执行SQL数据 定义语句(例如CREATE)， 数据控制语句(例如 GRANT)或会话控制语句 (例如ALTER SESSION).在PL/SQL中， 此类语句无法执行 静态地.

You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed statically.

Oracle动态SQL

这篇关于删除表空间(如果不存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！