cast函式的用法案例

最近一開發人員在建立物化檢視的時候拋瞭如下的異常"ORA-01723: 不允許長度為 0 的列".語句如下:

create materialized view v_service
as
select '2' as SERVSOURCE,v1.companycode as icpcode,v1.CompanyName as spname,v1.ShortName as spshortname,'' as SERVIDALIAS,v2.ServiceCode as icpservid,v2.ProductName as servname,
v2.ProductStatus as servstatus,'' as ServAttr,'' as umflag,v2.ServiceType as servtype,'' as UsageDesc,'' as wwwurl,'' as introURL,v2.ChargeType as ChargeType,
v2.Fee as Price,v2.ChargeDesc,'' as StartTime,'' as EndTime,v2.Description as servdesc,
'' as csrtel,'' as spurl,ACCESSMODEID,null as FREEUSETYPE,'' as OFFLINEDESC,null as OFFLINESTATE,v2.OfflineDate as OFFLINETIME,
null as FREEUSECOUNT,-1 as ONDEMANDFLAG,null as SERVICELOGO,null as GROUPTYPE,'' as SERVGROUPID,'' as TYPE,null as SPECORDERFLAG,
'' as SPECORDERURL,'' as brand,'' as demoURL,-1 as SERVCATID,'' as freeurl,'1' as paymode,'' as linkurl,'' as linkservid,'1' as linkservsource,v1.companycode||'_'||v2.ServiceCode as pksid,v2.LUPDDate
from V_OM_Company v1,V_OM_PRODUCT v2
where v1.CompanyID = v2.ProviderCOID and v2.ProductStatus <> '1' and ACCESSMODEID = '10';

可以看到用了很多null as 和'' as從而導致出現列長度為0.

修改後的語句用到了函式cast,該函式的格式為cast(exp as ),於是建立該mv可改為如下形式:

create materialized view v_service
as
select '2' as SERVSOURCE,v1.companycode as icpcode,v1.CompanyName as spname,v1.ShortName as spshortname,cast(null as varchar2(10)) as SERVIDALIAS,v2.ServiceCode as icpservid,v2.ProductName as servname,
v2.ProductStatus as servstatus,cast(null as varchar2(10)) as ServAttr,cast(null as varchar2(10)) as umflag,v2.ServiceType as servtype,cast(null as varchar2(10)) as UsageDesc,cast(null as varchar2(10)) as wwwurl,cast(null as varchar2(10)) as introURL,v2.ChargeType as ChargeType,
v2.Fee as Price,v2.ChargeDesc,cast(null as varchar2(10)) as StartTime,cast(null as varchar2(10)) as EndTime,v2.Description as servdesc,
cast(null as varchar2(10)) as csrtel,cast(null as varchar2(10)) as spurl,ACCESSMODEID,cast(null as varchar2(10)) as FREEUSETYPE,cast(null as varchar2(10)) as OFFLINEDESC,cast(null as varchar2(10)) as OFFLINESTATE,v2.OfflineDate as OFFLINETIME,
cast(null as varchar2(10)) as FREEUSECOUNT,-1 as ONDEMANDFLAG,cast(null as varchar2(10)) as SERVICELOGO,cast(null as varchar2(10)) as GROUPTYPE,cast(null as varchar2(10)) as SERVGROUPID,cast(null as varchar2(10)) as TYPE,cast(null as varchar2(10)) as SPECORDERFLAG,
cast(null as varchar2(10)) as SPECORDERURL,cast(null as varchar2(10)) as brand,cast(null as varchar2(10)) as demoURL,-1 as SERVCATID,cast(null as varchar2(10)) as freeurl,'1' as paymode,cast(null as varchar2(10)) as linkurl,cast(null as varchar2(10)) as linkservid,'1' as linkservsource,v1.companycode||'_'||v2.ServiceCode as pksid,v2.LUPDDate
from V_OM_Company v1,V_OM_PRODUCT v2
where v1.CompanyID = v2.ProviderCOID and v2.ProductStatus <> '1' and ACCESSMODEID = '10';

建立成功!

[@more@]