时间戳

首页 > 解决方案 > 如何执行动态过程

sql - 如何执行动态过程

问题描述

我正在尝试创建一个动态过程，该过程填充表中已经存在的数据中的表。我有两个主列变量，而不是主键列。我将一个序列（generate.nextval）传递给主键列。它将插入作为参数（数量）传递的特定行数

Create or replace procedure v_populate (table_name in varchar2, amount in number)
IS
v_pk varchar2(1000);
v_pk2 varchar2(1000);
V_dyntask LONG;
V_dyntask2 LONG;

CURSOR C1 IS 
'select REPLACE(REPLACE(REPLACE(xmlagg(xmlforest(cols.column_name ) 
ORDER BY cols.column_name ),''</COLUMN_NAME><COLUMN_NAME>'','',''),''<COLUMN_NAME>''),''</COLUMN_NAME>'') XMLAGG  --cols.column_name 
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name ='''||table_name||'''AND cons.constraint_type NOT IN (''P'',''U'',''R'')
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position';

CURSOR C2 IS
'select 
                    REPLACE( 
                      REPLACE(
                        REPLACE(xmlagg(xmlforest(cols.column_name ) ORDER BY cols.column_name )
                               ,''</COLUMN_NAME><COLUMN_NAME>'','','')
                              ,''<COLUMN_NAME>'')
                             ,''</COLUMN_NAME>'') XMLAGG --cols.column_name 
               FROM all_constraints cons, all_cons_columns cols
              WHERE cols.table_name ='''||table_name||'''
                AND cons.constraint_type = ''P''
                AND cons.constraint_name = cols.constraint_name
                AND cons.owner = cols.owner
           ORDER BY cols.table_name, cols.position
              ';

BEGIN

FOR F1 in C1 
LOOP
v_pk:=C1.COLUMN_NAME;
END LOOP;

FOR F2 in C2 
LOOP
v_pk2:=C2.COLUMN_NAME;
END LOOP;

V_dyntask2:= 'INSERT INTO ' ||table_name|| ' 
                        ( '||v_pk2||' , '||v_pk||' ) 
                 select generate.nextval,'||v_pk||' 
                   from ' ||table_name || '
                  where rownum <='||amount;
EXECUTE IMMEDIATE V_dyntask2;
COMMIT;
end;

我不断收到此错误：PLS-00103：遇到符号“select REPLACE(REPLACE(REPLACE(xmlagg(xmlforest(cols.column_name)”时期待以下之一：(选择符号“select”被替换为“select REPLACE( REPLACE(REPLACE(xmlagg(xmlforest(cols.column_name" 继续。PLS-00103：遇到符号“;”) 当期望以下之一时：, * & - + / 在 mod 剩余 rem 来自 ||

标签： sqloraclestored-proceduresplsqloracle-sqldeveloper

解决方案

光标不需要动态字符串，也不需要处理 XML。我会像下面那样做。

CREATE OR REPLACE PROCEDURE v_populate (table_name IN VARCHAR2, amount IN NUMBER)

    v_pk VARCHAR2(1000);
    v_pk2 VARCHAR2(1000);
    sqlstr VARCHAR2(30000);

    CURSOR C1 IS
    SELECT cols.column_name
    FROM all_constraints cons
        JOIN all_cons_columns cols USING (OWNER, constraint_name)
    WHERE cols.table_name = table_name
        AND cons.constraint_type NOT IN ('P','U','R');
    ORDER BY cols.column_name; -- For check constraints ('C') POSITION is NULL, thus 'ORDER BY POSITION' is pointless!

    CURSOR C2 IS
    SELECT cols.column_name, cols.position
    FROM all_constraints cons
        JOIN all_cons_columns cols USING (OWNER, constraint_name)
    WHERE cols.table_name = table_name
        AND cons.constraint_type = 'P';

BEGIN

    sqlstr := 'INSERT INTO ' ||table_name|| ' ( ';
    FOR aCol IN C2 LOOP
        sqlstr := sqlstr || aCol.COLUMN_NAME||',';
    END LOOP;

    FOR aCol IN C1 LOOP
        sqlstr := sqlstr || aCol.COLUMN_NAME||',';
    END LOOP;
    sqlstr := REGEXP_REPLACE(sqlstr, ',$', ') ');

    sqlstr := sqlstr || 'SELECT generate.nextval,';
    FOR aCol IN C1 LOOP
        sqlstr := sqlstr || aCol.COLUMN_NAME||',';
    END LOOP;
    sqlstr := REGEXP_REPLACE(sqlstr, ',$', ' ');
    sqlstr := sqlstr || 'FROM ' ||table_name|| ' WHERE ROWNUM <= :a';

    DBMS_OUTPUT.PUT_LINE ( sqlstr ); -- Only for testing to verify the generated command

    EXECUTE IMMEDIATE sqlstr USING amount;

END;

请注意，如果您的表具有复合主键，则该过程将失败，即主键由多于一列构建。如果您的表根本没有主键，它也会失败。

也许您应该跳过主键并generate.nextval在插入触发器之前通过行级应用。

请注意，17 年前的 Oracle 10g 也已经支持现代 ANSI 连接语法。

推荐阅读