Oracle Stored Procedure

In this Oracle tutorial, we will learn what are stored procedures and how to create and execute them in oracle 21c. Also, we will cover these topics:

• What is stored procedure in oracle SQL?
• What is recursive stored procedure in oracle?
• What is type in stored procedure in oracle 21c?
• What is out parameter in stored procedure in oracle 21c?
• What is the difference between function and stored procedure in oracle 21c?
• What is cursor in oracle stored procedure?
• How to create a stored procedure in oracle with parameter
• How to create a stored procedure with input parameter
• How to execute a stored procedure in oracle without parameter
• How to create stored procedure in oracle
• How to create a stored procedure in the oracle SQL developer tool?
• How to execute a stored procedure in oracle from sqlplus
• How to execute a stored procedure in oracle using SQL developer
• How to run a stored procedure in oracle
• How to invoke a stored procedure in oracle

What is stored procedure in oracle SQL?

A stored procedure is the block of oracle SQL statements that are compiled once and then can be used multiple times. Combining statements into a block and then sending them between application and database reduces network traffic.

An oracle creates procedural language extensions to SQL (PLSQL) to add programming features to SQL queries or statements. You can embed all the programming features like loops, if-else ladder, objective-oriented programming (OOPs), etc into SQL queries.

Oracle clauses – where, group by, having are not sufficient to perform advance operations on the database also they are comparatively slow. To overcome this problem oracle created PL/SQL which allows writing functions and store procedures.

There are 3 types of stored procedures in oracle:

• Standalone subprograms – Procedures created outside of a package.
• Subprograms – Procedures defined within a package
• local subprograms – procedures nested inside other subprograms.

Read: Oracle Change Database Name

What is recursive stored procedure in oracle?

When a procedure is written in such a way that it calls itself multiple times then it is called a recursive stored procedure. The factorial program is the best example of the same.

Below is the example, showing the recursive stored procedure in oracle. It is a factorial program wherein the procedure is calling itself the number of times of that number.
While executing the stored procedure we have provided the argument as 5 and as a result, 120 is produced.

-- stored procedure
CREATE OR REPLACE PROCEDURE orcl_fact(n in INTEGER, fact_n OUT INTEGER)
AS
BEGIN
  IF n=0 
    THEN fact_n := 1;
  ELSE 
    orcl_fact(n-1, fact_n);
    fact_n := n * fact_n;
  END IF;
END;
/

-- execute the procedure

SET SERVEROUTPUT ON;

DECLARE 
    i INTEGER;
BEGIN
  orcl_fact(5, i);
  dbms_output.put_line(i);
END;
/

-- output
120

Read: Database Size in Oracle 21c

What is type in stored procedure in oracle 21c?

Type IN is the input for the stored procedure in oracle 21c and this input can be used for the store procedure only. The procedure will use to manipulate the procedure. These inputs are in read-only mode and the procedure cannot change it.

Here is the syntax for using IN mode in a stored procedure:

CREATE OR REPLACE PROCEDURE <procedure_name>
(para1 IN [data type])

BEGIN
    ...
END;
/

In the next section, we have demonstrated the use of IN mode in oracle 21c.

What is out parameter in stored procedure in oracle 21c?

Out in oracle is used to mark those parameters which will produce the output in a later program. OUT mode set the value for the parameter that should be used outside the procedure.

Below syntax shows, how to create a stored procedure without parameters in oracle 21c:

CREATE OR REPLACE PROCEDURE <procedure_name>
(para1 OUT [data type] )
AS
BEGIN 
    ...
END;
/

Example:-

In the below example, we have created a stored procedure that will calculate the cube of a number. We are using IN OUT mode in this, which will allow us to take input and produce output.

The num is the parameter that the user will provide and then using that user input, the cube will be calculated and the output will be displayed.

-- create stored procedure
CREATE OR REPLACE PROCEDURE calcube(num IN OUT number) 
AS 
CREATE OR REPLACE PROCEDURE calcube(num IN OUT number) 
AS 
BEGIN 
  num := num * num * num;
  dbms_output.put_line('Cube: '|| num);
END;  


-- Execute stored procedure
SET SERVEROUTPUT ON;
DECLARE
  inp NUMBER;

BEGIN 
   inp:= 2; 
   calcube(inp); 
END; 
/

Output:-

In the below output, we have executed the program and provided user input as 2 and the program calculated the result as 2 * 2 * 2 = 8.

Read: Oracle Create User

What is the difference between function and stored procedure in oracle 21c?

Stored procedures & functions both are reusable and have a lot in common but despite similarities, there are a few key differences that we are going to discuss in the section.

Stored Procedure	Functions
It cannot be used with a select statement.	It can be used while querying using a select statement.
Uses the ‘out‘ keyword to return the result.	Uses the ‘return‘ keyword to return the result.
It is created to perform a specific operation and is not recommended to create for all the operations.	It is good practice to create functions in a program. Functions can be created for all the repetitive operations.
Values returned by the procedure cannot be assigned to a variable.	Values returned by the procedure can be assigned to a variable.

Also, check: Oracle check database status

What is cursor in oracle stored procedure?

The cursor in oracle acts as a pointer that points to the result of the query. It is the most powerful way of querying in the oracle database. The complete name for the cursor is REF CURSOR.

While the client receives the REF CURSOR, the data is not delivered until and unless the client opens the REF CURSOR and requests it. First user has to read the data then only he/she can retrieve the data..

• A REF CURSOR is the address of the memory on the database. To access the database, it is important that the client is connected to the ref cursor.
• The actual data is not available until the cursor is opened by the client and then requests the data and data cannot be retrieved until the user tries to reads it.
• A REF CURSOR once created cannot be updated all results can be presented in read-only mode.
• A REF CURSOR can be created and returned only inside the PLSQL code block.

Read: Connect to oracle database

How to create a stored procedure in oracle with parameter

A stored procedure is required when a business requirement demands the repetition of the same activity multiple times. If user input is also required then parameters are created.

To create a parameter provide the parameter name and data type in the first line while creating or replacing the stored procedure. You can also provide modes (IN, OUT, IN OUT) while creating parameters.

Syntax:

Below is the syntax to create a stored procedure with parameters:

CREATE OR REPLACE PROCEDURE <procedure_name>
(para1 [IN|OUT|IN OUT] [data_type], para2 [IN|OUT|IN OUT] [data_type])

BEGIN
   ...
END;
/

Example:-

In this example, we have created a program that accepts user input and then adds 5% to the base amount and displays the output of the new value.

-- create stored procedure
CREATE OR REPLACE PROCEDURE add_five_perc(amt IN OUT number) 
AS 
BEGIN 
  amt := amt + amt * 0.05;
  dbms_output.put_line('Result after adding 5 %: '|| amt);
END;  

-- execute the stored procedure
SET SERVEROUTPUT ON;
DECLARE
  user_input NUMBER;

BEGIN 
   user_input:= 20000; 
   add_five_perc(user_input); 
END; 
/

Output:-

In this output, we have given 20000 to the program and the program has returned 20000 + (20000 * (5/100)) = 21000.

How to create a stored procedure with input parameter

In a program, if user input is required in the stored procedure then parameters are created. The user has to fill in the information based upon the declared data type while executing the procedure in oracle.

Input is created using IN keyword and the stored procedure uses this input to manipulate the information.

Below syntax shows the use of input parameters in a stored procedure in oracle 21c:

CREATE OR REPLACE <procedure_name>
(para1 IN [data type] )

BEGIN
    ...
END;
/

In the section – What is the out parameter in stored procedure in oracle 21c? we have demonstrated the use of IN or input parameter as well.

Read: Oracle get database name

How to execute a stored procedure in oracle without parameter

In case, no user input is required in the procedure, then the developer doesn’t need to pass the parameters while creating the stored procedure.

Parameters are passed in the first line of creating procedures inside the parentheses after providing the procedure name. You can skip this part on jump on declaring statements.

Type execute or exec followed by the name of the stored procedure, you don’t need to start a parenthesis if the procedure does not require parameters.

EXECUTE [PROCEDURE_NAME];

EXEC [PROCEDURE_NAME];

How to create stored procedure in oracle

Stored procedures in oracle can be created using Pl/SQL which is the extension of oracle. PLSQL supports the implementation of programming features with SQL queries. This embraces the execution of SQL operations in oracle.

Use CREATE OR REPLACE PROCEDURE followed by the name of the procedure and parameters if required.

In the next line type AS here, you can type the name of variables required for the program if any. Provide the parameter name, data type, and value for the data type.

BEGIN will be the start of all the procedural activities required for this stored procedure in oracle. END statements tell the completion of stored procedure creation in oracle.

How to create a stored procedure in oracle SQL developer tool?

Stored Procedure is the block of multiple SQL statements that compiles once but can be used multiple times in oracle database 21c. These procedures are written in PLSQL which is the extension of the oracle database.

Before we jump on the syntax to create stored procedures let’s understand the components of Stored Procedures:

1. CREATE OR REPLACE PROCEDURE
2. IS
3. BEGIN
4. EXCEPTION
5. END

CREATE OR REPLACE PROCEDURE is the keyword used to create the store procedure. Replace keywords will modify the parameter or other information without dropping the existing procedure.

IS comprises all the declared statements. If you want to create any variable for the stored procedure then it can be placed here.

BEGIN is the main playground where all the program is written to execute the stored procedure as the requirement. It is the beginning of the main content.

EXCEPTION came into action when a program throws any unexpected error. The u

END declares the termination or finish of the stored procedure. When the program reads end then it understands then now all the operations have been completed.

Step 1: Write click on the procedure and click on the New Procedure.

Step 2: Select the schema from the drop-down, name the stored procedure, click on the plus icon to add a parameter for the stored procedure. You click on ok if there are no parameters.

From the select IN, OUT, or IN OUT as per the requirement also select the appropriate data type from the data type column and then click on the ok.

Step 3: Once you have clicked on the okay button in the previous step the stored procedure will be created.

Click on the gear icon next to the play button to compile the stored procedure. Click on the green play button or ctrl+10 to run the stored procedure.

How to execute a stored procedure in oracle from sqlplus

In oracle, store procedures can be created using PL/SQL which is a procedural language created by oracle especially to embed programming features in the SQL queries.

Using Execute or Exec keyword followed by the name of the procedure you can execute a stored procedure in oracle from the sqlplus command line.

below syntax shows how to execute a stored procedure in oracle from sqlplus:

SET SERVEROUTPUT ON

EXECUTE [PROCEDURE_NAME];

EXEC [PROCEDURE_NAME];

How to execute a stored procedure in oracle using SQL developer

Oracle SQL developer tool allows running huge oracle queries with command line and graphical user interface support.

After successful compilation of store procedure type SET SERVEROUTPUT ON; without this, the output won’t be visible.

A similar thing can be done using the GUI interface by clicking on the View -> DBMS Output option. You will notice a new Dbms Output window added below. Click on the green plus icon and logging into the user.

Execute the program and now the output will appear on the DBMS output window.

Read: How to backup table in Oracle

How to run a stored procedure in oracle

Stored Procedure in oracle can be run using the run keyword followed by the name of the procedure with open-close parenthesis.

In case, the procedure requires any argument then you can pass that inside this parenthesis. Below is the command to run a stored procedure in oracle 21c.

RUN Procedure_Name();

How to invoke a stored procedure in oracle

A stored procedure in oracle can be invoked using Execute keyword followed by the name of the procedure. You can also use the short name of Execute which is Exec in oracle 21c.

Below is the syntax to invoke a stored procedure in oracle, make sure to use set serveroutput on it before executing the stored procedure otherwise, the output won’t be visible.

EXECUTE [PROCEDURE_NAME];

EXEC [PROCEDURE_NAME];

You may also like to read the following Oracle tutorials.

• Oracle to_char Date Format
• Alter Table Add Column Oracle
• Number Datatype in Oracle 
• Oracle Install sample schemas 21c
• Oracle stored procedure create table
• How to create a database in Oracle

In this tutorial, we have learned about the stored procedures and how to create and execute a stored procedure in oracle 21c.

• What is stored procedure in oracle SQL?
• What is recursive stored procedure in oracle?
• What is type in stored procedure in oracle 21c?
• What is out parameter in stored procedure in oracle 21c?
• What is the difference between function and stored procedure in oracle 21c?
• What is cursor in oracle stored procedure?
• How to create a stored procedure in oracle with parameter
• How to create a stored procedure with input parameter
• How to execute a stored procedure in oracle without parameter
• How to create stored procedure in oracle
• How to create a stored procedure in oracle SQL developer tool?
• How to execute a stored procedure in oracle from sqlplus
• How to execute a stored procedure in oracle using SQL developer
• How to run a stored procedure in oracle
• How to invoke a stored procedure in oracle