MySQL Functions

Creating Database and Department Table

USE UNIVERSITY_DB;
CREATE TABLE departments(
    dept_name VARCHAR(20),
    budget BIGINT(20),
    building VARCHAR(20)
);

INSERT INTO departments VALUES('Electrical Engineering',15000,'Building A');
INSERT INTO departments VALUES('Telecommunications',45000,'Building B');
INSERT INTO departments VALUES('Computer Science',120000,'Building C');

Creating Instructor Table

CREATE TABLE instructors(
    employee_id INT,
    salary INT,
    department_name VARCHAR(20),
    FOREIGN KEY(department_name) REFERENCES departments(dept_name)
) ENGINE=innodb DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Note: When creating tables with foreign keys, the referenced column must be a primary key. Modify the department table:

ALTER TABLE departments MODIFY dept_name VARCHAR(20) PRIMARY KEY;

Inserting Data into Instructor Table

INSERT INTO instructors VALUES(1,1200,'Electrical Engineering');
INSERT INTO instructors VALUES(2,1200,'Electrical Engineering');
INSERT INTO instructors VALUES(3,1200,'Electrical Engineering');
INSERT INTO instructors VALUES(4,1200,'Electrical Engineering');
INSERT INTO instructors VALUES(5,1200,'Electrical Engineering');
INSERT INTO instructors VALUES(1,1300,'Telecommunications');
INSERT INTO instructors VALUES(2,1300,'Telecommunications');
INSERT INTO instructors VALUES(3,1300,'Telecommunications');
INSERT INTO instructors VALUES(1,1400,'Computer Science');

Creating Functions

If you encounter the error "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration", set:

SET GLOBAL log_bin_trust_function_creators=TRUE;

Creating a Parameterized Function

MySQL uses semicolon as the default statement delimiter. Use DELIMITER to change it:

DELIMITER //
CREATE FUNCTION count_department_members(deptName VARCHAR(20))
    RETURNS INTEGER
    BEGIN
    RETURN(
        SELECT COUNT(*)
        FROM instructors
        WHERE instructors.department_name = deptName);
    END//
DELIMITER ;

Using the Function

SELECT * FROM departments WHERE count_department_members(dept_name)>2;

Using Variables in Functions

DELIMITER //
CREATE FUNCTION count_department_members(deptName VARCHAR(20))
    RETURNS INTEGER
    BEGIN
    DECLARE member_count INTEGER;
        SELECT COUNT(*) INTO member_count
        FROM instructors
        WHERE instructors.department_name = deptName;
    RETURN member_count;
    END//
DELIMITER ;

Creating a Parameterless Function

CREATE FUNCTION getDefaultDepartment() RETURNS VARCHAR(20) RETURN "Electrical Engineering";
SELECT * FROM departments WHERE dept_name=getDefaultDepartment();

MySQL Procedures

Creating Parameterless Procedures

DROP PROCEDURE IF EXISTS display_count;
CREATE PROCEDURE display_count()
    SELECT 5;

CALL display_count();

DROP PROCEDURE IF EXISTS show_all_instructors;
CREATE PROCEDURE show_all_instructors()
    SELECT * FROM instructors;

CALL show_all_instructors();

Creating Procedures with Input and Output Parameters

DELIMITER //
DROP PROCEDURE IF EXISTS process_employee;
CREATE PROCEDURE process_employee(
    IN input_id INT, 
    OUT total_count INT
)
BEGIN
    DECLARE temp_id INT;
    
    IF input_id=10 THEN
        SET temp_id=input_id;
    ELSE
        SET temp_id=20;
    END IF;
    
    INSERT INTO instructors(employee_id) VALUES(temp_id);
    SELECT COUNT(*) INTO total_count FROM instructors;
END//
DELIMITER ;

CALL process_employee(10, @result);
SELECT @result;

Procedures Without Output Parameters

DROP PROCEDURE IF EXISTS show_department_members;
CREATE PROCEDURE show_department_members(dept VARCHAR(20))
    SELECT * FROM instructors WHERE department_name=dept;

CALL show_department_members('Electrical Engineering');

Control Flow in Procedures

Creating a Salary Table

CREATE TABLE salary_records(employee_name VARCHAR(20), salary_amount INT(11));
INSERT INTO salary_records VALUES('John',2000);
INSERT INTO salary_records VALUES('Sarah',2500);
INSERT INTO salary_records VALUES('Mike',3000);

Using WHILE Loop

DELIMITER //
DROP PROCEDURE IF EXISTS insert_multiple_salaries;
CREATE PROCEDURE insert_multiple_salaries()
    BEGIN
    DECLARE counter INT DEFAULT 0;
    WHILE counter<10 DO
        INSERT INTO salary_records VALUES('Mike',100); 
        SET counter=counter+1;
    END WHILE;
    END//
DELIMITER ;

CALL insert_multiple_salaries();
SELECT * FROM salary_records;

Using REPEAT Loop

DELIMITER //
DROP PROCEDURE IF EXISTS insert_salaries_repeat;
CREATE PROCEDURE insert_salaries_repeat()
    BEGIN
    DECLARE counter INT DEFAULT 0;
    REPEAT
        INSERT INTO salary_records VALUES('Sarah',100); 
        SET counter=counter+1;
    UNTIL counter>5
    END REPEAT;
    END//
DELIMITER ;

Using LOOP Statement

DELIMITER //
DROP PROCEDURE IF EXISTS process_salary_data;
CREATE PROCEDURE process_salary_data()
    BEGIN 
    DECLARE counter INT DEFAULT 0;
    salary_loop: LOOP
        IF counter>6 THEN
            LEAVE salary_loop;
        END IF;
        INSERT INTO salary_records VALUES('John',300);
        SET counter=counter+1;
    END LOOP;
    END//
DELIMITER ;

Using Cursors

Adding Auto-increment Field

ALTER TABLE salary_records ADD record_id INT AUTO_INCREMENT UNIQUE;

Procedure with Cursor

DELIMITER //
DROP PROCEDURE IF EXISTS adjust_low_salaries;
CREATE PROCEDURE adjust_low_salaries()
BEGIN
    DECLARE current_salary INT;
    DECLARE current_id INT;
    DECLARE total_sum INT DEFAULT 0;
    DECLARE finished INT DEFAULT FALSE;
    
    DECLARE salary_cursor CURSOR FOR 
        SELECT salary_amount, record_id FROM salary_records WHERE salary_amount<2700;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
    
    SET total_sum = 0;
    OPEN salary_cursor;
    
    process_loop: LOOP
        FETCH salary_cursor INTO current_salary, current_id;
        IF finished THEN
            LEAVE process_loop;
        END IF;
        
        UPDATE salary_records SET salary_amount=salary_amount+100 WHERE record_id=current_id;
        SET total_sum = total_sum + current_salary;
    END LOOP;
    
    CLOSE salary_cursor;
    SELECT total_sum;
END//
DELIMITER ;

CALL adjust_low_salaries();

MySQL Triggers

Insert Trigger

DROP TRIGGER IF EXISTS before_salary_insert;

CREATE TRIGGER before_salary_insert BEFORE INSERT ON salary_records
FOR EACH ROW
BEGIN
    IF new.salary_amount > 1000 THEN
        SET new.salary_amount = new.salary_amount - 200;
    END IF;
END;

INSERT INTO salary_records(employee_name, salary_amount) VALUES('Emma',1500);
SELECT * FROM salary_records;

Update Trigger

DROP TRIGGER IF EXISTS before_salary_update;

CREATE TRIGGER before_salary_update BEFORE UPDATE ON salary_records
FOR EACH ROW
BEGIN
    IF new.salary_amount > 4000 THEN
        SET new.salary_amount = old.salary_amount;
    END IF;
END;

UPDATE salary_records SET salary_amount=7000 WHERE record_id=28;
SELECT * FROM salary_records WHERE record_id=28;

MySQL Events

Scheduled Task Execution

CREATE EVENT weekly_report ON SCHEDULE EVERY 1 WEEK
    DO CALL generate_weekly_report('summary');

SQL and Shell Script Integration

Executing MySQL Commands from Shell

#!/bin/bash
# Shell script to query MySQL and process results

DB_USER="username"
DB_PASS="password"
DB_NAME="university_db"

# Execute SQL query and store results
result=$(mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "SELECT COUNT(*) FROM instructors WHERE department_name='Computer Science';")

# Process the result
count=$(echo $result | awk '{print $2}')
echo "Number of Computer Science instructors: $count"

# Conditional action based on result
if [ $count -gt 5 ]; then
    echo "Notification: Computer Science department has more than 5 instructors"
fi

Calling Shell Scripts from MySQL

DROP PROCEDURE IF EXISTS execute_shell_command;
DELIMITER //
CREATE PROCEDURE execute_shell_command(IN shell_command TEXT)
BEGIN
    SET @shell_result = sys_exec(shell_command);
    SELECT @shell_result AS execution_result;
END//
DELIMITER ;

-- Example usage
CALL execute_shell_command('ls -la /var/log/mysql/');

Trigger with Shell Integration

DROP TRIGGER IF EXISTS after_employee_insert;
CREATE TRIGGER after_employee_insert AFTER INSERT ON instructors
FOR EACH ROW
BEGIN
    -- Call shell script to send welcome email
    SET @shell_cmd = CONCAT('/usr/bin/send_welcome_email.sh ', NEW.employee_id);
    SET @result = sys_exec(@shell_cmd);
END;

Using UNION in Loops

DELIMITER //
DROP PROCEDURE IF EXISTS generate_union_report;
DROP TEMPORARY TABLE IF EXISTS temp_records;
CREATE TEMPORARY TABLE temp_records AS SELECT * FROM salary_records WHERE record_id<3;

CREATE PROCEDURE generate_union_report(iterations INT)
BEGIN
    DECLARE counter INT DEFAULT iterations;
    WHILE counter>0 DO
        SET counter=counter-1;
        SELECT * FROM ((SELECT * FROM temp_records) UNION (SELECT * FROM salary_records WHERE record_id<(10+counter))) AS combined_results;
    END WHILE;
END//
DELIMITER ;

CALL generate_union_report(3);