sqlserver2000 的视图编译问题

忘是亡心i 2022-07-11 08:42 262阅读 0赞

1 原来我一直认为视图纯粹是sql语句的集合，但是现在看来，视图也是会被编译的。直接看例子。

首先 我定义了视图V\_sh\_MainInfoSub，代码如下

CREATE view V\_sh\_MainInfoSub //主要是几个表的联合查询，没有什么复杂的操作  
as  
select  
m1.ma\_id,  
CONVERT(varchar(20), m1.ma\_rep\_datetime, 23) as ma\_rep\_datetime,  
\--(case ma\_isOK when 1 then '已解决' when 2 then '待解决' else '未知' end) as 影响排序，暂时不用  
m1.ma\_isOK,  
m1.ma\_appear, m1.ma\_link, d.dev\_pos\_detail,  
(select i.item\_info from sh\_item i where i.item\_order = d.dev\_type  
and i.item\_type\_name = 'dev\_type' and item\_valid = 1) as dev\_type,  
(select i.item\_info from sh\_item i where i.item\_order = d.dev\_spec  
and i.item\_type\_name = 'dev\_spec' and item\_valid = 1) as dev\_spec,  
m1.ma\_dev\_GUID,--ma\_dev\_GUID和ma\_order不用来显示到页面，  
\--当用户更新MainInfo时，先通过id找到临时表内容，然后通过ma\_dev\_GUID和ma\_order找到sh\_maintain的内容  
m1.ma\_order  
from sh\_maintain m1, sh\_device d  
  
  
where m1.ma\_dev\_GUID = d.dev\_GUID  
and d.dev\_valid = 1  
and m1.ma\_valid = 1

2 然后定义视图 V\_sh\_MainInfo 注意比上面的名字少了sub，内容如下

CREATE view V\_sh\_MainInfo //调用了V\_sh\_MainInfoSub 。主要功能是为查询结果动态创建连续的id号  
as  
select  
(select count(\*) from V\_sh\_MainInfoSub v2 where v2.ma\_id <= v1.ma\_id) as id,  
\* from V\_sh\_MainInfoSub v1

上面两个视图运行良好，没有任何问题。

3 然后，由于需要，在sub视图中增加了个别字段，修改之后的代码如下

CREATE view V\_sh\_MainInfoSub //主要是几个表的联合查询，没有什么复杂的操作  
as  
select  
m1.ma\_id,  
CONVERT(varchar(20), m1.ma\_rep\_datetime, 23) as ma\_rep\_datetime,  
\--(case ma\_isOK when 1 then '已解决' when 2 then '待解决' else '未知' end) as 影响排序，暂时不用  
m1.ma\_isOK,  
m1.ma\_appear, m1.ma\_link, d.dev\_pos\_detail,  
(select i.item\_info from sh\_item i where i.item\_order = d.dev\_type  
and i.item\_type\_name = 'dev\_type' and item\_valid = 1) as dev\_type,  
(select i.item\_info from sh\_item i where i.item\_order = d.dev\_spec  
and i.item\_type\_name = 'dev\_spec' and item\_valid = 1) as dev\_spec,  
m1.ma\_dev\_GUID,--ma\_dev\_GUID和ma\_order不用来显示到页面，  
\--当用户更新MainInfo时，先通过id找到临时表内容，然后通过ma\_dev\_GUID和ma\_order找到sh\_maintain的内容  
m1.ma\_order,  
p.province as dev\_province,  
c.city as dev\_city,  
a.area as dev\_area  
from sh\_maintain m1, sh\_device d, sh\_province p, sh\_city c, sh\_area a  
  
  
where m1.ma\_dev\_GUID = d.dev\_GUID  
and d.dev\_valid = 1  
and m1.ma\_valid = 1  
and p.provinceID = dev\_province  
and c.cityID = dev\_city  
and a.areaID = dev\_area  
and p.provinceID = c.father  
and c.cityID = a.father

怪事出现了，单独运行sub视图没问题，运行V\_sh\_MainInfo 里面的语句也没问题。注意是视图里的语句

select  
(select count(\*) from V\_sh\_MainInfoSub v2 where v2.ma\_id <= v1.ma\_id) as id,  
\* from V\_sh\_MainInfoSub v1

但是只要运行 exec V\_sh\_MainInfo 就是看不到最后追加的三个字段dev\_province ，dev\_city，dev\_area，

最后把V\_sh\_MainInfo 视图删除，重建，哎好了。

因此怀疑视图也是经过了编译了，以上是个人见解，欢迎批评指正