PL-SQL storage functions and stored procedures, pl-SQL stored procedures

PL-SQL storage functions and stored procedures, pl-SQL stored procedures
Zookeeper

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.

① 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 ...);

3. AUTHID
When creating a stored procedure, you can use the AUTHID CURRENT_USER or authid definer option to indicate the permissions used by Oracle during the process.
1) if you use the AUTHID CURRENT_USER option to create a process, Oracle uses the user permission to call the process to execute the process.
To successfully execute this process, the caller must have the required permissions to access all database objects referenced in the Stored Procedure body.

2) if you use the default authid definer option to create a process, Oracle uses the privilege of the process owner to execute this process. To successfully execute this process,
The process owner must have the required permissions to access all database objects referenced in the Stored Procedure body. To simplify the privileged Management of application users,
When creating a stored procedure, select the authid definer option. In this way, you do not have to authorize all users who need to call this procedure.

④ Delete stored procedures and functions
1. Deletion Process
You can use the drop procedure command to delete unnecessary processes. The syntax is as follows:
Drop procedure [user.] Procudure_name;
2. delete a function
You can use the drop function command to delete unnecessary functions. The syntax is as follows:
Drop function [user.] Function_name;

⑤ Example
[Storage function: return value. After creation, use select function () from dual; To execute]
[Stored procedure: Since no return value exists, the select statement cannot be used after creation, and only pl/SQL block can be used for execution]

[Format]
-- Function declaration (parameters are written in parentheses)
Create or replace function func_name (v_param varchar2)
-- Return Value Type
Return varchar2
Is
-- PL/SQL Block Variable, record type, and cursor Declaration (similar to the previous declare Section)
Begin
-- Function body (operations such as adding, deleting, modifying, and querying can be implemented, and return value is required)
Return 'helloworld' | v_logo;
End;

1. helloworld of the function: returns a "helloworld" string.

Create or replace function hello_func
Return varchar2
Is
Begin
Return 'helloworld ';
End;

Execute a function

Begin
Dbms_output.put_line (hello_func ());
End;

Or: select hello_func () from dual;

2. Return a "helloworld: atguigu" string, where atguigu is input when the function is executed.

-- Function declaration (parameters are written in parentheses)
Create or replace function hello_func (v_logo varchar2)
-- Return Value Type
Return varchar2
Is
-- PL/SQL Block Variable Declaration
Begin
-- Function body
Return 'helloworld' | v_logo;
End;

3. Create a storage function and return the current system time.
Create or replace function func1
Return date
Is
-- Define variables
V_date date;
Begin
-- Function body
-- V_date: = sysdate;
Select sysdate into v_date from dual;
Dbms_output.put_line ('I am a function ');

Return v_date;
End;

Execution Method 1:
Select func1 from dual;
Execution Method 2:
Declare
V_date date;
Begin
V_date: = func1;
Dbms_output.put_line (v_date );
End;
4. Define a function with parameters: Add two numbers

Create or replace function add_func (a number, B number)
Return number
Is
Begin
Return (a + B );
End;

Execute a function

Begin
Dbms_output.put_line (add_func (12, 13 ));
End;
Or
Select add_func (12, 13) from dual;

5. Define a function to get the total salary of a given department. The Department number is defined as a parameter and the total salary is defined as the return value.

Create or replace function sum_sal (dept_id number)
Return number
Is

Cursor sal_cursor is select salary from employees where department_id = dept_id;
V_sum_sal number (8): = 0;
Begin
For c in sal_cursor loop
V_sum_sal: = v_sum_sal + c. salary;
End loop;

-- Dbms_output.put_line ('sum salary: '| v_sum_sal );
Return v_sum_sal;
End;

Execute a function

Begin
Dbms_output.put_line (sum_sal (80 ));
End;

6. About OUT parameters: because a function can only return one value, PL/SQL programs can implement multiple return values through OUT parameters.

Requirement: define a function to obtain the sum of salaries of a given department and the total number of employees of this Department (defined as an OUT parameter ).
Requirement: The Department number is defined as a parameter, and the total salary is defined as the return value.

Create or replace function sum_sal (dept_id number, total_count out number)
Return number
Is

Cursor sal_cursor is select salary from employees where department_id = dept_id;
V_sum_sal number (8): = 0;
Begin
Total_count: = 0;

For c in sal_cursor loop
V_sum_sal: = v_sum_sal + c. salary;
Total_count: = total_count + 1;
End loop;

-- Dbms_output.put_line ('sum salary: '| v_sum_sal );
Return v_sum_sal;
End;

Execute the function:

Delare
V_total number (3): = 0;

Begin
Dbms_output.put_line (sum_sal (80, v_total ));
Dbms_output.put_line (v_total );
End;

7 *. Define a stored procedure: Get the total salary of a given department (using the out parameter). The requirement is that the Department number and total salary are defined as parameters.

Create or replace procedure sum_sal_procedure (dept_id number, v_sum_sal out number)
Is

Cursor sal_cursor is select salary from employees where department_id = dept_id;
Begin
V_sum_sal: = 0;

For c in sal_cursor loop
-- Dbms_output.put_line (c. salary );
V_sum_sal: = v_sum_sal + c. salary;
End loop;

Dbms_output.put_line ('sum salary: '| v_sum_sal );
End;
[Execution]
Declare
V_sum_sal number (10): = 0;
Begin
Sum_sal_procedure (80, v_sum_sal );
End;

8 *. to customize a stored procedure, complete the following operations:
Perform a salary increase for an employee of a given department (as input parameter (? , 95) salary increase % 5
[95, 98) % 3
[98 ,?) % 1
The following result is returned: how much extra cost does the company need to pay for this salary increase (define an OUT-type output parameter ).

Create or replace procedure add_sal_procedure (dept_id number, temp out number)

Is

Cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;
A number (4, 2): = 0;
Begin
Temp: = 0;

For c in sal_cursor loop
A: = 0;

If c. hd <to_date ('2017-1-1 ', 'yyyy-mm-dd') then
A: = 0.05;
Elsif c. hd <to_date ('2017-1-1 ', 'yyyy-mm-dd') then
A: = 0.03;
Else
A: = 0.01;
End if;

Temp: = temp + c. sal *;
Update employees set salary = salary * (1 + a) where employee_id = c. id;
End loop;
End;