Make a select according to an indexed table

Asked

Viewed 87 times

1

I have a table, getSalas, that simplified is 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

Which in the context of my problem means that the room with the ID 1 has 2 fire extinguishers and 1 air conditioner, and the room with the ID has 2 air conditioners. 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-conditioning and a fire extinguisher, for that I 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 character 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 to the room with the ID 1 and the room with the ID 2, because he is making a OR, that is, if the room has A/C or a fire extinguisher returns this room as well, but I wanted it to make a And, if the room has A/C and a fire extinguisher returns this room too.

Note 1:

I created a procedure, because this research 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, swimming pool and A/C. With money anything is possible ;)

Note 2:

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

Name of character, value

Name of character, value

Character name, value etc...

If I want a room with 2 unicorns and a pool character will be:

'Unicorn', 2

'Pool', 2

Note 2:

If possible I would like to find a solution that would only mess with the procedure, it would be a little complicated to go back to the tables, but present your suggestions whatever they are!

sql oracle pl-sql stored-procedures

  • I removed my answer by not answering your problem the way you want. I will think of a solution as soon as I have a little more time. Interesting question. +1

  • Thanks for your help @Marllonnasser, come back anytime :)

  • From what I understood I would do otherwise , assemble a dynamic sql (care with Injection) with the conditions to be searched, seems to me simpler.

  • I’m sorry I don’t understand, @Motta

1 answer

0

If you want to fetch the array

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

You mount a sql like this

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

Browser other questions tagged sql oracle pl-sql stored-procedures

You are not signed in. Login or sign up in order to post.