Parameter

The parameter is variable or placeholder of any valid PL/SQL datatype through which the PL/SQL
subprogram exchange the values with the main code. This parameter allows to give input to the
subprograms and to extract from these subprograms.

These parameters should be defined along with the subprograms at the time of creation.

These parameters are included in the calling statement of these subprograms to interact the values with
the subprograms.

The datatype of the parameter in the subprogram and the calling statement should be same.

The size of the datatype should not mention at the time of parameter declaration, as the size is dynamic
for this type.

Based on their purpose parameters are classified as

IN Parameter

OUT Parameter

IN OUT Parameter

IN Parameter

This parameter is used for giving input to the subprograms.

It is a read-only variable inside the subprograms. Their values cannot be changed inside the subprogram.

In the calling statement, these parameters can be a variable or a literal value or an expression, for
example, it could be the arithmetic expression like ‘5*8’ or ‘a/b’ where ‘a’ and ‘b’ are variables.

By default, the parameters are of IN type.

OUT Parameter

This parameter is used for getting output from the subprograms.

It is a read-write variable inside the subprograms. Their values can be changed inside the subprograms.

In the calling statement, these parameters should always be a variable to hold the value from the
current subprograms.
IN OUT Parameter

This parameter is used for both giving input and for getting output from the subprograms.

It is a read-write variable inside the subprograms. Their values can be changed inside the subprograms.

In the calling statement, these parameters should always be a variable to hold the value from the
subprograms.

These parameter type should be mentioned at the time of creating the subprograms.

subprograms.

RETURN

RETURN is the keyword that instructs the compiler to switch the control from the subprogram to the
calling statement. In subprogram RETURN simply means that the control needs to exit from the
subprogram. Once the controller finds RETURN keyword in the subprogram, the code after this will be
skipped.

PL/SQL Procedure

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:

PL/SQL Create Procedure

Syntax for creating procedure:

[ (parameter [,parameter]) ]

IS

[declaration_section]

BEGIN

executable_section

[EXCEPTION

exception_section]

END [procedure_name];

END [procedure_name ];

Code language: SQL (Structured Query Language) (sql)

PL/SQL procedure header

A procedure begins with a header that specifies its name and an optional parameter list.

Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a
parameter can be read from or written to.

IN

An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you cannot
change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a
parameter explicitly, Oracle will use the IN mode.

OUT

An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to
the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.
INOUT

An INOUT parameter is both readable and writable. The procedure can be read and modified.

Note that OR REPLACE option allows you to overwrite the current procedure with the new code.

PL/SQL procedure body

Similar to an anonymous block, the procedure body has three parts. The executable part is mandatory
whereas the declarative and exception-handling parts are optional. The executable part must contain at
least one executable statement.

1) Declarative part

In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a declaration
part of a procedure does not start with the DECLARE keyword.

2) Executable part

This part contains one or more statements that implement specific business logic. It might contain only a
NULL statement.

3) Exception-handling part

This part contains the code that handles exceptions.

Creating a PL/SQL
Create procedure example

In this example, we are going to insert record in user table. So you need to create user table first.

Table creation:

create table user(id number(10) primary key,name varchar2(100));

Now write the procedure code to insert record in user table.

Procedure Code:

create or replace procedure "INSERTUSER"

(id IN NUMBER,

name IN VARCHAR2)

is

begin

insert into user values(id,name);

end;

Output:

Procedure created.

PL/SQL program to call procedure

Let's see the code to call above created procedure.

BEGIN
 insertuser(101,'Rahul');

dbms_output.put_line('record inserted successfully');

END;

Now, see the "USER" table, you will see one record is inserted.

ID Name

101 Rahul

PL/SQL Drop Procedure

Syntax for drop procedure

DROP PROCEDURE procedure_name;

Example of drop procedure

DROP PROCEDURE pro1;