5. Create a cursor for the Employee table & extract the values from the table.
Declare the variables,Open the cursor & extract the values from the cursor.
Close the cursor. Employee(E_id,E_name, Age, Salary)
Solution:
STEP1: Create table Employee
CREATE TABLE Employee (
E_id INT,
E_name VARCHAR(255),
Age INT,
Salary DECIMAL(10, 2)
);
OUTPUT:
Table created
STEP 2:Insert values into Employee table:
BEGIN
INSERT INTO Employee VALUES(1, 'Samarth', 30, 50000.00);
INSERT INTO Employee VALUES(2, 'Ramesh Kumar', 25, 45000.00);
INSERT INTO Employee VALUES (3, 'Seema Banu', 35, 62000.00);
INSERT INTO Employee VALUES (4, 'Dennis Anil', 28, 52000.00);
INSERT INTO Employee VALUES (5, 'Rehman Khan', 32, 58000.00);
END;
OUTPUT:
Statement Processed.
STEP 3: Create a procedure
CREATE OR REPLACE PROCEDURE fetch_employee_data
IS
emp_id Employee.E_id%TYPE;
emp_name Employee.E_name%TYPE;
emp_age Employee.Age%TYPE;
emp_salary Employee.Salary%TYPE;
CURSOR emp_cursor IS
SELECT E_id, E_name, Age, Salary
FROM Employee;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_id, emp_name, emp_age, emp_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || emp_name
|| ', Age: ' || emp_age || ', Salary: ' || emp_salary);
END LOOP;
CLOSE emp_cursor;
END;
/
OUTPUT:
Procedure created.
STEP 4: Run the procedure
BEGIN
fetch_employee_data;
END;
OUTPUT:
Employee ID: 1, Name: Samarth, Age: 30, Salary: 50000
Employee ID: 2, Name: Ramesh Kumar, Age: 25, Salary: 45000
Employee ID: 3, Name: Seema Banu, Age: 35, Salary: 62000
Employee ID: 4, Name: Dennis Anil, Age: 28, Salary: 52000
Employee ID: 5, Name: Rehman Khan, Age: 32, Salary: 58000
Statement processed.
Program 6
Write a PL/SQL block of code using parameterized Cursor, that will merge the
data available in the newly created table N_RollCall with the data available in
the table O_RollCall. If the data in the first table already exist in the second
table then that data should be skipped.
Solution:
To accomplish this task in MySQL, we can use a stored procedure with a
parameterized cursor to merge data from one table (N_RollCall) into another table
(O_RollCall) while skipping existing data. We’ll iterate through the records of
N_RollCall and insert them into O_RollCall only if they do not already exist.
Step 1: First, let’s create the N_RollCall and O_RollCall tables with similar structure:
CREATE TABLE N_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);
OUTPUT:
Table created
CREATE TABLE O_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);
OUTPUT:
Table created
Step 2: Add Sample Records to both tables
(date format: MM-DD-YYYY)
Begin
INSERT INTO O_RollCall VALUES (1,'Shivanna','08-15-1995');
INSERT INTO O_RollCall VALUES (3,'Cheluva','12-10-1990');
end;
OUTPUT:
Statement Processed.
Select * from O_Rollcall;
Begin
INSERT INTO N_RollCall VALUES(1, 'Shivanna', '08-15-1995');
INSERT INTO N_RollCall VALUES(2, 'Bhadramma','03-22-1998');
INSERT INTO N_RollCall VALUES(3, 'Cheluva', '12-10-1990');
INSERT INTO N_RollCall VALUES(4, 'Devendra', '05-18-2000');
INSERT INTO N_RollCall VALUES(5, 'Eshwar', '09-03-1997');
end;
OUTPUT:
Statement Processed.
Select * from N-Rollcall;
OUTPUT:
Step 3: Define the Stored Procedure
Next, let’s define the merge_rollcall_data stored procedure to merge records from
N_RollCall into O_RollCall, skipping existing records:
CREATE OR REPLACE PROCEDURE merge_rollcall_data AS
-- Declare variables
n_id N_RollCall.student_id%TYPE;
n_name N_RollCall.student_name%TYPE;
n_birth_date N_RollCall.birth_date%TYPE;
v_count NUMBER;
-- Cursor declaration
CURSOR n_cursor IS
SELECT student_id, student_name, birth_date
FROM N_RollCall;
BEGIN
-- Open the cursor
OPEN n_cursor;
-- Start looping through cursor results
LOOP
-- Fetch data from cursor into variables
FETCH n_cursor INTO n_id, n_name, n_birth_date;
-- Exit loop if no more rows to fetch
EXIT WHEN n_cursor%NOTFOUND;
-- Check if the data already exists in O_RollCall
SELECT COUNT(*)
INTO v_count
FROM O_RollCall
WHERE student_id = n_id;
IF v_count = 0 THEN
-- Insert the record into O_RollCall
INSERT INTO O_RollCall (student_id, student_name, birth_date)
VALUES (n_id, n_name, n_birth_date);
END IF;
END LOOP;
-- Close the cursor
CLOSE n_cursor;
END merge_rollcall_data;
/
OUTPUT:
Procedure created.
Step 4: Execute the Stored Procedure
Finally, execute the merge_rollcall_data stored procedure to merge records from
N_RollCall into O_RollCall while skipping existing records:
Begin
merge_rollcall_data;
End;
OUTPUT:
Statement Processed.
Step 5: Verify Records in O_RollCall
select * from O_rollcall;
