SQL stored procedures and functions

SQL stored procedures:

Origin: In a specific application, a complete operation will contain multiple SQL statements, which need to be executed according to the execution result of the preceding SQL statement to execute the following SQL statement. As a result, MySQL provides database object stored procedures and functions.

Definition: A stored procedure is one or more SQL commands that are stored as executable objects in the database. The difference with a function is that the function has a return value, and the stored procedure does not.

Advantages:

1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can increase the speed of the database execution .
2. When complex operations are performed on a database, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database .
3. Stored procedures can be reused to reduce the workload of database developers.
4. High security, can be set only some users have the right to use the specified stored procedures

To create a stored procedure:

Creating a stored procedure in MySQL is implemented through the SQL statement CREATE procedure, which has the following syntax:
CREATE PROCEDURE procedure_name ([procedure_paramter[,...]])
[Characteristic ...] Routine_body
Instance:

DELIMITER $$     //Set delimiter CREATEPROCEDURE  proce_employee_sal () COMMENT'  query wages for all employees 'BEGIN    SELECT  sal    from  t _employee; END $ $DELIMITER;    Recover separators

To call a stored procedure:

Calling a stored procedure with call

To create a function:

The CREATE function in MySQL is implemented by the SQL statement create function, which has the following syntax:
CREATE function function _name ([function_paramter[,...])
[Characteristic ...] Routine_body
Example: Query salary based on employee number

DELIMITER $$CREATE FUNCTIONFunc_employee_sal (empnoINT( One))//Incoming valueRETURNS DOUBLE(Ten,2)//return value Comment'Query the salary of an employee'BEGIN    RETURN(SELECTSal fromT_employeeWHERET_employee.empno=empno);END$ $DELIMITER;

Call Function:

Call/select func_employee_sal (7035);

Is it similar to the process of creating a trigger?

Variables in the stored procedure:

1. declaring variables

The definition of a variable in MySQL is implemented by the keyword declare, which has the following grammatical form:

DECLARE var_name[,... ][DEFAULT value]

2. Assignment variables

In MySQL, the variable assignment is implemented by the keyword set, which has the following syntax:

SET var_name=expr[,... ]

When assigning a value to a variable, in addition to the above syntax, you can also use the keyword "SELECT ...". into "statement, which has the following grammatical form:

SELECT field_name[,... ] into Var_name[,... ]from    table_name  WHERE condition

Cursors in a stored procedure:

SQL query statements can return multiple record results, and if you want to traverse the results of these queries, you can use cursors to implement them. Equivalent to a pointer in C + +.

1. declaring cursors
DECLARE cursor_name cursor for select_statement;
2. Open cursor
OPEN cursor_name
3. Using cursors
FETCH cursor_name into Var_name [, Var_name] ...
4. Close Cursors
CLOSE cursor_name

Example: Statistics on the number of employees with wages greater than 999

DROP PROCEDURE IF EXISTSEmplayee_count;delimiter $ #创建存储过程CREATE PROCEDUREEmplayee_count (out NUMINTEGER)//out represents the output, num is the output variableBEGIN#声明变量DECLAREEmplayee_salINTEGER;DECLAREFlagINTEGER; #声明游标DECLARECursor_emplayeeCURSOR  for SELECTSal fromT_employee;DECLARE CONTINUEHANDLER for  notFOUNDSETFlag= 1; #设置结束标志SETFlag=0;SETNum=0; #打开游标OPENCursor_emplayee; #遍历游标指向的结果集FETCHCursor_emplayee intoemplayee_sal; whileFlag<>1 DoIFEmplayee_sal>999  ThenSETNum=Num+1;END IF;FETCHCursor_emplayee intoemplayee_sal; Next RecordEND  while; #关闭游标CLOSECursor_emplayee;END$DELIMITER;

To view stored procedures and functions:

• View stored procedure status information through the show PROCEDURE status statement
• viewing function state information through the show function status statement
• View stored procedure definition information through the show CREATE procedure statement
• viewing function definition information through the show CREATE function statement

To modify a stored procedure:

Modifying stored procedures in the MySQL database management system is implemented by the SQL language "ALTER PROCEDURE", which has the following grammatical form:

ALTER PROCEDURE procedure_name

[Characteristic ...]

To modify a function:

In the MySQL database management system, the modified function is implemented by the SQL language "ALTER function", which has the following grammatical form:

ALTER FUNCTION function_name

[Characteristic ...]

To delete stored procedures and functions:

DROP PROCEDURE Proce_name;

DROP FUNCTION Func_name;

SQL stored procedures and functions