In this blog, we’ll discuss Procedures in PL/SQL. A subprogram may be a program unit/module that performs a specific task. These subprograms are combined to make larger programs. this is often basically called the ‘Modular design’. A subprogram are often invoked by another subprogram or program which is named the calling program.
A subprogram are often created −
• At the schema level
• Inside a package
• Inside a PL/SQL block
At the schema level, subprogram may be a standalone subprogram. it’s created with the CREATE PROCEDURE or the CREATE FUNCTION statement. it’s stored within the database and may be deleted with the DROP PROCEDURE or DROP FUNCTION statement.
A subprogram created inside a package may be a packaged subprogram. it’s stored within the database and may be deleted only the package is deleted with the DROP PACKAGE statement. we’ll discuss packages within the chapter ‘PL/SQL – Packages’.
PL/SQL subprograms are named PL/SQL blocks which will be invoked with a group of parameters. PL/SQL provides two sorts of subprograms −
• Functions − These subprograms return one value; mainly wont to compute and return a value .
• Procedures − These subprograms don’t return a value directly; mainly wont to perform an action.
This chapter goes to hide important aspects of a PL/SQL procedure. we’ll discuss PL/SQL function within the next chapter.
Parts of a PL/SQL Subprogram
Each PL/SQL subprogram features a name, and should even have a parameter list. Like anonymous PL/SQL blocks, the named blocks also will have the subsequent three parts –

S.No	Parts & Description
1	Declarative Part It is an optional part. However, the declarative part for a subprogram doesn’t start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. these things are local to the subprogram and cease to exist when the subprogram completes execution.
2	Executable Part This is a compulsory part and contains statements that perform the designated action.
3	Exception-handling This is again an optional part. It contains the code that handles run-time errors.

Creating a Procedure
A procedure is made with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, …])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Where,
• procedure-name specifies the name of the procedure.
• [OR REPLACE] option allows the modification of an existing procedure.
• The optional parameter list contains name, mode and kinds of the parameters. IN represents the value which will be passed from outside and OUT represents the parameter which will be wont to return a value outside of the procedure.
• procedure-body contains the executable part.
• The AS keyword is employed rather than the IS keyword for creating a standalone procedure.
Example
The following example creates an easy procedure that displays the string ‘Hello World!’ on the screen when executed.
CREATE OR REPLACE PROCEDURE greetings

AS 
BEGIN 
dbms_output.put_line('Hello World!'); 
END; 
/

When the above code is executed using the SQL prompt, it’ll produce the subsequent result −
Procedure created.
Executing a Standalone Procedure
A standalone procedure are often called in two ways −
• Using the EXECUTE keyword
• Calling the name of the procedure from a PL/SQL block
The above procedure named ‘greetings’ are often called with the EXECUTE keyword as −
EXECUTE greetings;
The above call will display −
Hello World

PL/SQL procedure successfully completed.
The procedure also can be called from another PL/SQL block −
BEGIN
greetings;
END;
/
The above call will display −
Hello World

PL/SQL procedure successfully completed.
Deleting a Standalone Procedure
A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting a procedure is −
DROP PROCEDURE procedure-name;
You can drop the greetings procedure by using the subsequent statement −
DROP PROCEDURE greetings;
Parameter Modes in PL/SQL Subprograms
The following table lists out the parameter modes in PL/SQL subprograms –

S.No	Parameter Mode & Description
1	IN An IN parameter allows you to pass a value to the subprogram. it’s a read-only parameter. Inside the subprogram, an IN parameter acts sort of a constant. It can’t be assigned a value. you’ll pass a continuing, literal, initialized variable, or expression as an IN parameter. you’ll also initialize it to a default value; however, therein case, it’s omitted from the subprogram call. it’s the default mode of parameter passing. Parameters are gone by reference.
2	OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts sort of a variable. you’ll change its value and reference the value after assigning it. the particular parameter must be variable and it’s gone by value.
3	IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It is often assigned a value and therefore the value is often read. The actual parameter like an IN OUT formal parameter must be a variable, not a continuing or an expression. Formal parameter must be assigned a value. Actual parameter is gone by value.

IN & OUT Mode Example 1
This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.

DECLARE 
a number; 
b number; 
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
IF x < y THEN 
z:= x; 
ELSE 
z:= y; 
END IF; 
END; 
BEGIN 
a:= 23; 
b:= 45; 
findMin(a, b, c); 
dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.
IN & OUT Mode Example 2
This procedure computes the square useful of a passed value. this instance shows how we will use an equivalent parameter to simply accept a value then return another result.

DECLARE 
a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
x := x * x; 
END; 
BEGIN 
a:= 23; 
squareNum(a); 
dbms_output.put_line(' Square of (23): ' || a); 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Square of (23): 529

PL/SQL procedure successfully completed.
Methods for Passing Parameters
Actual parameters are often passed in 3 ways −
• Positional notation
• Named notation
• Mixed notation
Positional Notation
In positional representation system , you’ll call the procedure as −
findMin(a, b, c, d);
In positional representation system , the primary actual parameter is substituted for the primary formal parameter; the second actual parameter is substituted for the second formal parameter, and so on. So, a is substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.
Named Notation
In named notation, the particular parameter is related to the formal parameter using the arrow symbol ( => ). The procedure call are going to be just like the following −
findMin(x => a, y => b, z => c, m => d);
Mixed Notation
In mixed notation, you’ll mix both notations in procedure call; however, the positional representation system should precede the named notation.
The following call is legal −
findMin(a, b, c, m => d);
However, this is often not legal:
findMin(x => a, b, c, d);
So, this brings us to the end of blog. This Tecklearn ‘Procedures in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

455