17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure P_Func_GetIDList
(
IDFlag in out varchar2 --ID类型:HEAD,COLUMN
,TableHead in out varchar2 --表头
,varType in out varchar2 --类型
,csr_list in out sys_refcursor --返回游标集合
)
is
IDGroup varchar2(2000);
--v_row temp_headlist%rowtype;
begin
if (IDFlag='HEAD') then
goto lbl_HEAD;
end if;
if (IDFlag='COLUMN') then
goto lbl_COLUMN;
end if;
return;
<<lbl_HEAD>>
select ID_Group into IDGroup from TBL_Config_PageHeadList where Table_Head=TableHead and OBJ_Type=varType;
p_global_headlist(head_id_group => IDGroup,
csr_headlist => csr_list);
/*
loop
begin
fetch csr_list into v_row;
exit when csr_list%notfound;
dbms_output.put_line(v_row.HEAD_NAME);
dbms_output.put_line(v_row.HEAD_ALIAS_NAME);
end;
end loop;
*/
return;
<<lbl_COLUMN>>
select ID_Group into IDGroup from TBL_Config_PageColumnList where Table_Head=TableHead and OBJ_Type=varType;
p_global_columnlist(column_id_group => IDGroup,
csr_idlist => csr_list);
return;
end P_Func_GetIDList;
create or replace procedure P_Global_HeadList
(
Head_ID_Group in out varchar2, --指标集
csr_HeadList in out sys_refcursor --返回结果集
)
is
StartIndex NUMBER;
EndIndex NUMBER;
IDValue NUMBER;
begin
if (Head_ID_Group is null) then
return;
end if;
StartIndex :=1;
EndIndex :=0;
if(substr(Head_ID_Group,-1,1) <> ',') then
Head_ID_Group := Head_ID_Group || ',';
end if;
EndIndex := INSTR(Head_ID_Group,',');
commit;
while(EndIndex >0) LOOP
begin
IDValue := to_number(substr(Head_ID_Group,StartIndex,EndIndex-StartIndex));
execute immediate 'Insert into Temp_HEADLIST(HEAD_ID,TABLE_HEAD,HEAD_NAME,HEAD_ALIAS_NAME) select HEAD_ID,TABLE_HEAD,HEAD_NAME,HEAD_ALIAS_NAME from TBL_Config_HeadList where HEAD_ID=' || IDValue;
StartIndex := EndIndex + 1;
EndIndex := INSTR(Head_ID_Group,',',StartIndex+1);
end;
end loop;
open csr_HeadList for select * from Temp_HEADLIST;
end P_Global_HeadList;