MySQL: Syntax: stored procedures (stored procedures, stored functions, views, triggers, and scheduled tasks)

StorageProgramCategory

• Stored Procedure
• Storage Functions
• View
• Trigger
• Scheduled tasks

Write the ";" delimiter of the storage program in the mysql.exe Client

The mysql.exe client uses ";" as the statement separator (Terminator. In addition, the management of large numbers of MySQL applications is similar to mysql.exe. Therefore, the MySQL server must use delimiter, a recognizable command, to temporarily modify valid statement delimiters recognized by the server.

Structure of the storage Program
 [Label name]
Begin
[Declare common variable | declare exception alias];
[Declare abnormal alias | declare common variable];
[Declare cursor;]
[Declare exception handling;]

[In end compound statement;]

 
[Loop statement;]
[Condition Statement;]
......

End[{Lable name}]
//

Declare local variable
Declare va11[, Var2] [,...]Date_type[Default Value -- can be an expression or constant]

• Pay attention to the sequence when declaring local variables: Common variables, cursors, operators
• Local variables can declare multiple variables of the same type at a time -- similarC LanguageDifferent from PostgreSQL and Oracle. [not] Null constraints cannot be specified. It cannot be declared as a constant variable.
• The local variable can have the default value for initialization; otherwise, it is null.
• The local variable can be passed to the inner begin end statement, unless it is replaced by the inner variable overide with the same name.
• The name of the local variable should not be the same as that of the table column. During the process, the local variable is always preferentially used

Set variable assignment
Set var1 = value1[, Var2 = value2] [,...]
MySQL's set statement can assign values to local variables, system variables, and User-Defined variables, not just local variables!

The Select variable value must return 0 rows or 1 row of records.
Select col1[, Col2] [, exp1] [, exp2] [,...]Into val1[, Val2] [, val3] [, val4] [,...]From table_express

1. If select does not return data, MySQL has a warning of no data: Warning with error code 1329 is generated, and the variable remains the original value.
2. If select returns multiple rows of data, MySQL or an error occurs! It can be restricted by limit 1.

MySQL Exception Handling
Declare conditon_name condition for {sqlstate[Value]'Sqlvalue' | mysql_error_code}-- Definition exception: the purpose is to find a meaning that is more obvious or moreSuitable name. It can be understood as an alias.
Declare {continue | exit | undo} handler for {sqlstate[Value]'Sqlvalue' | mysql_error_code | conditon_name

| Sqlwarning | notfound | sqlexception}[,....]-- Define Exception Handling MySQL Exception Handling

1. Similar to error handling in other procedural SQL languages, the structure and usage are different (PostgreSQL and Oracle are optional parts of a fixed block structure exception;
2. Sqlserver is @ error or try catch final)
3. This handler can be associated with multiple conditions, similar to PostgreSQL and Oracle's when... when others...
4. The sqlwarning of handler is an error in the "01xxx" class.
5. Handler's not found is an error of the "02 XXXX" Class
6. The sqlexception of handler is an error except for the '123456', '01xxx', and "02 XXXX" classes.
7. Note that sqlstate value = '20140901' or mysql_error_code = 0 indicates that the operation is successful and should not be used!
8. Handler's continue indicates that when an error condition of the associated block occurs, the next statement from the wrong statement continues to be executed; exit indicates that the program is terminated immediately due to an error; Undo is not implemented yet!
9. Programs that do not have handler blocks or define Exit Processing (either the inner layer or the outer layer) will terminate program execution when an error occurs!
10. You can define an empty statement to ignore any errors: declare continue handler for sqlwarning begin end;

 

Cursor: Read-only, forward-only, insensitive

DeclareCursor_nameCursor for no_into_select_clause;
OpenCursor_name;
FetchCursor_nameIntoVar1 [, var2] [,...]; may produce a not found Exception error.
CloseIf cursor_name does not explicitly call the statement to close the cursor, the cursor will be automatically destroyed in the scope that exceeds the definition of the cursor

• MySQL cursor does not read any special internal variables. Handle with handler and "not found" Conditions

Control Structure
If if_condition then
Statement_list;
[Elseif if_condition then statement_list;]
[Elsestatement_list;]
End if;

Case
When case_condition then statement_list;
[When case_condition then statement_list;]
[Else statement_list;]
End case;

[Repeat_label:]Repeat-- Do Loop Structure in other languages.
Statement_list;
Util repeat_condition
End repeat[Repeat_label];

[While_label:]While while_condition do-- While loop structure of other languages
Statement_list;
End while[While_label];

[Loop_label:] Loop-- General loop structure. Note that there are no control conditions. The statement_list must contain control conditions, leave, and iterate statements;
Then statement_list;
End loop [loop_label];


Leave label_name;
  Similar to exit [when...] in other languages, the exit structure is displayed. Used to exit begin .. end, loop, repeat, while, provided that it must be in the corresponding structure, and the structure defines the label


Iterate label_name;
Similar to the continue of other languages, continue the next loop. Used to exit loop, repeat, while, on the premise that it must be in the corresponding structure, and the structure defines the label

FunctionReturn exp;Statement -- a statement used only to store functions!