Pl-sql storage functions and stored procedures



Pl-sql storage functions and stored procedures

ORACLE provides the ability to store PL/SQL programs in a database, and can run it from anywhere. This is called a stored procedure or function.
Procedures and functions are collectively known as PL/SQL subroutines, which are named PL/SQL blocks that are stored in the database and exchanged with their callers through input, output parameters, or input/output parameters.
The only difference between a procedure and a function is that the function always returns data to the caller, and the procedure does not return data.

① Creating a function
1. Create an inline function
The syntax is as follows:
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| As}
< type. Description of the variable >
BEGIN
Function_body
EXCEPTION
Other statements
END;
Description
1) OR REPLACE is optional. With it, you can either create a new function or replace a function with the same name without any conflict
2) The function name is followed by an optional parameter list that contains in, out, or out tags. The parameters are separated by commas.
The in parameter token indicates that the value passed to the function does not change in the execution of the function;
An out tag indicates that a value is evaluated in a function and passed to the calling statement by that parameter;
The in out token indicates that the value passed to the function can vary and be passed to the calling statement. If the tag is omitted, the parameter is implicitly in.
3) because the function needs to return a value, return contains the data type that returns the result.

2. Calls to inline functions
The parameters defined by the function declaration are called formal parameters, and the arguments passed to the function when the application is called are called actual parameters. When the application calls the function,
You can use the following three methods to pass parameters to a function:
The first parameter passing format is called positional notation, in the form of:

The second type of parameter passing format is called name notation, in the following format:
argument = parameter [,...]
Where: argument is the formal parameter, which must be the same as the formal parameter name declared when the function is defined. Parameter is the actual parameter.
In this format, the situation parameters and the actual parameters in pairs appear, the relationship between the unique determination, so the order of the parameters can be arbitrarily arranged.

The third parameter passing format is called mixed notation:
That is, when a function is called, both positional notation and name notation are used to pass parameters to the function. When using this parameter transfer method,
Parameters passed using positional notation must precede the parameters passed by the name notation. That is, no matter how many arguments a function has,
As long as one of the parameters uses the name notation, all subsequent arguments must use the name notation.

Regardless of the parameter passing method, there are only two methods of data transfer between the actual parameter and the formal parameter: The method of the transmission and the value of the transfer.
The so-called pass-through method refers to the actual parameter's address pointer is passed to the formal parameter when the function is called, so that the formal parameter and the actual parameter point to the same region in memory, thus realizing the transfer of the parameter data.
This method is also called the reference method, that is, the formal parameter refers to the actual parameter data. The input parameters are transmitted using the data transmission method.
The value-transfer method is to copy the data of the actual parameter to the formal parameter, rather than the address of the actual parameter. By default, the output parameters and input/output parameters are passed as a pass-through method.
When the function is called, Oracle copies the actual parameter data to the input/output parameters, and when the function exits, it copies the output form parameter and the input/output form parameter data to the actual parameter variable.

3. Parameter Default value
You can specify a default value for an input parameter using the Defaults keyword when declaring a function argument in the Create OR REPLACE function statement.
After a function that has a default value is created, the function uses the default value of the parameter when it is called, if no actual parameter value is supplied for the parameter with the default value.
But when the caller provides the actual arguments for the default arguments, the function uses the actual parameter values.
When you create a function, you can only set default values for input parameters, and you cannot set default values for input/output parameters.

② creation Process
1. Create a stored procedure
Establishing stored procedures on Oracle server can be called by multiple applications, passing parameters to stored procedures, or passing parameters back to stored procedures.
To create a procedure syntax:
Create[or REPLACE] PROCEDURE procedure_name
[(argment [{in| In Out}] Type,
argment [{in| Out| In Out}] Type]
[AUTHID definer| Current_User]
{is| As}
< type. Description of the variable >
BEGIN
< Executive section >
EXCEPTION
< optional exception error handlers >
END;

2. Calling a stored procedure
ORACLE uses the EXECUTE statement to implement a call to a stored procedure:
Exec[ute] Procedure_name (Parameter1, parameter2 ...);

3.AUTHID
When you create a stored procedure, you can use the Authid current_user or authid definer option to indicate the permissions that Oracle uses when the procedure is executed.
1) If you create a procedure using the Authid current_user option, Oracle executes the procedure with the user right to invoke the procedure.
In order to successfully execute the procedure, the caller must have access to all database objects referenced in the stored procedure body

2) If the process is created with the default authid definer option, Oracle executes the procedure with the privileges of the procedure owner. In order to successfully perform this procedure,
The owner of the procedure must have the permissions necessary to access all the database objects referenced in the stored procedure body. To simplify the privileged management of application users,
When you create a stored procedure, you generally choose the authid definer option –--so that you do not have to authorize all users of this procedure that need to be called.

④ deleting stored procedures and stored functions
1. Delete procedure
You can use the drop procedure command to delete an unwanted procedure, with the following syntax:
DROP PROCEDURE [user.] Procudure_name;
2. Delete a function
You can use the drop function command to delete unwanted functions, with the following syntax:
DROP FUNCTION [user.] Function_name;

⑤ Example
[Stored function: There is a return value, after creation is completed, through the Select function () from dual; execution]
[Stored procedure: Because there is no return value, after creation is complete, you cannot use the SELECT statement, only PL/SQL block execution]

Format
--declaration of functions (with arguments written in parentheses)
Create or Replace function Func_name (V_param varchar2)
--Return value type
return VARCHAR2
Is
--pl/sql block variable, record type, cursor declaration (similar to the part of the previous declare)
Begin
--function body (can make additions and deletions and other operations, return value needs return)
Return ' HelloWorld ' | | V_logo;
End

HelloWorld of the 1 function: Returns a string of "HelloWorld"

Create or Replace function Hello_func
return VARCHAR2
Is
Begin
Return ' HelloWorld ';
End

Execute function

Begin
Dbms_output.put_line (Hello_func ());
End

Or: Select Hello_func () from dual;

2 Returns a string of "Helloworld:atguigu", where Atguigu is entered by the execution of the function.

--declaration of functions (with arguments written in parentheses)
Create or Replace function Hello_func (V_logo varchar2)
--Return value type
return VARCHAR2
Is
Declaration of the--PL/SQL block variable
Begin
--Function body
Return ' HelloWorld ' | | V_logo;
End

3 Create a storage function that returns the current system time
Create or Replace function func1
Return date
Is
--Defining variables
V_date date;
Begin
--Function body
--v_date: = sysdate;
Select Sysdate to v_date from dual;
Dbms_output.put_line (' I am a function oh ');

return v_date;
End

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

Create or Replace function Add_func (a number, b number)
return number
Is
Begin
return (A + B);
End

Execute function

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

5. Define a function: Gets the sum of the wages for a given department, requires that the department number is defined as a parameter, and the payroll is defined as the return value.

Create or Replace function Sum_sal (dept_id number)
return number
Is

Cursor Sal_cursor is a 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 function

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

6. Parameters for out type: Because a function can have only one return value, a PL/SQL program can have multiple return values through an out-type parameter implementation

Requirement: Define a function that gets the sum of the payroll for a given department and the total number of employees in that department (defined as an out type parameter).
Requirement: The department number is defined as a parameter and the payroll 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 a 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 function:

Delare
V_total Number (3): = 0;

Begin
Dbms_output.put_line (Sum_sal (v_total));
Dbms_output.put_line (v_total);
End

7*. Define a stored procedure: Gets the sum of the wages for a given department (through out parameters), requires: Department number and payroll 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
Implementation
Declare
V_sum_sal Number (10): = 0;
Begin
Sum_sal_procedure (80,v_sum_sal);
End

8*. Customize a stored procedure to complete the following actions:
A pay raise for an employee of a given department (as an input parameter), if it is to the company at the time of (?, 95).
[95, 98)%3
[98,?] %
The following results are returned: How much extra cost is required per month for this pay raise (define an out-of-type output parameter).

Create or Replace procedure Add_sal_procedure (dept_id number, temp out number)

Is

Cursor Sal_cursor is a 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 (' 1995-1-1 ', ' yyyy-mm-dd ') then
A: = 0.05;
Elsif C.hd < To_date (' 1998-1-1 ', ' yyyy-mm-dd ') then
A: = 0.03;
Else
A: = 0.01;
End If;

Temp: = temp + c.sal * A;
Update employees Set salary = Salary * (1 + a) where employee_id = C.id;
End Loop;
End

Pl-sql storage functions and stored procedures