请问可以在视图或存储过程中使用游标吗?1、比如以下视图:CREATE VIEW dbo.VIEW_BM_BluePrintQueryInner
ASSELECT BluePrintName, BluePrintArea, BluePrintNum, BluePrintType, BluePrintTypeName, SpecialType, SpecialTypeName,
Designer, Auditing, Proofread, FinishDate,
SentFlag=case sum(CONVERT(decimal(10,0),isnull(SentFlag, 0))) when count(1) then 1 else 0 end,
ProjectName=dbo.f_BluePrintQuerySign(BluePrintName+BluePrintNum+BluePrintArea+convert(varchar(8),FinishDate,112)),
ProjectID=MAX(ProjectID), FileName,
BookinFlag=case sum(CONVERT(decimal(10,0),isnull(BookinFlag, 0))) when count(1) then 1 else 0 end,
PrintFlag=case sum(CONVERT(decimal(10,0),isnull(PrintFlag, 0))) when count(1) then 1 else 0 end,
PigeonFlag=case sum(CONVERT(decimal(10,0),isnull(PigeonFlag, 0))) when count(1) then 1 else 0 end,
MakeupFlag=case sum(CONVERT(decimal(10,0),isnull(MakeupFlag, 0))) when count(1) then 1 else 0 end,
BluePrintID=MAX(ID), LogoutFlagFROM VIEW_BM_BluePrintQuery where LogoutFlag=0
Group BY BluePrintName, BluePrintArea, BluePrintNum, BluePrintType, BluePrintTypeName, SpecialType, SpecialTypeName,
Designer, Auditing, Proofread, FinishDate, FileName, LogoutFlag这其中含有一个我自己写的函数调用,请问在此处可否使用游标?2、又比如以下存储过程:
CREATE PROCEDURE sql_rs_ComplexTableQuery @TableName varchar(800), --表名称
@FieldName varchar(8000), --字段名称
@strCondition varchar(8000), --查询条件
@strID varchar(50), --排序条件
@ID INT, --查询语句分类ID
@PageNo INT, --当前要返回的页码
@PageSize INT, --每页显示的最大条数
@RecordCount INT OUTPUT, --返回总记录数
@PageCount INT OUTPUT, --返回总页数
@ErrorFlag INT OUTPUT --错误标记
AS
/*定义局部变量*/
DECLARE @intBeginID INT --
DECLARE @intEndID INT --
DECLARE @intRootRecordCount INT --
DECLARE @intRowCount INT -- /*定义查询语句*/
DECLARE @SQLA nvarchar(4000) --
DECLARE @SQLB nvarchar(4000) --
DECLARE @SQLC nvarchar(4000) --
DECLARE @SQL nvarchar(4000) -- /*关闭计数*/
SET NOCOUNT ON /*初始化错误标记值*/
SET @ErrorFlag = 0 /*==========*/
/*求总代码数*/
/*==========*/
BEGIN
IF @ID = 1
BEGIN
set @SQLA = 'SELECT @intRootRecordCount = COUNT('+ @strID +') FROM '+@TableName+' where '+@strCondition+' '
exec sp_executesql @SQLA,N'@intRootRecordCount INT output',@intRootRecordCount output
END /*-如果没有所需要的代码,则返回零*/
IF (@intRootRecordCount = 0)
SET @ErrorFlag = 1
SET @RecordCount = @intRootRecordCount /*判断页数是否正确*/
/*@intRootRecordCount表示所有符合条件的记录*/
IF (@PageNo - 1) * @PageSize > @RecordCount
SET @ErrorFlag = 2 /*========*/
/*求总页数*/
/*========*/ IF @RecordCount < @PageSize
SET @PageCount = 1
ELSE
IF (@RecordCount % @PageSize) != 0
SET @PageCount = @RecordCount / @PageSize + 1
ELSE
SET @PageCount = @RecordCount / @PageSize
END /*===========*/
/*求开始rootID*/
/*===========*/ BEGIN
SET @intRowCount = (@PageNo - 1) * @PageSize + 1
/*限制条数*/
SET rowcount @intRowCount
BEGIN
IF @ID = 1
BEGIN
set @SQLB = 'SELECT distinct @intBeginID = '+ @strID +' FROM '+@TableName+' where '+@strCondition+' ORDER BY '+@strID+' DESC'
exec sp_executesql @SQLB,N'@intBeginID INT output',@intBeginID output
END
END
END /*===========*/
/*求结束rootID*/
/*===========*/ BEGIN
SET @intRowCount = @PageNo * @PageSize
/*限制条数*/
SET rowcount @intRowCount BEGIN
IF @ID = 1
BEGIN
set @SQLC = 'SELECT distinct @intEndID = '+ @strID +' FROM '+@TableName+' where '+@strCondition+' ORDER BY '+@strID+' DESC'
exec sp_executesql @SQLC,N'@intEndID INT output',@intEndID output
END
END
END /*恢复系统变量*/
SET rowcount 0
SET NOCOUNT OFF /*================*/
/*返回指定的记录集*/
/*================*/
BEGIN
IF @ID = 1
BEGIN
exec ('select '+@FieldName+' FROM '+@TableName+' WHERE '+@strCondition+' AND '+@strID+' BETWEEN '''+@intEndID+''' AND '''+@intBeginID+''' ORDER BY '+@strID+' DESC')
END
END RETURN (@@rowcount)
GO这是一个通用的存储过程,但在大数据量检索时还是不够理想,请问这里面可以使用游标提高检索速度么?
ASSELECT BluePrintName, BluePrintArea, BluePrintNum, BluePrintType, BluePrintTypeName, SpecialType, SpecialTypeName,
Designer, Auditing, Proofread, FinishDate,
SentFlag=case sum(CONVERT(decimal(10,0),isnull(SentFlag, 0))) when count(1) then 1 else 0 end,
ProjectName=dbo.f_BluePrintQuerySign(BluePrintName+BluePrintNum+BluePrintArea+convert(varchar(8),FinishDate,112)),
ProjectID=MAX(ProjectID), FileName,
BookinFlag=case sum(CONVERT(decimal(10,0),isnull(BookinFlag, 0))) when count(1) then 1 else 0 end,
PrintFlag=case sum(CONVERT(decimal(10,0),isnull(PrintFlag, 0))) when count(1) then 1 else 0 end,
PigeonFlag=case sum(CONVERT(decimal(10,0),isnull(PigeonFlag, 0))) when count(1) then 1 else 0 end,
MakeupFlag=case sum(CONVERT(decimal(10,0),isnull(MakeupFlag, 0))) when count(1) then 1 else 0 end,
BluePrintID=MAX(ID), LogoutFlagFROM VIEW_BM_BluePrintQuery where LogoutFlag=0
Group BY BluePrintName, BluePrintArea, BluePrintNum, BluePrintType, BluePrintTypeName, SpecialType, SpecialTypeName,
Designer, Auditing, Proofread, FinishDate, FileName, LogoutFlag这其中含有一个我自己写的函数调用,请问在此处可否使用游标?2、又比如以下存储过程:
CREATE PROCEDURE sql_rs_ComplexTableQuery @TableName varchar(800), --表名称
@FieldName varchar(8000), --字段名称
@strCondition varchar(8000), --查询条件
@strID varchar(50), --排序条件
@ID INT, --查询语句分类ID
@PageNo INT, --当前要返回的页码
@PageSize INT, --每页显示的最大条数
@RecordCount INT OUTPUT, --返回总记录数
@PageCount INT OUTPUT, --返回总页数
@ErrorFlag INT OUTPUT --错误标记
AS
/*定义局部变量*/
DECLARE @intBeginID INT --
DECLARE @intEndID INT --
DECLARE @intRootRecordCount INT --
DECLARE @intRowCount INT -- /*定义查询语句*/
DECLARE @SQLA nvarchar(4000) --
DECLARE @SQLB nvarchar(4000) --
DECLARE @SQLC nvarchar(4000) --
DECLARE @SQL nvarchar(4000) -- /*关闭计数*/
SET NOCOUNT ON /*初始化错误标记值*/
SET @ErrorFlag = 0 /*==========*/
/*求总代码数*/
/*==========*/
BEGIN
IF @ID = 1
BEGIN
set @SQLA = 'SELECT @intRootRecordCount = COUNT('+ @strID +') FROM '+@TableName+' where '+@strCondition+' '
exec sp_executesql @SQLA,N'@intRootRecordCount INT output',@intRootRecordCount output
END /*-如果没有所需要的代码,则返回零*/
IF (@intRootRecordCount = 0)
SET @ErrorFlag = 1
SET @RecordCount = @intRootRecordCount /*判断页数是否正确*/
/*@intRootRecordCount表示所有符合条件的记录*/
IF (@PageNo - 1) * @PageSize > @RecordCount
SET @ErrorFlag = 2 /*========*/
/*求总页数*/
/*========*/ IF @RecordCount < @PageSize
SET @PageCount = 1
ELSE
IF (@RecordCount % @PageSize) != 0
SET @PageCount = @RecordCount / @PageSize + 1
ELSE
SET @PageCount = @RecordCount / @PageSize
END /*===========*/
/*求开始rootID*/
/*===========*/ BEGIN
SET @intRowCount = (@PageNo - 1) * @PageSize + 1
/*限制条数*/
SET rowcount @intRowCount
BEGIN
IF @ID = 1
BEGIN
set @SQLB = 'SELECT distinct @intBeginID = '+ @strID +' FROM '+@TableName+' where '+@strCondition+' ORDER BY '+@strID+' DESC'
exec sp_executesql @SQLB,N'@intBeginID INT output',@intBeginID output
END
END
END /*===========*/
/*求结束rootID*/
/*===========*/ BEGIN
SET @intRowCount = @PageNo * @PageSize
/*限制条数*/
SET rowcount @intRowCount BEGIN
IF @ID = 1
BEGIN
set @SQLC = 'SELECT distinct @intEndID = '+ @strID +' FROM '+@TableName+' where '+@strCondition+' ORDER BY '+@strID+' DESC'
exec sp_executesql @SQLC,N'@intEndID INT output',@intEndID output
END
END
END /*恢复系统变量*/
SET rowcount 0
SET NOCOUNT OFF /*================*/
/*返回指定的记录集*/
/*================*/
BEGIN
IF @ID = 1
BEGIN
exec ('select '+@FieldName+' FROM '+@TableName+' WHERE '+@strCondition+' AND '+@strID+' BETWEEN '''+@intEndID+''' AND '''+@intBeginID+''' ORDER BY '+@strID+' DESC')
END
END RETURN (@@rowcount)
GO这是一个通用的存储过程,但在大数据量检索时还是不够理想,请问这里面可以使用游标提高检索速度么?
使用游标基本不会提高检索速度
请问在此处可否使用游标? 视图不能使用游标.2.这是一个通用的存储过程,但在大数据量检索时还是不够理想,
请问这里面可以使用游标提高检索速度么?存储过程可以使用游标,如果要提高检索速度的话,
游标也不能提高吧,应该是要建立好索引,处理好数据分区分组等等.
优化sp逻辑,尽量少循环遍历等.