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 
) 


The create or replace type v_table_ld_tb is table of v_customize_ld 



The 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; - a single data variables 
The rs v_table_ld_tb: v_table_ld_tb=(); Definition - returns the result and the initialization 

V_lh varchar2 (100); - road no. 

V_glbm varchar2 (500); 
V_dlmc varchar2 (500); 
V_min_gls varchar2 (100); 
V_max_gls varchar2 (100); 
V_sgqs number; 
The begin 
For obj (the select distinct lh in the from dm_gjj_jjr_ld_tmp where day_id in (
The select rq from dim_bas_jjrwdmx where year & gt;=v_ks_year and year & lt;=v_js_year and JJRMC=v_jjrmc and PM not in (' 0 ', '99'))) loop 
V_lh:=obj. Lh; 

For ld_data in (select - won consecutive points and 
FUN_GETDISTINCT_GLBM (GLBM) as GLBM, 
Lh, DLMC, flag, min_gls max_gls, sum (SGQS) as SGQS 
The from 
(select listagg (GLBM, ', ') within group (order by flag) as GLBM, 
Lh, DLMC, flag, min_gls max_gls, sum (SGQS) as SGQS 
The from 
(select GLBM, lh, DLMC, flag, min_gls, max_gls, sum (SGQS) as SGQS from (select - maximum minimum mileage 
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 
The 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 on al-qeada LBM b=b.g LBM 
Where day_id in (
The select rq from dim_bas_jjrwdmx where year & gt;=v_ks_year and year & lt;=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))) - each point and 
Group by GLBM, lh, DLMC, flag, min_gls, max_gls 
The order by flag) group by lh, flag, min_gls, max_gls DLMC) 
Group by FUN_GETDISTINCT_GLBM (GLBM), 
Lh, flag, min_gls max_gls, loop DLMC) 

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. The extend; 
Rs (rs. Count) :=v_row; 

End loop; 
End loop; 
Return (rs); 
End getld; 

CodePudding user response:

SQL should be no problem, right? Because I will plug in parameters, there is no question of executing SQL

CodePudding user response:

The 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
)
The less a ', ', type compiles but there is an error, is invalid