08. 函数和流程控制
函数和流程控制
-- 临时把语句结束 ; 改为以 $$
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());
