数据库高级查询设计

/* 

--前提描述，一张flidtab表有5个字段。Fid是主键，int类型、主动增长（identity）。cid、pid、tid是外键，int类型、主动增长（identity）。Fidno由cid、pid、tid组合而成，前提cid、pid、tid不足5位的添加成5位长度。用sql批量实现。

*/

 -- 创建表

 create table flidTab

 (

    fid int primary key identity(1,1),

    fidno varchar(21) not null,

    cid int not null,

    pid int not null,

    tid int not null

 )

 go

-- 插入数据

 insert flidtab 

   select '123123',1,1,1

     union all select 'qwe',2,3,3

     union all select 'asd',3,3,1

      union all select 'zXc',2,3,1

go

select * from flidtab

go

 

-- 建造用于填充位数的 id

-- 判断 cId 长度

create  proc BuildcId

 @inputId int ,

 @outputId varchar(5) output

as

 select @outputId = cid from flidtab where fid = @inputId

 -- 判断长度

if ( len ( @outputId)=2)

begin

set @outputId = '000'+@outputId

end

else if(len(@outputId)=3)

begin

set @outputId = '00'+@outputId

end

else if(len(@outputId)=4)

begin

set @outputId = '0'+@outputId

end

else if(len(@outputId)=1)

begin

set @outputId = '0000'+@outputId

end

else

begin

set @outputId =''

end

go

 

-- 判断 pId 长度

create  proc BuildpId

 @inputId int ,

 @outputId varchar(5) output

as

 select @outputId = pid from flidtab where fid = @inputId

 -- 判断长度

if ( len ( @outputId)=2)

begin

set @outputId = '000'+@outputId

end

else if(len(@outputId)=3)

begin

set @outputId = '00'+@outputId

end

else if(len(@outputId)=4)

begin

set @outputId = '0'+@outputId

end

else if(len(@outputId)=1)

begin

set @outputId = '0000'+@outputId

end

else

begin

set @outputId =''

end

go

-- 判断 tId 长度

create  proc BuildtId

 @inputId int ,

 @outputId varchar(5) output

as

 select @outputId = tid from flidtab where fid = @inputId

 -- 判断长度

if ( len ( @outputId)=2)

begin

set @outputId = '000'+@outputId

end

else if(len(@outputId)=3)

begin

set @outputId = '00'+@outputId

end

else if(len(@outputId)=4)

begin

set @outputId = '0'+@outputId

end

else if(len(@outputId)=1)

begin

set @outputId = '0000'+@outputId

end

else

begin

set @outputId =''

end

go

 

-- 测试

declare @out varchar(5)

exec buildpid 1, @out output

print '---------'+@out

go

 

-- 创建修改存储过程

create proc prc_updateFidNo

@id int

as

declare @cid varchar(5),

        @pid varchar(5),

        @tid varchar(5),

       @fidno varchar(30)  

 

 exec buildcid @id, @cid output

 exec buildpid @id, @pid output

 exec buildtid @id, @tid output

 

select @fidno = (select fidno from flidTab where fid = f.fid) from flidTab as f

select @fidno =@fidno + '_'+@cid+ '_'+@pid+ '_'+@tid from flidTab as f where f.fid=1

print '---------========='+@fidno

-- 修改数据

update flidTab set fidno = @fidno

     where fid = @id

go

 

-- 测试

exec prc_updateFidNo 1

go

 select * from flidtab

go

 

-- 声明只读游标

declare findfidNo cursor for

 select fid from flidTab

for read only

-- 打开游标

open findfidno

-- 执行

declare @fid int

fetch next from findfidno into @fid

while ( @@fetch_status = 0)

begin

 exec prc_updateFidNo @fid --执行修改

 fetch next from findfidno into @fid

end

 

go

-- 关闭游标

close findfidNo

deallocate findfidNo

go

-- 查看测试结果

select * from flidtab

go