Clever Geek Handbook

How to free a cursor in PLSQL?

I am coding in both SQL Server and Oracle.
When I am coding in SQL Server I used this:

OPEN curUSERS;
CLOSE curUSERS;
DEALLOCATE curUSERS;

Now when I am coding in Oracle I have used this:

OPEN curUSERS;
CLOSE curUSERS;

I saw the DEALLOCATE keyword in PL / SQL, but when I used this statement

DEALLOCATE(curUSERS);

It throws an error. How can I do the same (release) in PL / SQL?

+3


source to share


2 answers


Oracle does not explicitly require you to free cursor memory. Just CLOSE(cursor)

fine.



+5


source


Explicit pointers should be avoided whenever possible. Explicit cursors require more code and are slower because they are not collected automatically. FOR cursor loops are simple and fast.

Example circuit

drop table table1;
create table table1 as select 1 a from dual;

Explicit cursors - more code, worse performance



declare
    v_a number;
    cursor table1_cursor is select a from table1;
begin
    open table1_cursor;
    loop
        fetch table1_cursor into v_a;
        exit when table1_cursor%notfound;
        dbms_output.put_line(v_a);
    end loop;
    close table1_cursor;
end;
/

Cursor for no-code code, better performance

begin
    for table1_rows in (select a from table1)
    loop
        dbms_output.put_line(table1_rows.a);    
    end loop;
end;
/
-1


source






More articles:


All Articles