利用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    --用于查看是否执行成功

最后编辑于：2017.12.07 03:28:15

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,602评论 6赞 481
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,442评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 152,878评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,306评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,330评论 5赞 373
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,071评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,382评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,006评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,512评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,965评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,094评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,732评论 4赞 323
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,283评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,286评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,512评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,536评论 2赞 354
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,828评论 2赞 345

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

1.工作思路

2.工作难点

推荐阅读更多精彩内容