Scribd
Upload
82 views

Prgm5 & PRGM 6

Uploaded by

Gowrish HM
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
82 views

Prgm5 & PRGM 6

Uploaded by

Gowrish HM
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

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;

You might also like