Introduction

Procedures in Oracle are subprograms that are stored in the database and used to perform specified operations on the database’s or tables’ contents. Procedures, like any other program, should have a few mandatory parameters to successfully call and execute the procedure, such as the procedure name, arguments such as IN, OUT, or IN OUT for passing values when the procedure is called, a declaration section for declaring variables and datatype, and finally the main section to be executed.

What is an Oracle Procedure?

Let us now look at how processes are carried out. Procedures, as previously stated, are a named subprogram in PL/SQL that is typically utilized when reusability is desired. One thing to remember about procedures is that a named subprogram is stored in the database at the schema level. As a result, it is stored in the database until the operation is dropped.

There are three stages to a method. Declarative in the first section, executable in the second, and exception in the third. The cursors, types, constants, expressions, and nested subprograms are all declared in the declarative part of the program. The reported objects are procedure-specific, and they vanish as soon as the procedure is completed.

How to Create an Oracle Procedure

The syntax for creating a Procedure:

CREATE OR REPLACE procedure_name ([optional argument])
IS
[declaration section] BEGIN
[executable section] END

Oracle Procedure Parameters:

The following is a list of the different parameters used in the syntax:

1. Procedure name: This is the name of the procedure to be generated.
2. Optional Argument: This is the argument supplied to the procedure when it is called. Arguments can be categorized into three types:
   1. IN: This is the default value. It’s used to provide the argument a value. As an IN parameter, we can pass a constant, literal, or expression. Within a subprogram, their values cannot be modified.
   2. OUT: This parameter is used to return a value to the procedure’s caller. Within the procedure, we can change the value of the OUT parameter.
   3. IN OUT: It gives the method an initial value and then returns an updated value to the caller. An INOUT argument is usually a string buffer that is read and changed within the program.
3. The declaration section is where variables and data types are declared.

4. Executable Part: This section contains statements that are used to retrieve or update database values.

How to Call an Oracle Procedure

The syntax for Calling a Procedure:

BEGIN
Procedure_name(arguments)
END

The name of the stored procedure, as well as any arguments passed between BEGIN and END to call the procedure, is given here.

Example

CREATE OR REPLACE PROCEDURE first_test_procedure (p_myname IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Hi! my name is' ||p_myname);
END;

The query for Executing a Procedure:

SET SERVEROUTPUT ON
BEGIN
first_test_procedure('Nilanjan');
END;

How to Drop an Oracle Procedure

To save the space of an oracle procedure as long as the procedure is not needed, we can drop the procedure which was created earlier

Syntax of procedure

Drop procedure procedure_name;

Example

Drop procedure first_test_procedure;