create or replace type v_customize_ld as object
(
glbm varchar2(500),
lh varchar2(100)
dlmc varchar2(400),
min_gls varchar2(100),
max_gls varchar2(100),
sgqs number
)
create or replace type v_table_ld_tb is table of v_customize_ld
create or replace function getld (v_ks_year in varchar2,v_js_year in varchar2,v_jjrmc in varchar2)
return v_table_ld_tb is
v_row v_customize_ld; --定義單條資料變數
rs v_table_ld_tb:=v_table_ld_tb(); --定義回傳結果并初始化
v_lh varchar2(100); --路號
v_glbm varchar2(500);
v_dlmc varchar2(500);
v_min_gls varchar2(100);
v_max_gls varchar2(100);
v_sgqs number;
begin
for obj in (select distinct lh from dm_gjj_jjr_ld_tmp where day_id in (
select rq from dim_bas_jjrwdmx where year >= v_ks_year and year <= v_js_year and jjrmc = v_jjrmc and pm not in ('0','99')) ) loop
v_lh :=obj.lh;
for ld_data in (select -- 獲得了連續點段的和
FUN_GETDISTINCT_GLBM(glbm) as glbm,
lh,dlmc,flag,min_gls,max_gls,sum(sgqs) as sgqs
from
(select listagg(glbm,',')within group (order by flag) as glbm,
lh,dlmc,flag,min_gls,max_gls,sum(sgqs) as sgqs
from
(select glbm,lh,dlmc,flag,min_gls,max_gls,sum(sgqs) as sgqs from (select -- 獲得最大最小公里數
glbm,
lh,
dlmc,
gls,
sgqs,
flag,
min(gls) keep(dense_rank FIRST order by gls) over (partition by flag) as min_gls,
max(gls) keep(dense_rank LAST order by gls) over (partition by flag) as max_gls
from (select glbm,lh,gls,dlmc,sgqs,dense_rank() over(order by gls - rownum) as flag from (select listagg(glbm,',')within group (order by gls) as glbm,sum(sgqs) as sgqs,lh,gls,dlmc from (select xxlbmmc as glbm,lh,dlmc,gls,sum(sgqs) as sgqs from dm_gjj_jjr_ld_tmp a left join dw_dim_glbm b on a.glbm = b.glbm
where day_id in (
select rq from dim_bas_jjrwdmx where year >= v_ks_year and year <= v_js_year and jjrmc = v_jjrmc and pm not in ('0','99'))
and lh = v_lh
group by xxlbmmc,lh,gls,dlmc
)group by lh,gls,dlmc))) -- 獲得各個點段和
group by glbm,lh,dlmc,flag,min_gls,max_gls
order by flag) group by lh,flag,min_gls,max_gls,dlmc)
group by FUN_GETDISTINCT_GLBM(glbm),
lh,flag,min_gls,max_gls,dlmc) loop
v_glbm := ld_data.glbm;
v_dlmc := ld_data.dlmc;
v_max_gls := ld_data.max_gls;
v_min_gls := ld_data.min_gls;
v_sgqs :=ld_data.sgqs;
v_row := v_customize_ld(v_glbm,v_lh,v_dlmc,v_min_gls,v_max_gls,v_sgqs);
rs.extend;
rs(rs.count):=v_row;
end loop;
end loop;
return (rs);
end getld;
uj5u.com熱心網友回復：
sql應該是沒有問題的吧？因為我將引數代入，執行sql是沒有問題的uj5u.com熱心網友回復：
create or replace type v_customize_ld as object(
glbm varchar2(500),
lh varchar2(100)
dlmc varchar2(400),
min_gls varchar2(100),
max_gls varchar2(100),
sgqs number
)
這個少了一個‘，’,type編譯完成但有錯誤，報無效
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/21225.html
標籤：開發
上一篇：Oracle注冊
下一篇：一個日期查詢的問題