当使用IF语句MYSQL时 [英] WHILE LOOP with IF STATEMENT MYSQL

问题描述

我想为MySQL创建一个存储例程，以计算一个月的工作或工作日数(工作日为星期一至星期五).

I would like to create a stored routine for MySQL that figures out the number of business or working days for a month (Working Days are Monday thru Friday).

这是语法错误，但是我不知道语法错误是什么.它告诉我的是:

It's a syntax error however I don't know what the syntax error is. All it tells me is:

1064-您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以使用正确的语法 靠近'WHILE(@daycount< @totaldays)DO IF(WEEKDAY(@checkweekday)< 6) 然后在第2行

1064 - 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 'WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN ' at line 2

我的语法错误如下:

    WHILE(@daycount < @totaldays) DO
          IF (WEEKDAY(@checkweekday) < 6) THEN

我的代码:

      SELECT MONTH(CURDATE()) INTO @curmonth;
      SELECT MONTHNAME(CURDATE()) INTO @curmonthname;
      SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;
      SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;
      SELECT DAY(@checkweekday) INTO @checkday;
      SET @daycount = 0;
      SET @workdays = 0;

    BEGIN
      WHILE(@daycount < @totaldays) DO
          IF (WEEKDAY(@checkweekday) < 6) THEN
            SET @workdays = @workdays+1;
          END IF;
          SET @daycount = @daycount+1;
          SELECT ADDDATE('@checkweekday', INTERVAL 1 DAY) INTO @checkweekday;
      END WHILE;
    END;
    SELECT @workdays;

有人可以协助吗?

更新 我在以下代码中收到相同的错误，因此可能与此有关:

UPDATE I receive the same error with the following bit of code so it probably has something to do with this:

    SET @workdays = 0;
    IF (WEEKDAY('2013-06-13') < 6) THEN
      SET @workdays = @workdays+1;
    END IF;
    SELECT @workdays;

推荐答案

我发现您不能在mysql中的存储过程之外使用条件.这就是语法错误的原因.一旦我将所需的代码放在两者之间

I have discovered that you cannot have conditionals outside of the stored procedure in mysql. This is why the syntax error. As soon as I put the code that I needed between

   BEGIN
   SELECT MONTH(CURDATE()) INTO @curmonth;
   SELECT MONTHNAME(CURDATE()) INTO @curmonthname;
   SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;
   SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;
   SELECT DAY(@checkweekday) INTO @checkday;
   SET @daycount = 0;
   SET @workdays = 0;

     WHILE(@daycount < @totaldays) DO
       IF (WEEKDAY(@checkweekday) < 5) THEN
         SET @workdays = @workdays+1;
       END IF;
       SET @daycount = @daycount+1;
       SELECT ADDDATE(@checkweekday, INTERVAL 1 DAY) INTO @checkweekday;
     END WHILE;
   END

仅供他人使用:

如果不确定如何在phpmyadmin中创建例程，可以将其放在SQL查询中

If you are not sure how to create a routine in phpmyadmin you can put this in the SQL query

    delimiter ;;
    drop procedure if exists test2;;
    create procedure test2()
    begin
    select ‘Hello World’;
    end
    ;;

运行查询.这将创建一个名为test2的存储过程或存储例程.现在转到例程"选项卡，然后将存储过程编辑为所需的内容.我还建议阅读 http://net.tutsplus.com/tutorials/如果您是从存储过程开始的，则介绍存储过程/.

Run the query. This will create a stored procedure or stored routine named test2. Now go to the routines tab and edit the stored procedure to be what you want. I also suggest reading http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/ if you are beginning with stored procedures.

您需要的first_day函数是: 如何在mysql?

The first_day function you need is: How to get first day of every corresponding month in mysql?

显示程序正在运行 只需在END WHILE下方和END上方添加以下行

Showing the Procedure is working Simply add the following line below END WHILE and above END

    SELECT @curmonth,@curmonthname,@totaldays,@daycount,@workdays,@checkweekday,@checkday;

然后在"SQL查询"窗口中使用以下代码.

Then use the following code in the SQL Query Window.

    call test2 /* or whatever you changed the name of the stored procedure to */

注意::如果您使用此代码，请记住，此代码未考虑全国范围内的假日(或与此有关的任何假日).

NOTE: If you use this please keep in mind that this code does not take in to account nationally observed holidays (or any holidays for that matter).

这篇关于当使用IF语句MYSQL时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！