MySQL stored procedures and functions

MySQL stored procedures and functions

1. Basic concepts:

Creating stored procedures and functions is the combination of a frequently used set of SQL statements and storing these SQL statements as a whole in a MySQL server. For example, banks often need to calculate user interest. The interest rates for different categories of users are not the same. This makes it possible to write SQL code that calculates interest rates as a stored procedure or as a stored function. Whenever this stored procedure or stored function is called, the interest of different categories of users can be calculated.

1. Create a stored procedure

Delimiter $$;

CREATE PROCEDURE name (parameter list)

Begin

SQL statement block

End

$$;

Delimiter

1. Definitions, parameter types, and parameters for variables in stored procedures

Variable definition: –declare variable name data type default defaults

Parameter type:

–in parameter: Indicates that the value of this parameter must be specified before the stored procedure is called, the value modified in the stored procedure cannot be returned, that is, when the call is specified, the default is to pass in the parameter

Example:

–out parameter: This value can be changed inside the stored procedure and can be returned. is often used to get the parameter values in the stored procedure.

–inout parameter: This value can be specified at call time and can be modified and returned.

Reference: –create procedure test_p (in Parameter name, out parameter type)

Example:

Delimiter $$;

CREATE PROCEDURE Test_p8 (ID int,out phone int,inout s_name varchar (20))

Begin

DECLARE sex varchar (TEN) default ' male ';

Set id = id+1;

Set phone = 186125312;

Select S_name;

Set s_name = ' besttest ';

INSERT into students values (id,s_name,phone,sex);

End

$$;

Delimiter

Set @phone = 99888;

Set @s_name = ' Besttest '

Call TEST_P8 (@phone, @s_name);

SELECT * from students;

Select @s_name;

1. Statements in stored procedures

1) If condition judgment:

If condition Then

Statement

ElseIf condition Then

Statement

Else

Statement

End If;

2) Case Condition judgment:

Case value

When condition Then

SQL statements

When condition 2 Then

SQL statements

else# If none of the above conditions are met, execute

SQL statements

End case

3) While loop:

While condition do

SQL statements

End while;

4) Repeat cycle:

Repeat

SQL statements

Until conditions

End repeat;

1. Create a function

Functions are similar to stored procedures, except that the function has a return value, and the stored procedure does not return a value.

Create function name (variable 1, variable 2 ...)

Returns data types

Begin

...... Program code to execute

return data;

End

1. viewing stored procedures and functions

Show procedure status;

Show function status;

Show CREATE PROCEDURE;

Show Create

1. Delete a stored procedure or function

Drop {procedure| function} sp_name;

MySQL stored procedures and functions