首页 / MYSQL / 转 mysql 存储过程初探
转 mysql 存储过程初探
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了转 mysql 存储过程初探,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5750字,纯文字阅读大概需要9分钟。
内容图文
https://www.cnblogs.com/qmfsun/p/4838032.html
MySQL命令执行sql文件的两种方法
https://www.cnblogs.com/mark-chan/p/5384139.html
CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,‘ World‘);
SET @greeting=‘Hello‘;
CALL GreetWorld();
https://blog.csdn.net/shaochenshuo/article/details/49890947
mysql中使用tee实现类似oracle spool功能
SELECT
FROM_UNIXTIME(his.clock, "%Y-%m-%d") AS DATE,
his.VALUE AS traffic,
(a.key_) AS NAME,
a.name AS db_name,
a.host AS HOST
FROM
history his,(SELECT i.key_,i.itemid,h.hostid,h.host,h.name FROM items i,HOSTS h
WHERE h.hostid=i.hostid AND i.key_ LIKE ‘%size%db%oradata,pfree%‘ ) a
WHERE his.itemid=a.itemid
#AND FROM_UNIXTIME(his.clock) >= ‘2019-09-16‘
# AND FROM_UNIXTIME(clock) < ‘2019-08-11‘
AND FROM_UNIXTIME(clock, ‘%Y-%m-%d‘ ) =‘2019-09-16‘
#GROUP BY FROM_UNIXTIME(his.clock, "%Y-%m-%d")
##https://www.cnblogs.com/mark-chan/p/5384139.html
MySQL存储过程
##参考https://www.cnblogs.com/lyhc/p/5760164.html
mysql存储过程 --游标的使用 取每行记录 (多字段)
delimiter $
create PROCEDURE get_filesystem_inf()
BEGIN
DECLARE key varchar(64); -- id
DECLARE itemid1 varchar(16); -- 监控项ID
DECLARE hostid1 varchar(32); -- hostid
DECLARE host1 varchar(64); -- 主机名
DECLARE name1 varchar(64); -- ip
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur_account CURSOR FOR SELECT i.key_,i.itemid,h.hostid,h.host,h.name FROM items i,HOSTS h
WHERE h.hostid=i.hostid AND i.key_ LIKE ‘%size%db%oradata,pfree%‘;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
END
$
delimiter $
create PROCEDURE get_filesystem_inf()
BEGIN
DECLARE key1 varchar(64); -- id
DECLARE itemid1 varchar(16); -- 监控项ID
DECLARE hostid1 varchar(32); -- hostid
DECLARE host1 varchar(64); -- 主机名
DECLARE name1 varchar(64); -- ip
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur_account CURSOR FOR SELECT i.key_,i.itemid,h.hostid,h.host,h.name FROM items i,HOSTS h
WHERE h.hostid=i.hostid AND i.key_ LIKE ‘%size%db%oradata,pfree%‘;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历
read_loop: LOOP
-- 取值 取多个字段
FETCH NEXT from cur_account INTO key,itemid1,hostid1,host1,name1;
IF done THEN
LEAVE read_loop;
END IF;
-- 你自己想做的操作
--insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,‘的家长‘));
select itemid1,host11,name1,key,FROM_UNIXTIME(his.clock, "%Y-%m-%d") AS DATE, his.VALUE AS traffic
from history his
WHERE his.itemid=itemid1
and FROM_UNIXTIME(clock, ‘%Y-%m-%d‘ ) =‘2019-09-16‘ limit 1;
END LOOP;
CLOSE cur_account;
END $
---mysql 不支持 create or replace procedure 的写法,只支持drop and create
--
drop PROCEDURE get_filesystem_inf;
--最终模板,因为中文好像无法在MYSQL 提示符下识别,所以去掉所有中文
--错误代码: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘ at line 3
--换行不行, 解决方法 加入 delimiter $
--将脚本 放在 sqlyog 编辑器里,使用sql 格式化下。在放在mysql 提示符下 执行
###计算所有的空间
delimiter $
CREATE PROCEDURE get_filesystem_inf ()
BEGIN
DECLARE key1 VARCHAR(64);
DECLARE itemid1 VARCHAR(16);
DECLARE hostid1 VARCHAR(32);
DECLARE host1 VARCHAR(64);
DECLARE name1 VARCHAR(64);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_account CURSOR FOR
SELECT
i.key_,
i.itemid,
h.hostid,
h.host,
h.name
FROM
items i,
HOSTS h
WHERE h.hostid = i.hostid
AND i.key_ LIKE ‘%size%db/%/%,pfree%‘
AND i.key_ NOT LIKE ‘%client%,pfree%‘;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
OPEN cur_account ;
read_loop :
LOOP
FETCH NEXT FROM cur_account INTO key1,itemid1,
hostid1,
host1,
name1 ;
IF done
THEN LEAVE read_loop ;
END IF ;
SELECT
itemid1,
host1,
name1,
key1,
FROM_UNIXTIME(his.clock, "%Y-%m-%d") AS DATE,
his.VALUE AS traffic
FROM
history his
WHERE his.itemid = itemid1
AND FROM_UNIXTIME(clock, ‘%Y-%m-%d‘) = ‘2019-09-16‘
LIMIT 1 ;
END LOOP ;
CLOSE cur_account ;
END
##sample
###计算小于35%的空间
CREATE PROCEDURE get_filesystem_inf1 ()
BEGIN
DECLARE key1 VARCHAR(64);
DECLARE itemid1 VARCHAR(16);
DECLARE hostid1 VARCHAR(32);
DECLARE host1 VARCHAR(64);
DECLARE name1 VARCHAR(64);
DECLARE done INT DEFAULT FALSE;
DECLARE cur_account CURSOR FOR
SELECT
i.key_,
i.itemid,
h.hostid,
h.host,
h.name
FROM
items i,
HOSTS h
WHERE h.hostid = i.hostid
AND i.key_ LIKE ‘%size%db/%/%,pfree%‘
AND i.key_ NOT LIKE ‘%client%,pfree%‘;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
OPEN cur_account ;
read_loop :
LOOP
FETCH NEXT FROM cur_account INTO key1,itemid1,
hostid1,
host1,
name1 ;
IF done
THEN LEAVE read_loop ;
END IF ;
SELECT
itemid1,
host1,
name1,
key1,
FROM_UNIXTIME(his.clock, "%Y-%m-%d") AS DATE,
his.VALUE AS traffic
FROM
history his
WHERE his.itemid = itemid1
AND FROM_UNIXTIME(clock, ‘%Y-%m-%d‘) = ‘2019-09-16‘
and his.VALUE < 35
LIMIT 1 ;
END LOOP ;
CLOSE cur_account ;
END
tee /tmp/check1.log
call get_filesystem_inf1();
tee off
转 mysql 存储过程初探
标签:err false mit sys set gre iter art acl
本文系统来源:https://www.cnblogs.com/feiyun8616/p/11528897.html
内容总结
以上是互联网集市为您收集整理的转 mysql 存储过程初探全部内容,希望文章能够帮你解决转 mysql 存储过程初探所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。