oracle 添加输出数据集的函数

1.添加行类型

CREATE OR REPLACE TYPE CMTOOLS.gps_data_row as object
(
  equip_id char(36),
  gps_date date,
  acc_status number,
  equip_no varchar2(32),
  equip_name varchar2(32),
  equip_category varchar2(32),
  equip_model varchar2(32)
)

 

2.添加表类型

CREATE OR REPLACE TYPE CMTOOLS.gps_data_table is table of gps_data_row;

 

3.添加函数

CREATE OR REPLACE FUNCTION CMTOOLS.FUN_WORKHOUR_DT(equipid varchar2,begindate date,enddate date)
RETURN gps_data_table
IS 
rs gps_data_table := gps_data_table();
v_acc_status number;
v_equipNo varchar2(32);
v_equipName varchar2(32);
v_categoryName varchar2(32);
v_equipModel varchar2(32);

BEGIN    
    v_acc_status := 1;
    SELECT e.equip_no,e.equip_name,ec.category_name,e.equip_model 
    INTO v_equipNo,v_equipName,v_categoryName,v_equipModel
    FROM equip_equipment e
    LEFT JOIN equip_category ec ON e.equip_category=ec.category_id
    WHERE e.equip_id=equipid;
    
    FOR myrow IN (    
        SELECT t.equip_id,t.gps_date,t.acc_status
        FROM GPS_MONITOR_DATA t
        WHERE t.gps_date BETWEEN begindate AND enddate
        AND t.equip_id=equipid ORDER BY t.gps_date) 
    LOOP
        IF v_acc_status <> myrow.acc_status THEN
        BEGIN
            rs.EXTEND;
            rs(rs.count) := gps_data_row(myrow.equip_id,myrow.gps_date,myrow.acc_status,v_equipNo,v_equipName,v_categoryName,v_equipModel);            
        END;
        END IF;
        v_acc_status := myrow.acc_status;
    END LOOP;        
    RETURN rs;
    
END FUN_WORKHOUR_DT;