Member Avatar for richardzhangss

Hi,
Here is my mysql sentence:

select last_insert_id() from pp;

and the table pp is created by:

create table pp(id int not null auto_increment primary key,name varchar(255) not null);

Now I'd like to change the above into sentences that are right in Oracle.
First I create table:

create table pp(id integer not null primary key,name varchar2(255) not null);

then the sequence:

create sequence pp_sequence  start with 1 increment by 1;

and at last the trigger:

CREATE OR REPLACE TRIGGER pp_TRIGGER
BEFORE INSERT
ON pp
REFERENCING NEW AS NEW
FOR EACH ROW 
BEGIN
SELECT pp_sequence.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

And what else should I do to change the sentence 'select last_insert_id() from pp;' to be used in Oracle?
I'm a newbie ,
Any help will be greatly appreciated!

  • 2 Contributors
  • 1 Reply
  • 251 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by jwenting
Member Avatar for jwenting

why would you ever need to know the previous value emitted by a sequence?
It's unlikely to remain the same for long enough to be of any use, especially in databases that see a lot of activity.

But if you insist, you can find the answer on page 3-3 of the Oracle 11g SQL language reference.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.