how to generate primary key values while inserting data into table through pl/sql stored procedure

Question

I need to insert data into particular table through pl/sql stored procedure. My requirements are:

• while inserting it should generate PRIMARY KEY values for a particular column;
• it should return that PRIMARY KEY value to an output variable; and
• for another column it should validate my string such that it should contain only characters, not integers.

Answer 1

In oracle I believe the "identity" column is best achieved with a sequence and an insert trigger that checks if the primary key columns is null and if so gets the next sequence and inserts it.

you can then use the "returning" clause to get the newly created primary key:

insert into <table> (<columns>) values (<values>) returning <prim_key> into <variable>;

the filtering of the string field I would personally handle in code before going to the database (if that is a possibility). Databases are notoriously inefficient at handling string operations.

Answer 2

You can generate primary key values as a surrogate key using an Oracle SEQUENCE. You can create a constraint on a column that uses TRANSLATE to check that no numeric digits exist in newly inserted/updated data.

Some example code, suitable for SQL*Plus:

CREATE SEQUENCE mysequence;
/

CREATE TABLE mytable (
   pkidcol NUMBER PRIMARY KEY,
   stringcol VARCHAR2(100)
);
/

ALTER TABLE mytable ADD (
   CONSTRAINT stringnonnumeric
   CHECK (stringcol = TRANSLATE(stringcol,'A0123456789','A'))
);
/

DECLARE
   mystring mytable.stringcol%TYPE := 'Hello World';
   myid     mytable.pkidcol%TYPE;
BEGIN    
   INSERT INTO mytable (pkidcol, stringcol)
   VALUES (mysequence.NEXTVAL, mystring)
   RETURNING pkidcol INTO myid;
END;
/