PL-SQL perform query using null filters or not

Question

PL-SQL perform query using null filters or not

Navigation

#1 by (-1 votes)

0

Is there any way I can concatenate to a select a query according to a condition? for example in I have a procedure in which I will receive N input parameters, the query I have it ready but since they are filters not all parameters will come with a value (a code is received, date from and date until), the problem is that in the select that I have so that I can bring the data I must put the values to all the parameters, my question is I can concatenate it by means of an IF if for example the code is full, I append to that select that value and execute ?, so that Have an idea, in java using stringQueryBuilder and the IF condition, can I add to the query using .append the condition of 'and date_from =?', is there any form in PL or do they have any suggestions?

here the query

SELECT ORDER_SCHEDULE_NO, S.SUPPLIER, S.SUP_NAME, TO_CHAR (ORDER_DATE, 'DD / MM / YYYY HH24: MI: SS'), TO_CHAR (ORDER_CREATE_DATE, 'DD / MM / YYYY HH24: MI: SS') FROM ORDER_SCHEDULE OS, SUPS S WHERE S.SUPPLIER =: SUPPLIER AND TRUNC (order_date) > = TO_DATE (: DATES, 'DD / MM / YYYY hh24: mi: ss') AND TRUNC (order_date) < = TO_DATE (: DATEHASTA, 'DD / MM / YYYY hh24: mi: ss') AND S.SUPPLIER = OS.SUPPLIER ORDER BY ORDER_DATE

oracle plsql

asked by Jdeveloper 03.03.2018 в 18:24

source

1 answer

My android app does not receive data How do I order a linked list in java?

score -1 · Answer 1

Yes, you can.

For this you can use the SYS_REFCURSOR which is a special type of cursor that you can execute from a VARCHAR2 (a string of characters we are going). The VARCHAR2 you can make it to your liking using conditional IF , as you indicate.

A practical example:

We create a procedure and declare our cursor variables and varchar2:

PRUEBA_CURSOR SYS_REFCURSOR;
SENTENCIA VARCHAR2(10000);

Then we are making the sentence to our liking:

SENTENCIA:= 'SELECT ejemplo FROM ejemploTabla WHERE a = 1';

--Si se cumple la condición siguiente, agregamos un AND al where
IF(condicionEjemplo) THEN
    SENTENCIA := SENTENCIA || 'AND b = 2';
END;

Finally, we open the cursor, which will execute the sentence:

OPEN PRUEBA_CURSOR FOR SENTENCIA;

It should be noted that you can return this cursor as OUT parameter in the procedure, or you can even run a loop for each record returned by the Select.