MySQL預存程序之函數及中繼資料

標籤：

1.建立函數

 1 CREATE FUNCTION factorial (n DECIMAL(3,0))       2     RETURNS DECIMAL(20,0) 3     DETERMINISTIC 4 BEGIN   5     DECLARE factorial DECIMAL(20,0) DEFAULT 1; 6     DECLARE counter DECIMAL(3,0); 7     SET counter = n;   8     factorial_loop: REPEAT 9         SET factorial = factorial * counter;10         SET counter = counter - 1;11         UNTIL counter = 112     END REPEAT;13     RETURN factorial;14 END // 

　　函數跟過程很相似，唯一需要指出的文法上的不同:建立函數後必須有RETURN語句指定傳回值類型並返回相應類型的值.下面是函數使用的例子:

1 INSERT INTO t VALUES (factorial(pi)) //  2 SELECT s1, factorial (s1) FROM t //  3 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‘DECLAREIFITERATELOOPREPEATRETURN‘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:

1 mysql    > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM  INFORMATION_SCHEMA.COLUMNS       2         -> 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常式中有哪些列:

1 mysql    > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM  INFORMATION_SCHEMA.COLUMNS2         -> 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中定義的預存程序:

1 mysql    > SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES      2         -> 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欄位的名字.例如:

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

MySQL預存程序之函數及中繼資料