Make a select according to an indexed table

Question

Make a select according to an indexed table

Navigation

#1 by (0 votes)

1

I have a table, getSalas , which simplified something like this:

CREATE TABLE getSalas(
    ID_SALA NUMBER(4),
    NOME_CARATERISTICA VARCHAR2(20),
    VALOR_CARATERISTICA NUMBER(4)
);

The contents of this table are of the type:

1, 'Fire extinguisher', 2

1, 'A / C', 1

2, 'A / C', 2

What in the context of my problem means that the room with the ID 1 has 2 extinguishers and 1 Air conditioner, and the room with the ID has 2 Air -conditioned. I also created an indexed table of a data type that I created:

CREATE OR REPLACE TYPE c_valor FORCE IS OBJECT (CARATERISTICA VARCHAR2(20), VALOR NUMBER(30));
/
CREATE OR REPLACE TYPE tabc_valor FORCE IS TABLE OF c_valor;
/

What I need to know is if, for example , a room has at least one air conditioner and a fire extinguisher, for this I have created the following procedure:

CREATE OR REPLACE PROCEDURE getSalas_Carateristicas
(v_carateristicas IN tabc_valor, lista OUT SYS_REFCURSOR)
IS BEGIN
    OPEN lista FOR SELECT getSalas.ID_SALA
    FROM getSalas, TABLE(v_carateristicas) v_carateristicas
    WHERE getSalas.NOME_CARATERISTICA = v_carateristicas.CARATERISTICA
    AND getSalas.VALOR >= v_carateristicas.VALOR
    GROUP BY getSalas.ID_SALA
    ORDER BY getSalas.ID_SALA;
END;
/

And in v_carateristics I have the values, in this case it would be something like

'A / C', 1

'Fire extinguisher', 1

The problem with this procedure is that it returns the room with the ID 1 and the room with the ID 2 because it is making an OR , that is, if the room has an A / C or a fire extinguisher returns this room too, but I wanted it to do a E , if the room has A / C and a fire extinguisher returns this room too.

Note 1:

I created a procedure because this search is dynamic, that is, now I want a room with A / C and a fire extinguisher, then I want a room with 2 unicorns and then a room with 3 unicorns, pool and A /W. With money everything is possible;)

Note 2:

The procedure parameter, v_carateristics , is an indexed table with the following structure:

Character name, value

Character name, value

Character name, value etc ...

If I want a room with 2 unicorns and a pool, the v_carateristicas will be:

'Unicorn', 2

'Pool', 2

Note 2:

If possible I would like to find a solution that would only work with the procedure, it would be a bit complicated to go back to the tables, however, please submit your suggestions whatever they are!

sql oracle stored-procedures pl-sql

asked by anonymous 26.01.2017 / 14:04

1 answer

View Directory Image How to add this header to SOAP?

score 0 · Answer 1

If you want to search O ARRAY

 'A/C', 1
 'Extintor', 1

Mount to sql so

DECLARE 
   vs_sql varchar2(4000):
 Begin
   --SUPONDO UM ARRAY   
   vs_sql :=           'SELECT getSalas.ID_SALA';
   vs_sql := vs_sql || 'FROM getSalas WHERE 1=1';
   FOR I IN -- LOOP DO ARRAY
   LOOP
     vs_sql := vs_sql || 'and getSalas.NOME_CARATERISTICA =' || ARRAY(I,1);
     vs_sql := vs_sql || 'AND getSalas.VALOR >=' || ARRAY(I,2) ;
   END LOOP;
   open cursor for vs_sql;
 End;

Just a rough draft of the idea