MySQL-11-stored procedure, mysql Stored Procedure

MySQL-11-stored procedure, mysql Stored Procedure

Stored Procedure

The commonly used operating database language SQL statements must be compiled and then executed before execution. The Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions, after compilation, the stored procedure is stored in the database. You can call and execute the stored procedure by specifying the name of the stored procedure and specifying parameters (if the stored procedure has parameters.

A stored procedure is a Programmable function that is created and saved in a database. It can contain SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms or encapsulate specific functions. Stored Procedures in databases can be seen as simulation of object-oriented methods in programming. It allows you to control how data is accessed.

Advantages of stored procedures:

(1) stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.

(2) stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code.

(3). the stored procedure can achieve fast execution speed. If an operation contains a large number of Transaction-SQL code or is executed multiple times, the stored procedure is much faster than the batch processing. Because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The Transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow.

(4). stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the Transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load.

(5) stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security.

 

1. Create a stored procedure

A) Syntax:

Create procedure process name ([process parameter [,...])

[Features...]

Process body

B) Description

Stored Procedures may have input, output, and input/output parameters. Multiple parameters are separated by commas.

IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned.

OUT output parameter: The value can be changed within the stored procedure and can be returned.

INOUT input and output parameters: this parameter is specified during the call and can be changed or returned.

C) Example:

Example 1:

Delimiter $

Create procedure proc1 (IN p_in int)

Begin

Select p_in;

Set p_in = 2;

Select p_in;

End

$

Delimiter;

Call: set @ p_in = 1;

CALL proc2 (@ p_in );

After execution, use select @ p_in. If the result is 1, the input parameter is changed in the stored procedure, but the original value is not affected.

 

Example 2:

Delimiter $

Create procedure proc2 (OUT p_out int)

Begin

Select p_out;

Set p_out = 2;

Select p_out;

End

$

 

Delimiter;

 

Call: set @ p_out = 1;

CALL proc2 (@ p_out );

After execution, select @ p_out is used. If the result is 2, the output parameter is changed in the stored procedure, affecting the original value.

 

Example 3:

INOUT parameter example. .

 

D) Detailed descriptions in the function body

Declaration variables, variable assignments, comments, and so on are all consistent with mysql function programming!

 

 

 

 

 

2. Call the Stored Procedure

Use the CALL stored procedure for calling. See the preceding example.

 

3. query stored procedures

View stored procedures and functions under a database

Select name from mysql. proc where db = 'database name ';

Or

Select routine_name from information_schema.routines where routine_schema = 'database name ';

Or

Show procedure status where db = 'database name ';

.

View the details of a stored procedure

Show create procedure database. Name of the stored PROCEDURE;

You can view the details of the current stored procedure.

 

4. Modify the MySQL Stored Procedure

ALTER PROCEDURE

Change the pre-specified stored PROCEDURE created with create procedure without affecting the stored PROCEDURE or function.

 

5. Delete the MySQL Stored Procedure

Deleting a stored procedure is simple, just like deleting a table:

DROP PROCEDURE

Delete one or more stored procedures from a MySQL table.

 

6. MySQL stored procedure control statements

(1). Condition Statement

I. if-then-else statement

Ii. case statement:

DELIMITER $

Create procedure proc3 (in parameter int)

Begin

Declare var int;

Set var = parameter + 1;

Case var

When 0 then

Insert into t values (17 );

When 1 then

Insert into t values (18 );

Else

Insert into t values (19 );

End case;

End;

$

DELIMITER;

(3). Loop statement

I. while... end while:

Ii. repeat · end repeat:

It checks the result after the operation is executed, while it checks before the execution.

DELIMITER //

Create procedure proc5 ()

Begin

Declare v int default 0;

Repeat

Insert into t values (v );

Set v = v + 1;

Until v> = 5

End repeat;

End;

//

DELIMITER;

 

Iii. loop · end loop:

The loop does not require the initial conditions. This is similar to the while loop, and does not require the end condition like the repeat loop. The leave statement is used to exit the loop.

DELIMITER $

Create procedure proc6 ()

Begin

Declare v int;

Set v = 0;

LOOP_LABLE: loop

Insert into t values (v );

Set v = v + 1;

If v> = 5 then

Leave LOOP_LABLE;

End if;

End loop;

End;

$

DELIMITER;

Iv. LABLES labels:

The label can be used before the begin repeat while or loop statement. The statement label can only be used before a valid statement. You can jump out of the loop to make the running command the last step of the compound statement.

(4). ITERATE Iteration

Reference the compound statement label to start a compound statement.

DELIMITER $

Create procedure proc10 ()

Begin

Declare v int;

Set v = 0;

LOOP_LABLE: loop

If v = 3 then

Set v = v + 1;

ITERATE LOOP_LABLE;

End if;

 

Insert into t values (v );

Set v = v + 1;

If v> = 5 then

Leave LOOP_LABLE;

End if;

End loop;

End;

$

DELIMITER;