{SQL} 單欄資料列取值方式(資料指標-FETCH 迴圈)
逐列處理資料的方式其一Cursor
DECLARE @temp_addr NVARCHAR(100)=NULL
DECLARE @temp_zipcode NVARCHAR(10)
DECLARE @temp_district NVARCHAR(50)
DECLARE @temp_city NVARCHAR(50)
DECLARE @temp_IsPostAddr NVARCHAR(1)
DECLARE @temp_ori NVARCHAR(400)
DECLARE @data_count INT=0
SET @temp_ori= dbo.f_AddressMerge(@mail_addr_dis,@mail_road,@mail_lane,@mail_alley,@mail_number_start,@mail_number_end,@mail_floor_start,@mail_floor_end,@mail_room,@mail_remark,@mail_post_name,@mail_postal_address,@mail_lane_l,@mail_alley_l,@mail_hong,@mail_hong_l,@mail_number_start2)
----宣告一個指標
DECLARE my_cur CURSOR FOR (
--回傳純量函數
SELECT res from f_SplitToTable(@temp_ori,',')
)
--開啟指標
open my_cur
--當指標的狀態為0, 即指標內尚有資料
--如知道資料列中有多少資料，可以用data_count變數來判斷
--WHILE @@fetch_status = 0 AND @data_count<5
WHILE @data_count<5
BEGIN
--By不同列指定給不同的變數
SET @data_count=@data_count+1
IF @data_count='1'
BEGIN
FETCH NEXT FROM my_cur INTO @temp_IsPostAddr--,@temp_zipcode,@temp_city,@temp_district,@temp_addr
END
IF @data_count='2'
BEGIN
FETCH NEXT FROM my_cur INTO @temp_zipcode--@temp_city,@temp_district,@temp_addr
END
IF @data_count='3'
BEGIN
FETCH NEXT FROM my_cur INTO @temp_city--,@temp_district,@temp_addr
END
IF @data_count='4'
BEGIN
FETCH NEXT FROM my_cur INTO @temp_district--,@temp_addr
END
IF @data_count='5'
BEGIN
FETCH NEXT FROM my_cur INTO @temp_addr
END
END
--關閉指標
CLOSE my_cur
--釋放記憶體
DEALLOCATE my_cur
--方式二
DECLARE @temp_addr NVARCHAR(100)=NULL
DECLARE @temp_zipcode NVARCHAR(10)
DECLARE @temp_district NVARCHAR(50)
DECLARE @temp_city NVARCHAR(50)
DECLARE @temp_IsPostAddr NVARCHAR(1)
DECLARE @temp_ori TABLE (IsPostAddr nvarchar(1), zipcode nvarchar(6), city nvarchar(20), district nvarchar(20), result nvarchar(300))
DECLARE @data_count INT
--回傳多重式資料表直函式
INSERT INTO @temp_ori(IsPostAddr,zipcode,city,district,result)
(SELECT * FROM dbo.tf_AddressMerge(@mail_addr_dis,@mail_road,@mail_lane,@mail_alley,@mail_number_start,@mail_number_end,@mail_floor_start,@mail_floor_end,@mail_room,@mail_remark,@mail_post_name,@mail_postal_address,@mail_lane_l,@mail_alley_l,@mail_hong,@mail_hong_l,@mail_number_start2))
SET @data_count=(SELECT COUNT(*) CT FROM @temp_ori)
IF @data_count>0
BEGIN
SET @temp_IsPostAddr =(SELECT TOP 1 IsPostAddr FROM @temp_ori)
SET @temp_zipcode =(SELECT TOP 1 zipcode FROM @temp_ori)
SET @temp_city =(SELECT TOP 1 city FROM @temp_ori)
SET @temp_district =(SELECT TOP 1 district FROM @temp_ori)
SET @temp_addr =(SELECT TOP 1 result FROM @temp_ori)
END
