299QeQnk

1SELECT SUM
2( 
3    ROUND
4    ( 
5        (LENGTH(pageText) - LENGTH (REPLACE (pageText, "Word", "")))
6        /LENGTH("Word")
7    )
8) FROM pages WHERE bookID = id;
9	
10DELIMITER $$
11
12DROP PROCEDURE IF EXISTS `my_proc` $$
13CREATE PROCEDURE `my_proc`(arg1 INT) -- 1 input argument; you might need more or fewer
14BEGIN
15
16-- declare the program variables where we'll hold the values we're sending into the procedure;
17-- declare as many of them as there are input arguments to the second procedure,
18-- with appropriate data types.
19
20DECLARE val1 INT DEFAULT NULL;
21DECLARE val2 INT DEFAULT NULL;
22
23-- we need a boolean variable to tell us when the cursor is out of data
24
25DECLARE done TINYINT DEFAULT FALSE;
26
27-- declare a cursor to select the desired columns from the desired source table1
28-- the input argument (which you might or might not need) is used in this example for row selection
29
30DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
31 CURSOR FOR
32 SELECT t1.c1, 
33        t1.c2
34   FROM table1 t1
35  WHERE c3 = arg1; 
36
37-- this fancy spacing is of course not required; all of this could go on the same line.
38
39-- a cursor that runs out of data throws an exception; we need to catch this.
40-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
41-- and since this is a CONTINUE handler, execution continues with the next statement.   
42
43DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
44
45-- open the cursor
46
47OPEN cursor1;
48
49my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
50LOOP
51
52  -- read the values from the next row that is available in the cursor
53
54  FETCH NEXT FROM cursor1 INTO val1, val2;
55
56  IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
57    LEAVE my_loop; 
58  ELSE -- val1 and val2 will be the next values from c1 and c2 in table t1, 
59       -- so now we call the procedure with them for this "row"
60    CALL the_other_procedure(val1,val2);
61    -- maybe do more stuff here
62  END IF;
63END LOOP;
64
65-- execution continues here when LEAVE my_loop is encountered;
66-- you might have more things you want to do here
67
68END $$
69
70DELIMITER ;