MySQL Learning note 12 (Stored procedures and functions)

     Stored procedures and functions, a database object used to implement a set of SQL statement code about table operations as a whole, and the database object that is most closely associated with the database object table. In a database system, when stored procedures and functions are called, the groups of SQL statements set in those objects are executed to achieve the appropriate functionality.    The related concepts of stored procedures and functions a complete operation of a table is often not a single SQL statement, but rather a set of SQL statements to be implemented.         Example: Order Processing in order to purchase a product: (1) before generating an order, you first need to see if there is a corresponding item in the inventory (2) if there is a corresponding item in the inventory, order the item as needed to not sell it to others, and delete the inventory quantity to reflect the correct inventory quantity.    (3) If there is no corresponding commodity in the inventory of the goods, it is necessary to place an order with the supplier. In a specific application, a complete operation will contain multiple SQL statements, which need to be executed in accordance with the execution results of the preceding SQL statement, followed by a selection of SQL.    To solve this problem, MySQL provides database object stored procedures and functions. Stored procedures and functions are actually a set of SQL statements that are compiled beforehand and stored in the database 夅.                Ii. basic operation of the stored procedure create PROCEDURE procedure_name ([procedure_paramiter[,...]) [Characteristic ...] Routine_body procedure_name: Stored procedure name procedure_paramter: Stored procedure parameter characteristic: Stored Procedure properties Routine_bod        Y: Stored procedure SQL syntax code, you can use Begin...end to flag the beginning and end of the SQL statement Note: The respected process is named procedure_xxx or proce_xxx; Syntax form for each parameter in Procedure_paramter: [in| Out|        INOUT] Paramter_name Type the above statement: each parameter has 3 parts, respectively, the input output type, parameter name, parameter type.                Value of characteristic: LANGUAGE SQL                | [NOT] Deterministic | {CONTAINS Sql|no sql| READS SQL data| Modifies SQL DATA} | SQL SECURITY {definer| INVOKER} | COMMENT ' string ' language sql: The routine_body part of a stored procedure is made up of SQL statements. The default statement for MySQL [not]deterministic: Indicates whether the execution result of the stored procedure is determined. Deterministic indicates that the execution result is deterministic, that is, each time the stored procedure is executed, the same output will be obtained if the same parameters are entered, not deterministic, and the execution result is indeterminate, that is, the same input may have different outputs.        The default value is deterministic.            {contains sql|no sql|reads SQL Data|modifies SQL data}: Contains SQL: Contains SQL statement no SQL: SQL not included Reads SQL data: A statement containing read data modifies SQL data: A statement containing write data defaults to contains SQL SQL security {Definer |invoke} sets who has permission to execute, and if the value is definer, it means that only the definition is able to execute, and if the value is Invoker, the call can be executed.            The default is definer comment ' string ' for the comment statement example: Create a stored procedure that queries the payroll of all employees. Delimiter $ $create Procedure Proce_employee_sale () Comment ' query payroll for all employees ' Beginselect sale from T_employee;end $$ de      Limiter; View Show ProCedure Status \g Show CREATE PROCEDURE proce_name\g Modify ALTER procedure procedure_name [Charac        Teristic ...] The value of characteristic when modified is | {CONTAINS Sql|no sql| READS SQL data| Modifies SQL DATA} | SQL SECURITY {definer| INVOKER} | COMMENT ' String ' Deletes the drop procedure Proce_name Three, the basic operation of the function creates the CREATE function function_name ([Function_para            mter[,...]]) [Characteristic ...] Routine_body Routine_body can use Begin_end to flag the start and end of an SQL statement: A function to create an employee's salary delimiter $ $create functi On Func_employee_sale (empno int) returns double (10,2) Comment ' Querying the work of an employee ' Beginreturn (select sale from T_employee where t_    EMPLOYEE.EMPNO=EMPNO); end$$ delimiter;            View Show function Status\g Show create function func_name\g modify ALTER function function_name        [Characteristic ...] The value of characteristic when modified is | {CONTAINS Sql|no sql| READS SQL data|           Modifies SQL DATA} | SQL SECURITY {definer| INVOKER} | COMMENT ' String ' deletes the drop function func_name Four, stored procedure and function expression action variable declaration variable declare var_name[,.. .]        Type [default value] does not specify default or NULL example: declare employee_sale int default 1000            Assignment variable set var_name=expr[,...]                Expr is an assignment expression for a variable select field_name[,...] into var_name[,...]            From table_name where condition using query results to assign values to a parameter note: When you assign a query result to a variable, the result of the query statement can only be a single row.                Example: declare employee_sale int default + set employee_sale=3500;    Select Sale into Employee_sale from T_employee where empno=7566; Operation conditions define conditions by declare to define declare condition_name condition for Condition_value Co Ndition_value:sqlstate[value] Sqlstate_value|mysql_error_code ConditIon_name indicates the condition name to be defined condition_value the type Sqlstate_value and Mysql_error_code used to implement the set condition: the error used to set the condition                Define handlers declare Handler_type handler for condition_value[,...] sp_statement handler_type: Continue |exit |undo Condition_value:sqlstate[value] Sqlstat                E_value |condition_name |sqlwarning |not found |sqlexception        |mysql_error_code use cursor to DECLARE CURSOR DECLARE cursor_name cursor for select_statement;        Open cursor opening cursor_name using cursor fetch cursor_name into var_name [, Var_name] ...             Close cursor Close cursor_name Example: Declare cursor_employee cursor FOR select sale from T_employee;            Open Cursor_employee fetch cursor_employee into Employee_sale; Close Cursor_employee Note: When using the cursor specifically, the cursor must precede the handler and change theAfter the volume and conditions are declared, and finally, the cursor must be closed.            Using Process Control condition control to implement conditional control statements through if,case, if statements are conditionally controlled, different statements are executed according to whether conditions are met, and more complex conditional controls can be implemented for case statements.                IF search_condition then statement_list [ELSEIF search_condition then statement_list] ...            [ELSE search_condition] END IF Case Case_value when When_value then Statement_list [when When_value then                 Statement_list] [ELSE statement_list] END case loop control [Begin_label:] Loop statement_list END Loop [End_label] Begin_label and End_lab The El represents the start and end of the loop, the two flags must be the same, and can be omitted for the loop statement, if you want to exit the loop body being executed, you can use the keyword LEAVE LEAVE label [begin_l Abel:] While Search_condition does statement_list end while [end _label] [Begin_label:] REPEAT search_condition do sTatement_list END REPEAT The concepts and definitions of stored procedures and functions through the above introduction, but the specific use of stored procedures and functions is still vague. The difference between a stored procedure and a function: The function must have a return value, and the stored procedure does not.    Stored procedures have far more parameter types than function parameter types.    The advantages and disadvantages of stored procedures and functions. Advantages: 1, stored procedures and functions allow standard component programming, improve the reusability of SQL statements, sharing, and portability 2, stored procedures and functions can achieve a faster execution speed, can reduce network traffic 3, stored procedures and functions can be used as a security mechanism To take advantage of the disadvantages: 1, stored procedures and functions are more complex than a single SQL statement, require users with higher skills and more complex experience 2, when writing stored procedures and functions, you need to create these database objects permissions

MySQL Learning note 12 (Stored procedures and functions)