SQL创建视图与函数实践

create or replace view v_item as
select bt.type_id        typeid,
       bc.cgy_id         bigtypeid,
       bit.item_type_id  itemtypeid,
       bi.item_id        itemid,
       bc.cgy_name       bigtypename,
       bt.name           typename,
       bi.name           itemname,
       bc.cgy_code       bigtypecode,
       bi.code           itemcode,
       bt.type_url       typeurl,
       bit.analysis_dept analysisdept,
       bit.sample_dept sampledept,
       bit.report_dept reportdept,
       bt.reporturl,
       bi.resulturl,
       bit.if_sub_or_not issub,
       bit.if_mon_or_not ismon,
       bit.sort
  from t_lm_bas_type bt
  left join t_lm_bas_category bc on bt.big_type=bc.cgy_id
  left join t_lm_bas_item_type bit on bt.type_id = bit.type_id
  left join t_lm_bas_item bi on bit.item_id = bi.item_id;
  
  
  create or replace function F_STAND_LEAVEL_TREE2LIST(childcode in  varchar2) return  varchar2 is
  Result varchar2(500);
begin
  select
substr(replace(wmsys.wm_concat(stand_leavel_name),',','>>'),INSTR(replace(wmsys.wm_concat(stand_leavel_name),',','>>'),'>>')+2)