New to stored procedures, how to create stored procedure and use it?

[sarayusam]

Hi,

Please suggest how to create a stored procedure and how to use it? I have not done stored procedures. From where do i start to learn stored procedures.




regards,
sarayu

 

[SantaMufasa]

Sarayusam,

Here is a simple stored procedure that displays any string to your screen. You can create user-defined stored procedures from SQL*Plus:

create or replace procedure prt (x in varchar2) is
begin
    dbms_output.put_line(x);
end;
/

Then, once you have created the stored procedure, you can call it from any PL/SQL block, for example:

set serveroutput on format wrap
begin
    dbms_output.enable(1000000);
    for r in (select * from s_region) loop
        prt(r.id||': '||r.name);
    end loop;
end;
/

1: North America
2: South America
3: Africa / Middle East
4: Asia
5: Europe

PL/SQL procedure successfully completed.

As far as learning all of the nuances and idiosyncracies of creating stored procedures and functions using PL/SQL, I recommend your looking out on Amazon.com: There are several good books from which you can learn PL/SQL.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.