In this post, we’ll explore two powerful SQL concepts — Cursors and Triggers — with complete examples that you can run directly in Oracle LiveSQL.
CURSOR — Process Cursor with Condition
Problem Statement:
Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.
CREATE TABLE Employee (
EmpID NUMBER PRIMARY KEY,
EmpName VARCHAR2(100),
Salary NUMBER(10,2)
);
INSERT INTO Employee VALUES (1, 'Arjun', 45000);
INSERT INTO Employee VALUES (2, 'Sneha', 52000);
INSERT INTO Employee VALUES (3, 'Kiran', 60000);
INSERT INTO Employee VALUES (4, 'Meena', 48000);
COMMIT;
SET SERVEROUTPUT ON;
DECLARE
v_EmpName Employee.EmpName%TYPE;
v_Salary Employee.Salary%TYPE;
CURSOR emp_cursor IS
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_EmpName, v_Salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_EmpName || ' | Salary: ' || v_Salary);
END LOOP;
CLOSE emp_cursor;
END;
/
*TRIGGER *— AFTER INSERT Trigger (Student Table)
Problem Statement:
Whenever a new student is added to the Students table, automatically insert a log entry into the Student_Audit table to keep track of the registration.
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(100),
Course VARCHAR2(100)
);
CREATE TABLE Student_Audit (
AuditID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID NUMBER,
StudentName VARCHAR2(100),
ActionTaken VARCHAR2(100),
ActionDate DATE
);
CREATE OR REPLACE TRIGGER trg_AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName, ActionTaken, ActionDate)
VALUES (:NEW.StudentID, :NEW.StudentName, 'New Student Registered', SYSDATE);
END;
INSERT INTO Students (StudentID, StudentName, Course)
VALUES (1, 'Harini', 'Computer Science');
COMMIT;
SELECT * FROM Student_Audit;
Conclusion:
Cursors help process data row by row, while triggers automatically perform actions when data changes.
Together, they make SQL more powerful by combining control and automation in database operations.
Thank you @santhoshnc sir for guiding me!!!!
Top comments (0)