Re: Stored procedure

>
> Assuming I have set up a sequence called 'seq', and set the default value of
> id in foo to be nextval('seq'), then the following is getting close to what
> I need (there seems to be only one thing left - what do I replace the
> question mark with in order to get the id value from the initial select and
> pass it to the insert in the first block):
>
> if exists(select id from foo where x = "text") then
> INSERT INTO foo2 (foo_id, foo2_text) VALUES (?,"more_text")
> else
> INSERT INTO foo (text) VALUES ('text')
> INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
> "more_text")
> end if;
>
> The second block of the conditional statement looks like it ought to
> properly handle inserting new data into foo, autoincrementing id in foo and
> providing the value of id to the insert into foo2. However, for the first
> block, there is no way to know where 'text' is located in the table, so it
> is necessary to get the value of id from the SQL statement used as the
> argument for exists() and pass it to the insert into foo2 (where the
> question mark is located).
>
> Thanks for your time.
>
> Ted
>

maybe you can rewrite this to something else:

in the declare section declare a var

declare
var1 foo.id%TYPE;

[...and then in the begin section, where all code happens...]

select into var1 id from foo where x = "text";
if var1 is not null then
INSERT INTO foo2 (foo_id, foo2_text) VALUES (var1,"more_text")
else
INSERT INTO foo (text) VALUES ('text')
INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
"more_text")
end if;

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)