Oracle PL/SQL Procedures and Procedure Parameters

This article contains information about PL/SQL Procedures and parameters in procedures such as IN, OUT, IN OUT.

What is Procedure in Oracle?

PL/SQL Procedures are the structures that keep Oracle PL/SQL commands under a name and run them when needed.

Procedures are compiled the first time they run and a query plan is created.

When you run stored procedures next time, they use this query plan.

The keywords to be used when creating the procedure are as follows.

The REPLACE keyword included in the command is not mandatory. However, it would be beneficial to use it as it will throw an error if you try to recreate an existing procedure. Let’s create a sample procedure.

Once the procedure is created, we can run it as follows.

The commands in the procedure will be executed each time the procedure is run.

Procedure Parameters in Oracle

Another important feature of the procedure structure is the possibility to take parameters.

IN Parameter in PL/SQL Procedures

If the parameter is marked with IN, the parameter is passed only to the procedure.

OUT Parameter in PL/SQL Procedures

If the parameter is marked OUT, the parameter sends out data.

IN OUT Parameter in PL/SQL Procedures

If the parameter is marked with IN OUT, it takes both IN and OUT.

PL/SQL Procedure example with parameters

In the example below, the loop start and end values are taken as parameters.

SYS.all_procedures table can be used to get information about the created procedures.

The following command is used to delete the created procedure.

The reason for using the procedures is to ensure that the written commands are securely stored in the database management system.

Also, the created procedures will work better because they are compiled beforehand.

When data is received from a table with any programming language, the SQL sentence must first be written and sent to the database.

The sent SQL statement is executed after it is checked by the database management system.

However, since the procedures are compiled beforehand, it is operated without recompilation, thus ensuring performance.

The procedures are similar to function structures, causing confusion.

While procedures are not required to return values, functions must return values.

Procedures are generally used in CRUD processes.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

  1. What is PL/SQL
  2. Oracle PL/SQL Data Types and Variables and Literals
  3. Oracle PL/SQL Operators
  4. Oracle PL/SQL Conditional Statements
  5. Oracle PL/SQL Loops
  6. Oracle PL/SQL Procedures and Procedure Parameters
  7. Oracle PL/SQL Functions
  8. Oracle PL/SQL Cursor
  9. Oracle PL/SQL Records
  10. Oracle PL/SQL Exception
  11. Oracle PL/SQL Trigger
  12. Oracle PL/SQL Packages
  13. Oracle PL/SQL Collections

You can find more information about procedures at docs.oracle.com

Yusuf SEZER
Author: Yusuf SEZER

Leave a Reply

Your email address will not be published. Required fields are marked *