stored procedure

[Pieter_5]

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>

[Brian Crabtree]

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]

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.

[Yogeeraj_1 · Accepted solution]

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

[twang]

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;
/