MySQL之流程控制学习篇

-- 创建学生表
CREATE TABLE `student` (
  `id` int(11) NOT NULL COMMENT '编号',
  `sex` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别,1:男,2:女',
  `name` varchar(16) NOT NULL DEFAULT '' COMMENT '姓名',
	 `age` int(11)  NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

-- 插入数据
INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (2, 2, '李四', 24);
INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (3, 2, '李四', 25);

-- 查询
select * from student

-- -------------存储过程 case when then---------------
-- 删除存储过程
DROP PROCEDURE IF EXISTS proc_test1;
DELIMITER $
CREATE PROCEDURE proc_test1(id int,sex_str varchar(8),name varchar(16),age int(10))
  BEGIN
    DECLARE t_sex TINYINT UNSIGNED;
    CASE t_sex
      when '男' THEN
      SET t_sex = 1;
      ELSE 
      set t_sex = 2;
    END CASE;
    INSERT INTO student VALUES (id,t_sex,name,age);
  END $
DELIMITER;

-- 调用存储过程
CALL proc_test1(4,'男','wangwu',6)

SELECT * from student 


-- ----------------函数 case when then----------------
DROP FUNCTION IF EXISTS fun_test1;
DELIMITER $
CREATE FUNCTION fun_test1(sex TINYINT UNSIGNED)
  RETURNS varchar(8)
  BEGIN
    DECLARE t_sex VARCHAR(8);
    CASE sex
    WHEN 1 THEN
      SET t_sex:='男';
    ELSE
      SET t_sex:='女';
    END CASE;
    RETURN t_sex;
  END $
DELIMITER ;

 select sex, fun_test1(sex),name,age FROM student;
 
 
 
 -- -------------存储过程 if---------------
-- 删除存储过程
DROP PROCEDURE IF EXISTS proc_test2;
DELIMITER $
CREATE PROCEDURE proc_test2(t_id int,sex_str varchar(8),name varchar(16),age int(10),out result int)
  BEGIN
	-- 定义变量
      DECLARE t_count TINYINT DEFAULT 0;
			select count(id) into  t_count from student where id=t_id;
			 if t_count>0 THEN
			 BEGIN
        INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (6, 2, 'wangwu', 6);
				select 1 into result;
      END;
			 else
      BEGIN
        INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (7, 2, 'wangwu', 6);
				select 0 into result;
      END;
    END IF;
  END $
DELIMITER;

-- 调用存储过程
CALL proc_test2(4,'男','wangwu',6,@result)

SELECT * from student 


-- ------无循环控制语句-----------
DROP PROCEDURE IF EXISTS proc_test3;
DELIMITER $
CREATE PROCEDURE proc_test3(t_count int)
  BEGIN
    DECLARE i int DEFAULT 8;
    a:WHILE i<=t_count DO
      INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (i, 2, 'wangwu', 6);
      SET i=i+1;
    END WHILE;
  END $
	
	call proc_test3(9)
	
	-- -----LEAVE 控制循环------
DROP PROCEDURE IF EXISTS proc_test4;
DELIMITER $
CREATE PROCEDURE proc_test4(t_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:WHILE i<=t_count DO
       INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (i, 2, 'wangwu', 6);
      IF i=10 THEN
        LEAVE a;
      END IF;
      SET i=i+1;
    END WHILE;
  END $
DELIMITER ;

CALL proc_test4(30);

	-- -----LEAVE 控制循环------
DROP PROCEDURE IF EXISTS proc_test5;
DELIMITER $
CREATE PROCEDURE proc_test5(t_count int)
  BEGIN
    DECLARE i int DEFAULT 0;
    a:WHILE i<=t_count DO
		  set i=i+1;
      IF i%2=0 THEN
        ITERATE a;
      END IF;
      INSERT INTO `itstack`.`student`(`id`, `sex`, `name`, `age`) VALUES (i, 2, 'wangwu', 6);
    END WHILE;
  END $
DELIMITER ;

CALL proc_test5(10);