| How to get the new ID after insert a record? [message #17026] |
Tue, 21 December 2004 23:41  |
rushing
Messages: 135 Registered: January 2004 Location: Beijing,China
|
Senior Member |
|
|
Using a sequence and trigger to create records' ID automatually, how to get the ID after insert a record?
For example,
create table tb1 ID int,name varchar(20);
(ID is created by trigger)
insert into tb1 (name) values ("a");
How to get the ID of this new record? We have to consider that there are many users on several computers to insert at the same time.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get the new ID after insert a record? [message #17089 is a reply to message #17063] |
Thu, 23 December 2004 13:14  |
 |
Barbara Boehmer
Messages: 9106 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your question is unclear. bind_var1 is just a variable name. You could name it anything. The select statement using the reuturning clause returns the tb1.id generated by the sequence into whatever variable you include in the returning clause. That variable can be a local variable in a procedure or function or an anonymous pl/sql block or an ouput parameter of a procedure or a host variable or whatever you pass to it from Visual Basic. Here is an example of a procedure that would return the tb1.id into an output parameter of a procedure:
CREATE OR REPLACE PROCEDURE insert_tb1
(p_name IN tb1.name%TYPE,
p_id OUT tb1.id%TYPE)
AS
BEGIN
INSERT INTO tb1 (name) VALUES (p_name) RETURNING id INTO p_id;
END insert_tb1;
/
Perhaps it would help if you said what you want to do with the value of tb1.id once you get it.
If you were to execute the procedure above, you would still need to have some variable to receive the value of the output parameter. For example, in SQL*Plus:
VARIABLE some_variable NUMBER
EXECUTE insert_tbl ('a', :some_variable)
PRINT some_variable
You need to figure out what the corresponding things are in Visual Basic.
|
|
|
|