MySql Stored Procedure Notes

Stored Procedures

stored procedure abbreviation procedure, procedure, is a function with no return value

To create a process:

Create procedure Procedure name ([parameter list])

Begin

Process Body

End

viewing procedures

The way the function is viewed is fully applicable to the procedure: replace the keyword with procedure

Show procedure status [like '];

View Create statement

Show CREATE PROCEDURE Pro1;

Call procedure (Procedure has no return value, select Cannot access)

The procedure has a special calling keyword: call

Call Pro1 ();

Modify Process & Delete process

Process can not be modified, can only be deleted after the new

Drop Procedure Procedure Name

Procedure parameters for process parameter problems

function parameters require data type designation, procedure is more rigorous than function

The process has its own type qualification

In: Data is only passed externally to internal use (value passing), can be a numeric value, or it can be a variable

Out: Only allow internal use of the procedure (without external data), for external use (Reference delivery: External data is emptied before it goes inside): (implied) only variables.

Inout: External can be used internally, internal modifications can also be used externally, only variables

Use syntax:

Create procedure procedure name (in Parameter name data type, out parameter name data type, inout parameter name data type)

Call: Out and inout incoming must be a variable, cannot be a numeric value

Variable must be passed in

--Setting variables

Set @int_1: = 1;

Set @int_2: = 2;

Set @int_3: = 3;

Select @int_1, @int_2, @int_3;

Call Pro1 (@int_1, @int_2, @int_3);

Select @int_1, @int_2, @int_3;

The stored procedure has a lag in the operation of the variable (return), which assigns the internal modified value to the external incoming global variable at the end of the stored procedure .

Test

Incoming data:

--Setting variables

Set @int_1: = 1;

Set @int_2: = 2;

Set @int_3: = 3;

Call Pro2 (@int_1, @int_2, @int_3);

1. Explains that local variables are independent of global variables

2. After the end of the stored procedure call, the system assigns local variables to global variables (out and inout two types only)

Writing so much, in fact, no use, the main solution to the stored procedure does not return the value of the problem. To return the internal results to external use.

MySql Stored Procedure Notes