Mei_R - 10 months ago 64

MySQL Question

i'm having hard time with my homework , because im beginner using mysql procedure.

to search total numbers odd and even between 20-50 with looping.

i want to make output like this using mysql procedure to call:

total_odd_numbers :15

total_even_numbers:16

i tried for sum up for even numbers like this :

``DELIMITER $$`

CREATE DEFINER=`root`@`localhost` PROCEDURE `genap`()

BEGIN

DECLARE x INT;

DECLARE str VARCHAR(255);

declare total int;

SET x = 20;

SET str = '';

set total = 0;

loop_label: LOOP

IF x = 50 THEN

LEAVE loop_label;

END IF;

SET x =x+1;

IF (x mod 2) THEN

ITERATE loop_label;

ELSE

SET str = CONCAT(str,x,',');

SET total= total+x;

END IF;

END LOOP;

SELECT sum(total);

END`

Answer Source

Try this procedure:

```
DROP PROCEDURE IF EXISTS ShowOddEvesBetween;
DELIMITER ;;
CREATE PROCEDURE ShowOddEvesBetween(IN fromNum INT, IN toNum INT)
BEGIN
DECLARE odds INT DEFAULT 0;
DECLARE evens INT DEFAULT 0;
DECLARE x INT;
SET x = fromNum;
WHILE x <= toNum DO
IF x % 2 THEN
SET odds = odds + 1;
ELSE
SET evens = evens + 1;
END IF;
SET x = x + 1;
END WHILE;
SELECT CONCAT("total_odd_numbers: ", odds, ", total_even_numbers: ", evens) AS "Odds & Evens";
END;
;;
```

Then call it

```
CALL ShowOddEvesBetween(20, 50);
```

Will output what you want

```
MariaDB [test]> CALL ShowOddEvesBetween(20, 50);
+-----------------------------------------------+
| Odds & Evens |
+-----------------------------------------------+
| total_odd_numbers: 15, total_even_numbers: 16 |
+-----------------------------------------------+
1 row in set (0.00 sec)
```