存储过程和函数
存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。
1 创建存储过程和函数
创建存储过程和函数是指将经常使用的一组SQL语句的组合在一起,并将这些SQL语句当作一个整体存储在MySQL服务器中。
1.1创建存储过程:
CREATE PROCEDURE sp_name
([proc_parameter [ , .....] ])
[characteristic .......] routime_body
sp_name参数是存储过程的名称,proc_parameter 为存储过程的参数列表,characteristic 参数指定存储过程的特性,routime_body参数是SQL代码的内容。
proc_parameter 由三部分组成,其形式为[ IN | OUT | INOUT ] param_name type
IN 表示是输入的参数,OUT表示的是输出的参数,INOUT表示的是即可以作为输入又可以作为输出的参数。
param_name 参数是存储过程参数名称,type是参数指定存储过程的参数类型,该类型可以为MySQL数据库的任意数据类型。
2.变量的使用
存储过程和函数可以定义和使用变量,用户可以使用DECLARE关键字来定义变量,然后进行变量赋值,这些变量的作用范围是BEGIN.......END程序段中。
1.1定义变量
1.1局部变量: 以关键字declare声明,后跟变量名和变量的类型
declare a int
也可以在声明局部变量时,也可以采用关键字default为变量的默认值:declare a int default 10
delimiter//
create procedure p1()
begin
declare x char(10) default 'outer' ;
begin
declare x char(10) default 'inner';
select x;
end;
select x;
end;
//
全局变量:MySQL的会话变量不用声明即可使用,会话变量在整个过程中都有用,会话变量以字符@作为起始字符。
delimiter//
create procedure p2()
begin
set @t=1;
begin
set @t=2;
select @t;
end;
select @t;
end;
//
3.为变量赋值
使用DECLARE关键字来定义变量。定义变量的基本语法如下:
DECLARE var_name [ , .....] type [ DEFAULT value ]
使用DECLARE来声明变量:var_name是变量名,type是变量的类型,DEFAULT value是指定变量的默认值,不对该参数进行设置时,其默认值为NULL。
对于全局变量的声明使用的是set @ var_name,其基本语法是 SET var_name=expr [ ,var_name=expr ].......
var_name是变量名,expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量之间使用" ,"隔开。
SET mr_soft=10;
另外一种MySQL的变量赋值语句为:
SELECT col_name [, ...] INTO var_name [, ...] FROM table_name where condition
col_name参数标识查询的字段名称,var_name参数是变量的名称,table_name表示的是查询的表名,condition 表示指定的查询条件。
以上的赋值语句必须存在在创建的存储过程中,且需要将赋值语句放置在begin和end之间。若脱离此范围,该变量将不能使用或者被赋值。
光标的使用:查询语句可能查询出多条记录,在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。
光标的使用包括声明光标(DECLARE CURSOR)、打开光标(OPEN CURSOR)、使用光标(FETCH CURSOR )和关闭光标(CLOSE CURSOR)。光标必须声明在处理程序之前,并且声明在变量和条件之后。
声明光标:DECLARE cursor_name CURSOR FOR select_statement
cursor_name为光标的名称,select_statement 是一个SELECT 语句(SELECT 语句不能包含INTO子句),返回一行或多行数据。使用这个语句可以在存储过程中定义多个光标,但必须保证每个光标有自己的唯一的名称。
打开光标:OPEN cursor_name
cursor_name为光标的名称,在一个程序中一个光标可以被打开多次。由于可能在用户打开光标后,其他用户或者程序在更新数据,所以可能导致用户在每次打开光标时后,所显示的结果不同。 OPEN info_of_student
使用光标:
在打开光标之后可以使用光标:
FETCH......INTO 语句来读取数据。
FETCH cursor_name INTO var_name [ , var_name] ....
cursor_name为光标的名称,var_name为将光标中SELECT语句查询出来的信息存入该参数中。var_name是存放数据的变量名,必须在使用光标之前定义好。
FETCH INTO 和SELECT INTO 语句的具有相同的意义。
关闭光标:
CLOSE cursor_name
创建存储函数:
CREATE FUNCTION sp_name ( [ func_parameter [ , ...]])
RETURNS type
[ characteristic ....] routine_body
sp_name 为存储函数的名称,func_parameter
为存储函数的参数列表,可以由多个参数组成,其中每个参数均由参数名称和参数类型组成 param_name type
RETURNS type为指定返回值的类型
characteristic
指定存储过程属性
routine_body为SQL语言
例如:
delimiter //
create function name_of _students(stb_id INT)
returns varchar(50)
begin
return (select name fromstudentinfo where sid=std_id );
end
//