MySQL Stored procedure learning (total)

#一. Creating cases for stored procedures and stored functions

CREATE PROCEDURE myprocedure (in a int,in b int, out C int)
BEGIN
Set c=a+b;
End

Call Myprocedure (@num); #存储过程需要call to execute @num This is the result of execution, and we invoke it through select

SELECT @num;

CREATE FUNCTION myfunction (a int) RETURNS INT (2) #RETURNS the type to return
BEGIN
Set a=a+1;
RETURN A;
End

SELECT MyFunction (2) [email protected];

#二. Use of variables
#通用公式: DECLARE variable name variable type default value (default value is NULL if default is not written)
#给变量赋值用set or select ... into variable name

CREATE FUNCTION Myfunction2 (a int) RETURNS INT (2) #RETURNS the type to return
BEGIN
DECLARE b INT DEFAULT 100;
Set a=b+1;
RETURN A;
End
SELECT Myfunction2 (3);

SELECT MyFunction (2), [email protected];

CREATE FUNCTION Myfunction3 (a int) RETURNS INT (2)
BEGIN
#相同的变量可以写在一起
# b,c,d is a local variable
DECLARE b,c,d INT DEFAULT 100;
Set a=b+c+d;
RETURN A;
End
SELECT Myfunction3 (2);
#三. Defining conditions and handlers

#mysql stored functions and procedures can turn errors into the processing results we want in 2 steps

#1. Defining error condition Syntax: DECLARE condition name CONDITION for error encoding
#2. Define the error Executor: DECLARE type (exit,continue) HANDLER for conditional name Set @info = ' defined meaning ';

CREATE TABLE Contest (S1 int,primary KEY (S1));

CREATE PROCEDURE Myprocedure2 ()
BEGIN
DECLARE Myfirstcon CONDITION for 1062;
DECLARE CONTINUE HANDLER for Myfirstcon set @info = ' primary key conflict ';
INSERT into Contest VALUES (1);
INSERT into Contest VALUES (1);
Set @info = ' primary key conflict 1 ';
End

Call Myprocedure2 ();
SELECT @info
# @info for User variables

#备注:

--1. Local variables: Local variables are generally used in SQL statement blocks, such as the begin/end of stored procedures.
--
--2. User variables: The scope of user variables is wider than local variables. User variables can be used for the current entire connection, but when the current connection is broken, the defined user variables will disappear.
--
--3. Session variables: The server maintains a series of session variables for each connected client. (SET @num = 1; #设置一个变量等于1 SELECT @num; #查询该变量)
--
--4. Global variables: Global variables affect the overall operation of the server. When the server starts, it initializes all global variables to the default values (operating system variables can be @@ 或 global)

#四. Use of Cursors

#声明光标 syntax: DECLARE cursor name cursors for SQL statement

#打开光标 the OPEN cursor name

#使用光标 FETCH cursor name into ...

#关闭光标 CLOSE cursor Name

CREATE PROCEDURE Guangbiao ()
BEGIN
DECLARE a VARCHAR (20);
DECLARE c VARCHAR (20);
DECLARE b int DEFAULT 0;
DECLARE Myguangbiao CURSOR for SELECT title,body from Test. articless;
DECLARE CONTINUE HANDLER for not FOUND set b=1;
OPEN Myguangbiao;

Read_loop:
LOOP
FETCH Myguangbiao into A,c;
IF B=1 Then
LEAVE Read_loop;
End IF;
INSERT into Test. AAA (Text1,text) VALUES (A,C);
Set b=0;
END LOOP Read_loop;
CLOSE Myguangbiao;
END;

Call Guangbiao ();

#五. Use of Process Control

#1. IF Use

Create PROCEDURE Iftest1 ()
BEGIN
DECLARE a int DEFAULT 10;
--IF (a>1 &&a<20) Then
IF (a>1 and a<20) then
SELECT ' OK ';
ELSE SELECT ' No ';
END IF;
END;
Call Iftest ();

#2. Case use

Create PROCEDURE Casetest ()
BEGIN
DECLARE a int DEFAULT 10;
Case A
When a>10 then SELECT ' no ';
When ten then SELECT ' OK ';
ELSE SELECT ' Nono ';
End case;
END;
Call Casetest ();
#3. Loop use

CREATE PROCEDURE looptest ()
BEGIN
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
My_loop:loop
Set a=a+1;
If a>10 then LEAVE my_loop;
END if;
Set b=a;

END LOOP My_loop;
SELECT b;

End

Call Looptest ();

#4. LEAVE use jump out of the current loop to see the previous test case looptest equivalent to break in the Java loop

#5. Iterate use, jump to the loop head execution, equivalent to continue,iterate in the Java loop can only be used in the loop

CREATE PROCEDURE iteratetest ()
BEGIN
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
My_loop:loop
Set a=a+1;
If a>10 then LEAVE my_loop;
ELSEIF a>5 then iterate my_loop;
END if;
Set b=a;

END LOOP My_loop;
SELECT b;

End

Call Iteratetest ();

#6. REPEAT Loop with conditions

CREATE PROCEDURE repeattest ()
BEGIN
DECLARE a int DEFAULT 0;
REPEAT
Set a=a+1;
UNTIL a>10
END REPEAT;
SELECT A;
END;

Call Repeattest ();
#7. While with conditional loops

CREATE PROCEDURE whiletest ()
BEGIN
DECLARE a int DEFAULT 0;

While a<10 do
Set a=a+1;
END while;
SELECT A;

END;

Call Whiletest ()

MySQL Stored procedure learning (total)