How to use Sequences In SQL
Sequences are objects in PL/SQL that generate unique numeric values. They are particularly useful for auto-incrementing IDs in tables.
Syntax:
CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1;
Example:
SET SERVEROUTPUT ON;
CREATE SEQUENCE player_seq MINVALUE 1 START WITH 1
INCREMENT BY 1 NOCACHE ;
CREATE TABLE PLAYER1(ID NUMBER PRIMARY KEY , NAME VARCHAR(30), SCORE NUMBER );
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Ellyse Perry', 347);
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Smriti Mandhana', 300);
INSERT INTO PLAYER1(ID, NAME, SCORE) VALUES (player_seq.nextval, 'Shafali Verma', 309);
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Deepti Sharma', 295);
INSERT INTO PLAYER1 (ID, NAME, SCORE) VALUES (player_seq.nextval, 'Harmanpreet Kaur', 268);
SELECT * FROM PLAYER1;
Output:
Auto Increment in PLSQL
Explanation:
- SET SERVEROUTPUT ON; is used to display the output generated from the code.
- player_seq is the sequence created with a start value of 1 and with an increment value of 1.
- In the Insert query player_seq.nextval is used to auto increment the id.
How to Create id With AUTO_INCREMENT in PL/SQL?
PL/SQL, short for Procedural Language/Structured Query Language, combines SQL capabilities with procedural programming. It supports variable declaration, control flow structures, functions, records, cursors, procedures, and triggers.
PL/SQL features a block structure with optional sections for variable declarations and exception handling. Its execution section handles SQL queries seamlessly. Overall, PL/SQL offers a robust environment for developing database-centric applications with enhanced control and error-handling capabilities.