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

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