Create a PL/SQL procedure object in your schema. Using a Cursor the procedure code should be able to retrieve records of all employees and check in which city they live. If an employee lives in Glasgow the procedure should insert a record in the table GlasgowEmployees.

Question

Assume that the following tables are part of a Library database system
Employee(empNo, fName, lName, street, city, sex, salary, libName)
GlasgowEmployees(gEmpNo, fName, lName, sex, salary)

Answer 1

CREATE OR REPLACE PROCEDURE Glasgow_Members
IS
-- Declare a Cursor
CURSOR C1 IS
SELECT * FROM Employee;
BEGIN
FOR emp_rec IN C1 LOOP
IF (emp_rec.city = 'Glasgow') THEN
INSERT INTO GlasgowEmployees values (emp_rec.empNo,
emp_rec.fName, emp_rec.lName, emp_rec.sex, emp_rec.salary);
END IF;
END LOOP;
END;