Can I use the return of a stored procedure as part of the where clause?

Navigation
0

The idea is that the stored procedure returns a set of identifiers. Ex. pra_id:

  • 303
  • 404

Currently this is what I am trying to do, and it is to use the return of the pra_id as filters: 

SELECT rea.rea_id, rea.pra_id, etr.etr_id, etr.etr_codigo
    FROM tsga_registro_academico rea
   INNER JOIN tsga_estado_registro_acad etr
      ON rea.etr_id = etr.etr_id
   WHERE rea.ent_id = en_ent_id
     AND rea.pra_id = en_pra_id
     AND rea.pac_id = en_pac_id
     AND rea.coe_id = en_coe_id
     AND rea.pra_id IN
         (PKG_SGA_EQUIVAL_PROGRA_DETALLE.PRC_LIST_OBT_PRA_EQU(en_pra_id))

I share how the entries and exits of the stored procedure are declared. 

      *  %param en_pra_id     Identificador del programa acad?mico a consultar sus programas academicos equivalentes
  *  %param sq_resultados cursor de referencia que almacena los resultados de la consulta
  ************************************************************************/
  PROCEDURE PRC_LIST_OBT_PRA_EQU(en_pra_id     IN NUMBER,
                                 sq_resultados OUT SYS_REFCURSOR);
    
asked by Pit 13.10.2017 в 00:02
source

1 answer

0

Procedures do not return a value.

The functions yes. And you can use a function in the where clause, although it will make the query heavier.

When a function is declared, it specifies which value it returns (which is unique), and that value is the one that can be used in a query by invoking the function. 

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]

   RETURN return_datatype    <------  Aca se declara el tipo de dato que devuelve

IS | AS

   [declaration_section]

BEGIN
   executable_section

[EXCEPTION
   exception_section]

END [function_name];

On some side of the body, it returns the value with RETURN <valor> .

    
answered by 13.10.2017 в 06:07
Query that works in SQL and not in PHP How to export workbench database to phpmyadmin?