帆软报表调用oracle包

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

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;