Stored Procedures in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Stored Procedures in Oracle

In this article, we will learn about the real PL/SQL i.e. Stored Procedures in Oracle with Syntax and Examples. Until now, we learned about fundamentals. Let us start learning about Stored Procedures in Oracle. As we learned earlier that we have block types. We have anonymous blocks and subprograms. The sub-programs are mostly used for procedures and functions.

Differences between Anonymous Blocks and Sub Programs in Oracle:

Anonymous Blocks	Sub Programs
Unnamed PL/SQL Blocks	Named PL/SQL Blocks
Compiled Every time	Compiled only once
Not stored in the database	Stored in the database
Cannot be invoked by other applications	Named and therefore, can be invoked by other applications
Do not return values	Sub-programs called functions must return values
Cannot take parameters	Can take parameters

Sub Programs in Oracle:

We can compile the subprograms only once which can be stored in the database and named. These sub-programs can be invoked by other applications. The sub-programs must return values. The most important concept in sub-programs is it takes parameters.

Modularized Subprogram Design:

When we create a procedure or function it should have a proper reason for existing a procedure or function.

Example without using Stored Procedure in Oracle:

Statement 1: Update emp Set sal=sal+100 Where emp_id=100;
Statement 2: Update emp Set sal=sal+100 Where emp_id=101;
Statement 3: Update emp Set sal=sal+100 Where emp_id=102;

You can see we have used three different update statements to update their salaries by 100. Oracle suggests using the procedures. The procedures take two parameters as the input and then perform the action that we are looking for.

Example using Stored Procedure in Oracle:

PROCEDURE UPDATE_EMP
(
   P_EMP_ID NUMBER, 
   P_EM NUMBER
)
IS
BEGIN
   UPDATE EMP
   SET SAL=SAL_P_EM
   WHERE EMP_ID=P_EMP_ID
   COMMIT;
END;

Modularize Code into Subprograms:

1. Locate code sequences repeated more than once.
2. Create subprogram P containing the repeated code
3. Modify the original code to invoke the new subprogram. We can one procedure in another procedure so invoke the procedure multiple times.

Example:

PROCEDURE UPDATE_EMP_GROUP
IS
--
--
UPDATE_EMP(X,Y);
--
--
END;

Development with PL/SQL Blocks in Oracle:

PL/SQL is a block-structured language. The PL.SQL code block helps modularize code by using:

1. Anonymous blocks
2. Procedure and functions
3. Packages
4. Database triggers

We will understand all these blocks in further articles.

The benefits of using modular program constructs are:

1. Easy Maintenance
2. Improved data security and integrity: When we create a procedure, we can grant the procedure privilege to specific users
3. Improved performance: the performance will be
4. Improved code clarity

What are the Benefits of Anonymous Block in Oracle?

Generally, developers write code very formally using functions, procedures, and packages. We will be writing code for the oracle products called oracle forms. Let us see the below example:

We have an oracle form. We have a search button. The query for search is present inside the search button. We can write the PL/SQL in order to use the search button. Writing trigger code for oracle components. This is used to initiate calls for procedures, functions, and packages.

Example:

BEGIN
PACKAGE1;
FUNCTION1;
END;

Isolating exception handling within a block of code. We can write the code within the BEGIN and END blocks. In the begin and end anonymous block we can have another begin and end with exception handling.

What are PL/SQL Sub Programs in Oracle?

1. A PL/SQL subprogram is a named PL/SQL block that can be called with a set of parameters
2. You can declare and define a subprogram within either a PL/SQL or another subprogram.
3. A sub-program consists of a specification and a body.
4. A sub-program can be a procedure or a function.
5. Typically, you use a procedure to perform an action and a function to compute and return a value.

What are Stored Procedures in Oracle?

The procedures are a type of subprograms that perform an action and can be stored in the database as a schema object. This procedure promotes reusability and maintainability.

How to Create a Stored Procedure in Oracle?

First, we need to write a PL/SQL code to create a procedure. Once the code is completed. We have to compile the code. We have to check the compiler for any warnings or errors. If there are any warnings or errors then we have to re-edit the code. If there are no errors, then we can execute the procedure.

So, this is how the procedure is executed. Once the procedure is executed successfully, then the procedure will be called by mentioning the parameters.

Understanding Stored Procedure Syntax in Oracle

We understood what is procedure. Now, we will try to learn about the syntax of Stored Procedures in Oracle. Let us take a look at the below example.

CREATE OR REPLACE PROCEDURE UPDATE_SAL
( 
   P_EMP IN NUMBER, 
   P_AMOUNT IN NUMBER
)
IS
BEGIN
   UPDATE EMPLOYEES
   SET SALARY =SALARY + P_AMOUNT
   WHERE EMPLOYEE_ID=P_EMP_ID;
   COMMIT;
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLCODE);
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Let us understand each and every statement in detail to have a clear picture of the syntax of Oracle Stored Procedure.

CREATE OR REPLACE PROCEDURE UPDATE_SAL

Create or replace procedures are reserved words. The term REPLACE is optional. We use this to replace to override existing procedures. We have mentioned UPDATE_SAL as the procedure name. After this, we have to define the parameter list. The parameter list is optional.

(P_EMP IN NUMBER, P_AMOUNT IN NUMBER)

We have created a parameter P_EMP IN NUMBER. The term IN indicates the parameter mode. We have three parameter modes IN, OUT, IN/OUT. We will understand about this later. We have created another P_AMOUNT which is also a number. The parameter list is optional. Parameter data types should not have the size. If we define the size of the parameter (P_AMOUNT IN VARCHAR2(20)) then we may get an error.

After the parameters we have to mention the term IS or AS. Under the term IS or AS we have to mention the logic of the procedure. We have used the previous example. The Syntax is given below:

BEGIN
   UPDATE EMPLOYEES
   SET SALARY =SALARY + P_AMOUNT
   WHERE EMPLOYEE_ID=P_EMP_ID;
COMMIT;

We will be updating the salary by the value of P_AMOUNT. We have an exception column where we can mention the exception case of the procedure. What is Exception and how to handle Exceptions in Stored Procedure will be discussed in detail in our upcoming articles.

Note: In order to create a procedure in oracle, we must have the “create procedure” privilege. DBA needs to grant access. Substitution and host variables are not allowed in procedures. We should not use the host variables and ‘&’ in the procedure. This is because we already have the parameter list, so no need to mention the ‘&’ again.

Once the procedure is compiled, the procedure is stored in the database as a schema object. We can call this procedure by mentioning the below syntax.

Syntax: EXECUTE UPDATE_SAL(100,50);

So, here the value 100 is taken by the P_EMP parameter, and the value 50 is taken by the P_AMOUNT parameter. This is one of the approaches to calling a Stored Procedure in Oracle. We have another approach where the procedure is called inside a PL/SQL block. For Example:

BEGIN
—
—
UPDATE_SAL(100,50);
—
END;

In the next article, we will discuss IN-Parameter in Oracle Stored Procedure with Examples. Here, in this article, I try to explain the basic concept of Stored Procedures in Oracle. I hope you enjoy this Oracle Stored Procedures article.