EcalCondDB/sql/create_dcu_functions.sql

0001 /* the following commands provide a function that returns
0002    a table containing the required DCU data considered valid
0003    at a given time, without taking into account the EOV,
0004    since it is not properly set, by definition. */
0005
0006 /* create the types needed to define the result of the
0007    function */
0008 CREATE OR REPLACE TYPE T_DCU_SELECT AS OBJECT (
0009         LOGIC_ID INTEGER,
0010         IOV_ID   INTEGER,
0011         TIME     DATE,
0012         VALUE    FLOAT
0013 );
0014 /
0015
0016 CREATE OR REPLACE TYPE T_TABLE_DCU_SELECT AS TABLE OF T_DCU_SELECT;
0017 /
0018
0019 /* create the function
0020    usage: SELECT * FROM TABLE(DCU_SELECT(table, column, date)
0021    The date must be a string formatted as 'DD-MM-YYYY HH24:MI:SS' */
0022 CREATE OR REPLACE FUNCTION DCU_SELECT
0023 (
0024   tblname IN VARCHAR2,
0025   column IN VARCHAR2,
0026   time IN VARCHAR2)  RETURN T_TABLE_DCU_SELECT PIPELINED IS
0027
0028   TYPE      ref0 IS REF CURSOR;
0029
0030   sql_str   VARCHAR(1000);
0031   logic_id  INTEGER;
0032   channels  INTEGER;
0033   value     FLOAT;
0034   since     DATE;
0035   cur0      ref0;
0036
0037   V_RET     T_DCU_SELECT
0038          := T_DCU_SELECT(NULL, NULL, NULL, NULL);
0039
0040   BEGIN
0041     -- evaluate how many channels there are in the required table
0042     sql_str := 'SELECT COUNT(LOGIC_ID) FROM (SELECT DISTINCT LOGIC_ID FROM ' ||
0043         tblname || ')';
0044     EXECUTE IMMEDIATE sql_str INTO channels;
0045     -- get the value of the required field whose start of validity is lower
0046     -- than the required date (limit the number of days considered)
0047     OPEN cur0 FOR
0048         'SELECT LOGIC_ID, IOV_ID, TIME, VALUE FROM (SELECT LOGIC_ID, ' ||
0049         'MAX(D.IOV_ID) IOV_ID, MAX(SINCE) TIME, '
0050         || column || ' VALUE FROM ' || tblname ||
0051         ' D JOIN DCU_IOV R ON D.IOV_ID = R.IOV_ID WHERE' ||
0052         ' SINCE <= TO_DATE(:1, ''DD-MM-YYYY HH24:MI:SS'') ' ||
0053         ' AND SINCE >= (TO_DATE(:2, ''DD-MM-YYYY HH24:MI:SS'') - 7)' ||
0054         ' GROUP BY LOGIC_ID, ' || column ||
0055         ' ORDER BY TIME DESC) WHERE ROWNUM <= :3'
0056     USING time, time, channels;
0057     LOOP
0058       FETCH cur0 INTO V_RET.LOGIC_ID, V_RET.IOV_ID, V_RET.TIME, V_RET.VALUE;
0059       EXIT WHEN cur0%NOTFOUND;
0060       PIPE ROW(V_RET);
0061     END LOOP;
0062     CLOSE cur0;
0063     RETURN;
0064   END DCU_SELECT;
0065 /