Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using result of a select statement as a variable

Posted on 2008-10-14
15
Medium Priority
?
632 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
or take a look at this link
 
http://www.oracle-developer.net/display.php?id=413 
0
 

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 900 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 900 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

688 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