请告诉我如何在PHP中定义MySQL用户定义函数。。。
我正在使用以下代码。但它不起作用。
它抛出错误:
"(1313)仅在FUNCTION"中允许返回
$mysqli = new mysqli( $DBHost, $DBUserName, $DBPassword, $DBName );
$Query =
"CREATE PROCEDURE FunGetRegistryValue(OUT Param_RegistryKey varchar(40))
BEGIN
DECLARE vResult varchar(90);
SELECT iFnull(Value,'') INTO vResult FROM Project_Registry Where RegistryKey=Param_RegistryKey;
RETURN(vResult);
END;";
if( !$mysqli->query( $Query ) ) {
echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
当我使用FUNCTION关键字而不是PROCEDURE时,我会出现语法错误。。。
请给我"VALUE RETURNING FUNCTION"的示例代码
DELIMITER $$
CREATE DEFINER=root@localhost
FUNCTION FunGetRegistryValue(Param_RegistryKey varchar(40))
RETURNS varchar(90)
CHARSET latin1 DETERMINISTIC
BEGIN
DECLARE vResult varchar(90);
SELECT iFnull(Value,'') INTO vResult
FROM Project_Registry Where RegistryKey=Param_RegistryKey;
RETURN(vResult);
END$$
DELIMITER ;
最初是福齐亚的评论。我添加它作为一个答案,这样它就不会是一个混乱的运行查询,并且清晰易读。
$mysqli=new mysqli($DBHost,$DBUserName,$DBPassword,$DBName);
$Query =
"DROP FUNCTION IF EXISTS FunGetRegistryValue;
CREATE FUNCTION FunGetRegistryValue(Param_RegistryKey VARCHAR(40)) RETURNS VARCHAR(90) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE vResult VARCHAR(90);
SELECT IFNULL(VALUE,'') INTO vResult FROM Project_Registry WHERE RegistryKey=Param_RegistryKey;
RETURN(vResult);
END;";
if( !$mysqli->multi_query( $Query ) ) {
echo $mysqli->error;
exit();
}