Oracle存储过程中cursor + with用法
公用表表达式(Common Table Expression)与游标混合应用例子:
Oracle存储过程中cursor + with用法
-- P_PO_TYPE_CODE: 采购单据类型,如标准采购订单、一揽子采购协议
-- P_ITEM_CODE : 物料号
-- P_CURSOR1: 输出游标类型资料 CREATE OR REPLACE PROCEDURE CUX_TEST_CUR_RV( P_PO_TYPE_CODE IN VARCHAR2, P_ITEM_CODE IN VARCHAR2, P_CURSOR1 OUT SYS_REFCURSOR) AS BEGIN OPEN P_CURSOR1 FOR WITH CTE AS ( SELECT PV.VENDOR_NAME, PV.VENDOR_CODE, PHA.PO_NUMBER,PLA.UNIT_PRICE, PHA.CREATION_DATE FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA, PO_VENDORS PV WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID AND PHA.VENDOR_ID = PV.VENDOR_ID 。。。。。 ), DPT_INFO AS ( SELECT ..... ) SELECT CTE.VENDOR_NAME, CTE.VENDOR_CODE, CTE.PO_NUMBER,CTE.UNIT_PRICE FROM CTE , DPT_INFO DI WHERE CTE.PO_NUMBER = DI.PO_NUMBER ..... END; -- 测试 游标 declare R1 sys_refcursor; V_VENDOR_NAME VARCHAR2(250); V_VENDOR_CODE VARCHAR2(150); V_PO_NUMBER VARCHAR2(150); V_UNIT_PRICE NUMBER; begin
CUX_TEST_CUR_RV( 'BLANKET','GL0116', R1); loop fetch R1 into V_VENDOR_NAME, V_VENDOR_CODE, V_PO_NUMBER,V_UNIT_PRICE; exit when R1%notfound; DBMS_OUTPUT.PUT_LINE('VENDOR_NAME,PO_NUMBER,UNIT_PRICE:'|| V_VENDOR_NAME|| ','||V_PO_NUMBER|| ','||TO_CHAR( V_UNIT_PRICE, 'fm9999999999999990.00')); end loop; close R1; end;
优质生活从拆开始