首页 > 数据库 >MySQL存储过程示例代码

MySQL存储过程示例代码

时间：2024-08-21 09:15:16浏览次数：12  
标签：存储 set 示例 -- work machine ifnull MySQL id 

CREATE DEFINER=`root`@`%` PROCEDURE `getReportWeavingProductionDay`(in startTime datetime,in endTime datetime,in factoryIds varchar(100),in machineIds varchar(5000),in PageSize int,in DataFromIndex int)
BEGIN
-- 参数说明
-- startTime：开始时间，endTime：结束时间,factoryIds:工厂ID，多个工厂用逗号隔开，machineIds:机器ID,多个机器ID用逗号隔开
-- 1、判断结果临时表是否存在
drop temporary table if exists ReportWeavingProductionDay; -- 要确认会不会删除其他库

-- 2、数据计算
-- 2.1、参数处理
-- 2.2、分页计算
-- set @DataFromIndex=PageIndex * PageSize;


-- 2.3 、锁定主表范围，填充数据(plan_id正常>0即可，让走索引，用>=1，select部分略快,1000是永远小于plan_id，所以没问题)
create temporary table ReportWeavingProductionDay as
select A.* from terminal_output_data A
  left join base_machine_loom B on A.machine_id = B.machine_id
where A.plan_id>=10000 and B.status='3' and B.is_forbidden='0'
  and A.work_date>=startTime and A.work_date<=endTime
  and case factoryIds when '' then 1=1 else find_in_set(B.factory_id,factoryIds) end
  and case machineIds when '' then 1=1 else find_in_set(B.machine_loom_id,machineIds) end
limit PageSize offset DataFromIndex;

alter table ReportWeavingProductionDay add index idx_ReportWeavingProductionDay_workrecordid(work_record_id);
alter table ReportWeavingProductionDay add index idx_ReportWeavingProductionDay_planid(plan_id);
alter table ReportWeavingProductionDay add index idx_ReportWeavingProductionDay_machineid(machine_id);


-- 3.1 大表数据取锁定范围，写入临时表
-- tmp_work_order_production_plan
drop temporary table if exists tmp_work_order_production_plan;
create temporary table tmp_work_order_production_plan as
select * from work_order_production_plan where only_no in
(
    select distinct plan_id from ReportWeavingProductionDay 
);
alter table tmp_work_order_production_plan add index idx_tmpworkorder_planid(only_no);

set sql_safe_updates=false;
alter table ReportWeavingProductionDay add IsShoe_Name varchar(20);
update ReportWeavingProductionDay A left join tmp_work_order_production_plan B on A.plan_id=B.only_no and B.size_number<>'' and B.size_number is not null
  set A.IsShoe_Name='鞋型';
update ReportWeavingProductionDAy A left join tmp_work_order_production_plan B on A.plan_id=B.only_no and (B.size_number='' or B.size_number is null)
  set A.IsShoe_Name='公版';
set sql_safe_updates=true;

-- tmp_tmp_speed
drop temporary table if exists tmp_tmp_speed;
create temporary table tmp_tmp_speed as
select work_record_id,speed_value from tmp_speed where work_record_id in
(
    select distinct work_record_id from ReportWeavingProductionDay
);
alter table tmp_tmp_speed add index idx_tempspeed_workrecordid(work_record_id);

-- tmp_base_machine_loom
drop temporary table if exists tmp_base_machine_loom;
create temporary table tmp_base_machine_loom as
select machine_id,machine_type_id,factory_id,machine_name,unit_group,collection_mode from base_machine_loom;
alter table tmp_base_machine_loom add index idx_machineloom_machineid(machine_id);

-- 3.2 计算需要关联的列
-- base_machine_loom表相关
alter table ReportWeavingProductionDay 
      add machine_type_id bigint
     ,add machine_name varchar(50)
     ,add factory_id bigint
     ,add unit_group varchar(50)
     ,add collection_mode varchar(10);
set sql_safe_updates=false;
update ReportWeavingProductionDay A left join tmp_base_machine_loom B on a.machine_id=b.machine_id
set A.machine_type_id=B.machine_type_id
   ,A.machine_name=B.machine_name
   ,A.factory_id=B.factory_id
   ,A.unit_group=B.unit_group
   ,A.collection_mode=B.collection_mode;
set sql_safe_updates=true;

-- base_machine_type表相关,machine_type_name(机器类型)
alter table ReportWeavingProductionDay add machine_type_name varchar(50);
set sql_safe_updates=false;
update ReportWeavingProductionDay A left join base_machine_type B on A.machine_type_id=B.machine_type_id
set A.machine_type_name=B.machine_type_name;
set sql_safe_updates=true;

-- base_staff表相关,
alter table ReportWeavingProductionDay add staff_username varchar(50);
set sql_safe_updates=false;
update ReportWeavingProductionDay A left join base_staff B on A.work_man_id=B.staff_id
set A.staff_username=B.staff_username;
set sql_safe_updates=true;

-- base_work_class表相关
alter table ReportWeavingProductionDay add work_class_name varchar(50);
set sql_safe_updates=false;
update ReportWeavingProductionDay A left join base_work_class B on A.work_class_id=B.work_class_id
set A.work_class_name=B.work_class_name;
set sql_safe_updates=true;

-- work_order_production_plan表相关
alter table ReportWeavingProductionDay add order_type varchar(50)
,add material_name varchar(255)
,add sale_order_no varchar(100)
,add color_name varchar(255)
,add muslin_weft_density decimal(10,2)
,add machine_density decimal(10,2)
,add row_card varchar(255)
,add size_name varchar(255)
,add pair_count decimal(10,2)    -- 双数
,add yards_count decimal(10,2);    -- 码数

-- work_order_production_plan 表相关
set sql_safe_updates=false;
update ReportWeavingProductionDay A left join tmp_work_order_production_plan B on A.plan_id=B.only_no
set A.order_type=B.order_type
   ,A.material_name=B.material_name    
   ,A.sale_order_no=B.sale_order_no                    
   ,A.color_name=B.color_name
   ,A.muslin_weft_density=B.muslin_weft_density
   ,A.machine_density=B.machine_density
   ,A.row_card=B.row_card
   ,A.size_name=B.size_name;
set sql_safe_updates=true;

-- speed_value 表相关
alter table ReportWeavingProductionDay add speed_value decimal(10,2);
set sql_safe_updates=false;
 update ReportWeavingProductionDay A left join tmp_tmp_speed B on A.work_record_id=B.work_record_id
 set A.speed_value=B.speed_value;
 set sql_safe_updates=true;


-- 上轴时长、穿纱时长、机修时长
alter table ReportWeavingProductionDay 
      add times_shagnzhou decimal(10,2)
     ,add times_chuansha decimal(10,2)
     ,add times_jixiu decimal(10,2);

-- terminal_action_class_itemb表相关     
drop temporary table if exists tmp_terminal_action_class_item;
create temporary table tmp_terminal_action_class_item as
select work_record_id,type_id,begin_time,end_time 
      ,round(sum(timestampdiff(second, begin_time, end_time) / 60), 1) as Sum_TimeLong
  from terminal_action_class_item where work_record_id in
(
    select distinct work_record_id from ReportWeavingProductionDay
)
group by work_record_id,type_id,begin_time,end_time;
alter table tmp_terminal_action_class_item add index idex_actionclassitem(work_record_id);

set sql_safe_updates=false;
-- 上轴时长
update ReportWeavingProductionDay A left join tmp_terminal_action_class_item B 
    on A.work_record_id=B.work_record_id and B.type_id=1
 set A.times_shagnzhou=Sum_TimeLong;
 
 -- 穿纱时长
 update ReportWeavingProductionDay A left join tmp_terminal_action_class_item B 
    on A.work_record_id=B.work_record_id and B.type_id=2
 set A.times_chuansha=Sum_TimeLong;
 
 -- 机修时长
 update ReportWeavingProductionDay A left join tmp_terminal_action_class_item B 
    on A.work_record_id=B.work_record_id and B.type_id=3
 set A.times_jixiu=Sum_TimeLong;
 set sql_safe_updates=true;


-- 经停时长(statusid=3)、纬停时长(statusid=1)、断筒纱时长(statusid=4)
alter table ReportWeavingProductionDay 
      add times_jtsc decimal(10,2)
     ,add times_wtsc decimal(10,2)
     ,add times_dtssc decimal(10,2);

-- 经停时长(times_jtsc)、纬停时长(times_wtsc)、断筒纱时长(times_dtssc)
drop temporary table if exists tmp_terminal_machine_status_detail;
create temporary table tmp_terminal_machine_status_detail as
select MachineID,SpecRecordID,statusid,statusname
      ,round(sum(StopLong)/60,1) as Sum_StopLong 
    from terminal_machine_status_detail
    where SpecRecordID in
    (
        select distinct spec_record_id from ReportWeavingProductionDay
    )
    group by MachineID,SpecRecordID,statusid,statusname;
alter table tmp_terminal_machine_status_detail add index idx_machinestatus_specrecordid(SpecRecordID);

set sql_safe_updates=false;
-- 经停时长(times_jtsc)字段更新
update ReportWeavingProductionDay A left join tmp_terminal_machine_status_detail B 
    on A.machine_id=B.MachineID and A.spec_record_id=B.SpecRecordID and B.statusid=3
 set A.times_jtsc=Sum_StopLong;
 
 -- 纬停时长(times_wtsc)字段更新
update ReportWeavingProductionDay A left join tmp_terminal_machine_status_detail B 
    on A.machine_id=B.MachineID and A.spec_record_id=B.SpecRecordID and B.statusid=1
 set A.times_wtsc=Sum_StopLong;
 
 -- 断筒纱时长(times_dtssc)字段更新
 update ReportWeavingProductionDay A left join tmp_terminal_machine_status_detail B 
    on A.machine_id=B.MachineID and A.spec_record_id=B.SpecRecordID and B.statusid=4
 set A.times_dtssc=Sum_StopLong;
 set sql_safe_updates=true;
 
 -- 异常双数、异常码数
 alter table ReportWeavingProductionDay 
      add qty_ycss decimal(10,2)
     ,add qty_ycms decimal(10,2);

 drop temporary table if exists tmp_bad_class_detail;
 create temporary table tmp_bad_class_detail as 
 select
     WorkRecordID
    ,case when BadLeft>BadRight then BadLeft else BadRight end as qty_ycss
    ,sum(CodeNum) as qty_ycms
 from bad_class_detail
 where WorkRecordID in
 (
    select distinct work_record_id from ReportWeavingProductionDay
 )group by WorkRecordID,BadLeft,BadRight;
 
 set sql_safe_updates=false;
 update ReportWeavingProductionDay A left join tmp_bad_class_detail B 
    on A.work_record_id=B.WorkRecordID
 set A.qty_ycss=B.qty_ycss
    ,A.qty_ycms=B.qty_ycms;
 set sql_safe_updates=true;

-- 其他停机
-- 保养中
set sql_safe_updates=false;
alter table ReportWeavingProductionDay add other_stop_baoyangzhong int;
update ReportWeavingProductionDay A left join
(
    select timestampdiff(second,stop_begin_time,stop_end_time) as total_time 
          ,A.work_record_id
      from terminal_other_stop_detail A
      left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id
      where B.halt_type_name='保养中'
) B on A.work_record_id=B.work_record_id
 set A.other_stop_baoyangzhong=B.total_time; -- 赋值字段
 set sql_safe_updates=true;
 
-- 待单中
set sql_safe_updates=false;
alter table ReportWeavingProductionDay add other_stop_daidanzhong int;
update ReportWeavingProductionDay A left join
(
    select timestampdiff(second,stop_begin_time,stop_end_time) as total_time 
          ,A.work_record_id
      from terminal_other_stop_detail A
      left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id
      where B.halt_type_name='待单中'        -- 其他停机类型
) B on A.work_record_id=B.work_record_id
 set A.other_stop_daidanzhong=B.total_time; -- 赋值字段
 set sql_safe_updates=true;
 
-- 待花型
set sql_safe_updates=false;
alter table ReportWeavingProductionDay add other_stop_daihuaxing int;
update ReportWeavingProductionDay A left join
(
    select timestampdiff(second,stop_begin_time,stop_end_time) as total_time 
          ,A.work_record_id
      from terminal_other_stop_detail A
      left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id
      where B.halt_type_name='待花型'        -- 其他停机类型
) B on A.work_record_id=B.work_record_id
 set A.other_stop_daihuaxing=B.total_time;  -- 赋值字段
 set sql_safe_updates=true;
 
-- 待修中
set sql_safe_updates=false;
alter table ReportWeavingProductionDay add other_stop_daixiuzhong int;
update ReportWeavingProductionDay A left join
(
    select timestampdiff(second,stop_begin_time,stop_end_time) as total_time 
          ,A.work_record_id
      from terminal_other_stop_detail A
      left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id
      where B.halt_type_name='待修中'        -- 其他停机类型
) B on A.work_record_id=B.work_record_id
 set A.other_stop_daixiuzhong=B.total_time; -- 赋值字段
 set sql_safe_updates=true;
 
-- 待上轴
set sql_safe_updates=false;
alter table ReportWeavingProductionDay add other_stop_daishangzhou int;
update ReportWeavingProductionDay A left join
(
    select timestampdiff(second,stop_begin_time,stop_end_time) as total_time 
          ,A.work_record_id
      from terminal_other_stop_detail A
      left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id
      where B.halt_type_name='待上轴'         -- 其他停机类型
) B on A.work_record_id=B.work_record_id
 set A.other_stop_daishangzhou=B.total_time; -- 赋值字段
 set sql_safe_updates=true;
 
 -- 待穿纱
set sql_safe_updates=false;
alter table ReportWeavingProductionDay add other_stop_daichuansha int;
update ReportWeavingProductionDay A left join
(
    select timestampdiff(second,stop_begin_time,stop_end_time) as total_time 
          ,A.work_record_id
      from terminal_other_stop_detail A
      left join base_halt_reason_type B on A.terminal_other_stop_detail_id=B.halt_reason_type_id
      where B.halt_type_name='待穿纱'         -- 其他停机类型
) B on A.work_record_id=B.work_record_id
 set A.other_stop_daichuansha=B.total_time; -- 赋值字段
 set sql_safe_updates=true;

-- 4、查询结果
-- 总条数
-- select count(0) into TatalCount from ReportWeavingProductionDay;

-- select * from ReportWeavingProductionDay where order_type is not null;
select 
    ifnull(machine_id,'') as '机器ID'
    ,ifnull(GROUP_CONCAT(work_record_id SEPARATOR ','),'') as '工作记录ID'
    ,DATE_FORMAT(work_date,'%Y-%m-%d') as  '日期'
    ,ifnull(order_type,'') as '订单类型'
    ,ifnull(machine_type_name,'') as '设备类型'
    ,ifnull(machine_name,'') as '机台号'
    ,ifnull(unit_group,'') as '组别'
    ,ifnull(work_class_name,'') as '班别'
    ,ifnull(staff_username,'') as '员工姓名'
    ,ifnull(material_name,'') as '品名'
    ,case IsShoe_Name when '' then '' when null then '' else '鞋型' end as '是否鞋型'
    ,ifnull(sale_order_no,'') as '订单编号'
    ,ifnull(color_name,'') as '颜色'
    ,ifnull(muslin_weft_density,'') as '胚布纬密'
    ,ifnull(machine_density,'') as '上机纬密'
    ,ifnull(max(speed_value),'') as '机速'
    ,ifnull(row_card,'') as '预设排数'
    ,ifnull(size_name,'') as '尺码'
    ,ifnull(case when size_name is not null and size_name != '' then sum(order_nums) else null end,'') as '双数'
    ,ifnull(case when size_name is null or size_name = '' then sum(order_nums) else null  end,'') as '码数'
    ,work_nums as '生产米数'
    ,count as '生产梭数'
    ,ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0) as '工作时间'
    ,ifnull(round(sum(stop_times)/60,1),0) as '总停机时间'
    ,ifnull(times_shagnzhou,'') as '上轴时长'
    ,ifnull(times_chuansha,'') as '穿纱时长'
    ,ifnull(times_jixiu,'') as '机修时长'
    ,ifnull(times_jtsc,'') as '经停时长'
    ,ifnull(times_wtsc,'') as '纬停时长'
    ,ifnull(times_dtssc,'') as '断筒纱时长'
    ,ifnull(pair_count,'') as '异常双数'
    ,ifnull(yards_count,'') as '异常码数'
    ,(ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)-(ifnull(times_shagnzhou,0)+ifnull(times_chuansha,0)+ifnull(times_jixiu,0))) as '实际作业时间'
    ,CONCAT(ifnull(round(((ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)-ifnull(round(sum(stop_times)/60,1),0))/ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)),2)*100,0),'%') as '机台效率'
    ,round((ifnull(round(sum(timestampdiff(second, work_begin_time, work_end_time) / 60), 1),0)-ifnull(round(sum(stop_times)/60,1),0)),2) as '设备运转时间'
    ,(ifnull(round(sum(stop_times)/60,1),0)-ifnull(times_jtsc,0)-ifnull(times_wtsc,0)-ifnull(times_dtssc,0)) as '其他停机'
    ,ifnull(sum(other_stop_baoyangzhong),0) as '保养中'
    ,ifnull(sum(other_stop_daidanzhong),0) as '待单中'
    ,ifnull(sum(other_stop_daihuaxing),0) as '待花型'
    ,ifnull(sum(other_stop_daixiuzhong),0) as '待修中'
    ,ifnull(sum(other_stop_daishangzhou),0) as '待上轴'
    ,ifnull(sum(other_stop_daichuansha),0) as '待穿纱'
from ReportWeavingProductionDay
group by machine_id,work_date,order_type,machine_type_name,machine_type_name,machine_name,unit_group,work_class_name,staff_username,material_name,sale_order_no,color_name
        ,muslin_weft_density,machine_density,row_card,size_name,work_nums,count
        ,times_shagnzhou,times_chuansha,times_jixiu
        ,pair_count,yards_count
        ,times_jtsc,times_wtsc,times_dtssc
        ,IsShoe_Name
;
END

 

标签：存储,set,示例,--,work,machine,ifnull,MySQL,id
From： https://www.cnblogs.com/chengeng/p/18370918

相关文章

  • MySQL基本操作
    MySQL基本操作学习目标：学习基本的SQL操作，实现数据库的基本管理SQL基本语法SQL库操作SQL表操作SQL数据操作一、SQL语法规则目标：了解SQL的基本语法规则SQL语法规则：SQL是一种结构化编程语言基础SQL指令通常是以行为单位SQL指令需要语句结束符，默认是英文分号：;、\g、\G\G：主......
  • Mysql从指定位置截取字符串
    在实际的项目开发中有时会有对数据库某字段截取部分的需求，这种场景有时直接通过数据库操作来实现比通过代码实现要更方便快捷些，mysql有很多字符串函数可以用来处理这些需求，如Mysql字符串截取总结：left()、right()、substring()、substring_index()。一.从左开始截取字符串用法：lef......
  • vSphere5.9.5.存储IO控制
    第五部分存储i/o控制存储i/o介绍(1)thereisametric,however,thatvspherecanusetohelpdeterminetheutilizationofstorage.thatmetricislatency.usinglatencyasthemetrictodetectcontention,vspherecanoffershares(toestablishprioritywhencontent......
  • Linux中MySQL安装与升级中的相关知识
    Linux中MySQL安装与升级中的相关知识1.MySQL的RPM安装通常分为不同的包，包括Server、Common、Client、Devel、Libs、Libs-compat、Test、Source，上述每个包的功能。Server：包含MySQL服务器的主要组件。Common：提供通用的功能和文件。Client：提供客户端工具用于连接服务......
  • Python连接MySQL数据库
    连接Mysql数据库#!/usr/bin/envpython#-*-coding:utf-8-*-importMySQLdb#连接数据库db=MySQLdb.connect(host="localhost",user="zabbix",passwd="123123",db="zabbix")#创建cursor对象cursor=db.cursor()#执行SQL查询cu......
  • MySQL操作
    数据库类型常用数据类型详细数据类型需要注意的是：BOOLEAN在数据库保存的是tinyInt类型，false为0，true就是1char是定长，varchar是变长，char存储时，如果字符数没有达到定义的位数，后面会用空格填充到指定长度，而varchar没达到定义位数则不会填充，按实际长度存储。比如一个char(1......
  • oracle & mysql 驱动程序安装配置
    Install-PackageOracle.ManagedDataAccess-Version12.2.20230118  版本可以安装到19.18Install-PackageMySql.Data-Version8.0.32.1config文件新增内容<system.data>  <DbProviderFactories>    <removeinvariant="MySql.Data.MySqlClient"/>    &......
  • FLink1.17-Kafka实时同步到MySQL实践
    1．组件版本组件版本Kafka3.7.0Flink1.17.0MySQL8.0.32 2．Kafka生产数据./kafka-console-producer.sh--broker-listhadoop01:9092,hadoop02:9092,hadoop03:9092--topic  kafka_test_table2>{"id":123,"test_age":33}&......
  • MySQL-MGR实战指南：打造企业级高可用数据库集群
    文章目录前言MGR的介绍事务处理流程：实验测试环境：结束语前言在数字化时代，企业的数据安全和业务连续性至关重要。想象一下，当关键业务数据存储在数据库中，而数据库突然出现故障，或者面临硬件故障、网络中断、自然灾害等不可预知的灾难性事件时，企业如何确保数据的完整性和......
  • Linux(CentOS7)安装MySQL8全过程
    下载官方地址：https://dev.mysql.com/downloads/mysql/选择版本前需先看一下服务器的glibc版本ldd--version  上传将下载好的tar包上传到服务器上，这里演示上传到了/usr/local/文件夹下 解压tar -Jxvfmysql-8.0.36-linux-glibc2.17-x86_64.tar.xz ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99