最近维护时，会更新客户的数据库表记录，在更新同时，需要执行插入语句。

生成插入语句时，那些记录需要一个个的生产，影响工作效率，为了方便，搞了一个过程自动生成insert all into 执行语句。

希望对大家有帮助！

背景：

1.我事先在测试库中已经生成了自己的需要的数据，需要把这些表数据转移到客户那里。

2.为了远程客户那里直接执行insert all into 命令。

DECLARE

-- v_Prefix VARCHAR2(10) := '''';

-- v_Suffix VARCHAR2(10) := '''';

v_Sql VARCHAR2(2000);

v_Column VARCHAR2(1000);

v_Column2 VARCHAR2(1000);

v_Into VARCHAR2(2000);

v_Into2 VARCHAR2(3000);

v_Sql_Row VARCHAR2(2000);

TYPE Type_Column IS TABLE OF VARCHAR2(3000) INDEX BY BINARY_INTEGER;

Tab_Col_Name Type_Column;

TYPE Type_Rowval IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;

Tab_Rowval Type_Rowval;

Iv_Tablename VARCHAR2(30) := 'BMDOC'; --查询记录的表

Iv_Condition VARCHAR2(300) := ' BMID = ''DBM00000001'' '; ---查询记录的条件

BEGIN

v_Sql := ' INSERT ALL ';

SELECT MAX(Sys_Connect_By_Path(Column_Name, ', '))

INTO v_Column

FROM (SELECT a.Column_Name, a.Table_Name, Rownum AS Rowno

FROM User_Tab_Columns a

WHERE Table_Name = Iv_Tablename

ORDER BY a.Column_Id)

START WITH Rowno = 1

CONNECT BY Rowno = Rownum;

v_Column := Substr(v_Column, 2, Length(v_Column) - 1) || ' ';

v_Into := ' INTO ' || Iv_Tablename || '(' || v_Column || ') VALUES( ';

SELECT MAX(Sys_Connect_By_Path(Column_Name, '||'''''',''''''||'))

INTO v_Column2

FROM (SELECT a.Column_Name ,

a.Table_Name,

Rownum AS Rowno

FROM User_Tab_Columns a

WHERE Table_Name =Iv_Tablename

ORDER BY a.Column_Id)

START WITH Rowno = 1

CONNECT BY Rowno = Rownum;

v_Column2 := ''''''''|| Substr(v_Column2, 9, Length(v_Column2) - 8) || '||''''''''';

v_Sql_Row := 'select ' || v_Column2 || ' FROM ' || Iv_Tablename ||

' where ' || Iv_Condition;

EXECUTE IMMEDIATE v_Sql_Row BULK COLLECT

INTO Tab_Rowval;

FOR i IN 1 .. Tab_Rowval.Count LOOP

v_Into2 := v_Into2 || v_Into || Tab_Rowval(i) || ')';

-- v_Into3 := v_Into3 || v_Into2;

END LOOP;

v_Sql :=v_Sql || v_Into2 || ' SELECT * FROM DUAL ';

Dbms_Output.Put_Line(v_Sql);

END;

ITPub博客

生成插入语句

最新文章