2011-11-2 游标和管道函数

2025-04-12 技术教程

http://www.itpub.net/thread-1499223-11-1.html

105楼

我创建了这张表并填入数据：

CREATETABLEplch_parts(partnumINTEGERPRIMARYKEY,partnameVARCHAR2(100)UNIQUE)/BEGININSERTINTOplch_partsVALUES(1,'Mouse');INSERTINTOplch_partsVALUES(100,'Keyboard');INSERTINTOplch_partsVALUES(500,'Monitor');COMMIT;END;/

我建立了如下的嵌套表类型和包说明：

CREATEORREPLACETYPEnumbers_tISTABLEOFNUMBER;/CREATEORREPLACEPACKAGEplch_pipelineISCURSORrefcur_cISSELECTlineFROMuser_source;TYPErefcur_tISREFCURSORRETURNrefcur_c%ROWTYPE;FUNCTIONdouble_values(datasetrefcur_t)RETURNnumbers_tPIPELINED;ENDplch_pipeline;/

下列的选项中哪些实现了包体，从而使得这个查询执行之后：

SELECT*FROMTABLE(plch_pipeline.double_values(CURSOR(SELECTlineFROMuser_sourceWHEREname='PLCH_PIPELINE'ANDtype='PACKAGE'ANDline<=3ORDERBYline)))/

这三行会显示出来：

246

(A)

CREATEORREPLACEPACKAGEBODYplch_pipelineISFUNCTIONdouble_values(datasetrefcur_t)RETURNnumbers_tPIPELINEDISl_numberNUMBER;BEGINLOOPFETCHdatasetINTOl_number;EXITWHENdataset%NOTFOUND;UPDATEplch_partsSETpartnum=partnum;PIPEROW(l_number*2);ENDLOOP;CLOSEdataset;RETURN;END;ENDplch_pipeline;/

SQL>SELECT*2FROMTABLE(plch_pipeline.double_values(3CURSOR(SELECTline4FROMuser_source5WHEREname='PLCH_PIPELINE'6ANDtype='PACKAGE'7ANDline<=38ORDERBYline)))9/SELECT*FROMTABLE(plch_pipeline.double_values(CURSOR(SELECTlineFROMuser_sourceWHEREname='PLCH_PIPELINE'ANDtype='PACKAGE'ANDline<=3ORDERBYline)))ORA-14551:无法在查询中执行DML操作ORA-06512:在"YOGA.PLCH_PIPELINE",line12SQL>

(B)

CREATEORREPLACEPACKAGEBODYplch_pipelineISFUNCTIONdouble_values(datasetrefcur_t)RETURNnumbers_tPIPELINEDISPRAGMAAUTONOMOUS_TRANSACTION;l_numberNUMBER;BEGINLOOPFETCHdatasetINTOl_number;EXITWHENdataset%NOTFOUND;UPDATEplch_partsSETpartnum=partnum;PIPEROW(l_number*2);ENDLOOP;CLOSEdataset;RETURN;END;ENDplch_pipeline;/

SQL>SELECT*2FROMTABLE(plch_pipeline.double_values(3CURSOR(SELECTline4FROMuser_source5WHEREname='PLCH_PIPELINE'6ANDtype='PACKAGE'7ANDline<=38ORDERBYline)))9/SELECT*FROMTABLE(plch_pipeline.double_values(CURSOR(SELECTlineFROMuser_sourceWHEREname='PLCH_PIPELINE'ANDtype='PACKAGE'ANDline<=3ORDERBYline)))ORA-06519:检测到活动的独立的事务处理,已经回退ORA-06512:在"YOGA.PLCH_PIPELINE",line15SQL>

(C)

SQL>SELECT*2FROMTABLE(plch_pipeline.double_values(3CURSOR(SELECTline4FROMuser_source5WHEREname='PLCH_PIPELINE'6ANDtype='PACKAGE'7ANDline<=38ORDERBYline)))9/SELECT*FROMTABLE(plch_pipeline.double_values(CURSOR(SELECTlineFROMuser_sourceWHEREname='PLCH_PIPELINE'ANDtype='PACKAGE'ANDline<=3ORDERBYline)))ORA-06519:检测到活动的独立的事务处理,已经回退ORA-06512:在"YOGA.PLCH_PIPELINE",line15SQL>

(D)

CREATEORREPLACEPACKAGEBODYplch_pipelineISFUNCTIONdouble_values(datasetrefcur_t)RETURNnumbers_tPIPELINEDISPRAGMAAUTONOMOUS_TRANSACTION;l_numberNUMBER;BEGINLOOPFETCHdatasetINTOl_number;EXITWHENdataset%NOTFOUND;UPDATEplch_partsSETpartnum=partnum;COMMIT;PIPEROW(l_number*2);ENDLOOP;CLOSEdataset;RETURN;END;ENDplch_pipeline;/

SQL>SELECT*2FROMTABLE(plch_pipeline.double_values(3CURSOR(SELECTline4FROMuser_source5WHEREname='PLCH_PIPELINE'6ANDtype='PACKAGE'7ANDline<=38ORDERBYline)))9/COLUMN_VALUE------------246SQL>

答案说明在109楼

2011-11-2答案D.A:在SQL中调用的函数不能有DML,除非是自治事务；B:虽然用了自治事务，但是在返回之前（PIPEROW就是返回一行数据）必须提交或回滚这个事务；C:虽然用了自治事务而且有COMMIT,但是位置不对，COMMIT放在循环外面，这样在返回（PIPEROW）之前还是没有提交。