如何为多个DB_LINK创建PL/SQL的%ROWTYPE类型变量

PL/SQL编译时无法识别以下代码行，触发PLS-00201（标识符必须声明）和PLS-00352（无法访问指定数据库链接）错误：

my_table_rec my_data_table@g_db_link%ROWTYPE;

场景说明：my_data_table在所有源远程数据库和本地目标数据库中结构完全一致，需要编写通用存储过程，通过传入不同数据库链接参数，批量处理多个远程库的数据。

原存储过程代码：

CREATE OR REPLACE PACKAGE BODY concatenator_pkg
AS
-- 从多个远程数据库的同名表读取数据，插入到本地目标表
PROCEDURE my_proc (g_db_link VARCHAR2) IS
  l_my_query VARCHAR2(5000) := 'SELECT * FROM my_data_table@'||g_db_link;
  get_my_data_cursor SYS_REFCURSOR;
  my_table_rec my_data_table@g_db_link%ROWTYPE;  -- 此处报错
BEGIN
  OPEN get_my_data_cursor FOR l_my_query;
  LOOP
    FETCH get_my_data_cursor INTO my_table_rec;
    EXIT WHEN get_my_data_cursor%notfound;
    -- 数据处理逻辑
  END LOOP;
END my_proc;
END concatenator_pkg;

调用代码：

DECLARE
  l_db_link VARCHAR(50);
BEGIN
  l_db_link := 'mv_dev_db_01';
  concatenator_pkg.my_proc(l_db_link);

  l_db_link := 'mv_dev_db_02';
  concatenator_pkg.my_proc(l_db_link);

  l_db_link := 'mv_dev_db_03';
  concatenator_pkg.my_proc(l_db_link);
END;

解决方案 #

方案1：使用本地表的%ROWTYPE（推荐） #

既然本地目标库也有结构完全一致的my_data_table，直接用本地表的行类型定义变量即可。编译时本地表类型是确定的，远程表结构匹配，游标fetch时可以完美兼容：

CREATE OR REPLACE PACKAGE BODY concatenator_pkg
AS
PROCEDURE my_proc (g_db_link VARCHAR2) IS
  l_my_query VARCHAR2(5000) := 'SELECT * FROM my_data_table@'||g_db_link;
  get_my_data_cursor SYS_REFCURSOR;
  -- 改用本地表的%ROWTYPE替代远程表动态链接的类型
  my_table_rec my_data_table%ROWTYPE;
BEGIN
  OPEN get_my_data_cursor FOR l_my_query;
  LOOP
    FETCH get_my_data_cursor INTO my_table_rec;
    EXIT WHEN get_my_data_cursor%notfound;
    -- 执行数据处理逻辑
  END LOOP;
END my_proc;
END concatenator_pkg;

方案2：自定义PL/SQL记录类型 #

如果本地没有同名表，可以提前定义和my_data_table结构完全一致的记录类型：

-- 先创建自定义对象类型，字段和my_data_table完全匹配
CREATE OR REPLACE TYPE my_data_rec_type AS OBJECT (
  col1 NUMBER,
  col2 VARCHAR2(100),
  col3 DATE
  -- 按表的实际字段逐一添加
);
/

CREATE OR REPLACE PACKAGE BODY concatenator_pkg
AS
PROCEDURE my_proc (g_db_link VARCHAR2) IS
  l_my_query VARCHAR2(5000) := 'SELECT * FROM my_data_table@'||g_db_link;
  get_my_data_cursor SYS_REFCURSOR;
  my_table_rec my_data_rec_type;
BEGIN
  OPEN get_my_data_cursor FOR l_my_query;
  LOOP
    FETCH get_my_data_cursor INTO my_table_rec;
    EXIT WHEN get_my_data_cursor%notfound;
    -- 数据处理逻辑
  END LOOP;
END my_proc;
END concatenator_pkg;

注意：后续表结构变更时，需要同步更新自定义类型，否则会出现类型不匹配问题。

方案3：动态PL/SQL（复杂场景适用） #

如果必须基于远程表的类型处理，可以用动态SQL在运行时解析类型。这种方式避开编译时的类型检查，但调试和维护难度会增加：

CREATE OR REPLACE PACKAGE BODY concatenator_pkg
AS
PROCEDURE my_proc (g_db_link VARCHAR2) IS
  l_dynamic_sql VARCHAR2(10000);
BEGIN
  l_dynamic_sql := 'DECLARE
                      my_table_rec my_data_table@'||g_db_link||'%ROWTYPE;
                      get_my_data_cursor SYS_REFCURSOR;
                    BEGIN
                      OPEN get_my_data_cursor FOR ''SELECT * FROM my_data_table@'||g_db_link||''';
                      LOOP
                        FETCH get_my_data_cursor INTO my_table_rec;
                        EXIT WHEN get_my_data_cursor%NOTFOUND;
                        -- 在这里编写数据处理逻辑
                        -- 例：DBMS_OUTPUT.PUT_LINE(my_table_rec.col1);
                      END LOOP;
                      CLOSE get_my_data_cursor;
                    END;';
  EXECUTE IMMEDIATE l_dynamic_sql;
END my_proc;
END concatenator_pkg;

内容的提问来源于stack exchange，提问作者prain99