WL#3309: Stored Procedures: FOR statement
Affects: Server-7.1 — Status: Un-Assigned
Let's support standard FOR ... END FOR loops. For example: CREATE PROCEDURE p () BEGIN DECLARE counter INT DEFAULT 0; FOR SELECT a, b FROM t DO SET counter = counter + 1; END FOR; SELECT 'There are ',counter,' rows in t'; END //
MySQL supports LOOP, WHILE, and REPEAT, but not FOR. In the manual, section "I.1. Restrictions on Stored Routines and Triggers", there is a mention that "For loops are not supported". Per-Erik Martin and Peter Gulutzan discussed it in April 2003 (subject line = "The FOR Statement") and during the Bordeaux conference. Per-Erik felt it would be difficult, so FOR became the only standard loop that MySQL doesn't support. It's always been assumed that we should get around to it eventually. Syntax ------ We'll support what the SQL:2008 SQL/PSM document says: "
::= [ ] FOR [ AS ] [ [ ] CURSOR FOR ] DO END FOR [ ] ::= " The beginning label and ending label are nothing new, we already support them for LOOP, REPEAT, and WHILE. A "cursor specification" is just " [ ] [ ]". That is, it's a SELECT. For reasons that we will discuss below, the "[ AS ]" and "[ [ ] CURSOR FOR ]" are not useful for MySQL due to other limitations. Therefore the syntax that we actually need to support is: " FOR select_statement DO END FOR " The SQL statement list is what you want to do for each "fetched" row that the SELECT statement would cause. Example: FOR SELECT a, b FROM t DO SET counter = counter + 1; END FOR; Equivalence ----------- A FOR loop is equivalent to a WHILE loop with these conversions and implied statements: - The FOR's "cursor" is a DECLAREd cursor for a new BEGIN/END block - The names in the FOR's SELECT list (or the FOR's cursor's SELECT list) are DECLAREd variables for the BEGIN/END block - Each iteration of the FOR is an implied FETCH - The test for the end of iterations is an implied DECLARE EXIT HANDLER which breaks out of the loop Therefore this statement: FOR SELECT a, b FROM t DO SET counter = counter + 1; END Is equivalent to these statements: BEGIN DECLARE i_CN CURSOR FOR SELECT a, b FROM t; OPEN i_CN; BEGIN DECLARE a INT; /* Assuming column a is an INT */ DECLARE b INT; /* Assuming column b is an INT */ DECLARE EXIT HANDLER FOR NOT FOUND BEGIN CLOSE i_CN; END; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN CLOSE i_CN; RESIGNAL; END; LOOP FETCH i_CN INTO a, b; SET counter = counter + 1; END LOOP; END; END; (In the above, i_CN is an arbitrary implementation-dependent cursor name which must not conflict with other names in the same stored procedure. It may appear in error messages.) Some observations: The SQL statement ("SET counter = counter + 1") will happen 0 times if there are 0 rows in the result set. Since NOT FOUND conditions are handled, it makes no sense to declare your own handler for NOT FOUND before a FOR -- it would be ignored. If MySQL allowed the optional clause that specifies a cursor (e.g. "FOR cursor1 CURSOR FOR SELECT a, b FROM t DO ..."), you couldn't use that cursor name in an OPEN, FETCH, or CLOSE. The open/fetch/close are implied. And MySQL doesn't support UPDATE/DELETE ... WHERE CURSOR. Therefore there is no point, for us, in the optional clause "[ [ ] CURSOR FOR ]". The statement "FOR SELECT a, a ..." would be illegal. We would have to declare a variable named a, and then another variable named a. That wouldn't work. You may not use COMMIT/ROLLBACK. The assumption is that the transaction will not end within the loop. You should be careful with LEAVE statements, for example: x: FOR SELECT a, b FROM t DO LEAVE x; END Such a statement will leave an open cursor. For Loop Variable Name ---------------------- In standard SQL, it is possible to qualify a declared variable with the beginning label. For example: x: BEGIN DECLARE v INT; SET v.x = 5; END MySQL does not support this. Therefore the optional "[ AS ]" clause is useless to us. However, for the record, I include an old email to Per-Erik Martin where it was discussed. To: Per-Erik Martin cc: Peter Gulutzan Subj: The FOR Statement Date: April 17, 2003 Hi Per-Erik, As promised in San Jose, I've had a look at the SQL:2003 description of the FOR statement. This is how I understand it. Your main question was: what is the point of . This is a that effectively precedes the word FOR (never mind that it is possible to have two in the same place, that's legal). You will be asking "what's the point" by now? Well, the name can be used as a qualifier for the variables that are implicitly DECLAREd within the FOR loop. There is an implicit bunch of DECLARE statements, one for each column in the SELECT list, with the same name and the same data type as the column in the SELECT list. There is an implicit FETCH (which is performed just before the ). For example: DECLARE a1 INT; FOR start_of_for AS SELECT s1 FROM table1 DO SET a1 = s1; END FOR Is equivalent to: DECLARE a1 INT; DECLARE s1 INT; /* this is implied */ DECLARE cursor_name1 CURSOR FOR SELECT s1 FROM table1; /* The above is implied. The name cursor_name1 is arbitrary. */ OPEN cursor_name1; (loop start) FETCH arbitrary_cursor_name INTO v1; /* Exit loop if the FETCH returns "no [more] rows found" */ (loop end) CLOSE cursor_name1; I found some further description in the DB2 manual. I think the example is helpful, although I would prefer to insist on a semicolon after the words "END FOR". [ Editor's note: there was a quotation here; the text has been moved to 'QA notes' field, which is not publicly visible. See also "References" section which has URLs of other vendors' manuals. ] [ Editor's note: putting "DECLARE a1 INT;" before the OPEN is an error. The correct rules are shown in the "Equivalence" section. ] Other DBMSs ----------- DB2 allows: FOR ... AS cursor_name CURSOR FOR SELECT ... DO ... END FOR. This is the same as ANSI/ISO, so we'll follow it. Oracle and PostgreSQL allow: FOR ... IN [REVERSE] x..y LOOP ... END LOOP. This is close to a WHILE loop, so it's just a redundant syntax that saves hardly any keystrokes. Sometimes these are called "numeric FOR loops" or "Integer FOR loops". Oracle and PostgreSQL allow: FOR ... IN SELECT ... LOOP ... END LOOP. Oracle also allows: FOR rowtype_variable IN cursor_name LOOP ... END LOOP. The rowtype_variable is for qualifying, so it's like "for loop variable name", which we won't (usefully) support. They use LOOP where we use DO. We don't (usefully) support cursor_name. SQL Server 2008 does not have a FOR loop. References ---------- Oracle 11g FOR loops: http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems028.htm#i34785 http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/06_ora.htm#sthref737 DB2 FOR loops: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/c0024351.htm PostgreSQL FOR loops: http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html BUG#18845 "Cursor traversal issues" Forum posting "Cursor FOR loop in MySql" http://forums.mysql.com/read.php?61,265383,265383 Forum posting "FOR LOOP IN procedure" http://forums.mysql.com/read.php?98,77522,77522#msg-77522
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.