nn2dfsCX

1CREATE TABLE `SP_LOG` (
2    `ID` bigint(20) NOT NULL AUTO_INCREMENT,
3    `SP_NAME` varchar(255) NOT NULL,
4    `IN_PARAMS` varchar(4000) DEFAULT NULL,
5    `START_TIME` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
6    `END_TIME` timestamp(6) NULL DEFAULT NULL,
7    PRIMARY KEY (`ID`),
8    KEY `IDX.SP_LOG.SP_NAME` (`SP_NAME`)
9);
10
11CREATE TABLE `SP_LOG_DETAIL` (
12    `ID` bigint(20) NOT NULL AUTO_INCREMENT,
13    `SP_ID` bigint(20) NOT NULL,
14    `MESSAGE` varchar(255) NOT NULL,
15    `START_TIME` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
16    `END_TIME` timestamp(6) NULL DEFAULT NULL,
17    PRIMARY KEY (`ID`),
18    KEY `IDX.SP_LOG_DETAIL.SP_ID` (`SP_ID`),
19    CONSTRAINT `FK.SP_LOG_DETAIL.SP_ID` FOREIGN KEY (`SP_ID`) 
20    REFERENCES `SP_LOG` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
21);
22
23DROP PROCEDURE IF EXISTS `SP_PROCESS_USERS`;
24DELIMITER //
25CREATE PROCEDURE `SP_PROCESS_USERS`(IN spLocation VARCHAR(60))
26BEGIN    
27
28    /*
29     * -- Handle Exception --
30     */
31
32    DECLARE spLogId BIGINT(20);
33    DECLARE spLogDetailId BIGINT(20);
34    DECLARE EXIT HANDLER FOR SQLEXCEPTION
35    BEGIN
36        GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
37
38        INSERT INTO SP_LOG(SP_NAME, IN_PARAMS, END_TIME) VALUES ('SP_PROCESS_USERS', spMarket, NOW(6));
39        SELECT LAST_INSERT_ID() INTO spLogId;
40
41        INSERT INTO SP_LOG_DETAIL(SP_ID, MESSAGE, END_TIME) VALUES (spLogId, CONCAT('SQLEXCEPTION==>', @p1, ':', @p2), NOW(6));
42    END;
43
44    INSERT INTO SP_LOG(SP_NAME, IN_PARAMS) VALUES ('SP_PROCESS_USERS', spLocation);
45    SELECT LAST_INSERT_ID() INTO spLogId;
46
47    /*
48     * -- Process Users Part 1 --
49     */
50
51    INSERT INTO SP_LOG_DETAIL(SP_ID, MESSAGE) VALUES (spLogId, 'Process Users #1');
52    SELECT LAST_INSERT_ID() INTO spLogDetailId;
53
54    ... Logic ...
55
56    UPDATE SP_LOG_DETAIL SET END_TIME = NOW(6) WHERE ID = spLogDetailId;
57
58
59    ... Part 2, 3, ... n
60
61    UPDATE SQ_5G_SP_LOG SET END_TIME = NOW(6) WHERE ID = spLogId;
62
63END //