[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4208
  • Last Modified:

How to auto increment in pl sql collumn (LIKE SQL we have identity like wise in pl sql what ?)


How to auto increment in pl sql collumn (LIKE SQL we have identity like wise in pl sql what ?)

EXample i have emp table i have three column empid,empname,empaddress

I need to this empid column autoincerment.

In my pl sql .

0
mcanetvenkat
Asked:
mcanetvenkat
1 Solution
 
Ivo StoykovCommented:
Hello mcanetvenkat,

you have to use sequence for this

HTH

I
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
How to auto increment in pl sql collumn (LIKE SQL we have identity like wise in pl sql what ?)


CREATE TABLE TEST
 ( ID NUMBER(4) PRIMARY KEY
 , DESCRIPTION VARCHAR2(50)
 )


CREATE SEQUENCE SEQ_TEST_ID START WITH 1 INCREMENT BY 1;
 

CREATE OR REPLACE TRIGGER TRG_TEST_ID
BEFORE INSERT
ON TEST
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT SEQ_MNP_ID.nextval INTO :NEW.ID  FROM dual;
END;
/
 
0
 
WallaceAdrianCommented:
You could create a sequence and use the sequence directly in your insert statement

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

  INSERT INTO emp( empid, empname, empaddress )
  VALUES ( emp_seq.NEXTVAL, 'Name','Address' );
0
 
AnnieModCommented:
Force accepted

AnnieMod
Cleanup Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now