Call A Sequence To Generate Unique Id With In A Procedure

First Create A Sequence:-

CREATE SEQUENCE seq
    INCREMENT BY 10
    START WITH 1
    MAXVALUE 10000
    NOCACHE
    NOCYCLE;

Now Create A procedure And Use This Sequence:-

create or replace procedure ST_INSERT(
                                       ST_NAME VARCHAR,
                                       ST_BRANCH VARCHAR )
 as
begin
insert into STUD (STUDENT_ID,NAME,BRANCH)
          values ('CDS'||SEQ.NEXTVAL,ST_NAME,ST_BRANCH );
     end ST_INSERT;
     /

People who read this post also read :



5 comments:

Use TYPE or ROWTYPE instead of VARCHAR to avoid buffer overflow.

In a multiuser environment you'll never know wich ID was taken, use RETURNING clause on statement.

A FUNCTION will fit better for this, returning unique ID or SQLCODE.

Hope this helps you...

Actually... It is VERY common to do this sort of thing in a Before Insert DB Trigger.
I do this all the time for just about all of my tables.
However, I usually do...
CREATE SEQUENCE seq
START WITH 1
INCREMENT BY 1
ORDER
NOCACHE
NOCYCLE;

The use of ORDER NOCACHE is really bad practice. It's about as slow as you can go. You should at least use NOORDER CACHE 100.

I would suggest moving away from sequences and use GUIDs instead:

create table t (
id raw(16) default sys_guid() primary key,
other_columns... );

This avoids having any PL/SQL or triggers at all. And it guarantees that all ID's across all databases will be unique.

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More