About MySQL stored procedures

A stored procedure, which can be understood as a logical SQL statement.

Advantage: Execution is very efficient, and stored procedures are performed on the server side of the database.

Disadvantage: The portability is poor, and the stored procedures of different databases are not portable.

Two

1. Basic grammar

--Create a stored procedure start--declares that the stored procedure terminator is $DELIMITER $CREATE PROCEDUREStorage Name (inchIint, Out Snamevarchar( -))--parameter type (in,out,inout) parameter name database typeBEGIN    --can write SQL and logical body    --The if statement begins with the notation    --columns such as:    ifConditional expressions Then         --can write SQL and logical bodyElseIf conditional expression Then         --can write SQL and logical body    Else         --can write SQL and logical bodyEnd If--end of the IF statement        --While Loop statement starts    --columns such as:    --declaring variable I,result    DECLAREIINT DEFAULT 1; DECLAREResultINT DEFAULT 0;  whileI<=Num do--can write SQL and logical body        SETResult=Result+i; SETI=I+1; END  while; --While Loop statement ends        --use into to get the data for the database to start with the output parameters    --columns such as:        SELECTNAME intoSname fromTable nameWHEREId=I--i is input parameter, sname is output parameter    --use into to get the data from the database to the end of the output parameterEND $

--Create a stored procedure end

--Note parameters: inch :   represents an input parameter that can carry data in a stored procedure out: Represents an output parameter that can be returned from a stored procedure inout: Represents the input and output parameters, which can either be input or output functions

2. Call the stored procedure

Call  stored procedure name (Parameters ...);

3. Delete stored Procedures

DROP PROCEDURE stored procedure name;

Third, Trigger

1. Create a grammar

CREATE  TRIGGER  Trigger Name   
After| Before  
INSERT | DELETE | UPDATE   on
 for Each ROW
--Statement SQL
End

2. Delete

DROP  TRIGGER  Trigger name;

Yexiangyang

[Email protected]

About MySQL stored procedures