⑥ Mysql-- 存储过程(循环)

存储过程

语法:

delimiter //
create procedure 过程名(模式 形参1 数据类型,……)
begin
   程序体;
end;//
delimiter ;

注意：
(1)这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";“为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储 过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;“当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
(2)存储过程根据需要可能会有输入、输出、输入输出参数，多个参数用”,"分割开。
(3)过程体的开始与结束使用BEGIN与END进行标识。

形参模式：

in：该参数用于接收外部的值传入存储过程内部
out：该参数用于将存储过程内部处理结果传出存储过程

数据输出：

select  输出的对象;

调用存储过程：

call 过程名(实参1,实参2,......);

用户变量：

   使用SET或SELECT直接赋值，变量名以 @ 开头.

例如:

  set @var=1;  #定义变量并赋值为1
  select @var:=5; #定义变量并赋值为5
  select empno,ename into @var,@name from EMP where empno =7499;

可以在一个会话的任何地方声明，用户变量可以作用于当前整个连接，但是当前连接断开后，其所
定义的用户变量都会消失称为用户变量。

如: 定义一个存储过程，该过程接收两个参数，输出两个参数的和

delimiter //
create procedure test1(in a int,b int)
begin
  set @sum=a+b;
  select @sum;
end; //
delimiter ;

执行后就可以看到新建的存储过程了

使用call 调用存储过程

call testone(5,5);

如：定义一个存储过程，该过程接收一个员工编号，输出该员工的姓名，工资，职位

delimiter //
create procedure testtwo(in epno int)
begin
	select ename,sal,job into @ename,@sal,@job from emp where empno = epno;
	select @ename,@sal,@job;
end ; //
delimiter ;

call 调用存储过程

call testtwo(7369);

条件结构

① if 语句

if 语句根据条件表达式的值确定执行不同的语句块，if语句的语法格式如下。

if 条件表达式1 then 
   语句块1;
[elseif 条件表达式2  then
   语句块2] ...
[else   语句块n]
end if;

说明：end if后必须以“;”结束
1) 单分支 if 语句
【例】定义一个存储过程，该过程接收一个参数，如果参数为偶数，输出"xx为偶数"

delimiter //
create procedure testfour(in a int)
begin
	if MOD(a,2) = 0 then
		select CONCAT(a,"为偶数") as "结果";
	end if;

end; //
delimiter ;

call testfour(6);

输出:

2) 双分支IF语句
如：输入一个数，判断其偶数/奇数

delimiter //
create procedure testfive(in b int)
begin
	if MOD(b,2) = 0 then
		select CONCAT(b,"为偶数") as "结果";
	else
		select CONCAT(b,"为奇数") as "结果";
	end if;
end; //
delimiter ;

call testfive(31);

输出结果

3)多分支IF语句

如: 定义一个存储过程，该过程接收一个员工编号，如果该员工工资>3500.00,输出’xx工资等级H’,如果工资>2000.00，输出’xx工资等级M’，否则输出’xx工资等级L’

delimiter //
create procedure test10(in eno int)
begin
	select ename,sal into @ename,@sal from emp where empno = eno; 
	if (@sal > 3500) then
		select CONCAT(@ename,"工资等级H") as "结果";
	elseif 2000 < @sal and @sal < 3500 then
		select CONCAT(@ename,"工资等级m") as "结果";
	else
		select CONCAT(@ename,"工资等级l") as "结果";
	end if;
end; //
delimiter ;

call test10(7369);

输出:

case语句

case 表达式
when value1 then  语句块1;
when value2 then  语句块2;
…
else 语句块n;
end case;

注意：表达式的结果为一个常量，即一个具体的值

如:定义一个存储过程，该过程接收一个员工编号，如果员工职位为’SALESMAN’输出’销售人员’,如果为’CLERK’输出’普通员工’,如果为’MANAGER’输出’经理’,如果为’ANALYST’输出’技术人员’,否则输出’CEO’

delimiter //
create procedure test17(in emno int)
begin
	select ename,job into @ename,@job from emp where empno = emno;
	case @job
		when "SALESMAN" then select @job,"销售人员" as "结果";
		when "CLERK" then select @job,"普通员工" as "结果";
		when "MANAGER" then select @job,"经理" as "结果";
		when "ANALYST" then select @job,"技术人员" as "结果";
	else
		select @job,"CEO" as "结果";
	end case;
end; //
delimiter ;

call test17(7521);

删除一个储存过程名：

drop procedure 储存过程名;

循环结构

三种循环语句：while、repeat以及loop

iterate语句以及leave语句用于循环的控制
① while循环
while语句

[循环标签:]while 条件表达式 do   #循环标签：循环名称，自定义  
循环体;
end while [循环标签];

说明：
1）end while后必须以“;”结束
2）当条件表达式的值为true时,反复执行循环体,直到条件表达式的值为false,while语句的语法格式如下
3）循环标签可以省略

以下是定义一个存储过程，用while循环输出1-10

delimiter //
create procedure test22()
	begin
		set @a=0;
		      w:while @a<10 do
			set @a=@a+1;
			select @a; 
		      end while w;
	end; //
delimiter ;

call test22();

输出就会有十条结果

② repeat语句

当条件表达式的值为false时，反复执行循环，直到条件表达式的值为true

[循环标签:]repeat 
循环体;
until 条件表达式
end repeat [循环标签];

说明：end repeat后必须以“;”结束

定义一个存储过程，用repeat循环输出1-10

delimiter //
create procedure test26()
begin
	set @a=0;
	r:repeat
	set @a=@a + 1;
	select @a;
	until @a > 9 #当a > 9 时停止循环
	end repeat r;
end; //
delimiter ;

call test26();

③ Loop循环

语法

[循环标签:] loop
  循环体;
  if 条件表达式 then 
     leave [循环标签]; 
  end if;
end loop;

说明：end loop后必须以“;”结束

定义一个存储过程，用loop循环输出1-10

delimiter //
create procedure test31()
begin
	set @a = 0;
	l:loop
	set @a=@a+1;
	select @a;
	if @a>9 then
	leave l;
	end if;
	end loop;
end; //
delimiter ;

#分开执行
call test31(;)

循环控制语句

Leave关键字：用于跳出当前的循环语句（例如while语句）

语法格式如下：

   leave 循环标签;

说明：leave 循环标签后必须以“;”结束

Iterate关键字：用于跳出本次循环，继而进行下次循环。
语法格式：

 iterate 循环标签;

说明：iterate循环标签后必须以“;”结束

① Leave 用于跳出当前的循环语句（例如while语句）

定义一个存储过程，用while循环输出1-10

delimiter //
create procedure test28()
begin
	set @a = 0;
	w:while 1=1 do
		set @a = @a + 1;
		select @a;
		if @a>9 then
		leave w;
		end if;
	end while w; 
end; //
delimiter ;

#分开执行
call test28();

② iterate 用于跳出本次循环，继而进行下次循环。

iterate 循环标签;

定义一个存储过程，用while循环输出1-10中的偶数

delimiter //
create procedure test30()
begin
	set @a=0;
	w:while @a<10 do
		set @a=@a+1;
		if MOD(@a,2)=0 then
		select @a;
		else
		iterate w;
		end if;
	end while w;
end; //
delimiter ;

call test30();

如: 编写存储过程，向dept表中插入50行数据

delimiter //
create procedure test33()
begin
	set @deptno=50;
	set @dname= "测试部";
	set @loc = "成都";
	w:while @deptno < 100 do
	set @deptno = @deptno + 1;
	insert into dept values(@deptno,CONCAT(@dname,@deptno),CONCAT(@loc,@deptno));
	end while w;
	 
end; //
delimiter ;

call test33();

out形参

【例】

delimiter //
create procedure test1(out a varchar(20))  #out型 形参a 
begin
  select dname into a from dept where deptno=10;  # 查询dept表10号部门的名称，将这个值(部门名称)赋给变量a
end;  //
delimiter ;
  

call test1(@b);  # 调用存储过程，声明一个参数@a接收存储过程传出的数据

select @b; # 查询变量@b的值