wordpress导入项目,网站优化人员,简易h5制作开发,win没有wordpress目录 存储过程介绍技术背景存储过程的作用与优势存储过程跟自定义函数很像。它们的区别是#xff1a; 存储过程的缺点存储过程的特性基本存储过程使用1.创建语法语法说明#xff1a;使用案例1.创建获取新闻类别数量的存储过程2.创建获取指定新闻类别ID下新闻数量的存储过程 2… 目录 存储过程介绍技术背景存储过程的作用与优势存储过程跟自定义函数很像。它们的区别是 存储过程的缺点存储过程的特性基本存储过程使用1.创建语法语法说明使用案例1.创建获取新闻类别数量的存储过程2.创建获取指定新闻类别ID下新闻数量的存储过程 2.调用语法示例 3.查看4.删除 存储过程中的语法构造1.变量1.1系统变量查看系统变量设置系统变量示例备注 1.2用户定义变量语法使用演示 1.3局部变量局部变量的声明示例局部变量的赋值SET :SELECT INTO 2.条件判断2.1if判断语法示例 2.2 case语法一语法二示例 3.循环结构3.1while循环语法示例计算从1累加到n的值n为传入的参数值 3.2repeat循环语法示例计算从1累加到n的值n为传入的参数值 3.3loop循环语法示例1计算从1累加到n的值n为传入的参数值示例2计算从1到n之间的偶数累加的值n为传入的参数值 3.4游标循环遍历语法示例定义存储过程完成如下需求问题 4.条件处理程序Handler语法示例 存储函数语法示例计算从1累加到n的值n为传入的参数值。 触发器trigger触发器类型语法示例INSERT型触发器update型触发器delete型触发器 存储过程介绍
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来这个过程经编译和优化后存储在数据库服务器中因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时只需调用“CALL 存储过程名字”即可自动完成。类似调用Java里面的函数
技术背景 我们通常使用的SQL 语句都是针对一个表或几个表的单条 SQL 语句但是在数据库的实际操作中并非所有操作都那么简单有时候一个完整的操作需要多条 SQL 语句处理多个表才能完成。 例如为了确认学生能否毕业需要同时查询学生档案表、成绩表和综合表。此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。 常用操作数据库的 SQL 语句在执行的时候需要先编译然后执行。存储过程则采用另一种方式来执行 SQL 语句。一个存储过程是一个可编程的函数它在数据库中创建并保存一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时存储过程尤为合适。
存储过程的作用与优势
封装性。 存储过程被创建后可以在程序中被多次调用而不必重新编写该存储过程的 SQL 语句并且数据库专业人员可以随时对存储过程进行修改而不会影响到调用它的应用程序源代码可增强 SQL 语句的功能和灵活性。 存储过程可以用流程控制语句编写有很强的灵活性可以完成复杂的判断和较复杂的运算可减少网络流量。 这个不难理解以往我们使用单条sql每次调用都需要发起一次IO请求将完整的IO请求发送过去但是现在只需要【CALL 存储过程】就可以了。确切的说由于存储过程是在服务器端运行的且执行速度快因此当客户计算机上调用该存储过程时网络中传送的只是该调用语句从而可降低网络负载高性能。 存储过程执行一次后产生的二进制代码就驻留在缓冲区在以后的调用中只需要从缓冲区中执行二进制代码即可从而提高了系统的效率和性能提高数据库的安全性和数据的完整性。 使用存储过程可以完成所有数据库操作并且可以通过编程的方式控制数据库信息访问的权限
存储过程跟自定义函数很像。它们的区别是
存储过程实现的功能要复杂一些而函数的针对性更强。存储过程可以返回多个值函数只能有一个返回值。存储过程一般独立的来执行而函数可以作为其他SQL语句的组成部分实现出来。
存储过程的缺点
如果使用大量的存储过程那么使用这些存储过程的每个连接的内存使用量将大大增加。此外如果在存储过程中过度使用大量的逻辑操作那么CPU的使用率也在增加因为MySQL数据库最初的设计就侧重于高效的查询而不是逻辑运算。存储过程的构造使得开发具有了复杂的业务逻辑的存储过程变得困难。很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是MySQL不提供调试存储过程的功能。开发和维护存储过程都不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用程序开发和维护阶段的问题。对数据库依赖程度较高移值性差。
存储过程的特性
有输入输出参数可以声明变量有if/else, case,while等控制语句通过编写存储过程可以实现复杂的逻辑功能函数的普遍特性模块化封装代码复用速度快只有首次执行需经过编译和优化步骤后续被调用可以直接执行省去以上步骤
基本存储过程使用
1.创建
语法
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
beginsql语句
end 自定义的结束符合语法说明
上面的CREATE PROCEDUREBEGIN和END是固定的储存名存储过程的名字。我们在调用的时候就是使用CALL 过程名调用。另外尽量避免过程名跟Mysql已存在的函数名相同导致冲突参数列表-参数类型存储过程可以没有参数也可以有多个参数参数的声明是【参数名参数类型】声明的。 存储过程中的参数分别是 inoutinout三种类型 in代表输入参数默认情况下为in参数表示该参数的值必须由调用程序指定。ou代表输出参数表示该参数的值经存储过程计算后将out参数的计算结果返回给调用程序。inout代表即时输入参数又是输出参数表示该参数的值即可有调用程序制定又可以将inout参数的计算结果返回给调用程序。
使用案例
1.创建获取新闻类别数量的存储过程
CREATE PROCEDURE getAllNewsTypeCount() BEGINSELECTtname,count FROMnews_category cJOIN ( SELECT ntid, count( ntid ) count FROM news_detail GROUP BY ntid ) d ON c.tid d.ntid ORDER BYcount DESC;
END 2.创建获取指定新闻类别ID下新闻数量的存储过程
CREATE PROCEDURE getNewsTypeCountByTid(in tid int) BEGINSELECTtname,count FROMnews_category cJOIN ( SELECT ntid, count( ntid ) count FROM news_detail where ntidtid ) d ON c.tid d.ntid ORDER BYcount DESC;
END ;2.调用
语法
CALL 存储过程名称([参数]);示例
call getAllNewsTypeCount();call getAllNewsTypeCount();3.查看
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA 数据库名称;-- 查询整个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;# 查询存储过程的状态信息
show procedure status;4.删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;存储过程中的语法构造
存储过程是可以编程的意味着可以使用变量、表达式、控制语句来完成比较复杂的功能
1.变量
1.1系统变量
系统变量时MySQL服务器提供不是用户定义的属于服务器层面。可分为全局变量GLOBAL、会话变量SESSION。全局变量在所有会话中有效会话变量仅在当前会话中有效。
查看系统变量
-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;-- 可以通过like模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE ...;-- 查看指定变量的值
SELECT [SESSION. | GLOBAL.] 系统变量名;设置系统变量
SET [SESSION | GLOBAL] 系统变量名 值;
SET [SESSION. | GLOBAL.]系统变量名 值;示例
-- 变量系统变量
-- 查看系统变量
show session variables;
show session variables like auto%;
show global variables like auto%;
select global.autocommit;-- 设置系统变量
set session autocommit 1;
set global autocommit 1;
set global.autocommit 1;备注
如果没有指定SESSION/GLOBAL默认是SESSION会话变量。mysql服务重新启动之后所设置的全局参数会失效要想不失效可以在/etc/my.cnf中设置。
1.2用户定义变量
用户定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用“变量名”使用就可以。其作用域为当前连接会话。
语法
SET var_name expr[, var_name expr] ...;
SET var_name : expr[, var_name : expr] ...;SELECT var_name : expr[, var_name : expr] ...;
SELECT 字段名 INTO var_name FROM 表名;使用
SELECT var_name;演示
-- 变量用户定义变量
-- 赋值
set myname itcast;
set myage : 10;
set mygender : 女, myhobby : mysql;select mycolor : red;
select count(*) into mycount from tb_user;-- 使用
select myname, myage, mygender;select mycolor, mycount;用户定义的变量无需对其进行声明或初始化只不过获取的值为NULL
1.3局部变量
局部变量是根据需要定义的在局部生效的变量访问之前需要DECLARE声明。可用作存储过程内的局部变量和输入参数局部变量的范围是在其内声明的BEGIN … END块。
局部变量的声明
DECLARE 变量名[,...] type [DEFAULT value]声明变量的时候可以一次性声明多个使用逗号隔开。
示例
CREATE PROCEDURE calculate ()
BEGINDECLARE num1 INT;DECLARE num2 INT DEFAULT 1;SELECT num1 num2;
END注意上面示例num1是没有默认值的所以SELECT num1 num2;是一个null值。
局部变量的赋值
变量的赋值有2种方式分别为SET赋值以及SELECT INTO赋值。
SET :
直接赋值使用SET关键字可以赋常量或者是表达式具体语法如下
-- 注意一次可以给多个变量赋值中间使用逗号隔开。
SET 变量名 变量值 [,变量名 变量值] ...示例 CREATE PROCEDURE calculate ()BEGINDECLARE num1 INT;DECLARE num2 INT DEFAULT 1;SET num12,num23;SELECT num1 num2;ENDSELECT INTO
语法格式如下 SELECT column | 聚合函数 INTO 变量名;示例
CREATE PROCEDURE getNewsTypeCountByTid(in tid int) BEGINDECLARE count int;DECLARE num INT DEFAULT 1;SELECT count( ntid ) into count FROM news_detail where ntidtid;select count num;END ;2.条件判断
2.1if判断
语法
IF 条件1 THEN...
ELSEIF 条件2 THEN -- 可选...
ELSE -- 可选...
END IF;示例
DROP PROCEDURE IF EXISTS getNewsHotStatus;
CREATE PROCEDURE getNewsHotStatus(in tid int,out hotStatus VARCHAR(10)) BEGINDECLARE count int;SELECT count( ntid ) into count FROM news_detail where ntidtid;IF count10 THENset hotStatus火爆;ELSEIF count5 THEN set hotStatus一般;ELSE set hotStatus冷门;END IF;
END ;set hotStatus;
call getNewsHotStatus(6,hotStatus);
select hotStatus;2.2 case
语法一
CASE case_valueWHEN when_value1 THEN statement_list1[WHEN when_value2 THEN statement_list2]...[ELSE statement_list]
END CASE;语法二
CASEWHEN search_condition1 WHEN statement_list1[WHEN search_condition2 WHEN statement_list2]...[ELSE statement_list]
END CASE;示例
-- 创建存储过程
create procedure getQuarter(in month int)
begindeclare result varchar(10);casewhen month 1 and month 3 thenset result : 第一季度;when month 4 and month 6 thenset result : 第二季度;when month 7 and month 9 thenset result : 第三季度;when month 10 and month 12 thenset result : 第四季度;else set result : 非法传输;end case;select concat(您输入的月份为,month,所属的季度为,result);
end;-- 调用
call getQuarter(8);3.循环结构
3.1while循环
while循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。
语法
# 先判定条件如果条件为true则执行逻辑否则不执行逻辑
WHILE 条件 DOSQL逻辑...
END WHILE;示例计算从1累加到n的值n为传入的参数值
-- A.定义局部变量记录累加之后的值
-- B.每循环一次就会对n进行减1如果n减到0则退出循环create procedure whileSum(in n int)
begindeclare total int default 0;while n 0 do set total : total n;set n : n - 1;end while;select total;
end;-- 调用
call whileSum(100);3.2repeat循环
repeat是有条件的循环控制语句当满足条件的时候退出循环
语法
# 先执行一次逻辑然后判定逻辑是否满足如果满足则退出。如果不满足则继续下一次循环
REPEAT SQL逻辑...
UNTIL 条件
END REPEAT;示例计算从1累加到n的值n为传入的参数值
-- A.定义局部变量记录累加之后的值
-- B.每循环一次就会对n进行减1如果n减到0则退出循环create procedure repeatSum(in n int)
begindeclare total int default 0;repeat set total : total n;set n : n - 1;until n 0end repeat;select total;
end;-- 调用
call repeatSum(100);3.3loop循环
LOOP实现简单的循环如果不在SQL逻辑中增加退出循环的条件可以用其来实现简单的死循环。LOOP可以配合以下两个语句使用 LEAVE配合循环使用退出循环。ITERATE必须用在循环中作用是跳过当前循环剩下的语句直接进入下一次循环。
语法
[begin_label:] LOOPSQL逻辑...
END LOOP [end_label];-- 退出指定标记的循环体
LEAVE label;-- 直接进入下一次循环
ITERATE label;示例1计算从1累加到n的值n为传入的参数值
-- loop
-- A.定义局部变量记录累加之后的值
-- B.每循环一次就会对n进行减1如果n减到0则退出循环
create procedure loopSum(in n int)
begindeclare total int default 0;sum:loopif n 0 then leave sum;end if;set total : total n;set n : n - 1;end loop sum;select total;
end;-- 调用
call loopSum(100);示例2计算从1到n之间的偶数累加的值n为传入的参数值
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ---- leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. -------- iterate xxcreate procedure evenSum(in n int)
begindeclare total int default 0;sum:loopif n0 thenleave sum;end if;if n%2 1 thenset n : n - 1;iterate sum;end if;set total : total n;set n : n - 1;end loop sum;select total;
end;-- 调用
call evenSum(100);3.4游标循环遍历
游标cursor是用来存储查询结果集的数据类型在存储过程和函数中可以使用游标对结果集进行循环的处理。
语法
游标的使用包括游标的声明、OPEN、FETCH和CLOSE其语法分别如下
#声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
#打开游标
OPEN 游标名称;
#获取游标记录
FETCH 游标名称 INTO 变量[,变量];
#关闭游标
CLOSE 游标名称;
#释放游标
DEALLOCATE PREPARE 游标名称示例定义存储过程完成如下需求
根据传入的参数uage来查询用户表tb_user中所有用户年龄小于等于uage的用户姓名(name)和专业(profession)并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
-- 逻辑实现
-- A.声明游标存储查询结果集
-- B.准备创建表结构
-- C.开启游标
-- D.循环获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标create procedure loopSave(in uage int)
begindeclare uname varchar(100);declare uprofession varchar(100);declare u_cursor cursor for select name,profession from tb_user where age uage;drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname, uprofession;insert into tb_user_pro values(null, uname, uprofession);end while;close u_cursor;
end;-- 调用
call loopSave(40);声明变量在声明游标之前
问题
上述的功能虽然我们实现了但是逻辑并不完善而且程序执行完毕获取不到数据数据库还报错。 接下来我们就需要来完成这个存储过程并且解决这个问题。
要想解决这个问题就需要通过MySQL中提供的条件处理程序Handler来解决。
4.条件处理程序Handler
条件处理程序Handler可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
语法
DECLARE handler_action HANDLER FOR condition_value[,condition_value]... statement;handler_action CONTINUE继续执行当前程序EXIT终止执行当前程序 condition_value SQLSTATE sqlstate_value状态码如02000 SQLWARNING所有以01开头的SQLSTATE代码的简写NOT FOUND所有以02开头的SQLSTATE代码的简写SQLEXCEPTION所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写
示例
create procedure loopHandlerSave(in uage int)
begindeclare uname varchar(100);declare uprofession varchar(100);declare u_cursor cursor for select name,profession from tb_user where age uage;-- declare exit handler for SQLSTATE 02000 close u_cursor;declare exit handler for not found close u_cursor;drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname, uprofession;insert into tb_user_pro values(null, uname, uprofession);end while;close u_cursor;
end;call loopHandlerSave(40);存储函数
存储函数是有返回值的存储过程存储函数的参数只能是IN类型的。
语法
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristics ...]
BEGIN-- SQL语句RETURN ...;
END;characteristics的说明
DETERMINISTIC相同的输入参数总是产生相同的结果NO SQL不包含SQL语句READS SQL DATA包含读取数据的语句但不包含写入数据的语句。
示例计算从1累加到n的值n为传入的参数值。
-- 存储函数
-- 从1到n的累加
create function funCalc(n int)
returns int deterministic
begindeclare total int default 0;while n 0 doset total : total n;set n : n - 1;end while;return total;
end;-- 调用存储函数并显示结果
select funCalc(100);触发器trigger 触发器是与表有关的数据库对象指在insert/update/delete之前或之后触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性日志记录数据校验等操作。 使用别名OLD和NEW来引用触发器中发生变化的记录内容这与其他的数据库是相似的。现在触发器还只支持行级触发不支持语句级触发。
触发器类型
触发器类型NEW和OLDINSERT型触发器NEW表示将要或者已经新增的数据UPDATE型触发器OLD表示修改之前的数据NEW表示将要或已经修改后的数据DELETE型触发器OLD表示将要或已经删除的数据 行级触发比如执行一条update语句影响了5行数据此时涉及的触发器触发5次该触发器称为行级触发器。 语句级触发比如执行一条update语句不管影响了多少行数据此时涉及的触发器触发1次该触发器称为语句级触发器。
语法
#创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGINtrigger_stmt;
END;
#查看
SHOW TRIGGERS;
#删除
DROP TRIGGER [schema_name.]trigger_name; -- 如果没有指定schema_name默认为当前数据库示例
通过触发器记录tb_user表的数据变更日志将变更日志插入到日志表user_logs中包含增加修改删除
create table user_logs(id int(11) not null auto_increment,operation varchar(20) not null comment操作类型,insert/update/delete,operate_time datetime not null comment操作时间,operate_id int(11) not null comment操作的ID,operate_params varchar(50) not null comment操作参数,primary key(id)
)engineinnodb default charsetutf8;INSERT型触发器
-- 创建insert型触发器
create trigger tb_user_insert_trigger after insert on tb_user for each row
begininsert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null, insert, now(), new.id, concat(插入的数据内容为id,new.id,, name,new.name,, phone,new.phone,, email,new.email,, profession,new.profession));
end;-- 查看触发器
show triggers;-- 删除触发器
drop trigger tb_user_insert_trigger;-- 向tb_user表中插入数据
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
values(25, 二皇子, 18908823412, ehzemail.com, 软件工程, 23, 1, 1, now());
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
values(26, 三皇子, 18908823413, shzemail.com, 软件工程, 22, 1, 1, now());update型触发器
-- 修改数据触发器
create trigger tb_user_update_triggerafter update on tb_user for each row
begininsert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null, update, now(), new.id, concat(更新之前的数据内容为id,old.id,, name,old.name,, phone,old.phone,, email,old.email,, profession,old.profession, | 更新之后的数据内容为id,new.id,, name,new.name,, phone,new.phone,, email,new.email,, profession,new.profession));
end;-- 查看触发器
show triggers;-- 修改tb_user表中的数据
update tb_user set age 32 where id 25;
update tb_user set profession 计算机科学与技术 where id 26;
update tb_user set profession 会计学 where id 5;delete型触发器
-- 删除数据触发器
create trigger tb_user_delete_trigger after delete on tb_user for each row
begininsert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null, delete, now(), old.id, concat(删除之前的数据内容为id,old.id,, name,old.name,, phone,old.phone,, email,old.email,, profession,old.profession));
end;-- 查看触发器
show triggers;-- 删除tb_user表中的数据
delete from tb_user where id 25;
delete from tb_user where id 26;
