Getting started with MySQL is simple-learning notes-Chapter 1 stored procedures and functions

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;