视图学习之建立视图、授权、同义词创建

--新建视图
create or replace view OWNERNAME.SYSTEMNAME_SENDNOTICE_INFO_PV as 
select CREATED_BY,           
  CREATED_DATE,       
  UPDATED_BY,         
  UPDATED_DATE,       
  ID_SENDNOTICE_INFO, 
  REPORT_NO,          
  CASE_NO,            
  CASE_TIMES,         
  POLICY_NO,          
  LAST_SEND_DATE,     
  NOTE_SEND_TYPE,     
  SEND_TIMES,         
  NOTICE_TYPE,        
  substr(MOBILE,1,length(trim(MOBILE))-6)||'123456' MOBILE,                      ---手机号码        
  substr(EMAIL,1,instr(EMAIL,'@')-1)||'@mail.com.cn'  EMAIL,                        ----邮箱地址            
  SEND_CONTENT       
from OWNERNAME.SYSTEMNAME_SENDNOTICE_INFO;


--创建视图同义词
CREATE  PUBLIC SYNONYM SYSTEMNAME_SENDNOTICE_INFO_PV FOR OWNERNAME.SYSTEMNAME_SENDNOTICE_INFO_PV;



--收回原有表的授权
revoke select on OWNERNAME.SYSTEMNAME_SENDNOTICE_INFO from R_OWNERNAME_DEV_QRY; 



--视图授权
grant select on OWNERNAME.SYSTEMNAME_SENDNOTICE_INFO_PV to R_OWNNERNAME_QRY,R_OWNNERNAME_DEV_QRY;