存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在mysql服务器中存储和执行的,可以减少客户端和服务器端的数据传输。
一.创建存储过程和函数
1. 创建存储过程
mysql中创建存储过程的基本形式:
create procedure sp_name([proc_parameter[,…]]) [characteristic…] routine_body;
sp_name:参数是存储过程的名称;
proc_parameter:表示存储过程的参数列表;
characteristic:参数指定存储过程的特性;
routine_body:参数是SQL代码的内容,可以用begin…end来标志SQL代码的开始和结束;
proc_parameter中每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。
[in|out|inout] param_name type
in:表示输入参数
out:表示输出参数
inout:表示既可以是输入也可以是输出
param_name:参数是存储过程的参数名称
type:参数指定存储过程的参数类型,该类型可以是mysql数据库的任意数据类型。
characteristic参数有多个取值。取值说明:
language SQL:说明routine_body部分是由SQL语言的语句组成,这也是数据库系统默认的语言。
[not] deterministic:指明存储过程的执行结果是否是确定的。
deterministic:表示结果是确定的,每次执行存储过程时,相同的输入会得到相同的输出。
not deterministic:表示结果是非确定的,相同的输入可能得到不同的输出。默认情况下,结果也是非确定的。
{contains sql|no sql|reads sql data|modifies sql data}:指明子程序使用SQL语句的限制。
contains SQL:表示子程序包含SQL语句,但不包含读或者写数据的语句;
no SQL:表示子程序中不包含SQL语句;
reads SQL data:表示子程序中包含读数据的语句;
modifies sql data:表示子程序中包含写数据的语句。默认情况下,系统会指定为contains SQL。
SQL security{definer|invoker}:指明谁有权限来执行。
definer:表示只有定义者自己才能执行;
invoker:表示调用者可以执行。默认情况下,系统指定的权限是definer。
comment ‘string’:注释信息。
注:创建存储过程时,系统默认指定contains SQL,表示存储过程中使用SQL语句,最好设置为no SQL。而且,存储过程中最好在comment部分对存储过程进行简单的注释,以便以后再阅读存储过程的代码是更加方便。
示例:下面创建一个名为num_from_employee的存储过程。
delimiter &&
create procedure num_from_employee(in emp_id int,out count_num int)
reads sql data
begin
select count(*) into count_num
from employee
where d_id=emp_id;
end &&
注:代码执行完毕,没报错就表示存储过程创建成功;
说明:mysq中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来结束。为避免冲突,首先用”delimiter &&”将mysql的技术符设置为&&。最后再用”delimiter;”来将结束符恢复成分号。
创建存储函数
mysql中创建存储函数的语法:
create function sp_name([func_parameter[,…]])
returns type
[characteristic…] routine_body
sp_name:参数是函数的名称;
func_parameter:表示存储函数的参数列表;
returns type:指返回值类型;
characteristic:参数指定存储函数的特性,该参数的取值与存储过程中取值是一样的。
routine_body:参数是SQL代码的内容,可以用begin…end来标志SQL代码的开始和结束。
func_parameter:可以由多个参数组成,其中每个参数由参数名称和参数类型组成,形式:param_name type
其中,param_name 是存储函数的参数名称;
type:参数是指定存储函数的参数类型;
示例:下面创建一个名为name_from_employee的存储函数
create function name_from_employee(emp_id int)
returns varchar(20)
begin
return (select name from employee where num=emp_id);
end
存储函数的名称:name_from_employee;
函数的参数:emp_id;
返回值是varchar类型;
2. 变量的使用
在存储过程和函数中,可以定义和使用变量。可以使用declare关键字定义变量,然后赋值。变量的作用范围是begin…and程序段中。
(1) 定义变量
使用declare关键字定义变量。语法:
declare var_name[,…] type [default value]
declare:声明变量;
var_name:参数是变量的名称(可以同时定义多个变量);
type:指定变量的类型;
default value:子句将变量默认值设置为value,未使用default value时,默认值为null;
示例:declare my_sql int default 10;
(2) 为变量赋值
mysql中set关键字为变量赋值。语法:
set var_name = expr[,var_name=expr]…
set:为变量赋值;
var_name:参数是变量的名称;
expr:参数是赋值表达式;
注:一个set语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号(,)隔开。
示例:为变量my_sql赋值为30;
set my_sql=30;
mysql中还可以用select…into…语句为变量赋值。语法:
select col_name[,…] into var_name[,…] from table_name where condition
col_name:参数表示查询的字段名称;
var_char:参数是变量的名称;
table_name:参数指表的名称;
condition:参数指查询条件;
示例:下面从employee表中查询id为2的记录。将该记录的d_id值赋给my_sql变量。
select d_id into my_sql from employee where id=2;