Stored procedure/Cursor/mysql function

Stored Procedures and functions (proc tables stored in MySQL database, so check for this table)
A stored procedure is a stored procedure (like a subroutine in a normal language), and there are two types of MySQL support: stored procedures, functions that can return values in other SQL statements (as with the MySQL preloaded functions, such as PI ())
A stored procedure consists of a name, a list of parameters, and a set of SQL statements that can include many SQL statements.
As: (compound statement block)
CREATE PROCEDURE producedure1/*name Stored Procedure name */
(in Parameter1 interger)/*parameters parameter */
BEGIN/*start of block statement size */
DECLARE varialbe1 CHAR (10); /* Variables Variable declaration */
If Parameter1 =/*start of If condition */
SET variable1 = ' birds '; /*assignment Assignment */
ELSE
SET variable1 = ' beasts '; /*assignment Assignment */
END IF; /*end of If If End */
INSERT into table1 values (VARIABLE1); /*statement SQL statement */
......
END; /*end of block statement blocks end */

DELIMITER///* set delimiter, default; */
CREATE PROCEDURE p1 () select * from T; //
DELIMITER; /* Restore the original delimiter; */
Call P1 ();

Invocation: Call stored procedure name ();
Then call P1 (); Considerable scale in the execution of select * from T;
Valid stored procedures (curd, drop table, set, Commit, rollback ())

To delete a stored procedure:
DROP PROCEDURE stored procedure name;

Charayeristics clauses feature clauses
CREATE PROCEDURE P2 ()
LANGUAGE SQL--don't work, just make the following sentences are written in SQL, system default, can not be declared (in order to be compatible, it is best to declare)
Not deterministic---is the information passed to the system. The definition of a process here is a program that outputs the same output every time you enter it. Here, since the main SELECT statement, that return is definitely unknown, so it is called not deterministic
SQL Security Definer--this sentence can be defined as SQL Security INVOKER, which is the realm of access control, and SQL security definer means that the user's permission to create the process is checked upon invocation, When used to tell the MySQL server to check the creation process of the user, when the process has been called, do not check the user to execute the call process, another INVOKER is to tell the server in this step (that is, use) still check the caller's permissions
COMMENT ' A Procedure ' and an optional comment description
SELECT * from T; //
So the above is equivalent to the following:
CREATE PROCEDURE P2 ()
SELECT * from T; //

The feature clause also has a default value, which, if omitted, is equivalent to:
LANGUAGE SQL not deterministic SQL SECURITY Definer COMMENT

CREATE PROCEDURE P4 ()
Select "Hi"; //
Call P4 ();
The field name is hi and the value is hi

Parameters parameters
CREATE PROCEDURE P5 ()
.....

CREATE PROCEDURE P5 ([in] name Data-type)
.....

CREATE PROCEDURE P5 (out name Data-type)
.......

CREATE PROCEDURE P5 (INOUT name Data-type)
.........

In: Indicates input parameter, default is in (input)
Out: Output parameters
INOUT: can be used as input or as output

In:
DELIMITER//
CREATE PROCEDURE pp (P INT)
SET @x=p; //
Call PP (100);
SELECT @X;
Represents the value of p to the variable X, i.e. @X = 100;

Out:
DELIMITER//
CREATE PROCEDURE pp (out P int)
Set p =-100; //
Call PP (@y);
SELECT @y;
This time p is the output parameter

New SQL statement:
Varaibles variable
The instruction of declaring a variable in a compound statement is DECLARE;
As
DELIMITER//
CREATE PROCEDURE P8 ()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET B = 10;
INSERT into T VALUES (a);
SELECT S1 from T WHERE S1 >= b;
END; //
In Being/end, it is important to declare the variable name and its type, not with the @ modifier

Examples of no default clauses and SET statements
DELIMITER//
CREATE Proceduer P9 ()
BEGIN
DECLARE a INT; /* No default clause */
DECLARE b INT; /* No default clause */
SET a = 5; /* Set Value */
SET B = 10; /* Set Value */
INTER into T VALUES (a);
SELECT S1 from T WHERE S1 >= b;
END; //
There are many methods of initializing variables, if there is no default clause, then the initial value of the variable is NULL, you can use the SET statement at any time to assign a value to the variable

Contains the default clause:
DELIMITER//
CREATE PROCEDURE P10 ()
BEGIN
DECLARE A, b INT DEFAULT 5;
INSERT into T VALUES (a);
SELECT S1 from T WHERE S1 >= b;
END; //

The default clause is used here to set the initial value, which does not need to separate the implementation of the DECLARE and set statements.

Scope scope
DELIMITER//
CREATE PROCEDURE P11 ()
BEGIN
DECLARE x1 CHAR (5) DEFAULT ' outer ';
BEGIN
DECLARE x1 CHAR (5) DEFAULT ' inner ';
SELECT X1;
END;
SELECT X1;
END; //
Call P11 ();
The sentence is nested begin/end, is legal, the internal priority is high, so first execute the internal, then the internal variables disappear, and then the external, so there are two results, one is inner, one is outer;

Conditional and If-then-else
DELIMITER//
CREATE PROCEDURE P12 (in Parameter1 INT)
BEGIN
DECLARE variable INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 Then
INSERT into T VALUES (100);
END IF;
IF parameter1 = 0 Then
UPDATE t SET s1 = s1+1;
ELSE
UPDATE t set s1 = s1+2;
END IF;
END; //
Use = to indicate whether the value is equal and to assign a value

Case directive
DELIMITER//
CREATE PROCEDURE p13 (in Parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
Case Variable1
When 0 then inserts into T VALUES (555);
When 1 then inserts into T VALUES (888);
ELSE INSERT into T VALUES (199);
END case;
END; //

Looping statements
While ... END while
LOOP ... END LOOP
Repeal ... END REPEAT
GOTO (as little as possible, as in other languages, resulting in confusion)

While ... END while
CREATE PROCEDURE P14 ()
BEGIN
DECLARE v INT;
SET v = 0;
While v < 5 do
INSERT into v VALUES (v);
SET v = v+1;
END while;
END; //
The above only returns one row affected, because only the last insert action is counted

REPEAT ... END Repeat:until
DELIMITER//
CREATE PROCEDURE P15 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT into T VALUES (v);
SET v = v+1;
UNTIL v >= 5/* Cannot add semicolon, otherwise error */
END REPEAT;
END; //
Actually it is do ... while

Loop ... end loop
As
DELIMITER//
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
Label1:loop
INSERT into T VALUES (v);
SET v = v+1;
IF v >= 5 Then
LEAVE Label1; --Leave the loop
END IF;
END LOOP Label1;
END; //

Label label
DELIMITER//
CREATE PROCEDURE p17 ()
Label_1:begin
Label_2:
While 0=1
Do LEAVE label_2;
END while;
Label_3:repeat
LEAVE Label_3;
UNTIL 0=0---can't have semicolons
END REPEAT;
Label_4:loop
LEAVE Label_4;
END LOOP;
END [Label_1]; Label_1 Optional


LEAVE and Labels jump and label (the LEAVE statement causes the program to jump out of the compound statement)
DELIMITER//
CREATE PROCEDURE p19 (parameter1 CHAR)
Label_1:begin
Label_2:begin
Label_3:begin
IF Parameter1 is isn't NULL then
IF parameter1 = ' a ' Then
LEAVE Label_1;
ELSE BEGIN
IF parameter1 = ' B ' Then
LEAVE label_2;
ELSE
LEAVE Label_3;
END IF;
END;
END IF;
END IF;
END;
END;
END; //

Iterate Iteration
If the target is a iterate statement, you must use the Leave statement
Iterate (iteration) statements and leave statements are also circular references inside loops, a bit like the C language "Continue", which can also appear in compound statements, referencing compound statement designators, iterate () means re-starting compound statements
DELIMITER//
CREATE PROCEDURE P20 ()
BEGIN
DECLARE v INT;
SET v = 0;
Loop_label:loop/* Cycle label */
IF v = 3 Then
SET v = v+1;
Iterate Loop_label; /* Start the iteration so that the loop is back to the start */
END IF;
INSERT into T VALUES (v);
SET v=v+1;
IF v >= 5 Then
LEAVE Loop_label; /* Jump out of the loop so that the instruction jumps to the last step */
END IF;
END LOOP;
END; //
Call P20 ();
------------------
Exception handling
CREATE TABLE T2 (
S1 int PRIMARY Key
) engine = InnoDB;

CREATE TABLE T3 (
S1 int primary key references T2 (S1)
) engine = InnoDB;
A primary key table, and a foreign key table
INSERT into T3 values (5);
If prompted: Error 1216 (2300) cannot add or update a child row:a foreign key.
is because the main table doesn't have this value
Create an error log table:
CREATE TABLE Error_log (error_message char (100));

DELIMITER//
CREATE PROCEDURE P22 (parameter1 INT)
BEGIN
DECLARE EXIT HANDLER for 1216/* Insert Error number */
INSERT into Error_log VALUES (' Time: ', ' current_date ', ' xxx ');
INSERT into T3 VALUES (parameter1);
END; //

DECLARE exit HANDLER is used to handle exceptions, if error 1216 occurs, a row is inserted in the Error logging table, exit means that when the action is successfully committed and exits the compound statement, T3 does not insert the data at this time

Syntax for declaring exception handling
DECLARE
{EXIT | CONTINUE}
HANDLER for
{error-number| {SQLSTATE error-string} | Condition
SQL statement
When the program error, automatically trigger the code, MySQL allows two processing, one is the exit processing, one is continue processing, after it executes, the original program continues to run, the country this compound statement will not be exported
As

DELIMITER//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 = 1; /* Insert error code */
SET @x=1;
INSERT into T VALUES (1);
SET @x=2;
INSERT into T VALUES (1);
SET @x=3;
END; //
When execution starts, insert is 1, then set @x=2, and then insert, because 1 already exists and cannot be duplicated, so the Declare statement executes and then the set @x=3
So execute
Select @x, @x2, 3,1 value
----------------
Custom Name
CREATE PROCEDURE P24 ()
BEGIN
DECLARE ' Sql_1 '
CONDITION for SQLSTATE ' 23000 ';
DECLARE EXIT HANDLER for
' Sql_2 ' ROLLBACK;
START TRANSACTION;
INSERT into T2 VALUES (1);
INSERT into T2 VALUES (1);
COMMIT;
END; //
You can give sqlstate or other names to the error code so that you can use your own defined name in the process.

Stored procedure/Cursor/mysql function