精华内容
下载资源
问答
我要提问

  • 存储过程项目实战

     2019-03-13 14:15:00
    数据库脚本（注意if end if 这种格式） create or replace package body pmp_agnt_public_pkg as PROCEDURE fix_contract (p_agreement_no VARCHAR2, -- 入参 p_error OUT VARCHAR2) AS ... 
    数据库脚本（注意if  end if 这种格式）
    

    

    create or replace package body pmp_agnt_public_pkg as

   PROCEDURE fix_contract (p_agreement_no VARCHAR2,   -- 入参
                           p_error OUT VARCHAR2) AS   -- 出参
       v_agreement_id number; -- 声明变量
       .......
       BEGIN;
            SELECT COUNT(*) INTO v_cnt03 FROM TSE_AGREEMENT_INFO_NEW t WHERE t.bussiness_type = '422' AND t.agreemen_no = p_agreement_no;
            IF v_cnt03 = 0 THEN 
               p_error := '你所输的不是区域代理合同编号，请确认后再输';   -- 返回值
               RETURN;
            END IF;
            ...........
       END;
   END fix_contract;   

end pmp_agnt_public_pkg;
    

    

     
    

    xml文件：注意jdbcType=VARCHAR，被狠狠坑了一嗲
    

    

    <update id="fixContract" statementType="CALLABLE">
      {call pmp_agnt_public_pkg.fix_contract(#{p_agreement_no,mode=IN,jdbcType=VARCHAR},#{p_error,mode=OUT,jdbcType=VARCHAR})}
</update>
    

    

     
    

    怎么调用xml：入参用map封装，出参用map.get("");
    

    

    public String fixContract(String agreementNo) {
    Map<String, String> map = new HashMap<String, String>();
    map.put("p_agreement_no", agreementNo);    
    dao.update("fixContract", map);
    return map.get("p_error");   
}
    

    

     
    

    END!
    

    转载于:https://www.cnblogs.com/ericguoxiaofeng/p/10522778.html
    收起
    展开全文

  • 实战项目——oracle存储过程

     2021-04-23 09:44:23
    一 oracle存储过程编译 --申明存储过程 IN 入参 OUT出参create or replace procedure vipInfoTest(inPut IN number,outResult OUT number) as--定义属性v_totalCount number ; begin --CURSOR v_totalCount IS -... 
    一 oracle存储过程编译
    


    --申明存储过程 IN 入参  OUT出参
    create or replace procedure vipInfoTest(inPut IN number,outResult OUT number) as
    --定义属性
    v_totalCount number ;
    


    begin
    
      --CURSOR v_totalCount IS
    
  --查询赋值
      SELECT count(1),count(2) into v_totalCount,outResult from brsalpp.T_VIP_INFO091010;
    
      --IF判断  
    
  IF (v_totalCount > inPut)
    
  THEN 
    
      UPDATE 
    
        T_VIP_INFO091010 t 
    
      SET
    
        t.is_valid = 'Y',
    
        t.update_time = sysdate where rownum=1 and t.is_valid='N'; 
    
  ELSE
    
     insert into brsalpp.T_VIP_INFO091010 (C_SLS_CDE,LAST_PAY_TIME,VIP_BEGIN_TIME,VIP_END_TIME,IS_VALID,CREATE_TIME,CREATE_NAME,UPDATE_TIME,UPDATE_NAME,IS_PAY_VIP)
    
     select a.C_SLS_CDE,a.LAST_PAY_TIME,a.VIP_BEGIN_TIME,a.VIP_END_TIME,'Y',sysdate,a.CREATE_NAME,sysdate,a.UPDATE_NAME,a.IS_PAY_VIP from brsalpp.T_VIP_INFO091010 a where rownum=1;
    
  end if;
    --提交事务
    commit;
    --结束存储过程
    end vipInfoTest;
    


    二 PL SQL在sql窗口执行
    


    --定义接收属性
    declare 
    
aaa number; 
    --执行开始
    begin
      -- Call the procedure 带入入参、出参执行上面定义的存储过程
      vipinfotest(9,aaa); 
    
      --打印输出 可在plsql输出窗口查看打印输出的结果
      dbms_output.put_line(aaa); 
    
end;
    收起
    展开全文
    数据库 oracle plsql procedure

  • oraclde存储过程_02—Oracle存储过程学习（实战）

     2021-01-17 01:52:47
    整体分为三篇02—Oracle存储过程学习(实战)——本篇03—Oracle存储过程学习(Mybatis使用)本篇围绕几种模式：无参存储过程、带参存储过程、带参数存储过程含赋值方式、存储过程中游标定义使用、异常使用分别举例使用... 
    最近项目需要使用存储过程统计一些表数据，插入到日志表等，所以对Oracle存储过程进行稍微研究，总结如下。
    整体分为三篇
    02—Oracle存储过程学习(实战)——本篇
    03—Oracle存储过程学习(Mybatis使用)
    本篇围绕几种模式：无参存储过程、带参存储过程、带参数存储过程含赋值方式、存储过程中游标定义使用、异常使用分别举例使用。
    存储过程使用分为三步：编写存储过程->执行编译->调用使用
    一、无参存储过程语法
    (1)编写存储过程
    create or replace procedure print_Time
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE(SYSDATE);
    END print_Time;
    (2)运行编译
    (3)调用使用
    A)SQL窗口执行：
    DECLARE
    BEGIN
    print_Time();
    END;
    或
    B)SQL命令窗口执行：exec print_Time;
    二、带参存储过程实例
    (1)编写存储过程
    create or replace procedure print_Time2(in_callDate in varchar2)
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE(to_date(in_callDate, 'yyyy-MM-dd HH24:mi'));
    --insert Log values(in_callDate);
    END print_Time2;
    (2)调用：
    DECLARE
    p_outval number;
    p_inoutval VARCHAR2(10) := '~Hi~';
    BEGIN
    print_Time2('2018-10-22');
    END;
    三、带参数存储过程含赋值方式
    (1)创建两张表：一张用户表(并插入数据)，一张用于统计用户注册(脚本见附件)
    (2)添加几条数据到用户表
    (3)现在写一个存储过程，完成每天凌晨统计前一天不同城市用户注册数量
    首先按照常规写出查询语句：
    SELECT u.city as cityNme, count(*) as userCount
    FROM z_test_user u
    WHERE u.create_time >= to_date('2018-10-25', 'yyyy-MM-dd HH24:mi')
    AND u.create_time < to_date('2018-10-25', 'yyyy-MM-dd HH24:mi')+1
    GROUP BY u.city;
    按照语法创建存储过程：
    create or replace procedure PROC_STATISTICS_USER(inDate in varchar2)
    IS
    cityName          VARCHAR2(20);
    userCount         NUMBER(10);
    BEGIN
    for cur_row in (
    SELECT u.city as city, count(*)as tatal FROM z_test_user u
    WHERE u.create_time >= to_date(inDate, 'yyyy-MM-dd HH24:mi')
    AND u.create_time < to_date(inDate, 'yyyy-MM-dd HH24:mi')+1
    GROUP BY u.city ) loop
    cityName := cur_row.city;
    userCount:= cur_row.tatal;
    insert into Z_TEST_USER_LOG values (SQL_Z_TEST_USER_LOG.NEXTVAL, cityName, userCount,SYSDATE);
    commit;
    end loop;
    END PROC_STATISTICS_USER;
    说明：
    a)黑色字体为创建存储过程规范格式(如不明天请看第一部分理论学习)。
    b) 红色字体分为三步，第一定义变量包括类型，第二赋值，使用“:=”，第三插入使用变量
    c) BEGIN之后是查询，并进行循环，格式：for 变量名 in() loop ... end loop;
    d)查询语句按照时间条件并按城市名称进行分组查询城市名称，总数，然后赋值给变量cur_row，下文使用cur_row.city、cur_row.tatal进行取值。
    e) insert into... 进行插入。
    (4) 编译
    (5)调用执行
    DECLARE
    BEGIN
    PROC_STATISTICS_USER('2018-10-25');
    END;
     结果如下：
    注：实际存储调用一般放在定时任务调用。
    四、存储过程中游标定义使用
    注：依然使用上面表数据作为演示，完成每天凌晨统计前一天不同城市用户注册数量
    (1)创建存储过程
    create or replace procedure PROC_STATISTICS_USER_CUR(inDate in varchar2)
    IS
    cityName          VARCHAR2(20); --定义变量
    userCount         NUMBER(10);   --定义变量
    -- storeName      VARCHAR2(20); --1定义变量
    Cursor cur_row IS
    SELECT u.city as city, count(*)as tatal FROM z_test_user u
    WHERE u.create_time >= to_date(inDate, 'yyyy-MM-dd HH24:mi')
    AND u.create_time < to_date(inDate, 'yyyy-MM-dd HH24:mi')+1
    GROUP BY u.city;
    BEGIN
    FOR c_row IN cur_row LOOP
    cityName := c_row.city;  --赋值
    userCount:= c_row.tatal; --赋值
    -- 有些时候，有其他参数需要从其他表中查询下面给出一个举例
    -- select storeName
    -- into storeName    --2赋值
    -- from STORE_INFO t
    -- where t.STORE_NAME =c_row.city;
    insert into Z_TEST_USER_LOG values (SQL_Z_TEST_USER_LOG.NEXTVAL, cityName, userCount,SYSDATE);
    --storeName       -- 3 使用
    commit;
    END loop;
    END PROC_STATISTICS_USER_CUR;
    说明：a)首先看一下红色字体，storeName需要从其他表中查询，使用举例
    b) 游标使用格式：在is后Cursor 变量名 IS ... ;
    c) begin中使用：FOR c_row IN cur_row LOOP... END loop;赋值，插入等操作，此时for中in是直接使用游标进行。可以理解cur_row为父亲游标，c_row为子游标，就如数据和数组下标关系。
    (2)编译执行及执行结果如下：
    五、异常使用
    有时候执行存储过程会报错，调用者需要知道执行结果是否报错，此时需要定义传出参数，并在异常块进行赋值。捕获到异常之后:
    1.记录错误相关信息  放入相关日志表  SQLCODE   SQLERRM
    2.如果有事务相关的操作 一般是要rollback
    (1)创建存储过程
    create or replace procedure PROC_STATISTICS_USER_EX(inDate in varchar2, out_code out int, out_msg out varchar2)
    IS
    val int;  --定义一个整数变量
    BEGIN
    out_code := 0;
    out_msg  := 'success';
    val := 0/0;   -- 赋值，使用0做为除数，执行报异常
    EXCEPTION
    when others then
    out_code := '-1';
    out_msg  := '统计发生异常' || substr(sqlerrm, 1, 100);
    dbms_output.put_line(out_code || '::'||out_msg);
    rollback ;
    END PROC_STATISTICS_USER_EX;
    (2)使用：使用命令窗口或者SQL窗口
    a)先执行打开调试：set serverout on;
    b) 再执行：
    DECLARE
    out_code number;
    out_msg VARCHAR2(100);
    BEGIN
    PROC_STATISTICS_USER_EX('2018-10-23', out_code, out_msg);
    dbms_output.put_line(out_code || '::'||out_msg);
    END;
    /
    结果如下：
    SQL窗口：
    留下一个问题：循环里面错误处理(oracle只支持begin...end中放错误捕获)
    附件：
    1，用户表创建及添加数据：
    drop table Z_TEST_USER cascade constraints;
    create table Z_TEST_USER
    (
    ID          NUMBER not null,
    USERNAME    VARCHAR2(20),
    PASSWORD    VARCHAR2(100),
    CITY        VARCHAR2(50),
    CREATE_TIME DATE
    )
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 8K
    minextents 1
    maxextents unlimited
    );
    comment on column Z_TEST_USER.ID
    is '用户ID';
    comment on column Z_TEST_USER.USERNAME
    is '用户姓名(电话)';
    comment on column Z_TEST_USER.PASSWORD
    is '密码';
    comment on column Z_TEST_USER.CITY
    is '城市';
    comment on column Z_TEST_USER.CREATE_TIME
    is '创建日期';
    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
    values (5, 'eee', 'eee', '北京', to_date('25-10-2018 00:02:00', 'dd-mm-yyyy hh24:mi:ss'));
    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
    values (1, 'aaa', 'aaa', '上海', to_date('25-10-2018 02:08:00', 'dd-mm-yyyy hh24:mi:ss'));
    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
    values (2, 'bbb', 'bbb', '上海', to_date('25-10-2018 05:00:00', 'dd-mm-yyyy hh24:mi:ss'));
    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
    values (3, 'ccc', 'ccc', '合肥', to_date('25-10-2018 04:02:01', 'dd-mm-yyyy hh24:mi:ss'));
    insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
    values (4, 'ddd', 'ddd', '合肥', to_date('25-10-2018 09:00:01', 'dd-mm-yyyy hh24:mi:ss'));
    commit;
    2，日志表创建
    drop table Z_TEST_USER_LOG cascade constraints;
    create table Z_TEST_USER_LOG
    (
    ID         NUMBER not null,
    CITY       VARCHAR2(20),
    COUNT      NUMBER,
    COUNT_TIME DATE
    )
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
    initial 64K
    next 8K
    minextents 1
    maxextents unlimited
    );
    comment on column Z_TEST_USER_LOG.ID
    is '用户ID';
    comment on column Z_TEST_USER_LOG.CITY
    is '城市';
    comment on column Z_TEST_USER_LOG.COUNT
    is '注册个数';
    comment on column Z_TEST_USER_LOG.COUNT_TIME
    is '统计时间';
    commit;
    参考资料：
    http://blog.csdn.net/u013057786/article/details/17165623
    https://www.cnblogs.com/liangyihui/p/5886760.html
    https://bbs.csdn.net/topics/391912626?page=1
    同步于微信公众号：ifly_tech
    收起
    展开全文

  • oracle存储过程例子（实战项目）

     2014-09-10 19:08:30
    Sql代码  create or replace function func_get_user_by_msisdn(msisdn in number)  -----------------------------------------------...---功能描述：通过用户的电话号码,调用局方存储过程 获取用户 
    

    Sql代码  
    

    

      

    1. 
create or replace function func_get_user_by_msisdn(msisdn in number)  
    2. 
------------------------------------------------------------------------------  
    3. 
---功能描述：通过用户的电话号码,调用局方存储过程 获取用户的基本信息，      ---  
    4. 
---          以遍更新card_user_info本地数据表。                            ---  
    5. 
---参数：   msisdn in number 代表用户的电话号码（SIM卡）                   ---  
    6. 
---时间：2008-09-05                                                        ---  
    7. 
---作者：zhouyq                                                            ---  
    8. 
---单位：厦门新科技软件股份有限公司                                        ---  
    9. 
------------------------------------------------------------------------------  
    10. 
return pkg_gps_audit.user_record  
    11. 
is  
    12. 
  user_record_info pkg_gps_audit.user_record; --用户基本信息类型  
    13. 
  v_success number := 1;  --成功标志  
    14. 
   
    15. 
begin  
    16. 
  
    17. 
 --首先获取正常用户的基本信息  
    18. 
  begin  
    19. 
       pkg_gps_audit.proc_get_userinfo(msisdn,user_record_info);  
    20. 
       v_success := 1;  
    21. 
  exception  when others then  
    22. 
       v_success := 0;  
    23. 
  end;  
    24. 
  
    25. 
  --其次，如果正常用户获取不到，再查离线用户信息  
    26. 
  if v_success < 1 then  
    27. 
  begin  
    28. 
       pkg_gps_audit.proc_get_cancel_userinfo(msisdn,user_record_info);  
    29. 
       v_success := 1;  
    30. 
  exception when others then  
    31. 
       v_success := 0;  
    32. 
  end;  
    33. 
  end if;  
    34. 
    
    35. 
    
    36. 
  if v_success > 0 then   
    37. 
    return user_record_info;  
    38. 
  else  
    39. 
    raise no_data_found;  
    40. 
  end if;  
    41. 
  
    42. 
  
    43. 
return user_record_info;  
    44. 
end func_get_user_by_msisdn;  
    45. 
  
    46. 
  
    47. 
  
    48. 
  
    49. 
  
    50. 
create or replace function func_get_user_id(msisdn_bak in number)  
    51. 
------------------------------------------------------------------------------  
    52. 
---功能描述：通过用户的电话号码获取用户的ID，(有保证数据及时性)            ---  
    53. 
---参数：   msisdn in number 代表用户的电话号码（SIM卡）                   ---  
    54. 
---时间：2008-09-05                                                        ---  
    55. 
---作者：zhouyq                                                            ---  
    56. 
---单位：厦门新科技软件股份有限公司                                        ---  
    57. 
------------------------------------------------------------------------------  
    58. 
return number  
    59. 
is  
    60. 
user_info pkg_gps_audit.user_record; --用户基本信息  
    61. 
  
    62. 
card_info card_user_info%rowtype; --用户基本信息表结构   
    63. 
type base_cursor is ref cursor;  
    64. 
cr base_cursor;  
    65. 
  
    66. 
user_id number(15); --用户ID；  
    67. 
begin  
    68. 
  begin  
    69. 
    --如果本地card_user_info表可以找到数据，先在本地查找。  
    70. 
    open cr for select * from card_user_info where msisdn = msisdn_bak;  
    71. 
    fetch cr into card_info;  
    72. 
    if cr%found then  
    73. 
        
    74. 
      user_id := card_info.user_id;  
    75. 
    else  
    76. 
      --获取用户基本信息  
    77. 
      user_info := func_get_user_by_msisdn(msisdn_bak);  
    78. 
        
    79. 
      user_id := user_info.user_id;  
    80. 
    end if;  
    81. 
      
    82. 
    close cr;  
    83. 
      
    84. 
    --返回用户ID  
    85. 
     return user_id;  
    86. 
  exception   
    87. 
    when others then    
    88. 
    raise no_data_found;  --抛出异常  
    89. 
  end;   
    90. 
  
    91. 
  
    92. 
  
    93. 
end;  
    94. 
  
    95. 
  
    96. 
  
    97. 
  
    98. 
  
    99. 
create or replace function func_is_first_day  
    100. 
------------------------------------------------------------------------------  
    101. 
---功能描述：判断今天是否是该月的第一天（1号）                             ---  
    102. 
---返回参数：1代表是，0代表不是                                            ---  
    103. 
---时间：2008-09-05                                                        ---  
    104. 
---作者：zhouyq                                                            ---  
    105. 
---单位：厦门新科技软件股份有限公司                                        ---  
    106. 
------------------------------------------------------------------------------  
    107. 
return number   
    108. 
is  
    109. 
  v_result number := 0;  
    110. 
    
    111. 
  v_day varchar2(2);  
    112. 
begin  
    113. 
  select to_char(sysdate,'dd') into v_day from dual;  
    114. 
  if v_day = '01' then  
    115. 
    v_result := 1;  
    116. 
  else   
    117. 
    v_result := 0;  
    118. 
  end if;  
    119. 
    
    120. 
  return(v_result);  
    121. 
end func_is_first_day;  
    122. 
  
    123. 
  
    124. 
  
    125. 
  
    126. 
  
    127. 
create or replace procedure proc_delete_day_fee_info  
    128. 
------------------------------------------------------------------------------  
    129. 
---功能描述： 只保留一个月得数据（8月的日账单10月份删除）                  ---  
    130. 
---时间：2008-09-08                                                        ---  
    131. 
---作者：zhouyq                                                            ---  
    132. 
---单位：厦门新科技软件股份有限公司                                        ---  
    133. 
------------------------------------------------------------------------------  
    134. 
is  
    135. 
  
    136. 
type base_cursor is ref cursor;  
    137. 
cf base_cursor;  
    138. 
v_fee_id number(18);  
    139. 
begin  
    140. 
  begin  
    141. 
    open cf for  select a.fee_id from user_fee_info a ,day_fee_info b where a.fee_id = b.fee_id and to_char(b.start_time,'yyyy-mm') = to_char(add_months(sysdate,0),'yyyy-mm');  
    142. 
    fetch cf into v_fee_id;  
    143. 
    while cf%found loop  
    144. 
      delete from user_fee_info where fee_id = v_fee_id;  
    145. 
      delete from day_fee_info where fee_id = v_fee_id;  
    146. 
      commit;  
    147. 
        
    148. 
      fetch cf into v_fee_id;  
    149. 
    end loop;  
    150. 
      
    151. 
    close cf;  
    152. 
      
    153. 
  exception when others then  
    154. 
    rollback;  
    155. 
    return;  
    156. 
  end;    
    157. 
  
    158. 
  
    159. 
    
    160. 
end proc_delete_day_fee_info;  
    161. 
  
    162. 
  
    163. 
  
    164. 
  
    165. 
create or replace procedure proc_delete_month_fee_info  
    166. 
------------------------------------------------------------------------------  
    167. 
---功能描述： 只保留6个月得数据（8月的日账单1月份删除）                  ---  
    168. 
---时间：2008-09-08                                                        ---  
    169. 
---作者：zhouyq                                                            ---  
    170. 
---单位：厦门新科技软件股份有限公司                                        ---  
    171. 
------------------------------------------------------------------------------  
    172. 
is  
    173. 
  
    174. 
type base_cursor is ref cursor;  
    175. 
cf base_cursor;  
    176. 
v_fee_id number(18);  
    177. 
begin  
    178. 
  begin  
    179. 
    open cf for  select a.fee_id from user_fee_info a ,month_fee_info b where a.fee_id = b.fee_id and to_char(b.start_time,'yyyy-mm') = to_char(add_months(sysdate,0),'yyyy-mm');  
    180. 
    fetch cf into v_fee_id;  
    181. 
    while cf%found loop  
    182. 
      delete from user_fee_info where fee_id = v_fee_id;  
    183. 
      delete from month_fee_info where fee_id = v_fee_id;  
    184. 
      commit;  
    185. 
        
    186. 
      fetch cf into v_fee_id;  
    187. 
    end loop;  
    188. 
      
    189. 
    close cf;  
    190. 
      
    191. 
  exception when others then  
    192. 
    rollback;  
    193. 
    return;  
    194. 
  end;    
    195. 
  
    196. 
  
    197. 
    
    198. 
end proc_delete_month_fee_info;  
    199. 
  
    200. 
  
    201. 
  
    202. 
create or replace procedure proc_down_card_user_info  
    203. 
------------------------------------------------------------------------------  
    204. 
---功能描述：(重要)通过查询本地的group_sims表，获取sim号码集合。           ---  
    205. 
---          该表里面的sim字段有两种类型，1种是真实的sim号码，另外一种是   ---  
    206. 
---          虚拟的SIM号码，该虚拟的号码可以对应多哥真实的SIM号码。        ---  
    207. 
---          simtype字段代表SIM类型,0代表是虚拟的号码，1代表是真实的号码。 ---  
    208. 
---          当sim字段为虚拟号码时，对应的simtype为0，同时groupsim为空     ---  
    209. 
---          当sim字段为真实号码时，对应的simtype为1，如果groupsim为空，   ---  
    210. 
---          代表该号码没有对应的虚拟号。                                  ---  
    211. 
---          groupuserid字段代表号码所对应的本地用户，一个用户可以对应多个 ---  
    212. 
---          sim(虚拟的与真实的。)                                         ---  
    213. 
---                                                                        ---  
    214. 
---          遍历group_sims表，查询sim字段，如果为虚拟号码，就调用         ---  
    215. 
---           proc_get_payed_msisdns，获取其绑定的真实号码,然后将该批真实  ---  
    216. 
---           号码插入到group_sims，同时更新card_user_info表，如果sim是    ---  
    217. 
---          真实号码，则只更新card_user_info表 。                         ---  
    218. 
---时间：2008-09-05                                                        ---  
    219. 
---作者：zhouyq                                                            ---  
    220. 
---单位：厦门新科技软件股份有限公司                                        ---  
    221. 
------------------------------------------------------------------------------  
    222. 
is  
    223. 
  
    224. 
group_sims_info group_sims%rowtype;--定义group_sims表类型  
    225. 
  
    226. 
type type_group_sims_ref is ref cursor; --定义group_sims表游标类型  
    227. 
group_sims_ref type_group_sims_ref;  
    228. 
  
    229. 
user_record_info pkg_gps_audit.user_record; --定义用户基本类型  
    230. 
  
    231. 
msisdn_table_temp pkg_gps_audit.msisdn_table; --用户SIM号码集合  
    232. 
msisdn_len number(10) := 0; --用户SIM号码集合长度  
    233. 
  
    234. 
v_success number(1) := 1; --成功标志  
    235. 
  
    236. 
begin  
    237. 
  begin  
    238. 
    --打开group_sims游标  
    239. 
    open group_sims_ref for select * from group_sims where (groupsim is  null or length(ltrim(groupsim)) = 0);  
    240. 
    fetch group_sims_ref into group_sims_info;  
    241. 
  
    242. 
    --开始遍历该游标  
    243. 
    while group_sims_ref%found loop  
    244. 
      --首先判断是否是虚拟号码,  
    245. 
      if group_sims_info.simtype = 0 then  
    246. 
        --首先通过虚拟号码，获取对应的SIM号码;  
    247. 
        begin  
    248. 
          pkg_gps_audit.proc_get_payed_msisdns(group_sims_info.sim,msisdn_table_temp,msisdn_len);  
    249. 
            
    250. 
          if msisdn_len > 0 then  
    251. 
            for iLen in 1 .. msisdn_len loop  
    252. 
              --首先更新原来group_sims表中同一个虚拟卡所对应的sim卡(先删后插)  
    253. 
              delete from group_sims t where t.simtype = 1 and to_number(t.groupsim) = group_sims_info.sim and t.groupuserid = group_sims_info.groupuserid;  
    254. 
                 
    255. 
                
    256. 
              for xLen in 1..msisdn_len loop  
    257. 
                begin  
    258. 
                  -----通过msisdn获取用户基本信息  
    259. 
                  user_record_info := func_get_user_by_msisdn(msisdn_table_temp(xLen));  
    260. 
                exception when others then  
    261. 
                  dbms_output.put_line('通过msisdn获取用户基本信息出现了异常！');   
    262. 
                  v_success := 0;  
    263. 
                end;  
    264. 
                
    265. 
                  
    266. 
                if v_success > 0 then  
    267. 
                   insert into group_sims(id,sim,groupsim,groupuserid,simtype,updatetime)values(  
    268. 
                   SEQ_GROUP_SIMS.Nextval,to_char(msisdn_table_temp(xLen)),group_sims_info.sim,group_sims_info.groupuserid,  
    269. 
                  1,sysdate);  
    270. 
                    
    271. 
                  --更新原来在card_user_info里面的数据(先删除已经存在的再插入新的数据)  
    272. 
                  delete from card_user_info where msisdn =  msisdn_table_temp(xLen);  
    273. 
                 
    274. 
                  insert into card_user_info(user_id,msisdn,brand_name,deal_name,service_status,stop_time,status)  
    275. 
                  values(user_record_info.user_id,user_record_info.msisdn,user_record_info.brand_name,user_record_info.deal_name  
    276. 
                  ,user_record_info.service_status,user_record_info.stop_time,1);  
    277. 
                end if;  
    278. 
                   
    279. 
                --提交数据  
    280. 
                commit;  
    281. 
              end loop;  
    282. 
                 
    283. 
            end loop;  
    284. 
               
    285. 
          end if;  
    286. 
            
    287. 
            
    288. 
          --没有与该虚拟卡对应的SIM号码  
    289. 
          if msisdn_len <= 0 then    
    290. 
            --首先更新原来group_sims表中同一个虚拟卡所对应的sim卡(先删后插)  
    291. 
              delete from group_sims t where t.simtype = 1 and to_number(t.groupsim) = group_sims_info.sim and t.groupuserid = group_sims_info.groupuserid;  
    292. 
              commit;  
    293. 
          end if;  
    294. 
            
    295. 
        exception when others then  
    296. 
          --回滚数据  
    297. 
          rollback;  
    298. 
          dbms_output.put_line('首先通过虚拟号码，获取对应的SIM号码出现了异常!');  
    299. 
        end;  
    300. 
      end if;    
    301. 
           
    302. 
      --如果是真实号码,并且没有附属虚拟号码，则直接更新card_user_info表    
    303. 
      if group_sims_info.simtype > 0 and  (group_sims_info.groupsim is null or length(group_sims_info.groupsim) = 0 ) then  
    304. 
        begin  
    305. 
          user_record_info := func_get_user_by_msisdn(group_sims_info.sim);  
    306. 
            
    307. 
           --更新原来在card_user_info里面的数据(先删除已经存在的再插入新的数据)  
    308. 
          delete from card_user_info where msisdn =  group_sims_info.sim ;  
    309. 
          insert into card_user_info(user_id,msisdn,brand_name,deal_name,service_status,stop_time,status)  
    310. 
          values(user_record_info.user_id,user_record_info.msisdn,user_record_info.brand_name,user_record_info.deal_name  
    311. 
          ,user_record_info.service_status,user_record_info.stop_time,1);  
    312. 
            
    313. 
          --提交数据  
    314. 
          commit;  
    315. 
        
    316. 
        exception when others then  
    317. 
           --回滚数据  
    318. 
          rollback;  
    319. 
          dbms_output.put_line('更新card_user_info表出现了异常');  
    320. 
        end;  
    321. 
          
    322. 
          
    323. 
      end if;  
    324. 
  
    325. 
  
    326. 
      --遍历游标  
    327. 
      fetch group_sims_ref into group_sims_info;  
    328. 
    end loop;  
    329. 
      
    330. 
    --关闭游标  
    331. 
    close group_sims_ref;  
    332. 
  
    333. 
  
    334. 
  
    335. 
  exception  
    336. 
    when others then  
    337. 
    return;  
    338. 
  end;  
    339. 
  
    340. 
  
    341. 
  
    342. 
end proc_down_card_user_info;  
    343. 
  
    344. 
  
    345. 
  
    346. 
create or replace procedure proc_down_change_card  
    347. 
  
    348. 
------------------------------------------------------------------------------  
    349. 
---功能描述：(重要)通过查询本地的group_sims表，获取sim号码集合。           ---  
    350. 
---          该表里面的sim字段有两种类型，1种是真实的sim号码，另外一种是   ---  
    351. 
---          虚拟的SIM号码，该虚拟的号码可以对应多哥真实的SIM号码。        ---  
    352. 
---          simtype字段代表SIM类型,0代表是虚拟的号码，1代表是真实的号码。 ---  
    353. 
---          当sim字段为虚拟号码时，对应的simtype为0，同时groupsim为空     ---  
    354. 
---          当sim字段为真实号码时，对应的simtype为1，如果groupsim为空，   ---  
    355. 
---          代表该号码没有对应的虚拟号。                                  ---  
    356. 
---          groupuserid字段代表号码所对应的本地用户，一个用户可以对应多个 ---  
    357. 
---          sim(虚拟的与真实的。)                                         ---  
    358. 
---                                                                        ---  
    359. 
---          遍历group_sims表，查询sim字段，如果为虚拟号码，就调用         ---  
    360. 
---           proc_get_payed_msisdns，获取其绑定的真实号码,然后再获取换补卡---  
    361. 
---           信息以更新change_card表，如果sim是真实号码，                 ---  
    362. 
---            则只更新change_card表 。                                    ---  
    363. 
---时间：2008-09-05                                                        ---  
    364. 
---作者：zhouyq                                                            ---  
    365. 
---单位：厦门新科技软件股份有限公司                                        ---  
    366. 
------------------------------------------------------------------------------  
    367. 
  
    368. 
is  
    369. 
  
    370. 
group_sims_info group_sims%rowtype;--定义group_sims表类型  
    371. 
  
    372. 
type type_group_sims_ref is ref cursor; --定义group_sims表游标类型  
    373. 
group_sims_ref type_group_sims_ref;  
    374. 
  
    375. 
   
    376. 
  
    377. 
simcard_table_temp pkg_gps_audit.simcard_table; --用户换补卡信息集合  
    378. 
simcard_len number(10) := 0; --用户换补卡信息集合长度  
    379. 
  
    380. 
msisdn_table_temp pkg_gps_audit.msisdn_table; --用户SIM号码集合  
    381. 
msisdn_len number(10) := 0; --用户SIM号码集合长度  
    382. 
  
    383. 
begin  
    384. 
  begin  
    385. 
    --打开group_sims游标  
    386. 
    open group_sims_ref for select * from group_sims where (groupsim is  null or length(ltrim(groupsim)) = 0);  
    387. 
    fetch group_sims_ref into group_sims_info;  
    388. 
  
    389. 
    --开始遍历该游标  
    390. 
    while group_sims_ref%found loop  
    391. 
      --首先判断是否是虚拟号码,  
    392. 
      if group_sims_info.simtype = 0 then  
    393. 
        --首先通过虚拟号码，获取对应的SIM号码;  
    394. 
        begin  
    395. 
          pkg_gps_audit.proc_get_payed_msisdns(group_sims_info.sim,msisdn_table_temp,msisdn_len);  
    396. 
            
    397. 
          if msisdn_len > 0 then --有数据  
    398. 
            for iLen in 1 .. msisdn_len loop  
    399. 
               begin  
    400. 
                 --通过用户ID，获取远程的换补卡用户信息集合  
    401. 
                 pkg_gps_audit.proc_get_simcard_his(func_get_user_id(msisdn_table_temp(iLen)),simcard_table_temp,simcard_len);  
    402. 
            
    403. 
                 --更新原来在change_card里面的数据(先删除已经存在的再插入新的数据)  
    404. 
                 if simcard_len > 0 then  
    405. 
                   --这个步骤的删除条件 有待确认。？？  
    406. 
                   delete from change_card where msisdn = group_sims_info.sim ;  
    407. 
                   for ilen in 1 .. simcard_len loop  
    408. 
                     insert into change_card(user_id,msisdn,accept_time,accept_memo,status) values(  
    409. 
                     simcard_table_temp(ilen).user_id,simcard_table_temp(ilen).msisdn,simcard_table_temp(ilen).accept_time,  
    410. 
                     simcard_table_temp(ilen).accept_memo,1);  
    411. 
                   end loop;  
    412. 
                 end if;  
    413. 
                   
    414. 
                 if simcard_len = 0 then  
    415. 
                   dbms_output.put_line('通过用户IfffffffffD，获取远程的换补卡用户信息集合,出现了异常!');  
    416. 
                 end if;   
    417. 
                   
    418. 
                 commit;  
    419. 
               exception when others then  
    420. 
                 dbms_output.put_line('通过用户ID，获取远程的换补卡用户信息集合,出现了异常!');  
    421. 
                 rollback;  
    422. 
               end;  
    423. 
                
    424. 
                 
    425. 
            end loop;   
    426. 
          end if;  
    427. 
            
    428. 
            
    429. 
            
    430. 
        exception when others then  
    431. 
          --回滚数据  
    432. 
          rollback;  
    433. 
          dbms_output.put_line('首先通过虚拟号码，获取对应的SIM号码出现了异常!');  
    434. 
        end;  
    435. 
      end if;    
    436. 
           
    437. 
  
    438. 
      if  group_sims_info.simtype > 0 and  (group_sims_info.groupsim is null or length(group_sims_info.groupsim) = 0 ) then --如果是真实号码,并且没有附属虚拟号码，则直接更新change_card表  
    439. 
        begin  
    440. 
          --通过用户ID，获取远程的换补卡用户信息集合  
    441. 
          pkg_gps_audit.proc_get_simcard_his(func_get_user_id(group_sims_info.sim),simcard_table_temp,simcard_len);  
    442. 
            
    443. 
          --更新原来在change_card里面的数据(先删除已经存在的再插入新的数据)  
    444. 
          if simcard_len > 0 then  
    445. 
            --这个步骤的删除条件 有待确认。？？  
    446. 
            delete from change_card where msisdn = group_sims_info.sim ;  
    447. 
            for ilen in 1 .. simcard_len loop  
    448. 
              insert into change_card(user_id,msisdn,accept_time,accept_memo,status) values(  
    449. 
              simcard_table_temp(ilen).user_id,simcard_table_temp(ilen).msisdn,simcard_table_temp(ilen).accept_time,  
    450. 
              simcard_table_temp(ilen).accept_memo,1);  
    451. 
            end loop;  
    452. 
          end if;   
    453. 
            
    454. 
            
    455. 
          --提交数据  
    456. 
          commit;  
    457. 
        
    458. 
        exception when others then  
    459. 
           --回滚数据  
    460. 
          rollback;  
    461. 
           dbms_output.put_line('更新change_card表出现了异常');  
    462. 
        end;  
    463. 
          
    464. 
          
    465. 
      end if;  
    466. 
  
    467. 
  
    468. 
      --遍历游标  
    469. 
      fetch group_sims_ref into group_sims_info;  
    470. 
    end loop;  
    471. 
      
    472. 
    --关闭游标  
    473. 
    close group_sims_ref;  
    474. 
  
    475. 
  
    476. 
  
    477. 
  exception  
    478. 
    when others then  
    479. 
    return;  
    480. 
  end;  
    481. 
  
    482. 
  
    483. 
  
    484. 
end proc_down_change_card;  
    485. 
  
    486. 
  
    487. 
  
    488. 
create or replace procedure proc_down_fee_info  
    489. 
------------------------------------------------------------------------------  
    490. 
---功能描述：(重要)通过查询本地的group_sims表，获取sim号码集合。           ---  
    491. 
---          该表里面的sim字段有两种类型，1种是真实的sim号码，另外一种是   ---  
    492. 
---          虚拟的SIM号码，该虚拟的号码可以对应多哥真实的SIM号码。        ---  
    493. 
---          simtype字段代表SIM类型,0代表是虚拟的号码，1代表是真实的号码。 ---  
    494. 
---          当sim字段为虚拟号码时，对应的simtype为0，同时groupsim为空     ---  
    495. 
---          当sim字段为真实号码时，对应的simtype为1，如果groupsim为空，   ---  
    496. 
---          代表该号码没有对应的虚拟号。                                  ---  
    497. 
---          groupuserid字段代表号码所对应的本地用户，一个用户可以对应多个 ---  
    498. 
---          sim(虚拟的与真实的。)                                         ---  
    499. 
---                                                                        ---  
    500. 
---          遍历group_sims表，查询sim字段，如果为虚拟号码，就调用         ---  
    501. 
---           proc_get_payed_msisdns，获取其绑定的真实号码,然后获取每日消费---  
    502. 
---           情况，再分别保存到day_fee_Info,month_fee_info两张表，如果    ---  
    503. 
---           sim是 真实号码，则只更新day_fee_Info,month_fee_info表 。     ---  
    504. 
---时间：2008-09-05                                                        ---  
    505. 
---作者：zhouyq                                                            ---  
    506. 
---单位：厦门新科技软件股份有限公司                                        ---  
    507. 
------------------------------------------------------------------------------  
    508. 
  
    509. 
is  
    510. 
  
    511. 
group_sims_info group_sims%rowtype;--定义group_sims表类型  
    512. 
  
    513. 
type type_group_sims_ref is ref cursor; --定义group_sims表游标类型  
    514. 
group_sims_ref type_group_sims_ref;  
    515. 
  
    516. 
  
    517. 
msisdn_table_temp pkg_gps_audit.msisdn_table; --用户SIM号码集合  
    518. 
msisdn_len number(10) := 0; --用户SIM号码集合长度  
    519. 
  
    520. 
base_fee pkg_gps_audit.fee_record;  --用户每日消费结构信息  
    521. 
  
    522. 
is_first_day number(1); --是否是每月一号标志  
    523. 
user_fee_info_nextval number(15); --user_fee_info表的下一个序列号  
    524. 
  
    525. 
user_fee_info_temp pkg_gps_audit.fee_record;  --user_fee_info数据缓存  
    526. 
  
    527. 
v_fee_id number(15);--消费ID   
    528. 
   
    529. 
  
    530. 
begin  
    531. 
  begin  
    532. 
    
    533. 
    --打开group_sims游标  
    534. 
    open group_sims_ref for select * from group_sims where (groupsim is  null or length(ltrim(groupsim)) = 0);  
    535. 
    fetch group_sims_ref into group_sims_info;  
    536. 
  
    537. 
    --开始遍历该游标  
    538. 
    while group_sims_ref%found loop  
    539. 
      --首先判断是否是虚拟号码,  
    540. 
      if group_sims_info.simtype = 0 then  
    541. 
        --首先通过虚拟号码，获取对应的SIM号码;  
    542. 
        begin  
    543. 
          pkg_gps_audit.proc_get_payed_msisdns(group_sims_info.sim,msisdn_table_temp,msisdn_len);  
    544. 
            
    545. 
          if msisdn_len > 0 then --有数据  
    546. 
            for iLen in 1 .. msisdn_len loop  
    547. 
                
    548. 
              --首先获取该用户每日消费记录,如果今天刚好是1号，那么获取到的数据刚好是上个月1-30号的总和  
    549. 
              --如果不是1号，那么获取到的是本月前几天的总和  
    550. 
              pkg_gps_audit.proc_get_userfee(func_get_user_id(msisdn_table_temp(iLen)),base_fee);  
    551. 
             
    552. 
              --判断当前日期是否是每月1号  
    553. 
              is_first_day := func_is_first_day();  
    554. 
              if is_first_day = 1 then  --1号  
    555. 
                
    556. 
                -----(begin)保存到user_fee_info,day_fee_info表--------------  
    557. 
                begin  
    558. 
                  
    559. 
                -----计算出上个月的总和  
    560. 
                begin  
    561. 
                  select  user_id,  
    562. 
                  sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),  
    563. 
                  sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),  
    564. 
                  sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),  
    565. 
                  sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),  
    566. 
                  sum(fee_gprs),sum(fee_wap),sum(fee_data_month),  
    567. 
                  sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),  
    568. 
                  sum(fee_all),sum(fee_for_others),sum(fee_by_others)   
    569. 
                  into user_fee_info_temp from user_fee_info  
    570. 
                  where user_id = base_fee.user_id  
    571. 
                  and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm'))  group by user_id ;  
    572. 
                    
    573. 
                exception when others then  
    574. 
                  user_fee_info_temp.user_id := base_fee.user_id;  
    575. 
                  user_fee_info_temp.fee_base := 0.00;  
    576. 
                  user_fee_info_temp.fee_pkg_month := 0.00;  
    577. 
                  user_fee_info_temp.fee_keep := 0.00;  
    578. 
                  user_fee_info_temp.fee_oth_month := 0.00;  
    579. 
                  user_fee_info_temp.fee_vpn := 0.00;  
    580. 
                  user_fee_info_temp.fee_shift := 0.00;  
    581. 
                  user_fee_info_temp.fee_local := 0.00;  
    582. 
                  user_fee_info_temp.fee_long := 0.00;  
    583. 
                  user_fee_info_temp.fee_inprov := 0.00;  
    584. 
                  user_fee_info_temp.fee_inprov_long := 0.00;  
    585. 
                  user_fee_info_temp.fee_outprov := 0.00;  
    586. 
                  user_fee_info_temp.fee_outprov_long := 0.00;  
    587. 
                  user_fee_info_temp.fee_inter := 0.00;  
    588. 
                  user_fee_info_temp.fee_inter_long := 0.00;  
    589. 
                  user_fee_info_temp.fee_cmnet := 0.00;  
    590. 
                  user_fee_info_temp.fee_trust := 0.00;  
    591. 
                  user_fee_info_temp.fee_ptp_sms := 0.00;  
    592. 
                  user_fee_info_temp.fee_mms := 0.00;  
    593. 
                  user_fee_info_temp.fee_magazine := 0.00;  
    594. 
                  user_fee_info_temp.fee_gprs := 0.00;  
    595. 
                  user_fee_info_temp.fee_wap := 0.00;  
    596. 
                  user_fee_info_temp.fee_data_month := 0.00;  
    597. 
                  user_fee_info_temp.fee_data := 0.00;  
    598. 
                  user_fee_info_temp.fee_ring := 0.00;  
    599. 
                  user_fee_info_temp.fee_display := 0.00;  
    600. 
                  user_fee_info_temp.fee_ext := 0.00;  
    601. 
                  user_fee_info_temp.fee_other := 0.00;  
    602. 
                  user_fee_info_temp.fee_all := 0.00;  
    603. 
                  user_fee_info_temp.fee_for_others := 0.00;  
    604. 
                  user_fee_info_temp.fee_by_others := 0.00;  
    605. 
                end;  
    606. 
                  
    607. 
               
    608. 
                --保存到user_fee_info,day_fee_info表  
    609. 
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    610. 
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    611. 
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    612. 
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    613. 
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    614. 
                values(  
    615. 
                user_fee_info_nextval,base_fee.user_id,  
    616. 
                decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),  
    617. 
                decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),  
    618. 
                decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),   
    619. 
                decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),    
    620. 
                decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),     
    621. 
                decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),      
    622. 
                decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),      
    623. 
                decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),      
    624. 
                decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),      
    625. 
                decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),      
    626. 
                decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),      
    627. 
                decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),      
    628. 
                decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),      
    629. 
                decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),       
    630. 
                decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),        
    631. 
                decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),         
    632. 
                decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),          
    633. 
                decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),           
    634. 
                decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),            
    635. 
                decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),            
    636. 
                decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),             
    637. 
                decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),              
    638. 
                decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),                
    639. 
                decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee.fee_ring-user_fee_info_temp.fee_ring,0.00),                 
    640. 
                decode(sign(base_fee.fee_display-user_fee_info_temp.fee_display),1,base_fee.fee_display-user_fee_info_temp.fee_display,0.00),                 
    641. 
                decode(sign(base_fee.fee_ext-user_fee_info_temp.fee_ext),1,base_fee.fee_ext-user_fee_info_temp.fee_ext,0.00),                 
    642. 
                decode(sign(base_fee.fee_other-user_fee_info_temp.fee_other),1,base_fee.fee_other-user_fee_info_temp.fee_other,0.00),                  
    643. 
                decode(sign(base_fee.fee_all-user_fee_info_temp.fee_all),1,base_fee.fee_all-user_fee_info_temp.fee_all,0.00),                  
    644. 
                decode(sign(base_fee.fee_for_others-user_fee_info_temp.fee_for_others),1,base_fee.fee_for_others-user_fee_info_temp.fee_for_others,0.00),                  
    645. 
                decode(sign(base_fee.fee_by_others-user_fee_info_temp.fee_by_others),1,base_fee.fee_by_others-user_fee_info_temp.fee_by_others,0.00) );  
    646. 
               
    647. 
                insert into day_fee_info(id,fee_id,start_time) values(seq_day_fee_info.nextval,user_fee_info_nextval,sysdate-1);  
    648. 
                  
    649. 
                commit;  
    650. 
                exception when others then  
    651. 
                  rollback;  
    652. 
                  dbms_output.put_line('保存到user_fee_info,day_fee_info表出现了异常');  
    653. 
                end;  
    654. 
                -------------(end)保存到user_fee_info,day_fee_info表-------------------------------------  
    655. 
               
    656. 
               
    657. 
                -------------------(begin)保存到user_fee_info,month_fee_info表---------------------------------  
    658. 
                begin  
    659. 
                  
    660. 
                begin  
    661. 
                ----首先删除再更新原来的user_fee_info,month_fee_info  
    662. 
                select a.fee_id  into v_fee_id  from month_fee_info a, user_fee_info b where a.fee_id = b.fee_id and b.user_id = base_fee.user_id   and to_char(a.start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm');  
    663. 
                delete from user_fee_info x where x.fee_id = v_fee_id;  
    664. 
                delete from month_fee_info y where y.fee_id = v_fee_id;   
    665. 
                commit;  
    666. 
                  
    667. 
                  
    668. 
                exception when others then  
    669. 
                  rollback;  
    670. 
                end;  
    671. 
                ----插入新的数据  
    672. 
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    673. 
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    674. 
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    675. 
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    676. 
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    677. 
                values( user_fee_info_nextval,base_fee.user_id, base_fee.fee_base, base_fee.fee_pkg_month,  
    678. 
                base_fee.fee_keep, base_fee.fee_oth_month, base_fee.fee_vpn,  
    679. 
                base_fee.fee_shift, base_fee.fee_local, base_fee.fee_long, base_fee.fee_inprov,  
    680. 
                base_fee.fee_inprov_long, base_fee.fee_outprov, base_fee.fee_outprov_long,  
    681. 
                base_fee.fee_inter, base_fee.fee_inter_long, base_fee.fee_cmnet,  
    682. 
                base_fee.fee_trust, base_fee.fee_ptp_sms,  base_fee.fee_mms,  
    683. 
                base_fee.fee_magazine,  base_fee.fee_gprs,  base_fee.fee_wap,  
    684. 
                base_fee.fee_data_month,  base_fee.fee_data,  base_fee.fee_ring,  
    685. 
                base_fee.fee_display, base_fee.fee_ext,  base_fee.fee_other,  
    686. 
                base_fee.fee_all,  base_fee.fee_for_others, base_fee.fee_by_others);  
    687. 
              
    688. 
                insert into month_fee_info(id,fee_id,start_time) values(seq_month_fee_info.nextval,user_fee_info_nextval,sysdate-1);       
    689. 
                commit;  
    690. 
                exception when others then  
    691. 
                  rollback;  
    692. 
                  dbms_output.put_line('保存到user_fee_info,month_fee_info表出现了异常');  
    693. 
                end;  
    694. 
                ----------------(end)保存到user_fee_info,month_fee_info表--------------------------------------  
    695. 
                
    696. 
              else --不是本月第一天  
    697. 
                
    698. 
                -------------------(begin)保存到user_fee_info,day_fee_info表-------------------  
    699. 
                begin  
    700. 
                  
    701. 
                begin  
    702. 
                --计算出本月的总和  
    703. 
                select  user_id,  
    704. 
                sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),  
    705. 
                sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),  
    706. 
                sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),  
    707. 
                sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),  
    708. 
                sum(fee_gprs),sum(fee_wap),sum(fee_data_month),  
    709. 
                sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),  
    710. 
                sum(fee_all),sum(fee_for_others),sum(fee_by_others)   
    711. 
                into user_fee_info_temp from user_fee_info  
    712. 
                where user_id =  base_fee.user_id  
    713. 
                and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(sysdate,'yyyy-mm'))  group by user_id ;  
    714. 
                  
    715. 
                exception when others then  
    716. 
                  user_fee_info_temp.user_id := base_fee.user_id;  
    717. 
                  user_fee_info_temp.fee_base := 0.00;  
    718. 
                  user_fee_info_temp.fee_pkg_month := 0.00;  
    719. 
                  user_fee_info_temp.fee_keep := 0.00;  
    720. 
                  user_fee_info_temp.fee_oth_month := 0.00;  
    721. 
                  user_fee_info_temp.fee_vpn := 0.00;  
    722. 
                  user_fee_info_temp.fee_shift := 0.00;  
    723. 
                  user_fee_info_temp.fee_local := 0.00;  
    724. 
                  user_fee_info_temp.fee_long := 0.00;  
    725. 
                  user_fee_info_temp.fee_inprov := 0.00;  
    726. 
                  user_fee_info_temp.fee_inprov_long := 0.00;  
    727. 
                  user_fee_info_temp.fee_outprov := 0.00;  
    728. 
                  user_fee_info_temp.fee_outprov_long := 0.00;  
    729. 
                  user_fee_info_temp.fee_inter := 0.00;  
    730. 
                  user_fee_info_temp.fee_inter_long := 0.00;  
    731. 
                  user_fee_info_temp.fee_cmnet := 0.00;  
    732. 
                  user_fee_info_temp.fee_trust := 0.00;  
    733. 
                  user_fee_info_temp.fee_ptp_sms := 0.00;  
    734. 
                  user_fee_info_temp.fee_mms := 0.00;  
    735. 
                  user_fee_info_temp.fee_magazine := 0.00;  
    736. 
                  user_fee_info_temp.fee_gprs := 0.00;  
    737. 
                  user_fee_info_temp.fee_wap := 0.00;  
    738. 
                  user_fee_info_temp.fee_data_month := 0.00;  
    739. 
                  user_fee_info_temp.fee_data := 0.00;  
    740. 
                  user_fee_info_temp.fee_ring := 0.00;  
    741. 
                  user_fee_info_temp.fee_display := 0.00;  
    742. 
                  user_fee_info_temp.fee_ext := 0.00;  
    743. 
                  user_fee_info_temp.fee_other := 0.00;  
    744. 
                  user_fee_info_temp.fee_all := 0.00;  
    745. 
                  user_fee_info_temp.fee_for_others := 0.00;  
    746. 
                  user_fee_info_temp.fee_by_others := 0.00;  
    747. 
                end;  
    748. 
               
    749. 
                --保存到user_fee_info,day_fee_info表  
    750. 
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    751. 
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    752. 
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    753. 
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    754. 
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    755. 
                values(  
    756. 
                user_fee_info_nextval,base_fee.user_id,  
    757. 
                decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),  
    758. 
                decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),  
    759. 
                decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),   
    760. 
                decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),    
    761. 
                decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),     
    762. 
                decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),      
    763. 
                decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),      
    764. 
                decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),      
    765. 
                decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),      
    766. 
                decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),      
    767. 
                decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),      
    768. 
                decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),      
    769. 
                decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),      
    770. 
                decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),       
    771. 
                decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),        
    772. 
                decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),         
    773. 
                decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),          
    774. 
                decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),           
    775. 
                decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),            
    776. 
                decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),            
    777. 
                decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),             
    778. 
                decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),              
    779. 
                decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),                
    780. 
                decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee.fee_ring-user_fee_info_temp.fee_ring,0.00),                 
    781. 
                decode(sign(base_fee.fee_display-user_fee_info_temp.fee_display),1,base_fee.fee_display-user_fee_info_temp.fee_display,0.00),                 
    782. 
                decode(sign(base_fee.fee_ext-user_fee_info_temp.fee_ext),1,base_fee.fee_ext-user_fee_info_temp.fee_ext,0.00),                 
    783. 
                decode(sign(base_fee.fee_other-user_fee_info_temp.fee_other),1,base_fee.fee_other-user_fee_info_temp.fee_other,0.00),                  
    784. 
                decode(sign(base_fee.fee_all-user_fee_info_temp.fee_all),1,base_fee.fee_all-user_fee_info_temp.fee_all,0.00),                  
    785. 
                decode(sign(base_fee.fee_for_others-user_fee_info_temp.fee_for_others),1,base_fee.fee_for_others-user_fee_info_temp.fee_for_others,0.00),                  
    786. 
                decode(sign(base_fee.fee_by_others-user_fee_info_temp.fee_by_others),1,base_fee.fee_by_others-user_fee_info_temp.fee_by_others,0.00) );  
    787. 
               
    788. 
                insert into day_fee_info(id,fee_id,start_time) values(seq_day_fee_info.nextval,user_fee_info_nextval,sysdate-1);  
    789. 
                  
    790. 
                commit;  
    791. 
                exception when others then  
    792. 
                  rollback;  
    793. 
                  dbms_output.put_line('保存到user_fee_info,day_fee_info表出现了异常');  
    794. 
                end;  
    795. 
                ------------------(end)保存到user_fee_info,day_fee_info表----------------------------------  
    796. 
               
    797. 
              
    798. 
               
    799. 
               
    800. 
                 
    801. 
                ----------------(begin)保存到user_fee_info,month_fee_info表----------------------  
    802. 
                begin  
    803. 
                  
    804. 
                begin  
    805. 
                ----首先删除再更新原来的user_fee_info,month_fee_info  
    806. 
                 
    807. 
                select a.fee_id  into v_fee_id  from month_fee_info a, user_fee_info b where a.fee_id = b.fee_id and b.user_id = base_fee.user_id   and to_char(a.start_time,'yyyy-mm') = to_char(sysdate,'yyyy-mm') ;  
    808. 
                delete from user_fee_info x where x.fee_id = v_fee_id;  
    809. 
                delete from month_fee_info y where y.fee_id = v_fee_id;   
    810. 
                commit;  
    811. 
                exception when others then  
    812. 
                  rollback;  
    813. 
                end;  
    814. 
                
    815. 
               
    816. 
                ----插入新的数据  
    817. 
                select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    818. 
                insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    819. 
                fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    820. 
                fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    821. 
                fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    822. 
                values( user_fee_info_nextval,base_fee.user_id, base_fee.fee_base, base_fee.fee_pkg_month,  
    823. 
                base_fee.fee_keep, base_fee.fee_oth_month, base_fee.fee_vpn,  
    824. 
                base_fee.fee_shift, base_fee.fee_local, base_fee.fee_long, base_fee.fee_inprov,  
    825. 
                base_fee.fee_inprov_long, base_fee.fee_outprov, base_fee.fee_outprov_long,  
    826. 
                base_fee.fee_inter, base_fee.fee_inter_long, base_fee.fee_cmnet,  
    827. 
                base_fee.fee_trust, base_fee.fee_ptp_sms,  base_fee.fee_mms,  
    828. 
                base_fee.fee_magazine,  base_fee.fee_gprs,  base_fee.fee_wap,  
    829. 
                base_fee.fee_data_month,  base_fee.fee_data,  base_fee.fee_ring,  
    830. 
                base_fee.fee_display, base_fee.fee_ext,  base_fee.fee_other,  
    831. 
                base_fee.fee_all,  base_fee.fee_for_others, base_fee.fee_by_others);  
    832. 
              
    833. 
                insert into month_fee_info(id,fee_id,start_time) values(seq_month_fee_info.nextval,user_fee_info_nextval,sysdate);  
    834. 
                  
    835. 
                commit;  
    836. 
                exception when others then  
    837. 
                  rollback;  
    838. 
                  dbms_output.put_line('保存到user_fee_info,month_fee_info表出现了异常');  
    839. 
                end;   
    840. 
                ----------------(end)保存到user_fee_info,month_fee_info表----------------------       
    841. 
  
    842. 
              end if;  
    843. 
             
    844. 
             
    845. 
                 
    846. 
                
    847. 
                 
    848. 
            end loop;   
    849. 
          end if;  
    850. 
            
    851. 
            
    852. 
             
    853. 
        exception when others then  
    854. 
          --回滚数据  
    855. 
          rollback;  
    856. 
          dbms_output.put_line('首先通过虚拟号码，获取对应的SIM号码出现了异常!');  
    857. 
        end;  
    858. 
      end if;    
    859. 
           
    860. 
           
    861. 
      --如果是真实号码,并且没有附属虚拟号码，则直接更新day_fee_info,month_fee_info表  
    862. 
      if group_sims_info.simtype > 0 and (group_sims_info.groupsim is null or length(group_sims_info.groupsim) = 0 ) then  
    863. 
        begin  
    864. 
           --首先获取该用户每日消费记录,如果今天刚好是1号，那么获取到的数据刚好是上个月1-30号的总和  
    865. 
           --如果不是1号，那么获取到的是本月前几天的总和  
    866. 
           pkg_gps_audit.proc_get_userfee(func_get_user_id(group_sims_info.sim),base_fee);  
    867. 
             
    868. 
           --判断当前日期是否是每月1号  
    869. 
           is_first_day := func_is_first_day();  
    870. 
           if is_first_day = 1 then   
    871. 
             ----------------------(begin)-------------------   
    872. 
             begin  
    873. 
               
    874. 
             begin  
    875. 
               --计算出上个月的总和  
    876. 
               select  user_id,  
    877. 
               sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),  
    878. 
               sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),  
    879. 
               sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),  
    880. 
               sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),  
    881. 
               sum(fee_gprs),sum(fee_wap),sum(fee_data_month),  
    882. 
               sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),  
    883. 
               sum(fee_all),sum(fee_for_others),sum(fee_by_others)   
    884. 
               into user_fee_info_temp from user_fee_info  
    885. 
               where user_id = func_get_user_id(group_sims_info.sim)  
    886. 
               and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm'))    
    887. 
               group by user_id ;  
    888. 
               
    889. 
             exception when others then  
    890. 
                  user_fee_info_temp.user_id := base_fee.user_id;  
    891. 
                  user_fee_info_temp.fee_base := 0.00;  
    892. 
                  user_fee_info_temp.fee_pkg_month := 0.00;  
    893. 
                  user_fee_info_temp.fee_keep := 0.00;  
    894. 
                  user_fee_info_temp.fee_oth_month := 0.00;  
    895. 
                  user_fee_info_temp.fee_vpn := 0.00;  
    896. 
                  user_fee_info_temp.fee_shift := 0.00;  
    897. 
                  user_fee_info_temp.fee_local := 0.00;  
    898. 
                  user_fee_info_temp.fee_long := 0.00;  
    899. 
                  user_fee_info_temp.fee_inprov := 0.00;  
    900. 
                  user_fee_info_temp.fee_inprov_long := 0.00;  
    901. 
                  user_fee_info_temp.fee_outprov := 0.00;  
    902. 
                  user_fee_info_temp.fee_outprov_long := 0.00;  
    903. 
                  user_fee_info_temp.fee_inter := 0.00;  
    904. 
                  user_fee_info_temp.fee_inter_long := 0.00;  
    905. 
                  user_fee_info_temp.fee_cmnet := 0.00;  
    906. 
                  user_fee_info_temp.fee_trust := 0.00;  
    907. 
                  user_fee_info_temp.fee_ptp_sms := 0.00;  
    908. 
                  user_fee_info_temp.fee_mms := 0.00;  
    909. 
                  user_fee_info_temp.fee_magazine := 0.00;  
    910. 
                  user_fee_info_temp.fee_gprs := 0.00;  
    911. 
                  user_fee_info_temp.fee_wap := 0.00;  
    912. 
                  user_fee_info_temp.fee_data_month := 0.00;  
    913. 
                  user_fee_info_temp.fee_data := 0.00;  
    914. 
                  user_fee_info_temp.fee_ring := 0.00;  
    915. 
                  user_fee_info_temp.fee_display := 0.00;  
    916. 
                  user_fee_info_temp.fee_ext := 0.00;  
    917. 
                  user_fee_info_temp.fee_other := 0.00;  
    918. 
                  user_fee_info_temp.fee_all := 0.00;  
    919. 
                  user_fee_info_temp.fee_for_others := 0.00;  
    920. 
                  user_fee_info_temp.fee_by_others := 0.00;  
    921. 
                end;  
    922. 
               
    923. 
             --保存到user_fee_info,day_fee_info表  
    924. 
             select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    925. 
             insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    926. 
             fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    927. 
             fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    928. 
             fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    929. 
             values(  
    930. 
             user_fee_info_nextval,base_fee.user_id,  
    931. 
             decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),  
    932. 
             decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),  
    933. 
             decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),   
    934. 
             decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),    
    935. 
             decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),     
    936. 
             decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),      
    937. 
             decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),      
    938. 
             decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),      
    939. 
             decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),      
    940. 
             decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),      
    941. 
             decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),      
    942. 
             decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),      
    943. 
             decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),      
    944. 
             decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),       
    945. 
             decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),        
    946. 
             decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),         
    947. 
             decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),          
    948. 
             decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),           
    949. 
             decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),            
    950. 
             decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),            
    951. 
             decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),             
    952. 
             decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),              
    953. 
             decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),                
    954. 
             decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee.fee_ring-user_fee_info_temp.fee_ring,0.00),                 
    955. 
             decode(sign(base_fee.fee_display-user_fee_info_temp.fee_display),1,base_fee.fee_display-user_fee_info_temp.fee_display,0.00),                 
    956. 
             decode(sign(base_fee.fee_ext-user_fee_info_temp.fee_ext),1,base_fee.fee_ext-user_fee_info_temp.fee_ext,0.00),                 
    957. 
             decode(sign(base_fee.fee_other-user_fee_info_temp.fee_other),1,base_fee.fee_other-user_fee_info_temp.fee_other,0.00),                  
    958. 
             decode(sign(base_fee.fee_all-user_fee_info_temp.fee_all),1,base_fee.fee_all-user_fee_info_temp.fee_all,0.00),                  
    959. 
             decode(sign(base_fee.fee_for_others-user_fee_info_temp.fee_for_others),1,base_fee.fee_for_others-user_fee_info_temp.fee_for_others,0.00),                  
    960. 
             decode(sign(base_fee.fee_by_others-user_fee_info_temp.fee_by_others),1,base_fee.fee_by_others-user_fee_info_temp.fee_by_others,0.00) );  
    961. 
               
    962. 
             insert into day_fee_info(id,fee_id,start_time) values(seq_day_fee_info.nextval,user_fee_info_nextval,sysdate-1);  
    963. 
               
    964. 
             commit;  
    965. 
             exception when others then  
    966. 
               rollback;  
    967. 
               dbms_output.put_line('保存到user_fee_info,day_fee_info表出现了异常');  
    968. 
             end;  
    969. 
             ------------------(end)-------------------------------   
    970. 
               
    971. 
               
    972. 
             -------------------(begin)-----------------------------------------  
    973. 
             begin  
    974. 
               
    975. 
             --保存到user_fee_info,month_fee_info表  
    976. 
             begin  
    977. 
               ----首先删除再更新原来的user_fee_info,month_fee_info  
    978. 
                   
    979. 
                select a.fee_id into v_fee_id  from month_fee_info a, user_fee_info b where a.fee_id = b.fee_id and b.user_id = base_fee.user_id   and to_char(a.start_time,'yyyy-mm') = to_char(add_months(sysdate,-1),'yyyy-mm') ;  
    980. 
                delete from user_fee_info x where x.fee_id = v_fee_id;  
    981. 
                delete from month_fee_info y where y.fee_id = fee_id;  
    982. 
                commit;  
    983. 
                   
    984. 
                  
    985. 
             exception when others then  
    986. 
               rollback;  
    987. 
             end;  
    988. 
               
    989. 
             ----插入新的数据  
    990. 
             select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    991. 
             insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    992. 
             fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    993. 
             fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    994. 
             fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    995. 
             values( user_fee_info_nextval,base_fee.user_id, base_fee.fee_base, base_fee.fee_pkg_month,  
    996. 
             base_fee.fee_keep, base_fee.fee_oth_month, base_fee.fee_vpn,  
    997. 
             base_fee.fee_shift, base_fee.fee_local, base_fee.fee_long, base_fee.fee_inprov,  
    998. 
             base_fee.fee_inprov_long, base_fee.fee_outprov, base_fee.fee_outprov_long,  
    999. 
             base_fee.fee_inter, base_fee.fee_inter_long, base_fee.fee_cmnet,  
    1000. 
             base_fee.fee_trust, base_fee.fee_ptp_sms,  base_fee.fee_mms,  
    1001. 
             base_fee.fee_magazine,  base_fee.fee_gprs,  base_fee.fee_wap,  
    1002. 
             base_fee.fee_data_month,  base_fee.fee_data,  base_fee.fee_ring,  
    1003. 
             base_fee.fee_display, base_fee.fee_ext,  base_fee.fee_other,  
    1004. 
             base_fee.fee_all,  base_fee.fee_for_others, base_fee.fee_by_others);  
    1005. 
              
    1006. 
             insert into month_fee_info(id,fee_id,start_time) values(seq_month_fee_info.nextval,user_fee_info_nextval,sysdate-1);       
    1007. 
             commit;  
    1008. 
             exception when others then  
    1009. 
               rollback;  
    1010. 
               dbms_output.put_line('保存到user_fee_info,month_fee_info表出现了异常');  
    1011. 
             end;  
    1012. 
             ----------------------(end)-----------------------------------------------   
    1013. 
                
    1014. 
           else --不是本月第一天  
    1015. 
             ------------------------(begin)-------------------------------------  
    1016. 
             begin  
    1017. 
             --保存到user_fee_info,day_fee_info表  
    1018. 
               
    1019. 
             begin  
    1020. 
             --计算出本月的总和  
    1021. 
               select  user_id,  
    1022. 
               sum(fee_base),sum(fee_pkg_month),sum(fee_keep),sum(fee_oth_month),sum(fee_vpn),  
    1023. 
               sum(fee_shift),sum(fee_local),sum(fee_long),sum(fee_inprov),sum(fee_inprov_long),  
    1024. 
               sum(fee_outprov),sum(fee_outprov_long),sum(fee_inter),sum(fee_inter_long),  
    1025. 
               sum(fee_cmnet),sum(fee_trust),sum(fee_ptp_sms),sum(fee_mms),sum(fee_magazine),  
    1026. 
               sum(fee_gprs),sum(fee_wap),sum(fee_data_month),  
    1027. 
               sum(fee_data),sum(fee_ring),sum(fee_display),sum(fee_ext),sum(fee_other),  
    1028. 
               sum(fee_all),sum(fee_for_others),sum(fee_by_others)   
    1029. 
               into user_fee_info_temp from user_fee_info  
    1030. 
               where user_id = func_get_user_id(group_sims_info.sim)  
    1031. 
               and fee_id in (select fee_id from day_fee_info where to_char(start_time,'yyyy-mm') = to_char(sysdate,'yyyy-mm'))   
    1032. 
               group by user_id ;  
    1033. 
                 
    1034. 
             exception when others then  
    1035. 
                  user_fee_info_temp.user_id := base_fee.user_id;  
    1036. 
                    
    1037. 
                  user_fee_info_temp.fee_base := 0.00;  
    1038. 
                  user_fee_info_temp.fee_pkg_month := 0.00;  
    1039. 
                  user_fee_info_temp.fee_keep := 0.00;  
    1040. 
                  user_fee_info_temp.fee_oth_month := 0.00;  
    1041. 
                  user_fee_info_temp.fee_vpn := 0.00;  
    1042. 
                  user_fee_info_temp.fee_shift := 0.00;  
    1043. 
                  user_fee_info_temp.fee_local := 0.00;  
    1044. 
                  user_fee_info_temp.fee_long := 0.00;  
    1045. 
                  user_fee_info_temp.fee_inprov := 0.00;  
    1046. 
                  user_fee_info_temp.fee_inprov_long := 0.00;  
    1047. 
                  user_fee_info_temp.fee_outprov := 0.00;  
    1048. 
                  user_fee_info_temp.fee_outprov_long := 0.00;  
    1049. 
                  user_fee_info_temp.fee_inter := 0.00;  
    1050. 
                  user_fee_info_temp.fee_inter_long := 0.00;  
    1051. 
                  user_fee_info_temp.fee_cmnet := 0.00;  
    1052. 
                  user_fee_info_temp.fee_trust := 0.00;  
    1053. 
                  user_fee_info_temp.fee_ptp_sms := 0.00;  
    1054. 
                  user_fee_info_temp.fee_mms := 0.00;  
    1055. 
                  user_fee_info_temp.fee_magazine := 0.00;  
    1056. 
                  user_fee_info_temp.fee_gprs := 0.00;  
    1057. 
                  user_fee_info_temp.fee_wap := 0.00;  
    1058. 
                  user_fee_info_temp.fee_data_month := 0.00;  
    1059. 
                  user_fee_info_temp.fee_data := 0.00;  
    1060. 
                  user_fee_info_temp.fee_ring := 0.00;  
    1061. 
                  user_fee_info_temp.fee_display := 0.00;  
    1062. 
                  user_fee_info_temp.fee_ext := 0.00;  
    1063. 
                  user_fee_info_temp.fee_other := 0.00;  
    1064. 
                  user_fee_info_temp.fee_all := 0.00;  
    1065. 
                  user_fee_info_temp.fee_for_others := 0.00;  
    1066. 
                  user_fee_info_temp.fee_by_others := 0.00;  
    1067. 
                end;  
    1068. 
                  
    1069. 
             --保存到user_fee_info,day_fee_info表  
    1070. 
             select seq_user_fee_info.nextval into user_fee_info_nextval from dual;  
    1071. 
             insert into user_fee_info(fee_id,user_id,fee_base,fee_pkg_month,fee_keep,fee_oth_month,fee_vpn,  
    1072. 
             fee_shift,fee_local,fee_long,fee_inprov,fee_inprov_long,fee_outprov,fee_outprov_long,fee_inter,fee_inter_long,  
    1073. 
             fee_cmnet,fee_trust,fee_ptp_sms,fee_mms,fee_magazine,fee_gprs,fee_wap,fee_data_month,  
    1074. 
             fee_data,fee_ring,fee_display,fee_ext,fee_other,fee_all,fee_for_others,fee_by_others)  
    1075. 
             values(  
    1076. 
             user_fee_info_nextval,base_fee.user_id,  
    1077. 
             decode(sign(base_fee.fee_base-user_fee_info_temp.fee_base),1,base_fee.fee_base-user_fee_info_temp.fee_base,0.00),  
    1078. 
             decode(sign(base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month),1,base_fee.fee_pkg_month-user_fee_info_temp.fee_pkg_month,0.00),  
    1079. 
             decode(sign(base_fee.fee_keep-user_fee_info_temp.fee_keep),1,base_fee.fee_keep-user_fee_info_temp.fee_keep,0.00),   
    1080. 
             decode(sign(base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month),1,base_fee.fee_oth_month-user_fee_info_temp.fee_oth_month,0.00),    
    1081. 
             decode(sign(base_fee.fee_vpn-user_fee_info_temp.fee_vpn),1,base_fee.fee_vpn-user_fee_info_temp.fee_vpn,0.00),     
    1082. 
             decode(sign(base_fee.fee_shift-user_fee_info_temp.fee_shift),1,base_fee.fee_shift-user_fee_info_temp.fee_shift,0.00),      
    1083. 
             decode(sign(base_fee.fee_local-user_fee_info_temp.fee_local),1,base_fee.fee_local-user_fee_info_temp.fee_local,0.00),      
    1084. 
             decode(sign(base_fee.fee_long-user_fee_info_temp.fee_long),1,base_fee.fee_long-user_fee_info_temp.fee_long,0.00),      
    1085. 
             decode(sign(base_fee.fee_inprov-user_fee_info_temp.fee_inprov),1,base_fee.fee_inprov-user_fee_info_temp.fee_inprov,0.00),      
    1086. 
             decode(sign(base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long),1,base_fee.fee_inprov_long-user_fee_info_temp.fee_inprov_long,0.00),      
    1087. 
             decode(sign(base_fee.fee_outprov-user_fee_info_temp.fee_outprov),1,base_fee.fee_outprov-user_fee_info_temp.fee_outprov,0.00),      
    1088. 
             decode(sign(base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long),1,base_fee.fee_outprov_long-user_fee_info_temp.fee_outprov_long,0.00),      
    1089. 
             decode(sign(base_fee.fee_inter-user_fee_info_temp.fee_inter),1,base_fee.fee_inter-user_fee_info_temp.fee_inter,0.00),      
    1090. 
             decode(sign(base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long),1,base_fee.fee_inter_long-user_fee_info_temp.fee_inter_long,0.00),       
    1091. 
             decode(sign(base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet),1,base_fee.fee_cmnet-user_fee_info_temp.fee_cmnet,0.00),        
    1092. 
             decode(sign(base_fee.fee_trust-user_fee_info_temp.fee_trust),1,base_fee.fee_trust-user_fee_info_temp.fee_trust,0.00),         
    1093. 
             decode(sign(base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms),1,base_fee.fee_ptp_sms-user_fee_info_temp.fee_ptp_sms,0.00),          
    1094. 
             decode(sign(base_fee.fee_mms-user_fee_info_temp.fee_mms),1,base_fee.fee_mms-user_fee_info_temp.fee_mms,0.00),           
    1095. 
             decode(sign(base_fee.fee_magazine-user_fee_info_temp.fee_magazine),1,base_fee.fee_magazine-user_fee_info_temp.fee_magazine,0.00),            
    1096. 
             decode(sign(base_fee.fee_gprs-user_fee_info_temp.fee_gprs),1,base_fee.fee_gprs-user_fee_info_temp.fee_gprs,0.00),            
    1097. 
             decode(sign(base_fee.fee_wap-user_fee_info_temp.fee_wap),1,base_fee.fee_wap-user_fee_info_temp.fee_wap,0.00),             
    1098. 
             decode(sign(base_fee.fee_data_month-user_fee_info_temp.fee_data_month),1,base_fee.fee_data_month-user_fee_info_temp.fee_data_month,0.00),              
    1099. 
             decode(sign(base_fee.fee_data-user_fee_info_temp.fee_data),1,base_fee.fee_data-user_fee_info_temp.fee_data,0.00),                
    1100. 
             decode(sign(base_fee.fee_ring-user_fee_info_temp.fee_ring),1,base_fee  
    收起
    展开全文
    oracle

  • mysql存储过程之实战篇

     2010-06-24 14:37:58
    通过对mysql存储过程基本语法，控制语句，异常处理，光标等的学习，下面是在实际项目中，写了两个mysql的存储过程   代码1： /*====================================================================*/ /* ... 
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        MySQL   
                                        Mobile   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    从0开始学存储过程（二）---项目实战篇
    
                                    
                                2019-12-28 18:05:05
                            
    
                            
    
                                
    
                                    说明：这里主要讲解在项目中如何运用存储过程，我这里使用的工具是IDEA，项目搭建使用Springboot+MySQL+SSM，测试工具使用postman 一.目录结构  项目是怎么搭建的不是这里的重点，所以不打算讲。 二、数据库表...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    MySQL存储过程之游标实战
    
                                    
                                2019-09-19 06:21:14
                            
    
                            
    
                                
    
                                    MySQL存储过程之游标实战  ​ 博主日前在解决一个项目需求时，没有什么好的方法，于是就来学习存储过程了，之前也是接触过，奈何年少贪玩，竟是全部又还给了大学老师～苦不堪言呐～。  ​ 先说一下业务需求吧，即...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    mysql 存储过程编写中踩的坑-----工作项目实战二
    
                                    
                                2019-12-20 11:33:17
                            
    
                            
    
                                
    
                                    如要转载请注明：... 创作不易 且行且珍惜  存储过程 while循环 和 游标  第一次 用while写的割接商品数据   CREATE DEFINER=`wlwapp`@`%` PROCEDURE `pro_offer_cutover`()  SQL SECURITY INV...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        mysql   
                                        循环   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    mysql 存储过程编写中踩的坑-----工作项目实战一
    
                                    
                                2019-12-20 11:10:11
                            
    
                            
    
                                
    
                                    1、项目前提  由于计费系统先上线，CRM项目后上线。前期计费系统有的建立客户账户，商品以及商品的订购等功能，后期要计费系统这些功能要被CRM系统取代，由CRM创建客户账户和商品，由于两套系统的项目的表模型结构不...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        MySQL   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    Oracle存储过程实战案例分享。
    
                                    
                                2015-12-13 21:36:52
                            
    
                            
    
                                
    
                                    学习了Oracle的plsql及存储过程、方法。变成的基础是一方面，要有进一步的提高还是需要一些实战的案例的。如果你所在的公司没有这方面的工作分配给你，或者你们公司根本没有这方面的业务（我们公司就是没这方面的...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        oracle   
                                        plsql   
                                        方法   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    MySQL数据库应用实战教程 第5章教案 MySQL函数和存储过程.docx
    
                                    
                                2020-03-17 13:42:28
                            
    
                            
    
                                
    
                                    PAGE PAGE 1 第5章MySQL函数和存储过程 课程名称 MySQL数据库应用实战教程慕课版 项目名称 MySQL函数和存储过程 任务名称 MySQL函数和存储过程 课时 4 项目性质 演示性 验证性 设计性 综合性 授课班级 授课日期 授课...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    通用存储过程(通用的分页存储过程)
    
                                    
                                2010-05-14 21:20:52
                            
    
                            
    
                                
    
                                    通用存储过程(通用的分页存储过程) 在实际项目开发中，存储过程是非常常见的，因此掌握存储过程的用法是开发人员必备的实战法宝
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    delphi query 存储为dbf_Delphi 代码审计项目实战 1
    
                                    
                                2020-12-17 11:08:35
                            
    
                            
    
                                
    
                                    程序的主要function，与 procedure注：Delphi把操作数据的方法分成了两种，一种是function，另一种是procedure，大致理解为“函数”和“过程”。Procedure类似C语言中的无返回值函数，即VOID。而Function就是c语言中...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    项目视频讲解_基于Weblogic、Oracle实战开发企业级CRM客户关系管理系统(Jquery、存储过程)新...
    
                                    
                                2013-12-08 14:12:00
                            
    
                            
    
                                
    
                                    基于Weblogic、Oracle实战开发企业级CRM客户关系管理系统(Jquery、存储过程)新    视频地址：http://pan.baidu.com/s/1jBMzY
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    Oracle 11G 入门到项目实战
    
                                    
                                2020-04-09 22:08:20
                            
    
                            
    
                                
    
                                    Oracle 11G 课程完整的课程体系，从安装到入门到项目实战开发，整个学习过程，都以实践操作为主，大量的案例，实例，作业，来保证学习，练习，直到具备数据分析师，掌握SQL部分，能达到数据库开发工程师的能力要求与...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        oracle   
                                        oracle数据库   
                                        oracle安装   
                                        oracle下载   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    项目视频讲解_基于Weblogic、Oracle实战开发企业级CRM客户关系管理系统(Jquery、存储过程)新
    
                                    
                                    
                                            千次阅读
                                    
                                2013-12-08 14:12:16
                            
    
                            
    
                                
    
                                    基于Weblogic、Oracle实战开发企业级CRM客户关系管理系统(Jquery、存储过程)新         视频地址： http://pan.baidu.com/s/1jBMzY   
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        weblogic   
                                        oracle   
                                        管理   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    Python网络爬虫开发与项目实战
    
                                    
                                2020-05-06 20:18:35
                            
    
                            
    
                                
    
                                    百度百科词条项目实战过程中，详细讲解了网络爬虫开发的过程，包括： 百度百科-爬虫程序结构设计 模块导入 当前页面的爬取 解析器提取有效数据 解析器提取href属性 解析相关页面管理URL 数据存储
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        python工程师   
                                        python爬虫   
                                        python   
                                        python教程   
                                        python安装   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    hadoopdataplatform_# Hadoop离线数据分析平台实战——230项目数据存储结构设计
    
                                    
                                2020-12-19 12:19:32
                            
    
                            
    
                                
    
                                    Hadoop离线数据分析平台实战——230项目数据存储结构设计数据存储设计在本次项目中设计到数据存储的有三个地方：第一个就是将原始的日志数据按天保存到hdfs文件系统中；第二个就是将etl解析后的数据保存到hbase中；...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    软件测试之MySQL数据库项目实战
    
                                    
                                2020-08-31 17:09:42
                            
    
                            
    
                                
    
                                    涵盖MySQL数据库基础，如MySQL的安装和基本管理、各种管理工具的使用、数据表结构维护以及各种约束的使用、表记录的增、删、改、查操作，还有视图、索引、触发器、事件、自定义函数、存储过程等各类数据库对象的学习...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        软件测试计划   
                                        软件测试面试自我介绍   
                                        软件测试方案   
                                        软件测试笔试题   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    全面解剖RocketMQ和项目实战
    
                                    
                                    
                                            千人学习
                                    
                                2019-09-05 14:53:53
                            
    
                            
    
                                
    
                                    事务消息 章节二：项目实战 1.    项目背景介绍 (1)    电商高可用MQ实战 2.    功能分析 (1)    下单功能 (2)   ...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        java   
                                        java面试题   
                                        java面试   
                                        java学习路线   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    尹成Python27天入门到项目实战
    
                                    
                                2020-12-05 17:04:22
                            
    
                            
    
                                
    
                                    尹成Python27天从入门到实战项目！全课665节讲解详细易操作，助力实战项目能力成长，轻松、简单、易懂！ day1  python入门与学习方法精确思维与用到才能记忆深刻课程简介课前介绍计算机简介与硬盘概念内存作用...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        python工程师   
                                        python爬虫   
                                        python   
                                        python教程   
                                        python安装   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    大数据企业级项目实战--Titan大型数据运营系统项目课程
    
                                    
                                2020-06-01 15:22:06
                            
    
                            
    
                                
    
                                     由于本课程不光讲解项目的实现，还会在实现过程中反复揉和各种技术细节，各种设计思想，各种最佳实践思维，学完本项目并勤于实践的话，学员的收获将远远超越一个项目的具体实现，更能对大型数据系统开发产生深刻...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        大数据   
                                        大数据技术   
                                        大数据平台   
                                        大数据技术原理与应用   
                                        大数据学习路线   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    lucene项目实战总结
    
                                    
                                2018-03-08 23:00:04
                            
    
                            
    
                                
    
                                    lucene:简单点说就是扫描文章，将每一句话拆分成词语，并用这些词建立一个索引，因为各个国家语言不同，词语拆分的方法也不同，所以分词器也是不同的,将这些索引存储在特殊的文件中，当用户查询时根据建立的索引查找...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        lucene   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    Java 爬虫项目实战之爬虫简介
    
                                    
                                    
                                            千次阅读
                                    
                                2018-11-24 21:23:50
                            
    
                            
    
                                
    
                                    Java 爬虫项目实战之爬虫简介 0. 前言 今年三四月份学习Hbase，了解到openTSDB的底层存储使用到了Hbase，于是乎，学习openTSDB，在阅读openTSDB源码【其源码使用java编写】的过程中， 发现里面全是I/O，多线程，...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    JDBC中调用存储过程
    
                                    
                                2017-03-01 13:47:43
                            
    
                            
    
                                
    
                                    随时随地阅读更多技术实战干货，获取项目源码、学习资料，请关注源代码社区公众号(ydmsq666)、博主微信（guyun297890152）、QQ技术交流群(183198395)。    存储过程是一SQL语句和可选控制流语句的预编译集合。编译...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        jsp   
                                        数据库   
                                        java   
                                        jdbc   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    SpringBoot项目实战(0)-准备
    
                                    
                                2019-07-07 10:41:51
                            
    
                            
    
                                
    
                                    准备开始一个新的坑，完整的搭建一个SpringBoot的项目。从头开始一步一步的记录下来，时间可能有点长，但我会尽可能的保证细节到位，不会挖坑不填的（@盗墓笔记）。  项目分析 ...项目具体搭建过程  ...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        SpringBoot   
                                
    

                        
    • 
                        
  • 
                            
    
                                
                                    
    医疗知识图谱项目实战资料
    
                                    
                                2021-02-05 16:41:09
                            
    
                            
    
                                
    
                                    · ngram +fp_growth 从病例依据生成病的常见模式(可存储为图谱)  本体+关系：病名、症状、药物  过程：NER，核心语句提取，    · 备选药品图谱回扫病例，推荐出病例症状不冲突的药品最终给出一个病历得出三个产出...
                                
    
                                
    
                                    
    
                                        
    
                                    
    
                                    
    收起 
    
                                
    
                            
    
                                
    
                                    
                                        自然语言处理   
                                
    

                        
    • 
                

            


            

                

                

                    
                    
空空如也

                

            


            

                

                

                    
                    
空空如也

                

            


            

                


 

 
 
         

            
                     


                    
                        1 
                    
                    
                        2 
                    
                    
                        3 
                    
                    
                        4 
                    
                    
                        5 
                    
                        ... 
                    
                        20 
                    

                    
            
        

   

                

                

            


        

    

    

        

            

                收藏数
                    478
            

            

                精华内容
                    191
            

        

        

            
热门标签

            
        


        


        

            关键字：
存储过程项目实战