CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5236

How to use SELECT request in stored procedure (Oracle)?

I use Oracle 12c. I know that my next question is not new but I am little bit confused and need help.

I have this SQL statement:

SELECT * 
FROM TABLE_NAME
WHERE CREATE_DATE BETWEEN TO_DATE(FIRST_DATE, 'YYYY-MM-DD') 
                      AND TO_DATE(SECOND_DATE , 'YYYY-MM-DD')

Questions:

  1. How correctly to use SELECT request in stored procedure?

  2. That SQL statement returns more than 1 row, is it mean that I need to use cursor?

  3. If table has 15 columns, as output I need to set all of them?

EDIT:

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME
(
    FIRST_DATE IN VARCHAR2(10),
    SECOND_DATE IN VARCHAR2(10)
)
AS
    oracle_cursor SYS_REFCURSOR;
BEGIN
    OPEN oracle_cursor FOR
        SELECT *
        FROM scheme_name.table_name
        WHERE CREATE_DATE BETWEEN TO_DATE(FIRST_DATE, 'YYYY-MM-DD') AND TO_DATE(SECOND_DATE, 'YYYY-MM-DD');
    DBMS_SQL.RETURN_RESULT(oracle_cursor);
END PROCEDURE_NAME;

Upvotes: 0

Views: 240

Answers (1)

APC
APC

Reputation: 146239

How correctly to use SELECT request in stored procedure?

In a stored procedure you need to assign the queried result set to a variable (or variables) which match the projection:

select << column >>, << column >>
into << variable >>, << variable >>
from table_name
....

That SQL statement returns more than 1 row, is it mean that I need to use cursor?

A cursor is one way of handling it. Although a cursor loop is usually the better approach:

for r in ( SELECT * 
           FROM TABLE_NAME
           WHERE CREATE_DATE BETWEEN TO_DATE(FIRST_DATE, 'YYYY-MM-DD') 
                  AND TO_DATE(SECOND_DATE , 'YYYY-MM-DD')

) loop

Populating a collection variable is another approach, using the BULK COLLECT:

select << column >>
bulk collect into << collection >>
from table_name
....

If table has 15 columns, as output I need to set all of them?

You can choose to create fifteen distinct variables. However, if your query's projection matches the table's projection (which it does with select *) you can use the %rowtype construct to define a record variable or a collection:

declare
    l_rec TABLE_NAME%rowtype; -- single row

    type t_rec is table of TABLE_NAME%rowtype; -- multiple rows
    l_recs t_rec
begin

    SELECT * 
    bulk collect into l_recs
    FROM TABLE_NAME
    WHERE CREATE_DATE BETWEEN TO_DATE(FIRST_DATE, 'YYYY-MM-DD') 
              AND TO_DATE(SECOND_DATE , 'YYYY-MM-DD');

I need to take the result and show that data in web page.

For returning results you probably just need to return a Ref Cursor. This is just a pointer which maps to constructs like JDBC ResultSet and ODBC ResultSet. For PHP this would be an oci_new_cursor. Find out more.

 Create or replace procedure get_recs
   (FIRST_DATE in varchar2, 
       SECOND_DATE in varchar2,
    OUT_RECS out sys_refcursor 
  ) is
Begin
    Open out_recs for
    SELECT * 

    FROM TABLE_NAME
    WHERE CREATE_DATE BETWEEN TO_DATE(FIRST_DATE, 'YYYY-MM-DD') 
              AND TO_DATE(SECOND_DATE , 'YYYY-MM-DD');
End;

Incidentally, you seem to expect to pass the parameters as strings: it would be better to pass them as actual dates.

Upvotes: 2

Related Questions