Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle. Can't understand how FOR works with subquery SELECT INTO

Tags:

sql

oracle

plsql

Found strange thing. Can't understand why Oracle allow this query and why cnt variable doesn't change after it's execution:

declare cnt number;
begin
 for r in (Select count(1) into cnt from  v$session) loop
   dbms_output.put_line(cnt);
 END LOOP; 
end;

Select count(1) from v$session returns not null value

Of course I've understand:

  1. That FOR doesn't need in this situation. Count without INTO retuns only one row.
  2. That I can use it without INTO and it will works.

Just curious how and why it works in query above.

It's strange why Oracle allow subquery with SELECT INTO, because in common situation Oracle returns compilation error ORA-06550

declare cnt number;
 begin
        select count(1) from  (Select count(1) into cnt from  v$session)
 end;
or 

And if first query works - why it doesn't return cnt value correctly?

like image 815
Viktor Bardakov Avatar asked Oct 15 '15 08:10

Viktor Bardakov


People also ask

Can we use subquery in SELECT clause Oracle?

Yes. You can use multiple subqueries in a select list. You will have to use another subquery for adding a SUM function.

What is the requirement for using a subquery in the SELECT clause?

Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.

Which option will not work in subquery?

ORDER BY command cannot be used in a Subquery. GROUPBY command can be used to perform same function as ORDER BY command. Use single-row operators with singlerow Subqueries. Use multiple-row operators with multiple-row Subqueries.

Which is better JOIN or subquery in Oracle?

The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.


2 Answers

Correct sql statement

try this

declare cnt number;
begin
 for r in (Select count(1) as cnt from  v$session) loop
   dbms_output.put_line(r.cnt);
 END LOOP; 
end;

Explanation

select_statement

SQL SELECT statement (not PL/SQL SELECT INTO statement). For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal—you cannot reference it with the name SQL.

see http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/cursor_for_loop_statement.htm#LNPLS1155

the cnt variable is ignored, this can be proved by the following example, because no error is raised:

declare
  cnt       number;
  a_varchar varchar2(1);
begin
  for r in (Select 'AA' into a_varchar from v$session) loop
    dbms_output.put_line(a_varchar);
  end loop;
end;
like image 82
Frank Ockenfuss Avatar answered Oct 13 '22 01:10

Frank Ockenfuss


Very interesting, I'd say this is a bug except it's a benign one. The INTO cnt bit is valid PL/SQL but is ignored in this context, which can be proven with a simple test case:

declare cnt number;
begin
 for r in (select count(1) into cnt from dual) loop
   dbms_output.put_line('cnt=' || cnt);
   dbms_output.put_line('r=' || r."COUNT(1)");
 end loop;
end;

As you can see, the INTO clause is ignored, and as expected the r record is correctly populated. Output on my 11gR2 instance:

cnt=
r=1
like image 43
Jeffrey Kemp Avatar answered Oct 13 '22 01:10

Jeffrey Kemp