Oracle executes an INSERT statement in a function or stored procedure and returns the primary key ID value

Sometimes we need to insert a record into a table and return the primary key ID value.

Assume that the value of the primary key ID is obtained through the sequence of the corresponding table, and then the ID is assigned

Here are a few things to note:

1) If the Build Table statement contains the primary key ID of the trigger, through the trigger to achieve the primary key ID self-increment, the implementation of the following way:

INSERT  intoGp_month_bill (MONTH, Bonus_value, Cur_way, Cur_time, STATUS, Is_use, Create_time)VALUES(Cur_month, Cur_bonus_value,'Package Function Curing', Sysdate,'in Curing',             1, Sysdate) Returning ID intocur_month_bill_id;--cur_month_bill_id is a variable that receives the returned ID value

Key syntax: INSERT into TABLE VALUES (,) returning IDs into VAL

Supplement: In this case, the statement of the trigger may be as follows

CREATE OR REPLACE TRIGGER  Insert on "GPS". " Em_files " for each rowbegin  Selectto from  dual; End ; /

Or

ORALCE12C, the primary key is set sequence the next value by default

CREATE TABLEC # #EM. Gp_target_type (ID Number( +)DEFAULT"C # #EM". Iseq$$_124895". Nextval not NULL,--default defaults to the next value of SEQNAME NVARCHAR2 ( -) not NULL, REMARK NVARCHAR2 ( $), Create_time DATE not NULL, Create_uid Number( +), Delete_uid Number( +), Delete_time DATE, Is_delete Number(1) not NULL, Update_time DATE, Update_uid Number( +))

2) If the value of the primary key ID is obtained by the user's own sequence value, then the assignment can be implemented as follows:

SELECT Your_seq. Nextval  into from--mid_id as variable INSERT into TABLEVALUES(mid_id);

Supplement: In this case, there can be triggers, and the statements may be as follows:

CREATE OR REPLACE TRIGGERC # #WL. Tr_bulletin beforeINSERT onC # #WL. Wl_bulletin forEach ROW when(New.id is NULL ORNew.id= 0      )BEGIN     SELECTWl_bulletin_seq. Nextval into: new.id fromDUAL; END;/

The current inserted primary key ID is a null value or 0 o'clock, get the nextval value of the corresponding sequence to assign a value to the primary key ID

Oracle executes an INSERT statement in a function or stored procedure and returns the primary key ID value