MySQL存储过程和函数

MySQL存储过程和函数：
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
存储过程和函数的区别：
函数必须有返回值，存储过程没有。
存储过程的参数可以是in（输入参数）、out（输出参数[返回值有多个]）、inout（输入输出参数）类型，函数的参数只能是in。
优点：【高并发的应用程序就会较多的使用存储过程。】
存储过程只在创建时进行编译，SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库的执行效率。[逻辑性比较强的语句]
简化复杂操作，结合事务一起封装。[类似自己写个方法调用]
复用性。[可以随时调用]
安全性高，可以指定存储过程的使用权。


创建和调用存储过程：
create procedure procedureName(参数列表) [特性......] 过程体（相当于方法体）
存储过程的参数形式：[in | out | inout] 参数名 类型


用mysql用delimiter命令来把语句界定符从；变为//。这就允许用在程序体中的；界定符被传递到服务器而不是mysql自己来解释
创建时：
delimiter //
create procedure procedureName（参数列表）
begin
有效SQL语句...
end //
delimiter ；


例子：
create procedure procedureName（参数列表）
有效的SQL语句...：
create procedure procedureName（out result int）
select count(id) into result from emp;   ------把id的个数存到result中去




调用时：
call procedureName（实参列表）；
例子：call procedureName（@a）；----就是把result的值传给a


显示：select @a；----就会显示result传给a的值。


创建和调用函数：
create function functionName(参数列表)  returns 返回值类型 [特性......] 函数体
函数的参数形式：参数名 类型


用mysql用delimiter命令来把语句界定符从；变为$$。这就允许用在程序体中的；界定符被传递到服务器而不是mysql自己来解释
创建时：
delimiter $$
create function functionName（参数列表） returns 返回值类型
begin
有效SQL语句...
end $$
delimiter ；


例子：
create function functionName（参数列表）
有效的SQL语句...：
create  function  functionName(s char(20)) returns char(50)
return concat('hello,',s,'!');   ------把id的个数存到result中去




调用时：
select functionName（实参列表）；[和聚合函数的调用方法基本一致]
例子：select functionName('world');----就输出字符串"hello,world!" [字段名为：functionName('world')，记录为hello,world! ]


查看存储过程和函数的定义[比较乱的显示]：
show create procedure procedureName;
show create function functionName;






查看存储过程和函数的状态：
show procedure status [like 'pattern'];
show function status [like 'pattern'];


删除存储过程和函数[一次只能删除一个函数或者一个存储过程]：
drop procedure [if exists] procedureName;
drop function [if exists] functionName;


变量的定义：
局部变量：定义一个局部变量，作用范围只能在begin...end之中，也可以用在嵌套的块中
声明形式：declare 变量名 [，...] 变量类型[default，值]
变量的定义必须卸载复合语句的开通，并且在任何其他语句的前面。
变量的赋值：
直接赋值：
set 变量名 = 表达式值或者一个常量值[，...]；
将查询结果赋值给变量（查询返回的结果只能有一行）：
select 列名[，...] into 变量名[,...] from 表名 where 条件...


用户变量：由客户端定义的变量（不同用户有不同的变量，无法互相查看，客户端退出，对应的变量会自动释放）
声明形式：@变量名
赋值：
set @变量名 = 表达式值；
set 值 into @变量名；


变量的使用：
变量在存储过程中的使用：
delimiter $$
create procedure procedureName()
begin
declare a int;
declare n varchar(20);
set a=3;
set n = 'jack';
insert into emp(id,name,gender) values(a,n,'g');
end $$
delimiter ；
call procedureName();-------然后就增加了一个emp纪录。


流程控制：
if语法：
if 条件 then
SQL语句块
elseif 条件2 then 
SQL语句块2
else SQL语句块3
end if


例子：
delimiter $$
create procedure procedureName(in param int)
begin
declare a int;
set a=param+1;
if a>5 then
select * from emp where name like 'j%';
else 
select * from emp where name like 'k%';
end if; 
end $$




set @p = 3；
call procedureName(@p);


delimiter ；
-----那么就会显示名字like 'j%'的所有结果。


case语句：
case 表达式值
when 值1 then
SQL语句块1
when 值2 then 
SQL语句块2
else SQL语句块3
end case

例子：
delimiter $$
create procedure procedureName(in param int)
begin
case param
when 1 then
select * from emp where name like 'j%';
when 2 then
select * from emp where name like 'k%';
else
update emp set name='lucy' where id=2;
end case; 
end $$
delimiter ；


set @p = 1；
call procedureName(@p);-----那么就会显示名字like 'j%'的所有结果。


loop语句及调转：
[开始标注：] loop
语句块
end loop [结束标注]
标注两个地方要一致，要不都有，要不都没有！！！


关键字：
leave label---[break]
用于从标注的流程控制构造中退出，他和begin...end或循环一起被使用。
iterate label---[continue]
跳过当前循环的剩下语句，直接进入下一轮循环，该关键字只能出现在loop、repeat和while语句内。


例子：
delimiter $$
create procedure procedureName(p1 int)
begin
label:loop
set p1=p1+1;
if p<5 then
select * from emp where name like 'j%';
elseif p>10 then
iterate label;
end if; 
leave label;
end loop label;
[set @x=p1; ]----这里如果有定义的话，则只要是用户连接都可以用。
end $$
delimiter ；


set @x=0;
call procedureName(@x);-----那么就会显示名字like 'j%'的所有结果。