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:
FOR
doesn't need in this situation. Count
without INTO
retuns only one row. 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?
Yes. You can use multiple subqueries in a select list. You will have to use another subquery for adding a SUM function.
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.
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.
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.
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With