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.

What is Auto-Increment in PL/SQL?

Sometimes while creating a table we do not have a unique identifier within the table hence we face difficulty in choosing the Primary Key. To resolve such an issue, we’ve to provide unique keys to every record manually, but this is often also a tedious task. So we can use the Auto Increment feature that automatically generates a numerical Primary key value for every new record inserted. To use the auto-increment feature we have to make the auto-increment field with the sequence object which successively generates several sequences.

Syntax

CREATE SEQUENCE sequence_name MINVALUE 1 START WITH 1
INCREMENT BY 1
CACHE 10; 

In the above syntax,

• sequence_name: Refers to the name of the sequence object we would like to produce.
• START WITH: Followed by the starting value that we would like to use. Here we have provided 1 as the starting value.
• INCREMENT BY: Followed by the value by which you want to increment. Here we want to increase the subsequent keys by 1.
• CACHE: Followed by the maximum number of values to be stored for faster access.

How to Create Auto Incrementing IDs in PL/SQL

When working with PL/SQL, generating unique IDs for database records can be cumbersome. To streamline this process, two methods are commonly employed: using sequences or triggers.

1. Using Sequences

Sequences are objects in PL/SQL that generate unique numeric values. They are particularly useful for auto-incrementing IDs in tables. Here is how you can create and use sequences to achieve auto-incrementing IDs:

Syntax

CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1;

Example of Using Sequences:

Output:

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.

2. Using Trigger to AUTO_INCREMENT id in Pl/SQL

PL/SQL triggers are block structures and predefined programs invoked automatically when some event occurs. They are stored in the database and invoked repeatedly in a particular scenario. To auto increment the ID, we will use BEFORE conditional trigger so that it is activated as the user try to insert the row into the table.

Syntax

CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGINSELECT sequence_name.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

• Before Conditional trigger is defined to activate as the new rows are inserted in the table.
• sequence_name.NEXTVAL INTO :NEW.ID is used to auto-increment the id.
• FROM DUAL is a automatically created table used to execute function or operation which do not depend on any table data.

Example of Using Triggers:

Output:

Explanation:

• SET SERVEROUTPUT ON; is used to display the output generated from the code.
• play_id is the sequence created with a start value of 1 and with an increment value of 1.
• Player1 table is created with ID,NAME nad Score field.
• Trigger is defined with before insert effect to auto increment the id.
• As the rows are inserted, id_trigger is triggered and the id field is auto incremented.

Conclusion

In conclusion, we explored two methods to auto-increment IDs in PL/SQL tables: using sequences and triggers. Both approaches offer efficient ways to manage unique identifiers and streamline database operations. While sequences offer simplicity and ease of use, triggers provide greater flexibility and control over the insertion process.