如何检查同义词是否已存在，如果存在则不创建同义词。

Question

我正在使用Oracle SQL Developer 2.1来创建同义词。

CREATE OR REPLACE SYNONYM "ETKS_PR_RW"."SQ_CLDOS_ATCHMNT_ID" 
    FOR "CLDOS_ONLINE_DBA"."SQ_CLDOS_ATCHMNT_ID";

如果这个同义词已经存在，我该如何检查并且不创建重复的同义词？

- SOF User

Answer 1

由于您使用了 replace 关键字，因此无需先检查同义词是否存在。您将使用先前名称覆盖任何已存在的同义词。

唯一需要注意使用 replace 的原因是如果可能有不同的同义词具有相同的名称。如果您的数据库组织良好，则不应发生这种情况。您应该始终知道所有对象以及同义词指向哪里。

但是，如果您确实希望这样做，有几个选项：

1. 删除 replace。如果同义词已经存在，则语句将引发错误并且不会被覆盖。

2. 查询数据字典，因为您在多个模式中，all_synonyms 似乎是最好的选择。

select *
  from all_synonyms
 where owner = 'ETKS_PR_RW'
   and synonym_name = 'SQ_CLDOS_ATCHMNT_ID';

如果您想将它们合并为一个单独的块，您可以这样做：

declare

   l_exists number;

begin
   -- check whether the synonym exists
   select 1
     into l_exists
     from all_synonyms
    where owner = 'ETKS_PR_RW'
      and synonym_name = 'SQ_CLDOS_ATCHMNT_ID';

-- an error gets raise if it doesn-t.
exception when no_data_found then
   -- DDL has to be done inside execute immediate in a block.
   execute immediate 'CREATE OR REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID 
                   FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID';

end;
/

稍微与此有些不同的是，请不要引用您的对象名称。Oracle可以有大小写对象，但很少值得麻烦。所有对象将自动大写，所以您不需要"。

- Ben

Answer 2

我认为如果您删除OR REPLACE关键字，它会提示您该对象已存在

或者您可以使用这些表创建PL/SQL代码

desc dba_synonyms
desc user_synonyms

为了使其更加灵活和定制化

假设使用Oracle PL/SQL

DECLARE
    src_schema    VARCHAR2(256) := 'EMPIK_DYNAMO_01';
    target_schema VARCHAR2(256) := 'EMPIK_PORTAL_BETA_1';
    CURSOR src_objects IS
      SELECT table_name AS object_name
      FROM   all_all_tables
      WHERE  owner = src_schema
      UNION
      SELECT sequence_name AS object_name
      FROM   all_sequences
      WHERE  sequence_owner = src_schema;
BEGIN
    FOR next_row IN src_objects LOOP
        BEGIN
            EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM '|| target_schema|| '.'
            ||
            next_row.object_name|| ' for '|| src_schema|| '.'||
            next_row.object_name;
        EXCEPTION
            WHEN OTHERS THEN
              dbms_output.Put_line('ERROR WHILE CREATING SYNONYM FOR: '
                                   || next_row.object_name);

              dbms_output.Put_line(SQLERRM);
        END;
    END LOOP;
END;

/ 

这里是针对您的问题的定制化解决方案

BEGIN
    EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID';  
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line ('ERROR WHILE CREATING SYNONYM FOR: SQ_CLDOS_ATCHMNT_ID');
      dbms_output.Put_line (SQLERRM);
END; 

- shareef

---

你能告诉我选项2的语法吗？这就是我要找的。 - SOF User

请提供更多信息，您的问题不明确：如果您希望创建一个同义词并检测其存在，则选择上面的选项1；如果您想为模式中的对象创建同义词，则选择选项2并放置适当的where条件。请详细说明您的问题，并编辑您的问题。 - shareef

你能否按照我的情况发布脚本，使得我可以创建同义词，如果已存在则不会创建。 - SOF User

如果你删除了replace，它就不会提示你，你的代码块将永远不会抛出错误，也绝对不会出现dbms_output.put_line错误。这样做毫无意义，只会掩盖你应该处理的错误。 - Ben

@shareef，不是我给你点了踩，但我不同意你的说法。第二个可能会出现错误，但只有在您没有正确的权限时才会出现。dbms_output.put_line是处理错误的非常糟糕的方式。您必须在那里才能注意到它们！此外，我真的不理解您最新更新的原因。这过于复杂了。 - Ben