使用Oracle数据库链接而无需不可读的动态SQL [英] Using Oracle database links without unreadable dynamic SQL

问题描述

如何编写可以针对给定的数据库链接执行的整洁的PL/SQL存储过程?

How can I write a neat PL/SQL stored procedure that can execute against a given database link?

它真的很乱，写这样的东西:

It gets really messy, writing stuff like this:

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

随着查询的扩大.

我还能做什么?我一直在使用存储过程，并希望我的过程将针对几个数据库链接之一执行.

What else might I do? I'm stuck using stored procedures, and expect that my procedures will execute against one of several db links.

推荐答案

避免使用动态SQL的最简单方法是创建同义词.

The simplest way to avoid using dynamic SQL would be to create synonyms.

CREATE OR REPLACE SYNONYM MyTableRemote
   FOR MyTable@database_link

然后，您的存储过程将简单地引用同义词MyTableRemote.然后，您可以有一个单独的方法，该方法将数据库链接名称作为参数，并更改所有同义词以指向数据库链接.

Your stored procedures would then simply refer to the synonym MyTableRemote. You could then have a separate method that took the database link name as a parameter and changed all the synonyms to point at the database link.

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;

这篇关于使用Oracle数据库链接而无需不可读的动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！