In this section, we will talk about Procedures in PL/SQL. A subprogram is a program unit/module that plays out a specific assignment. These subprograms are consolidated to frame bigger projects. This is essentially called the 'Secluded plan'. A subprogram can be conjured by another subprogram or program which is known as the calling program.
A subprogram can be made −
- At the pattern level
- Inside a bundle
- Inside a PL/SQL block
At the composition level, subprogram is an independent subprogram. It is made with the CREATE PROCEDURE or the CREATE FUNCTION proclamation. It is put away in the data set and can be erased with the DROP PROCEDURE or DROP FUNCTION proclamation.
A subprogram made inside a bundle is a bundled subprogram. It is put away in the data set and can be erased just when the bundle is erased with the DROP PACKAGE proclamation. We will talk about bundles in the part 'PL/SQL - Packages'.
PL/SQL subprograms are named PL/SQL blocks that can be conjured with a bunch of boundaries. PL/SQL gives two sorts of subprograms −
- Capacities − These subprograms return a solitary worth; mostly used to register and restore a worth.
- Strategies − These subprograms don't restore a worth straightforwardly; primarily used to play out an activity.
This part will cover significant parts of a PL/SQL system. We will talk about PL/SQL work in the following section.
Parts of a PL/SQL Subprogram
Each PL/SQL subprogram has a name, and may likewise have a boundary list. Like mysterious PL/SQL blocks, the named squares will likewise have the accompanying three sections −
S.No | Parts & Description |
---|---|
1 |
Declarative Part It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution. |
2 |
Executable Part This is a mandatory 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 system is made with the CREATE OR REPLACE PROCEDURE explanation. The streamlined language structure for the CREATE OR REPLACE PROCEDURE explanation is as per the following −
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Where,
- method name indicates the name of the technique.
- [OR REPLACE] alternative permits the alteration of a current methodology.
- The discretionary boundary list contains name, mode and kinds of the boundaries. IN addresses the worth that will be passed from outside and OUT addresses the boundary that will be utilized to restore an incentive outside of the system.
- method body contains the executable part.
- The AS catchphrase is utilized rather than the IS watchword for making an independent method.
Example
The accompanying model makes a straightforward technique that shows the string 'Hi World!' on the screen when executed.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
At the point when the above code is executed utilizing the SQL brief, it will create the accompanying outcome −
Procedure created.
Executing a Standalone Procedure
An independent system can be brought twoly −
- Utilizing the EXECUTE watchword
- Calling the name of the method from a PL/SQL block
The above methodology named 'welcome' can be called with the EXECUTE catchphrase as −
EXECUTE greetings;
The above call will show −
Hello World
PL/SQL procedure successfully completed.
The method can likewise be called from another PL/SQL block −
BEGIN
greetings;
END;
/
The above call will show −
Hello World
PL/SQL procedure successfully completed.
Deleting a Standalone Procedure
An independent method is erased with the DROP PROCEDURE articulation. Punctuation for erasing a technique is −
DROP PROCEDURE procedure-name;
You can drop the welcome strategy by utilizing the accompanying assertion −
DROP PROCEDURE greetings;
Parameter Modes in PL/SQL Subprograms
The accompanying table records out the boundary modes in PL/SQL subprograms −
S.No | Parameter Mode & Description |
---|---|
1 |
IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference. |
2 |
OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed 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 can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value. |
The genuine boundary relating to an IN OUT proper boundary should be a variable, not a consistent or an articulation. Formal boundary should be relegated a worth. Real boundary is passed by esteem.
IN & OUT Mode Example 1
This program finds the base of two qualities. Here, the technique takes two numbers utilizing the IN mode and returns their base utilizing the OUT boundaries.
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;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
IN & OUT Mode Example 2
This method registers the square of estimation of a passed esteem. This model shows how we can utilize a similar boundary to acknowledge a worth and afterward return another outcome.
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;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Square of (23): 529
PL/SQL procedure successfully completed.
Methods for Passing Parameters
Real boundaries can be passed threely −
- Positional documentation
- Named documentation
- Blended documentation
Positional Notation
In positional documentation, you can call the system as −
findMin(a, b, c, d);
In positional documentation, the primary real boundary is fill in for the principal formal boundary; the second genuine boundary is fill in for the second proper boundary, etc. Along these lines, an is fill in for x, b is fill in for y, c is fill in for z and d is fill in for m.
Named Notation
In named documentation, the genuine boundary is related with the proper boundary utilizing the bolt image ( => ). The strategy call will resemble the accompanying −
findMin(x => a, y => b, z => c, m => d);
Mixed Notation
In blended documentation, you can blend the two documentations in technique call; in any case, the positional documentation ought to go before the named documentation.
The accompanying call is legitimate −
findMin(a, b, c, m => d);
Be that as it may, this isn't lawful:
findMin(x => a, b, c, d);