(vii) PL/SQLPL/SQL process

A subroutine is a program unit/module that performs a specific task. These subroutines are combined to form a larger program. This is basically called "Modular design". A subroutine can be called by another subroutine or program that is called a calling program.

Subroutines can be created:
1. At the pattern level
2. Inside the bag
3. Within a PL/SQL block

1. The mode-level subroutine is a separate subroutine. It is created using the CREATE PROCEDURE or creation function statement. It is stored in the database and can be deleted using the drop procedure or DROP FUNCTION statement.
2. A subroutine created within a package is a wrapper subroutine. It is stored in the database and can be deleted only if the package is deleted using the drop PACKAGES statement. The
3.pl/sql subroutine is named, and a set of parameters can be used to invoke a PL/SQL block. PL/SQL provides two seed programs:
  functions: These subroutines return a value that is used primarily to calculate and return a value.
  procedure: These subroutines do not have a direct return value and are primarily used to perform operations.
 
II, PL/SQL subroutine section
each PL/SQL subroutine has a name and can have a list of parameters. Just like an anonymous PL/SQL block, and naming a block subroutine will also have the following three sections:

s.n.	Section & Description
1	Declaration Section It is an optional component. However, the Declarations section is used for subroutines and starts with the DECLARE keyword. It contains: types, cursors, constants, variables, exceptions, and nested-program declarations. These items are local to the subroutine and stop when the subroutine finishes executing before the execution is complete.
2	execute section This is a mandatory part that contains the execution of the specified action statement
3	exception handling Section This is also an optional section that contains code to handle run-time errors.

three or one creation process

  CREATE [OR REPLACE] procedure   procedure_name
  [(parameter_name [In | Out | In out] type [, ...]]
 {is | As}
 begin
   < Procedure_body;
  END procedure_name;

here,
  procedure-name  The name of the specified program
 [or REPLACE "option allows you to modify an existing program
  optional parameter list contains the names, patterns, and types of parameters. In indicates that the value will be passed externally, and out indicates that the parameter will be used to return a value from the procedure to the outside
  procedure-body  Contains the executable part of the
  as keyword in place of the IS keyword used to create a separate program.

example:
The following example creates a string of simple procedures that will ' Hello world! "appears on the screen.
CREATE OR REPLACE PROCEDURE greetings
as
BEGIN
    dbms_output.put_line (' Hello world! ');
END;
/
When the above code is executed with SQL hints, It produces the following result:
Procedure created.

three or two execute standalone program

   1. Using the Execute keyword
   2. The name of the procedure is called from the PL/SQL block
&NBSP;&NBSP;
Span style= "font-size:13px" and the above program named "Greetings"
1. Programs can invoke the EXECUTE keyword to:
EXECUTE greetings;
The above call will appear:
Hello world
PL/SQL procedure successfully completed.

2. The program can also be called from another PL/SQL block:
BEGIN
Greetings
END;
/
The above call will show:
Hello World
PL/SQL procedure successfully completed.

Three or three removing standalone programs
The syntax for deleting a program is:
DROP PROCEDURE Procedure-name;

The following statement deletes greetings:
BEGIN
DROP PROCEDURE Greetings;
END;
/

Four, PL/SQL sub-program parameter mode

S.N.	parameter mode & Description
1	Inch an in parameter passes a value to the subroutine. It is a read-only parameter. Inside the subroutine, an in parameter acts like a constant. It can no longer be assigned a value. You can use a constant, literal, initialize variable, or expression as an in parameter. It can also be initialized to the default value; In this case, however, it is deleted from the subroutine call. This is the default mode for parameter passing. Parameters are passed by reference.
2	Out The Out parameter returns a value to the calling program. the inside of the subroutine out parameter is like a variable. You can change its value and reference the assigned value. The actual argument must be a variable, which is passed by value.
2	In Out An in-out parameter passes the initial value to a subroutine, and returns an updated value to the caller. It can be assigned a value whose value can be read. The actual argument corresponding to an in-out formal parameter must be a variable, not a constant or an expression. The formal parameter must be assigned a value. The actual parameters are passed by value.

In & Out mode Example 1
The program looks for the minimum value in two values, where the procedure uses in mode to receive two numbers and returns their minimum values using the out parameter.

DECLARE a number;   b number; c number; PROCEDURE findmin (x in number, y in number, z out number) isbegin IF x<y then Z:=x; ELSE Z:=y; END IF; END; BEGIN A:= at; B:= $;   Findmin (A, B, c); Dbms_output.put_line ('Minimum of (+):'||c); END;///when the above code is executed at the SQL prompt, it produces the following results:Minimum of ( at, $) : atPL/sql procedure successfully completed.

In & Out mode Example 2
This program calculates the square value of the passed value. This example shows how we can accept values with the same parameters and then return another result.

DECLARE   a number; PROCEDURE Squarenum (x in Out number) Isbegin  x:= x * x; END; BEGIN   A:;   Squarenum (a);   Dbms_output.put_line (" | | a); END; When The above code is executed at the SQL prompt, it produces the following result:Square of (529PL/sql Procedure successfully completed.

Five, Method passing Parameters
The actual parameters can be used in the following three ways:
Location markers
naming symbols
Mixed symbols

1 -Position notation
In the position symbol, the program that can be called is:
Findmin (A, B, C, D);
In position notation, the first actual parameter is taken into the first form parameter, the second actual parameter is taken into the second form parameter, and so on. Well, a substituted x,b is substituted for y,c for substitution Z and D to be substituted for M.

2. Named notation
Called symbols, the actual parameters are related to the formal parameters using the arrow symbols (= =). So the program call will look like this:
Findmin (X=>a, Y=>b, Z=>c, m=>d);

3. Mixed notation
In mixed notation notation, you can mix both of these writing procedure calls; "However, the location tag should precede the specified symbol."
is legal: Findmin (A, B, C, m=>d);
However, this is not legal: Findmin (X=>a, B, C, d);

(vii) PL/SQLPL/SQL process