Solved

Using result of a select statement as a variable

Posted on 2008-10-14
15
618 Views
Last Modified: 2013-12-07
I am working on a stored procedure (1st one for me) and I'm trying to do the following:

Run a Stored procedure that first executes a select statement and then uses the value from the select statement as an input for an insert statement and to also return that value when the stored procedure is done.

Here is something like what I'm trying to do:

Create procedure spAddTeam
@name varchar2(100),
@user varchar2(30)
as
Begin
 Select nextval as maxNbr
 From Dual
--This gets me a unique number that I can use as an ID for the insert
Insert into Team
(
 id,
name,
createdBy,
CreatedOn
)
Values
(
This is where I want the id,
@name,
@user,
sysdate
)
Return (value from dual)
end
0
Comment
Question by:vbemt
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 21

Expert Comment

by:silemone
ID: 22712958
yes...you could create a temp table...

i.e.


@tempTable Table


0
 

Author Comment

by:vbemt
ID: 22712988
I don't want to create a temporary table.  Again I want to select the value, and use the selected value as the variable to both insert and then later return when the stored procedure is complete.
0
 
LVL 21

Assisted Solution

by:silemone
silemone earned 50 total points
ID: 22713148
yes you can...here's how...instead of placing select statement in "" place in () and it will have the value instead of the unexecuted select text...


declare
  @ID int


Set @ID = ( //Whatever select you're going to use to get data...)




Insert into Team
(
 id,
name,
createdBy,
CreatedOn
)
Values
(
@ID
@name,
@user,
sysdate
)
0
 
LVL 21

Expert Comment

by:silemone
ID: 22713166
i'll see how this works in oracle...but this is basic sql...so should work in all versions of Sql
0
 

Author Comment

by:vbemt
ID: 22714102
I'm getting an PLS-00103 error off this simple Stored Procedure (yes this is my first):
Create or replace procedure spAddTeam
as
begin
end spAddTeam;

Any suggestions.  I'm trying to write this, but went back to the very basics....
0
 
LVL 18

Expert Comment

by:sventhan
ID: 22714255
use a simple cursor and a for loop for the insert....
declare
    total_val number(6);

    cursor c1 is
      select monthly_income
      from employees;

BEGIN
total_val := 0;
FOR employee_rec in c1
LOOP
Insert into your_table
(
 monthly_income)
Values
(employee_rec.monthly_income
)  
END LOOP;


END;

what do you want to return? I was not clear on that part.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 22714276
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:vbemt
ID: 22714642
I'm having a hard time just figuring out the correct syntax for the procedure.  I've seen places that state it's like this:
Create or replace procedure tmp(name in varchar2, user in varchar2, id out number)
as
begin
     select nextval into id from dual
     insert into team
     (id, name, createdby)
     values
     (ID, name, user)
null;
end;

Then I've seen:
Create or replace procedure tmp
@name varchar2(150),
@user varchar2(150)
as
begin
     select nextval into id from dual
     insert into team
     (id, name, createdby)
     values
     (ID, name, user)
null;
end;

Which of these is the correct start.....
0
 
LVL 18

Expert Comment

by:sventhan
ID: 22715187
Create or replace procedure tmp(name in varchar2, user in varchar2, id out number)
as
x number;
begin
     select nextval into x from dual
     insert into team
     (x, name, createdby)
     values
     (ID, name, user)
returning x into id;
null;
end;

http://www.psoug.org/reference/insert.html
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22718306
Since you are creating a procedure, there is no need for a return statement.

>>  Select nextval as maxNbr
>>  From Dual
This looks like you are trying to get a value from a sequence object.
If that is the case, then the select itself is not required. Your procedure reduces to

Create procedure spAddTeam(
p_name varchar2,
p_user varchar2 )
as
Begin

Insert into Team
(
id,
name,
createdBy,
CreatedOn
)
Values
(
<your sequence>.nextval ,
p_name,
p_user,
sysdate
);

end;
/

Or, if you are trying to get the ID from a table, you could write:

Create procedure spAddTeam(
p_name varchar2,
p_user varchar2 )
as
Begin

Insert into Team
(
id,
name,
createdBy,
CreatedOn
)
SELECT
<your sequence>.nextval ,
p_name,
p_user,
sysdate
FROM <your table>
WHERE <condition>;

end;
/
0
 

Author Comment

by:vbemt
ID: 22720148
sujith80, I understand how to trim down the procedure, and also how to do the select inside my insert statement.  However, I still need to solve the issue of returning the id that I get from the query.  That id will be used in other parts of the code so once it's determined, used in the insert, I need to figure out how to return it.
0
 

Author Comment

by:vbemt
ID: 22720155
Sventhan, I was under the impression that you don't want to type in the return statement when you are doing a stored procedure.  Is my information incorrect?
0
 
LVL 18

Accepted Solution

by:
sventhan earned 225 total points
ID: 22726963
vbemt -
The return that I was using in my procedure is one of the oracle feature returning clause which specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm
Hope I am not confusing you again.
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 225 total points
ID: 22729649
See the outline.
Create procedure spAddTeam(

p_name varchar2,

p_user varchar2 

p_ID OUT NUMBER ) -- this will be collected as an out parameter

as

l_id number;

Begin
 

-- your select to get the ID value into l_id;
 

Insert into Team

(

id,

name,

createdBy,

CreatedOn

)

SELECT

l_id ,

p_name,

p_user,

sysdate

FROM <your table> 

WHERE <condition>;
 

p_ID := l_id; -- this stmt assigns the value to the out parameter

end;

/

Open in new window

0
 

Author Closing Comment

by:vbemt
ID: 31505974
Thank you for all the help.  I knew that it took a while, but I appreciate the patience.  What was confusing me most when I was looking stuff up was that Oracle references intermix Functions and Procedures :(  Again thanks for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now