Home Database Mysql Tutorial Query stored procedure mysql

Query stored procedure mysql

May 14, 2023 am 11:38 AM

Preface

When developing database applications, most complex operations of database queries can be implemented using stored procedures. A query stored procedure is a reusable block of code that is a predefined SQL statement and is stored in the database. Developers can handle complex query operations by calling stored procedures. MySQL is a very popular open source database management system that also supports the use of stored procedures.

This article will briefly introduce the use of query stored procedures in MySQL.

1. Create a stored procedure

To create a stored procedure using MySQL, you need to use the CREATE PROCEDURE statement. The syntax is as follows:

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE procedure_name ([proc_parameter[,...]])
BEGIN
   /* 存储过程主体 */
END;
Copy after login

Among them, DEFINER defines the owner of the stored procedure, i.e. the user who created it. procedure_name is the name of the stored procedure. proc_parameter is an optional stored procedure parameter that can be passed to the stored procedure.

The following is an example of a simple query stored procedure:

CREATE PROCEDURE sp_GetUserById(IN userId INT)
BEGIN
   SELECT * FROM Users WHERE Id = userId;
END;
Copy after login

The name of this stored procedure is sp_GetUserById. It receives an integer parameter userId and queries the user table based on the passed in parameter value. Record.

2. Execute the stored procedure

To execute the stored procedure, you can use the CALL statement, the syntax is as follows:

CALL procedure_name([parameter[,...]]);
Copy after login

Among them, procedure_name is the name of the stored procedure to be executed, and the parameters Can be any parameter type defined by the stored procedure.

Using the query stored procedure example above, you can execute the following call:

CALL sp_GetUserById(1);
Copy after login

This will return the record with Id=1 in the user table.

3. Delete stored procedures

To delete a stored procedure, you can use the DROP PROCEDURE statement. The syntax is as follows:

DROP PROCEDURE [IF EXISTS] procedure_name;
Copy after login

Among them, procedure_name is the name of the stored procedure to be deleted. The IF EXISTS option avoids error messages when the stored procedure does not exist.

For example, to delete the stored procedure created earlier, you can use the following command:

DROP PROCEDURE IF EXISTS sp_GetUserById;
Copy after login

This will delete the stored procedure named sp_GetUserById (if it exists).

4. Using a cursor

When executing a query in a stored procedure, sometimes you need to use a cursor to iterate the result set. Cursor usage in MySQL is similar to standard SQL.

The following is an example of using a cursor:

CREATE PROCEDURE sp_GetAllUsers()
BEGIN
    /* 声明游标、变量 */
    DECLARE curUsers CURSOR FOR SELECT * FROM Users;
    DECLARE done INT DEFAULT FALSE;
    DECLARE userId INT;
    DECLARE userName VARCHAR(50);

    /* 打开游标 */
    OPEN curUsers;

    /* 迭代结果集 */
    REPEAT
        /* 获取下一行 */
        FETCH curUsers INTO userId, userName;

        /* 如果结果集为空,则退出循环 */
        IF done THEN
            LEAVE main_loop;
        END IF;

        /* 处理当前行 */
        SELECT userId, userName;

    /* 主循环,退出标签 */
    UNTIL done END REPEAT;

    /* 关闭游标 */
    CLOSE curUsers;

    /* 结束存储过程 */
END;
Copy after login

This stored procedure will query all records in the user table and use the cursor to iterate the result set. After obtaining each row of data, userId and userName will be output.

5. Use process control

Various process control structures can be used in stored procedures, such as IF-THEN, WHILE and REPEAT-UNTIL.

The following is an example of using the IF-THEN structure:

CREATE PROCEDURE sp_GetUsersByRole(IN roleId INT)
BEGIN
   IF roleId IS NULL THEN
      SELECT * FROM Users;
   ELSE
      SELECT * FROM Users WHERE RoleId = roleId;
   END IF;
END;
Copy after login

This stored procedure will query the records in the user table based on the passed parameter value roleId. If roleId is NULL, all records will be returned.

Conclusion

Query stored procedures are one of the very useful features of the MySQL database, which can be used to perform complex query operations. Each stored procedure can be used as an independent block of code and can be reused in the code.

This article introduces the creation, execution and deletion of stored procedures, the use of cursors and process control structures, and a simple example. However, it should be noted that the design of stored procedures should follow best practices, including proper checking and processing of input and output parameters, as well as ensuring security and performance issues.

The above is the detailed content of Query stored procedure mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

When might a full table scan be faster than using an index in MySQL? When might a full table scan be faster than using an index in MySQL? Apr 09, 2025 am 12:05 AM

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

Explain InnoDB Full-Text Search capabilities. Explain InnoDB Full-Text Search capabilities. Apr 02, 2025 pm 06:09 PM

InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

Can I install mysql on Windows 7 Can I install mysql on Windows 7 Apr 08, 2025 pm 03:21 PM

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

Difference between clustered index and non-clustered index (secondary index) in InnoDB. Difference between clustered index and non-clustered index (secondary index) in InnoDB. Apr 02, 2025 pm 06:25 PM

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values ​​and pointers to data rows, and is suitable for non-primary key column queries.

MySQL: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

The relationship between mysql user and database The relationship between mysql user and database Apr 08, 2025 pm 07:15 PM

In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.

Can mysql and mariadb coexist Can mysql and mariadb coexist Apr 08, 2025 pm 02:27 PM

MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.

Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial). Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial). Apr 02, 2025 pm 07:05 PM

MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.

See all articles