Oracle databases implement hierarchical management of log information by defining TYPE and Member objects

Call path:

Stored Procedure-call function initialization variables in TYPE-stored procedure call specific log operations-call TYPES to implement specific Member implementations in body type bobies-call common Member the member stored procedure is written to the syslog table.

Sample Code:

1. Stored Procedure

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. Rochelle 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 add initialization assignment
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
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. Rochelle tylog.up_leave ('SP _ message_hisorderdata_create processing is complete, number of successfully generated '| 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;

Fragment parsing:

Initialize the definition of log, in fact, the internal type;

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

Specific sub-process of calling 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. Rochelle tylog.up_leave ('SP _ message_hisorderdata_create processing is complete, number of successfully generated '| v_l_total );

1 2 3 4 Next Page