A REF CURSOR is basically a datatype. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at runtime. The primary advantage of using cursor variables is their capability to pass result sets between subprograms (like stored procedures, functions and packages)
An example for Ref Cursor is shown below:
DECLARE
TYPE Type r_cursor is REF CURSOR;
c_book type_r_cursor;
Book_rec Book%rowtype;
BEGIN
OPEN c_book FOR SELECT * FROM Book;
LOOP
FETCH c_emp INTO book_rec;
EXIT WHEN c_book%rowcount> 20 or c_book%notfound ; DBMS_OUTPUT.PUT_LINE(book_Rec.cBookname || ‘ - ‘ ||
book_Rec.cAuthorName);
END LOOP;
CLOSE c_book;
END;
/
Type type_r_cursor is REF CURSOR;
The above statement simply defines a new data type called “type_r_cursor,” which is of the type REF CURSOR. We declare a cursor variable named “c_BOOK” based on the type “type_r_cursor” as follows:
c_book type_r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
OPEN c_book FOR SELECT * FROM Book;
Fetch is done with in a Loop to retrieve the records from the dynamic cursor. Once the processing is done the cursor is closed before the block ends.
Then, what differentiates a cursor Variable from an Explicit Cursor?
• A cursor variable can be associated with more than one query at runtime.
• An explicit cursor is associated with one query at compilation time.
Summary
In this chapter, you have learnt that:
Composite datatypes (also known as collections) store multiple data of different datatypes due to its internal logical division.
They are not pre-defined hence created by the user.
PL/SQL records, INDEX BY TABLE, VARRAY, NESTED TABLE and REF cursor are collectively called Composite Datatypes.
PL/SQL records are used to store data fetched from multiple columns of a table and %rowtype can be used to store an entire record using SELECT * statement.
INDEX BY table holds data of one or more columns and INDEX BY table of records hold entire database table data either from the vertical selection of columns or entire table data.
Varray stores multiple values for a particular column as per the limit and Nested table can have an embedded table within a column specifying unlimited values.
Cursor variable declared using REF Cursor can be associated with more than one query dynamically.
Lab Exercises
1
.
Create a PL/SQL block to display the information about a given job, say‘ST_CLERK’.
Use a PL/SQL record, which is based on the structure of the Jobs table.Print the information about the job using DBMS_OUTPUT.PUT_LINE
The information should resemble as shown below:
2
.
Write a PL/SQL block to print information about a given country using PL/SQL Record.a. Use the DEFINE command to provide the country ID. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable.
SET SERVEROUTPUT ON SET VERIFY OFF
DEFINE p_countryid=CA
b. Use DBMS_OUTPUT.PUT_LINE to print information about country details.
C. Execute and test the PL/SQL block for the countries with the Ids CA,DE,UK,US.
3. Create a PL/SQL block that will store the information of a retired employee
into a table called RetiredEmpsData.
• Declare a PL/SQL record variable based on the structure of the Employees table.
• Supply the employee ID
• Retrieve the record of the employee specified and store it in the variable declared
• Query the RetiredEmpsData table
[Note: Create the RetiredEmpsData table with the columns: EmpID, EName, Job, MgrID, HireDate, RetiredDate, Sal, Comm, and DeptID]
4. Create a PL/SQL block that uses INDEX BY table to retrieve the names of cities of each location ID from the Locations table, and print the same on the screen. The block should do the following:
• Declare an INDEX BY table to store the names of the cities
• Retrieve the names of all current cities from the Locations table into the INDEX BY table using a loop. Assign value for the Location_ID column based on the following counter values:
• Use another loop to retrieve the city names from the INDEX BY table and print them using the DBMS_OUTPUT.PUT_LINE
The output from the block should be as shown below
Roma
Venice Tokyo Hiroshima Southlake
South San Francisco South Brunswick Seattle
Toronto Whitehorse Beijing Bombay Sydney Singapore London Oxford Stretford Munich Sao Paulo Geneva Bern Utrecht Mexico City
PL/SQL procedure successfully completed
5.
Create a table EMPPHDETAIL with following specification EMPLOYEE_ID NUMBER(6),FIRST_NAME VARCHAR2(26).Phone number should be a Varray column. Insert 3 phone numbers into each employee’s detail. Select the data from this table.
6. Create an Object type for employee ID. Use this in the creation of the Varray which is restricted to 10.
Create a table EmpInDept that contains a column of the employee ID varray, which holds Ids of the employees working in a department. Confirm whether the table has been created.
Insert few values into this table and view them.