利用PL/SQL开发基金持仓表

今天是2017.5.8r。

晚上快七点的时候下楼去对面卓越时代广场吃家乐缘,从去年到现在,在sz实习的期间吃饭的地点来来去去也就是在这附近了,除此基本别无其他。每次我自己一个人吃,习惯了也不会觉得别扭,只是偶尔会意识到很久没有和别人讲过话了。吃完往回走,等红绿灯的时候以为来实习已经快一个月了,发现只是错觉而已。刚刚六点多终于把这几天的基金持仓表存储过程开发好了,可能因此有了些许轻松感,而不再觉得时间难熬。

说实话,自己真的很抵触陌生的环境陌生的人,觉得在自己的圈子里就挺好的,想一直呆在里面不出来,可是经过了那么多的事情,自己也清楚知道哪有那么好的事,这世界上除非你不工作呆家里不出来,否则你总会要和形形色色的人打交道,还要随时生硬的保持一副笑脸,不然就会让别人觉得自己是多么的不合群,可是只有自己知道,在自己的内心世界里,你们才是不合群的人。

好了,可能只是纯粹的想说点什么而已。接下来几天还要有几件事要做:
1.把老师布置的小论文改好;
2.周五中午请假回去弄新的三方,然后找淘宝办理六月初的签证,顺便把宿舍的鞋什么整理一下;
3.这个月底就是正式答辩和宇北鼻的生日了;
4.在想租房子住的事。


总结一下自己上周和这周的工作, 主要涉及将旧估值系统和新估值系统中的数据整合到一起,自己负责基金持仓数据表的整理,先说一下自己的逻辑思路以及遇到的难点,最后贴上代码。

1.工作思路

首先创建目标表,然后将gz25中的估值数据利用存储过程对应插入到创建的表中,最后再在SQL窗口中调用即可。

2.工作难点

(1)前面5天的时间基本全部花在对gz25系统中的数据对照目标表字段进行整理了,因为gz25中是分套帐(即一个产品有一个相应的表)的,每个表名和里面的基金产品code是一一对应的,所以首先将每个表以及对应产品id找出来,以便后面进行一一遍历,后面在存储过程中是利用游标循环来实现遍历的。

(2)使用游标循环时必然会用到loop.....end loop,在这之间还要用begin...end。

(3)定义每次循环时执行的sql语句是变化的,因为一次循环就要遍历一个不同的表,所以定义了v_sql。此时要注意的是v_sql的赋值用‘ ’括起来,而在‘ ’里面如果又用到了‘ ’字符串,则需要用两个单引号‘‘,其中一个单引号表示转义。

(4)pl/sql中写的alter ,insert 等语句均需要添加一个commit;只有这样提交后对应表中的数据才会发生变化。

(5)还有一个问题是创建TEMPORARY TABLE ,后跟的ON COMMIT PRESERVE(delete) ROWS;必须要加上,同时注意创建这样的临时表时,若在一个sql窗口中进行了插入值操作,如果没有commit提交,那么在另一个sql会话窗口中是不能查询到表中的值,所以在进行insert、update等操作时一定要提交。

(6)筛选大于某一日期的数据: 字段名> to_date('20161202','yyyymmdd')

(7)建立DBlink跨库连接:(参考文章 http://blog.csdn.net/gavinloo/article/details/6459619)

(8)关于使用动态sql的问题,若是表名是变量,则需要使用动态sql,否则不会被解析,但是若只是条件里是变量,那么直接用变量就可以了,然后静态执行。

(9)如果更新的字段加了索引,更新时会重建索引,更新效率会慢。

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



---------------在res中创建一张临时表后,再运行下面的程序-------------------
drop table tem_gz4_fund_portfolio
CREATE GLOBAL TEMPORARY TABLE  tem_gz4_fund_portfolio
    (
       PORT_CODE varchar2(20) not null ,                  --产品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),                               --持仓数量
       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)                                 --数据来源
       --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);                         --基金产品代码
    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;    --取对应的产品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系统
          ------------理财----------
          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'' --代表现金存款的证券代码
          END,      
          
          B.FKMMC,
          
          CASE 
          when length(B.FKmbm)=8 THEN ''000000'' --代表现金的证券市场代码
          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''  --港股通联合市场
          WHEN substr(B.FKmbm,0,8) in (''11010203'',''11010204'',''11010603'',''11010604'') THEN ''XCFE'' --中国银行间交易市场
          ------------基金----------
          WHEN substr(B.FKmbm,0,8)=''11010301'' THEN ''CSDC''  --中国结算TA系统
          ------------理财----------
          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 ''港股通联合市场''  
          WHEN substr(B.FKmbm,0,8) in (''11010203'',''11010204'',''11010603'',''11010604'') THEN ''中国银行间交易市场'' 
          ------------基金----------
          WHEN substr(B.FKmbm,0,8)=''11010301'' THEN ''中国结算TA系统''  
          ------------理财----------
          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''  --股票品种_股票_创业板
          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 ''股票品种_股票_创业板''  
          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;

-----------------------------创建好存储过程后对存储过程进行调用--------------
call sp_gz4fundportfolio();
select count(1) from res.tem_gz4_fund_portfolio    --用于查看是否执行成功

推荐阅读更多精彩内容