Returns the status of the cursor An attribute that processes an exact number of

Question

Returns the status of the cursor

An attribute that processes an exact number of rows or counts the number of rows fetched in a loop

An attribute used to determine whether the most recent FETCH statement successfully returned a row

1. In your own words, explain the advantage of using %ROWTYPE to declare a record structure based on a cursor declaration.

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.

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.

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?

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.

Explanation / Answer

1. In your own words, explain the advantage of using %ROWTYPE to declare a record structure based on a cursor declaration.

In PL/SQL, it is easy to declare records which have the same structure as a table, a view or the result of a cursor by using the %ROWTYPE attribute.

Supposing there's a table with Student Details like:

SQL> DESCRIBE gradesix_stud

Name Null? Type

_________ __________ _____________

STUD_ID NOT NULL NUMBER(38)

STUD_NAME VARCHAR2(100)

GENDER VARCHAR2(7)

As per the above table, if we have to write a block of code that retrieves one single record or row from the gradesix_stud table using the student ID to work with the values in the row, we may declare a variable for each column and fetch values into the variables, like:

The above however is a lot of code and so to reduce code redudency and to make it much more easier we use the %ROWTYPE:

Now, to use %ROWTYPE to declare a record that has the same structure as say, a SELECT statement in a cursor:

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.