Использование ссылок на базу данных Oracle без нечитаемого динамического SQL

Question

Как я могу написать аккуратную хранимую процедуру PL/SQL, которая может выполняться для данной ссылки на базу данных?

Это становится действительно грязно, писать такие вещи, как это:

   PROCEDURE my_proc(aDbLink IN VARCHAR2)
   IS
   BEGIN       
       EXECUTE IMMEDIATE '
       SELECT mycolumn, anothercolumn
       FROM MYTABLE@' || aDbLink || '
       WHERE such-and-such...'
   END

как запрос становится больше.

Что еще я могу сделать? Я застрял, используя хранимые процедуры, и ожидаю, что мои процедуры будут выполняться по одной из нескольких ссылок на БД.

Answer 1

Самый простой способ избежать использования динамического SQL - создать синонимы.

CREATE OR REPLACE SYNONYM MyTableRemote
   FOR MyTable@database_link

Ваши хранимые процедуры будут просто ссылаться на синоним MyTableRemote, Тогда вы могли бы иметь отдельный метод, который бы брал имя ссылки на базу данных в качестве параметра и изменял все синонимы так, чтобы они указывали на ссылку на базу данных.

PROCEDURE replace_synonyms( p_db_link IN VARCHAR2 )
AS
BEGIN
  -- Adjust the query to identify all the synonyms that you want to recreate
  FOR syn IN (SELECT *
                FROM user_synonyms
               WHERE db_link IS NOT NULL)
  LOOP
    EXECUTE IMMEDIATE 
      'CREATE OR REPLACE SYNONYM ' || syn.synonym_name ||
      '   FOR ' || syn.table_owner || '.' || syn.table_name || '@' || p_db_link;
  END LOOP;
END;

Answer 2

Если вы не хотите использовать идею синонима, вы можете попробовать этот метод - использовать REPLACE и собственный синтаксис для генерации SQL - я считаю, что этот метод упрощает отладку динамического SQL:

PROCEDURE my_proc(aDbLink IN VARCHAR2)
IS
BEGIN       
    EXECUTE IMMEDIATE REPLACE('
    SELECT mycolumn, anothercolumn
    FROM MYTABLE@#DBLINK#
    WHERE such-and-such...'
       ,'#DBLINK#', aDbLink);
END