存储过程
存储过程概述:
通俗来讲存储过程就是像java一样创建一个方法,可以传递不同的参数,返回不同的结果,在一些地方使用方便,提高代码复用性
(1)增强SQL语言的功能和灵活性
(2)标准组件式编程
(3)较快的执行速度
(4)减少网络流量
(5)作为一种安全机制来充分利用
注意!
MySQL 5.0以前并不支持存储过程
存储过程的创建:
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体
代码示例:
创建存储过程
DELIMITER //
CREATE
PROCEDURE 过程名(参数)
BEGIN
过程体
END//
DELIMITER;
其中,DELIMITER 由于数据库版本不同,有些版本数据库创建时可能会报错,需要在存储过程前后加DELIMITER
关键字
参数:
存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:
- IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
- OUT:该值可在存储过程内部被改变,并可返回
- INOUT:调用时指定,并且可被改变和返回
存储过程的使用:
用call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。
示例:
创建一个存储过程并传入一个 1
-- CREATE PROCEDURE 创建触发器
CREATE PROCEDURE selectNum(
num int
)
BEGIN
SELECT num;
END;
-- CALL 使用触发器
CALL selectNum(1);
存储过程的删除
语法:
DROP PROCEDURE [过程1[,过程2…]]
示例:
DROP procedure selectNum;
在存储过程中创建变量:
存储过程中的变量可以分为基本变量和系统变量
基本变量
语法:
DECLARE 变量名1[,变量名2…] 数据类型 [默认值];
示例:
CREATE PROCEDURE selectNum(
)
BEGIN
DECLARE j int DEFAULT 1;
SELECT j;
END;
-- CALL 使用触发器
CALL selectNum();
用户变量
用户变量一般以@开头,用户变量可以在全局使用
示例:
set @i=1;
SELECT @i
注意:滥用用户变量会导致程序难以理解及管理
变量赋值
语法:
SET 变量名 = 变量值 [,变量名= 变量值 …]
存储过程中的条件语句:
IF-THEN-ELSE语句
语法:
IF var=0 THEN
......
END IF ;
IF var=0 THEN
....
ELSE
.......
END IF ;
CASE-WHEN-THEN-ELSE语句
语法:
CASE var
WHEN 0 THEN
......
WHEN 1 THEN
.....
ELSE
.....
END CASE ;
循环语句
语法:
WHILE-DO…END-WHILE
WHILE var<6 DO
.....
END WHILE ;
示例:
DECLARE var int DEFAULT 0 ;
WHILE var<5 DO
INSERT INTO smbms_type(typename,typepid) values("1",1);
set i = i+1;
end WHILE;
存储过程中使用事务
使用事务,重点要了解怎样捕获异常以及之后的处理,思路可以是定义一个变量,捕获到异常后,修改变量的默认值,之后判断变量的值,进行回滚或者提交事务
使用下列语句可捕获SQL异常:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; --异常时触发
代码示例:
CREATE
PROCEDURE 过程名(参数)
BEGIN
DECLARE t_error INTEGER DEFAULT 0; --定义变量
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; --异常时触发
START TRANSACTION; --开启事务
过程体
IF t_error = 1 THEN --判断
ROLLBACK; --回滚
ELSE
COMMIT; --提交
END IF;
Select t_error ; --返回状态
END//
触发器
触发器概述:
触发器就是当操作一个表中的数据时,同时可以操作其他表中的数据,例如,删除学生信息时,也删除其他表中的学生成绩信息,触发器可以方便我们操作数据库
触发器的创建和使用:
在Navicat中我们可以使用可视化界面,设计表中,手动添加一个触发器
其中
一个代表操作完成之后触发,一个代表操作之前触发
注意:
使用触发器时,不能操作本表的数据
定义好之后在下列书写代码体,其中的语法和存储过程中的语法大致相同
其中,有两个关键字需要了解
new 代表新的数据
old 代表旧的数据