| select into temporary variable on stored procedure for second insert (2 Merged) [message #501707] |
Wed, 30 March 2011 17:16  |
 |
BennyBoy76
Messages: 4
Registered: March 2011
Location: UK
|
Junior Member |
|
|
Hi there, at the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.
CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,
date_running IN excursion.date_running%TYPE,
description IN excursion.description%TYPE,
cruise_id IN excursion.cruise_id%TYPE,
excursion_id OUT excursion.excursion_id%TYPE)
AS
BEGIN
INSERT INTO EXCURSION VALUES(seq_EXCURSION_ID.nextval,min_places,max_places,
additional_charge,date_running,description,cruise_id);
SELECT MAX(excursion_id)
INTO excursion_id
FROM excursion;
END CREATE_EXCURSION;
/
I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword. Any advise about how to declare variables as runtime would be great as I'm new to Oracle.
Thanks..............Ben
[mod-edit: ending code tag fixed by bb; it was missing the /]
[Updated on: Wed, 30 March 2011 23:33] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: select into temporary variable on stored procedure for second insert [message #501713 is a reply to message #501709] |
Wed, 30 March 2011 19:11  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You don't need to declare a separate variable. You can just use the existing out parameter. You don't need to use a separate select statement. You can just use the returning clause in the insert statement. If you do use a local variable, just declare it like:
...
AS
v_excursion_id excursion.excursion_id%TYPE;
BEGIN
...
otherwise:
SCOTT@orcl_11gR2> CREATE TABLE excursion
2 (excursion_id NUMBER,
3 other_cols VARCHAR2 (15))
4 /
Table created.
SCOTT@orcl_11gR2> ALTER TABLE excursion
2 ADD CONSTRAINT excursion_id_pk
3 PRIMARY KEY (excursion_id)
4 /
Table altered.
SCOTT@orcl_11gR2> CREATE SEQUENCE seq_excursion_id
2 /
Sequence created.
SCOTT@orcl_11gR2> CREATE TABLE another_table
2 (id NUMBER)
3 /
Table created.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE create_excursion
2 (p_other_cols IN excursion.other_cols%TYPE,
3 p_excursion_id OUT excursion.excursion_id%TYPE)
4 AS
5 BEGIN
6 INSERT INTO excursion (excursion_id, other_cols)
7 VALUES (seq_excursion_id.NEXTVAL, p_other_cols)
8 RETURNING excursion_id INTO p_excursion_id;
9 --
10 INSERT INTO another_table VALUES (p_excursion_id);
11 END create_excursion;
12 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_excursion_id NUMBER
SCOTT@orcl_11gR2> SET AUTOPRINT ON
SCOTT@orcl_11gR2> EXEC create_excursion ('excursion1', :g_excursion_id)
PL/SQL procedure successfully completed.
G_EXCURSION_ID
--------------
1
SCOTT@orcl_11gR2> EXEC create_excursion ('excursion2', :g_excursion_id)
PL/SQL procedure successfully completed.
G_EXCURSION_ID
--------------
2
SCOTT@orcl_11gR2> SELECT * FROM excursion
2 /
EXCURSION_ID OTHER_COLS
------------ ---------------
1 excursion1
2 excursion2
2 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM another_table
2 /
ID
----------
1
2
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
