Oracle PL/SQL programming stored procedures and functions

The main content of this article is as follows:

6.1 Introduction

6.2 create a function

6.3 stored procedures

6.3.1 creation process

6.3.2 call a stored procedure

6.3.3 AUTHID

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

6.3.5 procedure

6.3.6 deletion process and Function

6.3.7 comparison of processes and functions

6.1 Introduction
Procedures and functions (In addition, packages and triggers) are named PL/SQL blocks (also the user's solution object), which are compiled and stored in the database for execution. Therefore, other PL/SQL blocks can be used by name. Therefore, business logic and enterprise rules can be written as functions or stored in the database for sharing.

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. This section mainly introduces:

1. Create stored procedures and functions.

2. Correctly Use System-level Exception Handling and user-defined exception handling.

3. create and manage stored procedures and functions.

6.2 create a function
1. Create a function

 

Syntax:

CREATE [or replace] FUNCTION function_name
(Arg1 [{IN | OUT | in out}] type1 [DEFAULT value1],
[Arg2 [{IN | OUT | in out}] type2 [DEFAULT value1],
......
[Argn [{IN | OUT | in out}] typen [DEFAULT valuen])
[Authid definer | CURRENT_USER]
RETURN return_type
IS |
<Type. Declaration of variables>
BEGIN
Execution part
RETURN expression
EXCEPTION
Exception Handling
END function_name;

L IN, OUT, and in out are the form parameters. If this parameter is omitted, the IN mode is used. IN-mode parameters can only pass real parameters to the function and enter the function, but they can only be read and cannot be written. The value of real parameters remains unchanged when the function returns. In the OUT mode, the parameter ignores the value of the real parameter during the call (or the initial value of this parameter is always NULL), but can be read or written inside the function, when the function returns, the value of the form parameter is assigned to the real parameter. In out has the features of the first two modes. When called, the value of the real parameter is always passed to the form parameter. At the end, the value of the form parameter is passed to the real parameter. During the call, the real parameters of the IN mode can be constants or variables, but the real parameters of the OUT and in out modes must be variables.

L generally, the or repalce keyword is used only when the function_name function is a new function OR a function to be updated. Otherwise, it is easy to delete useful functions.

Example 1. Obtain the total salary of a department:

 

-- Obtain the total salary of a department
CREATE OR REPLACE
FUNCTION get_salary (
Dept_no NUMBER,
Emp_count out number)
RETURN NUMBER
IS
V_sum NUMBER;
BEGIN
Select sum (SALARY), count (*) INTO V_sum, emp_count
From employees where DEPARTMENT_ID = dept_no;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('the data you need does not exist! ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );
END get_salary;
 

2. function call

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, it can use the following three methods to pass parameters to the function:

The first parameter transfer format is location notation.

That is, the name of the real parameter is written in sequence according to the order of the form parameters during the call, and the form is associated with the real parameter for transmission. When this method is used for calling, the names of the parameters involved in the form are independent of each other, and there is no relationship between them. It is important to emphasize the order.

Format:

Argument_value1 [, argument_value2…]

Example 2: calculate the total salary of a department:

 

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum: = get_salary (10, v_num );
DBMS_OUTPUT.PUT_LINE ('total salary of Department number: 10: '| v_sum |', number of students: '| v_num );
END;
The second parameter transfer format is name notation.

That is, when you call the API, write the corresponding parameters according to the name of the parameter and the name of the real parameter, and associate the form with the real parameter for transmission. In this method, the names of the parameters involved in the form are independent and irrelevant. The correspondence between the names is the most important, and the order is not important.

Format:

Argument => parameter [,…]

Where: argument is a form parameter, which must be the same as the form parameter name declared at function definition as 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.

Example 3: calculate the total salary of a department:

DECLARE
V_num NUMBER;
V_sum NUMBER;
BEGIN
V_sum: = get_salary (emp_count => v_num, dept_no => 10 );
DBMS_OUTPUT.PUT_LINE ('total salary of Department number: 10: '| v_sum |', number of students: '| v_num );
END;
 
The third parameter transfer format is combined transfer.

That is, when a function is called, the position representation and name representation are used to pass parameters for the function. When this parameter transfer method is used, the parameters passed by location notation must be placed before the parameters passed by name notation. That is to say, no matter how many parameters a function has, as long as one of the parameters uses name notation, all the parameters must use name notation.

 

Example 4:

Create or replace function demo_fun (
Name VARCHAR2, -- note that VARCHAR2 cannot provide precision, for example, VARCHAR2 (10 ).
Age INTEGER,
Sex VARCHAR2)
RETURN VARCHAR2
AS
V_var VARCHAR2 (32 );
BEGIN
V_var: = name | ':' | TO_CHAR (age) | 'years old. '| sex;
RETURN v_var;
END;

DECLARE
Var VARCHAR (32 );
BEGIN
Var: = demo_fun ('user1', 30, sex => 'male ');
DBMS_OUTPUT.PUT_LINE (var );

Var: = demo_fun ('user2', age => 40, sex => 'male ');
DBMS_OUTPUT.PUT_LINE (var );

Var: = demo_fun ('user3', sex => 'female ', age => 20 );
DBMS_OUTPUT.PUT_LINE (var );
END;
 

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.

 

Example 5:

Create or replace function demo_fun (
Name VARCHAR2,
Age INTEGER,
Sex VARCHAR2 DEFAULT 'male ')
RETURN VARCHAR2
AS
V_var VARCHAR2 (32 );
BEGIN
V_var: = name | ':' | TO_CHAR (age) | 'years old. '| sex;
RETURN v_var;
END;
 

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.

DECLARE

Var VARCHAR (32 );

BEGIN

Var: = demo_fun ('user1', 30 );

DBMS_OUTPUT.PUT_LINE (var );

Var: = demo_fun ('user2', age => 40 );

DBMS_OUTPUT.PUT_LINE (var );

Var: = demo_fun ('user3', sex => 'female ', age => 20 );

DBMS_OUTPUT.PUT_LINE (var );

END;

1 2 3 4 Next Page