Storage Program (1) & mdash; MYSQL

Source: Internet
Author: User

MySQL supports storing several objects inServerFor future use. Some of these objects can be called by program code as needed, some will be automatically executed when the data table is modified, and some can be automatically executed at the scheduled time. They include the following:

1. stored functions ). Returns a computation result, which can be used in an expression.
2. stored procedure )., However, it can be used to complete general operations or generate a result set and pass it back to the customer.
3. trigger ). Associated with the data table. When the data table is modified by the NSERT, DELETE, or UPDATE statement, the trigger is automatically executed.
4. Events ). It is automatically executed at the scheduled time according to the schedule.

MySQL supports stored functions and stored procedures in version 5.0.0 and triggers and events in version 5.0.2 and 5.1.6, respectively. The storage program has the following advantages and capabilities:

1. the executable part of the stored program object can be compiled using compound statements. Composite statements can expand the SQL syntax, including code blocks, loops, and condition statements.
2. The stored programs are stored on the server. To define the code they need, you only need to pass the code once when they are created, instead of once each execution. This greatly reduces the overhead.
3. They can encapsulate complex computing into program units, and you can simply call them through the names of program units. You can even package a group of storage programs into a "function library" for other applications to call.
4. They provide an error handling mechanism.
5. They can improve database security. You can restrict and control access to sensitive data by selecting the permissions required for execution of the storage program.

Storage program. It refers to various types of storage objects (storage functions, stored procedures, triggers, and events ). A storage routine (stored routine) refers to a storage function and a stored procedure. The definition Syntax of these two objects is very similar, so they are naturally put together for discussion. Before discussing various types of storage programs, we should first learn: Compound statements.

1. Compound statement and statement Separator

A simple storage program contains only one SQL statement and does not need to be specially written. The following stored procedure uses a SELECT statement to list the names of data tables in the sampdb database:

 TABLE_NAME  TABLE_SCHEMA’sampdb’   TABLE_NAME;

However, a stored program does not have to contain only one simple SQL statement. They can contain multiple SQL statements and can be constructed using multiple syntaxes, such as local variables, conditional statements, loops, and nested statement blocks. To use these structures to write a stored program, you need to use a composite statement.Composite statements start with BEGIN and END, and can write any number of statements between them. These statements constitute a statement Block. The following stored procedure will display a welcome message with your username. If you are an anonymous user, the username will be "earthing ":

    username  hostname        ()     (   INSTR(,’@’)  SUBSTRING_INDEX(, ,                 CONCAT(,, ) ;

When using a composite statement, you must consider and solve the following problem: the statements in the composite statement block must be separated by semicolons (;), but because semicolons are also used by mysql programs by default.Statement SeparatorTherefore, a conflict occurs when the mysql program is used to define the storage program.
The solution to this problem is to use the delimiter command to redefine the statement Separator of the mysql program as another character or string, which must not appear in the definition of the storage routine. In this way, the mysql program will not interpret the semicolon as the statement Terminator,It will pass the entire object definition as a statement to the server. After defining the storage program, you can redefine the statement terminator of the mysql program as a semicolon.

      ,  CALL show_times();

When defining a stored procedure, the default Separator of the mysql program is changed to $ temporarily, and the stored procedure is executed after the default Separator of the mysql program is restored:

The principle here is: as long as a semicolon is used in a stored program's internal statement, the separator of the mysql program should be changed temporarily when the storage program is defined.

The storage function returns a calculation result to the caller. This result can be used in an expression (like a built-in function such as COS () or HEX ).Use the CALL statement to CALL the stored procedure.Is an independent operation and cannot be used in expressions. There are two main scenarios for using stored procedures: (1)You only need to perform an operation to achieve a certain effect or action without returning a value., (2)Multiple result sets are returned.(The function cannot do this ). This is just some guiding suggestions, not a hard rule.
For example, if you need to return two or more values, you cannot use a function. However, you can use a process because the parameter types supported by the process allow their values to be set during execution, and the caller can access those values after the process is returned.

You must use the create function statement to CREATE a stored FUNCTION, and the create procedure statement to CREATE a stored FUNCTION. The following example creates a function with an integer parameter representing the year. (To be different from the name of a data table or data column, the parameter name must be prefixed with p ).

  count_born_in_year(p_year       ( ()  president  (birtb) 

This function has a RETURNS clause used to indicate the Data Type of the returned value and a function body used to calculate that value. The function body must contain at least one RETURN statement to RETURN a value to the caller. The benefit of defining computing as a function is that you can easily execute it without writing all the logic every time. You can call storage functions like using built-in functions:

 count_born_in_year();

You cannot have a given function return multiple values. You can write any number of functions and call them all in the same statement. Another way is to use a stored procedure and return multiple values through its OUT parameter. Stored procedures are responsible for calculating those values and assigning them to corresponding parameters, which can be accessed by the caller after the process is returned. If you define a storage function with the same name as a built-in MySQL function, you must use the database name to limit the name of the function when calling it to avoid ambiguity.

Stored procedures are similar to stored functions, but they do not return values.. Therefore, it does not have a RETURNS clause or any RETURN statement. The following simple stored procedure is similar to the count_born_in_year () function. It displays a result set rather than the calculation result as its return value.

  show_born_in_year(p_year      (birth)

Unlike stored functions, stored procedures cannot be used in expressions. They can only be called using CALL statements. As follows:

CALL show_born_in_year();

The preceding example selects information, but the storage routine can also be used to modify data tables, as shown in the following example:

  update_expiration (p_id      member  expirationp_date  member_id

The storage function must comply with the following restrictions:You cannot modify the data tables that are being read or written by the statements that call this function.. Stored procedures generally do not have this restriction, but if they are called from stored functions, they need to be followed.

3. Permission to store functions and stored procedures

Stored functions and stored procedures belong to databases. To CREATE a stored function or stored procedure, you must have the create routine permission for that database. By default, when you create a storage ROUTINE, the server automatically grants you the EXECUTE and alter routine permissions (if you have not yet obtained these permissions ), in this way, you can execute the routine or delete it. When you delete the routine, the server automatically revokes those permissions. If you do not want to use this automatic permission granting/revocation mechanism, set the automatic_sp_privileges system variable to 0.

If the server is enabledBinary log functionThe storage function also needs to comply with some additional restrictions (do not allow creation of uncertain or will modify the data storage function) to ensure that binary logs can be safely backed up and copied. These restrictions are as follows:
1. If the log_bin_trust_function_creators system variable is not activated, you must have the SUPER permission to create a storage function. Under this premise, each function you create must be definite and data cannot be modified. To demonstrate this, you need to use DETERMINISTIC, no SQL, or one of reads SQL DATA to define the storage function.
2. If the log bin_trust_function_creators system variable has been activated, there is no restriction. This setting is the most appropriate only when you believe that all users on the MySQL server will not define insecure storage functions.

Restrictions related to log_bin_trust_function_creators system variables apply to trigger creation.

4. parameter types of Stored Procedures

Stored ProcedureParameters are divided into three types. For the IN parameter, the caller passes a value to the process. The process can modify this value, but any modification is invisible to the caller after the process is returned. The OUT parameter is the opposite. The process assigns a value to the OUT parameter, which can be accessed by the caller after the process is returned. The INOUT parameter allows the caller to pass a value to the process and retrieve another value.
To explicitly specify a type for a parameter, write IN, OUT, or inout in the parameter table before the parameter name. If no type is specified for the parameter, the default type is IN.

When the OUT or INOUT parameter is used, a variable name must be provided during the call process.You can set the parameter value during the process. The corresponding variable will get the value when the process returns.. If you want a stored procedure to return multiple result values, the OUT and INOUT parameter types are very useful (the stored function can return only one value and cannot be competent ). The following process demonstrates the usage of the OUT parameter. It will calculate the number of boys and girls in the student data table and return these two count values through its parameters so that callers can access them:

  count_students_by_sex(OUT p_male , OUT p_female      ()  student  sex   ()  student  sex  

When calling this process, replace each parameter with the corresponding User-Defined variables. In this process, the Count value is put into these parameters. After it returns, those variables will contain the Count value:

CALL count_students_by_sex(,  ,;

The IN, OUT, And INOUT keywords are not applicable to storage functions, triggers, or events. For stored functions, all parameters are like IN parameters. Triggers and events do not have any parameters at all. The next section describes the triggers and events.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.