1、........................................例子【1】...........................................
ALTER PROCEDURE [dbo].[zy_update_process_route]
AS
BEGIN
-- 工序信息
-- INSERT INTO [dbo].[rms_process]([id], [tenant_id], [process_code], [process_name], [workstations], [code_scan_num], [always_one_code], [defects], [is_fifo], [unqualified_products], [over_output_rate], [report_tplt_ids], [product_desc], [is_deliverable], [is_output_frozen_category], [process_status], [remark], [create_user], [create_dept], [create_time], [update_user], [update_time], [status], [is_deleted], [whether_at_work], [whether_slitting_process]) VALUES (1668151297538174978, '303168', 'ZY', '造液', '1423197073588469761,1425062649977954306,1425281873941970946,1472854759887228929', NULL, NULL, '1432655830445170690,1432655830453559297,1432655830457753602,1432655830461947906,1432655830470336514,1432655830474530817,1432655830482919426,1432655830482919427,1432655830491308033,1432655830495502337,1432655830566805506', NULL, NULL, NULL, NULL, '', NULL, NULL, 1, NULL, 1427488715178217474, 1369156954975576065, '2023-06-12 15:00:36.740', 1427488715178217474, '2023-06-12 15:00:36.740', 1, 0, 0, 0);
-- rms_process
-- rms_process_route
-- rms_process_route_detail
CREATE TABLE #rms_process_route_detail_add(id BIGINT PRIMARY KEY,process_route_id BIGINT,process_id BIGINT,process_seq INT,workstations varchar(512))
CREATE TABLE #rms_process_route_detail_update(id BIGINT PRIMARY KEY,process_seq INT)
DECLARE @process_route_id BIGINT;--用于临时存储当前会员的id
DECLARE @index INT;--用于临时存储当前会员的id
SET @index = 1;
--1.创建游标
DECLARE process_route_cursor CURSOR --定义游标
FOR (SELECT id FROM rms_process_route WHERE is_deleted = 0 AND code != 'FQ01' ) --查出需要的集合放到游标中
--2.打开游标
OPEN process_route_cursor
FETCH NEXT FROM process_route_cursor
INTO @process_route_id --读取第一行数据 fetch到数据时进行的操作
--开始循环
WHILE @@FETCH_STATUS = 0
BEGIN
--打印方便观察脚本执行情况
BEGIN
--操作内容
print '第几次:'+convert(varchar,@index)
print '工艺路线标识:'+convert(varchar,@process_route_id)
DECLARE @id BIGINT;
DECLARE @tenant_id INT;
DECLARE @process_seq INT;
DECLARE @workstations varchar(512);
--1.1创建游标
DECLARE process_route_detail_cursor CURSOR --定义游标
FOR SELECT TOP 99999999 id,tenant_id,process_seq,workstations FROM rms_process_route_detail WHERE is_deleted = 0 AND process_route_id = @process_route_id ORDER BY process_seq --查出需要的集合放到游标中
--2.1打开游标
OPEN process_route_detail_cursor
FETCH NEXT FROM process_route_detail_cursor
INTO @id,@tenant_id,@process_seq,@workstations --读取第一行数据 fetch到数据时进行的操作
DECLARE @indexMin INT;--用于临时存储当前会员的id
SET @indexMin = 0;
--开始循环
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
--处理工艺路线详情
--获取标识
DECLARE @maxId BIGINT;
SET @maxId = ISNULL((SELECT MAX(id) FROM #rms_process_route_detail_add ), ISNULL((SELECT MAX(id) FROM rms_process_route_detail), 100000000001)) + 1;
print '第几次(内部):'+convert(varchar,@indexMin)
print '工艺路线标识(内部):'+convert(varchar,@maxId)
print '@maxId:'+convert(varchar,@maxId)
print '@tenant_id:'+convert(varchar,@tenant_id)
print '@process_route_id:'+convert(varchar,@process_route_id)
print '1668151297538174978:'+convert(varchar,1668151297538174978)
print '@process_seq:'+convert(varchar,@process_seq)
print 'status:'+convert(varchar,1)
print 'is_deleted:'+convert(varchar,0)
--新增工序
-- INSERT INTO rms_process_route_detail (id,tenant_id,process_route_id,process_id,process_seq,status,is_deleted)
-- VALUES(@maxId,@tenant_id,@process_route_id,1668151297538174978,@process_seq,1,0);
--
INSERT INTO #rms_process_route_detail_add (id,process_route_id,process_id,process_seq,workstations)
VALUES(@maxId,@process_route_id,1668151297538174978,@indexMin,@workstations);
set @indexMin+=1
INSERT INTO #rms_process_route_detail_update (id,process_seq)
VALUES(@id,@indexMin);
-- --修改工序顺序
-- UPDATE rms_process_route_detail SET process_seq = (process_seq+1)
-- WHERE id = @id
END
FETCH NEXT FROM process_route_detail_cursor
INTO @id,@tenant_id,@process_seq,@workstations --读取下一行数据,并把值重新赋值给变量
set @indexMin+=1
print '工艺路线标识(内部)111:'+convert(varchar,@indexMin)
END
--3.1循环结束|关闭游标
CLOSE process_route_detail_cursor;
--4.1删除游标|释放游标
DEALLOCATE process_route_detail_cursor;
END
FETCH NEXT FROM process_route_cursor INTO @process_route_id --读取下一行数据,并把值重新赋值给变量
set @index+=1
END
--3.循环结束|关闭游标
CLOSE process_route_cursor;
--4.删除游标|释放游标
DEALLOCATE process_route_cursor;
SELECT * FROM #rms_process_route_detail_add
SELECT * FROM #rms_process_route_detail_update
--新增工序
INSERT INTO rms_process_route_detail (id,tenant_id,process_route_id,process_id,process_seq,status,is_deleted,workstations)
SELECT id,'303168' tenant_id,process_route_id,process_id,process_seq,1 status,0 is_deleted,workstations
FROM #rms_process_route_detail_add
--修改工序顺序
UPDATE rms_process_route_detail SET process_seq = a.process_seq
FROM #rms_process_route_detail_update a
WHERE a.id = rms_process_route_detail.id
END
2、........................................例子【2】...........................................
ALTER PROCEDURE [dbo].[zy_update_bom]
AS
BEGIN
-- 工序信息
-- INSERT INTO [dbo].[rms_process]([id], [tenant_id], [process_code], [process_name], [workstations], [code_scan_num], [always_one_code], [defects], [is_fifo], [unqualified_products], [over_output_rate], [report_tplt_ids], [product_desc], [is_deliverable], [is_output_frozen_category], [process_status], [remark], [create_user], [create_dept], [create_time], [update_user], [update_time], [status], [is_deleted], [whether_at_work], [whether_slitting_process]) VALUES (1668151297538174978, '303168', 'ZY', '造液', '1423197073588469761,1425062649977954306,1425281873941970946,1472854759887228929', NULL, NULL, '1432655830445170690,1432655830453559297,1432655830457753602,1432655830461947906,1432655830470336514,1432655830474530817,1432655830482919426,1432655830482919427,1432655830491308033,1432655830495502337,1432655830566805506', NULL, NULL, NULL, NULL, '', NULL, NULL, 1, NULL, 1427488715178217474, 1369156954975576065, '2023-06-12 15:00:36.740', 1427488715178217474, '2023-06-12 15:00:36.740', 1, 0, 0, 0);
-- rms_process
-- rms_process_route
-- rms_process_route_detail
-- rms_mbom
-- rms_mbom_route_detail 需要处理 新增对象 修改顺序
-- rms_mbom_process_input 需要处理 新增对象新增投入 rms_mbom_process_make_liquid
-- rms_mbom_process_output 不需要处理
-- rms_mbom_process_make_liquid 造液 删除
CREATE TABLE #rms_mbom_route_detail_add(id BIGINT PRIMARY KEY,mbom_id BIGINT,process_id BIGINT,process_seq INT,workstations varchar(512));
CREATE TABLE #rms_mbom_process_input_add(id BIGINT PRIMARY KEY,mbom_id BIGINT,process_id BIGINT,material_id BIGINT,material_code varchar(512), unit_id varchar(512),amount DECIMAL(19,11),loss_rate DECIMAL(19,11),replace_materials varchar(512), workstations varchar(512),detail_id BIGINT,input_material_id BIGINT,input_material_code varchar(512));
CREATE TABLE #rms_mbom_route_detail_update(id BIGINT PRIMARY KEY,process_seq INT)
DECLARE @mbom_id BIGINT;--用于临时存储当前会员的id
DECLARE @index INT;--用于临时存储当前会员的id
SET @index = 1;
--1.创建游标
DECLARE mbom_cursor CURSOR --定义游标
FOR (SELECT id FROM rms_mbom WHERE is_deleted = 0 AND process_routing_id IN (SELECT id FROM rms_process_route WHERE is_deleted = 0 AND code != 'FQ01' )) --查出需要的集合放到游标中
--2.打开游标
OPEN mbom_cursor
FETCH NEXT FROM mbom_cursor
INTO @mbom_id --读取第一行数据 fetch到数据时进行的操作
--开始循环
WHILE @@FETCH_STATUS = 0
BEGIN
--打印方便观察脚本执行情况
BEGIN
--操作内容
print '第几次:'+convert(varchar,@index)
print 'Bom标识:'+convert(varchar,@mbom_id)
DECLARE @id BIGINT;
DECLARE @tenant_id INT;
DECLARE @process_seq INT;
DECLARE @workstations varchar(525);
--1.1创建游标
DECLARE rms_mbom_route_detail_cursor CURSOR --定义游标
FOR SELECT TOP 99999999 id,tenant_id,process_seq,workstations FROM rms_mbom_route_detail WHERE is_deleted = 0 AND mbom_id = @mbom_id ORDER BY process_seq --查出需要的集合放到游标中
--2.1打开游标
OPEN rms_mbom_route_detail_cursor
FETCH NEXT FROM rms_mbom_route_detail_cursor
INTO @id,@tenant_id,@process_seq,@workstations --读取第一行数据 fetch到数据时进行的操作
DECLARE @indexMin INT;--用于临时存储当前会员的id
SET @indexMin = 1;
--开始循环
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
--处理工艺路线详情
--获取标识
DECLARE @maxId BIGINT;
SET @maxId = ISNULL((SELECT MAX(id) FROM #rms_mbom_route_detail_add ), ISNULL((SELECT MAX(id) FROM rms_mbom_route_detail), 100000000001)) + 1;
print '第几次(内部):'+convert(varchar,@indexMin)
print '工艺路线标识(内部):'+convert(varchar,@maxId)
print '@maxId:'+convert(varchar,@maxId)
print '@tenant_id:'+convert(varchar,@tenant_id)
print '@mbom_id:'+convert(varchar,@mbom_id)
print '1668151297538174978:'+convert(varchar,1668151297538174978)
print '@process_seq:'+convert(varchar,@process_seq)
print '@workstations:'+convert(varchar,@workstations)
INSERT INTO #rms_mbom_route_detail_add (id,mbom_id,process_id,process_seq,workstations)
VALUES(@maxId,@mbom_id,1668151297538174978,@indexMin,@workstations);
set @indexMin+=1
INSERT INTO #rms_mbom_process_input_add
( id ,mbom_id,process_id,material_id,material_code, unit_id,amount,loss_rate,replace_materials,
workstations,detail_id,input_material_id,input_material_code)
SELECT id,mbom_id,process_id,material_id,material_code,unit_id,amount,loss_rate,'' replace_materials,
'' workstations,@maxId detail_id,make_liquid_material_id as input_material_id,make_liquid_material_code as input_material_code
FROM rms_mbom_process_make_liquid WHERE is_deleted = 0 and detail_id = @id;
INSERT INTO #rms_mbom_route_detail_update (id,process_seq)
VALUES(@id,@indexMin);
END
FETCH NEXT FROM rms_mbom_route_detail_cursor
INTO @id,@tenant_id,@process_seq,@workstations --读取下一行数据,并把值重新赋值给变量
set @indexMin+=1
print '工艺路线标识(内部)111:'+convert(varchar,@indexMin)
END
--3.1循环结束|关闭游标
CLOSE rms_mbom_route_detail_cursor;
--4.1删除游标|释放游标
DEALLOCATE rms_mbom_route_detail_cursor;
END
FETCH NEXT FROM mbom_cursor INTO @mbom_id --读取下一行数据,并把值重新赋值给变量
set @index+=1
END
--3.循环结束|关闭游标
CLOSE mbom_cursor;
--4.删除游标|释放游标
DEALLOCATE mbom_cursor;
SELECT * FROM #rms_mbom_route_detail_add
SELECT * FROM #rms_mbom_process_input_add
SELECT * FROM #rms_mbom_route_detail_update
--新增工序
INSERT INTO rms_mbom_route_detail (id,tenant_id,mbom_id,process_id,process_seq,status,is_deleted,workstations)
SELECT id,'303168' tenant_id,mbom_id,process_id,process_seq,1 status,0 is_deleted,workstations
FROM #rms_mbom_route_detail_add
--新增投入
INSERT INTO rms_mbom_process_input ( id ,tenant_id,mbom_id,process_id,material_id,material_code, unit_id,amount,loss_rate,replace_materials,detail_id,input_material_id,input_material_code,status,is_deleted)
SELECT id ,'303168',mbom_id,process_id,material_id,material_code, unit_id,amount,loss_rate,replace_materials,detail_id,input_material_id,input_material_code,1 status,0 is_deleted
FROM #rms_mbom_process_input_add
--修改工序顺序
UPDATE rms_mbom_route_detail SET process_seq = a.process_seq
FROM #rms_mbom_route_detail_update a
WHERE a.id = rms_mbom_route_detail.id
END