mysql存储过程及控制语句

一、变量及赋值

变量分类

• 局部变量
• 用户变量
• 会话变量
• 全局变量

1、局部变量

局部变量一般只在begin…end代码块中有效，作用域仅限于该语句块，在该语句执行完毕后，局部变量就消失了，局部变量的定义语法：

DECLARE var_name  data_type [ DEFAULT value ];

说明 :declare为定义变量的关键字，var_name为自定义的变量名称，data_type为变量的类型，整型就是int，字符串就是varchar或者char类型等等，default value给变量赋默认值，可有可不有

给局部变量赋值有两种方式：

• set语句赋值

delimiter $$
CREATE PROCEDURE sp_demo01 () BEGIN
	DECLARE
		name01 VARCHAR ( 32 ) DEFAULT 'fai';
	SELECT name01;
END $$
call sp_demo01();

测试结果：

• select…into…赋值

delimiter $$
CREATE PROCEDURE sp_demo02 () BEGIN
declare var_name varchar(32);
	SELECT `name` into var_name from people where id =2;
	select var_name;
END $$

含义：从people表中查出pid是2的数据，取出name的值，然后将name赋值给var_name

测试结果：

2、用户变量

用户变量作用于当前整个链接，如果当前连接断开后，定义的用户变量就会消失，用户变量使用前不需要申明，使用的时候使用@变量名即可。变量赋值也有2种方式。

方式一：

set @age=18;      或者  set @age:=18

说明：申明了一个变量，并赋值为18，也可以给他赋值18.25，因为变量的类型没有严格的限制，随着值的变化而变化

方式二：

delimiter $$
CREATE PROCEDURE sp_demo02 () BEGIN
	select @name01:= name from people where id =3;
	select @name01;
END $$
call sp_demo02();//调用存储过程

说明：@name01是变量名，name是表中的字段名,当使用select语句对用户变量进行赋值时，只能使用”:=”方式

为什么说用户变量作用于当前整个链接呢？

当我们创建完整个存储过程并调用完后，可以单独执行select语句，查看当前的变量是否和存储过程时的变量是否一致

select @name01;

得到如下结果：

3、全局变量

全局变量在mysql启动的时候由服务器自动将它们初始化为默认值，这些默认值可以在mysql的配置文件修改，修改全局变量会影响到整个服务器。

全局变量查看

SHOW GLOBAL VARIABLES;

执行结果：

可以使用模糊查询某些需要的变量

show global variables like “%var%”;

4、会话变量

会话变量在每次建立一个新的连接的时候，由mysql来初始化，修改会话变量影响的是当前的数据库连接，不会影响其他的连接，查看会话变量

select @@var_name;
select @@session.var_name;
show session variables like "%var%";

如果要查看当前会话的全部会话变量，就去掉like后面的即可

设置会话变量方式：

set session var_name = value;

set @@session.var_name = value;

set var_name = value;  #缺省session关键字默认认为是session

二、存储过程

一、创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    routine_body
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]
---------------------------------------------------
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
routine_body:
　　Valid SQL routine statement

说明：in表示输入参数，out表示输出参数，inout表示既可以输入，也可以输出，param_name表示参数名称；type表示参数的类型

示例一，没有参数的存储过程：

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //
CREATE PROCEDURE sp_test()
BEGIN
	IF
		age >= 20 THEN
		SELECT
			'成年人';
		ELSE SELECT
			'未成年人';
	END IF;
	END
DELIMITER ;

注意：sp_test()表示没有参数的存储过程，“DELIMITER //” 语句的作用是将mysql的结束符设置为//，因为MYSQL默认的语句结束符为分号; ，存储过程中的SQL语句需要分号来结束，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以"END //"结束存储过程。存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。

示例二，带有in参数的存储过程

CREATE PROCEDURE p_showage( IN age INT )
BEGIN
	IF
		age >= 20 THEN
		SELECT
			'成年人';
		ELSE SELECT
			'未成年人';
	END IF;
	END

表示在调用存储过程的时候，输入一个age变量，从而输出成年人或者未成年人

示例三：带有out参数的存储过程

delimiter //
CREATE PROCEDURE sp_add(a int, b int,out c int)
begin
 set c=a+ b;
end//
call sp_add (1,2,@a);
select @a;

二、删除存储过程

DROP PROCEDURE  IF  EXISTS  存储过程名;

例如：

drop procedure if exists sp_test;

三、调用存储过程

call 存储过程名(参数列表);

调用存储过程时，存储过程名称后面必须加括号，哪怕该过程没有传递参数

存储过程一旦创建就不能修改，如果想要达到修改的效果，只能删除存储过程，然后重新创建

三、控制语句

SQL中的控制语句主要用于进行顺序、分支、循环等程序设计

控制语句	说明
begin…end	程序块语句
if…else	条件处理语句
case	分支语句
while，repeat，loop	循环语句
return	无条件退出语句
break	跳出循环语句
continue	跳出本次循环语句

一、begin…end

1. 主要作用是：语句块的界定

begin
	语句块
end

注意：1、begin和end必须成对使用；2、通常与分支结构和循环结构一起使用；3、可以嵌套。

二、if…else

1. 主要作用是：条件判断，执行if…then…else语句，如果出现多条件分支的情况下，需要加上elseif，即 if…then…elseif…then…else

declare @a int,@b varchar(100) --定义变量
set @a=3;--给变量赋值
set @b='jeson';--给变量赋值
if @a>4 then
     select @a+1 as a
else
     begin
          select @b+'2' as b
          select 900
     end

将大于20的人显示成年人，否则显示未成年人

CREATE PROCEDURE p_showage ( IN age INT ) BEGIN
	IF
		age >= 20 THEN
		SELECT
			'成年人';
		ELSE SELECT
			'未成年人';
	END IF;
	END $$

显示结果：

一、控制语句

SQL中的控制语句主要用于进行顺序、分支、循环等程序设计

控制语句	说明
begin…end	程序块语句
if…else	条件处理语句
case	分支语句
while，repeat，loop	循环语句
return	无条件退出语句
break	跳出循环语句
continue	跳出本次循环语句

一、begin…end

1. 主要作用是：语句块的界定

begin
	语句块
end

注意：1、begin和end必须成对使用；2、通常与分支结构和循环结构一起使用；3、可以嵌套。

二、if…else

1. 主要作用是：条件判断，执行if…then…else语句，如果出现多条件分支的情况下，需要加上elseif，即 if…then…elseif…then…else

declare @a int,@b varchar(100) --定义变量
set @a=3;--给变量赋值
set @b='jeson';--给变量赋值
if @a>4 then
     select @a+1 as a
else
     begin
          select @b+'2' as b
          select 900
     end

将大于20的人显示成年人，否则显示未成年人

CREATE PROCEDURE p_showage ( IN age INT ) BEGIN
	IF
		age >= 20 THEN
		SELECT
			'成年人';
		ELSE SELECT
			'未成年人';
	END IF;
	END $$

显示结果：

三、case表达式

1. case的主要作用是选择语句分支，是一种简单的条件判断转换成一个函数，他会把满足条件的表达式转换为对应的结果
2. case具有两种格式，简单case函数和复杂case函数
3. case表达式必须以end结束
4. 如果查找的字段是枚举值，则用简单函数表达式，如果查找的是字段是范围，则用复杂函数表达式

简单函数：

case sex when 1 then '男' when 2 then '女' when 3 then '人妖' else '其他' end

现在有一张表如下，要求将1转换成男，2转换成女，3转换成人妖，其他值转换成其他

sql语句：

SELECT * , case sex when 1 then '男' when 2 then '女' when 3 then '人妖' else '其他' end as 性别 from people;

转换后的结果

复杂函数：

case when sex=1 then '男' when sex=2 then '女' when sex=3 then '人妖' else '其他' end

现在有一张表如下：要求90分以上的转换成及格，80分以上的转换成良好，70分以上的转换成中等，60分以上的转换成及格，60分以下的转换成其他，并输出结果

sql语句：

SELECT
	* ,
CASE
		WHEN score > 90 THEN
		'优秀' 
		WHEN score > 80  THEN
		'良好' 
		WHEN score > 70  THEN
		'中等' 
		WHEN score > 60  THEN
		'及格' ELSE '差' 
	END 
FROM
	users;

运行后的结果为：

四、循环语句

1、while语句

现在有一张表，需要往表中添加10条数据，就需要用到循环语句来实现

创建存储过程：

delimiter $$
CREATE PROCEDURE insertData() BEGIN
	DECLARE
		maxid INT DEFAULT 0;
	DECLARE
		i INT DEFAULT 1;
	WHILE
			i <= 10 DO
		SELECT
			max( id ) INTO maxid 
		FROM
			test1;
		SET maxid = maxid + 1;
		INSERT INTO test1
		VALUES
			( maxid, 'faith', 25 );
		SET i = i + 1;
	END WHILE;
END $$

调用存储过程：call insertData(),调用完成后，查看数据库的数据：select * from test1;

2、repeat语句

语法：

repeat 
内容
until 条件  //退出循环的条件
end repeat;

要求：将test1表中id为偶数的年龄加5岁

delimiter $$
CREATE PROCEDURE updateData () BEGIN
	DECLARE
		minid INT DEFAULT 1;
	DECLARE
		maxid INT DEFAULT 1;
	SELECT
		min( id ) INTO minid 
	FROM
		test1;
	SELECT
		max( id ) INTO maxid 
	FROM
		test1;
	REPEAT
		IF
			minid % 2 = 0 THEN
				UPDATE test1 
				SET age = age + 5 
			WHERE
				id = minid;
		END IF;
		SET minid = minid + 1;
		UNTIL minid > maxid 
	END REPEAT;
END $$

调用存储过程：call updateData(),调用完成后，查看数据库的数据：select * from test1;

3、loop语句

loop名字:loop
内容
if 条件 then
leave loop名字；//结束循环
end if;
end loop;

将表中数据的年龄全部改为50

delimiter $$
CREATE PROCEDURE updateAge () BEGIN
	DECLARE
		minid INT DEFAULT 1;
	DECLARE
		maxid INT DEFAULT 1;
	SELECT
		min( id ) INTO minid 
	FROM
		test1;
	SELECT
		max( id ) INTO maxid 
	FROM
		test1;
	myloop :
	LOOP
		IF
			minid % 2 = 1 THEN
				UPDATE test1 
				SET age = 50 
			WHERE
				id = minid;
		END IF;
		SET minid = minid + 1;
		IF
			minid > maxid THEN
				LEAVE myloop;
			END IF;
	END LOOP;
END $$

调用存储过程：call updateAge(),调用完成后，查看数据库的数据：select * from test1;

五、break语句和Continue语句

break语句终止整个循环，continue语句结束本次循环，开始下次循环。