mysql存储过程对查询结果遍历 mysql存储过程循环语句

转载

索姆拉 2023-09-08 20:13:05

文章标签 mysql存储过程对查询结果遍历存储过程 mysql SQL 文章分类 MySQL 数据库

今天需要用到MYSQL，研究了下，把项目的需要的存储过程写了一部分，写一下工作总结。这里没有给出数据库结构，不讨论SQL语句的细节，主要探讨存储过程语法，适合有基础的人。

快速入门

理解：

　　 迄今为止，我们学过的大多数SQL语句都是针对一个或多个表的单条语句。但是并不是所有的操作都是可以用一条语句来完成的，经常有一些操作是需要多条语句配合才能完成。我们引入的存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
　　简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为增强版的批处理文件。　　

使用存储过程的好处：
　　☐ 通过把处理封装在一个易用的单元中，可以简化复杂的操作。
　　☐ 不需要反复建立一系列的处理步骤，因而保证了数据的一致性。
　　☐ 简化了对变动的管理，这一点的延伸就是安全性。
　　☐ 存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。

创建：

创建语句：

mysql存储过程对查询结果遍历 mysql存储过程循环语句_SQL

一般形式：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])

说明：
　　DELIMITER $$ DELIMITER; 用来定义分隔符，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

调用：

示例：

mysql存储过程对查询结果遍历 mysql存储过程循环语句_SQL_02

说明：
　　CALL Avg_Price(); 执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后需要有 () 符号(即使不传递参数也需要)。

删除：

mysql存储过程对查询结果遍历 mysql存储过程循环语句_SQL_03

说明：
　　请注意只给出了存储过程名，并没有书写()。

使用参数

说明　　

　　在上面我们简单地显示SELECT语句的结果，一般存储过程并不显示结果，而是把结果返回给你指定的变量。
　　现在我们需求如下：

　　　　计算商品的最低，最高和平均价格，并保存到三个变量中。

创建存储过程

mysql存储过程对查询结果遍历 mysql存储过程循环语句_SQL_04

调用此存储过程

mysql存储过程对查询结果遍历 mysql存储过程循环语句_存储过程_05

使用IN参数传入条件

　　创建存储过程：

mysql存储过程对查询结果遍历 mysql存储过程循环语句_存储过程_06

　　调用存储过程：

mysql存储过程对查询结果遍历 mysql存储过程循环语句_SQL_07

更加智能的存储过程

说明：

　　迄今为止使用的所有存储过程基本上都是封装MySQL简单的 SELECT语句。虽然它们全都是有效的存储过程例子,但它们所能完成的工作你直接用这些被封装的语句就能完成(如果说它们还能带来更多的东西,那就是使事情更复杂)。只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来，来使我们的语句执行更加可靠和智能，比如我们可以声明局部变量、添加内部注释、使用循环或判断语句等等。

实例：

mysql存储过程对查询结果遍历 mysql存储过程循环语句_mysql_08

常用控制语句及示例

(1). 条件语句

Ⅰ. if-then -else语句

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc2(IN parameter int)  
         -> begin 
         -> declare var int;  
         -> set var=parameter+1;  
         -> if var=0 then 
         -> insert into t values(17);  
         -> end if;  
         -> if parameter=0 then 
         -> update t set s1=s1+1;  
         -> else 
         -> update t set s1=s1+2;  
         -> end if;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

Ⅱ. case语句：

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc3 (in parameter int)  
         -> begin 
         -> declare var int;  
         -> set var=parameter+1;  
         -> case var  
         -> when 0 then   
         -> insert into t values(17);  
         -> when 1 then   
         -> insert into t values(18);  
         -> else   
         -> insert into t values(19);  
         -> end case;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

(2). 循环语句

Ⅰ. while ···· end while：

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc4()  
         -> begin 
         -> declare var int;  
         -> set var=0;  
         -> while var<6 do  
         -> insert into t values(var);  
         -> set var=var+1;  
         -> end while;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

Ⅱ. repeat···· end repeat：

它在执行操作后检查结果，而while则是执行前进行检查。

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc5 ()  
         -> begin   
         -> declare v int;  
         -> set v=0;  
         -> repeat  
         -> insert into t values(v);  
         -> set v=v+1;  
         -> until v>=5  
         -> end repeat;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

Ⅲ. loop ·····end loop:

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

mysql > DELIMITER //  
    mysql > CREATE PROCEDURE proc6 ()  
         -> begin 
         -> declare v int;  
         -> set v=0;  
         -> LOOP_LABLE:loop  
         -> insert into t values(v);  
         -> set v=v+1;  
         -> if v >=5 then 
         -> leave LOOP_LABLE;  
         -> end if;  
         -> end loop;  
         -> end;  
         -> //  
    mysql > DELIMITER ;

Ⅳ. LABLES 标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

(3). ITERATE迭代

Ⅰ. ITERATE:

通过引用复合语句的标号,来从新开始复合语句

mysql > DELIMITER //  
        mysql > CREATE PROCEDURE proc10 ()  
             -> begin 
             -> declare v int;  
             -> set v=0;  
             -> LOOP_LABLE:loop  
             -> if v=3 then   
             -> set v=v+1;  
             -> ITERATE LOOP_LABLE;  
             -> end if;  
             -> insert into t values(v);  
             -> set v=v+1;  
             -> if v>=5 then 
             -> leave LOOP_LABLE;  
             -> end if;  
             -> end loop;  
             -> end;  
             -> //  
        mysql > DELIMITER ;

#查询文章回复
-- ----------------------------
-- Procedure structure for `sp_select_reply_article`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_select_reply_article`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_select_reply_article`(IN `ra_id` int,IN `pagefrom` int,IN `pagesize` int)
BEGIN
         #Routine body goes here...
         SET @ra_id = ra_id;
         SET @pagefrom = pagefrom;
         SET @pagesize = pagesize;
         SET @ssra = CONCAT('SELECT * FROM gk_article WHERE id = ? LIMIT ?,?');
         PREPARE sqlquery FROM @ssra;
         EXECUTE sqlquery USING @ra_id,@pagefrom,@pagesize;
END
;;
DELIMITER ;

#技术点1：MySql5.1不支持LIMIT参数（MySql5.5就支持了），如果编写存储过程时使用LIMIT做变量，那是需要用动态SQL来构建的，而这样做性能肯定没有静态SQL好。主要代码如下：

SET @ssra = CONCAT('SELECT * FROM gk_article WHERE id = ? LIMIT ?,?');

PREPARE sqlquery FROM @ssra;

EXECUTE sqlquery USING @ra_id,@pagefrom,@pagesize;

#技术点2：如果同时需要返回受影响行数需要在语句后面添加语句：ROW_COUNT()函数，两条语句之间需要“;”分隔。

#更新数据
-- ----------------------------
-- Procedure structure for `sp_update_permission`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_update_permission`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_permission`(IN `puser_uid` varchar(20),IN `plevel` int,IN `ppower` int)
BEGIN
         #Routine body goes here...
        
         SET @puser_uid = puser_uid;
         SET @plevel = plevel;
         SET @ppower = ppower;
         UPDATE gk_permission SET `level` = @plevel, power = @ppower WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci;
END
;;
DELIMITER ;

#技术点3：MySQL进行字符串比较时发生错误（Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='），解决方法：将比较等式一边进行字符串转换，如改为“CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci”，主要代码如下：

UPDATE gk_permission SET `level` = @plevel, power = @ppower WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci;

#插入数据
-- ----------------------------
-- Procedure structure for `sp_insert_user`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_insert_user`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_user`(IN `uid` varchar(20),IN `upw` varchar(32),IN `name` varchar(20),IN `sex` int,IN `phone` varchar(20),IN `u_id` int,IN `s_id` int,IN `j_id` int)
BEGIN
         #Routine body goes here...
         SET @uid = uid;
         SET @upw = upw;
         SET @uname = uname;
         SET @sex = sex;
         SET @phone = phone;
         #由于外键约束，所以添加的外键字段需要在对应外键所在表有相应数据
         SET @u_id = u_id;
         SET @s_id = s_id;
         SET @j_id = j_id;
         SET @verifytime = DATE('0000-00-00');
         INSERT INTO gk_user(uid,upw,uname,sex,phone,u_id,s_id,j_id,verifytime) 
　　　　　　　VALUES(@uid,@upw,@uname,@sex,@phone,@u_id,@s_id,@j_id,@verifytime);
         #查询结果会自动返回受影响行数
END
;;
DELIMITER ;

#根据ID删除数据

-- ----------------------------
-- Procedure structure for `sp_delete_exchange_by_id`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_delete_exchange_by_id`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_exchange_by_id`(IN `eid` int)
BEGIN
         #Routine body goes here...
         SET @eid = eid;
         DELETE FROM gk_exchange WHERE id = @eid;
END
;;
DELIMITER ;

#通过账号查询用户或者管理员

-- ----------------------------
-- Procedure structure for `sp_select_user_by_uid`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_select_user_by_uid`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_select_user_by_uid`(IN `uid` varchar(20),IN `getAdmin` int)
BEGIN
         #Routine body goes here...
         SET @uid = uid;
         #SET @getadmin = getAdmin;
         #查询管理员
         IF (getAdmin = 1) THEN
                   SELECT us.*, un.`name`, se.`name`, jo.`name`, pe.`level`, pe.power FROM gk_user AS us, gk_unit AS un, gk_section AS se, gk_jobtitle AS jo, gk_permission AS pe WHERE us.u_id = un.id AND us.s_id = se.id AND us.j_id = jo.id AND us.uid = pe.user_uid AND us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
         END IF;
         #查询用户
         IF (getAdmin = 0) THEN
                   SELECT us.*, un.`name`, se.`name`, jo.`name` FROM gk_user AS us, gk_unit AS un, gk_section AS se, gk_jobtitle AS jo WHERE us.u_id = un.id AND us.s_id = se.id AND us.j_id = jo.id AND us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
         END IF;
END
;;
DELIMITER ;

#技术点4：这个存数过程需要用到控制语句（if else elseif while loop repeat leave iterate）。

IF (getAdmin = 1) THEN

#语句…

END IF;

#技术点5：在传入参数不匹配的情况下报错（Column count doesn't match value count at row 1），这个就是细心问题了，详细检查参数吧。

#技术点6：获取当前时间的函数：NOW()

#技术点7：“`”这个符号是反单引号，两个反单引号夹起来的会被当做变量，一般是在定义字段时遇到关键字冲突的时候会用到。

访问我


关注天云定期福利