当前位置: 开发笔记 > 编程语言 > 正文

MySQL存储过程范例

作者：张骞在这里 | 来源：互联网 | 2023-05-19 01:55

mysql存储过程实例viewplaincopyDELIMITER $DROP PROCEDURE IF EXISTS `stat` $CREATE DEFINER`liulive

mysql 存储过程实例

[sql] view plaincopy

DELIMITER $
DROP PROCEDURE IF EXISTS `stat` $
CREATE DEFINER=`liulive`@`%` PROCEDURE `stat`(startDate VARCHAR(14), endDate VARCHAR(14))
BEGIN

[sql] view plaincopy

/*声明变量*/

[sql] view plaincopy

DECLARE total INT DEFAULT 0;
DECLARE dat DATE DEFAULT NULL;
DECLARE quals INT DEFAULT 0;
DECLARE scheds INT DEFAULT 0;
DECLARE shows INT DEFAULT 0;
DECLARE kepts INT DEFAULT 0;
DECLARE accepts INT DEFAULT 0;
DECLARE _time VARCHAR(14) DEFAULT NULL;
DECLARE assess VARCHAR(40) DEFAULT NULL;
DECLARE offic VARCHAR(60) DEFAULT NULL;
DECLARE recrui VARCHAR(60) DEFAULT NULL;
DECLARE ma VARCHAR(60) DEFAULT NULL;
DECLARE CNT INT DEFAULT 0;
DECLARE _cursor_flag TINYINT(1) DEFAULT 0;
/*定义光标*/
DECLARE totalCalls CURSOR FOR Select count(flow_step) as total, DATE(operate_date) as dat, office, recruit, main from v_student where DATE(operate_date) >= DATE(startDate) And DATE(operate_date) <= DATE(endDate) group by DATE(operate_date), office, recruit, main order by DATE(operate_date);
DECLARE qualCalls CURSOR FOR Select count(flow_step) as qual, DATE(operate_date) as dat, office, recruit, main from v_student where flow_step <> ‘NQ’ And DATE(operate_date) >= DATE(startDate) and DATE(operate_date) <= DATE(endDate) group by DATE(operate_date), office, recruit, main order by DATE(operate_date);
DECLARE schedCalls CURSOR FOR Select count(flow_step) as sched, DATE(operate_date) as dat, office, recruit, main from v_student where flow_step = ‘Assessment Scheduled’ And DATE(operate_date) >= DATE(startDate) and DATE(operate_date) <= DATE(endDate) group by DATE(operate_date), office, recruit, main order by DATE(operate_date);
DECLARE totalSched CURSOR FOR Select count(flow_step) as sched, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main from v_student where flow_step = ‘Assessment Scheduled’ And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);
DECLARE showSched CURSOR FOR Select count(shows) as shows, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main from v_student where shows is not null And flow_step = ‘Assessment Scheduled’ And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);
DECLARE qualSched CURSOR FOR Select count(qual) as quals, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main from v_student where qual is not null And flow_step = ‘Assessment Scheduled’ And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);
DECLARE keptSched CURSOR FOR Select count(kept) as kepts, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main from v_student where kept is not null And flow_step = ‘Assessment Scheduled’ And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);
DECLARE acceptSched CURSOR FOR Select count(accept) as accepts, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main from v_student where accept is not null And flow_step = ‘Assessment Scheduled’ And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);
nbsp;

[sql] view plaincopy

/* 定义光标结束标志 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cursor_flag = -1;
DELETE FROM STAT_TEMP;
COMMIT;
OPEN totalCalls; /*打开光标*/
LOOP_totalCalls:LOOP /* 循环声明 */
FETCH totalCalls INTO total, dat, offic, recrui, ma;
IF _cursor_flag = -1 then
LEAVE LOOP_totalCalls;
END IF;
insert into STAT_TEMP(DATE_, TOTAL_CALLS, OFFICE, RECRUIT, MAIN) values(dat, total, offic, recrui, ma);
END LOOP;
COMMIT;
CLOSE totalCalls; /* 关闭光标 */
SET _cursor_flag = 1; /* 重置光标结束标志 */
OPEN qualCalls;
LOOP_qualCalls:LOOP
FETCH qualCalls INTO quals, dat, offic, recrui, ma;
if _cursor_flag = -1 then
LEAVE LOOP_qualCalls;
end if;
SELECT COUNT(*) INTO CNT FROM STAT_TEMP WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;
IF CNT > 0 THEN
UPDATE STAT_TEMP SET QUAL_CALLS=quals WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;
SET CNT = 0;
ELSE
insert into STAT_TEMP (DATE_, QUAL_CALLS, OFFICE, RECRUIT, MAIN) values(dat, quals, offic, recrui, ma);
END IF;
END LOOP;
COMMIT;
CLOSE qualCalls;
SET _cursor_flag = 1;
OPEN schedCalls;
LOOP_schedCalls:LOOP
FETCH schedCalls INTO scheds,dat,offic,recrui,ma;
if _cursor_flag = -1 then
LEAVE LOOP_schedCalls;
end if;
SELECT COUNT(*) INTO CNT FROM STAT_TEMP WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;
IF CNT > 0 THEN
UPDATE STAT_TEMP SET SCHED_CALLS=scheds WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;
SET CNT = 0;
ELSE
insert into STAT_TEMP(DATE_, SCHED_CALLS, OFFICE, RECRUIT, MAIN) values(dat, scheds, offic, recrui, ma);
END IF;
END LOOP;
COMMIT;
CLOSE schedCalls;
SET _cursor_flag = 1;
OPEN totalSched;
LOOP_totalSched:LOOP
FETCH totalSched INTO scheds, _time, dat, assess, offic, recrui, ma;
if _cursor_flag = -1 then
LEAVE LOOP_totalSched;
end if;
select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
if CNT > 0 then
update STAT_TEMP set SCHED=scheds WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
SET CNT = 0;
else
insert into STAT_TEMP(DATE_, SCHED, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, scheds, assess, offic, recrui, ma);
end if;
END LOOP;
COMMIT;
CLOSE totalSched;
SET _cursor_flag = 1;
OPEN showSched;
LOOP_showSched:LOOP
FETCH showSched INTO shows, _time, dat, assess,offic,recrui,ma;
if _cursor_flag = -1 then
LEAVE LOOP_showSched;
end if;
select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
if CNT > 0 then
update STAT_TEMP set SHOWS=shows WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
SET CNT = 0;
else
insert into STAT_TEMP(DATE_, SHOWS, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, scheds, assess, offic, recrui, ma);
end if;
END LOOP;
COMMIT;
CLOSE showSched;
SET _cursor_flag = 1;
OPEN qualSched;
LOOP_qualSched:LOOP
FETCH qualSched INTO quals, _time, dat, assess,offic,recrui,ma;
if _cursor_flag = -1 then
LEAVE LOOP_qualSched;
end if;
select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
if CNT > 0 then
update STAT_TEMP set QUAL=quals WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
SET CNT = 0;
else
insert into STAT_TEMP(DATE_, QUAL, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, quals, assess, offic, recrui, ma);
end if;
END LOOP;
COMMIT;
CLOSE qualSched;
SET _cursor_flag = 1;
OPEN keptSched;
LOOP_keptSched:LOOP
FETCH keptSched INTO kepts, _time, dat, assess, offic, recrui, ma;
if _cursor_flag = -1 then
LEAVE LOOP_keptSched;
end if;
select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
if CNT > 0 then
update STAT_TEMP set KEPT=kepts WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
SET CNT = 0;
else
insert into STAT_TEMP(DATE_, KEPT, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, kepts, assess, offic, recrui, ma);
end if;
END LOOP;
COMMIT;
CLOSE keptSched;
SET _cursor_flag = 1;
OPEN acceptSched;
LOOP_acceptSched:LOOP
FETCH acceptSched INTO kepts, _time, dat, assess, offic, recrui, ma;
if _cursor_flag = -1 then
LEAVE LOOP_acceptSched;
end if;
select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
if CNT > 0 then
update STAT_TEMP set KEPT=kepts WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;
SET CNT = 0;
else
insert into STAT_TEMP(DATE_, KEPT, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, kepts, assess, offic, recrui, ma);
end if;
END LOOP;
COMMIT;
CLOSE acceptSched;
END $
DELIMITER ;

推荐阅读

ip
Python操作MySQL（pymysql模块）详解及示例代码

本文介绍了使用Python操作MySQL数据库的方法，详细讲解了pymysql模块的安装和连接MySQL数据库的步骤，并提供了示例代码。内容涵盖了创建表、插入数据、查询数据等操作，帮助读者快速掌握Python操作MySQL的技巧。 ... [详细]

蜡笔小新 2023-12-10 17:50:06
sum
Oracle的PL/SQL编程基础技能实战一

一基础代码检查检查以bm_开头的系统初始化编码表是否有空值。与业务系统相关的编码项不能存在空值，会导致系统业务无法办理。为初始化数据表、在做测试数据和正式上线前检查。上线 ... [详细]

蜡笔小新 2023-10-17 09:38:23
post
PHP操作MySql数据库_PHP教程

PHP操作MySql数据库_PHP教程:链接数据库$conn@mysql_connect(localhost,root,88888888)ordie(链接错误);解决中文乱码mys ... [详细]

蜡笔小新 2023-10-17 04:43:28
ip
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
substring
Java String与StringBuffer的区别及其应用场景

本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]

蜡笔小新 2023-12-13 19:21:06
fetch
游标的使用笔记

本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]

蜡笔小新 2023-12-13 15:24:30
ip
如何在php中将mysql查询结果赋值给变量

本文介绍了在php中将mysql查询结果赋值给变量的方法，包括从mysql表中查询count(学号)并赋值给一个变量，以及如何将sql中查询单条结果赋值给php页面的一个变量。同时还讨论了php调用mysql查询结果到变量的方法，并提供了示例代码。 ... [详细]

蜡笔小新 2023-12-12 18:22:57
get
MyBatis多表查询与动态SQL使用

本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]

蜡笔小新 2023-12-12 17:12:51
get
Oracle 11g物理Active Data Guard实时查询（Realtime query）特性

在Oracle11g以前版本中的的DataGuard物理备用数据库，可以以只读的方式打开数据库，但此时MediaRecovery利用日志进行数据同步的过 ... [详细]

蜡笔小新 2023-12-11 15:49:10
ip
MySQL语句大全：创建、授权、查询、修改等【MySQL】的使用方法详解

本文详细介绍了MySQL语句的使用方法，包括创建用户、授权、查询、修改等操作。通过连接MySQL数据库，可以使用命令创建用户，并指定该用户在哪个主机上可以登录。同时，还可以设置用户的登录密码。通过本文，您可以全面了解MySQL语句的使用方法。 ... [详细]

蜡笔小新 2023-12-11 15:34:14
post
MySQL中的MVVC多版本并发控制机制的应用及实现

本文介绍了MySQL中MVCC的应用及实现机制。MVCC是一种提高并发性能的技术，通过对事务内读取的内存进行处理，避免写操作堵塞读操作的并发问题。与其他数据库系统的MVCC实现机制不尽相同，MySQL的MVCC是在undolog中实现的。通过undolog可以找回数据的历史版本，提供给用户读取或在回滚时覆盖数据页上的数据。MySQL的大多数事务型存储引擎都实现了MVCC，但各自的实现机制有所不同。 ... [详细]

蜡笔小新 2023-12-11 13:09:19
get
android studio生成jks,android studio生成 keystore 以及获取 SHA1值等

合并列值－合并为一列问题需求：createtabletab(Aint,Bint,Cint)inserttabselect1,2,3unionallsel ... [详细]

蜡笔小新 2023-12-11 12:32:55
get
MySQL插入数据的四种方式及安全性分析

本文介绍了MySQL插入数据的四种方式：插入完整的行、插入行的一部分、插入多行和插入查询结果，并对其安全性进行了分析。在插入行时，应注意字段的定义和赋值，以提高安全性。同时指出了使用insert语句的不安全性，应尽量避免使用。建议在表中定义相关字段，并根据定义的字段赋予相应的值，以增加插入操作的安全性。 ... [详细]

蜡笔小新 2023-12-11 08:28:27
fetch
scrapy 采集入为库 mysql 数据库，只更新一个字段提示这个语法错误？

就是查询有没有这个字，如果查到这个字就更新这个字的bsmc字段表是news_topic插入代码如下： ... [详细]

蜡笔小新 2023-10-17 19:42:26
fetch
猜字母游戏

猜字母游戏猜字母游戏——设计数据结构猜字母游戏——设计程序结构猜字母游戏——实现字母生成方法猜字母游戏——实现字母检测方法猜字母游戏——实现主方法1猜字母游戏——设计数据结构1.1 ... [详细]

蜡笔小新 2023-12-13 12:04:03

张骞在这里

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章