欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL函数过程示例-根据视图和游标删除数据

程序员文章站 2022-05-10 23:32:13
...
注:游标和视图结合起来使用,因为游标不支持动态表传入。
参数
IN esCorCode varchar(20),IN startTime varchar(50),IN endTime varchar(50)

存储过程
BEGIN
-- CALL P_DELETE_SERVICE_DATA('10000','2015-12-23 15:00:00','2015-12-23 15:58:00');

DECLARE count_val INT;

DECLARE cur_in CURSOR FOR SELECT count FROM view_into_table;

DECLARE cur_out CURSOR FOR SELECT count FROM view_out_table;
-- ---------------------------------------------创建视图-------------------------------------开始---------------------------
drop view if exists view_into_table;
set @view_into_sql=concat("create view view_into_table as select COUNT(WMIO_ID) count from  WM_INTO_ORDER_",esCorCode,' WHERE WMIO_COMPLETION_TIME ',' between ','"',startTime,'"',' and ','"',endTime,'"');
PREPARE stmt FROM  @view_into_sql;
EXECUTE stmt;

drop view if exists view_out_table;
set @view_out_sql=concat("create view view_out_table as select COUNT(WMOO_ID) count from WM_OUT_ORDER_",esCorCode,' WHERE SURE_DATE ',' between ','"',startTime,'"',' and ','"',endTime,'"');
PREPARE stmt FROM  @view_out_sql;
EXECUTE stmt;
-- ---------------------------------------------创建视图-------------------------------------结束---------------------------
SET @index_=1;
-- ---------------------------------------------业务数据表-------------------------------------------------------
SET @array_tables=CONCAT('WM_INTO_ORDER');                       -- 入库表

SET @array_tables=CONCAT(@array_tables,',','WM_OUT_ORDER');      -- 出库表

SET @array_tables=CONCAT(@array_tables,',','WM_WAREHOUSE_MOVE'); -- 移库表

SET @array_tables=CONCAT(@array_tables,',','WM_BATCH_PICKING');  -- 波次表

SET @array_tables=CONCAT(@array_tables,',','EB_SYS_LOG');        -- 系统日志表

SET @array_tables=CONCAT(@array_tables,',','EB_OPERATE_LOG');    -- 操作日志表

SET @array_tables=CONCAT(@array_tables,',','EB_INTO_STOCK_LOG'); -- 入库日志表

SET @array_tables=CONCAT(@array_tables,',','EB_OUT_STOCK_LOG');  -- 出库日志表

-- ---------------------------------------------业务数据表对应字段------------------------------------------------

SET @array_fields=CONCAT('WMIO_COMPLETION_TIME');              --  入库表    完成时间标识

SET @array_fields=CONCAT(@array_fields,',','SURE_DATE');       --  出库表    出库确认时间标识

SET @array_fields=CONCAT(@array_fields,',','WWM_COMPLETION_TIME');     --  移库表    完成时间标识

SET @array_fields=CONCAT(@array_fields,',','RE_TIME');         --  波次表            波次释放时间标识

SET @array_fields=CONCAT(@array_fields,',','CREATE_TIME');     --  系统日志表        创建时间标识

SET @array_fields=CONCAT(@array_fields,',','CREATE_TIME');     --  操作日志表        创建时间标识

SET @array_fields=CONCAT(@array_fields,',','CREATE_TIME');     --  入库日志表        创建时间标识

SET @array_fields=CONCAT(@array_fields,',','CREATE_TIME');     --  出库日志表        创建时间标识
-- ---------------------------------------------计算循环次数------------------------------------------------------         
SET @table_count=CHAR_LENGTH(@array_tables)-CHAR_LENGTH(REPLACE(@array_tables,',',''))+1; 

-- ---------------------------------------------执行业务数据操作--------------------------------------------------

WHILE @index_ <= @table_count DO

  SET @table_prex=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_tables,',',@index_),',',-1);

  SET @table_name=CONCAT(@table_prex,'_',esCorCode);

  SET @fieldName=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_fields,',',@index_),',',-1);

  SET @delete_data=CONCAT('delete from ',@table_name ,' where ',@fieldName,' between ','"',startTime,'"',' and ','"',endTime,'"');

  -- 入库单删除时删除对应的:入库明细、收货信息、退回信息
  IF @table_prex = 'WM_INTO_ORDER' THEN

       OPEN cur_in;

       FETCH cur_in INTO count_val;

       IF count_val>0 THEN
           -- 查询符合条件的入库单ID
           SET @into_order_ids=CONCAT('SELECT WMIO_ID FROM ',@table_name,' where ',@fieldName,' between ','"',startTime,'"',' and ','"',endTime,'"');

           SET @into_orderinfo_ids=CONCAT('SELECT WMOF_ID FROM WM_INTO_ORDERINFO_',esCorCode,' where  WMOF_IO_ID in ','(',@into_order_ids,')');

           -- 删除 入库ID对应的入库明细
           SET @WM_INTO_ORDERINFO=CONCAT('WM_INTO_ORDERINFO_',esCorCode);

           SET @delete_into_orderinfo=CONCAT('delete from ',@WM_INTO_ORDERINFO,' where  WMOF_IO_ID in ','(',@into_order_ids,')');

           -- 删除 入库ID对应的收货信息
           SET @WM_RECEIPT_INFO=CONCAT('WM_RECEIPT_INFO_',esCorCode);

           SET @delete_receipt_info=CONCAT('delete from ',@WM_RECEIPT_INFO,' where  WMRPS_OF_ID in ','(',@into_orderinfo_ids,')');

           -- 删除 入库ID对应的退货信息
           SET @WM_RETURN_INFO=CONCAT('WM_RETURN_INFO_',esCorCode);

           SET @delete_return_info=CONCAT('delete from ',@WM_RETURN_INFO,' where WMSP_OF_ID in ','(',@into_orderinfo_ids,')');

           -- 删除过程中要注意删除顺序
           prepare delete_talbe_data from @delete_receipt_info; 
           execute delete_talbe_data; 

           prepare delete_talbe_data from @delete_return_info; 
           execute delete_talbe_data; 

           prepare delete_talbe_data from @delete_into_orderinfo; 
           execute delete_talbe_data; 

           prepare delete_talbe_data from @delete_data; 
           execute delete_talbe_data; 

       END IF;

       CLOSE cur_in;

 --  删除出库对应的出库详情及出库计划
 ELSEIF @table_prex = 'WM_OUT_ORDER' THEN

       OPEN cur_out;

       FETCH cur_out INTO count_val;

       IF count_val>0 THEN
           -- 查询符合条件的出库单ID
           SET @out_order_ids=CONCAT('SELECT WMOO_ID FROM ',@table_name,' where ',@fieldName,' between ','"',startTime,'"',' and ','"',endTime,'"');

           -- 删除 出库ID对应的出库明细
           SET @WM_OUT_ORDER_DETAIL=CONCAT('WM_OUT_ORDER_DETAIL_',esCorCode);

           SET @delete_out_order_detail=CONCAT('delete from ',@WM_OUT_ORDER_DETAIL,' where  WMOD_OUT_ORDER_ID in ','(',@out_order_ids,')');

           -- 删除过程中要注意删除顺序

           prepare delete_talbe_data from @delete_out_order_detail; 
           execute delete_talbe_data; 

           prepare delete_talbe_data from @delete_data; 
           execute delete_talbe_data; 

       END IF;

       CLOSE cur_out;
 ELSE

   prepare delete_talbe_data from @delete_data; 

   execute delete_talbe_data; 

 END IF;
 
  SET @index_=@index_+1; 

END WHILE; 

END
相关标签: mysql cursor view