做数据表比较的存储过程

来源：互联网发布：查看域名注册信息编辑：程序博客网时间：2024/04/27 16:47

内容未经测试，只是一种思路。

CREATE OR REPLACE PROCEDURE test
(
    pid1 IN varchar2(32),
    pid2 IN varchar2(32),
    start_date1 IN varchar2(8),
    start_date2 IN varchar2(8),
    end_date1 IN varchar2(8),
    end_date2 IN varchar2(8)

) IS
sql_A varchar2(200);
sql_B varchar2(200);
tablename varchar2(20);

BEGIN
sql_A:='';
sql_B:='';
/*查找数据表(一个月内)*/
/*如果是涉及到两个月的数据，还未作处理*/
tablename:='analog_data_'+substr(start_date1,1,6);
/*查询语句*/
sql_A:='select * into #T1 from '+tablename+
     'where pid like '+pid1+
     ' and dtag<=floor(to_date('+end_date1+
     ','''yyyymmdd''')-to_date('''20000101''','''yyyymmdd''')) '+
     ' and dtag>=floor(to_date('+start_date1+
     ','''yyyymmdd''')-to_date('''20000101''','''yyyymmdd'''))';
sql_B:='select * into #T2 from '+tablename+
     'where pid like '+pid2+
     ' and dtag<=floor(to_date('+end_date2+
     ','''yyyymmdd''')-to_date('''20000101''','''yyyymmdd''')) '+
     ' and dtag>=floor(to_date('+start_date2+
     ','''yyyymmdd''')-to_date('''20000101''','''yyyymmdd'''))';
/*创建临时表#T1.#T2*/
execute(sql_A);
execute(sql_B);

--记录数量
select count(*) into v_count_A from #T1;
select count(*) into v_count_B from #T2;

DECLARE
R_emp_A EMP%ROWTYPE;
R_emp_B EMP%ROWTYPE;
v_pid CHAR(32);
v_dtag NUMBER(5);
v_ttag NUMBER(10);
v_dqf NUMBER(10);
v_pval NUMBER(16,5);
v_count_A NUMER(5); /*A记录数*/
v_count_B NUMBER(5);/*B记录数*/
v_count_temp NUMBER(5);/*临时*/

--游标
CURSOR C_TEMP_A IS select * from #T1; /*声明游标*/
CURSOR C_TEMP_B IS select * from #T2;

BEGIN
if v_count_A<v_count_B /*A为主*/
Begin
    OPEN C_TEMP_A;
    OPEN C_TEMP_B;
    LOOP
      FETCH C_TEMP_B INTO R_emp_B;
      LOOP
         FETCH C_TEMP_A INTO R_emp_A;
           if (C_TEMP_B.v_dtag=C_TEMP_A.v_ttag and C_TEMP_B.v_ttag=C_TEMP_A.v_ttag)
           then
           --analog_data_ana为新建的表，字段同数据表
             insert into analog_data_ana(pid,dtag,ttag,dqf,pval) value(C_TEMP_A.v_pid,C_TEMP_A.v_dtag,C_TEMP_A.v_ttag,C_TEMP_A.v_dqf,C_TEMP_A.v_pval);
             insert into analog_data_ana(pid,dtag,ttag,dqf,pval) value(C_TEMP_B.v_pid,C_TEMP_B.v_dtag,C_TEMP_B.v_ttag,C_TEMP_B.v_dqf,C_TEMP_B.v_pval);
           end if;
      EXIT WHEN C_TEMP_A%NOTFOUND;
      END LOOP;
    EXIT WHEN C_TEMP_B%NOTFOUND;
    END LOOP;
    CLOSE C_TEMP_A;
    CLOSE C_TEMP_B;
END;

if v_count_A>v_count_B   /*B为主*/
Begin
    OPEN C_TEMP_A;
    OPEN C_TEMP_B;
    LOOP
      FETCH C_TEMP_A INTO R_emp_A;
      LOOP
         FETCH C_TEMP_B INTO R_emp_B;
           if C_TEMP_B.v_dtag=C_TEMP_A.v_ttag and C_TEMP_B.v_ttag=C_TEMP_A.v_ttag;
           then
             insert into analog_data_ana(pid,dtag,ttag,dqf,pval) value(C_TEMP_A.v_pid,C_TEMP_A.v_dtag,C_TEMP_A.v_ttag,C_TEMP_A.v_dqf,C_TEMP_A.v_pval);
             insert into analog_data_ana(pid,dtag,ttag,dqf,pval) value(C_TEMP_B.v_pid,C_TEMP_B.v_dtag,C_TEMP_B.v_ttag,C_TEMP_B.v_dqf,C_TEMP_B.v_pval);
           end if;
      EXIT WHEN C_TEMP_B%NOTFOUND;
      END LOOP;
    EXIT WHEN C_TEMP_A%NOTFOUND;
    END LOOP;
    CLOSE C_TEMP_B;
    CLOSE C_TEMP_A;
END;

End;

Drop Table #T1；/*删除临时表*/
Drop Table #T2；

Truncate analog_data_ana;/*清空临时表*/

END test