MySQL stored procedure function trigger

MySQL stored procedure and function stored procedure Demo

Mysql>Delimiter//  --here//for modifying the default delimiter;MySQL> CREATE  PROCEDURESimpleproc (out param1INT)     - BEGIN     -   SELECT  COUNT(*) intoparam1 fromT;  - END//Query OK,0Rows Affected (0.00sec) MySQL>delimiter;// --change back here the default delimiter is;

This calls the trial call

MySQL> call Simpleproc (@a);  MySQL>Select@a;

Function Demo

Parameter does not have input/output parameters

MySQL>CREATE  FUNCTIONCHAR()) MySQL> RETURNS  CHAR (deterministic    )  - RETURN  CONCAT (', S,'! ');

The trial select is called here, just like the normal function.

MySQL>SELECT Hello ('World'); + -- --------------+ | Hello ('World'| + -- --------------+ | Hello, world!  . | + -- --------------+

Use of variables

Declare Last_month_start DATE; DECLARE my_sql  INT  DEFAULTten  ; Set = 2;

Process Control

CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Student_insert ' ()BEGIN    Set @x=0; Ins:loopSet @x = @x +1; if @x= -  Thenleave ins; End if; Insert  intoStudent (Stuname)Values(CONCAT ("name",@x)); ENDLOOP ins;ENDCREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Student_insert2 ' ()BEGIN    Set @x= -; Ins:loopSet @x = @x +1; if @x= -  Thenleave ins; ElseIf MoD (@x,2)= 0  Theniterate ins; End if; Insert  intoStudent (Stuname)Values(CONCAT ("name",@x)); ENDLOOP ins;ENDCREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Loop_demo ' ()BEGIN    Set @x=1,@x1=1; RepeatSet @x = @x +1; Until@x > 0 Endrepeat;  while @x1 < 2 DoSet @x1=@x1+1; End  while;END

Use of Cursors

CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Cursor_demo ' ()BEGIN    DeclareI_stuidint; DeclareI_stunamevarchar( -); DeclareCur_stucursor  for SelectStuid,stuname fromstudent; Declare ExitHandler for  notFoundCloseCur_stu; Set @x1 = 0; Set @x2 = 0; OpenCur_stu; RepeatFetchCur_stu intoI_stuid,i_stuname; SelectI_stuid,i_stuname; Until0 Endrepeat; CloseCur_stu;ENDCREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Cursor_demo3 ' ()BEGIN  DECLAREDoneINT DEFAULT 0; DECLAREACHAR( -); DECLAREB,cINT; DECLARECur1CURSOR  for SELECTStuname,stuid from' student '; DECLARECur2CURSOR  for SELECTSubID from' Subject '; DECLARE CONTINUEHANDLER forSQLSTATE'02000' SETDone= 1; OPENCur1; OPENCUR2; REPEATFETCHCur1 intoA, B; FETCHCur2 intoC; IF  notDone Then       SELECTA,b,c; END IF; UNTIL DoneENDREPEAT; CLOSECur1; CLOSECur2;END

Trigger

Must be built on top of the real table, suitable for some initialization data

CREATE TABLETest1 (A1INT);CREATE TABLETest2 (A2INT);CREATE TABLETEST3 (A3INT  not NULLAuto_incrementPRIMARY KEY);CREATE TABLEtest4 (A4INT  not NULLAuto_incrementPRIMARY KEY, B4INT DEFAULT 0); DELIMITER| CREATE TRIGGERTestref beforeINSERT  ontest1 forEach ROWBEGIN    INSERT  intoTest2SETA2=new.a1; DELETE  fromTest3WHEREA3=new.a1; UPDATETest4SETB4=B4+ 1 WHEREA4=new.a1; END|DELIMITER;INSERT  intoTEST3 (A3)VALUES   (NULL), (NULL), (NULL), (NULL), (NULL),   (NULL), (NULL), (NULL), (NULL), (NULL); INSERT  intoTest4 (A4)VALUES   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

View Index

Index  from Table

MySQL stored procedure function trigger