mysql 事务存储过程案例集合

存储过程案例大全

基本说明

create procedure pro_test5()

begin

declare height int default 175; //声明变量height为int类型，默认值为175

declare description varchar(50);

if height >= 180 then //判断变量height 大等于180  then然后就

set description = '高度不低于180'; //写入变量的值为字符串

elseif height >= 170 and height < 180 then //判断其他情况， 变量的值在170和180之间然后就

set description = '鏍囧噯韬潗';

else //其他所有情况

set description = '涓€鑸韩鏉;

end if;

select description; //select打印变量

end;

=========================================================

delimiter $$

-- delimiter [dɪ'lɪmɪtə] 分隔符；声明结束符号

-- procedure [prəˈsidʒər] 程序，过程     in 输入数据库，date定义变量，类型

CREATE proceduer eisc(out date varchar(20))

BEGIN

SELECT * FROM rota;

end

创建一个存储过程，将结果传给date

调用： 

call eisc(@date)

select @date

 1、创建存储过程

DROP  PROCEDURE  IF EXISTS replaceStr;

DELIMITER $$

CREATE  PROCEDURE `replaceStr`(INOUT oldStr VARCHAR(1024), IN replace1 VARCHAR(16), IN replace2 VARCHAR(16))  

 BEGIN  

 SET @i=0;  

 -- 获取参数replace1出现的次数

 SET @count = CHAR_LENGTH(oldStr)-CHAR_LENGTH(REPLACE(oldStr, replace1, '')); 

 -- 先申明一个变量存放替换之后的字符串

 SET @newStr = ''; 

 WHILE @i <= @count  

 DO      

    SET @i=@i+1;  

    IF(@i != 1) THEN

        SELECT CONCAT(@newStr, replace2, SUBSTRING_INDEX(SUBSTRING_INDEX(oldStr, replace1 ,@i), replace1 ,-1)) INTO @newStr;

    ELSE 

        SELECT CONCAT(@newStr, SUBSTRING_INDEX(SUBSTRING_INDEX(oldStr, replace1, @i), replace1, -1)) INTO @newStr;

    END IF;

 END WHILE;  

 -- 把替换之后的字符串赋给旧字符串变量

 SET oldStr = @newStr;

END; 

// 2、调用存储过程

SET @arrayStr = '1 2 3 4 5 6 7';

CALL replaceStr(@arrayStr, ' ', '-');

SELECT @arrayStr;

2、游标简单使用

1、建立一张学生表  

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(256) DEFAULT NULL,

  `class` varchar(256) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', '张三', '1班');

INSERT INTO `student` VALUES ('2', '李四', '2班');

INSERT INTO `student` VALUES ('3', '王五', '3班');

INSERT INTO `student` VALUES ('4', '麻子', '1班');

INSERT INTO `student` VALUES ('5', '老王', '3班');

复制代码

2、建立存储过程

复制代码

DROP  PROCEDURE  IF EXISTS fors;

DELIMITER $$

CREATE PROCEDURE fors(OUT namess VARCHAR(1024))

BEGIN

    DECLARE a VARCHAR(500);

    DECLARE Done, nameCount INT DEFAULT 0 ;

    DECLARE rs CURSOR FOR SELECT NAME FROM student;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1 ;

    -- 不申明结果为NULL

    SET namess = '';

    -- 打开游标

    OPEN rs;

        FETCH NEXT FROM rs INTO a;

        REPEAT

            SET nameCount = nameCount + 1 ;

            IF(nameCount != 1 ) THEN

        SELECT CONCAT(namess,',' , a) INTO namess;

        ELSE

        SELECT CONCAT(namess, a) INTO namess;

            END IF;

        FETCH NEXT FROM rs INTO a;

    UNTIL Done END REPEAT;

    CLOSE rs;

END

复制代码

3、调用存储过程

CALL fors(@names);

SELECT @names;

4、执行结果

张三,李四,王五,麻子,老王

3.游标+while简单使用

1、建立一张学生表

复制代码

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(256) DEFAULT NULL,

  `class` varchar(256) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', '张三', '1班');

INSERT INTO `student` VALUES ('2', '李四', '2班');

INSERT INTO `student` VALUES ('3', '王五', '3班');

INSERT INTO `student` VALUES ('4', '麻子', '1班');

INSERT INTO `student` VALUES ('5', '老王', '3班');

复制代码

2、建立存储过程 

复制代码

DROP  PROCEDURE  IF EXISTS whileFor;

DELIMITER $$ 

CREATE PROCEDURE whileFor(OUT namess VARCHAR(1024))

BEGIN

    DECLARE a VARCHAR(500);

    DECLARE i , j , Done INT DEFAULT 0 ;

    -- 申明游标

    DECLARE rs CURSOR FOR SELECT NAME FROM student;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1 ;

    SET namess = '' , i = 0 , j = 1;

    -- 循环四次

    WHILE (j < 5) DO

    -- 如果不是第一次，我们这里加一个|区分

    IF(j > 1) THEN 

       SELECT CONCAT(namess,'|') INTO namess; 

    END IF;

    -- 打开游标

        OPEN rs;

            FETCH NEXT FROM rs INTO a;

            REPEAT

                SET i = i + 1 ; 

                IF(i != 1 ) THEN

                    SELECT CONCAT(namess,',' , a) INTO namess;

                ELSE

                    SELECT CONCAT(namess , a) INTO namess;

                END IF;

            FETCH NEXT FROM rs INTO a;

        UNTIL Done END REPEAT;

        CLOSE rs;

        SET j = j + 1;

        SET Done = 0 ; 

        SET i = 0 ;

    END WHILE;

END

复制代码

3、调用存储过程

CALL whileFor(@names);

SELECT @names;

  4、执行结果

张三,李四,王五,麻子,老王|张三,李四,王五,麻子,老王|张三,李四,王五,麻子,老王|张三,李四,王五,麻子,老王

4.游标+游标简单使用 

1、建立一张学生表

复制代码

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(256) DEFAULT NULL,

  `class` varchar(256) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', '张三', '1班');

INSERT INTO `student` VALUES ('2', '李四', '2班');

INSERT INTO `student` VALUES ('3', '王五', '3班');

INSERT INTO `student` VALUES ('4', '麻子', '1班');

INSERT INTO `student` VALUES ('5', '老王', '3班');

复制代码

2、建立存储过程 

复制代码

DROP  PROCEDURE  IF EXISTS forFor;

DELIMITER $$

CREATE  PROCEDURE   forFor(OUT classNames VARCHAR(1024))   

BEGIN

-- 班级名、姓名

DECLARE  classs , namess VARCHAR(500);

-- 班级数量、该班级下面学生的数量、班级变量++、学生变量++ 

DECLARE  classNumber , nameNumber, classCount , nameCount INT  DEFAULT   0;

-- 建立两个游标，第一个游标用来存储班级、第二个用来存储学生姓名

DECLARE  class_csr CURSOR  FOR  SELECT  class FROM   student GROUP  BY  class;

DECLARE  name_csr CURSOR   FOR  SELECT  NAME  FROM  student WHERE  class = classs ; 

-- 获取按班级分组之后的数量

SELECT   COUNT(*) INTO classNumber FROM  (SELECT   COUNT(*) FROM  student GROUP  BY  class) t;

SET classNames = '' ;

   -- 打开班级游标

   OPEN   class_csr;

    -- 开始班级游标循环

    class_loop: LOOP  

        FETCH   class_csr INTO  classs ;

        SET  classCount = classCount +1 ;

        IF(classCount != 1 ) THEN

        SELECT CONCAT(classNames,',' , classs, '(') INTO classNames;

        ELSE

        SELECT CONCAT(classNames, classs, '(') INTO classNames;

            END IF;

            -- 获取该班级下面学生的数量

        SELECT  COUNT(*) INTO  nameNumber FROM  student WHERE   class = classs;

        -- 打开学生游标

        OPEN  name_csr;

        -- 开始学生游标循环

        name_loop: LOOP  

        FETCH  name_csr INTO  namess;

            SET  nameCount = nameCount +1 ;

            IF(nameCount != 1 ) THEN

                SELECT CONCAT(classNames,',' , namess) INTO classNames;

            ELSE

                SELECT CONCAT(classNames , namess) INTO classNames;

            END IF;

            -- 如果学生变量等于学生数量，那么就终止该游标（第一个）

            IF(nameCount = nameNumber) THEN

                LEAVE name_loop;

            END  IF;

        -- 结束学生游标循环

        END LOOP name_loop;

        -- 关闭学生游标

        CLOSE  name_csr;

        SET  nameCount = 0 ;

        SELECT CONCAT(classNames , ')') INTO classNames;

        -- 如果班级变量等于班级数量，那么就终止该游标（第二个）

        IF(classCount = classNumber) THEN

        LEAVE class_loop;

        END  IF;

    -- 结束班级游标循环

    END LOOP class_loop;

  -- 关闭班级游标

  CLOSE   class_csr;

END  ;

复制代码

3、调用存储过程 

CALL forFor(@classNames);

SELECT @classNames;

菜鸟教程       mysql存储过程