ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 低阶码的存储过程

低阶码的存储过程

原创 Linux操作系统 作者:zhaoyu728 时间:2019-06-02 07:42:04 0 删除 编辑


ALTER procedure pr_inv10210_cst

as
declare @l_messcode char(4),
@l_trancnt int,
@l_count int,
@l_llc int ,
@l_xuhk char(10),
@l_i int ,
@l_xuhktemp char(10)

/*create table #temp_part_jj (
part varchar(30) not null)*/

/*select @v_part=RTRIM(LTRIM(@v_part))+'%'
insert into #temp_part_jj
select parent from bom10101 where parent LIKE @v_part
while (@@rowcount > 0)
begin
insert into #temp_part_jj
select distinct b.component from bom10101 b where
b.parent in (select t.part from #temp_part_jj t) and
b.component not in (select t1.part from #temp_part_jj t1)
end*/

select @l_trancnt = @@trancount
if @l_trancnt = 0
begin transaction tran_pr_inv10210_cst
else
save transaction tran_pr_inv10210_cst
/*复制所有未导入的料品数据到临时表中*/
select *,xuhk as xuhk1,xuhk as xuhk2 into #temp_part_jj from bom where (bom.chk <> 'Y' or bom.chk is null) and (bom.codea <> 'Y' or bom.codea is null) and (not isnull(CHARINDEX ('MX',dlhk ),0) > 0)

/*初始化低阶码为0,重复标志为N*/
update #temp_part_jj set llc= 0 , rep = 'N'
if (@@error<>0)
begin
select @l_messcode = '0002'
rollback transaction tran_pr_inv10210_cst
select @l_messcode
return 0
end

/*存在-的记录,低阶码增加1*/
update #temp_part_jj set llc = llc + 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if (@@error<>0)
begin
select @l_messcode = '0002'
rollback transaction tran_pr_inv10210_cst
select @l_messcode
return 0
end
/*保存上一节阶码*/
/*update #temp_part_jj set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */
/*删除第一个-*/
update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))

if (@@error<>0)
begin
select @l_messcode = '0002'
rollback transaction tran_pr_inv10210_cst
select @l_messcode
return 0
end

/*查询是否还有-资料*/

select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */
update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))

select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */
update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */

update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2),0))
select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */

update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2),0))
select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */

update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2),0))
select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */

update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2),0))
select @l_count = count(*) from #temp_part_jj where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
if @l_count > 0
begin
update #temp_part_jj set llc = llc+ 1 where isnull(CHARINDEX ('-',xuhk2 ),0) > 0
/*保存上一节阶码*/
/*update #temp_part_jj
set xuhk1 = SUBSTRING(xuhk2,1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0))
where isnull(charindex ('-',SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2 ),0) +1,
len(xuhk2) - isnull(CHARINDEX ('-',xuhk2 ),0)) ),0)> 0 */

update #temp_part_jj
set xuhk2 = SUBSTRING(xuhk2,isnull(CHARINDEX ('-',xuhk2),0) +1,len(xuhk2) - isnull(CHARINDEX ('-',xuhk2),0))
end
end
end
end
end
end
end

*/
/*计算大于1阶的上阶路径*/
update #temp_part_jj set xuhk1 = '' where llc = 0
if (@@error<>0)
begin
select @l_messcode = '0002'
rollback transaction tran_pr_inv10210_cst
select @l_messcode
return 0
end
/*select substring (xuhk1,1,len(xuhk1) - len(xuhk2) - 1),* from #temp_part_jj where llc >0 */

update #temp_part_jj set xuhk1 = substring (xuhk1,1,len(xuhk1) - len(xuhk2) - 1) where llc >0
if (@@error<>0)
begin
select @l_messcode = '0002'
rollback transaction tran_pr_inv10210_cst
select @l_messcode
return 0
end


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8382469/viewspace-259856/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ERP车间报表开发
请登录后发表评论 登录
全部评论

注册时间:2006-12-03

  • 博文量
    36
  • 访问量
    28411