DECLARE @WineID Char(36),@AreaID Char(36),@ChateauID Char(36) ,@wineVarietyID char(36),@Names NVARCHAR(100); Declare @WineVarietyEn nvarchar(50), @WineVarietyCn nvarchar(50) Declare @AliasCn nvarchar(max), @AliasEn nvarchar(max),@AliasCnCount int,@AliasEnCount int Declare @GrapeEnName nvarchar(max), @GrapeCnName nvarchar(max), @GrapeCount int Declare @ChateauCn nvarchar(max), @ChateauEn nvarchar(max) Declare @AreaCn nvarchar(max), @AreaEn nvarchar(max) Delete FROM LucenIndexTab; BEGIN --定义游标. DECLARE WineCursor CURSOR FOR
Select ID,Names,AreaID,ChateauID,wineVarietyID From WineIndexs Order by hitcount --
--where ID='E4907C1D-699F-48A8-9B86-45EB652FDB96' --FOR UPDATE; --打开游标. OPEN WineCursor; --填充数据. FETCH NEXT FROM WineCursor INTO @WineID,@Names,@AreaID,@ChateauID,@wineVarietyID; --假如检索到了数据,才处理. WHILE @@fetch_status = 0 BEGIN PRINT '+++++++++++++++++++++++++++++++++++++++++++++++++++++'; PRINT @WineID+'名称:'+@Names; PRINT '+++++++++++++++++++++++++++++++++++++++++++++++++++++'; --更新数据. -- UPDATE -- WineIndexs -- SET -- Names = Names + '更新' -- -- WHERE -- CURRENT OF WineCursor; -- 填充下一条数据. -- 创建数据库----下边是使用自定义函数部分 ---酒种类 set @WineVarietyEn=(select [foreignName] from GetWineVarietyEn(@wineVarietyID)); set @WineVarietyCn=(select [chineseName] from GetWineVarietyCn(@wineVarietyID)); --别名 set @AliasCn=(select Alias from GetUnionAliasTab(@WineID,1)); set @AliasEn=(select Alias from GetUnionAliasTab(@WineID,0)); set @AliasCnCount=(select AliasCount from GetUnionAliasTab(@WineID,1)); set @AliasEnCount=(select AliasCount from GetUnionAliasTab(@WineID,0)); --葡萄种类 set @GrapeEnName=(select GrapeName from GetUnionGrapeTab(@WineID,0)); set @GrapeCnName=(select GrapeName from GetUnionGrapeTab(@WineID,1)); set @GrapeCount=(select GrapeCount from GetUnionGrapeTab(@WineID,0)); -- 酒庄名称 set @ChateauEn=(select [foreignName] from GetChateauEn(@ChateauID)); set @ChateauCn=(select * from GetChateauCn(@ChateauID)); -- 产区名称 set @AreaCn=(select [chineseName] from GetAreaCn(@AreaID)); set @AreaEn=(select [foreignName] from GetAreaEn(@AreaID));----结束使用自定义函数部分 ---插入数据到新表(LucenIndexTab) insert into LucenIndexTab select * ,@WineVarietyEn,@WineVarietyCn,@AliasCn,@AliasEn,@AliasCnCount,@AliasEnCount ,@GrapeEnName,@GrapeCnName,@GrapeCount,@ChateauEn,@ChateauCn ,@AreaEn,@AreaCn from WineIndexs where ID=@WineID print '中文别名:'+@AliasCn print 'e文别名:'+@AliasEn PRINT '----------------------------------------------------'; print '葡萄中文名:'+@GrapeEnName print '葡萄e文名:'+@GrapeCnName PRINT '----------------------------------------------------'; print '产区中文名:'+@AreaCn print '产区e文名:'+@AreaEn FETCH NEXT FROM WineCursor INTO @WineID,@Names,@AreaID,@ChateauID,@wineVarietyID; END; --关闭游标 CLOSE WineCursor; --释放游标. DEALLOCATE WineCursor; END; Go
你其实要去研究你的数据,我举个例子,如果你是从A表去数据,然后对字段a调用函数f,那你可以使用: insert into 目标表 select f(a),.... from A这样就可以拉,没必要用游标甚至循环。 上述只是例子,有点.....别见怪。
,@wineVarietyID char(36),@Names NVARCHAR(100); Declare @WineVarietyEn nvarchar(50), @WineVarietyCn nvarchar(50) Declare @AliasCn nvarchar(max), @AliasEn nvarchar(max),@AliasCnCount int,@AliasEnCount int Declare @GrapeEnName nvarchar(max), @GrapeCnName nvarchar(max), @GrapeCount int Declare @ChateauCn nvarchar(max), @ChateauEn nvarchar(max)
Declare @AreaCn nvarchar(max), @AreaEn nvarchar(max) Delete FROM LucenIndexTab;
BEGIN
--定义游标.
DECLARE WineCursor CURSOR FOR
Select ID,Names,AreaID,ChateauID,wineVarietyID
From WineIndexs Order by hitcount --
--where ID='E4907C1D-699F-48A8-9B86-45EB652FDB96' --FOR UPDATE;
--打开游标.
OPEN WineCursor;
--填充数据.
FETCH NEXT FROM WineCursor INTO @WineID,@Names,@AreaID,@ChateauID,@wineVarietyID;
--假如检索到了数据,才处理.
WHILE @@fetch_status = 0 BEGIN PRINT '+++++++++++++++++++++++++++++++++++++++++++++++++++++';
PRINT @WineID+'名称:'+@Names;
PRINT '+++++++++++++++++++++++++++++++++++++++++++++++++++++';
--更新数据.
-- UPDATE
-- WineIndexs
-- SET
-- Names = Names + '更新'
--
-- WHERE
-- CURRENT OF WineCursor; -- 填充下一条数据. -- 创建数据库----下边是使用自定义函数部分 ---酒种类
set @WineVarietyEn=(select [foreignName] from GetWineVarietyEn(@wineVarietyID));
set @WineVarietyCn=(select [chineseName] from GetWineVarietyCn(@wineVarietyID)); --别名
set @AliasCn=(select Alias from GetUnionAliasTab(@WineID,1));
set @AliasEn=(select Alias from GetUnionAliasTab(@WineID,0));
set @AliasCnCount=(select AliasCount from GetUnionAliasTab(@WineID,1));
set @AliasEnCount=(select AliasCount from GetUnionAliasTab(@WineID,0));
--葡萄种类
set @GrapeEnName=(select GrapeName from GetUnionGrapeTab(@WineID,0));
set @GrapeCnName=(select GrapeName from GetUnionGrapeTab(@WineID,1));
set @GrapeCount=(select GrapeCount from GetUnionGrapeTab(@WineID,0)); -- 酒庄名称
set @ChateauEn=(select [foreignName] from GetChateauEn(@ChateauID));
set @ChateauCn=(select * from GetChateauCn(@ChateauID));
-- 产区名称
set @AreaCn=(select [chineseName] from GetAreaCn(@AreaID));
set @AreaEn=(select [foreignName] from GetAreaEn(@AreaID));----结束使用自定义函数部分
---插入数据到新表(LucenIndexTab) insert into LucenIndexTab select * ,@WineVarietyEn,@WineVarietyCn,@AliasCn,@AliasEn,@AliasCnCount,@AliasEnCount ,@GrapeEnName,@GrapeCnName,@GrapeCount,@ChateauEn,@ChateauCn ,@AreaEn,@AreaCn from WineIndexs where ID=@WineID
print '中文别名:'+@AliasCn
print 'e文别名:'+@AliasEn
PRINT '----------------------------------------------------'; print '葡萄中文名:'+@GrapeEnName
print '葡萄e文名:'+@GrapeCnName PRINT '----------------------------------------------------'; print '产区中文名:'+@AreaCn
print '产区e文名:'+@AreaEn
FETCH NEXT FROM WineCursor INTO @WineID,@Names,@AreaID,@ChateauID,@wineVarietyID; END; --关闭游标
CLOSE WineCursor;
--释放游标.
DEALLOCATE WineCursor; END; Go
insert into 目标表
select f(a),....
from A这样就可以拉,没必要用游标甚至循环。
上述只是例子,有点.....别见怪。
函数可以转为outer apply..的写法
子查询可以转为left join..的写法
不可以 有一对多的关系 而且 一对多这个多要变成一列 我自定义函数里边做这样处理了比如 一个 wineID 可以对应多个 GrpeID 类似的