什么是函数和存储过程
存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。
存储过程和函数的区别在于函数必须有返回值, 而存储过程没有, 存储过程的参数可以
使用IN、 OUT、 INOUT 类型, 而函数的参数只能是 IN 类型的。
权限控制:
create routine – 新增存储过程/函数的权限。
alter routine – 修改/删除存储过程和函数的权限。
execute – 执行存储过程和函数的权限。
创建、修改存储过程或者函数
1 允许DDL语句。
2 允许commit, rollback。
3 可以调用其他存储过程和函数。
4 不允许使用load data infile
delimiter // // --表示用//代替;作为结束符, 第二个//作为本次语句的结束符。
create procedure p1(out out1 int) reads sql data begin select * from t1; select 122 into out1; end// --返回游标, 并返回一个重新查询结果out1
delimiter ; ; --表示重新用;来作为结束符。
call p1(@a); --调用存储过程p1,并用变量@a存放存储过程的返回结果;
select @a;--查询变量@a的值
一 . 设置变量
SET var_name = expr [, var_name = expr] ... --直接赋值变量
SELECT col_name[,...] INTO var_name[,...] table_expr --通过查询赋值
二. 定义条件和处理
处理程序遇到问题后, 预先定义的处理步骤。分为:
- 条件的定义
declare c1 conditon for sqlstate xxxx|mysql_error_code --预先定义一个
条件, 这个条件用来处理特定的错误或者sql状态
- 条件的处理
declare handler_type handler for condition_value[,...] sp_statement
--声明一个处理模型, 在什么条件下,按照什么步奏处理
handler_type: continue/exit/undo --继续,或者退出过程,或者不执行这句sql。
condition_value:
SQLSTATE [VALUE] sqlstate_value -- 直接指定一个sql状态
condition_name :在条件定义部分定义的条件
SQLWARNING , NOT FOUND, SQLEXCEPTION, mysql_error_code
示例:
create procedure p1() begin insert into t2 values(100); insert into t2 values(200); end // 创建一个没有处理重复主键的存储过程
create procedure p1() begin declare continue handler for sqlstate '23000' set @a=1; insert into t2 values(100); insert into t2 values(200); end // 创建一个有重复主键的存储过程
二. 游标(光标)的使用
游标用于对结果集的循环处理。 声明游标需要放在变量、条件处理声明之后, 程序体之前。
DECLARE cursor_name CURSOR FOR select_statement --声明游标
OPEN cursor_name --打开游标
FETCH cursor_name INTO var_name [, var_name] ... --取出游标
CLOSE cursor_name --关闭游标
三. 流程控制
if 、case、loop(循环)、leave(用于表示离开循环,或者离开程序体),iterate(跳出当前的
循环,进入下一轮循环)、repeat(循环)、while(循环)