Learn to Tarena gold medal lecturer Heshan, Golden Dawn technology company technical Director Chalimoux Course notes synthesis.
1. What are stored procedures and functions
Put SQL statements in a collection, and then call stored procedures and functions directly to execute the SQL statements that have already been defined, and through stored procedures and functions, you can avoid developers repeating the same SQL statements.
MySQL stored procedures and functions are saved in the server, stored and executed in the server, can reduce the consumption of data transfer between the client and server side.
A stored procedure is a set of statements that have been saved in the database and can be called at any time.
Stored procedures allow for standard component programming, where a stored procedure can be called multiple times in a program after it is created without having to rewrite the SQL statement for the stored procedure, and the database professional can modify the stored procedure at any time, without affecting the application source code. Because the application source code contains only the call statement of the stored procedure, it greatly improves the portability of the program.
1.1 when to use stored procedures (characteristics of stored procedures):
(1) The stored procedure runs on the server side, and it executes faster.
(2) Once the stored procedure executes once, it will reside in the high buffer memory, in the future operation, only need to invoke the compiled binary code from the high buffer memory to execute, which can improve the performance and response time of the system.
(3) Using stored procedures, you can ensure the security of the database, because the use of stored procedures can complete all operations of the database, because you can put what you want to do in the SQL statement, and then programmatically control access to the database.
1.2 using MySQL the stored procedure is better than executing MySQL alone where is the advantage? What are the benefits?
A program that is written on a computer called Transaction-sql because the stored procedure has the following advantages
1 stored procedures allow standard component-type programming
A stored procedure can be called multiple times in a program after it is created, without having to rewrite the SQL statement for the stored procedure, and the database professional can modify the stored procedure at any time, without affecting the source code of the application, as
The application source code contains only the call statement of the stored procedure, which greatly improves the portability of the program
2 stored procedures enable faster execution
If an operation contains a large number of Transaction-sql code, or is executed more than once, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, when a stored procedure is first run, the query optimizer optimizes it for analysis and gives the execution plan in the system tables that are eventually present, while the batch TRANSACTION-SQL statements are compiled and optimized at each run, so the speed is relatively slower
3 stored procedures can reduce network traffic
For the same operation against a data database object, such as querying, modifying, if the TRANSACTION-SQL statement involved in this operation is organized into a stored procedure, when the stored procedure is called on the client computer, the call statement is passed on the network, otherwise it will be multiple SQL statement, which greatly increases network traffic and reduces network load
4 stored procedures can be used as a security mechanism to take full advantage of
The system administrator restricts the access to the corresponding data by restricting the permissions of executing a stored procedure so as to avoid unauthorized users ' access to the data and ensure the security of the data.
2. Create a stored procedure:
CREATE PROCEDURE Sp_name
([proc_parameter[,...]])
[Characteristic]
Routine_body
Description
Sp_name: Stored procedure name, custom, try to make a meaningful name
([proc_parameter[,...]]): Parameters that need to be received or output
[Characteristic]: properties
Routine_body: Code to execute, written in begin and end, begin and end similar to the function body {}
3. parameters of the stored procedure:
3.1 input and OUTPUT parameters
input and OUTPUT parameters name of the parameter Types of Parameters
3.1.1 input and output parameters include:
In: input, transfer data from outside to stored procedure
Out: Output that passes the results of stored procedure operations to the outside world
INOUT: Input and output, can not only transfer the external data to the stored procedure, but also can transfer the results of the operation of the stored procedure to the outside world
3.1.2 Types of Parameters
Can be any type in the MySQL database
3.1.3 features
LANGUAGE sql: Default, Description Routine_body part is registered by SQL statement, which is the default language of the database
Deterministic: Indicates that the result of the stored procedure execution is deterministic, and each time the stored procedure executes, the same input gets the same output.
Not deterministic: Indicates that the result of the execution of the stored procedure is not deterministic, and each time the stored procedure executes, the same input gets different output, by default, the result is non-deterministic.
subqueries Use SQL limitations of the statement:
CONTAINS sql: Indicates that the subroutine can contain SQL statements, but does not contain read or write data. This limit is used by default.
No sql: does not contain SQL statements
READS SQL Data: Statements that contain query data
Modifies SQL data: Statements that contain write data
--
SQL SECURITY Definer/invoker: Who has permission to execute this stored procedure, Definer (the default) means that only the definition can execute itself, INVOKER indicates that the caller can execute it.
3.1.4 Notes
COMMENT ' String '
' String ': note information that allows you to specify a comment when creating a stored procedure.
4. Changing the default delimiter for stored procedures
by delimiter to change
Example:
DELIMITER//
CREATE PROCEDURE Sp_demo1 ()
BEGIN
SELECT * from Users2;
END
//
DELIMITER;.
Query OK, 0 rows affected (0.28 sec)
Description: Because the stored procedure also contains a lot of SQL statements, and these SQL statements are also terminated with a semicolon, in order to avoid the delimiter conflict, so use Deliniter to change the delimiter
Attention:
(1 ) DELIMITER there must be a space between the delimiters and the delimiter, otherwise the setting will be invalid.
(2) Note that it is a good practice to return the delimiter to a semicolon after each creation of the stored procedure.
5. Create a stored procedure with parameters:
DELIMITER//
CREATE PROCEDURE age_from_user2 (in user_id int,out user_age INT)
READS SQL DATA
BEGIN
SELECT age to user_age from user
WHERE id=user_id;
END
//
DELIMITER;.
Description
(1) int indicates the return value of the parameter, or the data type of the parameter
(2) into parameter name: indicates that the result of the SQL statement execution is assigned to the argument following the into
Create in examples of stored procedures for parameters:
Inquiry order quantity is larger than the external pass in the parameter p_in order data
DELIMITER//
CREATE PROCEDURE proc2 (in p_in INT)
BEGIN
SELECT * from ' order ' WHERE onum>p_in;
END
//
DELIMITER;
Create with out examples of output parameters:
DELIMITER//
CREATE PROCEDURE proc3 (out P_out INT)
BEGIN
SELECT COUNT (*) into p_out from custom;
END
//
DELIMITER;
Description: Into parameter name: indicates that the result of Count (*) is assigned to the argument following into
Create with InOut stored procedures for input and output parameters:
DELIMITER//
CREATE PROCEDURE proc4 (INOUT p_io INT)
BEGIN
SET p_io=5;
END
//
DELIMITER;
6. Create a storage function
stored procedures and stored functions are essentially the same, all belong to the storage program, that is, stored in the database program, when used, can be called at any time.
6.1 the difference between stored procedures and stored functions
(1) The stored procedure can specify in and out parameters, the storage function does not need to specify the input and output parameters, the storage function all parameters belong to the in parameter.
(2) The store function can return the result of the operation through a return statement, but the stored procedure does not allow the return statement to be called, and the stored procedure can return the result of the operation to the outside world by calling out the out parameter.
6.2 Create a storage function
CREATE FUNCTION func_name ([func_parameter[...])
RETURNS type
[Characteristic ...]
Routine_body
Description
Return type: Indicates the kind of return value
[Characteristic ...] : Represents the characteristics of a function, consistent with the characteristics of a stored procedure
Example:
DELIMITER//
CREATE FUNCTION Username_from_user (user_id INT)
RETURNS VARCHAR (20)
BEGIN
RETURN (SELECT username from users2 WHERE id=user_id);
END
//
DELIMITER;
Description
RETURNS is the data type that specifies the return value
Return () is the return result that is fed back to the outside world.
7. calling a stored procedure
Call Sp_name ([parameter[,...])
Attention:
(1) When calling, be sure to have permission to execute
(2) After the call, the system executes the statement of the stored procedure, and then returns the output result
Example:
Call Sp_demo1 ();
7.1 to invoke a stored procedure with parameters:
Create a stored procedure first:
DELIMITER//
CREATE PROCEDURE age_from_user2 (in user_id int,out user_age INT)
READS SQL DATA
BEGIN
SELECT age to user_age from user
WHERE id=user_id;
END
//
DELIMITER;
Then call this stored procedure, and note that the variable uses the @ variable name
Call Age_from_user2 (@use_age);
To view the value of a variable:
Mysql> SELECT @use_age;
+----------+
| @use_age |
+----------+
| 22 |
+----------+
1 row in Set (0.00 sec)
7.2 Call has in stored procedures for parameters:
Create in examples of stored procedures for parameters:
Inquiry order quantity is larger than the external pass in the parameter p_in order data
DELIMITER//
CREATE PROCEDURE proc2 (in p_in INT)
BEGIN
SELECT * from ' order ' WHERE onum>p_in;
END
//
DELIMITER;
Call has in stored procedures for parameters:
SET @num = 10;
Call PROC2 (@num);
7.3 with out parameters of the stored procedure are called
Create with out examples of output parameters:
DELIMITER//
CREATE PROCEDURE proc3 (out P_out INT)
BEGIN
SELECT COUNT (*) into p_out from custom;
END
//
DELIMITER;
Description: Into parameter name: indicates that the result of Count (*) is assigned to the argument following into
To call a stored procedure:
mysql> SET @amount = 0;
Query OK, 0 rows Affected (0.00 sec)
Mysql> call PROC3 (@amount);
Query OK, 1 row Affected (0.00 sec)
Mysql> SELECT @amount;
+---------+
| @amount |
+---------+
| 4 |
+---------+
1 row in Set (0.00 sec)
7.4 Call has inout stored procedures for parameters
Create with InOut stored procedures for input and output parameters:
DELIMITER//
CREATE PROCEDURE proc4 (INOUT p_io INT)
BEGIN
SET p_io=5;
END
//
DELIMITER;
Call has inout stored procedures for parameters:
mysql> SET @num =-1;
Query OK, 0 rows Affected (0.00 sec)
Mysql> call proc4 (@num);
Query OK, 0 rows Affected (0.00 sec)
Mysql> SELECT @num;
+------+
| @num |
+------+
| 5 |
+------+
1 row in Set (0.00 sec)
8. calling a storage function
Calling a stored function is the same format as calling a system function.
SELECT func_name ([parameter[,...]]);
Example:
To create a storage function:
DELIMITER//
CREATE FUNCTION Username_from_user (user_id INT)
RETURNS VARCHAR (20)
BEGIN
RETURN (SELECT username from users2 WHERE id=user_id);
END
//
DELIMITER;
Call a stored function:
Mysql> SELECT Username_from_user (120);
+-------------------------------------+
| Username_from_user (120) |
+-------------------------------------+
| Nihao |
+-------------------------------------+
1 row in Set (0.05 sec)
Example of calling a stored function:
Create a storage function first
DELIMITER//
CREATE FUNCTION func1 (id CHAR)
RETURNS VARCHAR (10)
BEGIN
RETURN (SELECT cname from custom WHERE cid=id);
END
//
DELIMITER;
Call a stored function:
mysql> SET @id = ' 110002 ';
Query OK, 0 rows Affected (0.00 sec)
mysql> SET @name = ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> SELECT func1 (@id) into @name;
Query OK, 1 row Affected (0.00 sec)
Mysql> SELECT @name;
+--------------------------------------------+
| @name |
+--------------------------------------------+
| Hubei Zhongcheng Grain and oil Industry Co., Ltd. |
+--------------------------------------------+
1 row in Set (0.00 sec)
9. View the stored procedures and functions that have been created:
View stored procedures: Show PROCEDURE STATUS like ' sp_name ';
View stored functions: Show function STATUS like ' func_name ';
View all stored procedures: Show PROCEDURE STATUS \g
View all storage functions: Show function STATUS \g
Note: ' Sp_name ' and ' func_name ' must be quoted.
Cases:
SHOW PROCEDURE STATUS like ' Age_from_user3 ';
SHOW PROCEDURE STATUS like ' Age_from_user3 ' \g; (output by row)
1. Row ***************************
Db:homework (under which database)
Name:age_from_user3 (the name of the stored procedure)
Type:procedure (type of stored procedure)
Definer: [email protected] (defined by the stored procedure)
MODIFIED:2014-11-13 20:48:11 (modification time of the stored procedure)
CREATED:2014-11-13 20:48:11 (creation time of the stored procedure)
Security_type:definer (security type, who can execute this stored procedure)
Comment: (note)
CHARACTER_SET_CLIENT:GBK (client's character set)
Collation_connection:gbk_chinese_ci (check character set)
Database Collation:utf8_bin (character set for databases)
1 row in Set (0.00 sec)
To view storage functions:
SHOW FUNCTION STATUS like ' username_from_user ';
SHOW FUNCTION STATUS like ' Username_from_user ' \g;
1. Row ***************************
Db:homework
Name:username_from_user
Type:function
Definer: [Email protected]
MODIFIED:2014-11-13 20:40:10
CREATED:2014-11-13 20:40:10
Security_type:definer
Comment:
Character_set_client:gbk
Collation_connection:gbk_chinese_ci
Database Collation:utf8_bin
1 row in Set (0.00 sec)
See the definition of stored procedures and stored functions
SHOW CREATE PROCEDURE Sp_name;
SHOW CREATE FUNCTION Func_name;
Example:
SHOW CREATE PROCEDURE age_from_user3\g;
1. Row ***************************
Procedure:age_from_user3
Sql_mode:no_engine_substitution (SQL Type)
Create procedure:create definer= ' root ' @ ' localhost ' Procedure ' age_from_user3 ' (in user_id int,out user_age INT)
READS SQL DATA
BEGIN
SELECT age to User_age from Users2
WHERE id=user_id;
END
Character_set_client:gbk
Collation_connection:gbk_chinese_ci
Database Collation:utf8_bin
1 row in Set (0.00 sec)
See examples of creating functions:
mysql> SHOW CREATE FUNCTION username_from_user\g;
1. Row ***************************
Function:username_from_user
Sql_mode:no_engine_substitution
Create function:create definer= ' root ' @ ' localhost ' Function ' username_from_user ' (user_id INT) RETURNS varchar (20) CHARSET UTF8 COLLATE Utf8_bin
BEGIN
RETURN (SELECT username from users2 WHERE id=user_id);
END
Character_set_client:gbk
Collation_connection:gbk_chinese_ci
Database Collation:utf8_bin
1 row in Set (0.00 sec)
One by one . View stored procedures and functions in database Information_schema
The database information_schema holds all stored procedures and stored functions in the Routines table.
(1) Switch database:mysql> use INFORMATION_SCHEMA;
(2) View ROUTINES table:mysql> SELECT * from Routines\g
or use: SELECT * from Information_schema.routines \g
Conditional queries can be made based on the options of the query results:
SELECT * from Information_schema.routines WHERE routine_type= ' FUNCTION ' \g
Modifying the properties of stored procedures and functions
We need to understand that the change here is not to modify the SQL statement, but to modify its security and data access. We can also view and modify them through the client tool.
ALTER PROCEDURE sp_name [COMMENT ' string '];
ALTER FUNCTION func_name [COMMENT ' string '];
To modify a stored procedure:
mysql> ALTER PROCEDURE sp_demo1 COMMENT ' This is A TEST ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> SHOW PROCEDURE STATUS like ' sp_demo1 ' \g;
To modify a storage function:
ALTER FUNCTION username_from_user COMMENT ' This is A TEST of fuction ';
Mysql> SHOW FUNCTION STATUS like ' Username_from_user ' \g;
12.1 security of stored procedures
There are two types of security:
Definer: The person who defines the stored procedure can execute it, by default
INVOKER: caller, the person calling this stored procedure can execute it
There are 4 SQL data access options:
CONTAINS sql: Stored procedure or function contains SQL statement
No sql: Stored procedure or function does not contain SQL statements
READS SQL data: The SQL statement of a stored procedure or function is the data of a read database
Modifies SQL data: SQL statements for stored procedures or functions are data that modifies the database
12.2 modifying the security of stored procedures and functions
Check the property values before you modify them:
SELECT * from Information_schema.routines \g
Then use the ALTER statement to modify the security type and data access options for the stored procedure proc2:
ALTER PROCEDURE PROC2
Modifies SQL DATA
SQL SECURITY INVOKER;
SHOW PROCEDURE STATUS like ' proc2 ' \g
SELECT * from Information_schema.routines \g
Delete stored procedures and stored functions
DROP PROCEDURE Sp_name;
DROP FUNCTION Sp_name;
When you delete a stored procedure or stored function that does not exist, an error is displayed:
mysql> DROP PROCEDURE Sp_demo1;
ERROR 1305 (42000): PROCEDURE Homework.sp_demo1 does not exist
If you want to mask the error, you can use it as a warning:
DROP PROCEDURE IF EXISTS sp_name;
DROP FUNCTION IF EXISTS sp_name;
Example:
mysql> DROP PROCEDURE IF EXISTS sp_name;
Query OK, 0 rows affected, 1 Warning (0.00 sec)
View the contents of the warning: show WARNINGS;
Mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------+
| Note | 1305 | PROCEDURE Homework.sp_name does not exist |
+-------+------+-------------------------------------------------------------------------+
1 row in Set (0.01 sec)
Example of deleting a stored procedure:
mysql> DROP PROCEDURE Sp_demo1;
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW CREATE PROCEDURE sp_demo1;
ERROR 1305 (42000): PROCEDURE Sp_demo1 does not exist
Example of deleting a storage function:
DROP FUNCTION Username_from_user;
MySQL stored procedures and functions learning notes