Procedure

If you know how to deal with a PL/SQL anonymous block, you should easily grasp the same look and feel in the procedure definition.

The PL/SQL is a block of code that we could call from another PL/SQL block by its name. Besides, we could pass any (reasonable) number of parameters to it.

Here is how we create a procedure for the current schema:

create or replace procedure say_hello(in_name in varchar2)
is
begin
  dbms_output.put_line('hello ' || in_name || '!');
end;

We "create or replace" it. So, if we are posting a change in the procedure code we won't get an error. If we prefer to avoid the risk of destroying already existing code, we can just "create" it.

This procedure is named say_hello, this is the name we should use to call it.

It accepts one single input ("in") parameter, a varchar2 named in_name.

The procedure body should not require any further explanation.

Once we have a procedure in our schema, or in an accessible one, we can call it like this:

begin
  say_hello('Tom');
end;

We can get rid of our procedure, if we don't need it anymore, dropping it:

drop procedure say_hello;

Assuming our user has the rights to do that, naturally.

Procedures, functions, and parameters are thoroughly discussed in chapter 17 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.