MySQL存储过程之函数及元数据

1.创建函数

CREATE FUNCTION factorial (n DECIMAL(3,0))      
    RETURNS DECIMAL(20,0)
    DETERMINISTIC
BEGIN  
    DECLARE factorial DECIMAL(20,0) DEFAULT 1;
    DECLARE counter DECIMAL(3,0);
    SET counter = n;  
    factorial_loop: REPEAT
        SET factorial = factorial * counter;
        SET counter = counter - 1;
        UNTIL counter = 1
    END REPEAT;
    RETURN factorial;
END // 

　　函数跟过程很相似，唯一需要指出的语法上的不同:创建函数后必须有RETURN语句指定返回值类型并返回相应类型的值.下面是函数使用的例子:

INSERT INTO t VALUES (factorial(pi)) //  
SELECT s1, factorial (s1) FROM t //  
UPDATE t SET s1 = factorial(s1)  WHERE factorial(s1) < 5 //

　　注意在函数中访问表,这使得函数不如存储过程强大,以下是不能出现在函数中的限制:

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

　　以下指令是可以出现在函数中的:

'BEGIN END'
DECLARE
IF
ITERATE
LOOP
REPEAT
RETURN
'SET declared variable'
WHILE 

2. Metadata元数据

　　我们创建的过程或函数,都会保存在MySQL数据库中.如果要查看MySQL实际上保存了什么信息,有以下四种方法,分别是两个SHOW和两个SELECT语句:

　　1) Show: mysql> show create procedure p6//  

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

 | Procedure | sql_mode |            Create Procedure                                              | 

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

 |      p6      |                | CREATE PROCEDURE `db5`.`p6`(out p | int) set p = -5 |

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

　　这同SHOW CREATE TABLE及其他类似MySQL语句一样.它并不返回你创建过程时设定的返回值,但大部分情况下已经够用了.

　　2) Show: mysql> SHOW PROCEDURE STATUS LIKE 'p6'//  第二种获得无数据信息的方法是执行SHOW PROCEDURE STATUS,这种方法可以返回更多信息的细节.

　　3) SELECT from mysql.proc: SELECT * FROM mysql.proc WHERE name = 'p6'//  这种方法提供的信息是最多的.

　　4) SELECT from information_schema:

mysql    > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM  INFORMATION_SCHEMA.COLUMNS       
        -> WHERE TABLE_NAME = 'ROUTINES';// 

　　这是被推荐的方式,因为其他方式可能会出现错误:

　　1) 其他DBMS如SQL Server 2000,使用information_schema,只有MySQL才有SHOW方式.

　　2) 我们访问mysql.proc的权限是没有保证的,但有访问information_schema视图的权限,每个用户都有隐式的对当局者迷information_schema数据库的SELECT权限.

　　3) SELECT功能很多,可以计算表达式,分组,排序,产生可以获取信息的结果集.而这些功能SHOW没有.

　　下面是使用该方法的例子,首先使用SELECT information_schema来显示information_schema例程中有哪些列:

mysql    > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM  INFORMATION_SCHEMA.COLUMNS
        -> WHERE TABLE_NAME = 'ROUTINES';// 

　　执行以上指令后会输出结果如下:

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)

　　知道了上面的表结构,就可以查询我们想要得到的信息,如下代码可查看数据库db6中定义的存储过程:

mysql    > SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES      
        -> WHERE ROUTINE_SCHEMA = 'db6';//    

ROUTINE_DEFINITION列的访问控制

　　在INFORMATION_SCHEMA中的ROUTINE_DEFINITION列是由过程或函数组成过程体获得的,因可能存在敏感信息而只对过程创建者可见.

　　CURRENT_USER<>INFORMATIN_SCHEMA.ROUTINES.DEFINER:如果对它使用SELECT的用户不是创建它的用户,则MySQL将返回null值,而不是ROUTINE_DEFINITION列.

SHOW PROCEDURE STATUS中的辅助子句

　　既然已列出INFORMATION_SCHEMA.ROUTINES中的列,就可以回去解释SHOW PROCEDURE STATUS的新细节:语法是: SHOW PROCEDURE STATUS [WHERE condition]; .特别注意的部分是:在WHERE子句中你必须使用INFORMATION_SCHEMA列的名字,结果显示的是SHOW PROCEDURE STATUS字段的名字.例如:

mysql> SHOW PROCEDURE STATUS WHERE Db = 'p';  
/*ERROR 1054 (42S22): Unknown column 'Db' in 'where clause'*/
mysql> SHOW PROCEDURE STATUS WHERE ROUTINE_NAME = 'p';