MySQL stored procedures and temporary tables

MySQL Create stored procedure

In MySQL, the basic form of creating stored procedures is as follows:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])          

Where the Sp_name parameter is the name of the stored procedure, Proc_parameter represents the parameter list of the stored procedure, the characteristic parameter specifies the properties of the stored procedure, the Routine_body parameter is the contents of the SQL code, you can use the BEGIN ... End to flag the start and end of the SQL code.

• Proc_parameter

Each parameter in the Proc_parameter is made up of 3 parts. These 3 sections are input and output types, parameter names, and parameter types, respectively. The form is as follows:

Where in represents the input parameter; out represents an output parameter; The inout represents either an input or an output; the Param_name parameter is the parameter name of the stored procedure, and the type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database.

• Characteristic

The characteristic parameter has multiple values. The values are described as follows:

1. LANGUAGE sql: Description The routine_body part is made up of SQL language statements, which are the default languages of the database system.
2. [NOT] Deterministic: Indicates whether the execution result of the stored procedure is deterministic. Deterministic indicates that the result is deterministic. The same input will get the same output each time the stored procedure is executed. Not deterministic indicates that the result is indeterminate, and the same input may get different output. By default, the result is non-deterministic.
3. {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates the limitations of the use of SQL statements by subroutines. CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data; No SQL indicates that the subroutine does not contain SQL statements; READS SQL data represents the statements in the subroutine that contain read data; modifies SQL Data represents a statement in a subroutine that contains write data. By default, the system is specified as contains SQL.
4. SQL SECURITY {definer | INVOKER}: Indicates who has permission to execute. Definer means that only the definition can execute itself; Invoker indicates that the caller can execute it. By default, the system-specified permissions are definer.
5. COMMENT ' string ': Comment information.

Under Example 14-1, create a stored procedure named Num_from_employee. The code is as follows:

// CREATE  PROCEDURE  Num_from_employee (    in emp_id int, out     count_num int)   READS SQL DATA  BEGIN      SELECT  COUNT (*)  Into  count_num      from  employee      WHERE  d_id=emp_id;   // DELIMITER;

Description: The default statement terminator in MySQL is a semicolon (;). The SQL statement in the stored procedure requires a semicolon to end. To avoid conflicts, first use "DELIMITER//" to set MySQL's terminator to//. And finally the "DELIMITER;" To restore the Terminator to the component number. This is the same as when you create a trigger.

Mysql 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          

Where the Sp_name parameter is the name of the stored function, Func_parameter represents the parameter list of the stored function, RETURNS type specifies the kind of return value, and the characteristic parameter specifies the property of the stored function. The value of this parameter is the same as the value in the stored procedure; The Routine_body parameter is the contents of the SQL code, and you can use the BEGIN ... End to flag the start and end of the SQL code.

Func_parameter

Func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, in the form of the following: Param_name type

Where the Param_name parameter is the parameter name of the stored function, and the type parameter specifies the parameter type of the stored function, which can be any data type of the MySQL database.

Create a storage function named Name_from_employee under "Example 14-2". The code is as follows:

DELIMITER//

CREATE  FUNCTION  Name_from_employee (emp_id INT)  RETURNS VARCHAR ()  BEGIN  RETURN  ( SELECT  name  from  employee  WHERE  num=emp_id);   //

DELIMITER;

MySQL temp table

First, the temporary table is visible only on the current connection, and when the connection is closed, MySQL automatically deletes the table and frees all the space. Therefore, a temporary table with the same name can be created in a different connection, and the operation belongs to the temporary table of this connection.

The syntax for creating a temporary table is similar to creating a table syntax, where you add keyword temporary, such as:

CREATE temporary table name (...)

temporary tables use some restrictions :

* Temporary tables are used on memory, MyISAM, merge, or InnoDB and do not support MySQL cluster clusters);

The show tables statement does not list temporary tables, and temporary table information is not present in INFORMATION_SCHEMA; Show create table can view temporary tables;

* You cannot use Rename to rename a temporary table. But you can alter TABLE rename instead:

Mysql>alter TABLE orig_name RENAME new_name;

* You can copy a temporary table to get a new temporary table, such as:

Mysql>create temporary table new_table select * from Old_table;

* But in the same query statement, the same temporary table can only occur once. Such as:

You can use:mysql> select * from TEMP_TB;

But cannot use:mysql> select * from TEMP_TB, temp_tb as T;

Err Message: Error 1137 (HY000): Can ' t reopen table: ' TEMP_TB '

Similarly the same temporary table can not appear in the storage function more than once, if in a storage function, a different alias to find a temporary table multiple times, or in this storage function with a different statement lookup, this error will occur.

* But different temporary tables can appear in the same query statement, such as temporary table TEMP_TB1, TEMP_TB2:

Mysql> SELECT * from TEMP_TB1, TEMP_TB2;

* Temporary tables can be manually deleted:

DROP temporary TABLE IF EXISTS temp_tb;

When you create a temporary table with a claim type of heap, MySQL creates the temporary table in memory, which is the memory table :

CREATE temporary table name (.... ) TYPE = HEAP

Because the heap table is stored in memory, the query you run against it may be faster than the temporary table on the disk. Such as:

Mysql> Create temporary table temp_tb type='heap'select from TEMPTB;

CREATE Temporary TABLE ' temp_tb ' (  int(0'),   Char() notnull,  ' age ' tinyint (4) not null) ENGINE= MEMORY DEFAULT CHARSET=GBK

Temporary tables and memory tables

The temporary table is mainly to put some subsets of the middle large result set, the memory table can put some frequently used data.

* Temporary tables: tables are built into memory, data is in memory
* Memory table: Table is built on disk, data in memory

The memory size used by temporary tables and memory tables can be specified by Max_heap_table_size, tmp_table_size in MY.CNF:
[Mysqld]
max_heap_table_size=1024m #内存表容量
tmp_table_size=1024m #临时表容量

When the data exceeds the maximum setting of the temporary table, the automatic conversion to the disk table, at this time because of the need for IO operation, performance will be greatly reduced, and the memory table will not, the memory table full, will prompt the data full error.

The show tables command does not display a temporary table.

The following is a summary of the differences between the memory table and the temporary table:

Memory table:

1. The default storage engine is memory
2. Memory table size can be set by parameter max_heap_table_size
3. Error when reaching memory limit set by Max_heap_table_size
4. Table definitions are saved on disk, data and indexes are saved in memory
5. Cannot contain text, blob, and other fields
Temp table:

1. The default storage engine is the MySQL server default engine, the engine type can only be: memory (heap), MyISAM, Merge, InnoDB (memory temp table due to the increase of the table may be converted to myisam temporary table)
2. The temporal table size can be set by the parameter tmp_table_size.
3. A temporary file is created on disk after reaching the upper memory limit set by Tmp_table_size
4. Table definitions and data are stored in memory
5. Can contain fields such as text, blobs, etc.

Temporary tables are generally less used, typically created either dynamically in the application or internally by MySQL according to the SQL execution plan.

Memory tables are mostly used as caches, especially when no third-party caches are in use. Today, with the popularity of Memcache and NoSQL, fewer choices are being used in memory tables.

MySQL stored procedures and temporary tables