- Positioning Data - Design Idea: - Save the original key value and new home key value to be moved (if there is a primary key), then compare the size of the two primary key (record information is arranged in ascending order), - If the original key value is large, it indicates that the record is moved to the previous new location, and the record information of the original position can be saved, and the information from the new location to the previous record of the original record is sequentially Move, move the saved original record to the new location. - If the original key value is small, it indicates that the record is moved to the new location, and the record information of the original position can be saved, and the information from the new location to the next record of the original record is sequentially Move, move the saved original record to the new location. In fact, the movement of the record block .-- However, if the data in the data table is very large, the execution efficiency of the stored procedure will drop.
Use zzydb
- Create an exemplary table (student information table) create table t_studentsinfo (i_id int identity (1, 1), - system self-increased water number c_stu_id nvarchar (10), - student number c_classname nvarchar (50), - class D_BIRTHDAY DateTime) - Date of birth
- Insert 4 student information to the exemplary table to verify the following stored procedures (sp_myadjustRecordorder) INTO T_STUDENTSINFO VALUES ('001', 'Big Twenty-three class', '1978-01-25') Insert Into T_studentsinfo Values '002', 'big one and sixth class',' 1979-02-05 ') Insert Into T_studentsinfo Values (' 003 ',' Big Four Thousand Class', '1981-07-15') Insert Into T_Studentsinfo Values ('004 ',' Big three shifts', '1976-01-05')
Select * from t_studentsInfo
IF Object_ID ('sp_myadjustRecordorder') <> 0 Drop Proc SP_MYADJUSTRECORDORDERGO
Create Proc SP_MYADJUSTRECORDORDER (@eldstuid nvarchar (10)) AS - @ OldStuid student number (to represent the mobile record), - @ newstuid student number (to indicate that it will be moved New position of the record insertion) Begin Declare @old_i_id int
- obtaining id value 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)
- the original record information is moved to the front begin - save student information Select c_Stu_ID be moved, c_ClassName, d_BirthDay into New_StudentsInfo-- temporary table creation, deletion from T_StudentsInfo where c_Stu_ID = @OldStuId if @New_i_id <@Old_i_id after use 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 Endif @new_i_id> @old_i_id - Move the original record information to the back 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 - No change, do not do any processing ENDGO
- Example of use - Requirements: Move the C_STU_ID to '004' The location before the c_stu_id is '002' student information. - Call the stored procedure sp_myadjustRecordORDER ('004', '002'). - Note: I_ID here must be in order.
- Backup table data information for easy comparison Select * INTO StudentSinfobackupFrom T_StudentsInfo
- Comparison result is unanimous Select * from studentsinfobackupselect * from t_studentsinfo
- Move data record information, call the stored procedure EXEC SP_MYADJUSTRECORDORDER '003', '002' - the result of the comparison changes Select * from studentsinfoBackupselect * from t_studentsinfo
