Auto increment function, Oracle PL/SQL

Question

I have a number of triggers which do various things, but at the top of all of those which have unique integer primary keys, I need them to auto increment. The code currently looks as follows:

IF :NEW.student_id IS NULL THEN
    SELECT seq_student_id.nextval
    INTO :NEW.student_id
    FROM sys.dual;
END IF;

I thought it may be useful to make a small function for this, as I would if this were a conventional programming language. However, I'm unsure how to pass the arguments to where they need to go to allow the function to run correctly. Ideally, I'd just like to be able to call AUTO_INCREMENT(student_id) at the top, and have something like the following run (does not currently work):

create or replace function "AUTO_INCREMENT"
(field in VARCHAR2)
return BOOLEAN
is
begin
    IF ':NEW'.field IS NULL THEN
        SELECT 'seq_'||field.nextval
        INTO :NEW.field
        FROM sys.dual;
    END IF;
return true;
end;

Any help into getting this functionality working the way I would expect would be much appreciated. I'm new to PL/SQL and I think this would serve as a good introduction to functions (if this is where I should be using them). Thanks.

What reason is there to have a function at all? Why would you want to _not_ overwrite whatever someone is entering, ever? [If you're creating a surrogate key you need to ensure that the users don't use up values that you haven't yet.](http://stackoverflow.com/questions/17880185/advantage-of-using-a-trigger-to-populate-surrogate-key-in-oracle-plsql/17880500#17880500) — Ben, Nov 18 '13 at 16:20
A problem with this approach is that you lose the ability to use Oracle's data dictionary to see if a sequence is being used, and where. — Jeffrey Kemp, Nov 19 '13 at 05:20

Younes · Accepted Answer · 2013-11-18T16:42:47.867

This is two questions in one:

1) Solving your problem

You don't need the whole if at all. You can do the following one liner which shorter than passing this to a function(EDIT: Thanks to Ben's comment below, I've replaced NVL with COALESCE):

:NEW.student_id := COALESCE(:NEW.student_id, seq_student_id.nextval);

Answering your question, I don't think what you are asking for is possible because you are setting the value of :NEW.field from outside the trigger. In order to do do this, you must pass an OUT paramter variable to store the caculated new value or return the calculated value when you call the function. In either cases, you must capture the new value from within your trigger into a variable or directly to :NEW.field.

2) Writing dynamic sql statements:

You can pass the field name to a function as a string like this from the trigger:

:NEW.field := auto_increment('field');

then, in your auto_increment function, you can use EXECUTE IMMEDIATE to write dynamic sql statements.

Don't use `NVL()` in this situation; it'll [evaluate the sequence](http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce) every time, for no reason. It's better to use `COALESCE()`. — Ben, Nov 18 '13 at 16:19

Auto increment function, Oracle PL/SQL

1 Answers1