发信人: coolyylu(GoodDay)
整理人: hunter__fox(2002-03-16 22:15:51), 站内信件
|
【 在 golf2000 的大作中提到：】
: 在程序中使用的视图是事先预定义后使用好，还是在需要时用语句定义，用后删除好？我发现同一视图在每次定义时将会增加数据库的容量,如果采用预定义的方法又觉得视图文件太多，不知高手有何指教？
:......
一般不会很大。使用
open database xml exclusive
pack database &&删除没有的记录，不会影响数据库操作
另外一种情况是建立动态试图。包括远程和本地，通用程序如下（实际例子）：
FUNCTION MakeView
PARAMETER ctable,cview
if file("utgl_sql.dbc") &&处理远处视图
If not dbused("utgl_sql")
OPEN DATABASE UTGL_SQL shared
endif
set database to utgl_sql
endif
v_select=SELECT()
SELECT 0
USE utview
sqltable(1,'TABLE')
LOCATE FOR LOWER(table_name)=lower(ctable)
IF !FOUND()
MESSAGEBOX("在SQL Server中不存在"+CTABLE,0,'不能建立视图')
USE
SELECT utview
USE
SELECT (v_select)
RETURN
ENDIF
IF TYPE('cview')#'C'
cview=''
ENDIF
ctable=ALLTRIM(table_name)
SELECT utview
IF EMPTY(cview)
locate all for upper(tablename)=upper(ctable)
cview=ctable
ELSE
locate all for upper(tablename)=upper(ctable).AND.upper(viewname)=upper(cview)
ENDIF
STORE '' TO v_orderby,v_where
if found()
do while found()
cview=trim(viewname)
v_where=alltrim(defa_where)
v_orderby=alltrim(orderby)
do makeview_sub
select utview
continue
enddo
else
do makeview_sub
endif
SELECT utview
USE
SELECT (v_select)
RETURN
FUNCTION MAKEVIEW_SUB &&更改视图子程序
V_CREAT="CREATE SQL VIEW '"+cview+"' REMOTE CONNECT 'sqlsvr_utgl' AS SELECT * FROM "+ctable
if !empty(v_where)
v_where=ctable+subs(v_where,at('.',v_where))
v_creat=v_creat+ ' WHERE '+v_where
endif
if !empty(v_orderby)
v_orderby=ctable+subs(v_orderby,at('.',v_orderby))
v_creat=v_creat+ ' ORDER BY '+v_orderby
endif
&V_CREAT
DBSetProp(cview, 'View', 'UpdateType', 1)
DBSetProp(cview, 'View', 'WhereType', 1)
DBSetProp(cview, 'View', 'SendUpdates', .T.)
DBSetProp(cview, 'View', 'FetchMemo', .T.)
IF !empty(v_where)
* DBSetProp(cview, 'View', 'ParameterList', "cGcbh_view,'C';nKuHao_view,'N'")
endif
SELECT 0
USE &cview
GO BOTTOM
SQLCOLUMN(1,ctable,'NATIVE','sql_stru')
SELECT sql_stru
GO TOP
DO WHILE .NOT. EOF()
t_field=TRIM(column_name)
t_default=ALLTRIM(column_def)
if at("(",t_default)=1
t_default=subs(t_default,2,len(t_default)-2)
else
t_default=''
endif
v_field=cview+'.'+t_field
up_field=ctable+'.'+t_field
SELECT &cview
sql_key=DBGETProp(up_field, 'Field', 'KeyField')
DBSetProp(v_field, 'Field', 'KeyField', sql_key)
DBSetProp(v_field, 'Field', 'Updatable', .T.)
DBSetProp(v_field, 'Field', 'UpdateName', up_field)
sql_type=DBGETPROP(v_field,'Field','DataType')
c_sql_type=SUBS(sql_type,1,1)
DO CASE
CASE c_sql_type="C"
if empty(t_default)
SQLEXEC(1,"alter table "+ctable+" add default '' for "+t_field) &&修改SQL中的默认值
t_default="''"
endif
IF t_field="sqllno"
DBSetProp(v_field, 'Field', 'DefaultValue', "KeyValue()")
REPLACE ALL &t_field with sys(2015)+'系统' FOR EMPTY(&T_FIELD) OR ISNULL(&T_FIELD)
ELSE
DBSetProp(v_field, 'Field', 'DefaultValue', '"' + &t_default + '"')
REPLACE ALL &t_field WITH &t_default FOR ISNULL(&t_field)
ENDIF
IF t_field="sys_creater" &&记录创建者
DBSetProp(v_field, 'Field', 'DefaultValue', "username")
ENDIF
CASE c_sql_type="N"
if empty(t_default)
SQLEXEC(1,"alter table "+ctable+" add default 0 for "+t_field) &&修改SQL中的默认值
t_default='0'
endif
v_len=VAL(SUBS(sql_type,3))
old_type=STRT(sql_type,"N("+ALLTRIM(STR(v_len)),'')
new_type='N('+ALLTRIM(STR(v_len-2))+old_type
DBSETPROP(v_field,'Field','DataType',new_type)
CASE c_sql_type="M"
if empty(t_default)
SQLEXEC(1,"alter table "+ctable+" add default '' for "+t_field) &&修改SQL中的默认值
t_default="''"
endif
CASE c_sql_type="L"
if empty(t_default)
SQLEXEC(1,"alter table "+ctable+" add default 0 for "+t_field) &&修改SQL中的默认值
t_default='.f.'
else
t_default=iif(t_default='0','.f.','.t.')
endif
CASE c_sql_type="B"
if empty(t_default)
SQLEXEC(1,"alter table "+ctable+" add default 0 for "+t_field) &&修改SQL中的默认值
t_default="0"
endif
DBSETPROP(v_field,'Field','DataType','N(13,4)')
CASE c_sql_type="I"
if empty(t_default)
SQLEXEC(1,"alter table "+ctable+" add default 0 for "+t_field) &&修改SQL中的默认值
t_default="0"
endif
DBSETPROP(v_field,'Field','DataType','N(8)')
CASE c_sql_type="T"
DBSETPROP(v_field,'Field','DataType','D')
REPLACE ALL &t_field WITH null FOR year(&t_field)<1911
ENDCASE
IF C_sql_type$"NMLBI"
DBSetProp(v_field, 'Field', 'DefaultValue', "(&t_default)")
REPLACE ALL &t_field WITH &t_default FOR ISNULL(&t_field)
ENDIF
SELECT sql_stru
SKIP
ENDDO
SELECT sql_stru
USE
SELECT &cview
if tableupdate(1,.t.)=.f.
WAIT WIND cview+"更新未成功"
TABLEUPDATE(1,.T.)
endif
USE
RETURN
表字段有：tablename ,viewname ,default_where ,orderby四个字段为字符兴 |
|