sql - 当我尝试测试它是否有效时，mySQL 工作台功能将无法运行

Question

你好，我写了一个函数，它从 3 个表中获取数据并将想要的数据输入到我自己创建的表中。在它根据不同的条件将所有数据放入表中后，以字符串的形式输出想要的数据。问题是由于某种原因该功能根本无法运行。我认为这可能是语法错误。

CREATE FUNCTION getRevenue(year1 int, year2 int) returns varchar(50) 
BEGIN
    declare result varchar(50);
    declare messege varchar(50);
    declare count1 float;
    declare count2 float;
    declare num int;
    declare num2 int;
    declare diff1 int;
    declare diff2 int;
    

    declare search varchar(50);
    if (year1= '' || year1=null) || (year2='' || year2=null) then
        set result="Input year is invalid!";
        return result;
    else
    
        
        
        create table num1 as (SELECT dreamhome.Courses.cid,dreamhome.Courses.credits,
        dreamhome.Tuitions.year,dreamhome.Tuitions.fee_per_credit
        from ((dreamhome.Students_Courses
        inner join dreamhome.Courses on dreamhome.Students_Courses.cid=dreamhome.Courses.cid)
        INNER JOIN dreamhome.Tuitions on dreamhome.Students_Courses.year = dreamhome.Tuitions.year)); 
        set num=  (SELECT sum(credits) from num1 where year = year1);
        set num2 = (SELECT sum(credits) from num1 where year = year2);
        set count1 = (select num * fee_per_year from num1 where year = year1);
        set count2 = (select num2 * fee_per_year from num1 where year = year2);
        
        if(count1>count2) then
            set diff1 = count1-count2;
            set result= 'year ' + cast(year1 as char) + ' has revenue: ' + cast(diff1 as char) + ' more then ' + cast(year2 as char) + ' revunue: ' + cast(count2 as char);
            select result;
    if(count2>count1) then
    
    
                set diff1 = count2-count1;
            set result= 'year ' + cast(year2 as char) + ' has revenue: ' + cast(diff1 as char) + ' more then ' + cast(year1 as char) + ' revunue: ' + cast(count1 as char);
            select result;
    if(count2=count1)then
        set result = 'Year ' + cast(year1 as char) + ' and ' +cast(year2 as char) + ' have the same revenue: ' +cast(count1 as char);
        select result;
    return result;
    END ;
    delimiter ;

标签： sqlfunctionmysql-workbench

Answer 1

1. 不要与 if 一起使用，因为if() 函数与if 语句()之间存在差异。如果块也需要关闭使用块，则最后只需要 1。end if;if .. elseif .. elseend if;

2. create table select 不需要额外()的，在 select 之前和最后的一个就足够了。

3. 如果结果已设置，则不需要select result（就像第一个 if 一样），因此这些已被删除。
   

   if year1= '' || year1=null || year2='' || year2=null  then
       set result="Input year is invalid!";
       return result;
   else
   
       create table num1 as (SELECT dreamhome.Courses.cid,dreamhome.Courses.credits,
       dreamhome.Tuitions.year,dreamhome.Tuitions.fee_per_credit
       from dreamhome.Students_Courses
       inner join dreamhome.Courses on dreamhome.Students_Courses.cid=dreamhome.Courses.cid
       INNER JOIN dreamhome.Tuitions on dreamhome.Students_Courses.year = dreamhome.Tuitions.year); 
       set num=  (SELECT sum(credits) from num1 where year = year1);
       set num2 = (SELECT sum(credits) from num1 where year = year2);
       set count1 = (select num * fee_per_year from num1 where year = year1);
       set count2 = (select num2 * fee_per_year from num1 where year = year2);
   
       if count1 > count2 then
           set diff1 = count1-count2;
           set result= 'year ' + cast(year1 as char) + ' has revenue: ' + cast(diff1 as char) + ' more then ' + cast(year2 as char) + ' revunue: ' + cast(count2 as char);
       elseif count2 > count1 then
           set diff1 = count2-count1;
           set result= 'year ' + cast(year2 as char) + ' has revenue: ' + cast(diff1 as char) + ' more then ' + cast(year1 as char) + ' revunue: ' + cast(count1 as char);
       else -- counts are the same
           set result = 'Year ' + cast(year1 as char) + ' and ' +cast(year2 as char) + ' have the same revenue: ' +cast(count1 as char);
       end if; 
       return result;
   end if; -- end the first if/else block.