Mysql存储过程、函数、触发器、游标 简易使用说明及示例

存储过程、函数、触发器、游标 简易使用说明及示例

存储过程

CREATE DEFINER=root@% PROCEDURE prod_test(IN p_name varchar(10))
BEGIN

select *
from rural_sys_org_user
where name like CONCAT(‘%’,p_name,‘%’);

END

调用存储过程

call prod_test(xxx);

存储过程示例（游标）

CREATE DEFINER=`root`@`%` PROCEDURE `add_cost_project`()
BEGIN
   #声明结束标识
   DECLARE end_flag int DEFAULT 0;

   DECLARE company_id int;
   DECLARE lease int;
   DECLARE dormitory int;

   # 声明游标 curosr
   DECLARE curosr CURSOR FOR SELECT id FROM sys_company where pid != 0 and id != 24;

   #设置终止标志
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET end_flag = 1;
--     DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;

   # 打开游标
   OPEN curosr;

   # 遍历游标
   REPEAT
      #获取当前游标指针记录，取出值赋给自定义的变量
      FETCH curosr INTO company_id;
         #利用取到的值进行数据库的操作
         insert into cost_project
         (`pid`, `project`, `unit`, `rule_id`, `bill_type_id`, `create_time`, `company_id`) values
         (0, '房租类', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (0, '宿舍费用', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (0, '其它', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id);

         select id from cost_project where cost_project.company_id = company_id and project = '房租类' limit 1 into lease;
         select id from cost_project where cost_project.company_id = company_id and project = '宿舍费用' limit 1 into dormitory;

         insert into cost_project
         (`pid`, `project`, `unit`, `rule_id`, `bill_type_id`, `create_time`, `company_id`) values
         (lease, '环保基础设施费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (lease, '电梯维护费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (lease, '地下室租赁费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (lease, '剧毒仓库租赁费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (lease, '其它租赁费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (lease, '综合管理服务费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (lease, '保证金', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (dormitory, '宿舍租赁费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (dormitory, '宿舍电费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id),
         (dormitory, '宿舍水费', '之恩环保', 0, 1, '2022-12-01 00:00:00', company_id);

   # 根据 end_flag 判断是否结束
   UNTIL end_flag END REPEAT;

   #关闭游标
   close curosr;

   end;

   call add_cost_project();

注意其中的 limit 1，否则报错

局部变量company_id与字段名company_id重名时，表名.字段

其中使用了游标

函数

自定义函数与存储过程，主要区别为函数有return ；

系统自带常用函数：

1. GROUP_CONCAT([DISTINCT] column1 [ORDER BY column2 ASC\DESC] [SEPARATOR seq])

   示例

   select GROUP_CONCAT(DISTINCT short_name ORDER BY short_name ASC SEPARATOR ‘,’)
   from (select short_name from sys_company
   where pid != 0
   ORDER BY short_name
   ) q

2. cancat(a, b, c)

3. if(end_flag = 0,“∞”,end_date)

4. ifnull( , )

5. case when then else end;

6. date_format( time, ‘%Y%m%d’) = date_format( now(), ‘%Y%m%d’)

7. DATE_SUB(curdate(), INTERVAL 0 MONTH) DATE_SUB(CURDATE(), INTERVAL 7 DAY)

8. to_days()计算日期举例0000年1月1日的天数 year() QUARTER() month()

9. round(123.3453,2) 四舍五入

自定义函数

语法

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION functionName ( varName varType [, … ] )
RETURNS returnVarType
[characteristic …]
routine_body

说明

functionName：函数名，同MySQL内置函数一样，大小写不敏感
varName: 形参名
varType: 形参类型，其与varName配对使用。形参数量不限( ≥ 0 \geq 0≥0)
returnVarType: 返回值类型。函数必须有且只能有一个返回值
characteristic：函数特性，下将详述
routine_body：函数体。函数体中必须含有 return 语句，当函数体为复合结构时，需要使用begin … end 语句

示例

DROP FUNCTION if exists myfunTest KaTeX parse error: Expected 'EOF', got '#' at position 106: … #̲ 声明定义1个变量, 初值默认…
DELIMITER ;

触发器

语法

create trigger xxx before/after insert/delete/update on table_name

for each row +

• 单条语句
• begin 多条语句 end
• set xxx = ddd

关键字

OLD.xxx以及 NEW.xxx

适用范围

insert after new

delete before old

update before/after new/old