TXT

PL-SQL3

By Danny Marshall,2014-11-20 11:41
6 views 0
PL-SQL3

create or replace procedure unite.prc_report_ne_if_add

    (p_gentime varchar2, --?É??Ê??ä

     p_table_name varchar2, --?íÃû

     P_Vendor_name varchar2, --??ÉÌÃû

     p_ne_name varchar2 , --ÍøÔªÃû?Æ

     p_ok out varchar2 --Íê?ÉÇé?ö )as

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

    *********************

    --author: Ô?Ã?

    --create date :2001.12.28

    --chinese name:ÅÐ?Ï???íÍøÔª???ãÊÇ?ñ?É?? --Description :ÓÃÓÚÈÕ??B,C1,C2?í?É?? --update date:

    --memo:

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

    **********************

    v_gentime date;

    TYPE t_cursor IS REF CURSOR;

    V_Cursor_tab t_cursor;

    V_Cursor_column t_cursor;

    V_Cursor_SELECT t_cursor;

    v_table_name varchar2(50);

    v_Vendor_name varchar2(32);

    v_column_name varchar2(50);

    v_sql1 varchar2(200);

    v_sql_columns varchar2(4000);

    v_sql_columns2 varchar2(4000);

    v_sql_update0 varchar2(4000);

    v_sql_update1 varchar2(4000);

    v_sql_update2 varchar2(4000);

    v_sql_INSERT varchar2(4000);

    v_sql_delete varchar2(4000);

    v_sql_SELECT varchar2(4000);

    V_A NUMBER(10);

    begin

    v_gentime:=to_date(p_gentime,'YYYY-MM-DD HH24:MI:SS');

    V_TABLE_NAME:=UPPER(TRIM(P_TABLE_NAME)); V_A:=0;

    OPEN v_cursor_tab FOR

     select Vendor_name,table_name from DICT.UD_rep_TABLES where

    upper(table_name)=upper(TRIM(p_table_name)) AND upper(Vendor_name)=upper(TRIM(p_Vendor_name)); loop

     fetch v_cursor_tab into v_Vendor_name,v_table_name;

     -- dbms_output.put_line(v_table_name );

     -- dbms_output.put_line(v_Vendor_name);

     exit when v_cursor_tab%NOTFOUND;

     V_SQL_COLUMNS:=' AND (';

     V_SQL_COLUMNS2:='';

     v_sql1:='select column_name from dict.ud_rep_columns where table_name=:v_table_name AND Vendor_name=:v_Vendor_name AND COLUMN_flag=''1''';

     OPEN v_cursor_column FOR v_sql1 USING v_table_name,v_Vendor_name;

     loop

     fetch v_cursor_column into v_column_name;

     exit when v_cursor_column%NOTFOUND;

     IF V_COLUMN_NAME !='GENTIME' AND V_COLUMN_NAME!='NE_NUMBER' THEN

     V_SQL_COLUMNS:=V_SQL_COLUMNS||' '||v_column_name||' IS NOT NULL OR ';

     V_SQL_COLUMNS2:=V_SQL_COLUMNS2||' AND '||v_column_name||' IS NOT NULL';

     END IF;

     END LOOP;

    V_SQL_COLUMNS:=SUBSTR(V_SQL_COLUMNS,1,LENGTH(V_SQL_COLUMNS)-3)||')';

     CLOSE v_cursor_column;

     v_sql_update0:='UPDATE '||v_table_name||' SET NE_NUMBER=0 WHERE GENTIME=:V_gentime AND Vendor_name=:V_Vendor_name AND NE_NAME=:P_NE_NAME ';

     -- dbms_output.put_line(substr(v_sql_update0,1,254));

     EXECUTE IMMEDIATE v_sql_update0 USING

    V_gentime,V_Vendor_name,P_NE_NAME;

     v_sql_update2:='UPDATE '||v_table_name||' SET NE_NUMBER=2 WHERE GENTIME=:V_gentime AND Vendor_name=:V_Vendor_name AND NE_NAME=:P_NE_NAME '||V_SQL_COLUMNS;

     -- dbms_output.put_line(substr(v_sql_update2,1,254));

     -- dbms_output.put_line(substr(v_sql_update2,255,254));

     -- dbms_output.put_line(substr(v_sql_update2,509,254));

     EXECUTE IMMEDIATE v_sql_update2 USING

    V_gentime,V_Vendor_name,P_NE_NAME;

     v_sql_update1:='UPDATE '||v_table_name||' SET NE_NUMBER=1 WHERE GENTIME=:V_gentime AND Vendor_name=:V_Vendor_name AND NE_NAME=:P_NE_NAME '||V_SQL_COLUMNS2;

     -- dbms_output.put_line(substr(v_sql_update1,1,254));

     -- dbms_output.put_line(substr(v_sql_update1,255,254));

     -- dbms_output.put_line(substr(v_sql_update1,509,254));

     EXECUTE IMMEDIATE v_sql_update1 USING

    V_gentime,V_Vendor_name,P_NE_NAME;

     v_sql_SELECT:='select COUNT(*) ALL_NUMBER from '||v_table_name||' WHERE GENTIME=:V_gentime AND

    Vendor_name=:V_Vendor_name AND NE_NAME=:P_NE_NAME AND NE_NUMBER=1' ;

     OPEN V_Cursor_SELECT FOR v_sql_SELECT USING V_gentime,V_Vendor_name,P_NE_NAME;

     loop

     fetch V_Cursor_SELECT into V_A;

     -- dbms_output.put_line(v_table_name );

     exit when V_Cursor_SELECT%NOTFOUND;

     -- dbms_output.put_line(P_NE_NAME);

     -- dbms_output.put_line(V_A );

     IF V_A=1 THEN

     v_sql_INSERT:='INSERT INTO

    UNITE.ul_report_lose_ne_add_log(TABLE_NAME,NE_NAME,GENTIME,VENDOR_NAME,addtime) SELECT TABLE_NAME,NE_NAME,GENTIME,VENDOR_NAME,sysdate FROM UNITE.ul_report_lose_ne_log WHERE GENTIME=:V_gentime AND Vendor_name='''||V_Vendor_name||''' AND

    TABLE_NAME='''||V_TABLE_NAME||''' AND NE_NAME='''||P_NE_NAME||'''';

     --dbms_output.put_line(substr(v_sql_INSERT,1,254));

     --dbms_output.put_line(substr(v_sql_INSERT,255,254));

     EXECUTE IMMEDIATE v_sql_INSERT USING V_gentime;

     --dbms_output.put_line(substr(v_sql_INSERT,1,254));

     v_sql_delete:='DELETE FROM UNITE.ul_report_lose_ne_log WHERE GENTIME=:V_gentime AND Vendor_name=:V_Vendor_name AND TABLE_NAME=:V_TABLE_NAME AND NE_NAME=:P_NE_NAME';

     --dbms_output.put_line(substr(v_sql_delete,1,254));

     EXECUTE IMMEDIATE v_sql_delete USING

    V_gentime,V_Vendor_name,V_TABLE_NAME,P_NE_NAME;

     NULL;

     END IF;

     END LOOP;

    END LOOP;

    CLOSE V_cursor_tab;

     p_ok:='OK';

    --dbms_output.put_line(p_ok);

    commit;

exception

     when others then

     p_ok:='F';

ROLLBACK;

END;

Report this document

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