一、存储过程和函数的引入
存储过程和函数时在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器段的数据传输;
二、创建存储过程和函数
2.1 创建存储过程
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_nametype 其中,IN 表示输入参数;OUT 表示输出参数;INOUT 表示既可以是输入,也可以是输出;param_name 参数是 存储过程的参数名称;type 参数指定存储过程的参数类型,该类型可以是 MySQL 数据库的任意数据类型; Characteristic 参数有多个取值。其取值说明如下: LANGUAGESQL:说明 routine_body 部分是由 SQL 语言的语句组成,这也是数据库系统默认的语言。 [NOT]DETERMINISTIC :指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每 次执行存储过程时,相同的输入会得到相同的输出。NOTDETERMINISTIC 表示结果是非确定的,相同的输入 可能得到不同的输出。默认情况下,结果是非确定的。 {CONTAINS SQL|NO SQL|READSSQLDATA|MODIFIES SQLDATA} :指明子程序使用 SQL 语句的限制; CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句;NO SQL 表示子程序中不包含 SQL 语句;READSSQLDATA 表示子程序中包含读数据的语句;MODIFIES SQLDATA表示子程序中包含写数据的 语句。默认情况下,系统会指定为 CONTAINSSQL; SQLSECURITY{ DEFINER |INVOKER };指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行; INVOKER 表示调用者可以执行。默认情况下,系统指定的权限是 DEFINER。 COMMENT‘string’ :注释信息; |
例:
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_book (IN bT INT,OUT count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) FROM t_book WHERE bookTypeId = bT;
END
&&
DELIMITER;
//调用存储过程
CALL pro_book(1,@total)
2.2创建存储函数
CREATE FUNCTION sp_name([func_parameter[,...]]) RETURNS type [characteristic...]routine_body sp_name 参数是存储函数的名称;func_parameter 表示存储函数的参数列表;RETURNStype 指定返回值的 类型;characteristic 参数指定存储过程的特性,该参数的取值与存储过程中的取值是一样的;routine_body 参数 是 SQL 代码的内容,可以用 BEGIN...END 来标志 SQL 代码的开始和结束; func_parameter 可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下: param_name type 其中,param_name 参数是存储函数的参数名称;type 参数指定存储函数的参数类型, 该类型可以是 MySQL 数据库的任意数据类型; |
//创建存储函数
DELIMITER &&
CREATE FUNCTION func_book(bookId INT)
RETURNS VARCHAR(20)
BEGIN
RETURN(SELECT bookName FROM t_book WHERE id=bookId);
END
&&
DELIMITER;
//调用存储函数
SELECT func_book(2)
2.3 变量的使用
1,定义变量
DECLARE var_name [,...] type [DEFAULT value].
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user()
BEGIN
DECLARE a,b VARCHAR(20);//定义变量
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER;
//调用存储过程
CALL pro_user();
2,为变量赋值
SET var_name = expr[,var_name=expr]...
SELECT col_name[,..] INTO var_name[,...]
FROM table name WHERE condition
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user2()
BEGIN
DECLARE a,b VARCHAR(20);//定义变量
//SET a='java1234',b='123456';//方式一:直接为变量赋值
SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;//方式二:从其它表中查询给a,b赋值进去
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER;
//调用存储过程
CALL pro_user2;
2.4 游标的使用
查询语句可能查询出多条记录,在存储过程和函数中使用游标来逐条读取查询结果中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前,并且声明在变量和条件之后。
1,声明游标
DECLARE cursor_name CURSOR FOR select_statement;
2,打开游标
OPEN cursor_name;
3,使用游标
FETCH cursor_name INTO var_name[,var_name...];
4,关闭游标
CLOSE cursor_name
DELIMITER &&
CREATE PROCEDURE pro_user4()
BEGIN
DECLARE a,b VARCHAR(20);//声明变量
DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;//声明游标
OPEN cur_t_user2;//打开游标
FETCH cur_t_user2 INTO a,b;//使用游标
INSERT INTO t_user VALUES(NULL,a,b);
CLOSE cur_t_user2;//关闭游标
END
&&
DELIMITER;
2.5 流程控制的使用
存储过程和函数中可以使用流程控制来控制语句执行。MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。
1,IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list]...
[ELSE statement_list]
END IF
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user2 WHERE id = bookId;//将查询到的数据放到全局变量中@num代表全局变量
IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id = bookId;//有数据,那么就更新一条数据
ELSE
INSERT INTO t_user VALUES(NULL,'23123321','23123321');//没有数据就插入一条数据
END IF;
END
&&
DELIMITER;
//调用存储过程
CALL pro_user5(5);//有id=5的就更新数据,没有数据iu插入一条新数据
2,CASE语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user2 WHERE id = bookId;
CASE @num
WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'213123','123123');
ELSE INSERT INTO t_user VALUES(NULL,'213123123','213123');
END
&&
DELIMITER;
//调用存储过程
CALL pro_user6(5);//有id=5的就更新数据,没有数据iu插入一条新数据
3,LOOP,LEAVE语句
LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。但是LOOP语句本身没有停止循环的语句,必须是遇到LEAVE语句等才能停止循环。LOOP语句的语法的基本形式如下
[begin_label:]LOOP
Statement_list
END LOOP[end_label]
LEAVE语句主要用于跳出循环控制。语法形式如下:
LEAVE label
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum = bobalNum-1;
IF totalNum=0 THEN LEAVE aaa;
ELSE INSERT INTO t_user VALUES(totalNum,'213123','123123');
END IF;
END LOOP aaa;
END
&&
DELIMITER;
//调用存储过程
CALL pro_user7;
4,ITERATOR语句
ITERATE语句也是用来跳出循环的语句。但是,ITERATE语句是跳出本次循环,然后直接进入下一次循环。基本语法:
ITERATE label;
DELIMITER &&
//创建存储过程
CREATE PROCEDURE pro_user8(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum = totalNum-1;
IF totalNum=0 THEN LEAVE aaa;
ELSEIF totalNum=3 THEN ITERATE aaa;
END IF;
INSERT INTO t_user VALUES(totalNum,'2131244','12312455');
END LOOP aaa;
END
&&
DELIMITER;
//调用存储过程
CALL pro_user8;
5,REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT语句的基本语法形式如下:
[begin_label:]REPEAT
Statement_list
UNTIL search_condition
END REPEAT[end_label]
DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
BEGIN
REPEAT
SET total=totalNum-1
INSERT INTO t_user VALUES(totalNum,'21323','123213');
UNTIL totalNum=1;
END REPEAT;
ENG
&&
DELIMITER;
CALL pro_user9(11)
6,WHILE语句(经典)
[begin_label:]WHILE search_condition DO
Statement_list
END WHILE[end_label]
DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
BEGIN
WHILE totalNum>0 DO
INSERT INTO t_user VALUES(totalNum,'122315','3142321');
SET totalNum = totalNum-1;
END WHILE;
END
&&
DELIMITER;
CALL pro_user10(11);
三、调用存储过程和函数
3.1 调用存储过程
CALL sp_name([parameter[,,,]])
3.2 调用存储函数
fun_name([parameter[,...]])
四、查看存储过程和函数
4.1 SHOW STATUS 语句查看存储过程和函数的状态
SHOW {PROCEDURE | FUNCTION} STATUS[LIKE 'pattern'];
4.2 SHOW CREATE 语句查看存储过程的函数的定义
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
4.3 从information_schema.Routines表中查看存储过程和函数的信息
五、修改存储过程和函数
ALTER{PROCEDURE|FUNCTION}sp_name[characteristic...]
characteristic:
{CONTAINSSQL}NOSQL|READSSQLDATA|MODIFIESSQLDATA}
|SQLSECURITY{DEFINER |INVOKER }
|COMMENT‘string’
其中,sp_name 参数表示存储过程或函数的名称;characteristic 参数指定函数的特性。CONTAINS SQL 表示子程 序包含 SQL 语句,但不包含读或写数据的语句;NO SQL 表示子程序中不包含 SQL 语句;READS SQLDATA 表 示 子 程 序 中 包 含 数 据 的 语 句 ;MODIFIES SQL DATA 表 示 子 程 序 中 包 含 写 数 据 的 语 句 。SQL SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行; INVODER 表示调用者可以执行。COMMENT‘string’ 是注释信息。
六、删除存储过程
DROP {PROCEDURE|FUNCTION} sp_name;