Vreemd probleem met stored function
De volgende functie werkt:
Code (php)
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
DROP FUNCTION `get_wage`//
CREATE FUNCTION `get_wage`(
mPid INT,
score_count INT,
work_date DATE,
score_set VARCHAR(31),
personid INT(11)
)
RETURNS decimal(6,2)
READS SQL DATA
DETERMINISTIC
get_wage_body: BEGIN
DECLARE prev_start_value, start_value INT(11);
DECLARE prev_fee, fee DECIMAL(6, 2);
DECLARE prev_repeat_every, repeat_every TINYINT(4);
DECLARE bonus_value DECIMAL(6, 2);
DECLARE bonus_total DECIMAL(6, 2) DEFAULT 0.00;
DECLARE EOF BOOL DEFAULT FALSE;
DECLARE bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE extra_bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mExtraBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET EOF := TRUE;
DECLARE EXIT HANDLER
FOR SQLEXCEPTION
sql_exception: BEGIN
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
END;
OPEN bonus_cursor;
OPEN extra_bonus_cursor;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
SET EOF := FALSE;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
extra_bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE extra_bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
RETURN bonus_total;
END//
CREATE FUNCTION `get_wage`(
mPid INT,
score_count INT,
work_date DATE,
score_set VARCHAR(31),
personid INT(11)
)
RETURNS decimal(6,2)
READS SQL DATA
DETERMINISTIC
get_wage_body: BEGIN
DECLARE prev_start_value, start_value INT(11);
DECLARE prev_fee, fee DECIMAL(6, 2);
DECLARE prev_repeat_every, repeat_every TINYINT(4);
DECLARE bonus_value DECIMAL(6, 2);
DECLARE bonus_total DECIMAL(6, 2) DEFAULT 0.00;
DECLARE EOF BOOL DEFAULT FALSE;
DECLARE bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE extra_bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mExtraBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET EOF := TRUE;
DECLARE EXIT HANDLER
FOR SQLEXCEPTION
sql_exception: BEGIN
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
END;
OPEN bonus_cursor;
OPEN extra_bonus_cursor;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
SET EOF := FALSE;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
extra_bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE extra_bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
RETURN bonus_total;
END//
Nu wil ik een query toevoegen en de som van bonus_total en deze waarde teruggeven. Het enige wat ik verander is dat ik een extra variabele declareer om dit in op te slaan, een query draai (nadat alle cursors gesloten zijn) en het totaal teruggeef ipv enkel bonus_total. De functie wordt dan totaal het volgende:
Code (php)
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
DROP FUNCTION `get_wage`//
CREATE FUNCTION `get_wage`(
mPid INT,
score_count INT,
work_date DATE,
score_set VARCHAR(31),
personid INT(11)
)
RETURNS decimal(6,2)
READS SQL DATA
DETERMINISTIC
get_wage_body: BEGIN
DECLARE prev_start_value, start_value INT(11);
DECLARE prev_fee, fee DECIMAL(6, 2);
DECLARE prev_repeat_every, repeat_every TINYINT(4);
DECLARE base_wage DECIMAL(6, 2);
DECLARE bonus_value DECIMAL(6, 2);
DECLARE bonus_total DECIMAL(6, 2) DEFAULT 0.00;
DECLARE EOF BOOL DEFAULT FALSE;
DECLARE bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE extra_bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mExtraBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET EOF := TRUE;
DECLARE EXIT HANDLER
FOR SQLEXCEPTION
sql_exception: BEGIN
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
END;
OPEN bonus_cursor;
OPEN extra_bonus_cursor;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
SET EOF := FALSE;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
extra_bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE extra_bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
SELECT
`mProject`.`stdHours` *
`wages`.`wage`
INTO
base_wage
FROM
`wages`
JOIN
`person`
ON
`wages`.`age` = years(person.birthdate, work_date) AND
`person`.`pid` = personid
JOIN
(
`mWageTable2project`,
`mProject`
)
ON
`wages`.`group_id` = `mWageTable2project`.`fk_mWTid` AND
`mWageTable2project`.`fk_mPid` = `mProject`.`mPid` AND
`mProject`.`mPid` = project_id;
RETURN base_wage + bonus_total;
END//
CREATE FUNCTION `get_wage`(
mPid INT,
score_count INT,
work_date DATE,
score_set VARCHAR(31),
personid INT(11)
)
RETURNS decimal(6,2)
READS SQL DATA
DETERMINISTIC
get_wage_body: BEGIN
DECLARE prev_start_value, start_value INT(11);
DECLARE prev_fee, fee DECIMAL(6, 2);
DECLARE prev_repeat_every, repeat_every TINYINT(4);
DECLARE base_wage DECIMAL(6, 2);
DECLARE bonus_value DECIMAL(6, 2);
DECLARE bonus_total DECIMAL(6, 2) DEFAULT 0.00;
DECLARE EOF BOOL DEFAULT FALSE;
DECLARE bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE extra_bonus_cursor
CURSOR FOR
SELECT
amount,
value,
recursive
FROM
mExtraBonus
WHERE
fk_mPid = mPid AND
startdate <= work_date AND
(
enddate >= work_date OR
enddate = '0000-00-00'
) AND
amount <= score_count AND
setMember = score_set
ORDER BY
amount;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET EOF := TRUE;
DECLARE EXIT HANDLER
FOR SQLEXCEPTION
sql_exception: BEGIN
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
END;
OPEN bonus_cursor;
OPEN extra_bonus_cursor;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
SET EOF := FALSE;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (NOT EOF) THEN
extra_bonus_loop: LOOP
SET prev_start_value = start_value;
SET prev_fee = fee;
SET prev_repeat_every = repeat_every;
FETCH extra_bonus_cursor INTO
start_value,
fee,
repeat_every;
IF (EOF) THEN
IF (prev_repeat_every > 0) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
LEAVE extra_bonus_loop;
END IF;
IF (prev_repeat_every > 0 && score_count >= start_value) THEN
SET bonus_value = CEIL((start_value - prev_start_value) / prev_repeat_every) * prev_fee;
ELSEIF (prev_repeat_every > 0 && score_count < start_value) THEN
SET bonus_value = CEIL((score_count - prev_start_value + 1) / prev_repeat_every) * prev_fee;
ELSE
SET bonus_value = prev_fee;
END IF;
SET bonus_total = bonus_total + bonus_value;
END LOOP;
END IF;
CLOSE bonus_cursor;
CLOSE extra_bonus_cursor;
SELECT
`mProject`.`stdHours` *
`wages`.`wage`
INTO
base_wage
FROM
`wages`
JOIN
`person`
ON
`wages`.`age` = years(person.birthdate, work_date) AND
`person`.`pid` = personid
JOIN
(
`mWageTable2project`,
`mProject`
)
ON
`wages`.`group_id` = `mWageTable2project`.`fk_mWTid` AND
`mWageTable2project`.`fk_mPid` = `mProject`.`mPid` AND
`mProject`.`mPid` = project_id;
RETURN base_wage + bonus_total;
END//
De fout die ik krijg is 'ERROR 1326 (24000): Cursor is not open'. Dit probleem treedt op in MySQL #5.0.27-standard-log en #5.0.42-standard-log.
Wat doe ik hier verkeerd?
bart van der veen schreef op 11.07.2007 20:57:
uw heeft meer als 20 regels code geplaats, dat kunnen we hier niet aan... dit is ontspanning geen werk...
ik ga een aantal topics terug, en wat vindt ik... een topic van jou met 20+ regels
http://www.phphulp.nl/forum/showtopic.php?cat=1&id=29675
Hij vraag alleen of jij hier een regel of 20 wil posten omdat hij geen zin heeft om 300 regels door te lezen. Dan kan je dat ook gewoon doen in plaats van een excuus zoeken?
Freek schreef op 11.07.2007 22:10:
Sneeuwvlok, wat is jouw probleem? Het maakt toch niet uit dat bart in een ánder topic, wat hier niets mee te maken heeft meer dan 20 regels code post.
Hij vraag alleen of jij hier een regel of 20 wil posten omdat hij geen zin heeft om 300 regels door te lezen. Dan kan je dat ook gewoon doen in plaats van een excuus zoeken?
Hij vraag alleen of jij hier een regel of 20 wil posten omdat hij geen zin heeft om 300 regels door te lezen. Dan kan je dat ook gewoon doen in plaats van een excuus zoeken?
Nee, dat kan ik niet, aangezien er bij zulke sql's geen line nummer gegeven worden waar er een error plaatsvindt, Was dat maar zo :{ . Dat is ook wat het debuggen van functies & procedures zo vervelend maakt.
klik
Gewijzigd op 01/01/1970 01:00:00 door Klaasjan Boven
@freek, bedankt...
@sneeuwvlok, verder vindt ik het sterk staaltje (my)sql, ik blijf altijd hangen bij select enz...
edit:
ps heb nu nog hulp nodig hier staat al opgelost:
http://gathering.tweakers.net/forum/list_messages/1231456
edit2:
ik zag net het aantal foutcodes van 4.1 naar 5.0 met 147 verhoogt is...
Gewijzigd op 01/01/1970 01:00:00 door bart van der veen