存储过程中的SQL语句集（StoredP

05/05 19:23:23

介绍

存储过程（Stored Procedure）是一个小型数据库系统，一组完成特定功能的SQL语句集，它存储在数据库中，一旦编译永久有效，用户指定存储过程的名称并给出参数（如带参数的存储过程）来执行它。存储过程是数据库中的重要对象。

存储过程的使用创建存储过程

句型：

CREATE PROCEDURE 存储过程名( IN|OUT|INOUT 参数名 数据类型 , ...)
BEGIN
	...
END;

MySQL存储过程的参数类型：

IN，表示存储过程的输入参数，参数的值会传递给存储过程，在存储过程中可以改变参数，存储过程返回时，不会返回参数值，这相当于存储过程中对此参数的更改对调用者不可见。

OUT表示存储过程的输入参数。该参数的值将在存储过程中被初始化为 NULL。当存储过程返回时，值也会被返回，调用者可以听到改变后的值。

INOUT代表存储过程的输入输出参数。参数由调用者初始化。在存储过程中所做的任何修改都将被返回，调用者可以看到更改后的值。

示例存储过程创建：

创建一个存储过程student_procedure，student_procedure有一个输入参数age和一个输出参数num，查询tb_student表中年龄小于或等于输入参数age的学生人数，将人数设置为num。

CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
BEGIN
	SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
END;

MySQL命令行创建存储过程：

如果在MySQL命令行创建存储过程，需要临时更改句式分隔符，因为MySQL默认的句式分隔符是;，会导致直接解析存储过程中的语句辅助论坛，造成句型错误。

-- 设置//为语句分隔符
mysql> DELIMITER //
mysql> CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
    -> END;
    -> //
Query OK, 0 rows affected
--恢复为原来的分隔符
mysql> DELIMITER ;

调用存储过程

sql 查看存储过程脚本_sql server 方括号存储_sql 怎么查看sql语句

tb_student 表数据：

+----+------+-----+-------------+-----------+----------+
| id | name | age | phone       | address   | class_id |
+----+------+-----+-------------+-----------+----------+
|  1 | 小明 |  18 | 188xxxx1234 | xxxxxxxxx |        1 |
|  2 | 小米 |  28 | 188xxxx1234 | xxxxxxxxx |        2 |
|  3 | 小看 |  28 | 188xxxx1234 | xxxxxxxxx |        3 |
|  4 | 小阿 |  38 | 188xxxx1234 | xxxxxxxxx |        3 |
|  5 | 小鬼 |  48 | 188xxxx1234 | xxxxxxxxx |        3 |
+----+------+-----+-------------+-----------+----------+

调用存储过程查询年龄小于38的中学生人数：

-- 调用存储过程
mysql> CALL student_procedure(38, @num);
Query OK, 1 row affected
-- 查看返回结果
mysql> select @num;
+------+
| @num |
+------+
|    2 |
+------+

查看存储过程定义

句型：

SHOW CREATE PROCEDURE proc_name;

比如看student_procedure的定义：

mysql> SHOW CREATE PROCEDURE student_procedure;
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure         | sql_mode                                                       | Create Procedure                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| student_procedure | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `student_procedure`(IN age TINYINT, OUT num INT) BEGIN SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

更改存储过程

句型：

ALTER PROCEDURE proc_name [characteristic ...]
characteristic: {
    COMMENT "string"
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

ALTERPROCEDURE 语句用于更改存储过程的个别特征。如果要改变存储过程的内容，可以先删除原来的存储过程，然后再新建一个同名的存储过程。

删除存储过程

句型：

DROP PROCEDURE [ IF EXISTS ] proc_name

比如删除student_procedure：

mysql> DROP PROCEDURE student_procedure;
Query OK, 0 rows affected
mysql> CALL student_procedure(38, @num);
1305 - PROCEDURE student_procedure does not exist

流程控制语句IF语句

句型：

IF 判断条件 THEN 处理语句
    [ELSEIF 判断条件 THEN 处理语句]...
    [ELSE 处理语句]
END IF

例子：

CREATE PROCEDURE test1(IN sex TINYINT)
BEGIN
	IF sex=1 THEN SET @sex="男";
	ELSEIF sex=0 THEN SET @sex="女";
	ELSE SET @sex="未知";
	END IF;
END;

CASE语句

CASE语句有两种写法：

句型一：

CASE 值
    WHEN 值1 THEN 处理语句
    [WHEN 值2 THEN 处理语句]...
    [ELSE 处理语句]
END CASE

句型二：

CASE
    WHEN 条件判断 THEN 处理语句
    [WHEN 条件判断 THEN 处理语句] ...
    [ELSE 处理语句]
END CASE

例子：

-- 写法1
CREATE PROCEDURE test2(IN sex TINYINT)
BEGIN
	CASE sex
	WHEN 1 THEN SET @sex="男";
	WHEN 0 THEN SET @sex="女";
	ELSE SET @sex="未知";
	END CASE;
END;
-- 写法2
CREATE PROCEDURE test3(IN sex TINYINT)
BEGIN
	CASE 
	WHEN sex=1 THEN SET @sex="男";
	WHEN sex=0 THEN SET @sex="女";
	ELSE SET @sex="未知";
	END CASE;
END;

循环句

LOOP 循环是一个无限循环。通常，它需要与LEAVE语句和ITERATE语句一起使用。 LEAVE语句表示跳出循环（类似于Java中的break），ITERATE语句表示跳出本次循环（类似于Java中的continue）。

句型：

[别名:]LOOP
    处理逻辑
END LOOP [别名]

例子：

CREATE PROCEDURE test4()
BEGIN
	SET @num=0;
	add_num:LOOP
		SET @num=@num+1;
		IF @num=10 THEN LEAVE add_num;
		END IF;
	END LOOP add_num;
END;

重复句子

REPEAT语句是一个循环语句，自带条件判断。每一句执行完之后，都会进行条件判断。如果为真，则退出循环sql 查看存储过程脚本，否则继续循环。（类似于Java中的dowhile循环）

句型：

[别名:] REPEAT
    处理语句
    UNTIL 条件判断
END REPEAT [别名]

例子：

sql 查看存储过程脚本_sql 怎么查看sql语句_sql server 方括号存储

CREATE PROCEDURE test5()
BEGIN
	SET @num=0;
	add_num:REPEAT
		SET @num=@num+1;
		UNTIL @num=10 END REPEAT add_num;
END;

WHILE句

WHILE语句也是一个循环，自带条件判断。与REPEAT语句不同的是WHILE语句会先进行条件判断。当判断条件为真时，循环中的单词和句子会继续执行，如果为假，则直接退出循环。（类似于 Java 中的 while 循环）

句型：

[别名:] WHILE 条件判断 DO
    处理逻辑
END WHILE [别名]

例子：

CREATE PROCEDURE test6()
BEGIN
	SET @num=0;
	add_num:WHILE @num<10 DO
		SET @num=@num+1;
		END WHILE add_num;
END;

光标

游标用于逐行处理查询的结果集。

游标的声明必须出现在HANDLER声明之前，变量和条件声明之后。

创建游标：

DECLARE 游标名称 CURSOR FOR sql查询;

打开游标：

OPEN 游标名称;

使用游标：

FETCH 游标名称 INTO 变量1 [,变量2]...

将结果集中的数据保存到对应的变量中。第一次使用游标时，默认读取结果集的第一行，通常配合循环语句逐行处理整个结果集。

关闭光标：

CLOSE 游标名称;

CLOSE 释放游标使用的所有内部内存和资源，因此每个游标在不再需要时都应关闭。游标关闭后就不能再使用了。如果需要使用，需要重新打开游标。

例子

查询tb_student表，将所有中学生姓名拼接成一个字符串，并设置在变量@name_Str中。

CREATE PROCEDURE test7()
BEGIN
	-- 声明局部变量student_name，用于接收数据集中的数据
	DECLARE student_name VARCHAR(10);
	-- 声明局部变量done，用于判断是否退出循环，默认值为FALSE
	DECLARE done INT DEFAULT FALSE;
	-- 声明游标my_cursor
	DECLARE my_cursor CURSOR FOR SELECT `name` FROM tb_student;
	-- 声明continue handler句柄，当出现SQLSTATE "02000"时将done设置为TRUE
	DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done = TRUE;
	
	-- 设置用户变量@name_Str为空字符串
	SET @name_Str="";
	-- 打开游标
	OPEN my_cursor;
		-- 开始LOOP循环
		concat_name:LOOP
			-- 将数据集中的一行数据存放到指定的变量中
			FETCH my_cursor INTO student_name;
			-- 判断是否退出循环
			IF done THEN LEAVE concat_name;
			END IF;
			-- 连接学生名称字符串
			SET @name_Str = CONCAT(@name_Str,student_name);	
		END LOOP concat_name;
	-- 关闭游标	
	CLOSE my_cursor;
END;

结果：

mysql> call test7();
Query OK, 0 rows affected
mysql> select @name_Str;
+----------------------+
| @name_Str            |
+----------------------+
| 小明小米小看小阿小鬼 |
+----------------------+

关于 SQLSTATE "02000"

使用游标时sql 查看存储过程脚本，可以使用FETCH将数据集中的数据保存到一个变量中进行处理，当整个数据集已经被FETCH后，再去FETCH会抛出异常：

1329 - No data - zero rows fetched, selected, or processed

这个异常对应的SQLSTATE是02000，所以需要指定一个句柄来捕获这些异常给flag参数，然后就可以通过这个flag来判断循环读取数据集的结束。

异常信息详见：ErrorReference

游戏卡盟网

存储过程中的SQL语句集（StoredP

最新资讯