1-20: Basic Sequence Creation & Usage
-- 1. Create a simple sequence starting at 1 increment by 1
CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1;
-- 2. Create sequence with max and min values, cycling enabled
CREATE SEQUENCE seq_order_id START WITH 1000 INCREMENT BY 5 MAXVALUE 100000 MINVALUE 1000 CYCLE;
-- 3. Create sequence without max value (no max limit)
CREATE SEQUENCE seq_no_max START WITH 1 INCREMENT BY 1 NOMAXVALUE;
-- 4. Create sequence with cache of 20 for performance
CREATE SEQUENCE seq_cached START WITH 1 INCREMENT BY 1 CACHE 20;
-- 5. Create sequence without cache
CREATE SEQUENCE seq_nocache START WITH 1 INCREMENT BY 1 NOCACHE;
-- 6. Create sequence with guaranteed order in RAC (ORDER)
CREATE SEQUENCE seq_ordered START WITH 1 INCREMENT BY 1 ORDER;
-- 7. Get the next sequence value
SELECT seq_emp_id.NEXTVAL FROM dual;
-- 8. Get current sequence value (must call NEXTVAL before)
SELECT seq_emp_id.CURRVAL FROM dual;
-- 9. Use sequence in INSERT statement
INSERT INTO employees(emp_id, emp_name) VALUES (seq_emp_id.NEXTVAL, 'Alice');
-- 10. Use sequence in PL/SQL block
DECLARE
v_id NUMBER;
BEGIN
SELECT seq_emp_id.NEXTVAL INTO v_id FROM dual;
DBMS_OUTPUT.PUT_LINE('New ID: ' || v_id);
END;
/
-- 11. Drop a sequence
DROP SEQUENCE seq_emp_id;
-- 12. Alter sequence increment value
ALTER SEQUENCE seq_emp_id INCREMENT BY 10;
-- 13. Create descending sequence (decrementing)
CREATE SEQUENCE seq_desc START WITH 1000 INCREMENT BY -1 MINVALUE 1 NOCYCLE;
-- 14. Use sequence in SELECT query for multiple rows
SELECT seq_emp_id.NEXTVAL, emp_name FROM employees WHERE ROWNUM <= 5;
-- 15. Create sequence with NO CYCLE (default)
CREATE SEQUENCE seq_no_cycle START WITH 1 INCREMENT BY 1 NOCYCLE;
-- 16. Create cycling sequence wrapping at maxvalue 10
CREATE SEQUENCE seq_cycle START WITH 1 INCREMENT BY 1 MAXVALUE 10 CYCLE;
-- 17. Check sequence details in USER_SEQUENCES
SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name = 'SEQ_EMP_ID';
-- 18. Use sequence in MERGE UPSERT statement
MERGE INTO employees e USING dual ON (e.emp_id = 123)
WHEN NOT MATCHED THEN INSERT (emp_id, emp_name) VALUES (seq_emp_id.NEXTVAL, 'Bob');
-- 19. Use sequence in trigger to auto-generate primary key
CREATE OR REPLACE TRIGGER trg_emp_id BEFORE INSERT ON employees
FOR EACH ROW BEGIN
:NEW.emp_id := seq_emp_id.NEXTVAL;
END;
/
-- 20. Create sequence with NOCACHE and NOORDER
CREATE SEQUENCE seq_no_cache_no_order START WITH 1 INCREMENT BY 1 NOCACHE NOORDER;
________________________________________
21-40: Intermediate & Practical Usage
-- 21. Sequence with cache and cycle
CREATE SEQUENCE seq_cache_cycle START WITH 1 INCREMENT BY 1 MAXVALUE 1000 CYCLE CACHE 50;
-- 22. Format sequence number with leading zeros
SELECT LPAD(seq_emp_id.NEXTVAL, 6, '0') FROM dual;
-- 23. Reset sequence by dropping and recreating
-- 24. Grant sequence usage to another user
GRANT SELECT ON seq_emp_id TO hr;
-- 25. Revoke sequence usage from user
REVOKE SELECT ON seq_emp_id FROM hr;
-- 26. Use sequence in UPDATE statement (uncommon but possible)
UPDATE employees SET emp_id = seq_emp_id.NEXTVAL WHERE emp_name = 'Carol';
-- 27. Create sequence with NOORDER (RAC performance)
CREATE SEQUENCE seq_no_order START WITH 1 INCREMENT BY 1 NOORDER;
-- 28. Using CURRVAL before NEXTVAL generates error - handle with PL/SQL block
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE(seq_emp_id.CURRVAL);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('CURRVAL not defined until NEXTVAL is called');
END;
END;
/
-- 29. PL/SQL function returning sequence number
CREATE OR REPLACE FUNCTION get_next_id RETURN NUMBER IS
v_id NUMBER;
BEGIN
SELECT seq_emp_id.NEXTVAL INTO v_id FROM dual;
RETURN v_id;
END;
/
-- 30. Bulk insert with sequence in PL/SQL loop
DECLARE
v_id NUMBER;
BEGIN
FOR i IN 1..10 LOOP
v_id := seq_emp_id.NEXTVAL;
INSERT INTO employees(emp_id, emp_name) VALUES (v_id, 'Emp ' || i);
END LOOP;
COMMIT;
END;
/
-- 31. Query sequence settings in DBA_SEQUENCES
SELECT sequence_name, cache_size, cycle_flag FROM dba_sequences;
-- 32. Use NEXTVAL multiple times in one query (increments each call)
SELECT seq_emp_id.NEXTVAL, seq_emp_id.NEXTVAL FROM dual;
-- 33. Use sequence in VIEW definition (may cause issues with caching)
CREATE OR REPLACE VIEW v_emp_seq AS SELECT emp_name, seq_emp_id.NEXTVAL AS new_id FROM employees;
-- 34. Use sequence in inline view
SELECT emp_name, (SELECT seq_emp_id.NEXTVAL FROM dual) AS new_id FROM employees;
-- 35. Create sequence with cache and NOORDER
CREATE SEQUENCE seq_cache_noorder START WITH 1 INCREMENT BY 1 CACHE 20 NOORDER;
-- 36. Hack to reset sequence by altering increment and calling NEXTVAL
ALTER SEQUENCE seq_emp_id INCREMENT BY -1000;
SELECT seq_emp_id.NEXTVAL FROM dual;
ALTER SEQUENCE seq_emp_id INCREMENT BY 1;
-- 37. Use sequence in function to return formatted string
CREATE OR REPLACE FUNCTION formatted_emp_id RETURN VARCHAR2 IS
v_seq NUMBER;
BEGIN
SELECT seq_emp_id.NEXTVAL INTO v_seq FROM dual;
RETURN 'EMP_' || TO_CHAR(v_seq, 'FM0000');
END;
/
SELECT formatted_emp_id FROM dual;
________________________________________
41-60: Sequence Integration with Tables and Triggers
-- 41. Trigger for auto-generating order_id from sequence
CREATE OR REPLACE TRIGGER trg_order_id
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
:NEW.order_id := seq_order_id.NEXTVAL;
END;
/
-- 42. Trigger checks if ID is NULL before assigning sequence number
CREATE OR REPLACE TRIGGER trg_cust_id
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
IF :NEW.customer_id IS NULL THEN
:NEW.customer_id := seq_customer_id.NEXTVAL;
END IF;
END;
/
-- 43. Insert log record using sequence
INSERT INTO audit_log(log_id, event_time, event)
VALUES (seq_audit.NEXTVAL, SYSDATE, 'User login');
-- 44. Use sequence for surrogate keys in partitioned tables
-- 45. Bulk collect sequence values for batch inserts
DECLARE
TYPE t_id_tab IS TABLE OF NUMBER;
v_ids t_id_tab := t_id_tab();
BEGIN
FOR i IN 1..10 LOOP
v_ids.EXTEND;
v_ids(i) := seq_emp_id.NEXTVAL;
END LOOP;
FORALL i IN v_ids.FIRST..v_ids.LAST
INSERT INTO employees(emp_id, emp_name) VALUES (v_ids(i), 'Emp ' || i);
COMMIT;
END;
/
-- 46. Generate invoice number using sequence + current date in PL/SQL
-- 47. Use sequence to generate batch numbers for ETL
-- 48. Use sequence in MERGE for UPSERT
-- 49. Use sequence in global temporary tables
-- 50. Combine sequence with SYS_GUID for complex keys
-- 51. Insert multiple rows using sequence in single INSERT-SELECT
INSERT INTO employees(emp_id, emp_name)
SELECT seq_emp_id.NEXTVAL, 'Emp ' || LEVEL FROM dual CONNECT BY LEVEL <= 10;
-- 52. Use sequence for unique row identifiers in reporting
-- 53. Sequence in PL/SQL nested tables or collections
-- 54. Use sequence values in materialized views (limitations exist)
-- 55. Use sequence numbers in REF CURSORs
-- 56. Use sequences with autonomous transactions
-- 57. Combine sequence with SYS_GUID for hybrid keys
-- 58. Use sequence with foreign key maintenance triggers
-- 59. Use sequence for surrogate keys in data warehouse dimension tables
-- 60. Use sequence for surrogate keys in temporary tables
________________________________________
61-80: Sequence Maintenance, Monitoring, and Troubleshooting
-- 61. View sequence metadata
SELECT sequence_name, last_number, cache_size, cycle_flag FROM user_sequences WHERE sequence_name='SEQ_EMP_ID';
-- 62. Advance sequence by calling NEXTVAL repeatedly
BEGIN
FOR i IN 1..100 LOOP
SELECT seq_emp_id.NEXTVAL INTO v_dummy FROM dual;
END LOOP;
END;
/
-- 63. Detect gaps in sequence (application responsibility)
-- 64. Sequence cache loss on instance restart explanation
-- 65. Reset sequence by dropping and recreating
-- 66. Use CYCLE to avoid sequence exhaustion
-- 67. NOORDER usage in RAC environments
-- 68. Monitor sequences in Oracle Enterprise Manager
-- 69. Get sequence DDL using DBMS_METADATA
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'SEQ_EMP_ID') FROM dual;
-- 70. Export/import sequences using Data Pump
-- 71. Identify unused sequences
-- 72. Synchronize sequences across multiple databases
-- 73. Use sequences in PL/SQL packages for encapsulated ID generation
-- 74. Avoid collisions in distributed systems
-- 75. Recompile triggers using sequences
-- 76. Use sequences in parallel DML
-- 77. Monitor sequence contention (rare)
-- 78. Use sequences in JSON/XML document generation
-- 79. Handle CURRVAL errors with exception handling
-- 80. Use sequences in REST API ID generation
________________________________________
81-100: Miscellaneous and Advanced Uses
-- 81. Create synonym for sequence
CREATE SYNONYM seq_emp FOR seq_emp_id;
-- 82. Use sequences in hierarchical queries (CONNECT BY)
-- 83. Use sequence to generate partition keys
-- 84. Generate pseudo-random number using MOD and sequence
SELECT MOD(seq_emp_id.NEXTVAL, 100) + 1 AS random_num FROM dual;
-- 85. Emulate custom increments (like primes) with PL/SQL logic
-- 86. Use sequences with external tables
-- 87. Use sequence in dynamic SQL
-- 88. Use sequences in Oracle Apex primary keys
-- 89. Use sequences with JSON_TABLE function
-- 90. Use sequences in Oracle GoldenGate configuration
-- 91. Pass sequence values via REF CURSORs
-- 92. Use sequences in Data Pump job tagging
-- 93. Use sequences in file naming for exports
-- 94. Use session-specific sequences with temporary tables
-- 95. Use sequences in partition exchange operations
-- 96. Use sequences in Oracle Multitenant containers
-- 97. Use sequences with external Java/C procedures
-- 98. Use sequences in HTAP architectures
-- 99. Use sequences with Oracle Label Security rows
-- 100. Use sequences with DBMS_PIPE or DBMS_AQ messaging
No comments:
Post a Comment