Multiple select statement in Oracle

Question

I want to write a simple stored procedure in Oracle with multiple select statements in that. I can do the same in the SQL server like

Create proc spc_name
as
begin
select * from tab1

Select * from tab2
......
end

I want to know how to write the same equivalent stored procedure in Oracle as I have done in SQL Server

Answer 1

select * from tab1

Select * from tab2

You cannot simply have a select query in PL/SQL. It will throw PLS-00428: an INTO clause is expected in this SELECT statement error.

Either you use a SELECT..INTO clause or use CURSORS. The SELECT INTO clause is used to retrieve one row or set of columns. It is used to store the returned data into predefined variables. For multiple SELECTs you can have multiple SELECT INTO clause, each clause would store the result of respective SQL.

For example,

SQL> DECLARE
  2    v_ename emp.ename%TYPE;
  3    v_empno emp.empno%TYPE;
  4  BEGIN
  5    SELECT ename INTO v_ename FROM emp WHERE empno = 7369;
  6    SELECT empno INTO v_empno FROM emp WHERE ename = 'SCOTT';
  7    dbms_output.put_line('SELECT statement 1 returns name =: '||v_ename);
  8    dbms_output.put_line('SELECT statement 2 returns emp number =: '||v_empno)
  9  END;
 10  /
SELECT statement 1 returns name =: SMITH
SELECT statement 2 returns emp number =: 7788

PL/SQL procedure successfully completed.

SQL>

To return multiple rows, you could use CURSOR. In your case, with multiple statements, you can have two REFCURSOR.

For example,

SQL> variable v_ref1 refcursor
SQL> variable v_ref2 refcursor
SQL>
SQL> DECLARE
  2    v_ref1 sys_refcursor;
  3    v_ref2 sys_refcursor;
  4  BEGIN
  5    OPEN :v_ref1 FOR SELECT empno, ename
  6       FROM emp ORDER BY empno
  7    FETCH FIRST 5 ROWS ONLY;
  8    OPEN :v_ref2 FOR SELECT empno, ename
  9       FROM emp ORDER BY empno DESC
 10    FETCH FIRST 5 ROWS ONLY;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> print v_ref1

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN

SQL> print v_ref2

     EMPNO ENAME
---------- ----------
      7934 MILLER
      7902 FORD
      7900 JAMES
      7876 ADAMS
      7844 TURNER

SQL>

If you want to combine the resultset of your multiple SELECT statements, you could use UNION operator and have it in single REFCURSOR. Given that the column data types match and are in proper order. It is just an example,

SQL> variable v_ref refcursor
SQL>
SQL> DECLARE
  2    v_ref sys_refcursor;
  3  BEGIN
  4    OPEN :v_ref FOR
  5      SELECT empno, DEPTNO FROM emp WHERE ROWNUM <=5
  6         UNION ALL
  7      SELECT empno, DEPTNO FROM EMP WHERE ROWNUM <=5;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> print v_ref

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7499         30
      7521         30
      7566         20
      7654         30
      7369         20
      7499         30
      7521         30
      7566         20
      7654         30

10 rows selected.

SQL>

Answer 2

Alternatively you can use cursors for this purpose. Please check this or this for sample Example.

You can declare multiple Cursors in a Stored procedures.

If you want to fetch result from Oracle Stroed PRoc to Java probably you can refer this URL or this Stack Overflow answer