Database Programming with PL/SQL
5-2: Using Explicit Cursor Attributes
Practice Activities
Vocabulary
Declares a record with the same fields as the cursor on which it is based - %ROWTYPE
A composite data type in PL/SQL, consisting of a number of fields each with their own name and data type - RECORD
Returns the status of the cursor - ISOPEN
An attribute that processes an exact number of rows or counts the number of rows fetched in a loop - ROWCOUNT
An attribute used to determine whether the most recent FETCH statement successfully returned a row - %NOTFOUND
Try It/Solve It
1. In your own words, explain the advantage of using %ROWTYPE to declare a record structure
based on a cursor declaration.
With %ROWTYPE, we don't need to declare and use a lot of variables to store what a cursor returns. We just declare a variable with cursor_name%ROWTYPE and PL/SQL automatically creates a list of variables of the same type of the variables returned by the cursor and we can acces them with variable_name.column_name
2. Write a PL/SQL block to read through rows in the countries table for all countries in region 5
(South America region). For each selected country, display the country_name,
national_holiday_date, and national_holiday_name. Use a record structure to hold all the columns
selected from the countries table.
Hint: This exercise is similar to question 4G in the previous lesson. Use your solution as a starting
point for this exercise.
DECLARE
CURSOR countries_cur IS
SELECT country_name, national_holiday_date, national_holiday_name
FROM wf_countries
WHERE region_id = 5;
v_countries countries_cur%ROWTYPE;
BEGIN
OPEN countries_cur;
LOOP
FETCH countries_cur INTO v_countries;
EXIT WHEN countries_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_countries.country_name || ' ' || v_countries.national_holiday_date || ' ' || v_countries.national_holiday_name);
END LOOP;
CLOSE countries_cur;
END;
3. For this exercise, you use the employees table. Create a PL/SQL block that fetches and displays the six employees with the highest salary. For each of these employees, display the first name, last name, job id, and salary. Order your output so that the employee with the highest salary is displayed first. Use %ROWTYPE and the explicit cursor attribute %ROWCOUNT.
DECLARE
CURSOR employees_cursor IS
SELECT *
FROM employees
ORDER BY salary DESC;
v_emp_rec employees_cursor%ROWTYPE;
BEGIN
OPEN employees_cursor;
LOOP
FETCH employees_cursor INTO v_emp_rec;
EXIT WHEN employees_cursor%ROWCOUNT > 6;
DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' ' || v_emp_rec.last_name || ' ' || v_emp_rec.job_id || ' ' || v_emp_rec.salary);
END LOOP;
CLOSE employees_cursor;
END;
4. Look again at the block you created in question 3. What if you wanted to display 21 employees instead of 6? There are only 20 rows in the employees table. What do you think would happen?
We will get an error:
ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
5. In real life we would not know how many rows the table contained. Modify your block from question 3 so that it will exit from the loop when either 21 rows have been fetched and displayed, or when there are no more rows to fetch. Test the block again.
DECLARE
CURSOR employees_cursor IS
SELECT *
FROM employees
ORDER BY salary DESC;
v_emp_rec employees_cursor%ROWTYPE;
BEGIN
OPEN employees_cursor;
LOOP
FETCH employees_cursor INTO v_emp_rec;
EXIT WHEN employees_cursor%ROWCOUNT > 6 OR employees_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' ' || v_emp_rec.last_name || ' ' || v_emp_rec.job_id || ' ' || v_emp_rec.salary);
END LOOP;
CLOSE employees_cursor;
END;
0 Comments