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;
/
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;
/
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;
Thanks for your ideas
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.
Good reading your ppost
Post a Comment