MySql官方手册学习笔记3―MySql中的存储过程简介_MySQL
程序员文章站
2024-01-05 21:58:16
...
bitsCN.com
MySql官方手册学习笔记3—MySql中的存储过程简介 相关链接:MySql官方手册学习笔记1—MySql简单上手http:///database/201210/159522.html;MySql官方手册学习笔记2—MySql的模糊查询和正则表达式http:///database/201210/160095.html SELECT INTO CREATE PROCEDURE sp1 (x VARCHAR(5)) //创建储存过程 BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; //声明局部变量 DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid //将查到的属性值存入变量中 FROM table1 WHERE xname = xname; SELECT newname; END; 变量名不能与列名一样,当这个程序被调用的时候,无论table.xname列的值是什么,变量newname将返回值‘bob’。 HANDLER mysql> CREATE TABLE test.t (s1 int,primary key (s1)); Query OK, 0 rows affected (0.00 sec) mysql> delimiter // //将定界符“;”改为“//”,因为程序中需要用到“;”。 mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE insertErr CONDITION FOR SQLSTATE '23000'; //声明CONDITION -> DECLARE CONTINUE HANDLER FOR insertErr SET @x2 = 1;//声明HANDLER -> SET @x = 1; //为局部变量赋值 -> 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) 注意到,@x是3,这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR insertErr SET @x2 = 1; 这一行不在,第二个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取 默认(EXIT)路径,并且SELECT @x可能已经返回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 '02000' SET done = 1; OPEN cur1; //使用前先打开游标 OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b 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; EN bitsCN.com
MySql官方手册学习笔记3—MySql中的存储过程简介 相关链接:MySql官方手册学习笔记1—MySql简单上手http:///database/201210/159522.html;MySql官方手册学习笔记2—MySql的模糊查询和正则表达式http:///database/201210/160095.html SELECT INTO CREATE PROCEDURE sp1 (x VARCHAR(5)) //创建储存过程 BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; //声明局部变量 DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid //将查到的属性值存入变量中 FROM table1 WHERE xname = xname; SELECT newname; END; 变量名不能与列名一样,当这个程序被调用的时候,无论table.xname列的值是什么,变量newname将返回值‘bob’。 HANDLER mysql> CREATE TABLE test.t (s1 int,primary key (s1)); Query OK, 0 rows affected (0.00 sec) mysql> delimiter // //将定界符“;”改为“//”,因为程序中需要用到“;”。 mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE insertErr CONDITION FOR SQLSTATE '23000'; //声明CONDITION -> DECLARE CONTINUE HANDLER FOR insertErr SET @x2 = 1;//声明HANDLER -> SET @x = 1; //为局部变量赋值 -> 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) 注意到,@x是3,这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR insertErr SET @x2 = 1; 这一行不在,第二个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取 默认(EXIT)路径,并且SELECT @x可能已经返回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 '02000' SET done = 1; OPEN cur1; //使用前先打开游标 OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b 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; EN bitsCN.com
推荐阅读
-
MySql官方手册学习笔记3―MySql中的存储过程简介_MySQL
-
Mysql存储过程学习笔记--建立简单的存储过程_MySQL
-
MySql官方手册学习笔记2 MySql的模糊查询和正则表达式
-
【mysql基本使用学习笔记day01】【数据库简介】数据存储+数据库+RDBMS+RDBMS和数据库的关系+SQL+MySQL
-
【MYSQL笔记3】MYSQL过程式数据库对象之存储过程的调用、删除和修改
-
MySQL学习笔记3索引、存储过程_MySQL
-
MySql官方手册学习笔记2 MySql的模糊查询和正则表达式_MySQL
-
MySQL存储过程中的3种循环_MySQL
-
MySql官方手册学习笔记3―MySql中的存储过程简介_MySQL
-
MySql官方手册学习笔记2 MySql的模糊查询和正则表达式_MySQL