Oracle 中 table 函数的应用浅析
表函数可接受查询语句或游标作为输入参数，并可输出多行数据。该函数可以平行执行，并可持续输出数据流，被称作管道式输出。应用表函数可将数据转换分阶段处理，并省去中间结果的存储和缓冲表。
1.用游标传递数据
利用游标REFCURSOR可将数据集（多行记录）传递到PL/SQL函数：
SELECT* FROMTABLE(myfunction(CURSOR(SELECT* FROMmytab)));
2.利用两个实体化视图（或表）作为样板数据
CREATEMATERIALIZEDVIEWsum_sales_country_mv
BUILDIMMEDIATE
REFRESHCOMPLETE
ENABLEQUERYREWRITE
AS
SELECTSUBSTR(s.calendar_month_desc,1,4)YEAR,c.country_idcountry,
SUM(sum_amount_sold)sum_amount_sold
FROMsum_sales_month_mvs,customersc
WHEREs.cust_id=c.cust_id
ANDc.country_idIN('US','UK','FR','ES','JP','AU')
GROUPBYSUBSTR(s.calendar_month_desc,1,4),c.country_id
CREATEMATERIALIZEDVIEWsum_es_gend_mv BUILDDEFERRED REFRESHFAST ENABLEQUERYREWRITE AS SELECTSUBSTR(s.calendar_month_desc,1,4)YEAR, s.calendar_month_desccal_month,c.cust_gender, SUM(sum_amount_sold)sum_amount_sold FROMsum_sales_month_mvs,customerc WHEREs.cust_id=c.cust_id ANDc.country_id='ES' ANDsunstr(s.calendar_month_desc,1,4)='2000' GROUPBYSUBSTR(s.calendar_month_desc,1,4), s.calendar_month_desc, c.cust_gender;
3.定义对象类型和基于对象类型的表类型
定义对象类型并且为进一步引用做好准备。
（1）定义对象类型：TYPEsales_country_t
CREATEMATERIALIZEDVIEWsum_es_gend_mv BUILDDEFERRED REFRESHFAST ENABLEQUERYREWRITE AS SELECTSUBSTR(s.calendar_month_desc,1,4)YEAR, s.calendar_month_desccal_month,c.cust_gender, SUM(sum_amount_sold)sum_amount_sold FROMsum_sales_month_mvs,customerc WHEREs.cust_id=c.cust_id ANDc.country_id='ES' ANDsunstr(s.calendar_month_desc,1,4)='2000' GROUPBYSUBSTR(s.calendar_month_desc,1,4), s.calendar_month_desc, c.cust_gender;
（2）定义表类型：TYPESUM_SALES_COUNTRY_T_TAB
CREATETYPEsum_sales_country_t_tabASTABLEOFsales_country_t;
（3）定义对象类型：TYPEsales_gender_t
CREATETYPEsales_gender_tASOBJECT( YEARVARCHAR2(4), country_idCHAR(2), cust_genderCHAR(1), sum_amount_soldNUMBER );
（4）定义表类型：TYPESUM_SALES_GENDER_T_TAB
CREATETYPEsum_sales_gender_t_tabASTABLEOFsales_gender_t;
（5）定义对象类型：TYPEsales_roll_t
CREATETYPEsales_roll_tASOBJECT( channel_descVARCHAR2(20), country_idCHAR(2), sum_amount_soldNUMBER );
（6）定义表类型：TYPESUM_SALES_ROLL_T_TAB
CREATETYPEsum_sales_roll_t_tabASTABLEOFsales_roll_t;
（7）检查一下建立的类型
SELECTobject_name,object_type,status FROMuser_objects WHEREobject_type='TYPE';
4.定义包：CreatepackageanddefineREFCURSOR
CREATEORREPLACEPACKAGEcursor_pkg ITYPEsales_country_t_recISRECORD( YEARVARCHAR(4), countryCHAR(2), sum_amount_soldNUMBER ); TYPEsales_gender_t_recISRECORD( YEARVARCHAR2(4), country_idCHAR(2), cust_genderCHAR(1), sum_amount_soldNUMBER ); TYPEsales_roll_t_recISRECORD( channel_descVARCHAR2(20), country_idCHAR(2), sum_amount_soldNUMBER ); TYPEsales_country_t_rectabISTABLEOFsales_country_t_rec; TYPEsales_roll_t_rectabISTABLEOFsales_roll_t_rec; TYPEstrong_refcur_tISREFCURSOR RETURNsales_country_t_rec; TYPErow_refcur_tISREFCURSOR RETURNsum_sales_country_mv%ROWTYPE; TYPEroll_refcur_tISREFCURSOR RETURNsales_roll_t_rec; TYPErefcur_tISREFCURSOR; ENDcorsor_pkg;
5.定义表函数
（1）定义表函数：FUNCTIONTable_Ref_Cur_Week
CREATEORREPLACEFUNCTIONtable_ref_cur_week(curCURSOR.refcur_t) RETURNsum_sales_country_t_tab IS YEARVARCHAR(4); countryCHAR(2); sum_amount_soldNUMBER; objsetsum_sales_country_t_tab:=sum_sales_country_t_tab(); iNUMBER:=0; BEGIN LOOP --Fetchfromcursorvariable FETCHcur INTOYEAR,country,sum_amount_sold; EXITWHENcur%NOTFOUND; --exitwhenlastrowisfetched --appendtocollection i:=i+1; objset.EXTEND; objset(i):=sales_country_t(YEAR,country,sum_amount_sold); ENDLOOP; CLOSEcur; RETURNobjset; END; /
（2）定义表函数：FUNCTIONTable_Ref_Cur_Strong
CREATEORREPLACEFUNCTIONtable_ref_cur_strong(curcursor_pkg.strong_refcur_t) RETURNsum_sales_country_t_tabPIPELINED IS YEARVARCHAR(4); countryCHAR(2); sum_amount_soldNUMBER; iNUMBER:=0; BEGIN LOOP FETCHcur INTOYEAR,country,sum_amount_sold; EXITWHENcur%NOTFOUND;--exitwhenlastrowfetched PIPEROW(sales_country_t(YEAR,country,sum_amount_sold)); ENDLOOP; CLOSEcur; RETURN; END; /
（3）定义表函数：FUNCTIONTable_Ref_Cur_row
CREATEORREPLACEFUNCTIONtable_ref_cur_row(curcursor_pkg.row_refcur_t) RETURNsum_sales_country_t_tabPIPELINED IS in_reccur%ROWTYPE; out_recsales_country_t:=sales_country_t(NULL,NULL,NULL); BEGIN LOOP FETCHcur INTOin_rec; EXITWHENcur%NOTFOUND;--exitwhenlastrowisfetched out_rec.YEAR:=in_rec.YEAR; out_rec.country:=in_rec.country; out_rec.sum_amount_sold:=in_rec.sum_amount_sold; PIPEROW(out_rec); ENDLOOP; CLOSEcur; RETURN; END; /
（4）定义表函数：FUNCTIONGender_Table_Ref_Cur_Week
CREATEORREPLACEFUNCTIONgender_table_ref_cur_week(curcursor_pkg.refcur_t) RETURNsum_sales_gender_t_tab IS YEARVARCHAR2(4); country_idCHAR(2); cust_genderCHAR(1); sum_amount_soldNUMBER; objsetsum_sales_gender_t_tab:=sum_sales_gender_t_tab(); iNUMBER:=0; BEGIN LOOP FETCHcur INTOYEAR,country_id,cust_gender,sum_amount_sold; EXITWHENcur%NOTFOUND;--exitwhenlastrowisfetched i:=i+1; objset.EXTEND; objset(i):= sum_sales_gender_t(YEAR,country_id,cust_gender,sum_amount_sold); ENDLOOP; CLOSEcur; RETURNobjset; END; /
6.调用表函数
下列SQL查询语句调用已被定义的表函数。
SELECT* FROMTABLE(table_ref_cur_week(CURSOR(SELECT* FROMsum_sales_country_mv))); SELECT* FROMTABLE(table_ref_cur_strong(CURSOR(SELECT* FROMsum_sales_country_mv))); SELECT* FROMTABLE(table_ref_cur_row(CURSOR(SELECT* FROMsum_sales_country_mv))); SELECT* FROMTABLE(table_ref_cur_week(CURSOR(SELECT* FROMsum_sales_country_mv WHEREcountry='AU')));
以上所述是小编给大家介绍的Oracle中table函数的应用浅析，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！