sguf冲销脚本的实现
1.该脚本为PCISS项目的sguf冲销脚本：
1 DECLARE 2 type typ_sguf_table is table of sguf_rowid_tab_1%rowtype ; 3 sguf_tab typ_sguf_table; 4 v_insert_sql cLOB; 5 BEGIN 6 SELECT * BULK COLLECT INTO sguf_tab 7 FROM sguf_rowid_tab_1 s 8 WHERE s.ID=1053235 AND s.NO='BSD20170901000054' ; 9 IF sguf_tab.last >1 THEN 10 RETURN ; 11 ELSE 12 FOR i IN sguf_tab.first .. sguf_tab.last LOOP 13 14 15 v_insert_sql :=' 16 INSERT INTO sguf_rowid_tab_1 17 VALUES ('|| 18 sguf_tab(i).ID ||' , '|| 19 chr(39)||sguf_tab(i).PRODUCTCODE||chr(39) ||' , '|| 20 chr(39)||sguf_tab(i).NO||chr(39) ||' , '|| 21 chr(39)||sguf_tab(i).SUBNO||chr(39) ||' , '|| 22 'to_date('||CHR(39)||sguf_tab(i).ACCOUNTDATE||CHR(39)||')' ||' , '|| 23 'to_date('||CHR(39)||sguf_tab(i).DEALDATE||CHR(39)||')' ||' , '|| 24 'to_date('||CHR(39)||sguf_tab(i).STOPDATE||CHR(39)||')' ||' , '|| 25 chr(39)||sguf_tab(i).SIDE||chr(39) ||' , '|| 26 sguf_tab(i).MEMBERID ||' , '|| 27 chr(39)||sguf_tab(i).MEMBERFULLNAME||chr(39) ||' , '|| 28 chr(39)||sguf_tab(i).MEMBERABBRNAME||chr(39) ||' , '|| 29 chr(39)||sguf_tab(i).MEMBERCODE||chr(39) ||' , '|| 30 chr(39)||sguf_tab(i).MEMBERPARTCODE||chr(39) ||' , '|| 31 chr(39)||sguf_tab(i).MEMBERTYPE||chr(39) ||' , '|| 32 chr(39)||sguf_tab(i).DEALCURRENCY||chr(39) ||' , '|| 33 chr(39)||sguf_tab(i).DEALOUTCURRENCY||chr(39) ||' , '|| 34 CASE WHEN sguf_tab(i).EXCHANGERATE IS NULL THEN 'NULL' ELSE sguf_tab(i).EXCHANGERATE END ||' , '|| 35 CASE WHEN sguf_tab(i).DEALPRICE<>0 THEN '-'||sguf_tab(i).DEALPRICE ELSE '0' END ||' , '|| 36 CASE WHEN sguf_tab(i).DEALPRICECNY<>0 THEN '-'||sguf_tab(i).DEALPRICECNY ELSE '0' END ||' , '|| 37 CASE WHEN sguf_tab(i).DEALPRICEUSD <>0 THEN '-'||sguf_tab(i).DEALPRICEUSD ELSE '0' END ||' , '|| 38 CASE WHEN sguf_tab(i).MEMBERFEEPRICE <>0 THEN '-'||sguf_tab(i).MEMBERFEEPRICE ELSE '0' END ||' , '|| 39 CASE WHEN sguf_tab(i).MEMBERFEEPRICECNY <>0 THEN '-'||sguf_tab(i).MEMBERFEEPRICEcny ELSE '0' END ||' , '|| 40 CASE WHEN sguf_tab(i).MEMBERFEEPRICEUSD <>0 THEN '-'||sguf_tab(i).MEMBERFEEPRICEusd ELSE '0' END ||' , '|| 41 chr(39)||sguf_tab(i).DEPARTMENT||chr(39) ||' , '|| 42 CASE WHEN sguf_tab(i).PTCOUNT IS NULL THEN 'NULL' ELSE sguf_tab(i).PTCOUNT||'' END ||' , '|| 43 'to_date('||CHR(39)||sguf_tab(i).STARTDATE||CHR(39)||')' ||' , '|| 44 'to_date('||CHR(39)||sguf_tab(i).MATURITYDATE||CHR(39)||')' ||' , '|| 45 CASE WHEN sguf_tab(i).RATE IS NULL THEN 'NULL' ELSE sguf_tab(i).RATE||'' END ||' , '|| 46 CASE WHEN sguf_tab(i).SELFBROKERID IS NULL THEN 'NULL' ELSE sguf_tab(i).SELFBROKERID||'' END ||' , '|| 47 chr(39)||sguf_tab(i).SELFBROKERCODE||chr(39) ||' , '|| 48 chr(39)||sguf_tab(i).SELFBROKERNAME||chr(39) ||' , '|| 49 CASE WHEN sguf_tab(i).FORMCREATORID IS NULL THEN 'NULL' ELSE sguf_tab(i).FORMCREATORID||'' END ||' , '|| 50 chr(39)||sguf_tab(i).FORMCREATORNAME||chr(39) ||' , '|| 51 CASE WHEN sguf_tab(i).FORMCREATORTIME IS NULL THEN 'NULL' ELSE chr(39)||sguf_tab(i).FORMCREATORTIME||chr(39) END ||' , '|| 52 CASE WHEN sguf_tab(i).ACCOUNTDAYS IS NULL THEN 'NULL' ELSE sguf_tab(i).ACCOUNTDAYS||'' END ||' , '|| 53 CASE WHEN sguf_tab(i).BROKERID1 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID1||'' END ||' , '|| 54 chr(39)||sguf_tab(i).BROKERCODE1||chr(39) ||' , '|| 55 chr(39)||sguf_tab(i).BROKERNAME1||chr(39) ||' , '|| 56 CASE WHEN sguf_tab(i).PARTYMONEY1 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY1=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY1 END ||' , '|| 57 CASE WHEN sguf_tab(i).PARTYMONEYCNY1 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY1=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY1 END ||' , '|| 58 CASE WHEN sguf_tab(i).PARTYMONEYUSD1 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD1=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD1 END ||' , '|| 59 CASE WHEN sguf_tab(i).PERCENT1 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT1||'' END ||' , '|| 60 CASE WHEN sguf_tab(i).BROKERID2 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID2||'' END ||' , '|| 61 chr(39)||sguf_tab(i).BROKERCODE2||chr(39) ||' , '|| 62 chr(39)||sguf_tab(i).BROKERNAME2||chr(39) ||' , '|| 63 CASE WHEN sguf_tab(i).PARTYMONEY2 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY2=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY2 END ||' , '|| 64 CASE WHEN sguf_tab(i).PARTYMONEYCNY2 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY2=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYcny2 END ||' , '|| 65 CASE WHEN sguf_tab(i).PARTYMONEYUSD2 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD2=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYusd2 END ||' , '|| 66 CASE WHEN sguf_tab(i).PERCENT2 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT2||'' END ||' , '|| 67 CASE WHEN sguf_tab(i).BROKERID3 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID3||'' END ||' , '|| 68 chr(39)||sguf_tab(i).BROKERCODE3||chr(39) ||' , '|| 69 chr(39)||sguf_tab(i).BROKERNAME3||chr(39) ||' , '|| 70 CASE WHEN sguf_tab(i).PARTYMONEY3 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY3=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY3 end ||' , '|| 71 CASE WHEN sguf_tab(i).PARTYMONEYCNY3 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY3=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYcny3 end ||' , '|| 72 CASE WHEN sguf_tab(i).PARTYMONEYUSD3 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD3=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYusd3 end ||' , '|| 73 CASE WHEN sguf_tab(i).PERCENT3 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT3||'' END ||' , '|| 74 CASE WHEN sguf_tab(i).BROKERID4 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID4||'' END ||' , '|| 75 chr(39)||sguf_tab(i).BROKERCODE4||chr(39) ||' , '|| 76 chr(39)||sguf_tab(i).BROKERNAME4||chr(39) ||' , '|| 77 CASE WHEN sguf_tab(i).PARTYMONEY4 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY4=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY4 end ||' , '|| 78 CASE WHEN sguf_tab(i).PARTYMONEYCNY4 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY4=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYcny4 end ||' , '|| 79 CASE WHEN sguf_tab(i).PARTYMONEYUSD4 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD4=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD4 END ||' , '|| 80 CASE WHEN sguf_tab(i).PERCENT4 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT4||'' END ||' , '|| 81 CASE WHEN sguf_tab(i).BROKERID5 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID5||'' END ||' , '|| 82 chr(39)||sguf_tab(i).BROKERCODE5||chr(39) ||' , '|| 83 chr(39)||sguf_tab(i).BROKERNAME5||chr(39) ||' , '|| 84 CASE WHEN sguf_tab(i).PARTYMONEY5 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY5=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY5 END ||' , '|| 85 CASE WHEN sguf_tab(i).PARTYMONEYCNY5 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY5=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY5 END ||' , '|| 86 CASE WHEN sguf_tab(i).PARTYMONEYUSD5 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD5=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD5 END ||' , '|| 87 CASE WHEN sguf_tab(i).PERCENT5 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT5||'55---' END ||' , '|| 88 CASE WHEN sguf_tab(i).BROKERID6 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID6||'66--' END ||' , '|| 89 chr(39)||sguf_tab(i).BROKERCODE6||chr(39) ||' , '|| 90 chr(39)||sguf_tab(i).BROKERNAME6||chr(39) ||' , '|| 91 CASE WHEN sguf_tab(i).PARTYMONEY6 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY6=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY6 END ||' , '|| 92 CASE WHEN sguf_tab(i).PARTYMONEYCNY6 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY6=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY6 END ||' , '|| 93 CASE WHEN sguf_tab(i).PARTYMONEYUSD6 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD6=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD6 END ||' , '|| 94 CASE WHEN sguf_tab(i).PERCENT6 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT6||'' END ||' , '|| 95 CASE WHEN sguf_tab(i).BROKERID7 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID7||'' END ||' , '|| 96 chr(39)||sguf_tab(i).BROKERCODE7||chr(39) ||' , '|| 97 chr(39)||sguf_tab(i).BROKERNAME7||chr(39) ||' , '|| 98 CASE WHEN sguf_tab(i).PARTYMONEY7 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY7=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY7 END ||' , '|| 99 CASE WHEN sguf_tab(i).PARTYMONEYCNY7 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY7=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY7 END ||' , '|| 100 CASE WHEN sguf_tab(i).PARTYMONEYUSD7 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD7=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD7 END ||' , '|| 101 CASE WHEN sguf_tab(i).PERCENT7 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT7||'' END ||' , '|| 102 CASE WHEN sguf_tab(i).BROKERID8 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID8||'' END ||' , '|| 103 chr(39)||sguf_tab(i).BROKERCODE8||chr(39) ||' , '|| 104 chr(39)||sguf_tab(i).BROKERNAME8||chr(39) ||' , '|| 105 CASE WHEN sguf_tab(i).PARTYMONEY8 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY8=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY8 END ||' , '|| 106 CASE WHEN sguf_tab(i).PARTYMONEYCNY8 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY8=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY8 END ||' , '|| 107 CASE WHEN sguf_tab(i).PARTYMONEYUSD8 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD8=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD8 END ||' , '|| 108 CASE WHEN sguf_tab(i).PERCENT8 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT8||'' END ||' , '|| 109 CASE WHEN sguf_tab(i).BROKERID9 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID9||'' END ||' , '|| 110 chr(39)||sguf_tab(i).BROKERCODE9||chr(39) ||' , '|| 111 chr(39)||sguf_tab(i).BROKERNAME9||chr(39) ||' , '|| 112 CASE WHEN sguf_tab(i).PARTYMONEY9 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY9=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY9 END ||' , '|| 113 CASE WHEN sguf_tab(i).PARTYMONEYCNY9 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY9=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY9 END ||' , '|| 114 CASE WHEN sguf_tab(i).PARTYMONEYUSD9 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD9=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD9 END ||' , '|| 115 CASE WHEN sguf_tab(i).PERCENT9 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT9||'' END ||' , '|| 116 CASE WHEN sguf_tab(i).BROKERID10 IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERID10||'' END ||' , '|| 117 chr(39)||sguf_tab(i).BROKERCODE10||chr(39) ||' , '|| 118 chr(39)||sguf_tab(i).BROKERNAME10||chr(39) ||' , '|| 119 CASE WHEN sguf_tab(i).PARTYMONEY10 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEY10=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEY10 END ||' , '|| 120 CASE WHEN sguf_tab(i).PARTYMONEYCNY10 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNY10=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNY10 END ||' , '|| 121 CASE WHEN sguf_tab(i).PARTYMONEYUSD10 IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSD10=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSD10 END ||' , '|| 122 CASE WHEN sguf_tab(i).PERCENT10 IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENT10||'' END ||' , '|| 123 CASE WHEN sguf_tab(i).BROKERIDX IS NULL THEN 'NULL' ELSE sguf_tab(i).BROKERIDX||'' END ||' , '|| 124 chr(39)||sguf_tab(i).BROKERCODEX||chr(39) ||' , '|| 125 chr(39)||sguf_tab(i).BROKERNAMEX||chr(39) ||' , '|| 126 CASE WHEN sguf_tab(i).PARTYMONEYX IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYX=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYX END ||' , '|| 127 CASE WHEN sguf_tab(i).PARTYMONEYCNYX IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYCNYX=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYCNYX END ||' , '|| 128 CASE WHEN sguf_tab(i).PARTYMONEYUSDX IS NULL THEN 'NULL' WHEN sguf_tab(i).PARTYMONEYUSDX=0 THEN '0' ELSE '-'||sguf_tab(i).PARTYMONEYUSDX END ||' , '|| 129 CASE WHEN sguf_tab(i).PERCENTX IS NULL THEN 'NULL' ELSE sguf_tab(i).PERCENTX||'' END ||' , '|| 130 'NULL' ||' , '|| 131 'NULL' ||' , '|| 132 chr(39)||sguf_tab(i).REM||chr(39) ||' , '|| 133 chr(39)||sguf_tab(i).BONDGP||chr(39) ||' , '|| 134 chr(39)||sguf_tab(i).TAXRATE||chr(39) ||' , '|| 135 chr(39)||sguf_tab(i).TAXWAY||chr(39) ||' , '|| 136 CASE WHEN sguf_tab(i).TAXPRICE = 0 THEN '0' ELSE '-'||sguf_tab(i).TAXPRICE END ||' , '|| 137 CASE WHEN sguf_tab(i).TAXPRICECNY =0 THEN '0' ELSE '-'||sguf_tab(i).TAXPRICECNY END ||' , '|| 138 CASE WHEN sguf_tab(i).TAXPRICEUSD =0 THEN '0' ELSE '-'||sguf_tab(i).TAXPRICEUSD END ||' , '|| 139 CASE WHEN sguf_tab(i).COMPANYFEEPRICE =0 THEN '0' ELSE '-'||sguf_tab(i).COMPANYFEEPRICE END ||' , '|| 140 CASE WHEN sguf_tab(i).COMPANYFEEPRICECNY =0 THEN '0' ELSE '-'||sguf_tab(i).COMPANYFEEPRICECNY END ||' , '|| 141 CASE WHEN sguf_tab(i).COMPANYFEEPRICEUSD =0 THEN '0' ELSE '-'||sguf_tab(i).COMPANYFEEPRICEUSD END 142 ||') ;' ; 143 144 145 146 END LOOP; 147 END IF ; 148 INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE(NO,Strsql) VALUES ('BSD20170908000830',v_insert_sql); 149 --dbms_output.put_line(v_insert_sql); 150 END ;
2.该脚本为sguf与mian表的数据核对脚本：
1 SELECT 2 sguf.sgufmaxid, 3 MAINtab.no, 4 MAINtab.ma_memberid, 5 MAINtab.BSFLAG, 6 MAINtab.maFEEPRICE, 7 sguf.sguffeeprice, 8 (MAINtab.maFEEPRICE-abs(sguf.sguffeeprice)) AS balanceFreePrice, 9 MAINtab.maFEEPRICEcny, 10 sguf.sguffeepricecny, 11 (MAINtab.maFEEPRICEcny-abs(sguf.sguffeepricecny)) AS balanceFreePricecny, 12 MAINtab.maFEEPRICEusd, 13 sguf.sguffeepriceusd, 14 (MAINtab.maFEEPRICEusd-abs(sguf.sguffeepriceusd)) AS balanceFreePricecusd 15 FROM ( 16 SELECT M.NO, 'B' AS BSFLAG, m.buyerid AS ma_memberid , 17 M.BUYERFEEPRICE maFEEPRICE ,M.BUYERFEEPRICEcny maFEEPRICEcny ,M.BUYERFEEPRICEusd maFEEPRICEusd 18 19 FROM HIS_PRODUCT_DEAL_MAIN M 20 WHERE M.STATE = 9 21 -- AND M.BUYERID = 1216 22 -- AND M.PRODUCTID = 4 23 AND TO_CHAR(M.CREATETIME, 'yyyymmdd') BETWEEN '20171001' AND '20171031' 24 UNION ALL 25 SELECT M.NO, 'L' AS BSFLAG,m.sellerid AS ma_memberid, 26 M.sellerFEEPRICE maFEEPRICE ,M.sellerFEEPRICEcny maFEEPRICEcny ,M.sellerFEEPRICEusd maFEEPRICEusd 27 FROM HIS_PRODUCT_DEAL_MAIN M 28 WHERE M.STATE = 9 29 -- AND M.SELLERID = 1216 30 -- AND M.PRODUCTID = 4 31 AND TO_CHAR(M.CREATETIME, 'yyyymmdd') BETWEEN '20171001' AND '20171031' ) MAINtab , 32 ( 33 SELECT MAX(ID) AS sgufmaxid, s.no ,s.side,s.memberid,COUNT(s.no) nocount, 34 SUM(s.memberfeeprice) sguffeeprice, 35 SUM(s.memberfeepricecny) sguffeepricecny, 36 SUM(s.memberfeepriceusd) sguffeepriceusd 37 FROM SGUF_PRODUCT_DEAL_MAIN S 38 /* WHERE S.MEMBERID = 1216 AND to_char(s.dealdate,'yyyymmdd') BETWEEN '20171001' AND '20171031'*/ 39 GROUP BY s.no,s.side ,s.memberid ) sguf 40 WHERE MAINtab.no=sguf.no(+) AND 41 MAINtab.bsflag=sguf.side(+) AND 42 MAINtab.ma_memberid=sguf.memberid(+) AND ( 43 (MAINtab.maFEEPRICE-abs(sguf.sguffeeprice))<>0 OR 44 (MAINtab.maFEEPRICEcny-abs(sguf.sguffeepricecny))<>0 OR 45 (MAINtab.maFEEPRICEusd-abs(sguf.sguffeepriceusd))<>0 )
3.该脚本为修改sguf表中分摊错误的数据
1 DECLARE 2 max_indexvalue INT ; 3 TYPE typ_rowid_table IS TABLE OF VARCHAR2(100) ; 4 TYPE TYP_percent_TABLE IS TABLE OF NUMBER ; 5 TYPE TYP_IndexValue_TABLE IS TABLE OF integer ; 6 TYPE typ_percent_recode IS record ( 7 v_srNO Varchar2(100), 8 v_memberfeeprice number , 9 v_memberfeepricecny number , 10 v_memberfeepriceusd number , 11 v_sum_freeprice number , 12 v_sum_freepricecny number , 13 v_sum_freepriceusd number , 14 v_percent1 number , 15 v_percent2 number , 16 v_percent3 number , 17 v_percent4 number , 18 v_percent5 number , 19 v_percent6 number , 20 v_percent7 number , 21 v_percent8 number , 22 v_percent9 number , 23 v_percent10 number 24 ); 25 CURSOR sguf_cur IS SELECT sr.row_id FROM sguf_rowid_tab_1 sr /*WHERE sr.row_id='AAA+awAAFAABHs3AAC'*/ ; 26 --v_rowid VARCHAR2(100) ; 27 calc_freeprice_sum NUMBER; 28 calc_freepricecny_sum NUMBER; 29 calc_freepriceusd_sum NUMBER; 30 tab_rowid typ_rowid_table ; 31 REC_PR typ_percent_recode ; 32 TAB_INDEX TYP_IndexValue_TABLE := TYP_IndexValue_TABLE(NULL); 33 TAB_PT TYP_percent_TABLE := TYP_percent_TABLE(NULL); 34 v_balance NUMBER ; 35 v_balancecny NUMBER ; 36 v_balanceusd NUMBER ; 37 str_Fentan_sql VARCHAR2(1000); 38 Str_sql_Update VARCHAR2(1000); 39 all_sql VARCHAR2(4000); 40 41 42 BEGIN 43 44 OPEN sguf_cur ; 45 LOOP 46 FETCH sguf_cur BULK COLLECT INTO tab_rowid ; 47 EXIT WHEN sguf_cur%NOTFOUND ; 48 END LOOP ; 49 50 FOR x IN tab_rowid.first .. tab_rowid.last LOOP 51 str_Fentan_sql :=''; 52 Str_sql_Update := ''; 53 max_indexvalue :=0 ; 54 all_sql :=''; 55 v_balance :=0 ; 56 v_balancecny :=0 ; 57 v_balanceusd :=0 ; 58 calc_freeprice_sum :=0 ; 59 calc_freepricecny_sum :=0 ; 60 calc_freepriceusd_sum :=0 ; 61 SELECT S.NO, 62 S.MEMBERFEEPRICE, 63 S.MEMBERFEEPRICECNY, 64 S.MEMBERFEEPRICEUSD, 65 ABS(NVL(S.PARTYMONEY1, 0) + NVL(S.PARTYMONEY2, 0) + 66 NVL(S.PARTYMONEY3, 0) + NVL(S.PARTYMONEY4, 0) + 67 NVL(S.PARTYMONEY5, 0) + NVL(S.PARTYMONEY6, 0) + 68 NVL(S.PARTYMONEY7, 0) + NVL(S.PARTYMONEY8, 0) + 69 NVL(S.PARTYMONEY9, 0) + NVL(S.PARTYMONEY10, 0)) AS SUM_FREEPRICE, 70 ABS(NVL(S.PARTYMONEYCNY1, 0) + NVL(S.PARTYMONEYCNY2, 0) + 71 NVL(S.PARTYMONEYCNY3, 0) + NVL(S.PARTYMONEYCNY4, 0) + 72 NVL(S.PARTYMONEYCNY5, 0) + NVL(S.PARTYMONEYCNY6, 0) + 73 NVL(S.PARTYMONEYCNY7, 0) + NVL(S.PARTYMONEYCNY8, 0) + 74 NVL(S.PARTYMONEYCNY9, 0) + NVL(S.PARTYMONEYCNY10, 0)) AS SUM_FREEPRICECNY, 75 ABS(NVL(S.PARTYMONEYUSD1, 0) + NVL(S.PARTYMONEYUSD2, 0) + 76 NVL(S.PARTYMONEYUSD3, 0) + NVL(S.PARTYMONEYUSD4, 0) + 77 NVL(S.PARTYMONEYUSD5, 0) + NVL(S.PARTYMONEYUSD6, 0) + 78 NVL(S.PARTYMONEYUSD7, 0) + NVL(S.PARTYMONEYUSD8, 0) + 79 NVL(S.PARTYMONEYUSD9, 0) + NVL(S.PARTYMONEYUSD10, 0)) AS SUM_FREEPRICEUSD, 80 S.PERCENT1, 81 S.PERCENT2, 82 S.PERCENT3, 83 S.PERCENT4, 84 S.PERCENT5, 85 S.PERCENT6, 86 S.PERCENT7, 87 S.PERCENT8, 88 S.PERCENT9, 89 S.PERCENT10 90 INTO REC_PR 91 FROM SGUF_ROWID_TAB_1 S 92 WHERE s.row_id =tab_rowid(x); 93 --dbms_output.put_line(tab_rowid(x)); 94 95 ------得到总共拆分了几个经纪人，每个经纪人的拆分比例是多少------------- 96 TAB_PT.delete ; 97 TAB_PT.extend(10); 98 TAB_PT(1) := REC_PR.v_percent1; 99 TAB_PT(2) := REC_PR.v_percent2; 100 TAB_PT(3) := REC_PR.v_percent3; 101 TAB_PT(4) := REC_PR.v_percent4; 102 TAB_PT(5) := REC_PR.v_percent5; 103 TAB_PT(6) := REC_PR.v_percent6; 104 TAB_PT(7) := REC_PR.v_percent7; 105 TAB_PT(8) := REC_PR.v_percent8; 106 TAB_PT(9) := REC_PR.v_percent9; 107 TAB_PT(10) := REC_PR.v_percent10; 108 TAB_INDEX.delete ; 109 110 FOR I IN TAB_PT.first .. TAB_PT.last LOOP 111 112 IF TAB_PT(I) IS NOT NULL THEN 113 TAB_INDEX.extend(1); 114 TAB_INDEX(TAB_INDEX.last) :=i ; 115 calc_freeprice_sum :=calc_freeprice_sum+ round(abs(REC_PR.v_memberfeeprice) * TAB_PT(i)/100 ,2) ; 116 calc_freepricecny_sum := calc_freepricecny_sum+ round(abs(REC_PR.v_memberfeepricecny) * TAB_PT(i)/100 ,2); 117 calc_freepriceusd_sum := calc_freepriceusd_sum+ round(abs(REC_PR.v_memberfeepriceusd) * TAB_PT(i)/100 ,2); 118 END IF ; 119 END LOOP ; 120 dbms_output.put_line('calc_freeprice_sum='||calc_freeprice_sum); 121 dbms_output.put_line('calc_freepricecny_sum='||calc_freepricecny_sum); 122 dbms_output.put_line('calc_freepriceusd_sum='||calc_freepriceusd_sum); 123 dbms_output.put_line('---------------------------------------------------------------------------'); 124 v_balance := abs(REC_PR.v_memberfeeprice) - calc_freeprice_sum ; 125 v_balancecny := abs(REC_PR.v_memberfeepricecny) - calc_freepricecny_sum ; 126 v_balanceusd := abs(REC_PR.v_memberfeepriceusd) - calc_freepriceusd_sum ; 127 --max_indexvalue 拆了几个经纪人，最大经纪人编号； 128 max_indexvalue :=TAB_INDEX.last ; 129 IF REC_PR.v_sum_freeprice<>ABS(REC_PR.v_memberfeeprice) THEN 130 FOR i IN TAB_INDEX.first .. TAB_INDEX.last LOOP 131 Str_sql_Update :='UPDATE sguf_rowid_tab_1 s1 '|| 132 'SET s1.partymoney'||TAB_INDEX(i)||'='||ROUND(REC_PR.v_memberfeeprice *TAB_PT(TAB_INDEX(i))/100,2)|| 133 ' WHERE s1.row_id ='||CHR(39)||tab_rowid(x)||CHR(39); 134 --EXECUTE IMMEDIATE Str_sql_Update ; 135 all_sql :=all_sql||Str_sql_Update||' ; '||CHR(10); 136 --dbms_output.put_line('all_sql='||all_sql ); 137 END LOOP ; 138 --dbms_output.put_line('all_sql='||all_sql ); 139 INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE SU 140 VALUES 141 (TAB_ROWID(X), 142 REC_PR.V_SRNO, 143 ALL_SQL, 144 V_BALANCE, 145 REC_PR.V_MEMBERFEEPRICE, 146 calc_freeprice_sum, 147 REC_PR.V_MEMBERFEEPRICEcny, 148 calc_freepricecny_sum, 149 REC_PR.V_MEMBERFEEPRICEusd, 150 calc_freepriceusd_sum 151 ); 152 all_sql :=' '; 153 END IF ; 154 155 IF REC_PR.v_sum_freepricecny<>ABS(REC_PR.v_memberfeepricecny) THEN 156 FOR i IN TAB_INDEX.first .. TAB_INDEX.last LOOP 157 Str_sql_Update :=' 158 UPDATE sguf_rowid_tab_1 s1 set '|| 159 's1.partymoneycny'||TAB_INDEX(i)||'='||round(REC_PR.v_memberfeepricecny *TAB_PT(TAB_INDEX(i))/100,2)|| 160 ' WHERE s1.row_id ='||CHR(39)||tab_rowid(x)||CHR(39); 161 --EXECUTE IMMEDIATE Str_sql_Update ; 162 all_sql :=all_sql||Str_sql_Update||' ; '||CHR(10); 163 164 END LOOP ; 165 INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE SU 166 VALUES 167 (TAB_ROWID(X), 168 REC_PR.V_SRNO, 169 ALL_SQL, 170 V_BALANCE, 171 REC_PR.V_MEMBERFEEPRICE, 172 calc_freeprice_sum, 173 REC_PR.V_MEMBERFEEPRICEcny, 174 calc_freepricecny_sum, 175 REC_PR.V_MEMBERFEEPRICEusd, 176 calc_freepriceusd_sum 177 ); 178 all_sql :=' '; 179 180 END IF ; 181 182 IF REC_PR.v_sum_freepriceusd<>ABS(REC_PR.v_memberfeepriceusd) THEN 183 184 FOR i IN TAB_INDEX.first .. TAB_INDEX.last LOOP 185 Str_sql_Update :=' 186 UPDATE sguf_rowid_tab_1 s1 set '|| 187 's1.partymoneyusd'||TAB_INDEX(i)||'='||round(REC_PR.v_memberfeepriceusd *TAB_PT(TAB_INDEX(i))/100,2)|| 188 ' WHERE s1.row_id ='||CHR(39)||tab_rowid(x)||CHR(39); 189 --EXECUTE IMMEDIATE Str_sql_Update ; 190 all_sql :=all_sql||Str_sql_Update||' ; '||CHR(10); 191 --dbms_output.put_line('all_sql='||all_sql ); 192 END LOOP ; 193 INSERT INTO SGUF_SUM_UNEQUAL_MEMBERFREE SU 194 VALUES 195 (TAB_ROWID(X), 196 REC_PR.V_SRNO, 197 ALL_SQL, 198 V_BALANCE, 199 REC_PR.V_MEMBERFEEPRICE, 200 calc_freeprice_sum, 201 REC_PR.V_MEMBERFEEPRICEcny, 202 calc_freepricecny_sum, 203 REC_PR.V_MEMBERFEEPRICEusd, 204 calc_freepriceusd_sum 205 );all_sql :=' '; 206 END IF ; 207 COMMIT ; 208 END LOOP ; 209 END ;
