PL/SQL and SQL
Working with data that is accessed buy SQL can include the use of
In the last example we had to specify the data types, Oracle can automatically match the data types for us and there are two data types that allow us to do this %type and %rowtype.
%TYPE | is used to declare a field with the same type as that of a specified table column DECLARE |
%ROWTYPE | is used to declare a record with the same types as found in the specified table, view or cursor DECLARE |
Cursor Attributes
Cursors have attributes that can be used and are obtained by using cursor.attribute_name
%ISOPEN | contains TRUE if the cursor is currently open FALSE if not. Always returns FALSE for any implicit cursor. |
%FOUND | can contain several values NULL - before a fetch on the cursor |
%NOTFOUND | opposite to %FOUND |
%ROWCOUNT | contains the number of rows fetched so far by the DML statement or the number of rows returned by a SELECT INTO statement |
Every SQL statement in a PL/SQL block is actually an implict cursor. You can use the PL/SQL statement instead of a cursor, Oracle will create a implicit cursor that operates behind the scenes like a explicit cursor.
Using %ROWCOUNT | DECLARE Note: SQL stands for any implicit cursor |
Single-Row Implicit Cursor | DECLARE Note: you can also create a record for the data type, saves repeating |
Muliple-Row Implicit cursor | -- First Option - DML statement in a PL/SQL block -- Second Option - Write an embedded query in a cursor FOR loop |
Explicit cursors can be either static or dynamic
Static | return the same query each time with potentially different results, the results change as the data changes in the tables or views. |
Dynamic | act like parameterized subroutines, they run different queries each time, depending on the actual parameters provided when they're opened |
Explicit cursors require you to open, fetch and close them whether you use while or cursor for loops. Cursor for loops implicitly open, fetch and close cursors for you and thus are simpler to manage.
Explicit cursor | open cursor_name [(parameter1 [, parameter2 [,parameter(n+1]])]; close cursor_name; |
A cursor acts logically as a pointer to a result set, you can move the pointer through the result set processing each row until the end.
Static explicit cursor | -- Open, Fetch, Close version -- cursor FOR loop version Note: you can use records as well, you might also want to check that if no records are found |
Dynamic explicit cursor | -- Open, Fetch, Close version -- cursor FOR loop version - substitute the open,fetch, close stuff |
You can reduce the amount of code by using the for cursor construct, you had to go through a number of steps to get the data define variables, define the cursor, open the cursor, fetch the data in a loop, exit the loop then close the cursor, with the for construct all you have to do is define the cursor. There are more examples of the for cursor loop in the explicit cursor selection above.
For Cursor example | DECLARE |
A ref cursor allows you to pass a cursor reference from one PL/SQL program unit to another, you can create a variable that will receive a cursor and enable access to it.
REF cursor example | create or replace procedure test_cursor_proc_3(p_cursor OUT sys_refcursor) create or replace procedure test_cursor_proc_3(p_cursor OUT sys_refcursor) Note: the top example is a weakly typed ref cursor, the bottom type is a strongly typed ref cursor. |
See collections for more information