Using result of a select statement as a variable

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
vbemtAsked:
Who is Participating?
 
sventhanConnect With a Mentor Commented:
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
 
silemoneCommented:
yes...you could create a temp table...

i.e.


@tempTable Table


0
 
vbemtAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
silemoneConnect With a Mentor Commented:
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
 
silemoneCommented:
i'll see how this works in oracle...but this is basic sql...so should work in all versions of Sql
0
 
vbemtAuthor Commented:
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
 
sventhanCommented:
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
 
sventhanCommented:
or take a look at this link
 
http://www.oracle-developer.net/display.php?id=413 
0
 
vbemtAuthor Commented:
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
 
sventhanCommented:
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
 
SujithData ArchitectCommented:
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
 
vbemtAuthor Commented:
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
 
vbemtAuthor Commented:
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
 
SujithConnect With a Mentor Data ArchitectCommented:
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
 
vbemtAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.