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：in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出参数

 LANGUAGE SQL——说明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);