I had a stored procedure that defined a cursor using a crazy query, and then a LOOP that did a FETCH and went row-by-row and performed updates and inserts to 3 or 4 different tables. I was having some trouble completing the very last thing the procedure needed to do. So I figured I’d take a little ‘learning break’ and do s’more research on what I was trying to accomplish.
I came across Roland Bouman’s blog, and read quite a few of the articles there. He’s a good presenter of his knowledge, and would appear to be incredibly knowledgeable about all things SQL-related, and something in those articles got me thinking that what I was trying to do may not be the greatest use of the DB server’s resources.
I had a cursor that fetched something like 350 rows for my loop to process. On average, probably fewer than 5 of those 350 rows would point to changes the procedure would need to make (either inserts or updates). Further, the cursor query depended on three views I had created, in part to work around shortcomings in an application’s database design, and in part to map external data to id’s defined in the application. This was quickly turning into a steaming heap of rotten garbage that I was going to have to provide for the care and feeding of. I had entered the No Bueno Zoneâ„¢
I got s’more ideas from Roland’s blog, thought on this for the weekend, futzed around with some stuff, fell down a few times, and then, first thing this morning, I came in, sat down, stroked my chin for about 5 minutes, did some typing, and then sat back. I inspected the code. I looked at the output of a few other small queries. I tested the code, and immediately put my arms in the air signaling the touchdown that had just been achieved.
Here’s the final task I needed to accomplish in the stored procedure. Thanks again to Roland for sharing his knowledge on his blog. It was very useful.
UPDATE groups_members AS grpmem,
(
SELECT
u.id AS uid,
g.id AS newgid,
m.groupid AS oldgid
FROM
bb_data_map AS b
JOIN user AS u ON u.username = b.netid
JOIN course AS c ON c.idnumber = b.coursename
JOIN groups AS g ON g.name = b.precept AND g.courseid = c.id
LEFT JOIN
(
SELECT
gm.userid AS userid,
gm.groupid AS groupid,
g.name AS grpname,
c.idnumber AS coursename
FROM
groups_members AS gm,
groups AS g,
course AS c
WHERE g.id = gm.groupid
AND c.id = g.courseid
) AS m ON m.userid = u.id AND m.coursename = b.coursename
WHERE b.precept IS NOT NULL
AND c.id IS NOT NULL
AND m.groupid != g.id
) AS newgrp
SET grpmem.groupid = newgrp.newgid
WHERE (grpmem.userid = newgrp.uid AND grpmem.groupid = newgrp.oldgid)
Technorati Tags: sql, stored+procedure, syntax, mysql, database, cursor+loop,
Social Bookmarks:
