CODEWITHSUNDEEP
sqloracle-database
Soon
Soon

Reputation: 501

Creating Oracle procedure returning multiple records with two arguments

Code1 below create three tables and code2 show 2 rows. Usually, how to make the code2 as a procedure which input two varchar (i.e.'20180101' , 20180106' at the last line of the code2), and ouput multiple records?

code1

CREATE TABLE equip_type  (  
  EQUIP_TYPE VARCHAR(15),    
  EQUIP VARCHAR(15)
  );
INSERT INTO equip_type  VALUES ('A','e1');
CREATE TABLE output_history (  
  EQUIP VARCHAR(15),     
  MODEL VARCHAR(15),     
  Data1 VARCHAR(15),        
  QUANTITY  NUMBER(10) 
  );
INSERT INTO output_history VALUES ('e1','m1','20180103',10);
INSERT INTO output_history VALUES ('e1','m1','20180106',20);
INSERT INTO output_history VALUES ('e1','m1','20180107',20);
CREATE TABLE time_history (  
  EQUIP VARCHAR(15),     
  MODEL VARCHAR(15),       
  Data2 VARCHAR(15),    
  time NUMBER(10)
  );
INSERT INTO time_history VALUES ('e1','m1','20180101',6);
INSERT INTO time_history VALUES ('e1','m1','20180105',5);
INSERT INTO time_history VALUES ('e1','m1','20180107',5);

code2

    SELECT
    o.equip,
    o.model,
    o.data1,
    o.quantity,
    t.data2,
    t.time,
    e.equip_type
FROM 
    output_history o
    INNER JOIN equip_type e ON e.equip = o.equip
    INNER JOIN time_history t ON t.equip = o.equip AND t.data2 <= o.data1
WHERE NOT EXISTS (
    SELECT 1 
    FROM time_history
    WHERE 
        equip = o.equip
        AND data2 <= o.data1
        AND data2 > t.data2
) AND o.data1 >= '20180101' AND o.data1 <= '20180106';

Upvotes: 0

Views: 48

Answers (1)

eaolson
eaolson

Reputation: 15094

You can do this by creating a type for the rowtype you want, another type for a nested table of the first type, and a function to return that table type.

But before you do that please, please, please rework your tables so you are not storing DATEs as VARCHAR2s.

create type mytype as object (
    equip       VARCHAR(15),
    model       VARCHAR(15),
    data1       VARCHAR(15),
    quantity    NUMBER(10),
    data2       VARCHAR(15),    
    time        NUMBER(10),
    equip_type  VARCHAR(15)
);
/

create type mytype_tt as table of mytype;
/

create or replace function myfun( p1 varchar2, p2 varchar2 ) return mytype_tt
as
    l_returnval     mytype_tt;
begin

    SELECT mytype(
        o.equip,
        o.model,
        o.data1,
        o.quantity,
        t.data2,
        t.time,
        e.equip_type )
    BULK COLLECT INTO l_returnval
    FROM 
        output_history o
        INNER JOIN equip_type e ON e.equip = o.equip
        INNER JOIN time_history t ON t.equip = o.equip AND t.data2 <= o.data1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM time_history
        WHERE 
            equip = o.equip
            AND data2 <= o.data1
            AND data2 > t.data2
    ) AND o.data1 >= p1 AND o.data1 <= p2; 

    return l_returnval;

end;
/

Upvotes: 1

Related Questions