Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 | CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] data_type [, ...])] {IS | AS} BEGIN -- Commands END procedure_name; |
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.
1 2 3 4 5 6 | SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE First_SP AS BEGIN DBMS_OUTPUT.put_line('Hello Oracle!'); END; |
Once the procedure is created, we can run it as follows.
1 | EXECUTE First_SP |
1 | EXEC First_SP |
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.
1 2 3 4 5 6 7 8 9 10 | SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE MY_LOOP ( START_VALUE IN PLS_INTEGER, END_VALUE IN PLS_INTEGER ) AS BEGIN FOR v_number IN START_VALUE .. END_VALUE LOOP DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); END LOOP; END; |
1 | EXECUTE MY_LOOP(45,50) |
SYS.all_procedures table can be used to get information about the created procedures.
1 | SELECT * FROM SYS.all_procedures WHERE OWNER = 'USER_NAME'; |
The following command is used to delete the created procedure.
1 | DROP PROCEDURE procedure_name; |
1 | DROP PROCEDURE MY_LOOP; |
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.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about procedures at docs.oracle.com
