MySql official manual Study Notes 3-MySql stored procedure introduction bitsCN.com
SELECT
Create procedure sp1 (x VARCHAR (5 ))//Create storage process
BEGIN
DECLARE xname VARCHAR (5) DEFAULT 'Bob ';//Declare local variables
DECLARE newname VARCHAR (5 );
DECLARE xid INT;
SELECT xname, id INTO newname, xid//Store the property value to the variable.
FROM table1 WHERE xname = xname;
SELECT newname;
END;
The variable name cannot be the same as the column name. when this program is called, regardless of the value of the table. xname column, the variable newname will return 'Bob '.
HANDLER
Mysql>Create table test. t (s1 int, primary key (s1 ));
Query OK, 0 rows affected (0.00 sec)
Mysql>Delimiter // //Change the separator ";" to "//" because ";" is required in the program.
Mysql>Create procedure handlerdemo ()
->BEGIN
->DECLARE insertErr condition for sqlstate '201312 ';//Declare CONDITION
->DECLARE CONTINUE HANDLERInsertErrSET @ x2 = 1;//Declare HANDLER
->SET @ x = 1; //Assign a value to a local variable
->Insert into test. t VALUES (1 );
->SET @ x = 2;
->Insert into test. t VALUES (1 );
->SET @ x = 3;
->END;
->//
Query OK, 0 rows affected (0.00 sec)
Mysql>CALL handlerdemo ()//
Query OK, 0 rows affected (0.00 sec)
Mysql>SELECT @ x //
+ ------ +
| @ X |
+ ------ +
| 3 |
+ ------ +
1 row in set (0.00 sec)
Note that @ x is 3, which indicates that MySQL is executed to the end of the program. If declare continue handler for insertErr SET @ x2 = 1; this row is not present, MySQL may have adopted the default EXIT path after the second INSERT fails due to the primary key force, and SELECT @ x may have returned 2.
CURSOR
Create procedure curdemo ()
BEGIN
DECLARE done int default 0;
DECLARE a CHAR (16 );
DECLARE B, c INT;
DECLARE cur1 cursor for select id, data FROM test. t1;
DECLARE cur2 cursor for select I FROM test. t2;
Declare continue handler for sqlstate '000000' SET done = 1;
OPEN cur1;//Open the cursor before use
OPEN cur2;
REPEAT
FETCH cur1 INTO a, B;
FETCH cur2 INTO c;
If not done THEN
IF B <c THEN
Insert into test. t3 VALUES (a, B );
ELSE
Insert into test. t3 VALUES (a, c );
End if;
End if;
UNTIL done end repeat;
CLOSE cur1;
CLOSE cur2;
END
CASE
CASECase_value
WHENWhen_valueTHENStatement_list
[WHENWhen_valueTHENStatement_list]...
[ELSEStatement_list]
END CASE
Or:
CASE
WHENSearch_conditionTHENStatement_list
[WHENSearch_conditionTHENStatement_list]...
[ELSEStatement_list]
END CASE
The CASE statement of the storage program implements a complex condition structure. IfSearch_conditionIf the value is true, the corresponding SQL statement is executed. If no search conditions match, the statements in the ELSE clause are executed.
LEAVE
LEAVELabel
This statement is used to exit any labeled process control structure. It is used together with BEGIN... END or loop.
ITERATE
ITERATELabel
ITERATE can only appear in LOOP, REPEAT, and WHILE statements. ITERATE means: "loop again ."
For example:
Create procedure doiterate (p1 INT)
BEGIN
Label1: LOOP
SET p1 = p1 + 1;
IF p1 <10 then iterate label1; end if;
LEAVE label1;
End loop label1;
SET @ x = p1;
END
REPEAT
Mysql>Delimiter //
Mysql>Create procedure dorepeat (p1 INT)
->BEGIN
->SET @ x = 0;
->Repeat set @ x = @ x + 1; UNTIL @ x> p1 end repeat;
->END
->//
Query OK, 0 rows affected (0.00 sec)
Mysql>CALL dorepeat (1000 )//
Query OK, 0 rows affected (0.00 sec)
Mysql>SELECT @ x //
+ ------ +
| @ X |
+ ------ +
| 1, 1001 |
+ ------ +
1 row in set (0.00 sec)
WHILE
Create procedure dowhile ()
BEGIN
DECLARE v1 int default 5;
WHILE v1> 0 DO
...
SET v1 = v1-1;
End while;
EN
BitsCN.com