MySQL CONTROL STATEMENTS
----------------------------------------------------------------
----------- To Print “Hello World!” using stored
procedure--------------------------------------------------------
--------------
delimiter //
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT "Hello World!";
END; //
delimiter ;
CALL HelloWorld();
----------------------------------------------------------------
--- To Print “Hello (with given name)” using stored procedure---
----------------------------------------------------------------
CREATE FUNCTION HelloName(name VARCHAR(100))
RETURNS VARCHAR(120) DETERMINISTIC
RETURN CONCAT("Hello ", name);
SET @fn_res = HelloName("Hello!!!!!");
select @fn_res;
----------------------------------------------------------------
create table t(s1 int);
INSERT INTO t VALUES (17);
----------------------------------------------------------------
------------------ MySQL CONTROL STATEMENTS
-----------------------------------------------------------------
-----------------
------------------------Example for IF – ELSE
------------------------
delimiter //
CREATE PROCEDURE p112 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1 where s1 = 17;
ELSE
UPDATE t SET s1 = s1 + 2 where s1 = 17;
END IF;
END; //
delimiter ;
CALL p12(1);
-----------------------------------------------------------------
-----
------------------------Example for CASE (switch case)
---------------
delimiter //
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN
INSERT INTO t VALUES (17);
WHEN 1 THEN
INSERT INTO t VALUES (18);
ELSE
INSERT INTO t VALUES (19);
END CASE;
END; //
delimiter ;
CALL p13(0);
select * from t;
----------------------------------------------------------------
-----------------WHILE ... END WHILE
-----------------------------------------------------------------
-------------------------
delimiter //
CREATE PROCEDURE p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //
delimiter ;
CALL p14();
select * from t;
-----------------------------------------------------------------
-----
// Here's another way of doing a loop, the REPEAT loop.
It's doing the same thing as the WHILE loop did, except that it
checks the condition after doing the statements,
instead of checking the condition before doing the statements, as
the WHILE loop did.
-----------------------------------------------------------------
----------------- REPEAT ... END REPEAT ------------------
----------------------------------------------------------------
delimiter //
CREATE PROCEDURE p15 ()
BEGIN
DECLARE v INT;
SET v = 5;
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 10
END REPEAT;
END; //
delimiter ;
CALL p15();
select * from t;
-----------------------------------------------------------------
------------------ LOOP ... END LOOP ------------------------
delimiter //
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 10;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 15 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
delimiter ;
CALL p16();
select * from t;
-----------------------------------------------------------------
-----------------------------------------------------------------
---------
-- To display the even-numbered rows.
SELECT * FROM t WHERE MOD(s1, 2) = 0;
----------------------------------------------------------------
Questions:
1. Display the odd-numbered film_id rows from the film of the
sakila database.
2. Use “CASE” to insert or update one row in the film table(For
Ex. choice 1 for INSERT & Choice 2 for UPDATE).
