MySql official manual Study Notes 3-MySql stored procedure introduction _ MySQL

MySql official manual Study Notes 3-MySql stored procedure introduction bitsCN.com
MySql official manual study notes 3-MySql stored procedure introduction link: MySql official manual study notes 1-MySql easy to use http:///database/201210/159522.html MySql official manual study notes 2-MySql fuzzy query and regular expression http:///database/201210/160095.html Select into create procedure sp1 (x VARCHAR (5) // CREATE a stored procedure begin declare xname VARCHAR (5) DEFAULT 'Bob '; // DECLARE the local variable DECLARE newname VARCHAR (5); DECLARE xid INT; SELECT xname, id INTO newname, xid // Save the retrieved 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 table. what is the value of the xname column, and 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 delimiter ";" to "//", because ";" is required in the program. Mysql> create procedure handlerdemo ()-> BEGIN-> DECLARE insertErr condition for sqlstate '201312'; // declare condition-> declare continue handler for insertErr SET @ x2 = 1; // declare HANDLER-> SET @ x = 1; // assign a value to the 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) noted 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 OPEN cur2 before use; repeat fetch cur1 INTO a, B; FETCH cur2 INTO c; if not done then if B <c THEN INSERT INTO test. t3 VAL UES (a, B); ELSE INSERT INTO test. t3 VALUES (a, c); end if; UNTIL done end repeat; CLOSE cur1; CLOSE cur2; end case case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] end case Or: case when search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] The CASE statement of the end case storage program implements a complex condition structure. If search_condition is evaluated as true, the corresponding SQL statement is executed. If no search conditions match, the statements in the ELSE clause are executed. The LEAVE label statement is used to exit any labeled process control structure. It is used together with BEGIN... END or loop. ITERATE label ITERATE can only appear in LOOP, REPEAT, and WHILE statements. ITERATE means: "loop again ." 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 | + ------ + | 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; ENbitsCN.com