Consider the following tables to complete the following assignment.
Table 1: 'Emp_Detail'
Table2: 'Department'
Q. Write a PL/SQL block to create a cursor based records and display the total number of rows present in the table.
Answer:
The structure of cursor based records is similar to elements of a predefined cursor. The following code creates a cursor based records.
So, the following code creates a cursor based record and finds a total number of rows and exits when no rows found to fetch.
The above code will display the total number of selected fields and shows the total number of rows present in the table.
Output:
Table 1: 'Emp_Detail'
| Employee_id | First_Name | Last_name | Salary | DEPT_ID |
|---|---|---|---|---|
| 1 | Shruti | Shrabya | 50000 | 1 |
| 2 | Jaya | Singh | 10000 | 2 |
| 3 | Mangala | Thokal | 60000 | 3 |
| 4 | Surendra | Maurya | 70000 | 4 |
Table2: 'Department'
| Dept_ID | Dept_Name | Manager_ID |
|---|---|---|
| 1 | Accounting | 1 |
| 2 | Shipping | 3 |
| 3 | Store | 3 |
Q. Write a PL/SQL block to create a cursor based records and display the total number of rows present in the table.
Answer:
The structure of cursor based records is similar to elements of a predefined cursor. The following code creates a cursor based records.
So, the following code creates a cursor based record and finds a total number of rows and exits when no rows found to fetch.
DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM emp_detail;
rec_emp_detail c_emp_detail%ROWTYPE;
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_emp_detail;
EXIT WHEN c_emp_detail%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_emp_detail.employee_id ||' '||rec_emp_detail.first_name||' '||rec_emp_detail.last_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of rows : '||c_emp_detail%ROWCOUNT);
CLOSE c_emp_detail;
END;
The above code will display the total number of selected fields and shows the total number of rows present in the table.
Output:
