PL/SQL storage functions and stored procedures

PL/SQL storage functions and stored procedures

Oracle provides the ability to store PL/SQL programs in databases and run them anywhere. This is called a stored procedure or function.
Procedures and functions are collectively referred to as PL/SQL subprograms. They are named PL/SQL blocks and are stored in the database, and exchange information with the caller through input/output parameters or input/output parameters.
The only difference between a process and a function is that a function always returns data to the caller, while a process does not return data.

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------

① Create a function
1. Create embedded Functions
Syntax:
CREATE [or replace] FUNCTION function_name
[(Argment [{IN | in out}] Type,
Argment [{IN | OUT | in out}] Type]
[Authid definer | CURRENT_USER]
RETURN return_type
{IS |}
<Type. Variable description>
BEGIN
FUNCTION_body
EXCEPTION
Other statements
END;
Note:
1) or replace is optional. With it, you can create a new function or replace a function with the same name without conflict.
2) The function name is followed by an optional parameter list, which contains the IN, OUT, or in out Mark. parameters are separated by commas.
The IN parameter mark indicates that the value passed to the function does not change during the function execution;
The OUT mark indicates that a value is calculated in the function and passed to the call statement through this parameter;
The in out mark indicates that the value passed to the function can be changed and passed to the call statement. If the mark is omitted, the parameter is implicitly IN.
3) because the function needs to RETURN a value, RETURN contains the data type of the returned result.

2. Call of embedded Functions
The parameters defined during function declaration are called formal parameters, and the parameters passed when the application calls the function are called actual parameters. When an application calls a function,
You can use the following three methods to pass parameters to a function:
The first parameter transfer format is location notation. The format is:

The second parameter transfer format is called name notation. The format is:
Argument => parameter [,…]
Where: argument is a form parameter, which must be the same as the form parameter name declared during function definition. Parameter is the actual Parameter.
In this format, situation parameters and actual parameters appear in pairs, and the relationship between them is uniquely identified. Therefore, the order of parameters can be arranged in any order.

The third parameter transfer format is called hybrid Notation:
That is, when a function is called, the position representation and name representation are used to pass parameters for the function. When this parameter is used,
Parameters passed by location notation must be placed before the parameters passed by name notation. That is, no matter how many parameters a function has,
If one of the parameters uses name notation, all the parameters must use name notation.

No matter which parameter transmission method is used, there are only two methods for data transmission between the actual parameter and the formal parameter: The address transfer method and the value transfer method.
The address transfer method refers to passing the address pointer of the actual parameter to the form parameter when calling the function, so that the form parameter and the actual parameter point to the same area in the memory, this allows you to transmit parameter data.
This method is also called the reference method, that is, the formal parameter refers to the actual parameter data. All input parameters use the address transfer method to transmit data.
The value transfer method copies the data of the actual parameter to the form parameter, rather than passing the actual parameter address. By default, both output parameters and input/output parameters use the value passing method.
During function calling, ORACLE copies the actual parameter data to the input/output parameters. When the function Exits normally, also, the output parameters and input/output parameters are copied to the actual parameter variables.

3. default parameter values
When declaring a FUNCTION parameter in the create or replace function statement, you can use the DEFAULT keyword to specify the DEFAULT value for the input parameter.
After a function with the default value is created, if the actual parameter value is not provided for a parameter with the default value during function calling, the function uses the default value of this parameter.
However, when the caller provides actual parameters for the default parameter, the function uses the actual parameter value.
When creating a function, you can only set default values for input parameters, but not for input/output parameters.

② Creation process
1. Create a stored procedure
Creating a stored procedure on oracle server can be called by multiple applications. You can pass parameters to the stored procedure or send parameters to the stored procedure.
Creation process Syntax:
CREATE [or replace] PROCEDURE Procedure_name
[(Argment [{IN | in out}] Type,
Argment [{IN | OUT | in out}] Type]
[Authid definer | CURRENT_USER]
{IS |}
<Type. Variable description>
BEGIN
<Execution part>
EXCEPTION
<Optional exception handling program>
END;

2. Call the Stored Procedure
ORACLE uses the EXECUTE statement to call the stored procedure:
EXEC [UTE] Procedure_name (parameter1, parameter2 ...);

For more details, please continue to read the highlights on the next page:

1 2 Next Page