Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

stored procedure

SOLVED
Go to solution
Pieter_5
Advisor

stored procedure

Hi,

I want to create a stored procedure which select a record from a table where columna is equal to input parameter a. The procedure should return the value of column b. How to do this>
4 REPLIES
Brian Crabtree
Honored Contributor

Re: stored procedure

This seems like a waste. If you were doing some kind of processing of the record, or something like that, I could understand it, otherwise, I dont see why you don't just use a simple SQL statement to get the information you are looking for.

That being said, the following should work:

create procedure colout(incolA varchar2, outcolB out varchar2) as
begin
select columnB into outcolB from tableA where columnA = incolA;
end;
/

You could also do this with a function as well.

Thanks,

Brian
Indira Aramandla
Honored Contributor

Re: stored procedure

Hi,

When you need a simple output from a table then why go for a stored procedure / function.

You can do with a single sql statement. As Brian as given you the procedure, here is the simple sql statement.

SQL> select col-b from table-a
where col-a = &cola;

This will output as
Enter value for col-a:

When you enter the value for col-a
it will output the following

old : value
new : value

column b value.


I hope this helps.
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor
Solution

Re: stored procedure

hi,
*
A bit of theory first:
*
Procedure can be parametrised. This means that a caller of a procedure can pass in values for it to use. Parameters can be of any valid PL/SQL type and they come in three modes: IN, OUT, and IN OUT.
*
- IN
Parameters are passed in by the caller and can only be read by the procedure. That is to say that they are read-only values, and can't be changed by the procedure. This is the most commonly used mode of parameter behaviour and is the default where no parameter mode indication is given. IN parameter can also have default values so that the caller does not need to supply a value for them.
*
- OUT
Parameters can be written to by the procedure. These are useful when the procedure needs to pass back multiple pieces of information to the caller. OUT parameter cannot be defaulted, and a variable, not a constant or expression, must be passed to OUT parameters.
*
- IN OUT
Parameters, as the name suggests, have the properties of both IN and OUT parameters. Procedures can both read and write their values.
*
*
Now, as for your answer:
*
Consider this example: using the SCOTT.EMP table, We can write a lookup procedure that will return the ENAME and SAL of the employee whose EMPNO we supply:
-
scott@DEV.MU> create or replace
2 procedure emp_lookup(
3 p_empno IN number,
4 o_ename OUT emp.ename%type,
5 o_sal OUT emp.sal%type) as
6 begin
7 select ename, sal
8 into o_ename, o_sal
9 from emp
10 where empno = p_empno;
11 exception
12 when no_data_found then
13 o_ename := 'NULL';
14 o_sal := -1;
15* end emp_lookup;
-
Procedure created.
-
Elapsed: 00:00:00.06
scott@DEV.MU>
*
Testing using SQLPLUS:
=======================
scott@DEV.MU> variable name varchar2(10);
scott@DEV.MU> variable sal number;
scott@DEV.MU> exec emp_lookup('7782', :name, :sal);
-
PL/SQL procedure successfully completed.
-
Elapsed: 00:00:00.14
scott@DEV.MU> select :name, :sal
2 from dual;
-
:NAME :SAL
________________________________ __________
CLARK 2450
-
Elapsed: 00:00:00.01
scott@DEV.MU>
==========================================================
*
Now if you want to do the same in PLSQl:
========================================
consider the following PLSQL block
scott@DEV.MU> declare
2 l_ename emp.ename%type;
3 l_sal emp.sal%type;
4 begin
5 emp_lookup(7782, l_ename, l_sal);
6 dbms_output.put_line('Ename ='||l_ename);
7 dbms_output.put_line('Sal ='||l_sal);
8* end;
Ename =CLARK
Sal =2450
-
PL/SQL procedure successfully completed.
-
Elapsed: 00:00:00.01
scott@DEV.MU>
-
*
*
*
Hope this helps!
*
best regards
Yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
twang
Honored Contributor

Re: stored procedure

create or replace procedure find_b(
a_in IN number,
b_out OUT t.b%type) as
begin
select b
into b_return
from t
where a = a_in;
end;
/