How to use PL/SQL Cursors In SQL
Using PL/SQL cursors are an effective method for the retrieving the data from database and it process it row by row. Cursors are allowed to the iterate through the result set returned by the SQL query and enabling to the perform operations on each row individually.
Step 1: Create a table and name it as employee.
Here is the employee table.
Employee table
Step 2: Implement the Code
DECLARE
v_total_salary NUMBER := 0;
BEGIN
FOR emp_record IN (SELECT * FROM employee) LOOP
v_total_salary := v_total_salary + emp_record.salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);
END;
/
Output:
Output
Explanation:
- We declare the variable v_total_salary to store the total salary is initialized to 0.
- We can use cursor loop to the iterate the each row of the employee table. The cursor emp_record holds the current row data.
- In the loop, we increment the v_total_salary by adding the salary of the employee.
- Once the loop is finished, we print the total salary with the help of DBMS_OUTPUT.PUT_LINE.
How to Get Counts of all Tables in a Schema in PL/SQL?
In Database Management System, it is essential to retrieve the statistical information about tables with the schema. Whether it is for monitoring the database health, optimizing the performance, or simply understanding the data structures having access to row counts of the tables can be more valuable.