PL/SQL Programming _ sub Programming

The use of PL/SQL enables modular programming .

In a PL/SQL block, you can define several sub-programs .

Code that is relatively independent and needs to be executed frequently is defined as a subroutine that is called according to the name of the subroutine when needed.
This not only facilitates the programming and coding, but also facilitates the debugging of the program.
PL/SQL has two forms of subroutines, namely procedures and functions.

You can also define variables, types, cursors, and so on in subroutines, or you can do exception handling.
When you use a program, you can pass parameters to the subroutine.

The difference between a procedure and a function is that the function has a return value that returns the execution result to the caller, and the procedure does not return a value.

How to use procedures

The definition of the subroutine appears in the declaration portion of the PL/SQL block, and its invocation appears in the executable portion of the PL/SQL block.
The process is defined in the following format:

PROCEDURE Procedure name (parameter 1 definition, parameter 2 definition ...) ）

As or is

Variable Declaration section

BEGIN

Executable section

EXCEPTION

Exception Handling Section

END;

Parameters can be defined in the procedure, and actual parameters can be passed to the procedure when the procedure is called.

If there are no parameters, the parentheses followed by the procedure name and the argument list can be omitted.

The parameters are defined in the following form:

Parameter Name argument pass mode data type: = Default value

where the parameter name and data type are essential parts, the other two parts can be omitted.

The parameter passing pattern includes in , out , and in out three forms, where in is the default delivery mode, and if not specified, the default is in, which refers to passing an actual argument from the caller to the procedure.

out refers to passing parameters to the caller from the procedure , and if you want to use this delivery pattern, you need to specify it explicitly.

When the procedure is called, the execution of the procedure affects the value of the variable.
In-out is a two-way delivery pattern that passes parameters from the caller to the procedure , passing the result from the procedure to the caller , if you want to use this form, it needs to be explicitly specified.

　　　　　　　　　In Out
the function of a formal parameter a constant uninitialized variable that is not initialized

The form constants, expressions, and variables of the actual arguments must be a variable that must be a variable

The default value of the

parameter is to pass the default value as an actual parameter to the procedure when the procedure is called, if no actual arguments are provided. The
data type is used to specify the type of the parameter, and you cannot specify a constraint on the parameter in the parameter definition, that is, you cannot specify properties such as the length of the parameter and whether it is empty.
defines two procedures in the following PL/SQL block, where the prompt process is used to increase wages and bonuses for employees in a department, while the Total_income process calculates the total revenue and tax payable for a department's employees.

DECLARE
DNO number;
Procedure promption (Salary in integer, Commiss in Integer, d_no in integer:=0)
Is
BEGIN
If D_no=0 then--representing all departments
UPDATE EMP
Set Sal=sal+salary,comm=comm+commiss;
Else-Indicates only the specified department
UPDATE EMP
Set Sal=sal+salary,comm=comm+commiss
WHERE Deptno=d_no;
END if;
END;
Procedure Total_income (D_no in integer:=0)
Is
Empno integer;
Total number;
Tax number;
BEGIN
If D_no=0 then--representing all departments
SELECT sum (SAL+NVL (comm, 0)), sum (sal*0.03) to total,tax from EMP;
Else-Indicates only the specified department
SELECT sum (SAL+NVL (comm, 0)), sum (sal*0.03) into Total,tax
From EMP
WHERE Deptno=d_no;
END if;
Dbms_output.put_line (' gross income: ' | | total | | ' Total tax: ' | | Tax);
END;
BEGIN--pl/sql The executable part of the block
dno:=10;
Promption (100,0,dno);
Total_income (DNO);
END;

Three parameters are defined in procedure promption, where the parameter d_no has a default value, so the default value is used when the procedure is called, if no actual arguments are supplied for the parameter.
For example, in the above example, a salary of $100 was added to the staff in department 10.
If you use the following call form, increase the salary for all employees in the department by $100:

Promption (100, 0);

When calling a procedure, you need to provide the actual parameters for the parameters in the procedure, which correspond in order.

To ensure that parameters are passed correctly to the procedure, it is required that all parameters with default values be set to the right of the parameter list when the procedure is defined.
Because this is the only way to assign other actual parameters to a previous parameter without a default value.
If a procedure has more than one parameter, the corresponding relationship between the two parameters is required when the procedure is called and the actual parameters are not supplied in the order of the argument list.

For example, a procedure promption can take the following invocation form:
Promption (d_no=>dno,commiss=>0, salary=>100);

The parameter D_no in procedure Total_inco1ne also comes with a default value, so that when the procedure is called, if the actual parameters are provided, the total employee income and taxes for the specified department are calculated, if the following call form is used:

Total_income ();

Calculates the total employee income and gross tax for all departments.
In both of these processes, the pass pattern for all parameters is in, which is to pass the actual arguments from the caller to the procedure.

This form is the default and can be omitted from the keyword.
If you need to reflect the execution of the process to the caller, you need to use the out form, or the "in Out" form.
For example, in order to calculate the total employee income and tax, and to reflect the results into the main program, the process Total_ income has been modified, adding two parameters, the transfer mode is out.
The modified PL/SQL block code is as follows:

DECLARE
DNO number;
Abed number;
XYZ number;
Procedure Total_income (D_no in Integer,total out number,tax out number)
As
Empno integer;
BEGIN
If d_no=0 then--representing all departments
SELECT sum (SAL+NVL (comm,0)), sum (sal*0.03) to total,tax from EMP;
Else-Indicates only the specified department
SELECT sum (SAL+NVL (comm,0)), sum (sal*0.03) into Total,tax
From EMP
WHERE Deptno=d_no;
END if;
END;
BEGIN
dno:=10;
Executable part of the--pl/sql block
Total_income (DNO,ABED,XYZ);
Dbms_output.put_line (' Total revenue: ' | | abed | | ' Total tax: ' | | XYZ);
END;

When calling the procedure Total_income, three actual parameters are provided, where the parameters Abed and XYZ have no actual values , even if there is no effect here, because the passing pattern of the two parameters is when the out process is executing, Assigns the value of the parameter total and tax to Abed and XYZ, respectively, so that the data in the process is passed to the caller.
Note: The in type parameter is not assignable as a variable.

PL/SQL Programming _ sub Programming