SQL错误：＃1064使用mysql在plsql中创建函数时 [英] SQL Error:# 1064 when creating function in plsql using mysql

问题描述

这是我的plsql代码

  CREATE FUNCTION calculate_hours（mon varchar（20），years INT，i INT）
 RETURNS varchar（10）
 READS SQL DATA 
 BEGIN 
 DECLARE col1 varchar（10）; 
 DECLARE col2 varchar（10）; 
 DECLARE str1 varchar（30）; 
 DECLARE str2 varchar（30）; 
 SET str1 ='CHR_MORNING'+ i; 
 SET str2 ='CHR_EVENING'+ i; 
 SELECT str1，str2 FROM att_t_register WHERE CHR_MONTH = mon AND INT_YEAR = years; 
 SET col1 = str1; 
 SET col2 = str2; 
 IF（col1 ='SUN'&& col2 ='SUN'）then 
 RETURN'S'; 
 ELSE IF（col1 ='HOL'&& col2 ='HOL'）then 
 RETURN'H'; 
 ELSE IF（col1 ='LEA'&& col2 ='LEA'）then 
 RETURN'L'; 
 ELSE IF（col1 ='ABS'&& col2 ='ABS'）then 
 RETURN' - '; 
 ELSE IF（col1 ='NA'&& col2 ='NA'）then 
 RETURN' - '; 
 END IF; 
 RETURN'8'; 
 END; 
  

当我运行这个程序时，出现错误，例如..

SQL EXECUTION ERROR＃1064.从数据库响应
您的sql语法中有错误;请检查与您的mysql服务器版本相对应的手册，以便在第26行附近使用正确的语法

请帮我解决我犯的错误。 p>

解决方案

您不需要函数，您需要存储过程。

  DELIMITER $$ 
 CREATE PROCEDURE proc_name（IN col_number1 INT，IN col_number2 INT）
 BEGIN 
 SET @ col1：= CONCAT（'CHR_MORNING '，col_number1）; 
 SET @ col2：= CONCAT（'CHR_EVENING'，col_number2）; 
 
 SET @sql：= CONCAT（'
 SELECT 
 CASE WHEN '，@ col1，'=SUN&'，@ col2，'=SUNTHENS
 WHEN（'，@ col1，'=HOL&& （'，@ col1，'=LEA&'，@ col2，'=LEA）THENL
 WHEN（'，@ col1，'=ABS&'，@ col2，'=ABS）THEN - 
 WHEN（'，@ col1，'=NA& amp ;&'，@ col2，'=NA）THEN - 
 ELSE8
 END AS whatever_you_w ant_to_name_your_column 
 FROM att_t_register WHERE CHR_MONTH =janAND INT_YEAR = 2014; 
'）; 
 
从@sql中准备stmt; 
 EXECUTE stmt; 
 DEALLOCATE PREPARE stmt; 
 
 END $$ 
 DELIMITER; 
  

然后您可以使用

  CALL proc_name（1，1）; 
  

Here is my plsql code

CREATE FUNCTION calculate_hours(mon varchar(20),years INT,i INT)
RETURNS varchar(10)
READS SQL DATA
BEGIN
DECLARE col1 varchar(10);
DECLARE col2 varchar(10);
DECLARE str1 varchar(30);
DECLARE str2 varchar(30);
SET str1='CHR_MORNING'+i;
SET str2='CHR_EVENING'+i;
SELECT str1 ,str2   FROM att_t_register WHERE CHR_MONTH=mon AND INT_YEAR=years;
SET col1=str1;
SET col2=str2;
IF(col1='SUN' && col2='SUN') THEN
RETURN 'S';
ELSE IF(col1='HOL' && col2='HOL') THEN
RETURN 'H';
ELSE IF(col1='LEA' && col2='LEA') THEN
RETURN 'L';
ELSE IF(col1='ABS' && col2='ABS') THEN
RETURN '-';
ELSE IF(col1='NA' && col2='NA') THEN
RETURN '-';
END IF;
RETURN '8';
END;

When I was running this I got error like..

SQL EXECUTION ERROR # 1064. RESPONSE FROM DATA BASE You have an error in your sql syntax ; check the manual that corresponds to your mysql server version for the right syntax to use near " at line 26

please help me where I did mistake.

解决方案

You don't need a function for this, you need a stored procedure.

DELIMITER $$
CREATE PROCEDURE proc_name(IN col_number1 INT, IN col_number2 INT)
BEGIN
SET @col1 := CONCAT('CHR_MORNING', col_number1);
SET @col2 := CONCAT('CHR_EVENING', col_number2);

SET @sql := CONCAT('
SELECT 
CASE WHEN ', @col1, '="SUN" && ', @col2, '="SUN" THEN "S"
WHEN (', @col1, '="HOL" && ', @col2, '="HOL") THEN "H"
WHEN (', @col1, '="LEA" && ', @col2, '="LEA") THEN "L"
WHEN (', @col1, '="ABS" && ', @col2, '="ABS") THEN "-"
WHEN (', @col1, '="NA" && ', @col2, '="NA") THEN "-"
ELSE "8"
END AS whatever_you_want_to_name_your_column
FROM att_t_register WHERE CHR_MONTH = "jan" AND INT_YEAR = 2014;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

Then you'd execute it with

CALL proc_name(1, 1);

这篇关于SQL错误：＃1064使用mysql在plsql中创建函数时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！