如何无需构造函数初始化元素数量未知的Oracle二维表

问题背景 #

现有代码通过构造函数直接传入固定数量的一维表实例初始化二维表，但当需要创建任意数量的一维表实例来组成二维表时，无法提前确定构造函数的参数个数。要求使用Schema级SQL类型，实现动态指定数量的初始化，比如通过INIT函数传入模板和次数完成：

arr2d := tt_v2_255_arr_arr(INIT(arr1d, NUM_TIMES));

原固定数量初始化的代码示例：

CREATE OR REPLACE TYPE tt_v2_255_arr IS TABLE OF VARCHAR2(255);

CREATE OR REPLACE TYPE tt_v2_255_arr_arr IS TABLE OF tt_v2_255_arr;

declare
    arr2d tt_v2_255_arr_arr;
    arr1d tt_v2_255_arr := tt_v2_255_arr();
begin
    arr1d.extend(2);
    -- 固定传入2个一维表实例初始化
    arr2d := tt_v2_255_arr_arr(arr1d, arr1d);
    
    arr2d(1)(1) := 'A';
    arr2d(1)(2) := 'B';
    arr2d(2)(1) := '1';
    arr2d(2)(2) := '2';
    dbms_output.put_line(arr2d(1)(2));
exception when others then
    dbms_output.put_line(sqlerrm);
end;
/

解决方案 #

创建一个Schema级的初始化函数，接收一维表模板和需要创建的实例数量，动态生成并返回二维表：

1. 创建初始化函数 #

CREATE OR REPLACE FUNCTION INIT(p_template tt_v2_255_arr, p_num_times NUMBER) 
RETURN tt_v2_255_arr_arr
IS
    v_result tt_v2_255_arr_arr := tt_v2_255_arr_arr();
BEGIN
    -- 校验输入参数合法性
    IF p_num_times < 1 THEN
        RAISE_APPLICATION_ERROR(-20001, '次数必须大于等于1');
    END IF;
    
    -- 循环生成指定数量的独立一维表实例
    FOR i IN 1..p_num_times LOOP
        v_result.EXTEND;
        -- 复制模板实例，避免多个二维表元素指向同一个一维表对象
        v_result(i) := p_template.clone();
    END LOOP;
    
    RETURN v_result;
END INIT;
/

2. 调用函数动态初始化二维表 #

DECLARE
    arr2d tt_v2_255_arr_arr;
    arr1d tt_v2_255_arr := tt_v2_255_arr();
    NUM_TIMES NUMBER := 3; -- 可动态指定任意正整数
BEGIN
    -- 初始化一维表模板
    arr1d.extend(2);
    arr1d(1) := '模板值1';
    arr1d(2) := '模板值2';
    
    -- 调用INIT函数创建包含NUM_TIMES个一维表的二维表
    arr2d := INIT(arr1d, NUM_TIMES);
    
    -- 修改部分一维表内容，验证实例独立性
    arr2d(1)(1) := '修改后的A';
    arr2d(2)(1) := '修改后的1';
    
    -- 输出测试结果
    FOR i IN 1..arr2d.COUNT LOOP
        dbms_output.put_line('第' || i || '个一维表：');
        dbms_output.put_line('  元素1：' || arr2d(i)(1));
        dbms_output.put_line('  元素2：' || arr2d(i)(2));
    END LOOP;
EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
END;
/

关键说明 #

• 使用clone()方法复制一维表模板，确保二维表中的每个一维表都是独立实例，避免修改一个实例影响其他实例
• 函数内置参数校验，防止非法的次数输入
• 通过EXTEND方法动态扩展二维表容量，支持任意正整数数量的初始化

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