Oracle 表类型-表值函数-过程 -例子

-------------函数----------------

create or replace

function fun1(i_v Int) 
return GETCYCLEVALUE_OBJ_TB pipelined as  
v1 GETCYCLEVALUE_OBJ;  
begin  
for myrow in (select in_dept_id from RF_DICT_DEPT) loop  
v1 := GETCYCLEVALUE_OBJ(myrow.in_dept_id);  
pipe row (v1);  
end loop;  
return;  
end;  

 

--------------过程中表类型应用实例--------------------------

create or replace
PROCEDURE  "PR_SURVEY_COMPUTE" 
(
  V_IN_SURVEY_ID IN NUMBER  --
) AS 

num number;--判断临时表是否存在
--定义表变量
v_temptab PR_SURVEY_COMPUTE_OBJ_TB := PR_SURVEY_COMPUTE_OBJ_TB(); --返回内存表
BEGIN 
               FOR rec IN ( select c.IN_SURVEY_SUBJECT_GROUP_ID,a.IN_SURVEY_OBJECT_ID,a.IN_SURVEY_ITEM_ID,avg(a.DE_SCORE) as DE_SCORE,a.DE_SCALE as DE_SCALE
                          from RF_SURVEY_RESULT_DETAIL a left join RF_SURVEY_RELATION b on a.IN_SURVEY_SUBJECT_ID=b.IN_SURVEY_SUBJECT_ID 
                          left join RF_SURVEY_RELATE_TEMPLATE c on b.IN_SURVEY_RELATE_TEMPLATE_ID=c.IN_SURVEY_RELATE_TEMPLATE_ID 
                          where a.IN_SURVEY_ID=b.IN_SURVEY_ID and a.IN_SURVEY_ID=V_IN_SURVEY_ID and a.ST_STATE='2'
                                and a.DE_SCORE > 0 and (c.IN_TRIMMEAN_PARAM=0 or c.IN_TRIMMEAN_PARAM is null)
                          group by a.IN_SURVEY_OBJECT_ID,a.IN_SURVEY_ITEM_ID,c.IN_SURVEY_SUBJECT_GROUP_ID,a.DE_SCALE )  LOOP
                          
                  v_temptab.Extend; 
                  v_temptab(v_temptab.count) := PR_SURVEY_COMPUTE_OBJ(NULL,NULL,NULL,NULL,NULL,NULL); 
                  v_temptab(v_temptab.count).temp_id :=v_temptab.count;
                  v_temptab(v_temptab.count).in_subject_group_id := rec.IN_SURVEY_SUBJECT_GROUP_ID;
                  v_temptab(v_temptab.count).in_object_id := rec.IN_SURVEY_OBJECT_ID;  
                   v_temptab(v_temptab.count).in_survey_item_id := rec.IN_SURVEY_ITEM_ID;
                  v_temptab(v_temptab.count).de_avage := rec.DE_SCORE;
                  v_temptab(v_temptab.count).de_scale := rec.DE_SCALE;   
                  
                END LOOP;   
            

     
 
EXCEPTION
  WHEN OTHERS THEN
  --rollback to point_Start;
  ROLLBACK;--全部回滚
  RETURN;
END PR_SURVEY_COMPUTE;

----------SQL-------

select *    from table(v_temptab) t3