-- 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;