MySql_Tables_SREQUEST_Function.sql
2.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
select 'CREATE FUNCTION addOneToVisited(...) RETURNS INT';
DROP FUNCTION IF EXISTS addOneToVisited;
DELIMITER '$';
CREATE FUNCTION addOneToVisited(param_RequestProfile int, param_MTextID int, param_LoadLevel varchar(64))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE var_New_Count_General INT;
DECLARE var_New_Count_Login INT;
DECLARE var_MTextID INT;
DECLARE var_LoadLevel varchar(64);
IF (param_MTextID>0) THEN
-- set var_MTextID=(select MTextID from losp_BE_SREQUEST_visited where MTextID=param_MTextID);
select MTextID, LoadLevel into var_MTextID, var_LoadLevel from losp_BE_SREQUEST_visited where MTextID=param_MTextID and LoadLevel=param_LoadLevel;
IF (param_MTextID=var_MTextID and param_LoadLevel=var_LoadLevel) THEN
set var_New_Count_General=(select Count_General from losp_BE_SREQUEST_visited where MTextID=param_MTextID and LoadLevel=param_LoadLevel)+1;
IF (param_RequestProfile>0) THEN
set var_New_Count_Login=(select Count_Login from losp_BE_SREQUEST_visited where MTextID=param_MTextID and LoadLevel=param_LoadLevel)+1;
UPDATE losp_BE_SREQUEST_visited SET Count_General=var_New_Count_General , Count_Login=var_New_Count_Login, LoadLevel=param_LoadLevel WHERE MTextID=param_MTextID and LoadLevel=param_LoadLevel LIMIT 1;
ELSE
UPDATE losp_BE_SREQUEST_visited SET Count_General=var_New_Count_General, LoadLevel=param_LoadLevel WHERE MTextID=param_MTextID and LoadLevel=param_LoadLevel LIMIT 1;
END IF;
ELSE
IF (param_RequestProfile>0) THEN
INSERT INTO losp_BE_SREQUEST_visited (MTextID, Count_General, Count_Login, LoadLevel) VALUES (param_MTextID, 1, 1, param_LoadLevel);
ELSE
INSERT INTO losp_BE_SREQUEST_visited (MTextID, Count_General, Count_Login, LoadLevel) VALUES (param_MTextID, 1, 0, param_LoadLevel);
END IF;
END IF;
return var_New_Count_General;
ELSE
return 0;
END IF;
END;
$
DELIMITER ;
select 'CREATE FUNCTION insertToHistory(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertToHistory;
DELIMITER '$';
CREATE FUNCTION insertToHistory(param_ProfileID int, param_MTextID int, param_Action varchar(64), param_Url varchar(2048))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE var_index INT;
set var_index=(select max(NumberOfAction) as NumberOfAction from losp_BE_SREQUEST_history where ProfileID=param_ProfileID AND MTextID=param_MTextID AND Action=param_Action)+1;
IF (var_index is null) THEN
set var_index=1;
END IF;
INSERT INTO losp_BE_SREQUEST_history (ProfileID, MTextID, Action, NumberOfAction, Url) VALUES (param_ProfileID, param_MTextID, param_Action, var_index, param_Url);
return var_index;
END;
$
DELIMITER ;