利用PL/SQL開發(fā)基金持倉表

今天是2017.5.8r。

晚上快七點的時候下樓去對面卓越時代廣場吃家樂緣，從去年到現(xiàn)在，在sz實習的期間吃飯的地點來來去去也就是在這附近了，除此基本別無其他。每次我自己一個人吃，習慣了也不會覺得別扭，只是偶爾會意識到很久沒有和別人講過話了。吃完往回走，等紅綠燈的時候以為來實習已經(jīng)快一個月了，發(fā)現(xiàn)只是錯覺而已。剛剛六點多終于把這幾天的基金持倉表存儲過程開發(fā)好了，可能因此有了些許輕松感，而不再覺得時間難熬。

說實話，自己真的很抵觸陌生的環(huán)境陌生的人，覺得在自己的圈子里就挺好的，想一直呆在里面不出來，可是經(jīng)過了那么多的事情，自己也清楚知道哪有那么好的事，這世界上除非你不工作呆家里不出來，否則你總會要和形形色色的人打交道，還要隨時生硬的保持一副笑臉，不然就會讓別人覺得自己是多么的不合群，可是只有自己知道，在自己的內(nèi)心世界里，你們才是不合群的人。

好了，可能只是純粹的想說點什么而已。接下來幾天還要有幾件事要做：
1.把老師布置的小論文改好；
2.周五中午請假回去弄新的三方，然后找淘寶辦理六月初的簽證，順便把宿舍的鞋什么整理一下；
3.這個月底就是正式答辯和宇北鼻的生日了；
4.在想租房子住的事。

總結一下自己上周和這周的工作， 主要涉及將舊估值系統(tǒng)和新估值系統(tǒng)中的數(shù)據(jù)整合到一起，自己負責基金持倉數(shù)據(jù)表的整理，先說一下自己的邏輯思路以及遇到的難點，最后貼上代碼。

1.工作思路

首先創(chuàng)建目標表，然后將gz25中的估值數(shù)據(jù)利用存儲過程對應插入到創(chuàng)建的表中，最后再在SQL窗口中調(diào)用即可。

2.工作難點

（1）前面5天的時間基本全部花在對gz25系統(tǒng)中的數(shù)據(jù)對照目標表字段進行整理了，因為gz25中是分套帳（即一個產(chǎn)品有一個相應的表）的，每個表名和里面的基金產(chǎn)品code是一一對應的，所以首先將每個表以及對應產(chǎn)品id找出來，以便后面進行一一遍歷，后面在存儲過程中是利用游標循環(huán)來實現(xiàn)遍歷的。

（2）使用游標循環(huán)時必然會用到loop.....end loop，在這之間還要用begin...end。

（3）定義每次循環(huán)時執(zhí)行的sql語句是變化的，因為一次循環(huán)就要遍歷一個不同的表，所以定義了v_sql。此時要注意的是v_sql的賦值用‘ ’括起來，而在‘ ’里面如果又用到了‘ ’字符串，則需要用兩個單引號‘‘，其中一個單引號表示轉(zhuǎn)義。

（4）pl/sql中寫的alter ,insert 等語句均需要添加一個commit;只有這樣提交后對應表中的數(shù)據(jù)才會發(fā)生變化。

（5）還有一個問題是創(chuàng)建TEMPORARY TABLE ，后跟的ON COMMIT PRESERVE（delete） ROWS;必須要加上，同時注意創(chuàng)建這樣的臨時表時，若在一個sql窗口中進行了插入值操作，如果沒有commit提交，那么在另一個sql會話窗口中是不能查詢到表中的值，所以在進行insert、update等操作時一定要提交。

（6）篩選大于某一日期的數(shù)據(jù)： 字段名> to_date('20161202','yyyymmdd')

（7）建立DBlink跨庫連接:(參考文章 http://blog.csdn.net/gavinloo/article/details/6459619)

（8）關于使用動態(tài)sql的問題，若是表名是變量，則需要使用動態(tài)sql，否則不會被解析，但是若只是條件里是變量，那么直接用變量就可以了，然后靜態(tài)執(zhí)行。

（9）如果更新的字段加了索引，更新時會重建索引，更新效率會慢。

create index index3 on res.temp_gz2and4fundportfolio(port_code,hlddate);
drop index index2



---------------在res中創(chuàng)建一張臨時表后，再運行下面的程序-------------------
drop table tem_gz4_fund_portfolio
CREATE GLOBAL TEMPORARY TABLE  tem_gz4_fund_portfolio
    (
       PORT_CODE varchar2(20) not null ,                  --產(chǎn)品ID,   主鍵
       HLDDATE DATE not null ,                            --持倉日期
       SEC_CODE varchar2(50),                             --證券代碼  （由證券編碼和證券市場編碼組成）
       SEC_NAME varchar2(50),                             --證券名稱
       SEC_MKT_CODE varchar2(10),                         --證券市場編碼
       MKT_CODE varchar2(10),                                 --交易市場
       MKT_NAME varchar2(50),                              --交易市場名稱 
       SEC_TYPE varchar2(20),                             --證券類別代碼
       SEC_VAR_NAME varchar2(50),                         --證券類別名稱
       CURY_CODE varchar2(3),                             --幣種代碼
       IVT_CLSS varchar2(2),                              --投資分類編碼
       HLD_ATTR varchar2(20),                             --持有屬性
       TD_ATTR varchar2(2),                               --交易屬性編碼
       ML_ATTR varchar2(20),                              --受限流通類別
       HLDAMT number(18,4),                               --持倉數(shù)量
       HLDCST number(18,4),                               --原幣持倉成本
       HLDCST_LOCL number(18,4),                          --本幣持倉成本
       HLDMKV number(18,4),                               --原幣持倉市值
       HLDMKV_LOCL number(18,4),                          --本幣持倉市值
       HLDVVA number(18,4),                               --原幣證券估值
       HLDVVA_L number(18,4),                             --本幣證券估值
       VALPRICE number(18,6),                             --證券估值行情
       VALRATE number(18,15),                             --貨幣估值匯率
       SOURCE1 varchar2(4)                                 --數(shù)據(jù)來源
       --primary key (PORT_CODE) 
    )  ON COMMIT PRESERVE ROWS;
    -- Add comments to the table 
    comment on table tem_gz4_fund_portfolio
      is 'gz2.5和gz4.5整合后的持倉表';

---------------------------遍歷每個套賬號名稱----------------------------
create or replace procedure sp_gz4fundportfolio
as

    v_table_name varchar2(50);                        --2.5的源表名
    v_port_code varchar2(20);                         --基金產(chǎn)品代碼
    v_sql varchar2(32767);
    

    cursor cur_setcode is
    SELECT distinct lpad(t.FSETCODE,3,'0') as FSETCODE
       ,t.FSETID
    FROM sgt25.lsetlist@dblink_gz2 t;


begin
    for cur_id in cur_setcode
    loop
    begin
        v_table_name:='sgt25.'||'a'||cur_id.FSETCODE||'jjhzgzb'||'@dblink_gz2';  --取對應的表id
        v_port_code:=cur_id.FSETID;    --取對應的產(chǎn)品id

        v_sql:='
        insert into res.tem_gz4_fund_portfolio
        SELECT '||v_port_code||',
          B.Fdate,  
       
          CASE 
          WHEN substr(B.FKmbm,0,8) in (''11010101'',''11010103'',''11010105'',''11010107'',''11010201'',''11010501'',''11010601'') THEN substr(B.FKmbm,-6)||'' SH'' 
          WHEN substr(B.FKmbm,0,8) in (''11010102'',''11010104'',''11010106'',''11010108'',''11010202'',''11010205'',''11010502'',''11010602'') THEN substr(B.FKmbm,-6)||'' SZ''  
          WHEN substr(B.FKmbm,0,8) in (''11010109'',''11010110'',''11010111'',''11010503'') THEN substr(B.FKmbm,-6)||'' HK''  
          WHEN substr(B.FKmbm,0,8) in (''11010203'',''11010204'',''11010603'',''11010604'') THEN substr(B.FKmbm,-6)||'' CY'' 
          ------------基金----------
          WHEN substr(B.FKmbm,0,8)=''11010301'' THEN substr(B.FKmbm,-6)||'' TA''  --中國結算TA系統(tǒng)
          ------------理財----------
          WHEN substr(B.FKmbm,0,8)in (''11011801'',''11018801'',''11018802'') THEN substr(B.FKmbm,-6)||'' OTC''   
          -----------股指期貨-------
          WHEN substr(B.FKmbm,0,8) in (''31020101'',''31020104'',''31020107'',''31020110'',''31020113'',''31020116'') THEN substr(B.FKmbm,-6)||'' CFX''  
          WHEN length(B.FKmbm)=8 THEN ''000000'' --代表現(xiàn)金存款的證券代碼
          END,      
          
          B.FKMMC,
          
          CASE 
          when length(B.FKmbm)=8 THEN ''000000'' --代表現(xiàn)金的證券市場代碼
          ELSE substr(B.FKmbm,-6) END,
         
          CASE 
          WHEN substr(B.FKmbm,0,8) in (''11010101'',''11010103'',''11010105'',''11010107'',''11010201'',''11010501'',''11010601'') THEN ''XSHG'' --上海證券交易所
          WHEN substr(B.FKmbm,0,8) in (''11010102'',''11010104'',''11010106'',''11010108'',''11010202'',''11010205'',''11010502'',''11010602'') THEN ''XSHE''  --深圳證券交易所
          WHEN substr(B.FKmbm,0,8) in (''11010109'',''11010110'',''11010111'',''11010503'') THEN ''HKCG''  --港股通聯(lián)合市場
          WHEN substr(B.FKmbm,0,8) in (''11010203'',''11010204'',''11010603'',''11010604'') THEN ''XCFE'' --中國銀行間交易市場
          ------------基金----------
          WHEN substr(B.FKmbm,0,8)=''11010301'' THEN ''CSDC''  --中國結算TA系統(tǒng)
          ------------理財----------
          WHEN substr(B.FKmbm,0,8)in (''11011801'',''11018801'',''11018802'') THEN ''COTC''   --中國柜臺交易市場
          -----------股指期貨-------
          WHEN substr(B.FKmbm,0,8) in (''31020101'',''31020104'',''31020107'',''31020110'',''31020113'',''31020116'') THEN ''CCFX''  --中國金融期貨交易市場
          when length(B.FKmbm)=8 THEN ''other'' 
          END,
          
          CASE  
          WHEN substr(B.FKmbm,0,8) in (''11010101'',''11010103'',''11010105'',''11010107'',''11010201'',''11010501'',''11010601'') THEN ''上海證券交易所'' 
          WHEN substr(B.FKmbm,0,8) in (''11010102'',''11010104'',''11010106'',''11010108'',''11010202'',''11010205'',''11010502'',''11010602'') THEN ''深圳證券交易所''  
          WHEN substr(B.FKmbm,0,8) in (''11010109'',''11010110'',''11010111'',''11010503'') THEN ''港股通聯(lián)合市場''  
          WHEN substr(B.FKmbm,0,8) in (''11010203'',''11010204'',''11010603'',''11010604'') THEN ''中國銀行間交易市場'' 
          ------------基金----------
          WHEN substr(B.FKmbm,0,8)=''11010301'' THEN ''中國結算TA系統(tǒng)''  
          ------------理財----------
          WHEN substr(B.FKmbm,0,8)in (''11011801'',''11018801'',''11018802'') THEN ''中國柜臺交易市場''   
          -----------股指期貨-------
          WHEN substr(B.FKmbm,0,8) in (''31020101'',''31020104'',''31020107'',''31020110'',''31020113'',''31020116'') THEN ''中國金融期貨交易市場''  
          when length(B.FKmbm)=8 THEN ''其他交易場所'' 
          END,
        ------------------------------------------------------------------------------------
           
          CASE 
          WHEN substr(B.FKmbm,0,6)=''110101'' and substr(B.FKmbm,-6) like ''300___'' THEN ''GP_GP_CYB''  --股票品種_股票_創(chuàng)業(yè)板
          WHEN substr(B.FKmbm,0,6)=''110101'' and substr(B.FKmbm,-6) like ''60____'' or substr(B.FKmbm,-6) like ''900___'' or substr(B.FKmbm,-6) like ''00____'' or substr(B.FKmbm,-6) like ''200___'' THEN ''GP_GP''  --股票品種_股票
          WHEN substr(B.FKmbm,0,6)=''110102'' THEN ''ZQ''  --債券品種
          WHEN substr(B.FKmbm,0,6)=''110103'' THEN ''JJ''  --基金品種
          WHEN substr(B.FKmbm,0,6)=''110115'' THEN ''QQ''  --期權品種
          WHEN substr(B.FKmbm,0,6) = ''110116'' THEN ''HG''  --回購品種
          WHEN substr(B.FKmbm,0,6) =''110118'' THEN ''LC_YHLC''  --理財品種_銀行理財
          WHEN substr(B.FKmbm,0,6) =''110188'' THEN ''LC_XT''  --理財品種_信托
          WHEN substr(B.FKmbm,0,8) in (''31020101'',''31020104'',''31020107'',''31020110'',''31020113'',''31020116'') THEN ''QH_GZ''  --期貨品種_股指
          WHEN substr(B.FKmbm,0,8) like ''10020___'' THEN ''CK_DQ''  --存放品種_定期
          END,
          
          CASE 
          WHEN substr(B.FKmbm,0,6)=''110101'' and substr(B.FKmbm,-6) like ''300___'' THEN ''股票品種_股票_創(chuàng)業(yè)板''  
          WHEN substr(B.FKmbm,0,6)=''110101'' and substr(B.FKmbm,-6) like ''60____'' or substr(B.FKmbm,-6) like ''900___'' or substr(B.FKmbm,-6) like ''00____'' or substr(B.FKmbm,-6) like ''200___'' THEN ''股票品種_股票''  
          WHEN substr(B.FKmbm,0,6)=''110102'' THEN ''債券品種''  
          WHEN substr(B.FKmbm,0,6)=''110103'' THEN ''基金品種''  
          WHEN substr(B.FKmbm,0,6)=''110115'' THEN ''期權品種''  
          WHEN substr(B.FKmbm,0,6) = ''110116'' THEN ''回購品種''  
          WHEN substr(B.FKmbm,0,6) =''110118'' THEN ''理財品種_銀行理財''  
          WHEN substr(B.FKmbm,0,6) =''110188'' THEN ''理財品種_信托''  
          WHEN substr(B.FKmbm,0,8) in (''31020101'',''31020104'',''31020107'',''31020110'',''31020113'',''31020116'') THEN ''期貨品種_股指''  
          WHEN substr(B.FKmbm,0,8) like ''10020___'' THEN ''存放品種_定期''  
          END,
          
          ''CNY'',
          '''',
          '''',
          '''',
          '''',
          
          B.FZqsl,
          B.FZqcb,
          B.FZqcb*1 ,
          B.FZqsz ,
          B.FZqsz*1 ,
          
          '''' ,    --原幣證券估值
          '''' ,    --本幣證券估值
          
          CASE FZqsl
          WHEN 0 THEN 0
          ELSE FZqsz/FZqsl  
          END ,
          
          1 ,
          ''GZ25''  
          
          FROM  ( 
          select * from '||v_table_name||' b
          where B.FKmbm like ''11010____%'' or B.FKmbm like ''11011____%'' or B.FKmbm like ''11018____%'' or B.FKmbm like ''31020____%'' or B.FKmbm like ''10020___''
          )B
          order by fdate desc
         '; 
        execute immediate v_sql;      

        commit;
        EXCEPTION WHEN NO_DATA_FOUND THEN
            continue;
    end;
    end loop;


end sp_gz4fundportfolio;

-----------------------------創(chuàng)建好存儲過程后對存儲過程進行調(diào)用--------------
call sp_gz4fundportfolio();
select count(1) from res.tem_gz4_fund_portfolio    --用于查看是否執(zhí)行成功
最后編輯于 ：
?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容