实现上下文相关视图

实现上下文相关视图
一、 引言
    在数据库应用的开发过程中，我们会经常碰到类似这样的查询语句用来查询某个业务员本人某天的业务记录：
SELECT * FROM DailyBusiness WHERE ClerkId = 'a001'AND OccurDate = '2002-5-1'
同时，出于业务的一些特殊要求，本人只能查看自己的业务数据，或者某人只能查看某日的数据等等。一般，我们用到的方法是在客户端程序中写类似上面语句的查询代码。
    但有没有一种方法，实现这种智能的视图，会根据当前的上下文只显示必要的数据集合呢？这样不但可以降低变成的工作量，并且可以提高程序的可维护性。
因为不必要再重复大量的书写WHERE子句，其次前后台（前台应用程序、后台数据库），前台各个模块中，使用到该数据集合的地方，因为业务逻辑的变化等原因，需要调整该查询语句，那么我们只要简单地修改该上下文视图，而没有必要去修改使用该查询的每个模块。
二、 实现方法
    情况1：如果仅仅是同登录用户的登录Id有关（这里的Id是后台SQL Server数据库的登录帐号，也就是上面的ClerkId就是这个登录帐号），那么我么可以简单地使用以下方式来实现：
CREATE VIEW v_ClerkDailyBusiness
AS
SELECT a.* FROM DailyBusiness a, Master.dbo.sysproceeses b
WHERE a.ClerkId = b.loginname and b.spid = @@spid.

但这种情况一般比较少。另外，有一个缺点就是只能实现有限的上下文相关，灵活性不够。
情况2：许多数据库应用系统一般都只建立少数几个数据库登录帐号，对于应用系统的权限，一般有程序进行控制，那么根据登录帐号来实现，就显得力不从心，因此，我们必须采取其他方法来实现。
SQL Server提供了一个设置连接上下文的功能，即SET CONTEXT_INFO可以为当前的数据库连接设置最大达到128字节的上下文信息，并可以通过查询sysprocesses表获取当前的上下文信息。因此，我们可以初步设想通过SET CONTEXT_INFO设置一些上下文信息，然后再将上下文信息作为上下文相关视图的查询条件，既可以实现。
但是，这里有两个需要解决的问题：
1． 什么时候设置上下文信息
2． 取出上下文信息，并作为查询的条件时，由于需要进行数据转换，免不了使用系统函数，SQL Server就无法对该查询进行优化，如果数据量较大，因此会遇到性能问题。
对于第一个问题，如果是跟用户相关的视图，那么用户登录的时候是最好的设置时机。写上一个用于登录的存储过程，一方面验证用户的口令等信息，另一方面可以调用SET CONTEXT_INFO设置用户的登录Id(这里的Id是数据库应用系统的Id，而不是SQL Server的登录帐号)。
如果是像财务如选择帐套之类的操作，我们可以在选择帐套时，设置当前的上下文为当前的帐套。
由于SET CONTEXT_INFO允许设置最大达到128字节的信息，因此，我们可以进行组合，包含多个上下文信息，如前2个字节为用户Id，后两个字节为帐套号，再接下去是指定的日期等等。
对于第二个问题：首先我们分析一下产生性能问题的原因。
假设我们设置了用户Id上下文，下面是建立视图的脚本如下：
CREATE VIEW v_DailyClerkBusiness
AS
SELECT a.* FROM DailyBusiness a, MASTER.DBO.SysProceeses b
WHERE a.ClerkId = Convert(CHAR(20), b.context_info) 
AND b.spid = @@spId
GO
在做这个查询的时候，因为用到了函数，SQL Server 就无法决定使用DailyBusiness的索引，因此一旦数据量比较大的时候，就会产生严重的性能问题。
那么，解决问题的关键是在做查询的时候，再条件字句中不要使用到Convert之类的影响到SQL Server无法进行查询优化的任何函数。
还好，SQL Server 2000为我们提供了用户函数，并且让我们可以通过用户函数返回一个表变量。因此，我们可以先建立一个进行查询的用户函数，然后再创建以该用户函数为数据源的视图,脚本如下：
CREATE FUNCTION dbo.fn_DailyClerkBusiness()
RETURNS @retVal TABLE
( ClerkId CHAR(20),
OccurDate DATETIME,
……
)
AS
BEGIN
DECLARE @sClerkId CHAR(20)
SELECT @sClerkId = CONVERT(CHAR(20), context_info) 
FROM MASTER.dbo.sysprocesses 
WHERE spid = @@spid

INSERT INTO @retVal
SELECT ClerkId, OccurDate, … 
FROM DailyBusiness 
WHERE ClerkId = @sClerkId
RETURN
END 
GO
CREATE VIEW v_ClerkDailyBusiness
AS
SELECT * FROM dbo.fn_ClerkDailyBusiness()
GO

通过这种方式，虽然SQL Server在调用用户函数的时候将数据集放到了临时表中，但是由于SQL Server能够对该查询进行优化，因此较之第一种方式还是能大大提高查询的性能。当然，凡事有利必有弊，后者只能作为只读视图，不能进行更新操