对SQL的高级应用, 很难不提到function,游标,异常处理等。MySql在1.5后也提供了类似的功能。给出相应的例子:
一 split方法:
Code
DELIMITER //
DROP FUNCTION IF EXISTS Split//
CREATE FUNCTION Split(f_string VARCHAR(1000), f_delimiter VARCHAR(5)) RETURNS INT(11)
BEGIN
RETURN 1 + (LENGTH(f_string) - LENGTH(REPLACE(f_string, f_delimiter, '')));
END
二 游标:
Code
DROP FUNCTION IF EXISTS GetValueForSpecial//
CREATE FUNCTION GetValueForSpecial (s VARCHAR(255), p VARCHAR(255)) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE returnValue VARCHAR(255);
DECLARE cur1 CURSOR FOR
SELECT PropertyValue from tempvalue where Server=s and Property=p;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET returnValue=NULL;
OPEN cur1;
-- we don't need to do repeat here for we just have one row returned.
FETCH cur1 INTO returnValue;
CLOSE cur1;
RETURN returnValue;
end//
三 function
Code
DROP FUNCTION IF EXISTS GetPropertyValue//
CREATE FUNCTION GetPropertyValue(item VARCHAR(255), properties VARCHAR(1000)) RETURNS VARCHAR(1000)
BEGIN
DECLARE propertyValue VARCHAR(1000);
DECLARE property VARCHAR(255);
DECLARE temp VARCHAR(255);
DECLARE len INT(8);
DECLARE i INT(8) DEFAULT 0;
-- Get the