Document directory
- 14.1.1. Create a stored procedure
- 14.1.2. Create a storage function
- 14.1.3. Use of Variables
- 14.1.4, defining conditions and handling procedures
- 14.1.5 use of the cursor
- 14.1.6. Use of Process Control
- 14.2.1 call a stored procedure
- 14.2.2. Call storage functions
Avoid writing repeated statements
Secure and controllable
High execution efficiency
14.1 create a stored procedure and function 14.1.1. Create a stored procedure
Create proceduresp_name ([proc_parameter [,...])
[Characteristic...] routine_body
Procedure pronunciation [pr comment 'si: D comment]
Proc_parameter in | Out | inout param_name type
Characteristic N. features; characteristics; Characteristics
Languagesql default, routine_boyd composed of SQL
[Not] deterministic indicates whether the execution result of the stored procedure is correct. By default, the result is unknown.
Constainssql | no SQL | reads SQL data | modifies SQL data specifies the restrictions on the use of SQL statements by programs
Constains SQL subprograms contain SQL statements, but do not contain statements for reading and writing data. The default value is
No SQL subprograms do not contain SQL statements
Reads SQL data subprogram contains read data statements
The modifies SQL data subroutine contains the statement for writing data.
Sqlsecurity {definer | invoker} indicates who has the permission to execute.
Definer. It can only be executed by the definer. The default value is
Invoker indicates that the caller can execute
Comment 'string' comment
Createprocedure num_from_employee (in emp_id, Int, out count_num INT)
Reads SQL data
Begin
Selectcount (*)IntoCount_num
Fromemployee
Whered_id = emp_id;
End
14.1.2. Create a storage function
Create functionsp_name ([func_parameter [,...])
Returns type
[Characteristic...] routine_body
Createfunction name_from_employee (emp_id INT)
Returnsvarchar (20)
Begin
Return(Select name from employee wherenum = emp_id );
End
14.1.3. Use of variables 1. Define Variables
Declare var_name [,…] Type [default value]
Declaremy_ SQL int default 10;
2. assign values to variables
Setvar_name = expr [, var_name = expr]…
Select col_name [,…] Into var_name [,…] From table_name where Condition
14.1.4, defining conditions and handling procedures 1. defining conditions
Declare condition_namecondition for condition_value
Condition value:
Sqlstate [value] sqlstate_value | mysql_error_code
For error 1146 (42s02)
Sqlstate_value: 42s02
Mysql_error_code: 1146
// Method 1
Declare can_not_find condition for sqlstate '42s02'
// Method 2
Declare can_not_find condition for 1146
2. Define the Handler
Declarehander_type handler for condition_value [,…] Sp_statement
Handler_type:
Continue | exit | undo
Condition_value:
Sqlstate [value] sqlstate_value | condition_name | sqlwarning | notfound | sqlexception | mysql_error_code
Undo is currently not supported by MySQL
1. Capture sqlstate_value
Declare continue handler for sqlstate '42s02 'set @ info = 'could not find ';
2. Capture mysql_error_code
Declare continue handler for 1146 set @ info = 'can not find ';
3. define conditions first, and then call
Declare can_not_find condition for 1146;
Declare continue handler for can_not_find set @ info = 'cannot find ';
4. Use sqlwarning
Declare exithandler for sqlwarning set @ info = 'could not find ';
5. Use not found
Declare exit handler for not found set @ info = 'cannot find ';
6. Use sqlexception
Declare exit handler for sqlexception set @ info = 'could not find ';
14.1.5 use of the cursor
Process multiple records in the stored procedure using the cursor
1. Declare the cursor
Declarecousor_name coursor for select statement;
Declarecur_employee cursor for select name, age from employee;
2. Open the cursor
Opencursor_name;
Opencur_employee;
3. Use the cursor
Fetchcur_employee into var_name [, var_name…];
Fetch cur_employeeinto emp_name, emp_age;
4. Close the cursor
Closecursor_name
Close cur_employee
14.1.6. Use of process control 1. If statement
Ifsearch_condition then statement_list
[Elseif search_condition thenstatement_list]…
[Else statement_list]
End if
If age> 20 then set @ count1 = @ count1 + 1;
Elseif age = 20 then @ count2 = @ count2 + 1;
Else @ count3 = @ count3 + 1;
End
2. Case statement
Case case_value
When when_value then statement_list
[When when_value then statement_list]…
[Else statement_list]
End case
Case
When search_condition thenstatement_list
[When search_condition thenstatement_list]…
[Else statement_list]
End case
Case age
When 20 then set @ count1 = @ count1 + 1;
Else set @ count2 = @ count2 + 1;
End case;
Case
Where age = 20 then set @ count1 = @ count1 + 1;
Else set @ count2 = @ count2 + 1;
End case;
3. Loop statement
[Begin_label:] Loop
Statement_list
Endloop [end_label]
Add_num: loop
Set @ COUNT = @ count + 1;
End loopadd_num;
4. Leave statement
Jump out of Loop Control
Leave label
Add_num: loop
Set @ COUNT = @ count + 1;
Leave add_num;
End loopadd_num;
5. iterate statement
Jump out of this loop and execute the next loop
Iterate label
Add_num: loop
Set @ COUNT = @ count + 1;
If @ COUNT = 100 then leave add_num;
Elseif Mod (@ count, 3) = 0 then iterateadd_num;
Select * from employee;
End loopadd_num;
6. Repeat statement
Conditional loop: exit the loop if conditions are met
[Begin_label:] Repeat
Statement_list
Until search_condition
Endrepeat [end_label]
Repeat
Set @ COUNT = @ count + 1;
Until @ COUNT = 100;
Endrepeat;
7. While statement
[Begin_label:] whilesearch_condition do
Statement_list
Endrepeat [end_label]
While @ count & lt; 100 do
Set @ COUNT = @ count + 1;
Endwhile;
14.2 call stored procedures and functions
The stored procedure is called through the call statement. The usage of storage functions is the same as that of MySQL internal functions. Execute permission is required to execute stored procedures and stored functions. Execute Permission information is stored in the user_privileges table under the information_schema Database
14.2.1 call a stored procedure
Call sp_name ([parameter [,…]) ;
14.2.2. Call storage functions
The usage of storage functions is the same as that of MySQL internal functions.
14.3 view stored procedures and functions
Show {procedure | function} status [like 'pattern'];
Show create {procedure | function} sp_name;
Select * frominformation_schema.routines where routine_name = 'sp_name ';
14.4 modify stored procedures and functions
Alter {procedure | function} sp_name [characteristic...]
Characteristic:
{Contains SQL | no SQL | reads SQL data | modifies SQL data}
| SQL security {definer | invoker}
| Comment 'string'
14.5 delete stored procedures and functions
Drop {procedure | function} sp_name;