Oracle运行时动态切换多客户独立表的实现方案咨询

带表名验证的SQL宏实现（推荐） #

这是最贴合需求的方案，既满足动态访问的便捷性，又能避免SQL注入，同时保证查询性能：

1. 创建表名验证函数 #

先实现一个函数，校验传入的客户名对应的_INFO表是否存在于数据字典：

CREATE OR REPLACE FUNCTION VALIDATE_CLIENT_TABLE(P_CLIENT VARCHAR2) RETURN VARCHAR2 IS
  V_TABLE_NAME VARCHAR2(30) := UPPER(P_CLIENT) || '_INFO';
  V_EXISTS NUMBER;
BEGIN
  SELECT COUNT(1) INTO V_EXISTS
  FROM USER_TABLES
  WHERE TABLE_NAME = V_TABLE_NAME;
  
  IF V_EXISTS = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '客户表 ' || V_TABLE_NAME || ' 不存在');
  END IF;
  
  RETURN V_TABLE_NAME;
END;
/

2. 创建SQL表宏 #

基于验证函数创建表宏，Oracle 12c及以上版本支持：

CREATE OR REPLACE FUNCTION FN_CLIENT_INFO(P_CLIENT VARCHAR2) 
RETURN VARCHAR2 SQL_MACRO(TABLE) IS
  V_TABLE_NAME VARCHAR2(30);
BEGIN
  V_TABLE_NAME := VALIDATE_CLIENT_TABLE(P_CLIENT);
  RETURN 'SELECT ID, SOMEVAL1, SOMEVAL2 FROM ' || V_TABLE_NAME;
END;
/

使用示例 #

• 查询操作（自动利用目标表索引）：

SELECT * FROM FN_CLIENT_INFO('ACME') WHERE SOMEVAL1 = 4;

数据库会将宏展开为直接访问ACME_INFO的SQL，优化器能正常识别并使用该表上的索引，性能和直接查表一致。

• 更新操作（需目标表ID为唯一键/主键）：

UPDATE (SELECT * FROM FN_CLIENT_INFO('ACME')) SET SOMEVAL1 = 444 WHERE ID = 3;

替代方案：DBMS_TF表函数（Oracle 18c+） #

若SQL宏受环境限制无法使用，可采用表函数结合DBMS_TF实现：

1. 创建自定义类型 #

CREATE OR REPLACE TYPE CLIENT_INFO_REC IS OBJECT (
  ID NUMBER,
  SOMEVAL1 NUMBER,
  SOMEVAL2 VARCHAR2(100)
);
/

CREATE OR REPLACE TYPE CLIENT_INFO_TAB IS TABLE OF CLIENT_INFO_REC;
/

2. 创建表函数及实现包 #

CREATE OR REPLACE FUNCTION FN_CLIENT_INFO_TF(P_CLIENT VARCHAR2) 
RETURN CLIENT_INFO_TAB PIPELINED
USING DBMS_TF.DECLARE_TF(
  NAME => 'FN_CLIENT_INFO_TF_IMPL',
  PARAMETERS => DBMS_TF.PARAMETER_LIST(
    DBMS_TF.PARAMETER('P_CLIENT', DBMS_TF.TYPE_VARCHAR2)
  ),
  TABLE_FUNCTION => TRUE
);
/

CREATE OR REPLACE PACKAGE BODY FN_CLIENT_INFO_TF_IMPL IS
  PROCEDURE FETCH_ROWS(P_CONTEXT IN OUT DBMS_TF.CONTEXT_T) IS
    V_PARAMS DBMS_TF.PARAMETER_SET_T;
    V_CLIENT VARCHAR2(30);
    V_TABLE_NAME VARCHAR2(30);
    V_CURSOR SYS_REFCURSOR;
    V_REC CLIENT_INFO_REC;
  BEGIN
    -- 获取传入参数
    DBMS_TF.GET_PARAMETERS(P_PARAMS);
    V_CLIENT := P_PARAMS('P_CLIENT').VALUE.VARCHAR2_VALUE;
    
    -- 验证表存在
    V_TABLE_NAME := VALIDATE_CLIENT_TABLE(V_CLIENT);
    
    -- 查询目标表并输出结果
    OPEN V_CURSOR FOR 'SELECT ID, SOMEVAL1, SOMEVAL2 FROM ' || V_TABLE_NAME;
    LOOP
      FETCH V_CURSOR INTO V_REC.ID, V_REC.SOMEVAL1, V_REC.SOMEVAL2;
      EXIT WHEN V_CURSOR%NOTFOUND;
      PIPE ROW(V_REC);
    END LOOP;
    CLOSE V_CURSOR;
  END FETCH_ROWS;
END;
/

该方案同样支持查询和更新，但性能略低于SQL宏，因为涉及数据类型转换。

方案优势总结 #

• SQL宏方案完全等价于直接访问目标表，优化器可正常解析索引，满足报表平台性能要求；
• 表名验证从数据字典校验，彻底规避SQL注入风险；
• 操作语法完全匹配需求，无需修改现有报表逻辑。

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