- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: stored procedure
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-23-2003 02:49 AM
тАО09-23-2003 02:49 AM
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>
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-23-2003 01:08 PM
тАО09-23-2003 01:08 PM
Re: stored procedure
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-23-2003 06:25 PM
тАО09-23-2003 06:25 PM
Re: stored procedure
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-23-2003 07:00 PM
тАО09-23-2003 07:00 PM
Solution*
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-23-2003 07:32 PM
тАО09-23-2003 07:32 PM
Re: stored procedure
a_in IN number,
b_out OUT t.b%type) as
begin
select b
into b_return
from t
where a = a_in;
end;
/