Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Insert Into (Oracle with TOAD)
icon4.gif  Procedure Insert Into [message #447251] Fri, 12 March 2010 15:05 Go to next message
victor_vsnet
Messages: 1
Registered: March 2010
Location: PERU
Junior Member
Hello everyone, I'm trying to make a stored procedure in Oracle insertcion of records, but before you insert has to get the most code and generate a new one generated more than everyone else, I'm using Max, but as I assign to a variable as in SQL is:
Declare @ IDMax numeric
Select @ IDMax = Max (Code) From Members
Then I would make a:
Insert into Users (Code, Name) values (@ IDMax, 'Victor');
As serious for Oracle to perform com from declaring a parameter as the Code for me to store the value (Code Maximo)

Thank's

[mod-edit] color and font removed to make it "more" readable.

[Updated on: Mon, 15 March 2010 08:09] by Moderator

Report message to a moderator

Re: Procedure Insert Into [message #447252 is a reply to message #447251] Fri, 12 March 2010 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

code you posted is not Oracle compliant.

I suggest you use SEQUENCE; not MAX

You way could fail in a multi-user environment.
Re: Procedure Insert Into [message #447287 is a reply to message #447251] Sat, 13 March 2010 00:37 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Outside the procedure (only once like create table):
create sequence seq_id;


In your procedure:
Insert into Users (Code, Name) values (seq_id.nextval, 'Victor');

Regards
Michel

[Updated on: Sat, 13 March 2010 00:43]

Report message to a moderator

Previous Topic: to left align the number fields
Next Topic: Interval Partitioning and NULL Column Values
Goto Forum:
  


Current Time: Sun Sep 25 19:42:29 CDT 2016

Total time taken to generate the page: 0.06974 seconds