The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.

The procedure contains a header and a body.

Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.

How to pass parameters in procedure:

When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:

IN parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

Where,

procedure-name specifies the name of the procedure.
[OR REPLACE] option allows the modification of an existing procedure.
The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
procedure-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Example

The following example creates a simple procedure that displays the string 'Hello World!' on the screen when executed.

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

When the above code is executed using the SQL prompt, it will produce the following result −

Procedure created.

PL/SQL program to call procedure

Declare

BEGIN

greetings;

END;

Creating a PL/SQL function

Similar to a procedure, a PL/SQL function is a reusable program unit stored as a schema object in the Oracle Database. The following illustrates the syntax for creating a function:

CREATE [OR REPLACE] FUNCTION function_name (parameter_list)

    RETURN return_type

IS

    [declarative section]


BEGIN


    [executable section]


[EXCEPTION]


    [exception-handling section]


END;

A function consists of a header and body.

The function header has the function name and a RETURN clause that specifies the datatype of the returned value. Each parameter of the function can be either in the IN, OUT, or INOUT mode.

The function body is the same as the procedure body which has three sections: declarative section, executable section, and exception-handling section.

The declarative section is between the IS and BEGIN keywords. It is where you declare variables, constants, cursors, and user-defined types.
The executable section is between the BEGIN and END keywords. It is where you place the executable statements. Unlike a procedure, you must have at least one RETURN statement in the executable statement.
The exception-handling section is where you put the exception handler code.

PL/SQL Function Example

create or replace function adder(n1 in number, n2 in number)

return number

is

n3 number(8);

begin

n3 :=n1+n2;

return n3;

end;

/

call the function.

DECLARE

n3 number(2);

BEGIN

n3 := adder(11,22);

dbms_output.put_line('Addition is: ' || n3);

END;

/