-- create a temporary table to hold page IDs for results
create temporary table if NOT EXISTS u2204__testing.pageid ( id int(11) PRIMARY KEY );
-- enable recursion
set MAX_SP_RECURSION_DEPTH = 255;
-- create a function to do the work
DROP PROCEDURE IF EXISTS u2204__testing.categoryCount;
DELIMITER ';;'
CREATE PROCEDURE u2204__testing.categoryCount(in categoryname varchar(255)) BEGIN
-- define some variables I'm going to use
declare pid int;
declare pns int;
declare ptitle VARCHAR(255);
declare done int default false;
-- declare a cursor (think a SQL statement that gets executed into a set of results, which you can then loop over)
declare cursor_i cursor for select page_id, page_namespace, page_title from enwiki_p.categorylinks
inner join enwiki_p.page on page_id = cl_from
where cl_to = categoryname;
declare continue HANDLER for not found set done = true;
-- OK, we're ready to start. Open the cursor to execute the query
open cursor_i;
-- loop over the results in the cursor
read_loop: LOOP
-- get the current row and put the results into the three variables
FETCH cursor_i INTO pid, pns, ptitle;
-- done is set above when there's no more results, so quit the loop
if done THEN
LEAVE read_loop;
END IF;
-- check the page namespace is 14, and if it is, call this procedure again with the new category title
if pns = 14 THEN
call u2204__testing.categoryCount(ptitle);
END IF;
-- insert this page's ID into the results table (doing a dummy operation if the key already exists)
insert into u2204__testing.pageid values (pid) on DUPLICATE KEY UPDATE id = id;
END LOOP;
-- we're done with the cursor, so close it
close cursor_i;
-- end of the stored procedure declaration
END;;
DELIMITER ';'
-- call the stored procedure we just defined
CALL u2204__testing.categoryCount('Philosophy');
-- simple count from the results.
select count(*) from u2204__testing.pageid;