I have so many complex join queries, for which I want to return the nu开发者_运维技巧mber of rows affected. Say for select 100, insert 50, etc..
I dont want to change the query for this operation. I tried SQL%ROWCOUNT
. But it doesnt work. Please help me in this.
To use sql%rowcount, you'll need to use it directly after the select/insert/update/delete. For example:
declare
v_date date;
v_cnt pls_integer := 0;
v_cnt2 pls_integer := 0;
begin
select sysdate into v_date from dual;
v_cnt := sql%rowcount;
dbms_output.put_line('rowcount: ' || v_cnt);
commit;
v_cnt2 := sql%rowcount;
dbms_output.put_line('rowcount2: ' || v_cnt2);
end;
Output:
rowcount: 1
rowcount2: 0
Hope that helps.
If I understood correctly, you want to examine the inner workings of your query (cardinality of joined tables etc.) - is that right? If yes, you need to look at the execution plan Oracle generates for your query. Try a command like this:
EXPLAIN PLAN FOR SELECT * FROM ...; // <-- insert your query here
SELECT * FROM TABLE(dbms_xplan.display);
(See SQL*Plus FAQ for other options and more information.)
精彩评论