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 to execute them wherever they are. This is called a stored procedure or function.

Procedures and functions are collectively referred to as PL/SQL subroutines. They are named PL/SQL blocks that are stored in the database and exchanged information 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 conflicting
2) The function name is followed by an optional list of references, including 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 function's operation;
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 number of references is implied in.

3) because the function needs to return a value, return includes 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 parameters passed to the function when the application is called are called actual parameters. When the application calls the function.
The following three methods can be used to pass the parameters to a function:
The first pass-through format is called positional notation, in the following format:

Another parameter pass format is called name notation, in the following format:
argument = parameter [,...]
In this case, argument is the formal parameter, which must be the same as the name of the form parameter declared when the function is defined. Parameter is the actual number of references.

In such a format, the number of the situation and the actual number of participants in pairs appear, the relationship between each other is determined, so the order of the parameters can be arbitrarily arranged.

The third parameter transfer format is called mixed notation:
That is, when a function is called, it is used at the same time by using positional notation and name notation to pass the parameters for the function. When using this method of passing a number of parameters,
The parameters that are passed by using positional notation must precede the number of references passed by the name notation. Other words. Regardless of how many parameters a function has,
Just one of the parameters is to use the name notation. The name notation must be used for all subsequent references.

Regardless of which method is used, there are only two ways to pass data between the actual and formal parameters: The method of transmission and the value of the transfer.

The so-called address method refers to when the function is called. The address pointers of the actual parameters are passed to the formal parameters so that the formal and actual parameters point to the same region in memory. Thus, the transfer of the parameter data is realized.

This method is also referred to as the participation method, that is, the formal parameters to participate in the actual parameters of the data.

Input parameters are used to transmit data using the method of transmission.
The pass-through method refers to the data of the actual parameters copied to the formal parameters, rather than the address of the actual number of references. By default, the output parameters and input/output parameters are used in the pass-through method.

When the function is called, Oracle copies the actual parameter data to the input/output parameters, and when the function exits normally, it copies the output form parameters and the input/output parameter data to the actual parameter variables.

3. Default value of the parameters
You can use Defaultkeyword to specify default values for input parameters when declaring function parameters in the Create OR REPLACE function statement.

After a function with a default value is created. When a function call is assumed, the function uses the default value of the parameter, assuming that no actual parameter is provided for the reference with the default value.
However, when the caller provides the actual number of parameters for the default, the function uses the actual parameters.
When you create a function, you can only set default values for input parameters, not default values for input/output parameters.

② creation Process
1. Create a stored procedure
A stored procedure is established on Oracle server that can be called by multiple applications, can pass a parameter to a stored procedure, and can be passed back to a stored procedure.
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
< run part >
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 you run the procedure.
1) Assuming that a procedure is created using the Authid current_user option, Oracle runs the procedure with the user right to invoke the procedure.
In order for the procedure to run successfully, the caller must have access to the required permissions for all database objects referenced in the stored procedure body

2) Assuming that the process is created with the default authid definer option, Oracle runs the procedure with the privileges of all of the procedures. In order to run the process successfully,
All of the procedures must have access to 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 process that need to be called.

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

⑤ Sample Example
[Stored function: There is a return value. After creation, via select function () from dual; run]
[Stored procedure: Because there is no return value, after creation, you cannot use the SELECT statement, only the PL/SQL block can run]

Format
--Declaration of function (the number of references is 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

Run function

Begin
Dbms_output.put_line (Hello_func ());
End

Or: Select Hello_func () from dual;

2 Returns a string of "Helloworld:atguigu". The Atguigu is entered by running the function.

--Declaration of function (the number of references is 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

Operation Method 1:
Select func1 from dual;
Operation Method 2:
Declare
V_date date;
Begin
V_date: = func1;
Dbms_output.put_line (v_date);
End
4. Define the function with the number of parameters: two numbers added

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

Run 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, requiring 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

Run function

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

6. Reference to Out type: Since the function can only have a return value, the PL/SQL program can implement multiple return values through the out-type parameters

Requirement: Define a function: Gets the sum of the wages for a given department and the total number of employees in that department (defined as the number of out types).
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

To run the 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), requiring: The department number and the payroll are defined as the number of 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
Run
Declare
V_sum_sal Number (10): = 0;
Begin
Sum_sal_procedure (80,v_sum_sal);
End

8*. Define a stored procedure yourself complete the following actions:
Pay a raise for the employee of a given department (as input) if it is at the company's time

, 95) period, for which the increase in%5
[95, 98)%3
[98,?] %
Get the following return result: How much extra cost per month is required for this pay rise (define an out-of-the-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