Display total number of rows in a table
Consider the following tables to complete the following assignment.
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:
