Fsl's Markdown site

Appearance

存储过程 ​

对sql进行数据库层面的封装,可以进行复用

博客

查看存储过程 ​

查看所有储存过程 ​

sql
SHOW PROCEDURE STATUS

查看存储过程源码 ​

sql
SHOW CREATE PROCEDURE 存储过程名;

定义 ​

sql
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'
    */
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END$$

DELIMITER ;

DELIMITER: mysql分隔符，在mysql客户端中分隔符默认是分号（；）。如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。例如在声明存储过程时,需要写许多语句,可能需要更换分隔符

in:代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。 ou:代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。 inout:代表即时输入参数，又是输出参数，表示该参数的值即可有调用程序制定，又可以将inout参数的计算结果返回给调用程序。

存储过程中的语句必须包含在BEGIN和END之间。

DECLARE中用来声明变量，变量默认赋值使用的DEFAULT，语句块中改变变量值，使用SET 变量=值,变量在使用之前需要进行先声明,再进行调用,如果是存储过程的参数除外

调用存储过程 ​

使用call加上存储过程名加上参数列表,下面有实例进行说明

实例 ​

建表语句 ​

sql
CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `class_id` int DEFAULT NULL,
  `password` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
);

实例一 ​

这个示例包含in,out,declare,if的用法

存储过程 ​

存储过程的入参有user,passwd,出参有result,ok,使用delimiter声明界定符为$$,并在整个存储过程写完之后添加上$$表示语句结束,再使用delimiter将分隔符改为;

sql
delimiter $$
drop procedure login;
create procedure login(in user varchar(10),in passwd varchar(20),out result varchar(20),out ok varchar(10))
begin
	-- i在参数列表中没有出现,所以需要在这里进行声明
    declare i int default 0;
    select count(*) into i from task_10_1.student where name = user and password = passwd;
    if i > 0 then
        set ok = '登录成功';
        set result = concat('用户名:',user,' 密码:',passwd);
    else
        set ok = '登录失败';
        set result = NULL;
    end if;
end $$
delimiter ;

调用 ​

sql
call login('张三','123456',@result,@ok);
select @result,@ok;

实例二 ​

这个例子包含了select into和inout的应用

存储过程 ​

sql
delimiter $$
create procedure task_10_1.test(inout sid int,out user varchar(10),out passwd varchar(20))
begin
    select id, name, password into sid,user,passwd
        from task_10_1.student where sid = id;
end $$

调用 ​

sql
set @sid = 1;
call test(@sid,@user,@passwd);
select @sid,@user,@passwd;

实例三 ​

声明了IF..ELSE..的用法

存储过程 ​

sql
DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
	-- 存储过程体
	BEGIN
		IF `day` = 0 THEN
		SELECT '星期天';
		ELSEIF `day` = 1 THEN
		SELECT '星期一';
		ELSEIF `day` = 2 THEN
		SELECT '星期二';
		ELSE
		SELECT '无效日期';
		END IF;
	END$$
DELIMITER ;

实例四 ​

介绍了case..when的用法

声明 ​

sql
DELIMITER $$
CREATE 
    PROCEDURE demo5(IN num INT)
	BEGIN
		CASE num  -- 条件开始
		WHEN 1 THEN 
			SELECT '输入为1';
		WHEN 0 THEN 
			SELECT '输入为0';
		ELSE 
		SELECT '不是1也不是0';
		END CASE; -- 条件结束
	END$$
DELIMITER;

实例五 ​

介绍了while的用法

声明 ​

sql
DELIMITER $$
CREATE 
    PROCEDURE demo6(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     WHILE num<10 DO -- 循环开始
	         SET num = num+1;
	         SET SUM = SUM+num;
	         END WHILE; -- 循环结束
	END$$
DELIMITER;

实例六 ​

REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。

声明 ​

sql
-- 创建过程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     REPEAT-- 循环开始
		SET num = num+1;
		SET SUM = SUM+num ;
		UNTIL num>=10
		END REPEAT; -- 循环结束
	END$$
DELIMITER;

实例七 ​

循环语句，用来重复执行某些语句。

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。

LEAVE 语句效果对于Java中的break，用来终止循环； ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

声明 ​

sql
DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num <= 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;