MySQL stored procedures and functions learning notes

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