A case of creating stored procedures in a mysql database

Editor to share with you the case of creating stored procedures in the mysql database, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql stored procedure tutorial: first, right-click [Create Stored Procedures] in the [Stored Procedures] menu of the test database; then enter the relevant code in the create stored procedure interface; finally, click [Apply] to create the stored procedure.

Mysql stored procedures tutorial:

1. Create a table

1) Open the mysql graphical management tool [MySQL Workbench 6.3CE] and log in to the mysql database

2) on the "Tables" menu of [test] test database (an empty database created by yourself, you can use a specific database in the actual scenario), right-click and select "Create Table".

3) in the new table interface, enter the table name, comments, and add columns, and click the [Apply] button to complete the creation of the table.

2. Insert test data

1) Select test database, this step is optional (because all sql statements of mysql are prefixed with database name)

2) Click the first icon menu [Create a new tab...] at the top of the menu.

3) in the sql statement window, enter insert statement and query statement

4) Click the lightning icon above the sql window to execute the sql statement

3. Create a stored procedure

1) right-click [Create Stored Procedures] in the "Stored Procedures" menu of test database.

2) in the create stored procedure interface, enter the code shown in the figure (although there is an input parameter str, it is not used, which can be used directly in the stored procedure)

3) Click "Apply" to create a stored procedure, and then click "Apply" in the pop-up window.

4. Execute stored procedures

1) call the stored format [call new_procedure ('');] in the SQL window

A) call is a necessary command to call a stored procedure

B) new_procedure is the name of the stored procedure to be called

C) ('') is the parameter passed into the stored procedure

2) if the stored procedure executes the error "safe update mode", modify the data update mode, and then execute the stored procedure

SET SQL_SAFE_UPDATES = 0

5. Modify the stored procedure

1) right-click on the name of the stored procedure to be modified, and select [Alter Stored Procedure...]

2) after modifying the interface of the stored procedure, Apply can

6. Delete stored procedures

Right-click on the name of the stored procedure to be deleted and select [Drop Stored Procedure...]

The above is all the contents of the case of creating stored procedures in the mysql database. 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!