08. 函数和流程控制
生活随笔
收集整理的這篇文章主要介紹了
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 DOINSERT 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());轉載于:https://www.cnblogs.com/bchen/p/7426298.html
總結
以上是生活随笔為你收集整理的08. 函数和流程控制的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： [ 测试管理 ] 如何描述缺陷报告？
- 下一篇： 【9018题解】2109 卡德加的兔子