oracle 添加输出数据集的函数

来源：互联网发布：大数据用英文怎么说编辑：程序博客网时间：2024/05/22 17:40

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_tableIS 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;

0 0