-- 定位數據
-- 設計思路:
-- 保存要被移動記錄的原主鍵值和新主鍵值(如果有主鍵的話)，然后比較兩個主鍵值的大?。ㄓ涗浶畔瓷蚺帕校?br>-- 如果原主鍵值大，表明該記錄被移動到前面的新位置，可將原位置的記錄信息保存下來，并將從新位置的記錄信息到
-- 原記錄的前一條記錄的信息順次往下移動，把保存的原記錄移動到新位置即可。
-- 如果原主鍵值小，表明該記錄被移動到后面的新位置，可將原位置的記錄信息保存下來，并將從新位置的記錄信息到
-- 原記錄的下一條記錄的信息順次往上移動，把保存的原記錄移動到新位置即可。實際上就是記錄塊的移動.
-- 但是,如果數據表中的數據非常龐大,該存儲過程的執行效率將會下降.
use zzydb
--創建舉例表(學生信息表)
create table t_studentsinfo
(i_id int identity(1,1), --系統自增流水號
c_stu_id nvarchar(10), --學號
c_classname nvarchar(50), --班級
d_birthday datetime) --出生日期
go
--向舉例表中插入4條學生信息,以驗證下面的存儲過程(sp_myadjustrecordorder)
insert into t_studentsinfo values('001', '大二三班', '1978-01-25')
insert into t_studentsinfo values('002', '大一六班', '1979-02-05')
insert into t_studentsinfo values('003', '大四三班', '1981-07-15')
insert into t_studentsinfo values('004', '大三一班', '1976-01-05')
select * from t_studentsinfo
if object_id('sp_myadjustrecordorder') <> 0
drop proc sp_myadjustrecordorder
go
create proc sp_myadjustrecordorder(@oldstuid nvarchar(10), @newstuid nvarchar(10))
as
[email protected] 學生學號(用以表示被移動的記錄),
[email protected] 學生學號(用以表示將被移動的記錄插入的新位置)
begin
declare @old_i_id int, @new_i_id int
declare @i_blockcount int--即將被移動的記錄塊條數
declare @i int--循環變量
--獲得id值
select @old_i_id = (select i_id from t_studentsinfo where c_stu_id = @oldstuid)
select @new_i_id = (select i_id from t_studentsinfo where c_stu_id = @newstuid)
select @i_blockcount = abs(@old_i_id - @new_i_id)
--保存被移動的學生信息
select c_stu_id, c_classname, d_birthday
into new_studentsinfo--臨時創建的表，用完后刪除
from t_studentsinfo
where c_stu_id = @oldstuid
if @new_i_id < @old_i_id --將原記錄信息移動到了前面
begin
select @i = 0
while @i <= @i_blockcount - 1
begin
update t_studentsinfo
set c_stu_id = t2.c_stu_id,
c_classname = t2.c_classname,
d_birthday = t2.d_birthday
from t_studentsinfo , t_studentsinfo t2
where (t_studentsinfo.i_id = @old_i_id - @i) and
(t2.i_id = @old_i_id - @i - 1)
select @i = @i + 1
end
end
if @new_i_id > @old_i_id --將原記錄信息移動到了后面
begin
select @i = 0
while @i <= @i_blockcount - 1
begin
update t_studentsinfo
set c_stu_id = t2.c_stu_id,
c_classname = t2.c_classname,
d_birthday = t2.d_birthday
from t_studentsinfo, t_studentsinfo t2
where (t_studentsinfo.i_id = @old_i_id + @i) and
(t2.i_id = @old_i_id + @i + 1)
select @i = @i + 1
end
end
update t_studentsinfo
set c_stu_id = t3.c_stu_id,
c_classname = t3.c_classname,
d_birthday = t3.d_birthday
from t_studentsinfo t1, new_studentsinfo t3
where (t1.i_id = @new_i_id )
drop table new_studentsinfo
--if @new_i_id = @old_i_id --位置未發生改變，不做任何處理
end
go
--使用舉例
--要求:將c_stu_id為'004'的學生信息移動到c_stu_id為'002'的學生信息之前的位置.
--調用存儲過程 sp_myadjustrecordorder('004','002')即可.
--注意:這里的i_id必須是順序的。
--備份表數據信息，以便于比較
select *
into studentsinfobackup
from t_studentsinfo
--比較結果是否一致
select * from studentsinfobackup
select * from t_studentsinfo
--移動數據記錄信息，調用存儲過程
exec sp_myadjustrecordorder '003', '002'
--比較結果是否改動
select * from studentsinfobackup
select * from t_studentsinfo
