Oracle数据库通过定义TYPE及Member对象来实现日志信息的分级管理


调用路径：

存储过程-》调用函数初始化TYPE里变量-》存储过程调用具体级别的日志操作-》调用TYPES实现体TYPE BOBIES里的具体Member实现体-》调用公用的Member成员存储过程写入syslog表。

代码样例展示：

1、存储过程

  1. createorreplaceprocedure sp_message_hisorderdata_create(
  2. p_l_date HsCrmType.LDate%TYPE,
  3. p_vc_customer hscrmtype.vc50%type) IS
  4. v_l_date HsCrmType.LDate%Type;
  5. v_l_currentdate HsCrmType.LDate%Type;
  6. v_vc_customer hscrmtype.vc50%type;
  7. v_l_count HsCrmType.LInt%Type;
  8. v_l_total HsCrmType.LInt%Type;
  9. v_vc_open_value HsCrmType.VC255%Type;
  10. v_vc_open_value_temp HsCrmType.VC255%Type;
  11. v_vc_customer_no HsCrmType.VC20%Type;
  12. vc_product_no HsCrmType.CKind%Type;
  13. vc_product_temp HsCrmType.CKind%Type;
  14. v_vc_prefix HsCrmType.CKind%Type;
  15. v_vc_temp1 HsCrmType.VC255%Type;
  16. v_vc_temp2 HsCrmType.VC255%Type;
  17. v_vc_temp3 HsCrmType.VC255%Type;
  18. v_vc_temp4 HsCrmType.VC255%TYPE;
  19. V_L_SPEAK_NO HsCrmType.LInt%Type;
  20. l_tyLog ty_logManager := ty_logManager('system','003');
  21. begin
  22. l_tyLog.up_Enter('sp_message_hisorderdata_create开始');
  23. v_vc_temp1 := chr(1)||' '||chr(1)||' '||chr(1)||' '||chr(1)||' ';
  24. v_vc_temp2 := chr(1)||' '||chr(1)||' '||chr(1)||' ';
  25. v_vc_temp3 := chr(1)||' '||chr(1)||' ';
  26. v_vc_temp4 := chr(1)||' ';
  27. --zhoudy 2013-01-30 添加初始化赋值
  28. v_vc_open_value := '';
  29. v_l_total := 0;
  30. V_L_SPEAK_NO := 0;
  31. v_l_date := nvl(p_l_date,0);
  32. v_vc_customer := nvl(p_vc_customer,' ');
  33. if v_l_date = 0 then
  34. v_l_date := to_number(to_char(SYSDATE-1,'YYYYMMDD'));
  35. end if;
  36. FOR r IN (SELECT a.vc_customer_no
  37. ,a.l_product_no
  38. ,'3'AS c_sourcetype
  39. ,a.l_de_begin_date AS l_de_begin_date
  40. ,a.l_de_end_date AS l_de_end_date
  41. ,(SELECT WMSYS.WM_CONCAT(b.vc_open_value) FROM hscrm_dbo.orderinfo b
  42. WHERE b.vc_customer_no=a.vc_customer_no
  43. AND b.l_product_no=a.l_product_no
  44. and b.l_product_no < 20000) AS vc_open_value
  45. ,'1'AS c_processtype
  46. FROM hscrm_dbo.speakforrelation a
  47. WHERE--a.l_create_date <= v_l_date
  48. --a.l_create_date <= 20101026
  49. --and
  50. a.l_product_no < 20000
  51. --and a.l_create_date <= 20101024
  52. --and (a.vc_customer_no = v_vc_customer or v_vc_customer = ' ')
  53. --and a.l_product_no=11003
  54. GROUPBY vc_customer_no,l_product_no,l_de_begin_date,l_de_end_date
  55. )
  56. LOOP
  57. v_vc_prefix := substr(r.vc_customer_no,1,3);
  58. if v_vc_prefix = 'CRM'then
  59. v_vc_customer_no := substr(r.vc_customer_no,4);
  60. v_vc_open_value_temp := r.vc_open_value;
  61. vc_product_temp := substr(to_char(r.l_product_no),1,1);
  62. if vc_product_temp = '1'then
  63. v_l_total := v_l_total + 1;
  64. vc_product_no := substr(to_char(r.l_product_no),2);
  65. SELECTCOUNT(*) INTO v_l_count FROM hscrm_dbo.smsserviceparam WHERE vc_smsno = to_char(r.l_product_no);
  66. CASE
  67. WHEN v_l_count = 0 THEN
  68. v_vc_open_value := '';
  69. WHEN v_l_count = 1 THEN
  70. v_vc_open_value := REPLACE(r.vc_open_value,v_vc_temp1);
  71. WHEN v_l_count = 2 THEN
  72. v_vc_open_value := REPLACE(r.vc_open_value,v_vc_temp2);
  73. WHEN v_l_count = 3 THEN
  74. v_vc_open_value := REPLACE(r.vc_open_value,v_vc_temp3);
  75. WHEN v_l_count = 4 THEN
  76. v_vc_open_value := REPLACE(r.vc_open_value,v_vc_temp4);
  77. --zhoudy 20130131
  78. ELSE
  79. v_vc_open_value := REPLACE(r.vc_open_value,v_vc_temp4);
  80. ENDCASE;
  81. v_vc_open_value := REGEXP_REPLACE(v_vc_open_value,' ');
  82. v_vc_open_value := REPLACE(v_vc_open_value,',',chr(1));
  83. --V_L_SPEAK_NO := r.L_SPEAK_NO;
  84. INSERTINTO THJZX_DZGX_IN_HIS2(KHH,CPBH,DZLY,DZSJ,DQSJ,DZLX,CS,CJRQ)
  85. VALUES(v_vc_customer_no
  86. ,vc_product_no
  87. ,r.c_sourcetype
  88. ,r.l_de_begin_date
  89. ,r.l_de_end_date
  90. ,r.c_processtype
  91. ,v_vc_open_value
  92. ,v_l_date);
  93. end if;
  94. end if;
  95. COMMIT;
  96. END LOOP;
  97. commit;
  98. l_tyLog.up_Leave('sp_message_hisorderdata_create处理结束,成功生成数'||v_l_total);
  99. exception
  100. when others then
  101. rollback;
  102. l_tyLog.up_Error('sp_message_orderdata_create error:'||v_vc_customer_no||':'||V_L_SPEAK_NO||':'||v_l_date||':'||vc_product_no||':'|| v_vc_open_value||':'||SQLCODE);
  103. end sp_message_hisorderdata_create;

片段解析：

初始化log的定义，其实现在type的内部；

  1. l_tyLog ty_logManager := ty_logManager('system','003');

调用log具体子过程

  1. l_tyLog.up_Error('sp_message_orderdata_create error:'||v_vc_customer_no||':'||V_L_SPEAK_NO||':'||v_l_date||':'||vc_product_no||':'|| v_vc_open_value||':'||SQLCODE);
  1. l_tyLog.up_Leave('sp_message_hisorderdata_create处理结束,成功生成数'||v_l_total);
  • 1
  • 2
  • 3
  • 4
  • 下一页

相关内容