创建视图详解编程语言

CREATE VIEW v_total_asset 
    (cid,fin,wallet) 
AS  
SELECT c.cid,nvl(( 
    SELECT SUM(f.total_num*nvl( 
                        CASE  
                           WHEN f.prof_mod='1' THEN 1 
                           WHEN f.prof_mod='0' THEN f.prod_net 
                           ELSE 1 
                        END 
                    ,1)) FROM t_fin_held f WHERE f.cid = c.cid  
 ),0) AS fin,nvl(( 
    SELECT SUM(w.total_amt) FROM t_wallet_balance w WHERE w.cid = c.cid AND w.fund_state = '0' 
),0) AS wallet 
     FROM  t_customer cc 
      
 
 
CREATE VIEW v_mgr_cust_asset 
    (id,mgr_id,cid,cust_name,risk_level,birthday,phone_number,mark_prop,priority,check_status,mc_desc,fin,wallet,total_asset) 
AS  
SELECT m.id,m.mgr_id,m.cid,c.cust_name,c.risk_level,c.birthday,c.phone_number,m.mark_prop,nvl(m.priority,0) AS priority,m.check_status,m.mc_desc,a.fin,a.wallet,(a.fin+a.wallet) AS total_asset 
 FROM t_mgr_cust m  
    LEFT JOIN v_total_asset a ON a.cid = m.cid 
    LEFT JOIN t_customer c ON c.cid = m.cid