The meaning of characteristic in MySQL Stored Procedure Definition,

The meaning of characteristic in MySQL Stored Procedure Definition,

 

The MySQL stored procedure is very wordy. Compared with the MSSQL or Oracle stored procedure, if it is not explicitly specified, it will implicitly specify a series of features (Characteristic) To create a stored procedure.

When you write a stored procedure using a graphic interface tool, the graphic interface tool automatically adds the content ratio,
If you use HeidiSQL to create a stored procedure, the default values of these features (characteristic) are automatically generated.
However, what are these features? What are the impacts? I have never figured out.

 

LANGUAGE SQL
Stored Procedure language. The default value is SQL. It indicates that the stored procedure is written in SQL. Currently, only SQL is supported. Other languages may be supported in the future.

NOT DETERMINISTIC
DETERMINISTIC input is a DETERMINISTIC output. The default value is not deterministic. The output is the same only for the same input. The current value is NOT used.

CONTAINS SQL
Provide the internal information of the data used by the subroutine. These feature values are currently provided to the server and are not used to restrict the actual use of data based on these feature values.
Includes the following four options:
1. contains SQL indicates that the subprogram does not contain statements for reading or writing data.
2. no SQL indicates that the subprogram does not contain SQL
3. reads SQL DATA indicates that the subprogram contains the statement for reading DATA, but does not contain the statement for writing DATA.
4. modifies SQL DATA indicates that the subprogram contains the statement for writing DATA.

SQL SECURITY DEFINER
Specifies whether the stored procedure is executed using the Creator's license or the executor's license. The default value is DEFINER.
DEFINER is called by the creator. For the current user, if the Creator has the permission to execute the stored procedure and has the permission to access the table, the current user can call the process successfully.
To put it bluntly, the current user calls the stored procedure. The specific operations performed in the stored procedure are performed with the user permission that defines the stored procedure.
INVOKER invocation is performed by the identity of the caller. For the current user, if the user has the permission to execute the stored procedure, access the table as the current identity. If the current identity does not have the permission to access the table, even if you have the permission to execute the process, you still cannot call the successful execution process.
To put it bluntly, the current user calls the stored procedure. Only when the current user has the permission to perform operations on the objects involved in the stored procedure can the execution be successful.

COMMENT''
The annotation information of the stored procedure is written in the COMMENT. It can only be a single line of text. Many texts will be removed to the carriage return and line feed. One word: Pull
Why is this nonsense?
In general, the landlord will comment out an example of calling the stored procedure in the remarks, so that others can call the stored procedure quickly when there are many parameters while debugging, it is troublesome to write parameters for half a day.
-- Therefore, there will be comments similar to the following, but all the statements in the comments will be saved as one line, and the format will be erased.
/*
Set @ p_parameter1 = 'abc ';
Set @ p_parameter2 = 200;
Call mysql_procedure (@ p_parameyter1, @ p_parameter2)
*/

 

The following is a demo of a stored procedure. delimiter is only used to let the stored procedure know that it is the ending mark when a character defined by delimiter is encountered.
MySQL does not support anonymous blocks. When can I define a stored procedure, I cannot resolve it myself?

Delimiter kkkkkkk (of course, fuck is also supported) create definer = 'root' @ '% 'Procedure 'porcedurename' (in p_parameter1 varchar (200), in p_parameter2 int, out p_outparameter int) -- stored procedure language. The default value is SQL, indicating that the stored procedure is written in SQL. Currently, only SQL is supported, in the future, it may support language SQL in other languages-whether DETERMINISTIC input is DETERMINISTIC output. The default value is NOT terministic. The output is the same only for the same input, currently, this value does NOT use not deterministic-provides the internal information of the data used by the subroutine. These feature values are currently provided to the server and do NOT constrain the actual use of the data based on these feature values, to put it bluntly, it is not used -- ontains SQL indicates that the subroutine is not included A statement containing read or write DATA -- no SQL indicates that the subprogram does not contain SQL -- reads SQL DATA indicates that the subprogram contains read DATA, but the statement that does not contain DATA writing -- modifies SQL data indicates the statement contains SQL of the subprogram containing DATA writing -- used to specify whether the stored procedure is executed using the permission of the creator or the executor's permission, the default value is the identity of the DEFINER -- DEFINER creator. If the Creator has the permission to access the tables in the stored procedure, the caller has the permission to execute the process and can execute the -- INVOKER caller identity, depends on whether the call has the execution process + permission for the SQL statement in the execution process SQL SECURITY DEFINER -- the stored procedure annotation information is written in the COMMENT, where it can only be a single line of text, many lines will be removed to the carriage return and line feed. One word: Pull-why is this nonsense? -- In general, the landlord will comment out an example of calling the stored procedure in the remarks, so as not to keep yourself alive. When debugging, when there are many parameters, it will be called, it is troublesome to write parameters for a long time. Therefore, there will be comments similar to the following, but the statements in the comments will be saved as one line, format:/* set @ p_parameter1 = 'abc'; set @ p_parameter2 = 200; call mysql_procedure (@ p_parameyter1, @ p_parameter2) */COMMENT ''begin select * from user where id = 100; endKKKKKKKK (if delimiter is fuck, it is fuck, and the end mark is only)

 

Reference: MySQL