日期:2014-05-16  浏览次数:20713 次

帮我看看mysql存储函数有什么错误
delimiter // 
CREATE FUNCTION getpercent(dec1 char(20),dec2 char(20)) 
returns char(50) 
begin 
DECLARE Result char(50); 
SET Result = ''; 
if ifnull(dec1,0)=0 or ifnull(dec2,0)=0 then  
  Result='0'; 
elseif dec1>=dec2 then 
  Result='100'; 
else 
  Result = round(dec1 / dec2 * 100, 2); 
  if substr(Result,0,1)='.' then 
  Result =concat('0',Result); 
  end if; 
end if; 
Result =concat(Result,'%'); 
return (Result); 
end // 


------解决方案--------------------
SQL code
mysql> delimiter //
mysql> CREATE FUNCTION getpercent(dec1 int,dec2 int)
    -> returns char(50)
    -> begin
    -> DECLARE Result char(50);
    -> SET Result = '';
    -> if ifnull(dec1,0)=0 or ifnull(dec2,0)=0
    -> then return 0;
    -> elseif dec1>=dec2
    -> then return 100;
    -> else
    -> set Result = round(dec1 / dec2 * 100, 2);
    -> if substr(Result,0,1)='.'
    -> then return round(dec1 / dec2 * 100, 2);
    -> else
    -> return concat(Result,'%');
    -> end if;
    -> end if;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select getpercent(NULL,0);
+--------------------+
| getpercent(NULL,0) |
+--------------------+
| 0                  |
+--------------------+
1 row in set (0.00 sec)

mysql> select getpercent(0,0);
+-----------------+
| getpercent(0,0) |
+-----------------+
| 0               |
+-----------------+
1 row in set (0.00 sec)

mysql> select getpercent(1,2);
+-----------------+
| getpercent(1,2) |
+-----------------+
| 50.00%          |
+-----------------+
1 row in set (0.00 sec)

mysql> select getpercent(2,1);
+-----------------+
| getpercent(2,1) |
+-----------------+
| 100             |
+-----------------+
1 row in set (0.02 sec)