One day at a point MySQL review-stored procedures

First, the concept of stored procedures

When writing code that accesses a database using SQL, there are two ways to store and execute the code, one that stores the code on the client and creates a SQL command (or SQL statement) sent to the database server, such as a SQL statement that embeds access to the database in a client programming language such as C #, Java The other is to store the SQL statements on the database server side (which is actually stored in a specific database as an object of the database ) and then executed by the application call.

These SQL statements stored on the database server for client invocation are stored procedures that the client application can invoke and execute directly, and the execution results of the stored procedure can be returned to the client.

Stored procedures can:

• Accepts input parameters and returns multiple values to the caller in the form of an output parameter.
• Contains statements that perform database operations.
• Returns the result of the query statement execution to the client memory.

In summary, a stored procedure is actually a precompiled collection of SQL statements and process control statements stored on a database server that is stored as a single name and processed as a unit, which can be executed by an application invocation, allowing operations such as control flow, logic, and queries on the data to be included. Stored procedures can accept input parameters, can have output parameters, and can return single or multiple result sets.

Ii. Creating a stored procedure

CREATE PROCEDURE procedure name (parameter list) begin    --process body End

Simple example:

Iii. Viewing stored procedures

Show procedure status [like ' procedure_name '];

Simple example:

Where ' \g ' means: Display query results in each group

To view the CREATE statement:

Show CREATE PROCEDURE procedure_name;

Iv. calling a stored procedure

You cannot use Select to access stored procedures:

Calling a stored procedure should call:

V. Modify & Delete stored procedures

Stored procedures cannot be modified and can only be deleted after you add

drop procedure stored procedure name;

Vi. Stored Procedure Parameters

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

The process also has its own type qualification:

• In: Data is only passed from outside to internal use (value passing), can be a numeric value or it can be a variable
• Out: Only allow internal use of the procedure, only pass variables, reference delivery, external data will be emptied before entering into the internal
• InOut: External can be used internally, internal modifications can be used externally, reference is passed, only variables are passed

The in parameter is passed as a value

The out parameter is passed as a reference and is passed into the procedure, emptying the value of the passed parameter itself

The InOut parameter is passed as a reference

Instance:

    Delimiter $$    CREATE PROCEDURE Pro2 (in int_1 int,out int_2 int,inout int_3 int)    begin         --View three variables        Select int _1,int_2,int_3;    End    $$    delimiter;

    Delimiter $$    CREATE PROCEDURE Pro3 (in int_1 int,out int_2 int,inout int_3 int)    begin        -view three local variables;        Select Int_1,int_2,int_3;                --Modification of three local variables;        Set int_1 = ten;        Set int_2 = +;        Set int_3 = +;        --View three local variables        select Int_1,int_2,int_3;        --View three global variables        select @int_1, @int_2, @int_3;        --Modify three global variables        set @int_1 = ' a ';        Set @int_2 = ' B ';        Set @int_3 = ' C ';        --View three global variables        select @int_1, @int_2, @int_3;    End    $$    delimiter;

One day at a point MySQL review-stored procedures