帆软报表调用oracle包

wushenghfut 2021-08-06 原文

帆软报表调用包，返回的数组需要放到动态游标

1. 首先定义两个类型对象

CREATE OR REPLACE TYPE t_shipment_print as object(shipment_id NUMBER,)；

CREATE OR REPLACE TYPE t_shipment_prints IS TABLE OF t_shipment_print

2. 定义包

CREATE OR REPLACE PACKAGE fly_print_pkg IS
TYPE g_result IS REF CURSOR;
PROCEDURE get_shipment(x_result OUT g_result,
cumtomer_po_number IN VARCHAR2);

END fly_print_pkg;

定义包体

CREATE OR REPLACE PACKAGE BODY fly_print_pkg IS
PROCEDURE get_shipment(x_result           OUT g_result,
                         po_number IN VARCHAR2) IS
    l_seq NUMBER;
    v_shipments_table t_shipment_prints := t_shipment_prints();
    v_shipments       t_shipment_print := t_shipment_print(null);
    —解料数据
    CURSOR cur_shipment_tbl(po_number_temp VARCHAR2) IS
      SELECT
             MIN(ooos.shipment_id) AS shipment_id
        FROM shipments ooos;

BEGIN
    IF po_number is null THEN
      RETURN;
    END IF;
    l_seq := 1;
    –检查变量
    FOR c IN cur_shipment_tbl(po_number) LOOP
      FOR cur_shipment IN (SELECT ooos.shipment_id
                             FROM shipments ooos
                            WHERE ooos.shipment_id = c.shipment_id) loop
        v_shipments_table.extend;
        v_shipments_table(l_seq) := v_shipments;
        l_seq := l_seq + 1;
        v_shipments.shipment_id := cur_shipment.shipment_id;
      END LOOP;
    END LOOP;
     dbms_output.put_line(v_shipments.shipment_id);
    —返回游标值
    OPEN x_result FOR
      SELECT * FROM table(v_shipments_table);
END get_shipment;
END fly_print_pkg;