Advertisement
ovalerio

Creating a sequence for exisiting non empty table in Oracle

Oct 10th, 2019
1,231
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Defining an auto incrementing sequence for an already existing table using the
  2. -- max value present on the table to define the start value of the sequence.
  3. -- This script uses a copy of the departments table and define a sequence for the department_id field  
  4. SET serveroutput ON;  -- enabling DBMS_OUTPUT which is turned off by default.
  5.  
  6. -- Cleaning up in case the script has been run before.
  7. DROP TABLE dept_copy;
  8. DROP SEQUENCE seq_dep_deptid;
  9.  
  10. -- Creating a copy of departments from HR
  11. CREATE TABLE dept_copy AS SELECT * FROM departments;
  12.  
  13. -- Creating the sequence to use every time rows are added into dept_copy
  14. -- Necessary to use the procedure since start values of sequences cannot
  15. -- be set using the result of a query
  16. -- NOTE: Department id values are multiples of 10. Same convention is used by the sequence.
  17. DECLARE
  18.   max_deptid NUMBER;
  19. BEGIN
  20.   SELECT MAX(department_id) INTO max_deptid FROM dept_copy;
  21.   IF SQL%FOUND THEN
  22.     DBMS_OUTPUT.PUT_LINE('Max Dept Id: ' || max_deptid );
  23.   END IF;
  24.   EXECUTE IMMEDIATE 'CREATE SEQUENCE seq_dep_deptid START WITH '|| TO_CHAR(max_deptid + 10) ||' INCREMENT BY 10 NOCYCLE';
  25. END;
  26.  
  27. -- Defininig a trigger for table dept_copy that is used every time a row is added into the table
  28. CREATE TRIGGER dept_copy_trigger
  29.   BEFORE INSERT ON dept_copy
  30.   FOR EACH ROW
  31. BEGIN
  32.   :NEW.department_id := seq_dep_deptid.NEXTVAL;
  33. END;
  34.  
  35. -- Testing inserting some values
  36. INSERT INTO dept_copy(department_name, manager_id, location_id) VALUES ('Mathematics', 100, 1700);
  37. INSERT INTO dept_copy(department_name, manager_id, location_id) VALUES ('Chemistry', 103, 1400);
  38. INSERT INTO dept_copy(department_name, manager_id, location_id) VALUES ('Linguistics', 100, 1700);
  39.  
  40. -- Displaying contents of dept_copy
  41. SELECT * FROM dept_copy
  42. ORDER BY department_id DESC;
Advertisement
RAW Paste Data Copied
Advertisement