六安网站开发,wordpress安装教程,福州网站关键词推广,企业培训课程体系目录
前言#xff1a;
一、触发器
1.1 触发器简介
1.2 触发器的创建
1.3 示例演示
二、存储过程
2.1 什么是存储过程
2.2 优势与应用场景
2.3 存储过程的创建与使用
各参数类型所实现的存储过程
三、存储函数
3.1 什么是存储函数
3.2 存储函数的创建与使用
调用…目录
前言
一、触发器
1.1 触发器简介
1.2 触发器的创建
1.3 示例演示
二、存储过程
2.1 什么是存储过程
2.2 优势与应用场景
2.3 存储过程的创建与使用
各参数类型所实现的存储过程
三、存储函数
3.1 什么是存储函数
3.2 存储函数的创建与使用
调用存储函数
删除存储函数
3.3 实例演示
四、游标
4.1 如何使用游标
1、声明游标
2、声明游标后想从游标中提取数据必须要打开游标
3、使用游标
4关闭游标
4.2 游标的应用 前言 在本文中你将了解并学习到SQL编程中的触发器、存储过程与存储函数的创建与使用游标的声明与应用并对SQL中的这些工具有一个宏观上的概念。
一、触发器
1.1 触发器简介
触发器是一种比较特殊的存储过程它的执行不是由程序调用也不是手工调用而是通过事件来进行触发比如说 对一张表 进行增删改去激活它的执行。
触发器经常应用在加强数据完整性和业务规则中如当一个学生表中添加了一个学生信息时那么对应的学生数目肯定会有所改变。像这样的情况我们就可以针对学生表创建一个触发器以确保每次增加一个学生记录时就执行一次关于学生总数的计算操作从而确保学生总数与记录数的一致性。
1.2 触发器的创建
#语法要求
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件ON 表名 FOR EACH ROWBEGIN触发器程序体;END #说明
触发器名称 参考索引命名||表命名
BEFORE|AFTER 触发器时机
INSERT DELETE UPDATE 触发的事件
ON 表名 在哪张表上建立触发器
FOR EACH ROW 触发器执行间隔策略
1.3 示例演示
#示例2职员表 (工号,姓名,性别,年龄) 工资表 (工资编号,姓名,工资5000)
对职工进行添加时 工资表中也要体现当前职工的信息
对职工进行修改时 工资表中也要一并修改当前职工的信息
对职工进行解聘时 工资表中也要一并删除当前员工的工资信息
#创建职工表
CREATE TABLE tab1(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20),sex ENUM(m,f),age INT
);
#创建工资表
CREATE TABLE tab2(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20),salary DOUBLE(10,2)
);
#创建触发器1 INSERT 对职工进行添加时 工资表中也要体现当前职工的信息
CREATE TRIGGER tab1_insert_trigger AFTER INSERT ON tab1FOR EACH ROWBEGININSERT INTO tab2 VALUES (NULL,new.name,5000);END;测试用户新增
INSERT INTO tab1 VALUES (1,AA,f,18);
新增完成后我们检查tab2会发现有变化#创建触发器2 UPDATE 对职工进行修改时 工资表中也要刷新当前职工的信息
CREATE TRIGGER tab1_update_triggerAFTER UPDATE ON tab1FOR EACH ROWBEGINUPDATE tab2 SET name new.name WHERE name old.name;END;测试用户修改
UPDATE tab1 SET name FF WHERE name BB;
修改完成后我们检查tab2会发现有变化#创建触发器3 DELETE 对职工进行删除时 工资表中也要刷新当前职工信息
CREATE TRIGGER tab1_delete_triggerAFTER DELETE ON tab1FOR EACH ROWBEGINDELETE FROM tab2 WHERE name old.name;END;
触发器的实际使用也可以参考这篇文章https://blog.csdn.net/AXDRXS/article/details/135900340
二、存储过程
2.1 什么是存储过程
存储过程指的是事先经过了编译并以对象形式存储在数据库中的一段sql语句的集合。调用存储过程可以简化应用开发人员的很多工作减少了数据在数据库和应用服务器之间的传输对于提高数据的处理能力具有很强的好处。
存储过程和存储函数的区别
1、存储过程可以不设返回值而存储函数必须要设置返回值
2、存储过程的参数 IN OUT INOUT 类型而存储函数的参数只能是IN
2.2 优势与应用场景
1、存储过程只在创建时编译一次而常规的SQL命令每执行一次就会编译一次所以它能够提高执行效率
2、简化复杂操作支持封装
3、复用性极强
4、安全性高可以指定其使用权
场景在并发量较小的情况下很少使用。在并发量高的情况下多采用存储过程或存储函数
2.3 存储过程的创建与使用
CREATE PROCEDURE 函数名(形式参数列表)BEGIN函数体END; 参数列表类型 [IN OUT INOUT] 参数名 类型
IN 输入参数
OUT 输出参数
INOUT 该参数在调用方法是充当了实参变量,在方法调用完毕返回结果时又充当了接受返回值的变量
CALL函数名(实际参数列表)
各参数类型所实现的存储过程
无参数无返回的存储过程
#查询当前学生人数
CREATE PROCEDURE p1()BEGINSELECT COUNT(*) FROM student;END;
有参数无返回的存储过程
#创建一张测试表
CREATE TABLE t1(id int,name varchar(50)
);CREATE PROCEDURE autoinsert(IN num INT)BEGINDECLARE i INT DEFAULT 1; WHILE(inum)DOINSERT INTO t1 VALUES(i,md5(i));SET i i1;END WHILE;END;
无参数有返回的存储过程
#查询当前学生人数
CREATE PROCEDURE p2(OUT num INT)BEGINSELECT COUNT(*) INTO num FROM student;END;
有参数有返回的存储过程
#统计指定班级编号的学生人数
CREATE PROCEDURE p3(IN num1 INT,OUT num2 INT)BEGIN SELECT COUNT(*) INTO num2 FROM student WHERE student.GradeID num1;END;
INOUT
CREATE PROCEDURE p4(INOUT num INT)BEGIN IF(num IS NOT NULL) thenSET num num1;ELSESELECT 100 INTO NUM;END IF; END; CREATE PROCEDURE p5(INOUT num INT)BEGIN SELECT COUNT(*) INTO num FROM student WHERE student.GradeID num;END;
三、存储函数
3.1 什么是存储函数
MySQL存储函数自定义函数函数一般用于计算和返回一个值可以将经常需要使用的计算或功能写成一个函数。函数和存储过程类似。
存储过程和存储函数的区别
1、存储过程可以不设返回值而存储函数必须要设置返回值
2、存储过程的参数 IN OUT INOUT 类型而存储函数的参数只能是IN
3.2 存储函数的创建与使用
在MySql中创建存储函数使用的关键字 是 CREATE FUNCTION 其基本语法如下
CREATE FUNCTION 函数名 ([参数名 参数类型,参数名 参数类型....])RETURNS type[characteristic ...]BEGINroutine_bodyEND;参数说明RETURNS type 返回值的类型characteristic 指定存储函数的特性routine_body SQL代码内容
调用存储函数
在MySQL中存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下
SELECT NOW();SELECT 函数名(实际参数列表)
删除存储函数
DROP FUNCTION myf2;
3.3 实例演示
注意事项:MySQL开启bin-log后调用存储过程或者函数以及触发器时会出现错误号为1418的错误
如何解决此问题:信任子程序的创建者禁止创建、修改子程序时对SUPER权限的要求:
在客户端上执行 SET GLOBAL log_bin_trust_function_creators 1;
#1 无参数有返回值
#统计student表中行数
CREATE FUNCTION myf1() RETURNS INT
BEGINDECLARE num INT DEFAULT 0; SELECT COUNT(*) INTO num FROM student;RETURN num;
END;
#2 有参数有返回值
#根据学生姓名查询学生学号
CREATE FUNCTION myf2(stuName VARCHAR(20)) RETURNS INT
BEGINDECLARE num INT; SELECT id INTO num FROM student WHERE name stuName;RETURN num;
END;#调用 使用 SELECT命令
四、游标
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。遍历 使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。 游标充当指针的作用。 尽管游标能遍历结果中的所有行但他一次只指向一行。 用SQL语言从数据库中检索数据后结果放在内存的一块区域中且结果往往是一个含有多个记录的集合。 游标机制允许用户逐行地访问这些记录按照用户自己的意愿来显示和处理这些记录。
优点 允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作而不是对整个行集合执行同一个操作。 提供对基于游标位置的表中的行进行删除和更新的能力。 游标实际上作为面向集合的数据库管理系统RDBMS和面向行的程序设计之间的桥梁使这两种处理方式通过游标沟通起来。
原理
游标就是把数据按照指定要求提取出相应的数据集然后逐条进行数据处理。
4.1 如何使用游标
1、声明游标
DECLARE cursor_name CURSOR FOR select_statement (table)
#声明游标
2、声明游标后想从游标中提取数据必须要打开游标
xxxxxxxxxx OPEN cursor_name
注意当我们执行了打开游标操作后游标并不是指向第一条记录而是指向第一条记录的前边。
在程序内一个游标可以打开多次用户打开游标后其他用户或程序可能正在更新数据所以有时候可能导致用户每次打开游标时显示的结果不同。
3、使用游标
当用户顺利打开游标后,可以使用 FETCH...INTO 语句进行数据的读取
# 这条语句用户指定的打开游标并读取下一行且前进游标指针。
FETCH cursor_name INTO value1[,value2.....]
4关闭游标
游标使用完毕后要及时关闭在MySQL中,使用CLOSE关键字关闭游标,其语法和格式
CLOSE cursor_name;
CLOSE 用于释放游标所有的内部资源和内存因此每个游标再不需要的时候都需要及时关闭。
关闭的游标 如果没有重新打开则不能二次使用。但是已经声明过的游标不需要再次声明了用OPEN语句打开它就可以如果忘记关闭MySql将会在到达END语句时自动关闭。游标关闭后就不能使用FETCH去进行游标的遍历了。
4.2 游标的应用
示例说明
编写两个表 sys_user和user表编写其存储过程。当两个表id值一样的时候,将user表内的name字段值同步成 sys_user表的name字段值。
#1创建sys_user表并注入一些数据
CREATE TABLE sys_user(id INT(11) NOT NULL AUTO_INCREMENT,username VARCHAR(200) NOT NULL,PRIMARY KEY(id)
)ENGINEInnoDB AUTO_INCREMENT 4 DEFAULT CHARSET utf8;INSERT INTO sys_user (username) VALUES (AA),(BB),(CC),(DD),(EE),(FF),(GG);CREATE TABLE user(id INT(11) DEFAULT NULL,name VARCHAR(200) DEFAULT NULL
)ENGINEInnoDB DEFAULT CHARSET utf8;INSERT INTO user VALUES (1,OO),(2,PP),(3,QQ),(4,RR),(5,SS),(6,TT),(7,UU);
创建存储过程 user_test并创建游标 cur_test
CREATE PROCEDURE user_test()
BEGIN-- 定义变量DECLARE sys_user_id BIGINT;DECLARE sys_user_name VARCHAR(11);DECLARE done INT;-- 创建游标并存储数据DECLARE cur_test CURSOR FOR SELECT id AS user_id,user_name AS sys_user_name FROM sys_user;-- 游标中的内容执行完后将 done 设置为 1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1;-- 打开游标OPEN cur_test;-- 执行循环posLoop:LOOP-- 判断是否结束循环IF done1 THENLEAVE posLoop;END IF;-- 取游标中的值FETCH cur_test INTO sys_user_id,sys_user_name;-- 执行更新操作UPDATE user SET NAMEsys_user_name WHERE idsys_user_id;END LOOP posLoop;-- 释放游标 CLOSE cur_test;
END;
