Duration: 4 hours (Lecture: 2 hours, Lab: 4 hours)

Syllabus Topics:

• Lecture:
  • Loop Constructs (ITERATE, LEAVE)
  • Functions with and without Parameters
  • MySQL Built-in Functions (String, Numeric, Date, etc.)
• Lab (4 hours):
  • Creating Function and Returning Value from It
  • Use of Built-in Functions in Queries

Lecture Notes (In-Depth)

1. Loop Constructs (ITERATE, LEAVE)

MySQL provides loop constructs to control the flow within looping structures (LOOP, WHILE, REPEAT) in stored procedures and functions.

• ITERATE:

  • Skips the current iteration of a loop and starts the next iteration.

  • Similar to continue in programming languages like C or Java.

  • Syntax: ITERATE label;

  • Example: In a LOOP, skip even numbers when inserting into a table.

    DELIMITER //
    CREATE PROCEDURE InsertOddNumbers()
    BEGIN
        DECLARE num INT DEFAULT 1;
        my_loop: LOOP
            IF num % 2 = 0 THEN
                SET num = num + 1;
                ITERATE my_loop; -- Skip even numbers
            END IF;
            INSERT INTO numbers (value) VALUES (num);
            SET num = num + 1;
            IF num > 10 THEN
                LEAVE my_loop;
            END IF;
        END LOOP my_loop;
    END //
    DELIMITER ;
    
    

  • Explanation: If num is even, ITERATE skips the INSERT and moves to the next iteration.

• LEAVE:
  • Exits a loop entirely, terminating its execution.
  • Similar to break in programming languages.
  • Syntax: LEAVE label;
  • Example: Stop inserting numbers after reaching 10 (already shown in the above example).
  • Key Notes:
    • Both ITERATE and LEAVE require a loop label when used in LOOP, WHILE, or REPEAT.
    • Essential for controlling complex loops, especially when processing rows or handling conditions.

2. Functions with and without Parameters

• Stored Functions:

  • Similar to stored procedures but return a single value and are used in SQL expressions (e.g., SELECT, WHERE).

  • Cannot modify database state (e.g., no INSERT, UPDATE, DELETE in non-deterministic functions).

  • Syntax:

    CREATE FUNCTION function_name ([parameters]) RETURNS datatype
    [DETERMINISTIC | NOT DETERMINISTIC]
    BEGIN
        -- Logic
        RETURN value;
    END;
    
    

  • DETERMINISTIC: Same input always produces the same output (e.g., a calculation).

  • NOT DETERMINISTIC: Output may vary (e.g., depends on current time or database state).

• Function without Parameters:

  • Returns a fixed or calculated value based on database state or constants.

  • Example: Return the total number of employees.

    DELIMITER //
    CREATE FUNCTION GetEmployeeCount() RETURNS INT
    DETERMINISTIC
    BEGIN
        DECLARE emp_count INT;
        SELECT COUNT(*) INTO emp_count FROM employees;
        RETURN emp_count;
    END //
    DELIMITER ;
    
    

    • Usage: SELECT GetEmployeeCount();
• Function with Parameters:

  • Accepts input parameters to compute a result dynamically.

  • Example: Calculate tax based on salary.

    DELIMITER //
    CREATE FUNCTION CalculateTax(salary DECIMAL(10,2)) RETURNS DECIMAL(10,2)
    DETERMINISTIC
    BEGIN
        DECLARE tax DECIMAL(10,2);
        SET tax = salary * 0.2; -- 20% tax rate
        RETURN tax;
    END //
    DELIMITER ;
    
    

    • Usage: SELECT name, CalculateTax(salary) AS tax FROM employees;
• Key Notes:
  • Functions must specify a RETURNS data type.
  • Parameters are implicitly IN (no OUT or INOUT in functions).
  • Use DROP FUNCTION function_name; to delete a function.

3. MySQL Built-in Functions (String, Numeric, Date, etc.)

MySQL provides a wide range of built-in functions for data manipulation and calculations.

• String Functions:
  • CONCAT(str1, str2, ...): Concatenates strings.
    • Example: SELECT CONCAT(name, ' - ', dept_id) AS emp_info FROM employees;
  • UPPER(str), LOWER(str): Converts string to uppercase or lowercase.
    • Example: SELECT UPPER(name) FROM employees;
  • SUBSTRING(str, start, length): Extracts a substring.
    • Example: SELECT SUBSTRING(name, 1, 3) FROM employees;
  • LENGTH(str): Returns the length of a string.
    • Example: SELECT name, LENGTH(name) FROM employees;
  • TRIM(str): Removes leading/trailing spaces.
    • Example: SELECT TRIM(' test '); -- Returns 'test'
• Numeric Functions:
  • ROUND(num, decimals): Rounds a number to specified decimals.
    • Example: SELECT ROUND(salary, 0) FROM employees;
  • CEIL(num), FLOOR(num): Returns the ceiling or floor of a number.
    • Example: SELECT CEIL(45.3), FLOOR(45.7); -- Returns 46, 45
  • ABS(num): Returns the absolute value.
    • Example: SELECT ABS(-100); -- Returns 100
  • RAND(): Returns a random number between 0 and 1.
    • Example: SELECT RAND();
• Date Functions:
  • NOW(), CURRENT_TIMESTAMP: Returns current date and time.
    • Example: SELECT NOW();
  • DATE_FORMAT(date, format): Formats a date (e.g., %Y-%m-%d).
    • Example: SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
  • DATEDIFF(date1, date2): Returns the number of days between two dates.
    • Example: SELECT DATEDIFF('2025-07-16', '2025-01-01'); -- Returns 196
  • DATE_ADD(date, INTERVAL value unit): Adds time to a date.
    • Example: SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);