Primary keys play an important role in the database world. Based on them you can clearly identify a record. Most of the time, a primary key is defined as a number, which is filled via a sequence. This can be done automatically in a trigger:
--create a test table
create table TBL_TEST_SEQ
(
id NUMBER PRIMARY KEY,
text VARCHAR2(255)
);
--create a sequence for the PK
create sequence MY_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1;
-- create the trigger to fill the PK
create or replace trigger tri_tbl_test_seq before insert on TBL_TEST_SEQ
for each row
begin
:new.id := my_seq.nextval;
end tri_tbl_test_seq;
Since Oracle 12.1 you do not need to explicitly create a sequence and the trigger. All you have to do is define the Primary Key column with the clause generated as identity in the table DDL (This option has existed for long time in MySQL).
create table TBL_TEST_SEQ
(
id number generated always as identity,
text varchar2(255)
)
With the following query you can see, that oracle has automatically created a sequence to fill the primary key:
select * from user_tab_identity_cols where table_name = 'TBL_TEST_IDENTITY'
After an insert, the column is filled implicitly. To still get the generated id, Oracle offers the „returnuning into“ clause:
declare
v_id number;
begin
insert into TBL_TEST_SEQ (text) values ('hello world') returning id into v_id;
dbms_output.put_line(v_id);
end;
This is just one use case. In general you can retrieve all columns of the table:
drop table tbl_test purge;
-- create a table tbl_test
create table tbl_test
(
id number generated always as identity,
text varchar2(255),
text2 varchar2(255) default 'text2 default'
);
declare
v_txt varchar2(255);
begin
insert into tbl_test(text) values ('hello world') returning text2 into v_txt;
dbms_output.put_line('Output: ' || v_txt);
end;
Output: text2 default
Another interesting use case is after a delete or update statement:
declare
v_txt varchar2(255);
begin
delete from tbl_test where id = 1 returning text into v_txt;
dbms_output.put_line(v_txt);
end;
If multiple records are affected by insert or update, you can retrieve the column values into collection:
eclare
v_txts sys.odcivarchar2list ;
begin
delete from tbl_test returning text bulk collect into v_txts;
for i in 1..v_txts.count loop
dbms_output.put_line(v_txts(i));
end loop;
end;
Returning bulk collect into for Insert Statement:
declare
v_ids sys.odcinumberlist;
begin
forall i in 1..10
insert into tbl_test (text) values( 'hello welt ')
returning id bulk collect into v_ids;
for i in 1..v_ids.count loop
dbms_output.put_line(v_ids(i));
end loop;
end;
But what I miss is when using „insert + select from„. It seems that something like this is not implemented for some reason. Of course, there are other restrictions. For example, this does not work with parallel processing.
Ahmed Fikri 