How do you implement auto incremented sequence values in Oracle PL/SQL?

Question

In a Oracle database table, how are auto incremented sequence values done with PL/SQL such as for a key value or id typed columns?

Included is a discussion on sizing table resources based on what you know about the projected growth of all the related tables in a given schema. How many piano tuners are there in the city of Chicago? Probably less than the population of the city altogether... :) and so on.

Know your data.

How do I do that? Read on.

Answer 1

Using Database Triggers to Auto Increment Column Values

One possible approach is to use database triggers. The values are ensured unique through the implementation of sequence typed database objects.

In the example above, the table FUNDRAISER has an associated sequence called FUNDRAISER_SEQ for a primary key value.

The Parts List: What you need for the example.

CREATE TABLE  "FUND_PEOPLE" 
   (    "PERSON_ID" NUMBER NOT NULL ENABLE, 
        "ANONYMOUS_IND" VARCHAR2(1) NOT NULL ENABLE, 
        "ALIAS_IND" VARCHAR2(1) NOT NULL ENABLE, 
        "ALIAS_NAME" VARCHAR2(50), 
        "FIRST_NAME" VARCHAR2(50), 
        "LAST_NAME" VARCHAR2(50), 
        "PHONE_NUMBER" VARCHAR2(20), 
        "EMAIL_ADDRESS" VARCHAR2(100), 

     CONSTRAINT "FUND_PEOPLE_PK" PRIMARY KEY ("PERSON_ID") ENABLE
   ) ;

This is an older sample of code, but it the

SELECT FROM ... INTO DUAL

Construct was what we did with older releases of the Oracle Database. Release 11g changes that so that sequence increments can be assigned directly to a PL/SQL variable or a SQL statement call. Both will work.

Challenge yourself to figure out what the revised PL/SQL might look like...

CREATE OR REPLACE TRIGGER  "BI_FUND_PEOPLE" 
  before insert on "FUND_PEOPLE"               
  for each row  
begin   
  if :NEW."PERSON_ID" is null then 
    :NEW."PERSON_ID" := "FUND_PEOPLE_SEQ".nextval; 
  end if; 
end; 

The notation is:

1. "BI" for Before INSERT
2. :NEW for the value of the column value after the event.

FUND_ID is the column that is ignored in the insert statement. The starting value of each sequence is 20. A call to the SEQ.NEXTVAL value increments the sequence by the amount identified in the sequence declaration.

A Brief Discussion on Sequence Sizing

If you spec a sequence without a MAXIMUM value, the system assigns the largest possible integer handled by the database, which is 20+ digits long.

Now what are we going to do with a table that contains records numbering twenty or more orders of magnitude: "9999999999999999999999999999"?

Hmmmm... I've heard of "defensiveness in coding", but for most applications, this is... in defensible?

Consider looking at the original project design I have worked with on Google Code: Google Code Project: Fundraiser Organizer

This is the rough relation in record counts between each entity in the schema I set up. The two circles are metrics which are proportional to what values they calculate or aggregate. Considering the data cases, what are the relative magnitudes for each count of records?

A look at the first screenshot shows my estimates. This project started out as a sheet of data to keep track of an fund raising drive. Hope you find it informative and thought inspiring!

The UI and my testing efforts were conducted on a public demo site hosted by Oracle at Oracle Apex Demo System