1、简单的存储过程
-- 设置结束符号
delimiter $$
-- 创建存储，命名为hello_world，可以传参，就跟封装方法一样
CREATE PROCEDURE hello_world()
BEGIN
SELECT 'hello world';
END $$
-- 调用
CALL hello_world()
2、设置变量的存储过程
delimiter $$
CREATE PROCEDURE sp_var01()
BEGIN
-- DECLARE对变量描述，设置类型
DECLARE var01 VARCHAR(32) DEFAULT 'SF';
SELECT var01; -- SF
-- 设置变量初始值，该变量为局部变量，只作用域begin里
SET var01 = 100;
SELECT var01; -- 100
END$$
-- 删除存储
DROP PROCEDURE sp_var01;
CALL sp_var01();
3、传参的存储过程
delimiter //
CREATE PROCEDURE sp_var_into()
BEGIN
-- @xx用户变量赋值
SELECT d.cname into @clname FROM t_class d WHERE d.cno=2;
END//
CALL sp_var_into();
SELECT @clname;
-- ==================================================================
delimiter //
/*
IN:入参
OUT:出参
age：形参名称
INT:参数类型，如果是char和varchar必须加上范围（）
*/
CREATE PROCEDURE sp_param(IN age INT)
BEGIN
set @use_age = age; -- 相当于实例属性
END//
CALL sp_param(998);
SELECT @use_age;
-- ==================================================================
delimiter //
/*
t_class表中：传入room，返回一个cname
*/
-- DESC t_class;
CREATE PROCEDURE sp_param02(IN croom CHAR(10),OUT clname VARCHAR(10))
BEGIN
-- 将t_class表里的room传参给入参的croom，然后结果赋值给变量clname
SELECT cname INTO clname from t_class WHERE room=croom;
END//
-- DROP PROCEDURE sp_param02;
CALL sp_param02('r101',@clname);
-- CALL sp_param02('r102',clname); -- 1054 - Unknown column 'clname' in 'field list'
SELECT @clname;
4、if判断的存储过程
/*
需求：
入职年限<=38是新手 >38并且<=40老员工 其它元老
*/
SELECT * FROM t_student;
-- 查询张三至今的年龄:函数TIMESTAMPDIFF(单位，起，始值)
SELECT TIMESTAMPDIFF(YEAR,e.hiredate,NOW()) FROM t_student e WHERE e.sname = "张三";
delimiter //
CREATE PROCEDURE sp_hire_if()
BEGIN
-- 声明一个变量接受结果是什么员工
DECLARE result VARCHAR(32);
-- 将年龄传参给year
DECLARE years int;
SELECT TIMESTAMPDIFF(YEAR,e.hiredate,NOW()) INTO years FROM t_student e WHERE e.sname = "张三";
-- 判断
IF years >40 THEN
set result = '元老';
ELSEIF years > 38 THEN
set result = '老员工';
ELSE
set result = '新手';
END IF;
SELECT result;
SELECT years;
END//
-- DROP PROCEDURE sp_hire_if;
-- 调用call
CALL sp_hire_if();
5、while循环的存储过程
while单循环
delimiter //
CREATE PROCEDURE sp_flow_while()
BEGIN
DECLARE c_index INT DEFAULT 1;
-- 收集结果字符串
DECLARE result_str VARCHAR(256) DEFAULT '1';
WHILE c_index<10 DO
set c_index = c_index + 1;
set result_str = CONCAT(result_str,',',c_index);
END WHILE;
SELECT result_str;
END //
CALL sp_flow_while();
SELECT DATE_ADD(now(),INTERVAL 1 MONTH); -- 获取一个月后的日期
SELECT LAST_DAY(NOW()); -- 获取日期的最后一天
SELECT YEAR(LAST_DAY(NOW())); -- 获取年
SELECT MONTH(LAST_DAY(NOW())); -- 获取月
SELECT DAYOFMONTH(LAST_DAY(DATE_ADD(now(),INTERVAL 1 MONTH))); -- 下个月月最后一天是几号
while嵌套循环
delimiter //
CREATE PROCEDURE add_team()
BEGIN
DECLARE team_num INT DEFAULT 1;
DECLARE org_num INT DEFAULT 711;
DECLARE create_by INT DEFAULT 4857;
-- 设置外层循环次数，机构id为边界
WHILE org_num < 769 DO
WHILE team_num < 5001 DO
INSERT INTO team(team_name,hospital_id,create_by)
VALUES(CONCAT("班组",team_num,org_num),org_num,create_by);
set team_num = team_num + 1;
-- 内层循环第一个机构创建6个班组后，退出循环
END WHILE;
-- 机构id增加，对应机构创建者id增加
SET org_num =org_num + 3;
SET create_by = create_by + 15;
-- 此时必须重置内层循环次数的变量，否则team_num直接从6开始，就无法再insert数据
SET team_num = 1;
END WHILE;
END //
CALL add_team();
6、游标：cursor
- 在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，而使用简单的 SELECT 语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。游标在部分资料中也被称为光标。
-
关系数据库管理系统实质是面向集合的，在 MySQL 中并没有一种描述表中单一记录的表达形式，除非使用 WHERE 子句来限制只有一条记录被选中。所以有时我们必须借助于游标来进行单条记录的数据处理。
- 一般通过游标定位到结果集的某一行进行数据修改
delimiter //
CREATE PROCEDURE fetch_test()
BEGIN
DECLARE tellph INT;
DECLARE num_id INT;
DECLARE user_id VARCHAR(22);
-- 定义一个退出flag
DECLARE has_data INT DEFAULT 1;
-- 声明一个游标名称供那个查询使用
DECLARE user_result CURSOR FOR SELECT * FROM t_user;
-- mysql自带的预警退出机制
DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;
-- 打开游标
OPEN user_result;
REPEAT
FETCH user_result INTO num_id,tellph,user_id;
SELECT CONCAT('cid=',num_id,'cellphone=',tellph,'userid=',user_id);
UNTIL has_data =0
END REPEAT;
-- 关闭游标
CLOSE user_result;
END //
DROP PROCEDURE fetch_test;
CALL fetch_test();
repeat语法
- 语法： REPEAT doSomething UNTIL condition END REPEAT ;
- 说明：doSomething 先执行相关操作 ，再判断是否满足条件，满足，结束循环。(和WHILE循环相反) --- 先执行，后判断
网友评论