--------------------------------------------------------- 小路原创 ------------------------------------------------
简介:
在具体应用中,一个完整的操作会包含多条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;
--------------------------------------------------------- 小路原创 ------------------------------------------------