08

use 20200198db;
set foreign_key_checks = 0;

set foreign_key_checks  = 1;

## To Print “Hello World!”
drop procedure if exists HelloWorld;
delimiter //
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT "Hello World!";
END; //
delimiter ;
CALL HelloWorld();

## To Print “Hello (with given name)” using function
drop function if exists HelloName;
CREATE FUNCTION HelloName(name VARCHAR(100))
RETURNS VARCHAR(120) DETERMINISTIC
RETURN CONCAT("Hello ", name);
SET @fn_res = HelloName("BPDC");

select @fn_res;

## function to compute square
drop function if exists compute_square_fn;
delimiter //
create function compute_square_fn(number int)
returns int
begin
return number * number;
end //
delimiter ;
select compute_square_fn(3);

## function to compute area of circle
drop function if exists compute_circle_area;
delimiter //
create function compute_circle_area(radius int)
returns float DETERMINISTIC
begin
return 3.14*radius * radius;
end
//
delimiter ;
select compute_circle_area(4);

## Control Statements
drop table if exists t;
create table t(s1 int);
INSERT INTO t VALUES (17);

drop procedure if exists p12;
delimiter //
CREATE PROCEDURE p12 (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);

## switch case
drop procedure if exists p13;
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 loop
drop procedure if exists p14;
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;

## do-while loop
drop procedure if exists p15;
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
drop procedure if exists p16;
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;

## display even-valued rows
SELECT * FROM t WHERE MOD(s1, 2) = 0;

## display odd-valued rows
SELECT * FROM t WHERE MOD(s1, 2) != 0;

## Use “CASE” to insert or update
drop procedure if exists assignment;
delimiter //
CREATE PROCEDURE assignment (IN parameter1 INT, in parameter2 INT)
BEGIN
CASE parameter2
WHEN 1 THEN
INSERT INTO t VALUES (parameter1/parameter2);
WHEN 2 THEN
INSERT INTO t VALUES (parameter1/parameter2);
ELSE
INSERT INTO t VALUES (parameter1);

END CASE;
END; //
delimiter ;
CALL assignment(10, 0);
CALL assignment(10, 1);
CALL assignment(10, 2);
select * from t;