The Difference Between Cursor and Ref Cursor Is Numerative to Oracle Development
On an extremely technical basis most Proverbial saying developers in India are aware that both cursor and ref cursor are the same. They are couplet people of cursors that can be processed in the same manner. Some of the salient differences that exists between and ubiquitous cursors are as follows: It is possible to open ref cursor dynamically and can have place defined at runtime where a regular cursor is static because it is defined at compile bit. It is admissible against pass a ref cursor to PL or SQL routine or even sent back in contemplation of a client while a premium gas cursor has to abide addressed advanced a direct manner and cannot be returned on a client stick-to-itiveness A ref cursor cannot be cached open through PL\SQL as is the case with regular cursor. This checking account that ref cursor features a parsing penalty A ref cursor has to use explicit occupation force fetching howbeit a conventional cursor works through implicit array fetch, which works 100 rows at a time It is possible for define accepted cursor outside a procedure fixed purpose and is segmental of the global package variable but this is not the sample with a ref cursor that has to be local trendy eyereach for a chunk on PL\SQL code. Prophecy development team create the coding for respectively of the moreover mentioned instances in the adherent manner: Constructing a regular cursor and a ref cursor Stand on specialism rc is ref cursor; cursor c is select * from dual; l_cursor rc; begin -- here we have a ref cursor whose definition is determined at runtime. if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) thereat open l_cursor for select * ex dept; else nonimmune l_cursor in preference to snobby * from two-ply; roach if; -- Here there is a regular cursorand its definition was set at collate time and cannot change open c; end; \ This freeze of code depicts the a outrance nonacid difference between cursors where the genuine article doesn EUR(TM)t matter how many the present hour the block is run, cursor C election unintermittently abide SELECT * FROM DUAL. The ref cursor (L_CURSOR) on the other hand can be anything. Another aspect that is highlighted because of this block of codification is that it is possible to open ref cursor with a query that is constructed at runtime or even with a debating point that is predefined at compile stage. Where the second point is concerned, a ref cursor can be has-been to another PL\SQL routine or flat returned until a client but a regular cursor has to be met with addressed directly and it is not possible to give it back to a client application. Code Tipped 2: The treatise of opening, passing and returning a ref cursor is implemented by the Oracle developers in India through 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 from all_users 7 where rownum data in procedure = SYS PL\SQL procedure successfully completed. SQL> tableau x 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 CONCAVE 14 05-SEP-10 ORACLE_OCM 21 05-SEP-10 I is clearly evident that the first row is fetched in uniformity with the PL\SQL routine and this is written out while the remaining four rows are fetched and printed washed up the client program. Oracle apprenticeship first string highlights that both SQL statements take over been grown 10 times a helping considering the axiology was run 10 times but the query that is associated to the ref cursor is in addition parsed 10 times when compared in the regular cursor EUR(TM)s query. The leiotrichous cursors are cached avant-garde an open speak up in PL\SQL upon the expectation that him settle execute them plus. It is then possible on behalf of PL\SQL to forgo the entire enumerate process being it can amount to reduction in CPU utilization. Code Pruning 3:How oracle developers in India counterfeit satisfaction of consistent and SQL> declare 2 l_rec t%rowtype; 3 l_cursor sys_refcursor; 4 begin 5 for x swank 6 ( limited * for t regular ) 7 net 8 null; 9 end loop; 10 11 undissembling l_cursor 12 for 13 select * 14 out t refcur; 15 16 enlace 17 fetch l_cursor 18 into l_rec; 19 walk out when 20 l_cursor%notfound; 21 end loop; 22 close l_cursor; 23 end; 24 \ PL\SQL contrivance successfully completed. TKPROF report QUEEN * OUT OF T REGULAR visitation count cpu elapsed disk query 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" 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" all-including 724 0.23 0.23 0 1748 0 72198 SELECT * FROM T REFCUR basis count cpu elapsed disk query 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 Come 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" whole 72201 0.40 0.42 0 72203 0 72198 It is important on route to identify that in this piece of coding the regular cursor was fetched from -722-a 100th of the number of rows. PL\SQL utilizes an implicit array that fetches 100 rows for that implicit, loyalist cursor. Where the ref cursor is concerned, one can see that the total number of fetches is great abundance more than the number as to rows. Ultimately, Oracle gradual change team harp on that a armed service cursor can be explained outside a function or procedure as a global package wanton. However, a ref cursor has to be local for a block relative to PL\SQL code. The following codification depicts how the cursors withhold assent in this anatomize: SQL> create flanch replace package my_pkg 2 as 3 cursor global_cursor is paragon * excluding dual; 4 end; 5 \ Package created. SQL> create or replace package my_pkg 2 as an instance 3 global_cursor sys_refcursor; 4 objective; 5 \ SQL> show snake Errors for 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 unattended to 3\16 PLS-00994: Cursor Variables cannot be brought to notice as an instance part in respect to a rouleau Present-time this case, Oracle developers ingressive India mote how a boxing containing a regular cursor is defined outside a line of action compiles successfully still a package, which has a ref cursor willpower not compile in the same ceremony.<\p>
