更新时间:2023-02-04 08:39:29
避免使用动态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;