将两个以上表的不同数据插入一个新表的思路

将两个以上表的不同数据插入一个新表的思路

一、方法一：

0、先将需要被关联的记录插入新表：

insert into test_table(test_col1, test_col2, ...) select s_col1,s_col2 from source_table1;

1、procedure

set serveroutput on;
/
create or replace procedure test_proce is

    type cursor_type is ref cursor;
    v_deal_sql varchar2(1000);
    num0 number := 1;
    str varchar2(50);
    c cursor_type;
begin
    
    v_deal_sql := 'select 1111111 from dual';
    open c for v_deal_sql;-- open c for v_deal_sql using xxx1,xxx2 when use column_name1:=?,column_name2:=?
    loop
        fetch c into str;
        exit when num0 > 100;--exit when xxx_cur%notfound
        update test_table set test_col=str where test_col2 is null and id=num0;
        dbms_output.put_line(num0);
        num0 := num0 + 1;
    end loop;
    close c;
    commit;
end test_proce;
/

2、declare

set serveroutput on;
/
declare 
	cur is select 11111 from dual;
	num0 number := 1;
    str varchar2(50);
begin
	open cur;
	loop
		fetch cur into str;
		exit when num0 > 100;--exit when xxx_cur%notfound
		update xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
		dbms_output.put_line(num0);
		num0 := num0 + 1;
	end loop;
	close cur;
end;
/

ps： 在loop中打印可能会出现ORA-20000:ORU-10027:buffer overflow,limit of 10000 bytes错误，解决方法简单粗暴就是不要打印，或者只在测试环境调试的时候打印，或者加大缓冲区set serveroutput on size 999999

二、方法二：

0、使用with直接insert

insert into test_table(test_col1, test_col1, ...) select source_col1, source_col2, ... from (
with
    t1 as (select base_columns from source_table1),
	t2 as (select some_columns from source_table1),
    t3 as (select some_columns from source_table2),
    t4 as (anything you want to select),
    ...

    select t1.*, t2.*, t3.*, t4.*, ... from t1 
    	left join t2 on t1.pivot_col1=t2.pivot_col1 and ...
    	left join t3 on t1.pivot_col1=t2.pivot_col1 and ...
    	...
)

1、WITH AS的含义

    WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。 

生产环境实测with as 比存储过程（可能写得比较烂）快很多，使用with as 可以大大提高效率。