Functions and meta-data of MySQL stored procedures

1. Create a function

1 CREATEFUNCTIONFactorial (nDECIMAL(3,0))      2     RETURNSDECIMAL( -,0)3 Deterministic4 BEGIN  5DECLAREFactorialDECIMAL( -,0)DEFAULT1;6DECLARECounterDECIMAL(3,0);7SETCounter=N; 8 factorial_loop:repeat9 SETFactorial=Factorial*counter;TenSETCounter=Counter-1; OneUNTIL counter= 1 AENDREPEAT; -RETURNfactorial; - END//

The function is similar to the procedure, the only grammatical difference that needs to be pointed out: after you create a function, you must have a return statement that specifies the value of the returned value type and returns the corresponding type. The following are examples of function use:

1 //  2 //  3 UPDATE t SET s1 = factorial (S1)  5//

Note that accessing the table in a function makes the function less powerful than the stored procedure, and the following are limitations that cannot appear in the function:

Alter'CACHE INDEX'Call COMMIT CREATE DELETE DROP'FLUSH Privileges'GRANT INSERT KILL LOCK OPTIMIZE REPAIR REPLACE REVOKE ROLLBACK savepoint'SELECT from Table' 'SET System Variable' 'SET TRANSACTION'SHOW'START TRANSACTION'TRUNCATE UPDATE

The following directives can appear in a function:

' BEGIN END ' Declareifiteratelooprepeatreturn ' SET declared variable '

2. Metadata Meta data

The procedure or function that we create is saved in the MySQL database. If you want to see what information MySQL actually holds, there are four ways, two show and two SELECT statements, respectively:

1) Show: mysql> show Create procedure P6//

+-------------+------------+--------------------------------------------------------------------+

| Procedure |            Sql_mode | Create Procedure |

+-------------+------------+--------------------------------------------------------------------+

|                P6 | | CREATE PROCEDURE ' db5 '. ' P6 ' (out P | int) Set P =-5 |

+-------------+------------+--------------------------------------------------------------------+

This is the same as show create table and other MySQL-like statements. It does not return the return value that you set when you created the process, but most of the time it is sufficient.

2) Show: mysql> show PROCEDURE STATUS like 'p6'// The second way to get no data information is to perform the show PROCEDURE STATUS, which returns details of more information.

3) Select from Mysql.proc: Select * from MySQL. Proc WHERE name = 'p6'// This method provides the most information.

4) SELECT from Information_schema:

1 mysql    >SELECTfrom  information_schema. COLUMNS       2,         WHERE='ROUTINES '; //

This is the recommended way, because other ways may appear error:

1) Other DBMS such as SQL Server 2000, using INFORMATION_SCHEMA, only MySQL has the show mode.

2) Our access to Mysql.proc is not guaranteed, but there are permissions to access the Information_schema view, each user has an implicit SELECT permission to the Lookers information_schema database.

3) Select a lot of functions, you can calculate the expression, grouping, sorting, produce a result set that can get information. And these features show No.

Here's an example of using this method, first using select Information_schema to show which columns are in the INFORMATION_SCHEMA routine:

1 mysql    >SELECTfrom  information_schema. COLUMNS2,         WHERE='ROUTINES' ; //

Executing the above command will result in the following output:

table_name	column_name	Column_type
ROUTINES	Specific_name	VARCHAR (64)
ROUTINES	Routine_catalog	Longtext
ROUTINES	Routine_schema	VARCHAR (64)
ROUTINES	Routine_name	VARCHAR (64)
ROUTINES	Routine_type	varchar (9)
ROUTINES	Dtd_identifier	VARCHAR (64)
ROUTINES	Routine_body	varchar (8)
ROUTINES	Routine_definition	Longtext
ROUTINES	External_name	VARCHAR (64)
ROUTINES	External_language	VARCHAR (64)
ROUTINES	Parameter_style	varchar (8)
ROUTINES	Is_deterministic	varchar (3)
ROUTINES	Sql_data_access	VARCHAR (64)
ROUTINES	Sql_path	VARCHAR (64)
ROUTINES	Security_type	varchar (7)
ROUTINES	CREATED	varbinary (19)
ROUTINES	Last_altered	varbinary (19)
ROUTINES	Sql_mode	Longtext
ROUTINES	Routine_comment	VARCHAR (64)
ROUTINES	Definer	varchar (77)

Knowing the table structure above, you can query the information we want, and the following code can see the stored procedures defined in database DB6:

1 mysql    >SELECTCOUNT(* from information_schema. ROUTINES      2,         WHERE='db6' ; //    

Access control for routine_definition columns

The Routine_definition column in Information_schema is obtained by a procedure or function that is part of the process body, because sensitive information may be present and visible only to the creator of the process.

Current_user<>informatin_schema. ROUTINES. Definer: If the user that uses select is not the user who created it, MySQL returns a null value instead of a routine_definition column.

The auxiliary clauses in SHOW PROCEDURE STATUS

Now that Information_schema is listed. Routines column, you can go back and explain the new details of show PROCEDURE status: The syntax is: SHOW PROCEDURE status [WHERE condition ]; The special note is that you must use the name of the Information_schema column in the WHERE clause, and the result shows the name of the show PROCEDURE status field. For example:

1 mysql>PROCEDUREWHERE='P';   2 /* ERROR 1054 (42S22): Unknown column ' Db ' in ' WHERE clause ' */ 3 mysql>PROCEDUREWHERE='p' 

Functions and meta-data of MySQL stored procedures