If you're diving into Oracle SQL and want to explore how cursors work, this post is for you! Let's walk through a simple example where we use a cursor to display employee names whose salary exceeds ₹50,000.
What’s a Cursor?
A cursor in SQL is a pointer that allows you to iterate through query results row by row. It’s especially useful when you need to process each record individually.
The Scenario
We have an Employee table with columns like emp_name and salary. Our goal is to display the names of employees earning more than ₹50,000.
The Code
sql
DECLARE
-- Variable to hold employee name
v_emp_name Employee.emp_name%TYPE;
-- Cursor declaration
CURSOR high_salary_cursor IS
SELECT emp_name
FROM Employee
WHERE salary > 50000;
BEGIN
-- Loop through the cursor
FOR emp_record IN high_salary_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.emp_name);
END LOOP;
END;
Triggers in Oracle SQL are like little watchdogs—they automatically execute when certain events happen in the database. Let’s create a trigger that logs a message whenever a new employee is inserted into the Employee table.
The Trigger Code
sql
CREATE OR REPLACE TRIGGER log_new_employee
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('New employee added: ' || :NEW.emp_name || ', Salary: ' || :NEW.salary);
END;
What’s Happening Here?
AFTER INSERT ON Employee: The trigger fires after a new row is inserted.
After creating the trigger, insert a new employee:
sql
INSERT INTO Employee VALUES (101, 'Priya', 60000);
Combining cursors and triggers gives you powerful control over your data flow. Cursors help you process data row-by-row, while triggers let you react to changes automatically.Thank you @santhoshnc sir for guide me
Top comments (0)