十二、存储过程和函数

---------------------------------------------------------       小路原创           ------------------------------------------------

简介：

在具体应用中，一个完整的操作会包含多条SQL语句，在执行过程中需要根据前面的SQL语句的结果又选择地执行后面的SQL语句，这就要用到存储过程和函数了。

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句集合。函数必须有返回值，而存储过程没有。

使用存储过程和函数的优点：

1、允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。

2、能够实现较快的执行速度，能减少网络流量。

3、安全操作数据。

一、创建存储过程和函数

1、创建存储过程

create procedure procedure_name(procedure_paramter[,……])

[characteristic……] routine_body;

其中：

procedure_paramter中每个参数的格式为：[in|out|inout] paramter_name type

characteristic表示存储过程的特性，可以是以下值：

language sql:表示存储过程的routine_body部分由sql语言的语句组成，mysql的默认语句。

[not]deterministic:表示存储过程的执行结果是否是确定的。默认是deterministric。

{contains sql|no sql|reads sql data|modify data}:表示sql语句的限制，contains sql表示包含sql语句但是不包含读或写数据的语句，

no sql表示不包含sql语句，reads sql data表示包含读数据的语句，modify data表示包含写数据的语句。默认是contains sql。

sql security{definer|invoker}:设置执行权限，definer表示定义者，invoker表示调用者。默认是difiner。

comment 'string':表示注释语句。

routine_body:表示执行代码部分。

example1:

use school;

create procedure pro_select_students()

comment '查询所有所有学生姓名'

begin 

select Name 

from students;

end;

2、创建函数

create function function_name([function_paramter[,……]])

[characteristic……] routine_body

其中：

function_patamter表示参数，格式：paramter_name type

characteristic表示函数的特性，与存储过程的相同。

routine_body表示代码部分。

example1:

use school;

create function fun_select_students(id int)

returns varchar(10)

comment '查询指定学号的学生姓名'

begin 

return (select Name 

from students

where students.ID = id);

end;

二、存储过程和函数中的表达式

1、常量的操作

声明：declare var_name[,……] type [default value]

赋值：set var_name = expr[,……]

      或者select field_name[,……] into var_name[,……]

from table_name

where condition;注意查询结果只能是单行

example:

declare name varchar(10) default '小路';

set name = '张三';

select Dept,Name into dept,name 

from students

where students.ID = '1';

2、条件的操作

定义条件：

declare condition_name condition for condition_value

condition_value:

sqlstate[value] sqlstate_value

|mysql_error_code

 

定义处理程序：

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 

|mysql_error_code

3、游标的使用

声明：declare cursor_name cursor for select_statement;

打开：open cursor_name;

使用：fetch cursor_name into var_name [,var_name]……

关闭：close cursor_name;

example:

declare Dept varchar(20);

declare cursor_dept

cursor for select Dept from students;

open cursor_dept;

fetch cursor_dept into Dept;

close cursor_dept;

4、流程控制的使用

条件控制：if和case

循环控制：loop、while和repeat

条件控制：

if search_condition then statement_list

[elseif search_condition then statement_list]……

[else search_condition]

end if 

或

case case_value

when when_value then statement_list

[when when_value then statement_list]……

[selse statement_list]

end case 

循环控制：

[begin_label:] loop 

statement_list

end loop [end_label]

可以通过leave label实现退出循环

[begin_label:]while search_condition do 

statement_list

end while [end_label]

[begin_label:] repeat search_condition do 

statement_list

end repeat [end_label]

三、查看存储过程和函数

show procedure status [like 'pattern'];

show function status [like 'pattern'];

查看系统表：

use information_schema;

select * from routines where condition;

查看定义信息：

show create procedure pro_name;

show create function fun_name;

四、修改存储过程和函数

alter procedure pro_name

[characteristic……]

其中characteristic可取：

{contains sql|no sql|reads sql data|modify sql data}

|sql security{definer|invoker}

|comment 'string'

alter function fun_name

[characteristic……]

其中characteristic可取值和存储过程的相同

五、删除存储过程和函数

drop procedure pro_name;

drop function fun_name;

use school;

drop procedure pro_select_students;

drop function fun_select_students;

show procedure status;

show function status;

---------------------------------------------------------       小路原创           ------------------------------------------------