Stored Procedures and functions

Stored procedures and functions are collections of SQL statements that are defined in the database, and then call these stored procedures and functions directly to execute the SQL statements that have already been defined. Stored procedures and functions can prevent developers from writing the same SQL statements repeatedly. Furthermore, stored procedures and functions are stored and executed in the MySQL server, which can reduce the data transfer between the client and server side.

 -Create stored procedures-create stored functions-use variables-define conditions and handlers-use of cursors-process Control usage-invoke stored procedures and functions-view stored procedures and functions-modify stored procedures and functions-delete stored procedures and functions 14.1 create stored procedures and functions create stored procedures and A function is a combination of frequently used sets of SQL statements that are stored in a MySQL server as a whole, for example, banks often need to calculate user interest. Interest on different categories of services is not the same. This makes it possible to write SQL code that calculates interest rates as a stored procedure or as a stored function. Whenever this stored procedure or stored function is called, the interest of different categories of users can be calculated.  14.1.1 Creating a stored procedure create PROCEDURE sp_name ([proc_parameter[,...]]) [Characteristic ...] routine_body [in| Out| INOUT] Param_name Type input | output | Input output stored procedure parameter name the parameter type of the stored procedure  delimiter &&create PROCEDURE num_from_employee (in emp _id int, out count_num int) READS SQL databeginselect Count (*) into Count_numfrom employeewhere d_id=emp_id; End&&delimiter; 14.1.2 Create a storage function in MySQL, the basic form of creating a storage function is as follows: Create function Sp_name ([func_parameter[,...]]) RETURNS type[characteristic ...] routine_body  delimiter &&create function Name_from_employee (emp_ ID int) returns varchar (Beginreturn) (select name from employee where d_id=emp_id); end&&delimiter; 14.1.3 Variables can be defined and used in stored procedures and functions using a variable. Users can use the DECLARE keyword to define variables. The variable can then beAssigned value. The scope of these variables is begin ... End of the program segment. 1 define variable 2 for variable assignment  # define variable my_sql, the data type is int, the default value is 10. DECLARE my_sql INT Default 10; # variable assignment set My_sql=30; select d_id into My_sql from employee where id=2;14.1.4 define condition and handlers define conditions and handlers in advance to define the problems that you may encounter during program execution. You can also define ways to resolve these problems in your handlers. This way you can anticipate potential problems in advance and propose solutions. This will enhance the ability of the program to handle problems, and prevent the program from stopping abnormally. In MySQL, the conditions and handlers are defined by the DECLARE keyword. 1. Define condition 2. Define handlers  declare condition_name condition for condition_valuecondition_value;sqlstate [value] sqlstate_value | Mysql_error_code "Error 1146 (42S02)", the name is Can_not_find. It can be defined in two different ways. # method One: Use Sqlstate_valuedeclare can_not_find CONDITION for SQLSTATE ' 42s02 ';  # method Two: Use Mysql_error_codedeclare can_ Not_find CONDITION for 1146; # defining handlers declare Handler_type handler for condition_value[,...] Sp_statementhandler_ typecontinuew| Exit| undocondition_valuesqlstate[value]sqlstate_value|condition_name| Sqlwarning| Not found| SQLEXCEPTION |mysql_error_code # Method One: Capture Sqlsate_valuedeclare CONTINUE HANDLER for SQLSTATE ' 42s02 ' SET @info = ' CAN Not FIND ';  # method Two: Capture Mysql_error_codedeclare CONTINUE HANDLER for 1146 SET @info = ' CAN not FIND ';  # method Three: Define the condition first, then call declare Can_ Not_find CONDITION for 1146;declare CONTINUE HANDLER for can_not_find SET @info = ' can not find ';  # method four: Use Sqlwarningdec Lare exit HANDLER for sqlwarning set @info = ' ERROR ';  # method Five: Use not founddeclare EXIT HANDLER for not FOUND SET @info = ' CA N not FIND ';  # method Six: Use Sqlexceptiondeclare exit handler for SqlException set @info = ' ERROR '; the use of the 14.1.5 cursor query statement may query out more than one record, Use the cursor in stored procedures and stored functions to read the records in the query result set individually. Some books call the cursor a cursor. The use of cursors includes declaring the cursor, opening the cursor, using the cursor, and closing the cursor. The cursor must be declared before the handler and declared after the variable and the condition. 1. Declare the cursor declare cur_employee cursor for the select name, age from Employee;2. Opens the cursor open cur_employee;3. Use the cursor to fetch cur_employee into emp_name,emp_age;4. Close the cursor close cur_employee;14.1.6 Process Control can be used to control the execution of statements using stored procedures and stored functions. In MySQL, you can use if statements, case statements, loop statements, leave statements, iterate statements, repeat statements, while statements for process control.  1. If statement if Age>20 then set @[email protected]+1;elseif age=20 then @[email protected]+1;else @[email  Protected]+1;end if; if Search_conditioin then Statement_list[elseif search_condition] then statement_list] ... [Else Statement_list]end if;2. Case statement Case Case_valuewhen When_value then Statement_list[when when_value then Statement_list] ... [ELSE Statement_list] END case; case Agewhen Set @[email protected]+1;else set @[email protected]+1;end case;3. Loop statement [Begin_label:]loopstatement_listend Loop [End_label] add_num:loopset @[email protected]+1;end Loop Add_num;4. Leave statement add_num:loopset @[email protected]+1;if @count =100 thenleave add_num; # end loop, like end loop add_num;5 with Brean. Iterate statement Add_num:loopset @[email protected]+1if @count =100 thenleave add_num;else if mod (@count, 3) =0 theniterate Add_num;select * from Employee;end loop add_num;6. Repeat statement [Begin_label:]repeatstatement_listuntil search_conditionend Repeat[end_label]; repeatset @[email  protected]+1; UNTIL @count =100end repeat; 7. While statement [Begin_label:]while search_condition Dostatement_listeND while

Stored procedures and functions