變量
0.準(zhǔn)備工作
CREATE DATABASE test16_var_cur;
use test16_var_cur;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
無參有返回
1. 創(chuàng)建函數(shù)get_count(),返回公司的員工個(gè)數(shù)
DELIMITER //
CREATE FUNCTION get_count() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定義局部變量
SELECT COUNT(*) INTO c#賦值
FROM employees;
RETURN c;
END //
DELIMITER ;
#調(diào)用
SELECT get_count();
有參有返回
2. 創(chuàng)建函數(shù)ename_salary(),根據(jù)員工姓名，返回它的工資
DELIMITER //
CREATE FUNCTION ename_salary(emp_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
SET @sal=0;#定義用戶變量
SELECT salary INTO @sal #賦值
FROM employees
WHERE last_name = emp_name;
RETURN @sal;
END //
DELIMITER ;
#調(diào)用
SELECT ename_salary('Abel');
#3. 創(chuàng)建函數(shù)dept_sal() ,根據(jù)部門名，返回該部門的平均工資
DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
DECLARE avg_sal DOUBLE ;
SELECT AVG(salary) INTO avg_sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=dept_name;
RETURN avg_sal;
END //
DELIMITER ;
#調(diào)用
SELECT dept_sal('Marketing');
4. 創(chuàng)建函數(shù)add_float()，實(shí)現(xiàn)傳入兩個(gè)float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT)
RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT;
SET SUM=value1+value2;
RETURN SUM;
END //
DELIMITER ;
#調(diào)用
SET @v1 := 12.2;
SET @v2 = 2.3;
SELECT add_float(@v1,@v2);
流程控制
題目文章來源：http://www.zghlxwxcb.cn/news/detail-803034.html
- 創(chuàng)建函數(shù)test_if_case()，實(shí)現(xiàn)傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回
C，否則返回D
#要求：分別使用if結(jié)構(gòu)和case結(jié)構(gòu)實(shí)現(xiàn) - 創(chuàng)建存儲(chǔ)過程test_if_pro()，傳入工資值，如果工資值<3000,則刪除工資為此值的員工，如果3000 <= 工
資值 <= 5000,則修改此工資值的員工薪資漲1000，否則漲工資500 - 創(chuàng)建存儲(chǔ)過程insert_data(),傳入?yún)?shù)為 IN 的 INT 類型變量 insert_count,實(shí)現(xiàn)向admin表中批量插
入insert_count條記錄
準(zhǔn)備工作
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(25) NOT NULL,
user_pwd VARCHAR(35) NOT NULL
);
SELECT * FROM admin;
1. 創(chuàng)建函數(shù)test_if_case()，實(shí)現(xiàn)傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回
C，否則返回D
要求：分別使用if結(jié)構(gòu)和case結(jié)構(gòu)實(shí)現(xiàn)
#方式1：
DELIMITER //
CREATE FUNCTION test_if_case1(score DOUBLE)
RETURNS CHAR
BEGIN
DECLARE ch CHAR;
IF score>90
THEN SET ch='A';
ELSEIF score>80
THEN SET ch='B';
ELSEIF score>60
THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END //
DELIMITER ;
#調(diào)用
SELECT test_if_case1(87);
#方式2：
DELIMITER //
CREATE FUNCTION test_if_case2(score DOUBLE)
RETURNS CHAR
BEGIN
DECLARE ch CHAR;
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END //
DELIMITER ;
#調(diào)用
SELECT test_if_case2(67);
2. 創(chuàng)建存儲(chǔ)過程test_if_pro()，傳入工資值，如果工資值<3000,則刪除工資為此值的員工，如果3000 <= 工
資值 <= 5000,則修改此工資值的員工薪資漲1000，否則漲工資500
DELIMITER //
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<3000
THEN DELETE FROM employees WHERE salary = sal;
ELSEIF sal <= 5000
THEN UPDATE employees SET salary = salary+1000 WHERE salary = sal;
ELSE
UPDATE employees SET salary = salary+500 WHERE salary = sal;
END IF;
END //
DELIMITER ;
SELECT * FROM employees;
#調(diào)用
CALL test_if_pro(3100);
3. 創(chuàng)建存儲(chǔ)過程insert_data(),傳入?yún)?shù)為 IN 的 INT 類型變量 insert_count,實(shí)現(xiàn)向admin表中批量插
入insert_count條記錄
DELIMITER //
CREATE PROCEDURE insert_data(IN insert_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= insert_count DO
INSERT INTO admin(user_name,user_pwd) VALUES(CONCAT('Rose-',i),ROUND(RAND() *
100000));
SET i=i+1;
END WHILE;
END //
DELIMITER ;
#調(diào)用
CALL insert_data(100);
游標(biāo)的使用
創(chuàng)建存儲(chǔ)過程update_salary()，參數(shù)1為 IN 的INT型變量dept_id，表示部門id；參數(shù)2為 IN的INT型變量
change_sal_count，表示要調(diào)整薪資的員工個(gè)數(shù)。查詢指定id部門的員工信息，按照salary升序排列，根
據(jù)hire_date的情況，調(diào)整前change_sal_count個(gè)員工的薪資，詳情如下。文章來源地址http://www.zghlxwxcb.cn/news/detail-803034.html
| hire_date | salary |
|---|---|
| hire_date < 1995 | salary = salary*1.2 |
| hire_date >=1995 and hire_date <= 1998 | salary =salary*1.15 |
| hire_date > 1998 and hire_date <= 2001 | salary = salary *1.10 |
| hire_date > 2001 | salary = salary * 1.05 |
DELIMITER //
CREATE PROCEDURE update_salary(IN dept_id INT,IN change_sal_count INT)
BEGIN
#聲明變量
DECLARE int_count INT DEFAULT 0;
DECLARE salary_rate DOUBLE DEFAULT 0.0;
DECLARE emp_id INT;
DECLARE emp_hire_date DATE;
#聲明游標(biāo)
DECLARE emp_cursor CURSOR FOR SELECT employee_id,hire_date FROM employees
WHERE department_id = dept_id ORDER BY salary ;
#打開游標(biāo)
OPEN emp_cursor;
WHILE int_count < change_sal_count DO
#使用游標(biāo)
FETCH emp_cursor INTO emp_id,emp_hire_date;
IF(YEAR(emp_hire_date) < 1995)
THEN SET salary_rate = 1.2;
ELSEIF(YEAR(emp_hire_date) <= 1998)
THEN SET salary_rate = 1.15;
ELSEIF(YEAR(emp_hire_date) <= 2001)
THEN SET salary_rate = 1.10;
ELSE SET salary_rate = 1.05;
END IF;
#更新工資
UPDATE employees SET salary = salary * salary_rate
WHERE employee_id = emp_id;
#迭代條件
SET int_count = int_count + 1;
END WHILE;
#關(guān)閉游標(biāo)
CLOSE emp_cursor;
END //
DELIMITER ;
# 調(diào)用
CALL update_salary(50,2);
到了這里，關(guān)于變量、流程控制、游標(biāo)-練習(xí)題的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
