请教涉及到两个表比较差异的存储过程怎么写？

请教这样的存储过程怎么写？

两个表，表结构一样
BH,LX,DW,QFRQ(类型为date类型)
例如
TABLE_SJYA
0001  sl , 320010  2013/3/10 17:28:51
0002  sl , 320010  2013/3/10 17:28:51
0005  sl , 320010  2013/3/10 7:28:51

TABLE_SJYB
0001  sl , 320010  2013/3/10 17:28:51
0002  sl , 320010  2013/3/10 7:28:51
0006  sl , 320010  2013/3/10 17:28:51

根据传入的参数lx，dw，qfrq （格式是varchar2（6）YYYYMM,比如201303）为条件
分别找到A表和B表的数据，然后比较差异，把不同的数据放到C表
C表结构为
BH,LX,DW,QFRQ （格式也是YYYYMM）

比如上述例子中传入的参数为sl，320010,201303 ，则会把0005,0006 放到C表里，结果为
0005  sl , 320010  201303
0006  sl , 320010  201303

10 个解决方案

#1

不明白楼主说的意思，我没看到A表跟B表数据的区别

#2

TO
xin_1747
A表比B表多个0005,
B表比A表多个0006,

#3

那就是说其实是根据你传的值进来，然后过滤A表跟B表，得到的结果集进行比较BH字段是否一致。不一致存如C表是么

#4

to xin_1747
是的~

#5

with a as (
select '0001' sl, '320010', '201303'
  from dual
union all
select '0002' sl, '320010', '201303'
  from dual
union all
select '0005' sl, '320010', '201303' from dual),
b as (select '0001' sl, '320010', '201303'
  from dual
union all
select '0002' sl, '320010', '201303'
  from dual
union all
select '0006' sl, '320010', '201303' from dual)

(select * from a minus select * from b)
union
(select * from b minus select * from a)

#6

没看清楚，是要存储过程，楼主试一下：
create or replace procedure SP_TABLE_SJYC(lx   in varchar2,
                                          dw   in varchar2,
                                          qfrq in varchar2) IS
  allsql varchar2(4000);
begin
  execute immediate 'drop table TABLE_SJYC';
  execute immediate 'create table TABLE_SJYC as
                     select * from ((select * from TABLE_SJYA minus select * from TABLE_SJYB)
                     union
                    (select * from TABLE_SJYB minus select * from TABLE_SJYA))
where lx=''' || lx || ''' and dw=''' || dw ||
                    ''' and qfrq=''' || qfrq || '''';
END SP_TABLE_SJYC;

#7

求差异，那minus不就可以了~~

#8

哈哈，itpub给你答复过了、、

insert into TABLE_c
  SELECT BH, LX, DW, to_char(t.qfrq, 'yyyymm')
    FROM (select *
            from TABLE_SJYA
          UNION
          select * from TABLE_SJYb) t
   where t.lx = 'sl'
     and t.dw = '320010'
     and to_char(t.qfrq, 'yyyymm') = '201303'
  MINUS
  SELECT BH, LX, DW, qfrq
    FROM (select BH, LX, DW, to_char(t.qfrq, 'yyyymm') qfrq
            from TABLE_SJYA t
          INTERSECT
          select BH, LX, DW, to_char(t.qfrq, 'yyyymm') from TABLE_SJYB t) t
   where t.lx = 'sl'
     and t.dw = '320010'
     and qfrq = '201303'

#9

to u010412956 及其他各位
感谢你们的帮助~

#10

insert into c
(
select BH,LX,DW,to_char(QFRQ,'yyyymm') from a
minus
select BH,LX,DW,to_char(QFRQ,'yyyymm') from b
)
union
(
select BH,LX,DW,to_char(QFRQ,'yyyymm') from b
minus
select BH,LX,DW,to_char(QFRQ,'yyyymm') from a)
;

#1