PL/SQL Programming _ Stored Procedures

The basic writing method for PL/SQL blocks is described in the previous section.

when you write a PL/SQL program in Sql*plus and execute it in Sql*plus, the code for the PL/SQL block is stored in the Sql*plus buffer.
If you execute other SQL statements or PL/sql*plus blocks in the buffer, the new code is stored in the buffers, and the original PL/SQL blocks are purged from the buffer.
This non-named PL/SQL block that is temporarily stored in a buffer is called an anonymous block.
An anonymous block is a PL/SQL block with no name, which is stored only in the buffer and can only be executed in the current sql*plus environment.
If you want PL/SQL blocks to be invoked at any time and can be shared by the database user , you need to create a stored program .
A stored program is a PL/SQL block with a name, and the user can make multiple calls based on its name.

The stored program is compiled and optimized at the time of creation and is stored in the database, which can be called by any user with the appropriate permissions .
And it does not need to be compiled at the time of invocation, so it can be executed at a very fast speed.
stored programs are stored in the database as database objects, as opposed to anonymous blocks , so you first create a stored program in the database.

The calls to the stored program can be made in SQL statements, in the application, in Sql*plus, and in other PL-SQL blocks.
The first time it is called, the code of the stored program is loaded into the shared pool of the system global, and it can be executed directly from the shared pool when it is re-invoked.
The difference between a stored program and a subroutine described earlier is that a subroutine is a segment of a program that accomplishes a particular function, which itself cannot be executed as a single candle, but only as a module that is invoked inside a PL/SQL block.
a stored program, however, is a program that can be executed independently, and it may contain multiple subroutines that can be invoked in SQL statements, in applications, in Sql*plus, and in other PL/s blocks.
Stored procedures include: stored procedures, storage functions, triggers, packages, and so on.

Stored Procedures

If a user wants to create a stored procedure in their own mode and needs to have the Create PROCEDURE system permission , you will need to have the Create any PROCEDURE system permission If you want to create the stored procedure in another user's mode.
The syntax for creating a stored procedure is:

CREATE OR REPLACE PROCEDURE Procedure name (parameter 1, parameter 2 ...) ）

AUTHID Current_User | Definer

As

Declarations section

BEGIN

Executable section

EXCEPTION

Exception Handling Section

END;

Where the or Replace option works is when a stored procedure with the same name exists, it is first deleted, and then a new stored procedure is created.
Of course, the condition is that the current user has permission to delete the original stored procedure.

Stored procedures have been compiled and optimized during the creation process.
If you need to modify the stored procedure, you cannot directly modify its source code, only the Create command can be recreated.
This is the case with stored procedures, stored functions, and packages.
Stored procedures can have parameters, so you need to specify the appropriate actual parameters when you call the stored procedure.

If there are no parameters, the parentheses and argument lists following the procedure name can be omitted.
Each parameter is defined in the following format:

Parameter Name argument pass mode data type: = Default value

The usage of the parts in each definition of a parameter is exactly the same as the parameters in the subroutine.

The AUTHID option is used to specify the permissions that the stored procedure executes.

This option has two optional values, current_user and definer , which can only be selected.
The performer and creator of a procedure may not be the same user, and if you use Current_User to create a stored procedure, the procedure is executed as the current logged-on user when called.
To do this, the creator of the procedure must grant the current user permission to perform the procedure.

If you create a stored procedure with Definer, the procedure executes as the creator when called, which is the default option when you create the stored procedure.
variables, types, subroutines, cursors, and other elements can be defined in a stored procedure and are defined in exactly the same way as in an anonymous block.

This is not described in detail here. The declaration portion of the stored procedure begins with the key, and ends with the keyword begin, and
And you do not need to use the keyword declare.
The executable part of a stored procedure is its main part, which can contain SQL statements and flow control statements, which are stored procedures
Function of the central embodiment. The exception handling section is used to handle errors that may occur during the execution of the stored procedure. For example, the following
Code to create a stored procedure total_income that calculates the total revenue of a department's employees. This process has a
parameter, which represents the department number and specifies the default value. This allows the specified part to be evaluated when a parameter is supplied at the time of invocation.
The data for all employees will be calculated.

PL/SQL Programming _ Stored Procedures