mysql 函数if mysql 函数调用存储过程

存储过程就是一条或多条sql语句集合，可视为批文件，但是其作用不仅限于批处理

一、创建存储过程和函数

存储程序可以分为存储过程和函数，分别使用create procedure,create function创建，用call语句来调用存储过程，只能输出变量返回值。函数可以从语句外调用，也是返回标量值。存储过程也可以调用其他存储过程。

1、创建存储过程

CREATE

    [DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

解析：

proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
：in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出参数
——说明routine_body部分是有sql语句组成的
[NOT]DETERMINISTIC——指明存储过程执行的结果是否确定 
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
CONTAINS SQL ——子程序中包含sql语句且不包含写数据语句,系统默认指定CONTAINS SQL 
NO SQL——子程序中不包含sql语句
READS SQL DATA ——说明子程序包含读数据的语句
MODIFIES SQL DATA ——子程序包含写数据的语句
实例1：
Delimiter //
Create procedure AvgFruitPrice()
Begin
Select avg(f_price) as avgprice 
From fruits;
End //
‘Delimiter 。//’语句作用是将MySQL的结束运算符设为//,为了区分默认的结束符’;’,其作用是为了避免与存储过程中SQL语句结束符相冲突，并以 end //结束存储过程
实例2：
Create procedure Proc()
Begin
Select * from fruits;
End //
实例3：
Create procedure CountProc(out param1 int)
Begin
Select count(*) into param1 from fruits;
End //

2、创建存储函数

Create function func_name( [func_parameter])

Returns type

[characteristic...] routine_body

解析

func_parameter: [ IN | OUT | INOUT ] param_name type

Returns typ：表示函数返回的数据类型

Characteristic表示存储函数的特性，与存储过程类似，不懂往前看存储过程

实例1：
Delimiter //
Create function NameByZip()
Returns char(50)
Return (select s_name from suppliers where s_call=’48075’); //
注意：
a. 若在存储函数中的return语句返回一个类型不同于函数的returns子句指定的类型的值，则会被强制转换为恰当的类型。如函数返回一个enum数据而return语句返回一个整数，对于enum从函数返回值是字符串
b. 指定参数in/out/inout只对procedure是合法的，function中总会默认为in参数。
3、变量的使用——作用范围begin....end程序中
1) 定义变量
Declare var_name,[var_name]... data_type [default value];
实例：declare myparam int defaule 100;
2) 为变量赋值
a. Set var_name =expr [,var_name=expr]....;
b. Select col_name[,....] into var_name[,....] table_expr;
实例a：
Declare var1,var2,var3 int;
Set var1=10,var2=20;
Set var3=var1+var2;
实例b：
Declare fruitname char(50);
Declare fruitprice decimal(8,2);
Select f_name,f_price into fruitname,fruitprice from fruits where f_id=’a1’;

4、定义条件和处理程序——可以增强存储程序处理问题能力，避免程序异常停止运行

定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了遇到这些问题的处理方式，保证存储过程/函数在遇到警告/错误时能继续执行。

1) 定义条件
 Declare condition_name condition for [condition_type]
[condition_type];
Sqlstate [value] sqlstate_value|mysql_error_code
解析：
Condition_name参数表示条件的名称；
condition_type参数表示条件的类型，条件类型有两种——一种是sqlstate_value和mysql_error_code表示数值类型的错误，都可以表示MySQL的错误。
Sqlstate_value表示字符串类型的错误,它的值为42000
MySQL_error_code的值为1142
实例：
Declare command_not_allowed condition for sqlstate ‘42000’
Declare  command_not_allowed condition for  1148;
2) 定义处理程序
Declare handler_type handler for condition_value[,...] sp_statement
Handler_type:
Continue|exit|undo
Condition_value:
Sqlstate [value] sqlstate_value
|condition_name
|sqlwarning
|not found
|sqlexcception
|mysql_error_code
解析：
Handler_type：为处理错误方式，continue表示遇到错误不处理，继续执行，exit表示遇到错误马上退出，undo表示遇到错误返回上一步
Condition_value表示错误类型：
Sqlstate [value] sqlstate_value:包含5个字符的字符串错误值
Condition_name :表示定义条件的错误条件名称
Sqlwarning:匹配所有以01开头的SQLstate错误代码
Not found：匹配所有以02开头的SQLstate错误代码
Sqlexception:匹配没有被sqlwarning/not found捕获的错误代码
Mysql_error_code：匹配数值类型的错误代码
Sp_statement参数为程序语句段，表示在遇到定义错误是需要执行的存储过程或者函数
 
实例：定义处理程序的几种方法
方法一：捕获sqlstate_value
Declare continue handler for sqlstate ‘45s02’ set  @info=’no_sush_table’;
方法二：捕获mysql_error_code
Declare continue handler for 1146 set  @info=’no_sush_table’;
方法三：先定义条件然后调用
Declare no_such_table condition for 1146
Declare continue handler for no_sucn_table set  @info=’no_sush_table’;方法四：使用SQLwarning
Declare exit handler for sqlwarning set @info=’error’;
方法五：使用not found
Declare exit handler for not found set @info=’no_such_table’;
方法六：使用SQLexception
Declare exit handler for sqlexception set @info=’error’;

定义条件和处理程序具体的执行过程：
Create table test.t(s1 int primary key);
Delimiter //
Create procedure handlerdemo()
Begin
Declare continue handler for sqlstate ‘23000’ set @x2=1;
Set @x=1;
Insert into test.t values(1);
Set @x=2;
Insert into test.t values(1);
Set @x=3;
End;
//
Delimiter ;
Call handemo();
Select @x;

‘@var_name’表示用户变量，使用set语句为其赋值，用户变量与连接有感，一个用户端定义的变量不能被其他客户端看到或使用，当客户端退出时，该客户端连接的所有变量将自动释放。

5、光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果的记录，应该程序可以根据需要滚动或浏览其中的数据。

光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1）声明光标
Declare cursor_name cursor for select_statement
Select_statement参数表示select语句的内容，返回一个用户创建光标的结果集
2）打开光标
Open cursor_name;
3) 使用光标
Fetch cursor_name into var_name [,var_name]...[参数名称]
4) 关闭光标
Close cursor_name;
实例：Declare cursor_fruit cursor for select f_name,f_price from fruits;
Open cursor_fruit;
Fetch cursor_fruit into fruit_name,fruit_price;
Close cursor_fruit;

光标只能在存储过程和函数中使用

6、流程控制的使用

流程控制语句根据条件控制语句的执行。MySQL中的构造控制流程的语句有:IF语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句。

1）if语句
If语句包含多个条件判断，根据判断的结果为true或false执行相应的语句，语法如下：
If expr_condition then statement_list
[elseif expr_condition then statement_list]........
[else statement_list]
End if
实例：
If var is null
Then select ‘var is null’;
Else select ‘var is not null’;
End if ;
2)Case 语句
Case是另一个进行提哦案件判断语句，有两种格式
格式1：
Case case_expr
When when_value then statement_list
[When when_value then statement_list]....
[else statement_list]
End case
格式2：
Case 
When expr_condition then statement_list
[when expr_conditon then statement_list]....
[else statement_list]
End case

实例1：
Case val 
When 1 then select ‘val is 1’;
When 2 then select ‘val is 2’;
Else then select ‘val is not 1 or 2’;
End case;
实例2：
Case 
When val is null select ‘val is null’;
When val<0 then select ‘val is less than 0’;
When val>0 then select ‘val is greater than 0’;
Else select ‘val is 0’;
End case;
3) loop语句
基本格式如下：
[loop_label:] loop
Statement_list
End loop[loop_label]
实例：
Declare id int default 0;
add_loop:loop
Set id=id+1；
 If id>=10 then leave add_loop;
End if;
End loop add_loop;
4) leave语句——leave label，用于跳出任何被标注的流程控制构造
实例：
Add_num:loop
set@count=@count+1;
If @count=50 then leave add_num;
End loop add_num;
5) iterate语句——将执行顺序转到语句段开头处，格式：itrate label
Iterate只可以出现在loop、repeat、while语句内;iterate语句必须跟在循环标志前面。
实例：
Delimiter //
create procedure doiterate()
Begin
Declare p1 int default 0;
my_loop:loop
Set p1=p1+1;
If p1<10 then iterate my_loop;
Elseif p1>20 then leave my_loop;
End if;
Select ‘p1 is between 10 and 20 ‘;
End loop my_loop;
End;//

6) repeate语句

Repeate语句创建一个条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，表达式为真则结束循环否则重复执行循环中的语句。基本格式如下：

[repeat label:] repeat
Statement_list
Untll expr_conditon 
End repeat [repeat_label]
实例：
declare id int default 0;
Repeate set id=id+1;
Untll id>=10
End repeat;
7) while语句
While语句创建一个带条件判断的循环过程，while在执行语句先进行指定的表达式判断，为真则执行循环否则结束。格式如下
[while_label:]while expr_condition do
Statement_list
End while [while_label];
实例：
Declare i int defaule 0;
While i<10 do 
Set i=i+1;
End while;
二、调用存储过程和函数
1.调用存储过程
Call sp_name([parmeter[,...]])
实例：
Delimiter //
Create procedure countproc1(in sid int,out num int)
Begin
Select count(*) into num from fruits where s_id=sid;
End //
Delimiter ; 
Call countproc1(101,@num);
Select @num;
2.调用存储函数——与调用存储过程一样
实例：
Delimiter //
Create function countproc2(sid int)
Returns int
Begin
Return (select count(*) from fruits where s_id=sid);
End //
Delimiter ;
Select countproc2(101);
三、查看存储过程和函数
1、通过show status语句查看存储过程和存储函数
Show [procedure|function] status [like ‘paterrn’]
实例：show procedure status like ‘c%’ \G
2、通过show create语句查看存储过程和函数定义
Show create [procedure|function] sp_name;
3、从information_schema.routines表中查看存储过程和函数的信息
Mysql中存储过程和函数的信息存储在information_schema数据库下的routine表中。
Select * from information_schema.routines where routine_name=’sp_name’;
一、修改存储过程和函数
Alter [procedure|function] sp_name [characteristic....]
Charateristic参数指定存储函数的特性，可能取值有；
Constrains sql表示子程序包含SQL语句，但不含读或写数据的语句
No sql表示子程序中不包含sql语句
Reads sql data表示子程序中包含读数据的语句
Modifies sql data 表示子程序中包含写数据的能力
SQL security{definer|invoker}指明谁有权限来执行
Definer表示只有定义者可以执行，invoker表示调用者可以执行
Comment ‘string’表示注释信息
实例：
Alter procedure countproc 
Modifies sql data
Sql security invoker;
//查看修改结果
Select specific_name,sql_data_access,security_type
From information_schema.routines
Where routines_name=’count_proc’ and routine_type=’procedure’;
二、删除存储过程
Drop  [procedure|function]  [if exists] sp_name;

解惑：

1、Mysql中的存储过程和函数的区别？

本质上都是存储程序，函数只能通过return语句返回单个值或者表对象，而存储过程通过out参数返回多个值，函数限制比较多，而存储过程限制比较少。

1、存储过程的代码可以改变吗？——不可以

2、存储过程可以调用其他存储过程吗？——可以

3、存储过程的参数不要与数据表中的字段名相同

4、存储过程的参数可以用中文吗？——可以，但是需要加上character set gbk

Create procedure useInfo(in u_name varhcar(50) charcter set gbk ,out u_age int);