对SQL的高级应用, 很难不提到function,游标,异常处理等。MySql在1.5后也提供了类似的功能。给出相应的例子:
一 split方法:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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
二 游标:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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 length of current properties.
SET len = Split(properties, ',');
-- repeat to get whole value.
REPEAT SET i = i + 1;
SET property = SUBSTRING_INDEX(SUBSTRING_INDEX(properties, ',', i), ',', -1);
SET temp = GetValueForSpecial(item, property);
IF(propertyValue IS NULL) THEN
SET propertyValue = temp;
ELSEIF(temp IS NOT NULL) THEN
SET propertyValue = CONCAT(propertyValue, ',', temp);
END IF;
UNTIL i >= len
END REPEAT;
RETURN propertyValue;
END