mysql--Stored Procedures

Stored Procedures

Definition of a stored procedure

Second, the advantages of stored procedures

Third, the use of stored procedures

3.1 Creation and invocation of stored procedures

3.2 Viewing and deletion of stored procedures

The definition of a stored procedure:

A stored procedure is a set of SQL statements that are set up to accomplish a particular function, stored in the data, and re-called without two compilations after a compilation, and can be understood as a batch statement.

Second, the advantages

① improves the reusability of code

② Simplifying operations

③ improves the security of your data

④ improves efficiency with minimal compile times and database server connection times

Third, the use of stored procedures

3.1 Creating and invoking

Create syntax:

Create procedure stored procedure name (parameter list)

Begin

Stored procedure Body (correct SQL statement)

End

Note:
1, the parameter list contains three parts
Parameter pattern parameter Name argument type
Example:
In Stuname varchar (20)

Parameter mode:
In: This parameter can be input, that is, the parameter requires the caller to pass in the value
Out: This parameter can be used as the output, that is, the parameter can be used as the return value
InOut: This parameter can be both as input and as output, that is, the parameter needs to pass in the value, but also can return the value

2. If the stored procedure body has only one sentence, begin end can omit
The end of each SQL statement in the stored procedure body must have a semicolon.
The end of the stored procedure can be reset using delimiter
Grammar:
Delimiter end tag
Case:
Delimiter $

Stored Procedure Calls

Call stored procedure name (argument list);

Case (Null argument list): Inserting 3 data into the user table

Case (with in mode parameter): Query boys name according to beauty name

Case (mode parameter with out): Determine if the user is a member

Case (mode parameter with InOut): Pass in A and b two values, eventually a and b are doubled and returned

3.2 Viewing and deletion of stored procedures

To delete a stored procedure
Syntax: drop procedure Stored Procedure name
DROP PROCEDURE chen00; the right way
DROP PROCEDURE chen00,chen02; wrong way

To view information about a stored procedure
DESC MYP2; Wrong way
SHOW CREATE PROCEDURE chen02; the right way

mysql--Stored Procedures