MySQL笔记： 函数和流程控制

函数和流程控制

-- 临时把语句结束 ; 改为以 $$
DELIMITER $$
SELECT * FROM student$$
DELIMITER ; -- 改回来
-- 函数格式
-- DELIMITER $$
-- CREATE FUNCTION 函数名(参数1 参数类型,...) RETURNS 函数返回值类型
-- 函数描述
-- BEGIN
--     函数体
-- END;
-- $$
-- DELIMITER ;
-- 函数描述
-- contains sql: 不包含读写语句
-- no sql: 函数体不包含SQL查询语句
-- reads sql data:
-- modifies sql data:
-- sql security:
-- definer:
-- invoker:
-- comment:

DELIMITER $$
CREATE FUNCTION row_no_fn() RETURNS INT
NO SQL
BEGIN
    SET @row_no = @row_no + 1;
    RETURN @row_no;
END;
$$
DELIMITER ;
SET @row_no = 0;
SELECT row_no_fn() AS '行号',student_no,student_name FROM student;

-- 查询学生选择的课程编号
-- 传入的学生学号,返回的是选择的科目数量
DROP FUNCTION get_choose_number_fn;
DELIMITER $$
CREATE FUNCTION get_choose_number_fn(student_no1 VARCHAR(10)) RETURNS INT
READS SQL DATA
BEGIN
    DECLARE choose_number INT;
    SELECT COUNT(course_no) INTO choose_number FROM choose WHERE student_no = student_no1;
    RETURN choose_number;
END;
$$
DELIMITER ;
SELECT get_choose_number_fn('2012003') AS '选择了几门?';
SELECT NAME FROM mysql.proc WHERE db = 'choose' AND TYPE = 'function';
SHOW CREATE FUNCTION row_no_fn; -- 查看已定义的函数

流程控制

-- 1. if语句
-- if 条件 then
--     语句1
-- elseif 条件2 then
--     语句2
-- end if
-- 根据编号及角色得到名字
DROP FUNCTION get_name_fn;
DELIMITER $$
CREATE FUNCTION get_name_fn(number VARCHAR(10),role VARCHAR(10)) RETURNS VARCHAR(30)
READS SQL DATA
BEGIN
    DECLARE tmpName VARCHAR(30); -- 局部变量
    IF (role = 'student') THEN
        SELECT student_name INTO tmpName FROM student WHERE student_no = number;
    ELSEIF (role = 'teacher') THEN
        SELECT teacher_name INTO tmpName FROM teacher WHERE teacher_no = number;
    ELSE 
        SET tmpName = '输入有误'; 
    END IF;
    RETURN tmpName;
END;
$$
DELIMITER ;
SELECT get_name_fn('2012001','student'),get_name_fn('001','teacher'),get_name_fn('001','s');
-- 2. CASE 语句
-- case 判断的变量
--     when 条件值 then 语句1
--     WHEN 条件值2 THEN 语句2
-- end case;

DROP FUNCTION get_week_fn;
DELIMITER $$
CREATE FUNCTION get_week_fn(week_no INT) RETURNS VARCHAR(20)
NO SQL
BEGIN
    DECLARE tmpName VARCHAR(20); -- 局部变量
    CASE week_no
    WHEN 0 THEN SET tmpName = '星期一';
    WHEN 1 THEN SET tmpName = '星期二';
    WHEN 2 THEN SET tmpName = '星期三';
    WHEN 3 THEN SET tmpName = '星期四';
    WHEN 4 THEN SET tmpName = '星期五';
    ELSE SET tmpName = '今天休息';
    END CASE;
    RETURN tmpName;
END;
$$
DELIMITER ;
SELECT NOW(),get_week_fn(WEEKDAY(NOW()));

-- 循环语句
-- while 条件 DO
--     循环体
-- End While;

-- labelA:while 条件 DO
--     循环体
--      leave labelA; 跳出循环
-- End While;

-- repeat 
--    循环体
--     until 条件
-- end repeat

-- labelB:loop
--    循环体
--      if 条件 then
--         leave  labelB;
--      end if;
-- end loop


DELIMITER $$
CREATE FUNCTION get_sum1_fn(n INT) RETURNS INT
NO SQL
BEGIN
    DECLARE SUM INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        SET SUM = SUM + i;
        SET i= i + 1;
    END WHILE;
    RETURN SUM;
END;
$$
DELIMITER ;

SELECT get_sum1_fn(100);

DELIMITER $$
CREATE FUNCTION get_sum2_fn(n INT) RETURNS INT
NO SQL
BEGIN
    DECLARE SUM INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    A:WHILE TRUE DO
        SET SUM = SUM + i;
        SET i= i + 1;
        IF i = 101 THEN
            LEAVE A; -- 跳出
        END IF;
    END WHILE;
    RETURN SUM;
END;
$$
DELIMITER ;

SELECT get_sum2_fn(100);


CREATE TABLE testTable
(a INT);

DROP FUNCTION insert_100_testdata_fn;
DELIMITER $$
CREATE FUNCTION insert_100_testdata_fn() RETURNS BOOL
BEGIN
    DECLARE i INT DEFAULT 1;
    A:WHILE TRUE DO
        
        INSERT INTO testTable VALUES(i);        
        SET i= i + 1;
        IF i = 101 THEN
            LEAVE A; -- 跳出 相当于break
            -- iterate A; -- 相当continue
        END IF;
    END WHILE;
    RETURN TRUE;
END;
$$
DELIMITER ;

SELECT insert_100_testdata_fn();
SELECT COUNT(*) FROM testTable;

DELIMITER $$
CREATE FUNCTION get_sum4_fn(n INT) RETURNS INT
NO SQL
BEGIN
    DECLARE SUM INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    REPEAT
        SET SUM = SUM + i;
        SET i= i + 1;
        UNTIL i=101
    END REPEAT;
    
    RETURN SUM;
END;
$$
DELIMITER ;

SELECT get_sum4_fn(100);

上面程序的数据创建代码：

CREATE DATABASE choose;
USE choose;

DROP TABLE choose;
DROP TABLE student;
DROP TABLE course;
DROP TABLE classes;
DROP TABLE teacher;


CREATE TABLE teacher(
    teacher_no VARCHAR(10) PRIMARY KEY,
    teacher_name VARCHAR(30) NOT NULL,
    teacher_contact VARCHAR(30)
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE classes(
    class_no VARCHAR(10) PRIMARY KEY,
    class_name VARCHAR(30) NOT NULL UNIQUE,
    department_name VARCHAR(30) NOT NULL
    ) ENGINE = INNODB DEFAULT CHARSET=utf8;

ALTER TABLE classes CHANGE deaprtment_name department_name VARCHAR(30);
CREATE TABLE course(
    course_no VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(30) NOT NULL,
    up_limit INT DEFAULT 60,
    description TEXT,
    STATUS VARCHAR(6) DEFAULT '未审核',
    teacher_no VARCHAR(10) NOT NULL UNIQUE,
    CONSTRAINT course_teacher_fk FOREIGN KEY(teacher_no) REFERENCES teacher(teacher_no)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE student(
    student_no VARCHAR(10) PRIMARY KEY,
    student_name VARCHAR(30) NOT NULL,
    student_contact VARCHAR(30),
    class_no VARCHAR(10),
    CONSTRAINT student_class_fk FOREIGN KEY(class_no) REFERENCES classes(class_no)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE choose(
    choose_no INT AUTO_INCREMENT PRIMARY KEY,
    student_no VARCHAR(10) NOT NULL,
    course_no VARCHAR(10) NOT NULL,
    score TINYINT UNSIGNED,
    choose_time DATETIME,
    CONSTRAINT choose_student_fk FOREIGN KEY(student_no) REFERENCES student(student_no),
    CONSTRAINT choose_course_fk FOREIGN KEY(course_no) REFERENCES course(course_no)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;
    
INSERT INTO teacher VALUES('001','张老师','11000000000');
INSERT INTO teacher VALUES('002','李老师','12000000000');
INSERT INTO teacher VALUES('003','王老师','13000000000');

INSERT INTO classes(class_no,class_name,department_name) VALUES('1','2012自动化1班', '机电工程');
INSERT INTO classes(class_no,class_name,department_name) VALUES('2','2012自动化2班', '机电工程');
INSERT INTO classes(class_no,class_name,department_name) VALUES('3','2012自动化3班', '机电工程');

INSERT INTO course VALUES('1','java语言程序设计',DEFAULT,'暂无','已审核','001');
INSERT INTO course VALUES('2','MySQL数据库',150,'暂无','已审核','002');
INSERT INTO course VALUES('3','c语言程序设计',230,'暂无','已审核','003');

INSERT INTO student VALUES
('2012001','张三','15000000000',1),
('2012002','李四','16000000000',1),
('2012003','王五','17000000000',3),
('2012004','马六','18000000000',2),
('2012005','田七','19000000000',2);

INSERT INTO choose VALUES
(NULL,'2012001',2,40,NOW()),
(NULL,'2012001',1,50,NOW()),
(NULL,'2012002',3,60,NOW()),
(NULL,'2012002',2,70,NOW()),
(NULL,'2012003',1,80,NOW()),
(NULL,'2012004',2,90,NOW()),
(NULL,'2012005',3,NULL,NOW()),
(NULL,'2012005',1,NULL,NOW());