PL/SQL存储过程编写求助：基于两表主键创建关联新表

需求说明 #

编写一个PL/SQL存储过程，接收两个现有数据库表名作为参数，完成以下操作：

• 识别两个表的主键列
• 创建新表，新表列沿用对应主键列的数据类型
• 新表通过外键关联两个参数表的主键
• 输出新表名称或自定义错误信息（非ORA错误）

原代码问题分析 #

原代码存在以下关键问题：

• 动态SQL中错误嵌入PL/SQL循环语法（动态SQL仅支持纯SQL语句）
• 变量引用错误（如未定义的l_cnt、未声明的err）
• 使用不存在的listing函数，应使用Oracle内置LISTAGG进行列名拼接
• 未正确获取主键列的数据类型，无法在动态SQL中直接使用%TYPE
• 多主键列的外键约束未正确拼接列列表

正确实现方案 #

以下是修正后的存储过程，解决了多主键列的动态SQL构建问题：

create or replace procedure create_link_table (p_table_a varchar2, p_table_b varchar2)
is
    -- 存储表A的主键列信息：列名、数据类型（按主键顺序拼接）
    l_pk_cols_a        varchar2(32767);
    l_pk_col_defs_a    varchar2(32767);
    -- 存储表B的主键列信息
    l_pk_cols_b        varchar2(32767);
    l_pk_col_defs_b    varchar2(32767);
    -- 新表名称（自定义为两表名拼接格式）
    l_new_table_name   varchar2(128) := 'LINK_' || upper(p_table_a) || '_' || upper(p_table_b);
    -- 自定义异常
    e_no_pk_exception  exception;
    e_table_not_exists exception;
    -- 异常错误码映射
    pragma exception_init(e_no_pk_exception, -20001);
    pragma exception_init(e_table_not_exists, -20002);
    l_sql              varchar2(32767);
begin
    -- 验证表A存在且获取主键列及数据类型
    begin
        select 
            listagg(cols.column_name, ', ') within group (order by cols.position) as pk_cols,
            listagg(cols.column_name || '_A ' || cols.data_type || 
                    case when cols.data_type in ('VARCHAR2', 'CHAR') then '(' || cols.data_length || ')'
                         when cols.data_type in ('NUMBER') then case when cols.precision is not null then '(' || cols.precision || ',' || cols.scale || ')' end
                    end, ', ') within group (order by cols.position) as col_defs
        into l_pk_cols_a, l_pk_col_defs_a
        from all_constraints cons
        join all_cons_columns cols on cons.constraint_name = cols.constraint_name
        join all_tab_columns tab_cols on cols.table_name = tab_cols.table_name 
                                     and cols.column_name = tab_cols.column_name
        where cons.table_name = upper(p_table_a)
          and cons.constraint_type = 'P';
    exception
        when no_data_found then
            -- 检查表是否存在
            declare
                l_exists number;
            begin
                select 1 into l_exists from all_tables where table_name = upper(p_table_a);
                raise e_no_pk_exception; -- 表存在但无主键
            exception
                when no_data_found then
                    raise e_table_not_exists; -- 表不存在
            end;
    end;

    -- 验证表B存在且获取主键列及数据类型
    begin
        select 
            listagg(cols.column_name, ', ') within group (order by cols.position) as pk_cols,
            listagg(cols.column_name || '_B ' || cols.data_type || 
                    case when cols.data_type in ('VARCHAR2', 'CHAR') then '(' || cols.data_length || ')'
                         when cols.data_type in ('NUMBER') then case when cols.precision is not null then '(' || cols.precision || ',' || cols.scale || ')' end
                    end, ', ') within group (order by cols.position) as col_defs
        into l_pk_cols_b, l_pk_col_defs_b
        from all_constraints cons
        join all_cons_columns cols on cons.constraint_name = cols.constraint_name
        join all_tab_columns tab_cols on cols.table_name = tab_cols.table_name 
                                     and cols.column_name = tab_cols.column_name
        where cons.table_name = upper(p_table_b)
          and cons.constraint_type = 'P';
    exception
        when no_data_found then
            declare
                l_exists number;
            begin
                select 1 into l_exists from all_tables where table_name = upper(p_table_b);
                raise e_no_pk_exception;
            exception
                when no_data_found then
                    raise e_table_not_exists;
            end;
    end;

    -- 构建创建新表的动态SQL
    l_sql := 'CREATE TABLE ' || l_new_table_name || ' (' || chr(10)
           || l_pk_col_defs_a || ', ' || chr(10)
           || l_pk_col_defs_b || ', ' || chr(10)
           -- 构建表A的外键约束（支持多列主键）
           || 'CONSTRAINT FK_' || upper(p_table_a) || '_LINK FOREIGN KEY (' 
           || replace(l_pk_cols_a, ', ', '_A, ') || '_A) ' || chr(10)
           || 'REFERENCES ' || upper(p_table_a) || '(' || l_pk_cols_a || '), ' || chr(10)
           -- 构建表B的外键约束（支持多列主键）
           || 'CONSTRAINT FK_' || upper(p_table_b) || '_LINK FOREIGN KEY (' 
           || replace(l_pk_cols_b, ', ', '_B, ') || '_B) ' || chr(10)
           || 'REFERENCES ' || upper(p_table_b) || '(' || l_pk_cols_b || ') ' || chr(10)
           || ')';

    -- 执行动态SQL
    execute immediate l_sql;

    -- 输出成功信息
    dbms_output.put_line('新表创建成功：' || l_new_table_name);

exception
    when e_table_not_exists then
        dbms_output.put_line('错误：输入的表不存在');
    when e_no_pk_exception then
        dbms_output.put_line('错误：输入的表无主键');
    when others then
        dbms_output.put_line('错误：' || sqlerrm);
end create_link_table;
/

关键实现说明 #

1. 主键信息获取：通过all_constraints、all_cons_columns和all_tab_columns联合查询，获取主键列名、数据类型及精度，使用LISTAGG按主键顺序拼接列列表和列定义。
2. 多主键处理：外键约束中，将原主键列名拼接后缀（如_A、_B）作为新表列名，同时按顺序拼接多列作为外键列和引用列。
3. 错误处理：区分表不存在和表无主键两种自定义错误，同时捕获其他异常输出错误信息。
4. 动态SQL构建：纯SQL语句拼接，避免嵌入PL/SQL语法，确保执行合法性。

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