Re: how to create oracle stored procedure

You have log way to go!!

 create procedure ws_emp as
var employee.emp_lname%type;
 begin
 select emp_lname into var
 from employee where emp_id=100;
dbms_output.put_line('employee name is : '||var);  end;
 /

or
 create procedure ws_emp as
 begin
for c1 in (select emp_lname from employee) loop dbms_output.put_line('employee name is : '||c1.emp_lname); end loop;
 end;
 /

but what would happened if no records was found, you would get an error. you need to capture errors. In oracle we use exception handlers.

 create procedure ws_emp as
var employee.emp_lname%type;
 begin
 select emp_lname into var
 from employee;
dbms_output.put_line('employee name is : '||var); exception

    when no_data_found then
    dbms_output.put_line('No record found'); when others then

    dbms_output.put_line('Error');

Tom

 end;
 /

<wsunarko_at_my-deja.com> wrote in message news:8je85n$9kj$1_at_nnrp1.deja.com...
> Could someone please tell me how to create a stored proc in oracle.
> I created the following from sql plus:
>
> create procedure ws_emp as
> begin
> select emp_lname
> from employee;
> end;
> /
>
> It came back with:
> Procedure created with compilation errors.
>
> TIA
> Welly
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 29 2000 - 00:00:00 CEST