PL/SQL根据数据库对象的存在进行有条件编译

Question

是否可以在Oracle中进行条件编译，条件是数据库对象(特别是表、视图或同义词(的存在？我希望能够做这样的事情：

sp_some_procedure is
    $IF /*check if A exists.*/ then 
        /* read from and write to A as well as other A-related non-DML stuff...*/
    $ELSE /*A doesn't exist yet, so avoid compiler errors*/
        dbms_output.put_line('Reminder: ask DBA to create A!')
    $ENDIF
end;

Answer 1

是的。这里是第一个存储过程要从XALL_TABLES中选择的示例，但如果该表不存在，请从dual中选择。最后，因为我没有XALL_TABLES对象，所以第一个存储过程从dual中选择。第二个对ALL_TABLES对象执行相同的操作。因为存在ALL_TABLES，所以第二个存储过程从ALL_TABLES中进行选择，而不是从DUAL中进行选择。

这种构造在包必须部署在所有数据库上并使用并非到处都部署的表的情况下非常有用。。。(好吧，也许有一个概念上的问题，但它确实发生了(。

--conditionals compilation instructions accept only static condition (just with constants)
--passing sql bind variable doesn't work 
--To pass a value to a conditional compilation instruction, I bypasses the use of input parameters of the script
--these 4 next lines affect a value to the first and the second input parameter of the script
--If your originally script use input script parameter, use the next free parameter ...
column param_1 new_value 1 noprint
select nvl(max(1), 0) param_1 from all_views where owner = 'SYS' and view_name = 'XALL_TABLES';
column param_2 new_value 2 noprint
select nvl(max(1), 0) param_2 from all_views where owner = 'SYS' and view_name = 'ALL_TABLES';
CREATE or replace PACKAGE my_pkg AS
  function test_xall_tables return varchar2;
  function test_all_tables return varchar2;
END my_pkg;
/
CREATE or replace PACKAGE BODY my_pkg AS
  function test_xall_tables return varchar2 is
    vch varchar2(50);
  begin
    $IF (&1 = 0) $THEN
      select 'VIEW XALL_TABLES D''ONT EXISTS' into vch from dual;
    $ELSE
      select max('VIEW XALL_TABLES EXISTS') into vch from XALL_TABLES;
    $END        
    return vch;      
  end test_xall_tables;
  function test_all_tables return varchar2 is
    vch varchar2(50);
  begin
    $IF (&2 = 0) $THEN
      select 'VIEW ALL_TABLES D''ONT EXISTS' into vch from dual;
    $ELSE
      select max('VIEW ALL_TABLES EXISTS') into vch from ALL_TABLES;
    $END
    return vch;
  end test_all_tables;             
END my_pkg;
/

测试：

select my_pkg.test_xall_tables from dual;

给出

视图XALL_TABLES D'ONT存在

select my_pkg.test_all_tables from dual;

给出

查看所有表存在

Answer 2

我会使用'EXECUTE IMMEDIATE'和EXCEPTION子句。

Answer 3

使用动态SQL创建包常量来跟踪存在的对象，然后在条件编译中使用这些常量。

--E.g., say there are two possible tables, but only one of them exists.
--create table table1(a number);
create table table2(a number);

--Create a package with boolean constants to track the objects.
--(Another way to do this is to use ALTER SESSION SET PLSQL_CCFLAGS)
declare
  table1_exists_string varchar2(10) := 'true';
  table2_exists_string varchar2(10) := 'true';
  temp number;
begin
  begin
    execute immediate 'select max(1) from table1 where rownum <= 1' into temp;
  exception when others then
    table1_exists_string := 'false';
  end;
  begin
    execute immediate 'select max(1) from table2 where rownum <= 1' into temp;
  exception when others then
    table2_exists_string := 'false';
  end;
  execute immediate '
    create or replace package objects is
      table1_exists constant boolean := '||table1_exists_string||';
      table2_exists constant boolean := '||table2_exists_string||';
    end;
  ';
end;
/
--Look at the results in the source:
select * from user_source where name = 'OBJECTS';

--Create the object that refers to the tables.
create or replace function compile_test return varchar2 is
    v_test number;
begin
    $if objects.table1_exists $then
        select max(1) into v_test from table1;
        return 'table1 exists';
    $elsif objects.table2_exists $then
        select max(1) into v_test from table2;
        return 'table 2 exists';
    $else
    return 'neither table exists';
    $end
end;
/
--Check the dependencies - only TABLE2 is dependent.
select * from user_dependencies where name = 'COMPILE_TEST';
--Returns 'table 2 exists'.
select compile_test from dual;

混合动态SQL、动态PL/SQL和条件编译通常是一个非常邪恶的想法。但它将允许您将所有丑陋的动态SQL放在一个安装包中，并维护真正的依赖性跟踪。

这可以在半动态环境中很好地工作；例如安装有不同对象集但不经常在它们之间改变的程序。

(此外，如果这一切只是为了用友好的警告取代可怕的错误消息，在我看来，这是一个非常糟糕的主意。如果你的系统要失败，故障应该是明显的，这样它就可以立即修复。大多数人忽略任何以"提醒…"开头的内容。(

Answer 4

否-这是不可能的。。。但是，如果您创建一个引用不存在的DB对象的存储过程并尝试编译它，则编译将显示错误。。。存储过程将存在，但"无效"。。。DBA在查看时都可以访问编译错误…所以我只需要继续创建所有需要的存储过程，如果出现任何编译错误，请询问DBA(有时对象存在，但存储过程需要访问它的权限…(…在修复错误原因后，您可以重新编译存储过程(通过ALTER PROCEDURE MySchema.MyProcName COMPILE;(，一切都很好。。。

如果你不想让代码出现在那里，你可以只使用DROP，选通过程和/或替换是通过CREATE OR REPLACE。。。体内有CCD_ 4。

唯一的另一种选择是kevin指出EXECUTE IMMEDIATE具有正确的EXCEPTION处理。。。

Answer 5

我要做的是通过all_objects检查是否存在，比如：

declare
l_check_sql varchar2(4000);
l_cnt number;
begin
l_check_sql := q'{
select count(1)
from all_objects
where object_name = 'MY_OBJ'
and owner = 'MY_OWNER'
}';
execute immediate l_check_sql into l_cnt;
if (l_cnt > 0) then
  -- do something referring to MY_OBJ
else
  -- don't refer to MY_OBJ
end if;
end;