Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql stored procedure execute/return value (oracle 11g pl/sql)
pl/sql stored procedure execute/return value [message #560532] Fri, 13 July 2012 23:43 Go to next message
lrncon
Messages: 1
Registered: July 2012
Junior Member
I am creating a stored procedure with pl/sql for the first time. I need this procedure to execute and return values for the employee with the maximum salary by displaying the employee's last name and max salary. I have gotten the procedure to create and the exec statement gives me a "procedure successfully completed" message, without the returned values. I have modified the procedure several times to try to get the results I am looking for, with no luck. I have searched google for an answer, I haven't found anything that makes any sense to me at this point. Do I need to declare something within the procedure?

SQL> create or replace procedure max_salary
2 is
3 last_name varchar(25);
4 salary number(8,2);
5 begin
6 select last_name, salary into last_name, salary
7 from employees
8 Where salary = (select Max(salary) from employees);
9 end;
10 /

Procedure created.

SQL> excute max_salary;
SP2-0734: unknown command beginning "excute max..." - rest of line ignored.
SQL> exec max_salary;

PL/SQL procedure successfully completed.

SQL> create or replace procedure max_salary
2 is
3 last_name varchar(25);
4 salary number(8,2);
5 begin
6 select last_name, salary into last_name, salary
7 from employees
8 Where salary = (select Max(salary) from employees);
9 dbms_output.put_line('salary: ' || last_name);
10 end;
11 /

Procedure created.

SQL> execute max_salary;

PL/SQL procedure successfully completed.

Re: pl/sql stored procedure execute/return value [message #560533 is a reply to message #560532] Fri, 13 July 2012 23:44 Go to previous messageGo to next message
BlackSwan
Messages: 22677
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: pl/sql stored procedure execute/return value [message #560534 is a reply to message #560532] Sat, 14 July 2012 00:48 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

did you use SET SERVEROUTPUT ON
and then you have display the output using dbms_output.put_line to display your output.
Re: pl/sql stored procedure execute/return value [message #560536 is a reply to message #560532] Sat, 14 July 2012 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

You discover PL/SQL is not made to display things.
You should create a function instead (compare how my post is formatted compares to yours, which one is easier to read?):
SQL> create or replace function max_salary return varchar2
  2  is
  3    last_name emp.ename%type;
  4    salary    emp.sal%type;
  5  begin
  6    select ename, sal into last_name, salary
  7    from emp
  8    where sal = (select max(sal) from emp)
  9      and rownum = 1; -- in case several employess with max salary
 10    return last_name||': '||salary;
 11  end;
 12  /

Function created.

SQL> select max_salary from dual;
MAX_SALARY
-----------------------------------------------------------------------
KING: 5000

Regards
Michel

Re: pl/sql stored procedure execute/return value [message #560539 is a reply to message #560536] Sat, 14 July 2012 01:15 Go to previous message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or if you really want to use a procedure then use OUT paramaters:
SQL> drop function max_salary;

Function dropped.

SQL> create or replace procedure max_salary (last_name out emp.ename%type, salary out emp.sal%type)
  2  is
  3  begin
  4    select ename, sal into last_name, salary
  5    from emp
  6    where sal = (select max(sal) from emp)
  7      and rownum = 1; -- in case several employess with max salary
  8  end;
  9  /

Procedure created.

SQL> var last_name varchar2(30)
SQL> var salary number
SQL> set autoprint on
SQL> exec max_salary (:last_name, :salary)

PL/SQL procedure successfully completed.

    SALARY
----------
      5000

LAST_NAME
--------------------------------
KING

Regards
Michel
Previous Topic: how to store xml in blob from a query. (2 Merged)
Next Topic: Trigger
Goto Forum:
  


Current Time: Wed Aug 20 06:39:23 CDT 2014

Total time taken to generate the page: 0.11155 seconds