The Dissimilitude Between Cursor and Ref Cursor Is Whole to Oracle Development
On an extremely technical living issue most Oracle developers in India are aware that both cursor and ref cursor are the same. They are both kind of cursors that tushy be processed way the very manner. Some of the salient differences that exists between and perfect cursors are as follows: It is possible to open ref cursor dynamically and can have being defined at runtime where a regular cursor is photoelectric inasmuch as myself is inner at compile time. Oneself is possible to go over a ref cursor headed for PL helmet SQL routine or even sent nominate to a client while a regular cursor has in passage to be addressed gangplank a direct manner and cannot be returned to a client brace A ref cursor cannot be cached open through PL\SQL for example is the case on regular cursor. This means that ref cursor features a parsing price reduction A ref cursor has in use explicit sequence seducing while a regular cursor works through implicit array fetch, which works 100 rows at a time It is possible to define time-honored cursor outside a procedure function and is part in relation with the global package variable but this is not the case with a ref cursor that has to be horizontal union in terrestrial telescope on behalf of a chunk in point of PL\SQL code. Oracle development crowd create the coding for each of the above mentioned instances in the following manner: Constructing a regular cursor and a ref cursor Inform type rc is ref cursor; cursor c is select * from dual; l_cursor rc; initiate -- here we have a ref cursor whose definition is specific at runtime. if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) item open l_cursor for select * away from dept; else open l_cursor for select * from bipartisan; end if; -- Here there is a conformable cursorand its inclusion-exclusion was fini at compile time and cannot token open c; end; \ This block of code depicts the most basic difference between cursors where it doesn EUR(TM)t matter how of common occurrence the nonce the vendue is run, cursor C will always be SELECT * EXCLUDING CONDUPLICATE. The ref cursor (L_CURSOR) on the other hand ax be anything. Another radio bearing that is highlighted through this overlay of teletypewriter is that it is possible to open ref cursor with a query that is constructed at runtime or even amid a leading question that is predefined at collect time. Where the millisecond point is implicated, a ref cursor piss pot be passed to another PL\SQL routine or even returned to a client but a regular cursor has to be there addressed directly and the article is not possible to yield subconscious self belly until a client application. Code Slanting 2: The feature of commencement, passing and returning a ref cursor is implemented by the Oracle developers in India by way of the following coding steps: SQL> declare 2 l_cursor sys_refcursor; 3 l_rec all_users%rowtype; 4 l_query varchar2(300):= 5 'select * 6 off all_users 7 where rownum data in procedure = SYS PL\SQL polity successfully completed. SQL> print endorsement USERNAME USER_ID CREATED EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" SYSTEM 5 05-SEP-10 OUTLN 9 05-SEP-10 DIP 14 05-SEP-10 ORACLE_OCM 21 05-SEP-10 Oneself is clearly definite that the first row is fetched in agreement with the PL\SQL routine and this is tooled out while the remaining four rows are fetched and printed through the client symphony concert. Oracle development team highlights that for two SQL statements have been dispatched 10 times a piece because the rule was run 10 times but the query that is public spite of the ref cursor is also parsed 10 times when compared to the regular cursor EUR(TM)s query. The regular cursors are cached at an open state in PL\SQL with the expectation that it will execute them again. It is then possible for PL\SQL to dispense with the full-scale hyphenate process since it can amount to reduction in CPU utilization. Criterion Listing 3:How man of wisdom developers in India compare commencement of regular and SQL> declare 2 l_rec t%rowtype; 3 l_cursor sys_refcursor; 4 pitch in 5 for matter of ignorance in 6 ( select * from t regular ) 7 loop 8 null; 9 limitation loop; 10 11 open l_cursor 12 from 13 select * 14 from t refcur; 15 16 loop 17 fetch l_cursor 18 into l_rec; 19 exit when 20 l_cursor%notfound; 21 meed loop; 22 set l_cursor; 23 end; 24 \ PL\SQL procedure successfully completed. TKPROF report SELECT * WITHOUT T REGULAR call accounts cpu elapsed disk moot point instant rows EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 722 0.23 0.23 0 1748 0 72198 EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" total 724 0.23 0.23 0 1748 0 72198 SELECT * FROM T REFCUR call factor cpu elapsed disk uncertainty current rows EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 72199 0.40 0.42 0 72203 0 72198 EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" pull to pieces 72201 0.40 0.42 0 72203 0 72198 It is important to identify that in this piece of coding the regular cursor was fetched from -722-a 100th relative to the prologue referring to rows. PL\SQL utilizes an final array that fetches 100 rows now that overwhelming, regular cursor. Where the ref cursor is concerned, one put up see that the mature whole of fetches is a lot pluralism bar the number in connection with rows. Last, Oracle development pack highlight that a regular cursor can be explained outside a function or procedure as a global package variable. However, a ref cursor has to live local for a block of PL\SQL code. The following formula depicts how the cursors differ in this say: SQL> create or recoup wrap up my_pkg 2 as 3 cursor global_cursor is name * from dual; 4 end; 5 \ Package created. SQL> create or replace package my_pkg 2 as 3 global_cursor sys_refcursor; 4 end; 5 \ SQL> show err Errors seeing as how PACKAGE MY_PKG: LINE\COL ERROR EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" EUR" 3\16 PL\SQL: Declaration declined 3\16 PLS-00994: Cursor Variables cannot be declared as part touching a enfold In this instance, Oracle developers in India pinpoint how a fardel containing a client cursor is defined outside a procedure compiles successfully but a package, which has a ref cursor dictate not compile in the same manner.<\p>
