|
Your organization has
a lot of duplicated records in their Account
Employee table. This is a daily problem in any
organization and you should be prepared to fix the
problem. You have be assigned to clean all of the
duplicated records.
-- Hands-On 04
(Troubleshooting Deleting duplicated records)
SET ECHO ON
CLEAR SCR
-- Begin by logging in as the oracle user.
--
pause
CONNECT oracle/learning
pause
CLEAR SCR
-- Set the linesize to 100 and the pagesize to 55.
--
pause
SET LINESIZE 100 PAGESIZE 55
pause
CLEAR SCR
-- First, let's create a table with lots of
duplicated
-- records.
-- Create a table named dup_emp and copy all of the
EMP's
-- records into it.
pause
CREATE TABLE dup_emp
AS (SELECT * FROM emp)
/
pause
CLEAR SCR
-- Query the dup_emp table.
--
pause
SELECT * FROM dup_emp
/
pause
CLEAR SCR
-- Insert all the accounting department rows into
the dup_emp table.
--
pause
INSERT INTO dup_emp
SELECT * FROM emp WHERE deptno = 10
/
commit;
pause
CLEAR SCR
-- Query all the duplicated records from
-- the dup_emp table order by the employee name.
--
pause
SELECT * FROM dup_emp
WHERE deptno = 10
ORDER BY ename
/
-- Notice that all of the employees of department 10
-- have been duplicated.
pause
CLEAR SCR
-- Write a procedure to delete all of the
-- duplicated records.
pause
DECLARE
-- Get the unique empno.
CURSOR c_empno IS
SELECT DISTINCT empno AS empno FROM dup_emp;
-- Get all duplicated records for an employee.
CURSOR c_duprec (p_empno NUMBER) IS
SELECT rowid FROM dup_emp WHERE empno = p_empno;
first_one BOOLEAN;
BEGIN
FOR v_empno IN c_empno LOOP
first_one := TRUE;
FOR v_duprec IN c_duprec (v_empno.empno) LOOP
IF NOT first_one THEN
DELETE FROM dup_emp
WHERE rowid = v_duprec.rowid;
COMMIT;
END IF;
first_one := FALSE;
END LOOP;
END LOOP;
END;
/
pause
CLEAR SCR
-- Query again the dup_emp table order by the
department
-- and employee number.
-- Then check for duplication.
--
pause
SELECT * FROM dup_emp
WHERE deptno = 10
ORDER BY ename
/
-- Notice that there are no duplicated records.
pause
CLEAR SCR
-- Or you could write a delete statement to
-- remove all duplicated records.
-- In this time, you did not have any duplicated
-- record to be deleted.
pause
DELETE FROM dup_emp
WHERE ROWID IN (SELECT MAX(ROWID) FROM dup_emp
GROUP BY empno
HAVING COUNT (empno) > 1)
/
commit;
pause
CLEAR SCR
-- Drop the dup_emp table.
--
pause
DROP TABLE dup_emp
/
pause
CLEAR SCR
-- Now, practice this exercise over and over
-- until you become a master at it.
-- For more information about the subject, you are
encouraged
-- to read from a wide selection of available books.
-- Good luck!
pause |
