帆软使用oracle序列,帆软报表调用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;