精华内容
下载资源
问答
  • "link":"https://www.aliyun.com/service/chiefexpert/platform"},{"txt":"存储首席专家","link":"https://www.aliyun.com/service/chiefexpert/storage"}],"countinfo":{"search":{"length_pc":0,"length":0},"card...

    {"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航,为用户的数据库应用系统进行性能和风险评估,参与配合进行数据压测演练,提供数据库优化方面专业建议,在业务高峰期与用户共同保障数据库系统平稳运行。","link1":"https://www.aliyun.com/service/optimization/database","link":"https://www.aliyun.com/service/chiefexpert/database","icon":"https://img.alicdn.com/tfs/TB1a5ZfonnI8KJjy0FfXXcdoVXa-100-100.png","btn2":"数据库紧急救援服务","tip":"还有更多专家帮助您解决云上业务问题:立即查看","btn1":"云上数据库优化服务","link2":"https://www.aliyun.com/service/databaserescue","title":"数据库专家服务"}],"search":[{"txt":"安全首席专家","link":"https://www.aliyun.com/service/chiefexpert/security"},{"txt":"中间件首席专家","link":"https://www.aliyun.com/service/chiefexpert/middleware"},{"txt":"系统与平台首席专家","link":"https://www.aliyun.com/service/chiefexpert/platform"},{"txt":"存储首席专家","link":"https://www.aliyun.com/service/chiefexpert/storage"}],"countinfo":{"search":{"length_pc":0,"length":0},"card":{"length_pc":0,"length":0}},"simplifiedDisplay":"newEdition","newCard":[{"link":"https://www.aliyun.com/product/rds/mysql","icon":"rds_mysql1","contentLink":"https://www.aliyun.com/product/rds/mysql","title":"云数据库RDS MySQL版","des":"云数据库RDS MySQL版 MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供稳定极致的数据库性能,同时灵活的部署架构及产品形态,可满足不同场景下的数据库需求。","btn1":"了解详情","link1":"https://www.aliyun.com/product/rds/mysql","btn2":"管理控制台","link2":"https://rdsnext.console.aliyun.com/#/rdsList/cn-hangzhou/basic/","btn3":"在线咨询","link3":"https://www.aliyun.com/core/online-consult?spm=5176.8789780.7y9jhqsfz.115.47ea55caeL2Mfu&from=OVtXEBmlH8","infoGroup":[{"infoName":"最新活动","infoContent":{"firstContentName":"MySQL 1年仅19.9元","firstContentLink":"https://www.aliyun.com/database/dbfirstbuy#J_9036464270","lastContentName":"ECS+MySQL 0.7元/日","lastContentLink":"https://www.aliyun.com/1111/enterprise"}},{"infoName":"快速入门","infoContent":{"firstContentName":"选型指南","firstContentLink":"https://help.aliyun.com/document_detail/164594.html?spm=a2c4g.11186623.6.551.56b67384ispvvk","lastContentName":"上手指南","lastContentLink":"https://help.aliyun.com/document_detail/26124.html?spm=a2c4g.11174283.2.16.51795b83pbkzav"}},{"infoName":"最佳实践","infoContent":{"firstContentName":"X-Engine的应用场景","firstContentLink":"https://help.aliyun.com/document_detail/148402.html?spm=a2c4g.11186623.2.14.3f592685zjuUxu#concept-2364901","lastContentName":"读写分离功能","lastContentLink":"https://help.aliyun.com/document_detail/96073.html?spm=a2c4g.11174283.2.53.42db5b83VvZvMJ"}},{"infoName":"产品推荐","infoContent":{"firstContentName":"云数据库PolarDB","lastContentName":"云原生数据仓库MySQL","firstContentLink":"https://www.aliyun.com/product/polardb","lastContentLink":"https://www.aliyun.com/product/ads"}}]}],"visual":{"textColor":"dark","topbg":""}}

    {"$env":{"JSON":{}},"$page":{"env":"production"},"$context":{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航,为用户的数据库应用系统进行性能和风险评估,参与配合进行数据压测演练,提供数据库优化方面专业建议,在业务高峰期与用户共同保障数据库系统平稳运行。","link1":"https://www.aliyun.com/service/optimization/database","link":"https://www.aliyun.com/service/chiefexpert/database","icon":"https://img.alicdn.com/tfs/TB1a5ZfonnI8KJjy0FfXXcdoVXa-100-100.png","btn2":"数据库紧急救援服务","tip":"还有更多专家帮助您解决云上业务问题:立即查看","btn1":"云上数据库优化服务","link2":"https://www.aliyun.com/service/databaserescue","title":"数据库专家服务"}],"search":[{"txt":"安全首席专家","link":"https://www.aliyun.com/service/chiefexpert/security"},{"txt":"中间件首席专家","link":"https://www.aliyun.com/service/chiefexpert/middleware"},{"txt":"系统与平台首席专家","link":"https://www.aliyun.com/service/chiefexpert/platform"},{"txt":"存储首席专家","link":"https://www.aliyun.com/service/chiefexpert/storage"}],"countinfo":{"search":{"length_pc":0,"length":0},"card":{"length_pc":0,"length":0}},"simplifiedDisplay":"newEdition","newCard":[{"link":"https://www.aliyun.com/product/rds/mysql","icon":"rds_mysql1","contentLink":"https://www.aliyun.com/product/rds/mysql","title":"云数据库RDS MySQL版","des":"云数据库RDS MySQL版 MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供稳定极致的数据库性能,同时灵活的部署架构及产品形态,可满足不同场景下的数据库需求。","btn1":"了解详情","link1":"https://www.aliyun.com/product/rds/mysql","btn2":"管理控制台","link2":"https://rdsnext.console.aliyun.com/#/rdsList/cn-hangzhou/basic/","btn3":"在线咨询","link3":"https://www.aliyun.com/core/online-consult?spm=5176.8789780.7y9jhqsfz.115.47ea55caeL2Mfu&from=OVtXEBmlH8","infoGroup":[{"infoName":"最新活动","infoContent":{"firstContentName":"MySQL 1年仅19.9元","firstContentLink":"https://www.aliyun.com/database/dbfirstbuy#J_9036464270","lastContentName":"ECS+MySQL 0.7元/日","lastContentLink":"https://www.aliyun.com/1111/enterprise"}},{"infoName":"快速入门","infoContent":{"firstContentName":"选型指南","firstContentLink":"https://help.aliyun.com/document_detail/164594.html?spm=a2c4g.11186623.6.551.56b67384ispvvk","lastContentName":"上手指南","lastContentLink":"https://help.aliyun.com/document_detail/26124.html?spm=a2c4g.11174283.2.16.51795b83pbkzav"}},{"infoName":"最佳实践","infoContent":{"firstContentName":"X-Engine的应用场景","firstContentLink":"https://help.aliyun.com/document_detail/148402.html?spm=a2c4g.11186623.2.14.3f592685zjuUxu#concept-2364901","lastContentName":"读写分离功能","lastContentLink":"https://help.aliyun.com/document_detail/96073.html?spm=a2c4g.11174283.2.53.42db5b83VvZvMJ"}},{"infoName":"产品推荐","infoContent":{"firstContentName":"云数据库PolarDB","lastContentName":"云原生数据仓库MySQL","firstContentLink":"https://www.aliyun.com/product/polardb","lastContentLink":"https://www.aliyun.com/product/ads"}}]}],"visual":{"textColor":"dark","topbg":""}}}

    展开全文
  • CREATE DEFINER=`root`@`%` PROCEDURE `insertPresale`()BEGIN#Routine body goes here...... /*用于判断是否结束循环*/DECLARE goodsId VARCHAR(255);#标记商品idDECLARE flag INT DEFAULT 0;#标记数据库是否包含...

    CREATE DEFINER=`root`@`%` PROCEDURE `insertPresale`()

    BEGIN

    #Routine body goes here...

    DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/

    DECLARE goodsId VARCHAR(255);#标记商品id

    DECLARE flag INT DEFAULT 0;#标记数据库是否包含此条商品记录

    /*用于存储结果集的记录*/

    /*定义游标*/

    DECLARE idCur CURSOR FOR SELECT goods_id FROM `sys_goods_publish` WHERE presale= 1 AND presale_time <= NOW();

    /*定义 设置循环结束标识done值怎么改变 的逻辑*/

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/

    OPEN idCur;  /*打开游标*/

    /* 循环开始 */

    REPEAT

    #/* 如果要fetch多列应该这样写,fetch cur/*对应下面的idCur*/

    FETCH idCur INTO goodsId;  /*还可以fetch多列(假设结果集的记录不是单列的话)*/

    IF NOT done THEN  /*数值为非0,MySQL认为是true*/

    SELECT COUNT(*) INTO flag FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;

    IF (flag>0) THEN #如果数据库中有爬取此条记录则删除已用户发布的为准

    DELETE FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;

    END IF;

    INSERT INTO `itemsinfonew` (TaoBaoitemId,CouponID,CreateSourceName)

    SELECT goods_id,coupon_id,SUBSTRING("customer_fd",0.5)

    FROM `sys_goods_publish`

    WHERE  presale_time <= NOW() AND goods_id = goodsId;

    UPDATE `sys_goods_publish` SET presale = 0 WHERE presale= 1 AND presale_time <= NOW() AND goods_id = goodsId;

    END IF;

    UNTIL done END REPEAT;

    CLOSE idCur;  /*关闭游标*/

    展开全文
  • MySQL存储过程循环遍历查询到的结果集1、创建存储过程根据MySQL的语法创建存储过程,要注意的是如果循环遍历查询到的结果集,取出结果集中的数据做操作。CREATE DEFINER=`root`@`%` PROCEDURE `alter_view_counts`...

    MySQL存储过程-循环遍历查询到的结果集

    1、创建存储过程

    根据MySQL的语法创建存储过程,要注意的是如果循环遍历查询到的结果集,取出结果集中的数据做操作。

    CREATE DEFINER=`root`@`%` PROCEDURE `alter_view_counts`()

    BEGIN

    #声明结束标识

    DECLARE end_flag int DEFAULT 0;

    DECLARE albumId bigint;

    #声明游标 album_curosr

    DECLARE album_curosr CURSOR FOR SELECT album_id FROM album;

    #设置终止标志

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;

    #打开游标

    OPEN album_curosr;

    #遍历游标

    REPEAT

    #获取当前游标指针记录,取出值赋给自定义的变量

    FETCH album_curosr INTO albumId;

    #利用取到的值进行数据库的操作

    UPDATE album SET album.views_count= (SELECT SUM(light_chat.views_count) FROM `light_chat` WHERE light_chat.album_id = albumId) WHERE album.album_id = albumId;

    # 根据 end_flag 判断是否结束

    UNTIL end_flag END REPEAT;

    #关闭游标

    close album_curosr;

    END

    2,调用存储过程

    CALL alter_view_counts()

    展开全文
  • MySQL存储过程循环遍历查询到的结果集1、创建存储过程(更好的阅读体验,请移步我的个人博客)根据MySQL的语法创建存储过程,要注意的是如果循环遍历查询到的结果集,取出结果集中的数据做操作。CREATE DEFINER=`...

    MySQL存储过程-循环遍历查询到的结果集

    1、创建存储过程

    (更好的阅读体验,请移步我的个人博客)根据MySQL的语法创建存储过程,要注意的是如果循环遍历查询到的结果集,取出结果集中的数据做操作。

    CREATE DEFINER=`root`@`%` PROCEDURE `alter_view_counts`()

    BEGIN

    #声明结束标识

    DECLARE end_flag int DEFAULT 0;

    DECLARE albumId bigint;

    #声明游标 album_curosr

    DECLARE album_curosr CURSOR FOR SELECT album_id FROM album;

    #设置终止标志

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;

    #打开游标

    OPEN album_curosr;

    #遍历游标

    REPEAT

    #获取当前游标指针记录,取出值赋给自定义的变量

    FETCH album_curosr INTO albumId;

    #利用取到的值进行数据库的操作

    UPDATE album SET album.views_count= (SELECT SUM(light_chat.views_count) FROM `light_chat` WHERE light_chat.album_id = albumId) WHERE album.album_id = albumId;

    # 根据 end_flag 判断是否结束

    UNTIL end_flag END REPEAT;

    #关闭游标

    close album_curosr;

    END

    2,调用存储过程

    CALL alter_view_counts()

    展开全文
  • 注意每个版本的mysq的存储过程,触发器写法都会有些许区别,注意查看官方版本,不然你网上copy的语句可能执行无效,或者不成功官方英文说明文档https://dev.mysql.com/doc/refman/5.7/en/cursors.htmldemo 数据版本...
  • mysql 存储过程 查询结果集循环处理游标使用 注意每个版本的mysq的存储过程,触发器写法都会有些许区别,注意查看官方版本,不然你网上copy的语句可能执行无效,或者不成功 官方英文说明文档 ...
  • 最近有个需求,要批量创建一批数据,但要以另一张表筛选出的结果集作为条件,逻辑很简单,只是需要用到存储过程,之前都没用过,所以写篇文章记录下,下面的代码可以作为示例。 ##创建存储过程之前需判断该存储...
  • 在使用mysql.exe 命令行执行时 直接 传递编写好的过程sql文件 会出错 cmd脚本如下 rem @echo off mysql.exe -h 10 . 80 . 1 . 74 -u root -p < query.sql > result.txt echo " !!! !!! !!! !!! ...
  • 现在有这样一个需求,将A表中的汇总结果,用于更新B表中的字段。 select count(*) as c,sum(xxx) as s from A where yyy=zzz; update B set x=c,y=s ...实现这个需求可以用存储过程。 1. A表中是以yyy字段作为...
  • 2020年3月25日11:16:15注意每个版本的mysq的存储过程,触发器写法都会有些许区别,注意查看官方版本,不然你网上copy的语句可能执行无效,或者不成功官方英文说明文档...dbForge Studio ...
  • SQL Server 存储过程 循环遍历结果集

    千次阅读 2013-09-22 15:35:29
    --如果记录存在,进入循环 IF(@pid is not null and @pid != 0) BEGIN set @total = 0; DECLARE vend_cursor CURSOR FOR SELECT tbTask.AlreadyPercent,tbTask.Percents FROM tbTask where tbTask....
  • 背景:需要从shxh40_test 这张表 获取 upperpolicyno,serialno,kindcode,...-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除DROP PROCEDURE IF EXISTS shxc40;-- 创建存储过程CREATE PROCEDUR...
  • mysql存储过程循环遍历查询结果集

    万次阅读 多人点赞 2019-07-31 19:37:33
    -- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除 DROP PROCEDURE IF EXISTS init_reportUrl; -- 创建存储过程 CREATE PROCEDURE init_reportUrl() BEGIN -- 定义变量 DECLARE s int DEFAULT 0; ...
  • MySQL存储过程循环遍历查询到的结果集 1、创建存储过程 根据MySQL的语法创建存储过程,要注意的是如果循环遍历查询到的结果集,取出结果集中的数据做操作。 CREATE DEFINER=`root`@`%` PROCEDURE `alte...
  • -- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除DROP PROCEDURE IF EXISTS init_reportUrl;-- 创建存储过程CREATE PROCEDURE init_reportUrl()BEGIN-- 定义变量DECLARE s int DEFAULT 0;DECLARE ...
  • 会对你有不错的帮助,有不足之处还请指教导航: 一、存储过程的创建及调用二 、游标的使用三、 示例四、补充说明:1、用到的两个数据表:from_datato_data2、示例需求 :将表from_data 的select结果集循环插入到表to...
  • 使用navicat创建存储过程 BEGIN #Routine body goes here... DECLARE startTime DATETIME default date_sub(now(),interval 60 MINUTE);#起始时间 当前时间前一小时 DECLARE endTime DATETIME default NOW();...
  • demo format: BEGIN declare cursorName cursor for (select sql); //声明游标和取得结果集 declare continue handler for not found set condition_arg = value1; set condition_arg = value2 ; //设置结...
  • -- 定义游标,并将sql结果集赋值到游标中 DECLARE company_list CURSOR FOR SELECT company_id,max_id FROM customer_job WHERE id>1; -- 声明当游标遍历完全部记录后将标志变量置成某个值 DECLARE CONTINUE ...
  • 有这么个需求 topic表中有id 和comments数量 commemt表中有 topicid 和id 由于历史原因,【删除评论表中时未同步删除topic中评论数量的值】,所以要求一条语句执行...# 创建存储过程 add_test CREATE PROCEDURE add
  • //这里的select每循环一次,值就不一样,我想让他作为结果集返 //回应该怎么做 set Str=substring(Str,inext+1); set i=i+1; select Str,ind,inext,len,unit; end; end if; end; end if; end; set ind=inext+1; end...
  • create PROCEDURE updatedate() BEGIN DECLARE did int ; DECLARE stdNo VARCHAR(120); DECLARE stdName VARCHAR(120); DECLARE sql_update VARCHAR(500); -- 遍历数据结束标志 DECLARE done INT DEFAULT FALSE.....
  • 背景:需要从shxh40_test 这张表 获取 upperpolicyno,serialno,kindcode,...-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除DROP PROCEDURE IF EXISTS shxc40;-- 创建存储过程CREATE PROCEDUR...
  • 背景:  需要从shxh40_test 这张表 获取 upperpolicyno,serialno,kindcode,oldregistno,uniqueno 这几个...-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除 DROP PROCEDURE IF EXISTS shxc40; ...
  • -- 定义游标,并将sql结果集赋值到游标中 DECLARE company_list CURSOR FOR SELECT company_id,max_id FROM customer_job WHERE id>1; -- 声明当游标遍历完全部记录后将标志变量置成某个值 DECLARE CONTINUE ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 508
精华内容 203
关键字:

存储过程循环结果集