Hi Oracle [3]

Development subroutine

Development Process

Creation process
Create [or replace] Procedure procedure_name (argument1 [mode1] datatype1 ,...)
Is [as]
PL/SQL block;
Is or as is used to start a PL/SQL block mode which can be in, out, in out. The default is in

Parameter Transfer Method
Location Transfer
When a subroutine is called, the corresponding variable or value is specified for the parameter in sequence according to the parameter definition order.
Name Transfer
The subroutine is called to specify the parameter name and use the correlated symbol => to provide it with the corresponding value or variable
Combined Transfer
Use both

Deletion Process
Drop procedure procedure_name;

Develop Functions

Create a function
Create [or replace] function function_name (argument1 [mode1] datatype1 ,...)
Return datatype
Is [as]
PL/SQL block;
Return specifies the data type returned by the function. The function must contain at least one return statement.

Development Kit
Packages are used to logically combine PL/SQL types (table and record types), PL/SQL types (cursors and cursor variables), and PL/SQL subprograms (procedures and functions ). It consists of packege specification and package body.

Create a package

Establish package specifications
The package specification is used to define the public components (constants, variables, cursors, processes, and functions) of a package)
Create [or repalce] package package_name
Is |
Public type and item declarations
Subprogram specifications
End package_name;

Create a package
It is used to implement the process and function of locking the definition of the package specification.
Create [or repalce] package body package_name
Is |
Private type and item declarations
Subprogram bodies
End package_name;

Call package component-package name. component name

Purity level)
Definition
Prgma restrict_references (function_name, wnds, [wnps] [, rnds] [, rnps]);
Wnds -- used to restrict the function from modifying database data (prohibit DML execution)
Wnps -- used to restrict functions from modifying package variables (package variables cannot be assigned)
Rnds-used to restrict the function from reading database data (the Select Operation is prohibited)
Rnps -- used to restrict functions from reading package variables (package variables cannot be assigned to other variables)