Operations for Mysql stored procedures and functions (12) _mysql

Database object tables store and manipulate the logical structure of data, while database object stored procedures and functions are used to implement a set of SQL statements about table operations as a whole. In the database system, when the stored procedures and functions are invoked, the SQL statement sets that are set in those objects are executed, enabling the corresponding functionality.
1. Why use stored procedures and functions
sometimes a complete operation on a table is not always possible without a single SQL statement, but requires a set of SQL statements to implement. In a specific application, a complete operation will contain multiple SQL statements, which need to be executed selectively to execute the following SQL statements based on the execution result of the preceding SQL statement.
Stored procedures and functions can be simply understood as a collection of one or more SQL statements. Stored procedures and functions are a collection of SQL statements that are compiled beforehand and stored in a database.
What is the difference between stored procedures and functions? The main difference between the two is that the function must have a return value, while the stored procedure does not. Stored procedures have much more parameter types than function parameters.

The advantages of stored procedures and functions are as follows:
1. Stored procedures and functions allow standard component programming to improve the reusability, sharing, and portability of SQL statements.
2. Stored procedures and functions can achieve faster execution speed and reduce network traffic.
3. Stored procedures and functions can be exploited as a security mechanism.

The disadvantages of stored procedures and functions are as follows:
1. The writing of stored procedures and functions is more complex than simple SQL statements, requiring users to have higher skills and richer experience.
2. When writing stored procedures and functions, you need to create permissions for these database objects. =

2. Creating stored procedures and functions
2.1 Create a Stored procedure syntax form:

The grammatical forms are as follows:

CREATE PROCEDURE procedure_name ([procedure_parameter[,...]])
  [Characteristic ...] Routine_body

//Description: The Procedure_name parameter represents the name of the stored procedure to be created, and the Procedure_parameter parameter represents the parameters
of the stored procedure. The characteristic parameter represents the attributes of the stored procedure, and the Routine_body parameter represents the SQL statement code of the stored procedure, which can be used to flag the start and end of the SQL statement.
//NOTE: The stored procedure name cannot be duplicated with the stored procedure name that already exists when the stored procedure is specifically created, and the recommended stored procedure name is procedure_xxx or proce_xxx;

The syntax for each parameter in Procedure_parameter is:
[in| Out| INOUT] Parameter_name type

//each parameter in the statement consists of three parts, namely the input/output type, the parameter name, and the parameter type.

The value of the characteristic parameter is:
Language SQL
| [NOT] Deterministic
| {Constains SQL | no SQL | reads SQL Data|modifies SQL data}
|sql Security {definer | invoker}
|comment ' String '

1. Language SQL, which indicates that the routine_body part of the stored procedure consists of statements in the SQL language. All default statements for MySQL software.
2. [NOT] deterministic, which indicates whether the execution result of the stored procedure is determined. If the value is deterministic indicates that the execution result is OK. That is, each time a stored procedure is executed, the same output is obtained if the same parameter is entered, and if the value is not deterministic, the execution result is uncertain, that is, the same input may get different output. The default value is deterministic.
3. {contains sql|no sql|reads SQL Data|modifies SQL Data}, which represents the limit of the SQL statement, if the value is contains SQL representation can contain SQL statements, but does not contain statements that read or write data; If the value is no The SQL representation does not contain the SQL statement, if the value is reads SQL data represents the statement containing the read data, or if the value is modifies SQL data represents the statement containing the read data. The default value is contains SQL.
4. SQL Security{definer|invoker}, set who has permission to execute. If the value is definer, it means that only the definition can execute, and if the value is Invoker, the caller can execute it. The default value is Definer.
5. Comment ' String ' representing the comment statement.

2.2 Create a function syntax form:

The grammatical forms are as follows:

Create function function_name ([function_parameter[,...]])
  [Characteristic ...] Routine_body

In the preceding statement, the Function_name parameter represents the name of the function to be created; the Function_parameter parameter represents the parameter of the function, and the characteristic parameter represents the attribute of the function, which is the same as the value in the stored procedure. The routine_body parameter represents the SQL statement code for the function, and you can use Begin...end to represent the beginning and end of the SQL statement.

The syntax for each parameter in Function_parameter is as follows:
Parameter_name type

Each parameter in the preceding statement is composed of two parts, the parameter name and the argument type respectively. Parameter_name represents the name of the parameter. Type represents the parameter type.

2.3 Create simple stored procedures and functions:

A stored procedure that queries all employee wages in an employee table:
Example:

Mysql> delimiter $$
mysql> delimiter $$ CREATE PROCEDURE proce_employee_sal () 
    comment ' Inquire all employees ' wages ' 
    Begin 
     Select Sal from T_employee;
    End $$
    dilimiter;

Typically, when a stored procedure is created, the Terminator of the SQL statement is modified by the command delimiter && from the ";" Symbol to two dollar signs. This is mainly because the default statement terminator in the SQL statement is divided (;), that is, the SQL statement in the stored procedure also needs to end with a semicolon, after modifying the ending symbol to two dollar characters, you can avoid conflicts during execution. But in the end do not forget to pass the order "delimiter;" Modify the Terminator to the default ending symbol in the SQL statement.

To create a function example:

Delimiter $$
Create function func_employee_sal (empno int ())
 returns double (10,2)
 comment ' inquire about the wages of an employee '
 begin return
 (
  select Sal from T_employee where T_employee.empno=empno;
 )
end$$
delimiter;

A function named Func_employee_sal is created that has a type of int (11), a parameter named Empno, and the return value is a double (10,2) type. The SELECT statement queries from the T_employee table that the Empnoo field value is equal to the empno value of the passed-in parameter, and returns the value of the Sal field for that record.

3. Expressions about stored procedures and functions

3.1 Operation variables:
A variable is the most basic element in an expression statement and can be used to temporarily store data. You can store data that is queried from a table through variables.

3.1.1 Declaring variables:

The grammatical forms are as follows:
declare var_name[,...] Type [default value]

In the preceding statement, the Var_name parameter represents the name of the variable to declare, and the parameter type represents the type of the variable to declare, and default value is used to implement the defaults for setting the variable, if no default value of the statement is null. When you declare a variable, you can define multiple variables at the same time.

3.1.2 Assignment Variable:

The grammatical forms are as follows:
Grammar one:
set var_name=expr[,...]
Syntax Two:

Select filed_name[,...] into var_name[,...]
  From table_name
    WHERE condition

The Var_name parameter represents the name of the variable to be assigned, and parameter expr is an assignment expression about the variable. When you assign a value to a variable, you can assign values to multiple variables, separated by commas between the assignment statements for each variable.
In syntax two, the result of the query is assigned to the variable, the parameter Filed_name represents the field name of the query, and the parameter var_name represents the variable name. Assigns the result of the query to a variable, which can only be a single row.

Example:

DECLARE employee_sal int default 1000;

DECLARE employee_sal int default 1000;
Set employee_sal = 3500;

Select Sal into Employee_sal from T_employee where empno=7556;

3.2 Operating conditions:
3.2.1 Define conditions:

The grammatical forms are as follows:

DECLARE condition_name condition for Condition_value
condition_value:
  Sqlstate[value] Sqlstate_value
  |mysql_error_code

The Condition_name parameter represents the name of the condition to be defined, the type of parameter condition_value used to implement the setting condition, and the arguments Sqlstate_value and mysql_error_code the error used to set the condition.

3.2.2 defines the handler:

The grammatical form is:

Declare Handler_type handler for condition_value[,...] sp_statement
handler_type:
  continue
  |exit
  | Undo
Condition_value:
  sqlstate[value] sqlstate_value
  |condition_name
  |sqlwarning
  |not found
  |sqlexception
  |mysql_error_code

This statement specifies each handler that can handle one or more conditions. If one or more conditions are generated, the specified statement is executed. Continues after the execution handler statement for the current subroutine for an continue handler. For an exit handler, execution of the current Begin...end compound statement is terminated. The Undo handler type statement is not yet supported.
1. SQLWarning is shorthand for all SQLSTATE codes that begin with 01.
2. Not found is shorthand for all SQLSTATE codes that begin with 02.
3. SqlException is shorthand for all SQLSTATE code that is not captured by sqlwarning or not found.

3.3 Using cursors:
The MySQL query can return multiple record results, so how do you traverse the records in an expression? MySQL provides a cursor to implement. By specifying the rowset returned by the SELECT statement, including all rows that meet the conditions listed in the WHERE clause of the statement, the complete rowset called the result set is returned by the statement. An application requires a mechanism to process one or more consecutive rows of a result set at a time, and the cursor completes interaction with the application by specifying a record at a time.
A cursor can be viewed as a data type that can be used to traverse a result set, rather a pointer or an array subscript. The method of processing a result set can be positioned through a cursor to a row of the result set, searching for a row or a branch or the current row in the result set for data modification from the location of the current result sets.

3.3.1 Declaration cursor:

The grammatical forms are as follows:
DECLARE cursor_name cursor for select_statement;

In the preceding statement, the cursor_name parameter represents the name of the cursor, and the parameter select_statement represents a SELECT statement. Because cursors need to traverse each row in the result set, they increase the burden on the server, resulting in a low efficiency of the cursor. If the cursor operation has more than 10,000 rows of data, other ways should be used, and if you use a cursor, you should also try to avoid table join operations in the cursor loop.

3.3.2 Open cursors:

The grammatical form is:
Open cursor_name

Note that when you open a cursor, the cursor does not point to the first record, but to the front of the first record.

3.3.3 Using cursors:

The grammatical forms are as follows:
fetch cursor_name into var_name [, Var_name] ...

3.3.4 Close cursor:

The grammatical forms are as follows:
Close cursor_name

4. Modifying stored procedures and functions
for stored procedures and functions that have already been created, some definition changes will be required when used for a period of time. You can modify the stored procedure through the ALTER PROCEDURE statement and implement the modification function by using the ALTER FUNCTION statement.
4.1 Modify the stored procedure:

The grammatical forms are as follows:

Alter procedure procedure_name
  [characteristic ...]

The procedure_name parameter represents the name of the stored procedure that you want to modify, and the characteristic parameter specifies the attributes of the modified stored procedure, which can only be the following value compared to the parameter that defines the stored procedure:

| (Contains sql|no sql|reads SQL Data|modifys SQL data) 
| SQL Security {Definer|invoker} 
|comment ' string ' 
) 

4.2 Modifying functions:

The grammatical forms are as follows:

alter function function_name
  [characteristic ...]

The Function_name parameter represents the name of the function to be modified, and the characteristic parameter specifies the modified function attribute, which can only be the following value compared to the parameter that defines the function:
|( Contains sql|no sql|reads SQL Data|modifys SQL data)
|sql Security {Definer|invoker}
|comment ' String '

5. Delete stored procedures and functions
5.1 Delete stored procedures through the DROP statement:

The grammatical forms are as follows:
drop prcedure proce_name;

5.2 Removing functions through the DROP FUNCTION statement:

The grammatical forms are as follows:
drop function Func_name;

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.