Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of stored procedures and stored functions in MySQL", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let me lead you to study and learn about "sample analysis of stored procedures and stored functions in MySQL".

MySQL PROCEDURE

A stored procedure (PROCEDURE) is a set of specified SQL statements stored in the server, and the client can reference the stored procedure without constantly reissuing a single statement. Stored procedure types are divided into stored procedures (PROCEDURE) and stored functions (FUNCTION).

Z. stored procedure: call the procedure through the CALL statement. These procedures can use output variables or result sets to return values.

Z. store function: call the function in a statement. These functions return scalar values.

Benefits of using stored procedures:

1) client function

With stored procedures, you can create a single statement or a series of statements in a database for use by multiple client applications written in different programming languages or running on different platforms.

2) Security

Stored procedures provide a solution for applications that require the highest level of security. For example, banks use stored procedures and functions for all common operations. This provides a consistent and secure environment. Stored procedures can be encoded to ensure that each operation is recorded correctly. In such settings, applications and users cannot access database tables directly and can only execute specific stored procedures.

3) performance

Because there is less information to send between the server and the client, stored procedures can improve performance. Instead of passing all the statements contained in the stored procedure, the client calls the stored procedure by name.

4) function library

Through stored procedures, you can use libraries in the database server. These libraries are used as API for the database.

L problems with using stored procedures:

1) increased server load

Executing stored procedures in the database itself increases the load on the server and reduces the performance of the application. You can run tests and use common sense to ensure that the convenience of including logic in the database itself is more significant than the performance problems that may arise.

2) the development tools are limited

The development tools that support stored procedures in MySQL are not as mature and explicit as they are in more general programming languages. This limitation makes the process of writing and debugging stored procedures more difficult and needs to be considered in the decision-making process.

3) the language function and speed are limited.

Although there are great advantages in including logic in the database itself in many cases, there are still limitations in terms of achievable content compared with other programming languages. Stored procedures are executed in the context of a database and perform better when dealing with large amounts of data than stored procedures in client applications, but client application languages may have more powerful, more general processing, integration, or other library functions. You must consider the scope of the required functionality to ensure that the best possible solution is used for each stored procedure.

4) debugging and profiling functions are limited

1.1. Execute stored procedure

The commands used to invoke stored procedures are very similar to other commands in MySQL. Use the CALL statement to call the stored procedure (procedure). Stored procedures return values using output variables or result sets. Executing FUNCTION, like any other function, calls the function from within the statement (that is, by calling the name of the corresponding function), and the function returns a scalar value.

Each stored procedure is associated with a specific database. This has multiple implications:

·USE: when a stored procedure is called, MySQL executes an implicit USE while the stored procedure is running. You cannot issue USE statements within a stored procedure.

Qualified name: the database name of the stored procedure can be used to qualify the stored procedure name. Do this to reference a stored procedure other than the current database. For example, to call the stored procedure p or function f associated with the test database, use CALL test.p () or test.f ().

Database deletion: when you delete a database, all stored procedures associated with it are also deleted.

MySQL allows the use of regular SELECT statements within stored procedures. The result set of such a query is sent directly to the client.

1) sample stored procedure

Mysql > DELIMITER / /

Mysql > CREATE PROCEDURE record_count ()

-> BEGIN

-> SELECT 'Country count', COUNT (*) FROM Country

-> SELECT 'City count', COUNT (*) FROM City

-> SELECT 'CountryLanguage count', COUNT (*) FROM CountryLanguage

-> END//

Mysql > DELIMITER

Compound statement

By using BEGIN in stored procedures... Using END syntax and using triggers, you can create compound statements. BEGIN... An END block can contain zero or more statements. An empty compound statement is legal, and there is no limit to the number of statements in the compound statement.

ZD delimiter

In BEGIN... In END syntax, each statement must be terminated with a semicolon (;). Because the mysql client uses a semicolon as the default termination character for the SQL statement, you must use the DELIMITER statement to change this setting when using the mysql command line client interactively or for batch processing.

In the example, the first DELIMITER statement is used to change the termination character of the SQL statement to two forward slashes (/ /). This change ensures that the client does not interpret the semicolon in the compound statement as a statement delimiter and that the client does not send the CREATE PROCEDURE statement to the server prematurely. When the statement that creates the stored procedure is terminated by / /, the client sends the statement to the server before issuing a second DELIMITER statement to reset the statement delimiter to a semicolon.

2) Storage function: exampl

Mysql > DELIMITER / /

Mysql > CREATE FUNCTION pay_check (gross_pay FLOAT (9), tax_rate FLOAT (3))

-> RETURNS FLOAT (9pm 2)

-> NO SQL

-> BEGIN

-> DECLARE net_pay FLOAT (9pm 2)

-> DEFAULT 0

-> SET net_pay=gross_pay-gross_pay * tax_rate

-> RETURN net_pay

-> END//

Mysql > DELIMITER

Angular RETURNS clause

The RETURNS clause is used to determine the type of value to return by this function.

Zero feature

Through a number of features, the nature of the data used by the storage function can be determined. In MySQL, these features are for reference only. The server does not use these features to limit the types of statements that are allowed to be executed by stored functions.

L CONTAINS SQL means that the storage function contains statements for reading or writing data. If none of the above features are explicitly provided, this is the default value.

L NO SQL means that the storage function does not contain any SQL statements.

L READS SQL DATA means that the storage function contains statements for reading data (for example, SELECT), but not statements for writing data.

L MODIFIES SQL DATA indicates that the stored procedure contains statements (for example, INSERT or DELETE) that are used to write data.

Note: after binary logging is enabled, MySQL generates an error: NO SQL, READS SQL DATA, or DETERMINISTIC if one of the following items is not specified when the function is created.

Z. DECLARE statement

Use DECLARE statements in stored procedures to declare local variables and initialize user variables. You can add the DEFAULT clause to the end of the DECLARE statement to specify the initial value for the user variable. If you omit the DEFAULT clause, the initial value of the user variable is NULL.

Z. SET statement

With the SET statement, you can use = or: = as the assignment operator to assign values to defined variables.

Z. RETURN statement

The RETURN statement is used to terminate the execution of the stored function and return the value expression to the function caller.

1.2. Check stored procedures

Z. SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION

These statements are MySQL extensions, similar to SHOW CREATE TABLE. These statements return a specific string that can be used to recreate the specified stored procedure. One of the main limitations of these statements is that you must know the name of the procedure or function and must determine that it is a procedure or function before you can try to view the appropriate information.

Z. SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS

These statements are specific to MySQL. They return the characteristics of the stored procedure, such as database, name, type, creator, and date of creation and modification. These statements have the advantage of displaying specific stored procedures based on LIKE mode. If no mode is specified, information for all stored procedures or stored functions is listed based on the statement used. For example, the following statement displays information about a process whose name begins with "film":

SHOW PROCEDURE STATUS LIKE 'film%'\ G

Dead INFORMATION_SCHEMA.ROUTINES

The INFORMATION_SCHEMA.ROUTINES table contains information about stored procedures (procedures and functions) and returns information that can be simultaneously displayed in SHOW CREATE. And SHOW... Most of the details found in the STATUS statement to contain the actual syntax used to create the stored procedure. Of these three options, this table fully renders the stored procedures available in the database.

Example:

Mysql > SELECT routine_name, routine_schema, routine_type, definer

> FROM INFORMATION_SCHEMA.ROUTINES

> WHERE routine_name LIKE 'film%'

+-+

| | routine_name | routine_schema | routine_type | definer | |

+-+

| | film_in_stock | sakila | PROCEDURE | root@localhost | |

| | film_not_in_stock | sakila | PROCEDURE | root@localhost | |

+-+

2 rows in set (0.00 sec)

The table associated with the programming component in the database of the @ mysql system

The mysql system database contains tables that provide information related to the functionality of MySQL stored procedures. These tables include:

L mysql.event table, which contains information about events stored in the MySQL server

L mysql.proc table, which contains information about stored procedures and functions in the MySQL server

L mysql.procs_priv table, which provides access control grant details for users referencing stored procedures

1.3. Stored procedure and execution security

The use of stored procedures and functions involves multiple permissions.

Default action: when you create a stored procedure, MySQL automatically grants your account EXECUTE and ALTER ROUTINE permissions on the stored procedure. Users with revoked permissions and GRANT OPTION permissions can revoke or delete those permissions later. After you create a stored procedure, you can verify these permissions by issuing a SHOW GRANTS statement.

Grant permissions: when ownership limits are granted at the global or database level, the GRANT ALL statement includes all stored procedure permissions except GRANT OPTION. To grant GRANT OPTION permission, include the WITH GRANT OPTION clause at the end of the statement. You can grant EXECUTE, ALTER ROUTINE, and GRANT OPTION permissions at a single stored procedure level, but only to stored procedures that already exist. To grant permissions to a single stored procedure, qualify the stored procedure with its database name and provide the keyword PROCEDURE or FUNCTION to indicate the stored procedure type, as shown in the following example:

Mysql > GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost' WITH GRANT OPTION

Mysql > GRANT ALL ON world_innodb.* TO 'magellan'@'localhost'

Mysql > GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost'

Permissions correspond to allowed actions

CREATE ROUTINE: create a stored procedure.

ALTER ROUTINE: changes or deletes stored procedures.

EXECUTE: executes stored procedures.

GRANT OPTION: Grant permissions to other accounts.

These are all the contents of the article "sample Analysis of stored procedures and stored functions in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.