深南花园裙楼+网站建设,建立html网站,免费服务器有哪些,网站开发者工具解读存储过程 存储函数存储过程基本语法变量系统变量用户定义变量局部变量if判断参数case判断while循环repeat循环loop循环游标条件处理程序存储函数存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的工作#xff0c;减…
存储过程 · 存储函数存储过程基本语法变量系统变量用户定义变量局部变量if判断参数case判断while循环repeat循环loop循环游标条件处理程序存储函数存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的工作减少数据在数据库和应用服务器之间的传输能够提高数据处理的效率。存储过程思想上很简单就是数据库 SQL 语言层面的代码封装与重用。
特点
封装复用可以接收参数也可以返回数据减少网络交互提升效率
基本语法
创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGINsql语句1;...sql语句n;
END;在命令行中执行创建存储过程的SQL时需要通过关键字 delimiter 指定SQL语句的结束符。 调用存储过程 CALL 存储过程名([参数]);
查询指定数据库的存储过程及状态信息 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA 数据库名;
查询某个存储过程的定义 SHOW CREATE PROCEDURE 存储过程名;
删除数据库名 DROP PROCEDURE [IF EXISTS] 存储过程名称;
示例设 student表 在 test1库 中
CREATE TABLE student(id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键ID,name VARCHAR(10) COMMENT 姓名,stuno VARCHAR(10) COMMENT 学号
) COMMENT 学生表;INSERT INTO student VALUES(null, 黛绮丝, 2000100101), (null, 谢逊, 2000100102), (null, 殷天正, 2000100103), (null, 韦一笑, 2000100104);# 创建存储过程
CREATE PROCEDURE p1()
BEGINSELECT COUNT(*) FROM student;
END;# 调用存储过程
CALL p1();# 查询test1数据库的存储过程及状态信息
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA test1;# 查询p1存储过程的定义
SHOW CREATE PROCEDURE p1;# 删除存储过程
DROP PROCEDURE IF EXISTS p1;如果是在命令行中创建存储过程
# 先通过关键字 delimiter 指定SQL语句的结束符为 “$$”可随意指定
DELIMITER $$# 然后可进行存储过程创建
CREATE PROCEDURE p1()
BEGINSELECT COUNT(*) FROM student;
END$$# 最后将结束符再修改为“;”
DELIMITER ;变量
在MySQL中变量分为三种类型系统变量、用户定义变量、局部变量。
系统变量
系统变量是MySQL服务器提供不是用户定义的属于服务器层面。分为全局变量GLOBAL、会话变量SESSION。
查看全局所有系统变量 SHOW GLOBAL VARIABLES;
查看当前会话所有系统变量 SHOW [SESSION] VARIABLES;
通过模糊匹配方式查找变量 SHOW [SESSION或GLOBAL] VARIABLES LIKE 变量;
查看指定变量的值全局 SELECT GLOBAL.系统变量名;
查看指定变量的值当前会话 SELECT [SESSION.]系统变量名;
设置全局系统变量 方式一SET GLOBAL.系统变量名 值; 方式二SET GLOBAL 系统变量名 值;
设置当前会话系统变量 方式一SET [SESSION.]系统变量名 值; 方式二SET [SESSION] 系统变量名 值; mysql服务重新启动之后所设置的全局参数会失效要想不失效可以在 /etc/my.cnf 中配置。 用户定义变量
用户定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用“变量名”使用就可以。其作用域为当前连接。
赋值 方式一SET 变量1 值, 变量2 值, ..., 变量n 值; 方式二SET 变量1 : 值, 变量2 : 值, ..., 变量n : 值; 方式三SELECT 变量1 : 值, 变量2 : 值, ..., 变量n : 值; 方式四SELECT 查询结果 INTO 变量 FROM 表名;
使用或查询 SELECT 变量名; 注用户定义的变量无需对其进行声明或初始化只不过获取到的值为NULL。 示例
# 赋值示例
SET myname itcast;
SET myage : 10;
SET mygender : 男, myhobby : java;
SELECT mycolor : red;
SELECT COUNT(*) INTO mycount FROM tb_user;# 使用或查询
SELECT myname, myage, mygender, myhobby;
SELECT mycolor, mycount;局部变量
局部变量是根据需要定义的在局部生效的变量访问之前需要DECLARE声明。可用作存储过程内的局部变量和输入参数局部变量的范围是在其内声明的BEGIN … END块。
声明 DECLARE 变量名 变量类型 [DEFAULT 初始值];
赋值 方式一SET 变量1 值, 变量2 值, ..., 变量n 值; 方式二SET 变量 : 值; 方式三SELECT 查询结果 INTO 变量 FROM 表名;
以基本语法里的student表为例
# 局部变量声明与赋值
CREATE PROCEDURE p2()
BEGINDECLARE stu_count INT DEFAULT 0;SELECT COUNT(*) INTO stu_count FROM student;SELECT stu_count;
END;CALL p2();if判断
if 用于做条件判断。
语法
IF 条件1 THENsql语句1;...sql语句n;
ELSEIF 条件2 THENsql语句1;...sql语句n;
ELSEsql语句1;...sql语句n;
END IF;在if条件判断的结构中ELSEIF 结构可以有多个也可以没有。ELSE结构可以有也可以没有。
示例
/*
根据定义的分数score变量判定当前分数对应的分数等级。
score 85分等级为优秀。
score 60分 且 score 85分等级为及格。
score 60分等级为不及格。
*/
CREATE PROCEDURE p3()
BEGINDECLARE score INT DEFAULT 58;DECLARE result VARCHAR(10);IF score 85 THENSET result : 优秀;ELSEIF score 60 THENSET result : 及格;ELSESET result : 不及格;END IF;SELECT result;
END;CALL p3();上述的需求已经实现了但是存在一些问题比如score 分数是在存储过程中定义死的而且最终计算出来的分数等级也仅仅是最终查询展示出来而已。 怎样把score分数动态的传递进来计算出来的分数等级作为返回值返回 可以通过参数来解决上述问题。
参数
参数的类型主要分为三种IN、OUT、INOUT。
类型含义备注IN该类参数作为输入也就是调用时需要传入值默认OUT该类参数作为输出也就是该参数可以作为返回值INOUT既可以作为输入参数也可以作为输出参数
语法
CREATE PROCEDURE 存储过程名称([参数类型 参数名 属性类型])
BEGINsql语句;...
END;示例一
/*
根据传入参数score判定当前分数对应的分数等级并返回。
score 85分等级为优秀。
score 60分 且 score 85分等级为及格。
score 60分等级为不及格。
*/
CREATE PROCEDURE p4(IN score INT, OUT result VARCHAR(10))
BEGINIF score 85 THENSET result : 优秀;ELSEIF score 60 THENSET result : 及格;ELSESET result : 不及格;END IF;
END;# 定义用户变量 result 来接收返回的数据用户变量可以不用声明
CALL p4(98, result);SELECT result;示例二
# 将传入的200分制的分数换算成百分制然后返回。
CREATE PROCEDURE p5(INOUT score DOUBLE)
BEGINSET score : score * 0.5;
END;SET score 198;CALL p5(score);SELECT score;case判断
case结构及作用和流程控制函数类似。有两种语法格式。
语法一
CASE case_valueWHEN 值1 THEN sql语句;...WHEN 值n THEN sql语句;[ELSE sql语句;]
END CASE;含义当case_value的值为 值n 时执行对应后面的sql语句否则就执行else后面的SQL语句。
语法二
CASEWHEN 条件1 THEN sql语句;...WHEN 条件n THEN sql语句;[ELSE sql语句;]
END CASE;示例
/*
根据传入的月份判定月份所属的季节要求采用case结构。
1-3月份为第一季度
4-6月份为第二季度
7-9月份为第三季度
10-12月份为第四季度
*/
CREATE PROCEDURE p6(IN my_month INT)
BEGINDECLARE result VARCHAR(10);CASEWHEN my_month 1 AND my_month 3 THENSET result : 第一季度;WHEN my_month 4 AND my_month 6 THENSET result : 第二季度;WHEN my_month 7 AND my_month 9 THENSET result : 第三季度;WHEN my_month 10 AND my_month 12 THENSET result : 第四季度;ELSESET result : 非法参数;END CASE;SELECT CONCAT(您输入的月份为: , my_month, , 所属的季度为: ,result);END;CALL p6(16);如果判定条件有多个多个条件之间可以使用 and 或 or 进行连接。 while循环
while循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。
WHILE 条件 DOsql逻辑;
END WHILE;含义先判定条件如果条件为true则执行逻辑否则不执行逻辑。
示例
# 计算从1累加到n的值n为传入的参数值。
CREATE PROCEDURE p7(IN n INT)
BEGINDECLARE total INT DEFAULT 0;WHILE n0 DOSET total : total n;SET n : n - 1;END WHILE;SELECT total;
END;CALL p7(100);repeat循环
repeat是有条件的循环控制语句当满足until声明的条件的时候退出循环。
语法
REPEATSQL逻辑;
UNTIL 条件
END REPEAT;先执行一次逻辑然后判定UNTIL条件是否满足如果满足则退出。如果不满足则继续下一次循环。
示例
# 计算从1累加到n的值n为传入的参数值。
CREATE PROCEDURE p8(IN n INT)
BEGINDECLARE total INT DEFAULT 0;REPEATSET total : total n;SET n : n - 1;UNTIL n 0END REPEAT;SELECT total;
END;CALL p8(10);CALL p8(100);loop循环
LOOP 实现简单的循环如果不在SQL逻辑中增加退出循环的条件可以用其来实现简单的死循环。
LOOP可以配合以下两个语句使用
LEAVE配合循环使用退出循环。ITERATE只能在循环中使用作用是跳过当前循环剩下的语句直接进入下一次循环。
基础语法
[标记:] LOOPSQL逻辑;
END LOOP [标记];退出指定标记的循环体 LEAVE 标记;
直接进入下一次循环 ITERATE 标记;
示例一
# 计算从1累加到n的值n为传入的参数值。
CREATE PROCEDURE p9(IN n INT)
BEGINDECLARE total INT DEFAULT 0;sum:LOOPIF n 0 THENLEAVE sum;END IF;SET total : total n;SET n : n - 1;END LOOP sum;SELECT total;
END;CALL p9(100);示例二
# 计算从1到n之间的偶数累加的值n为传入的参数值。
CREATE PROCEDURE p10(IN n INT)
BEGINDECLARE total INT DEFAULT 0;sum:LOOPIF n 0 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 p10(100);游标
游标CURSOR是用来存储查询结果集的数据类型在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE。
声明游标 DECLARE 游标名称 CURSOR FOR 查询语句; 要先声明普通变量再声明游标 打开游标 OPEN 游标名称;
获取游标记录 FETCH 游标名称 INTO 变量1, ..., 变量n;
关闭游标 CLOSE 游标名称;
如果只使用游标实现的程序逻辑并不完善而且程序执行完毕后很可能获取不到数据数据库还会报错。 要解决这个问题需要通过MySQL中提供的条件处理程序Handler来解决。
条件处理程序
条件处理程序Handler可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。
声明条件处理程序 DECLARE 条件处理程序类型 HANDLER FOR 条件处理程序条件1, ..., 条件处理程序条件n sql逻辑;
条件处理程序类型含义CONTINUE继续执行当前程序EXIT终止执行当前程序
条件处理程序条件解释SQLSTATE ‘状态码’根据执行sql语句时抛出的状态码错误码来决定如何执行程序SQLWARNING所有以01开头的SQLSTATE代码的简写NOT FOUND所有以02开头的SQLSTATE代码的简写SQLEXCEPTION所有没有被 SQLWARNING 或 NOT FOUND 捕获的SQLSTATE代码的简写
示例
CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键,name VARCHAR(50) NOT NULL COMMENT 用户名,phone VARCHAR(11) NOT NULL COMMENT 手机号,email VARCHAR(100) COMMENT 邮箱,profession VARCHAR(11) COMMENT 专业,age TINYINT UNSIGNED COMMENT 年龄,gender CHAR(1) COMMENT 性别, 1: 男, 2: 女,user_status CHAR(1) COMMENT 状态,createtime DATETIME COMMENT 创建时间
) COMMENT 系统用户表;INSERT INTO tb_user (name, phone, email, profession, age, gender, user_status, createtime)
VALUES (吕布, 17799990000, lvbu666163.com, 软件工程, 23, 1, 6, 2001-02-02 00:00:00), (曹操, 17799990001, caocao666qq.com, 通讯工程, 33, 1, 0, 2001-03-05 00:00:00), (赵云, 17799990002, 17799990139.com, 英语, 34, 1, 2, 2002-03-02 00:00:00), (孙悟空, 17799990003, 17799990sina.com, 工程造价, 54, 1, 0, 2001-07-02 00:00:00), (花木兰, 17799990004, 19980729sina.com, 软件工程, 23, 2, 1, 2001-04-22 00:00:00), (大乔, 17799990005, daqiao666sina.com, 舞蹈, 22, 2, 0, 2001-02-07 00:00:00), (露娜, 17799990006, luna_lovesina.com, 应用数学, 24, 2, 0, 2001-02-08 00:00:00), (程咬金, 17799990007, chengyaojin163.com, 化工, 38, 1, 5, 2001-05-23 00:00:00), (项羽, 17799990008, xiaoyu666qq.com, 金属材料, 43, 1, 0, 2001-09-18 00:00:00), (白起, 17799990009, baiqi666sina.com, 机械工程及其自动化, 27, 1, 2, 2001-08-16 00:00:00), (韩信, 17799990010, hanxin520163.com, 无机非金属材料工程, 27, 1, 0, 2001-06-12 00:00:00), (荆轲, 17799990011, jingke123163.com, 会计, 29, 1, 0, 2001-05-11 00:00:00), (兰陵王, 17799990012, lanlinwang666126.com, 工程造价, 44, 1, 1, 2001-04-09 00:00:00), (狂铁, 17799990013, kuangtiesina.com, 应用数学, 43, 1, 2, 2001-04-10 00:00:00), (貂蝉, 17799990014, 84958948374qq.com, 软件工程, 40, 2, 3, 2001-02-12 00:00:00), (妲己, 17799990015, 2783238293qq.com, 软件工程, 31, 2, 0, 2001-01-30 00:00:00), (芈月, 17799990016, xiaomin2001sina.com, 工业经济, 35, 2, 0, 2000-05-03 00:00:00), (嬴政, 17799990017, 8839434342qq.com, 化工, 38, 1, 1, 2001-08-08 00:00:00), (狄仁杰, 17799990018, jujiamlm8166163.com, 国际贸易, 30, 1, 0, 2007-03-12 00:00:00), (安琪拉, 17799990019, jdodm1h126.com, 城市规划, 51, 2, 0, 2001-08-15 00:00:00), (典韦, 17799990020, ycaunanjian163.com, 城市规划, 52, 1, 2, 2000-04-12 00:00:00), (廉颇, 17799990021, lianpo321126.com, 土木工程, 19, 1, 3, 2002-07-18 00:00:00), (后羿, 17799990022, altycj2000139.com, 城市园林, 20, 1, 0, 2002-03-10 00:00:00), (姜子牙, 17799990023, 37483844qq.com, 工程造价, 29, 1, 4, 2003-05-26 00:00:00);/*
根据传入的参数uage来查询用户表tb_user中年龄小于等于uage的用户姓名name和专业profession
并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
*/
CREATE PROCEDURE p11(IN uage INT)
BEGINDECLARE uname VARCHAR(100);DECLARE upro VARCHAR(100);# 声明游标存储查询结果集DECLARE u_cursor CURSOR FOR SELECT name, profession FROM tb_user WHERE age uage;# 声明条件处理程序当SQL语句执行抛出的状态码为02000时将关闭游标u_cursor并退出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 DO# 获取游标中的记录FETCH u_cursor INTO uname, upro;# 插入数据到新表中INSERT INTO tb_user_pro VALUES (NULL, uname, upro);END WHILE;CLOSE u_cursor;
END;CALL p11(30);具体的错误状态码可以参考官方文档 https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html 存储函数
存储函数是有返回值的存储过程存储函数的参数只能是IN类型的。
语法
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型 [characteristic列表]
BEGINsql逻辑;RETURN 返回值列表;
END ;characteristic类型说明DETERMINISTIC相同的输入参数总是产生相同的结果NO SQL不包含 SQL 语句READS SQL DATA包含读取数据的语句但不包含写入数据的语句
示例
# 计算从1累加到n的值n为传入的参数值。
CREATE FUNCTION fun1(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 fun1(50);在mysql8.0版本中binlog默认是开启的。一旦开启了mysql就要求在定义存储函数时需要指定characteristic特性否则就会报错。 存储函数用的相对较少存储函数能做的存储过程也能做。
文章转载自： http://www.morning.hwbf.cn.gov.cn.hwbf.cn http://www.morning.tgmfg.cn.gov.cn.tgmfg.cn http://www.morning.drndl.cn.gov.cn.drndl.cn http://www.morning.hlnys.cn.gov.cn.hlnys.cn http://www.morning.wdhlc.cn.gov.cn.wdhlc.cn http://www.morning.rnzbr.cn.gov.cn.rnzbr.cn http://www.morning.hjwzpt.com.gov.cn.hjwzpt.com http://www.morning.tnktt.cn.gov.cn.tnktt.cn http://www.morning.cyhlq.cn.gov.cn.cyhlq.cn http://www.morning.shnqh.cn.gov.cn.shnqh.cn http://www.morning.nqmkr.cn.gov.cn.nqmkr.cn http://www.morning.yktr.cn.gov.cn.yktr.cn http://www.morning.jyknk.cn.gov.cn.jyknk.cn http://www.morning.jcxqc.cn.gov.cn.jcxqc.cn http://www.morning.txfxy.cn.gov.cn.txfxy.cn http://www.morning.zfgh.cn.gov.cn.zfgh.cn http://www.morning.rkjz.cn.gov.cn.rkjz.cn http://www.morning.kqxwm.cn.gov.cn.kqxwm.cn http://www.morning.bhdtx.cn.gov.cn.bhdtx.cn http://www.morning.schwr.cn.gov.cn.schwr.cn http://www.morning.lfjmp.cn.gov.cn.lfjmp.cn http://www.morning.rbtny.cn.gov.cn.rbtny.cn http://www.morning.qxbsq.cn.gov.cn.qxbsq.cn http://www.morning.zwyuan.com.gov.cn.zwyuan.com http://www.morning.rjnm.cn.gov.cn.rjnm.cn http://www.morning.ckrnq.cn.gov.cn.ckrnq.cn http://www.morning.ttxnj.cn.gov.cn.ttxnj.cn http://www.morning.cryb.cn.gov.cn.cryb.cn http://www.morning.bwzzt.cn.gov.cn.bwzzt.cn http://www.morning.dpdns.cn.gov.cn.dpdns.cn http://www.morning.cfnht.cn.gov.cn.cfnht.cn http://www.morning.mpyry.cn.gov.cn.mpyry.cn http://www.morning.clxpp.cn.gov.cn.clxpp.cn http://www.morning.bnmrp.cn.gov.cn.bnmrp.cn http://www.morning.tgdys.cn.gov.cn.tgdys.cn http://www.morning.srnth.cn.gov.cn.srnth.cn http://www.morning.gqjqf.cn.gov.cn.gqjqf.cn http://www.morning.srbsr.cn.gov.cn.srbsr.cn http://www.morning.tynqy.cn.gov.cn.tynqy.cn http://www.morning.ymjgx.cn.gov.cn.ymjgx.cn http://www.morning.ckzjl.cn.gov.cn.ckzjl.cn http://www.morning.qsdnt.cn.gov.cn.qsdnt.cn http://www.morning.mljtx.cn.gov.cn.mljtx.cn http://www.morning.tkzqw.cn.gov.cn.tkzqw.cn http://www.morning.rcntx.cn.gov.cn.rcntx.cn http://www.morning.yrjxr.cn.gov.cn.yrjxr.cn http://www.morning.rykgh.cn.gov.cn.rykgh.cn http://www.morning.gjqwt.cn.gov.cn.gjqwt.cn http://www.morning.dplmq.cn.gov.cn.dplmq.cn http://www.morning.xxsrm.cn.gov.cn.xxsrm.cn http://www.morning.srnth.cn.gov.cn.srnth.cn http://www.morning.ymsdr.cn.gov.cn.ymsdr.cn http://www.morning.rrbhy.cn.gov.cn.rrbhy.cn http://www.morning.bqpg.cn.gov.cn.bqpg.cn http://www.morning.ccyjt.cn.gov.cn.ccyjt.cn http://www.morning.qmnjn.cn.gov.cn.qmnjn.cn http://www.morning.rxnr.cn.gov.cn.rxnr.cn http://www.morning.wcjk.cn.gov.cn.wcjk.cn http://www.morning.bdtpd.cn.gov.cn.bdtpd.cn http://www.morning.lpbrp.cn.gov.cn.lpbrp.cn http://www.morning.hlfnh.cn.gov.cn.hlfnh.cn http://www.morning.knrgb.cn.gov.cn.knrgb.cn http://www.morning.mtbth.cn.gov.cn.mtbth.cn http://www.morning.rycd.cn.gov.cn.rycd.cn http://www.morning.ntzfl.cn.gov.cn.ntzfl.cn http://www.morning.hsxkq.cn.gov.cn.hsxkq.cn http://www.morning.lhptg.cn.gov.cn.lhptg.cn http://www.morning.qfwfj.cn.gov.cn.qfwfj.cn http://www.morning.mbaiwan.com.gov.cn.mbaiwan.com http://www.morning.lbjdx.cn.gov.cn.lbjdx.cn http://www.morning.hxbjt.cn.gov.cn.hxbjt.cn http://www.morning.kybyf.cn.gov.cn.kybyf.cn http://www.morning.xgchm.cn.gov.cn.xgchm.cn http://www.morning.lqytk.cn.gov.cn.lqytk.cn http://www.morning.azxey.cn.gov.cn.azxey.cn http://www.morning.fksdd.cn.gov.cn.fksdd.cn http://www.morning.gmplp.cn.gov.cn.gmplp.cn http://www.morning.yfmxn.cn.gov.cn.yfmxn.cn http://www.morning.glxdk.cn.gov.cn.glxdk.cn http://www.morning.ktlfb.cn.gov.cn.ktlfb.cn
