TXT

PL-SQL2

By Alice King,2014-11-20 11:44
18 views 0
PL-SQL2

CREATE OR REPLACE PROCEDURE UNITE.prc02_RB_A(

     p_time IN VARCHAR2, -- Ê??äµã

     p_ok OUT VARCHAR2 -- ?ö?Î

     )AS

    --*************************************************************************************

    --author: ÕÔ?ÜÍ?

    --create date :2002-7-9

    --chinese name:ÉÏ??ÈÕ??a?í

    --Description :ÓÃÓÚÈÕ???ã×Ü

    --update date:

    --memo:

    --**************************************************************************************

v_a_gentime DATE;

    v_b_gentime DATE;

    CURSOR c_a IS

     SELECT a.name_desc,a.city_code,a.m101,a.m102,a.m103

     FROM unite.upr02_sms_b_ne_vendor a

     WHERE a.gentime=v_a_gentime;

    CURSOR c_b IS

     SELECT b.name_desc,b.city_code,b.m101,b.m103,b.m104

     FROM unite.upr02_sm_b_city b

     WHERE b.gentime=v_b_gentime;

    v_a_rownum NUMBER;

    v_b_rownum NUMBER;

    v_tmp number;

    v_a_name unite.upr02_sms_b_ne_vendor.name_desc%TYPE; v_a_citycode unite.upr02_sms_b_ne_vendor.city_code%TYPE; v_a_dm001 unite.upr02_sms_b_ne_vendor.m101%TYPE; v_a_dm002 unite.upr02_sms_b_ne_vendor.m102%TYPE; v_a_dm003 unite.upr02_sms_b_ne_vendor.m103%TYPE; v_b_name unite.upr02_sm_b_city.name_desc%TYPE; v_b_citycode unite.upr02_sm_b_city.city_code%TYPE; v_b_dm001 unite.upr02_sm_b_city.m101%TYPE; v_b_dm004 unite.upr02_sm_b_city.m103%TYPE; v_b_dm005 unite.upr02_sm_b_city.m104%TYPE; BEGIN

     v_a_gentime:=to_date(to_char(to_date(p_time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd'),'yyyy-mm-dd');

     v_b_gentime:=to_date(p_time,'yyyy-mm-dd hh24:mi:ss');

     DELETE FROM unite.upr02_rb_a a WHERE a.gentime=v_a_gentime ;

     OPEN c_a;

     LOOP

     FETCH c_a INTO

    v_a_name,v_a_citycode,v_a_dm001,v_a_dm002,v_a_dm003;

     EXIT WHEN c_a%NOTFOUND;

     INSERT INTO

    unite.upr02_rb_a(name_desc,city_code,gentime,dm001,dm002,dm003)

    VALUES(v_a_name,v_a_citycode,v_a_gentime,v_a_dm001,v_a_dm002,v_a_dm003);

     END LOOP;

     CLOSE c_a;

     OPEN c_b;

     LOOP

     FETCH c_b INTO

    v_b_name,v_b_citycode,v_b_dm001,v_b_dm004,v_b_dm005;

     EXIT WHEN c_b%NOTFOUND;

     OPEN c_a;

     LOOP

     FETCH c_a INTO

    v_a_name,v_a_citycode,v_a_dm001,v_a_dm002,v_a_dm003;

     EXIT WHEN c_a%NOTFOUND;

     v_tmp:=0;

     IF v_b_dm001=v_a_dm001 THEN

     UPDATE unite.upr02_rb_a

     SET(dm004,dm005)=(SELECT m103,m104

     FROM unite.upr02_sm_b_city b

     WHERE b.gentime=v_b_gentime AND

     b.m101=v_a_dm001)

     WHERE unite.upr02_rb_a.gentime=v_a_gentime AND unite.upr02_rb_a.dm001=v_b_dm001;

     v_tmp:=1;

     EXIT;

     END IF;

     END LOOP;

     CLOSE c_a;

     IF v_tmp=0 THEN

     INSERT INTO

    unite.upr02_rb_a(name_desc,city_code,gentime,dm001,dm004,dm005)

    VALUES(v_b_name,v_b_citycode,v_b_gentime,v_b_dm001,v_b_dm004,v_b_dm005);

     END IF;

     END LOOP;

     CLOSE c_b;

     p_ok:='T';

     COMMIT;

    EXCEPTION

     WHEN OTHERS THEN p_ok:='F';

     ROLLBACK;

    END;

Report this document

For any questions or suggestions please email
cust-service@docsford.com