MySQL第16章_变量、流程控制与游标
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SHOW VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'admin_%';
SHOW VARIABLES LIKE 'character_%';
SELECT @@global.max_connections;
SELECT @@global.character_set_client;
SELECT @@global.pseudo_thread_id;
SELECT @@session.max_connections;
SELECT @@session.character_set_client;
SELECT @@session.pseudo_thread_id;
SELECT @@character_set_client;
SET @@global.max_connections = 161;
SET GLOBAL max_connections = 171;
SET @@session.character_set_client = 'gbk';
SET SESSION character_set_client = 'gbk';
CREATE DATABASE dbtest16;
USE dbtest16;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
SELECT * FROM employees;
SELECT * FROM departments;
SET @m1 = 1;
SET @m2 := 2;
SET @sum := @m1 + @m2;
SELECT @sum;
SELECT @count := COUNT(*) FROM employees;
SELECT @count;
SELECT AVG(salary) INTO @avg_sal FROM employees;
SELECT @avg_sal;
DELIMITER
CREATE PROCEDURE test_var()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b INT ;
DECLARE emp_name VARCHAR(25);
SET a = 1;
SET b := 2;
SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
SELECT a,b,emp_name;
END
DELIMITER ;
CALL test_var();
DELIMITER
CREATE PROCEDURE test_pro()
BEGIN
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE(10,2) DEFAULT 0.0;
SELECT last_name,salary INTO emp_name,sal
FROM employees
WHERE employee_id = 102;
SELECT emp_name,sal;
END
DELIMITER ;
CALL test_pro();
SELECT last_name,salary FROM employees
WHERE employee_id = 102;
SET @v1 = 10;
SET @v2 := 20;
SET @result := @v1 + @v2;
SELECT @result;
DELIMITER
CREATE PROCEDURE add_value()
BEGIN
DECLARE value1,value2,sum_val INT;
SET value1 = 10;
SET value2 := 100;
SET sum_val = value1 + value2;
SELECT sum_val;
END
DELIMITER ;
CALL add_value();
DELIMITER
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
DECLARE emp_sal DOUBLE DEFAULT 0.0;
DECLARE mgr_sal DOUBLE DEFAULT 0.0;
DECLARE mgr_id INT DEFAULT 0;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;
SET dif_salary = mgr_sal - emp_sal;
END
DELIMITER ;
SET @emp_id := 103;
SET @dif_sal := 0;
CALL different_salary(@emp_id,@dif_sal);
SELECT @dif_sal;
SELECT * FROM employees;
INSERT INTO employees(last_name)
VALUES('Tom');
DESC employees;
DELIMITER
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END
DELIMITER ;
CALL UpdateDataNoCondition();
SELECT @x;
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';
DECLARE command_not_allowed CONDITION FOR 1148;
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'NO_SUCH_TABLE';
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
DROP PROCEDURE UpdateDataNoCondition;
DELIMITER
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END
DELIMITER ;
CALL UpdateDataNoCondition();
SELECT @x,@prc_value;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
DESC departments;
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
DELIMITER
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 3;
END
DELIMITER ;
CALL InsertDataWithCondition();
SELECT @x;
DROP PROCEDURE IF EXISTS InsertDataWithCondition;
DELIMITER
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
DECLARE duplicate_entry CONDITION FOR 1062;
DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 3;
END
DELIMITER ;
CALL InsertDataWithCondition();
SELECT @x,@pro_value;
DELIMITER
CREATE PROCEDURE test_if()
BEGIN
DECLARE age INT DEFAULT 20;
IF age > 40
THEN SELECT '中老年';
ELSEIF age > 18
THEN SELECT '青壮年';
ELSEIF age > 8
THEN SELECT '青少年';
ELSE
SELECT '婴幼儿';
END IF;
END
DELIMITER ;
CALL test_if();
DROP PROCEDURE test_if;
DELIMITER
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE hire_year DOUBLE;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;
IF emp_sal < 8000 AND hire_year >= 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
END IF;
END
DELIMITER ;
CALL update_salary_by_eid1(104);
SELECT DATEDIFF(CURDATE(),hire_date)/365, employee_id,salary
FROM employees
WHERE salary < 8000 AND DATEDIFF(CURDATE(),hire_date)/365 >= 5;
DROP PROCEDURE update_salary_by_eid1;
DELIMITER
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE hire_year DOUBLE;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;
IF emp_sal < 9000 AND hire_year >= 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END
DELIMITER ;
CALL update_salary_by_eid2(103);
CALL update_salary_by_eid2(104);
SELECT * FROM employees
WHERE employee_id IN (103,104);
DELIMITER
CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE bonus DOUBLE;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
IF emp_sal < 9000
THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
ELSEIF emp_sal < 10000 AND bonus IS NULL
THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END
DELIMITER ;
CALL update_salary_by_eid3(102);
CALL update_salary_by_eid3(103);
CALL update_salary_by_eid3(104);
SELECT *
FROM employees
WHERE employee_id IN (102,103,104);
DELIMITER
CREATE PROCEDURE test_case()
BEGIN
DECLARE var1 INT DEFAULT 10;
CASE
WHEN var1 >= 100 THEN SELECT '三位数';
WHEN var1 >= 10 THEN SELECT '两位数';
ELSE SELECT '个数位';
END CASE;
END
DELIMITER ;
CALL test_case();
DROP PROCEDURE test_case;
DELIMITER
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE bonus DOUBLE;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
CASE
WHEN emp_sal < 9000 THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
WHEN emp_sal < 10000 AND bonus IS NULL THEN UPDATE employees SET commission_pct = 0.01
WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END CASE;
END
DELIMITER ;
CALL update_salary_by_eid4(103);
CALL update_salary_by_eid4(104);
CALL update_salary_by_eid4(105);
SELECT *
FROM employees
WHERE employee_id IN (103,104,105);
DELIMITER
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
DECLARE hire_year INT;
SELECT ROUND(DATEDIFF(CURDATE(),hire_date) / 365) INTO hire_year
FROM employees WHERE employee_id = emp_id;
CASE hire_year
WHEN 0 THEN UPDATE employees SET salary = salary + 50 WHERE employee_id = emp_id;
WHEN 1 THEN UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
WHEN 2 THEN UPDATE employees SET salary = salary + 200 WHERE employee_id = emp_id;
WHEN 3 THEN UPDATE employees SET salary = salary + 300 WHERE employee_id = emp_id;
WHEN 4 THEN UPDATE employees SET salary = salary + 400 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
END CASE;
END
DELIMITER ;
CALL update_salary_by_eid5(101);
SELECT *
FROM employees
DROP PROCEDURE update_salary_by_eid5;
DELIMITER
CREATE PROCEDURE test_loop()
BEGIN
DECLARE num INT DEFAULT 1;
loop_label:LOOP
SET num = num + 1;
IF num >= 10 THEN LEAVE loop_label;
END IF;
END LOOP loop_label;
SELECT num;
END
DELIMITER ;
CALL test_loop();
DELIMITER
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE loop_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
loop_lab:LOOP
IF avg_sal >= 12000
THEN LEAVE loop_lab;
END IF;
UPDATE employees SET salary = salary * 1.1;
SELECT AVG(salary) INTO avg_sal FROM employees;
SET loop_count = loop_count + 1;
END LOOP loop_lab;
SET num = loop_count;
END
DELIMITER ;
SELECT AVG(salary) FROM employees;
CALL update_salary_loop(@num);
SELECT @num;
DELIMITER
CREATE PROCEDURE test_while()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 10 DO
SET num = num + 1;
END WHILE;
SELECT num;
END
DELIMITER ;
CALL test_while();
DELIMITER
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE while_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9 ;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
SET num = while_count;
END
DELIMITER ;
CALL update_salary_while(@num);
SELECT @num;
SELECT AVG(salary) FROM employees;
DELIMITER
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE num INT DEFAULT 1;
REPEAT
SET num = num + 1;
UNTIL num >= 10
END REPEAT;
SELECT num;
END
DELIMITER ;
CALL test_repeat();
DELIMITER
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE repeat_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
REPEAT
UPDATE employees SET salary = salary * 1.15;
SET repeat_count = repeat_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
UNTIL avg_sal >= 13000
END REPEAT;
SET num = repeat_count;
END
DELIMITER ;
CALL update_salary_repeat(@num);
SELECT @num;
SELECT AVG(salary) FROM employees;
DELIMITER
CREATE PROCEDURE leave_begin(IN num INT)
begin_label:BEGIN
IF num <= 0
THEN LEAVE begin_label;
ELSEIF num = 1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num = 2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END
DELIMITER ;
CALL leave_begin(1);
DELIMITER
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE;
DECLARE while_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
while_label:WHILE TRUE DO
IF avg_sal <= 10000 THEN
LEAVE while_label;
END IF;
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
SET num = while_count;
END
DELIMITER ;
CALL leave_while(@num);
SELECT @num;
SELECT AVG(salary) FROM employees;
DELIMITER
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
loop_label:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE loop_label;
ELSEIF num > 15
THEN LEAVE loop_label;
END IF;
SELECT '尚硅谷：让天下没有难学的技术';
END LOOP;
END
DELIMITER ;
CALL test_iterate();
SELECT * FROM employees;
DELIMITER
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
DECLARE sum_sal DOUBLE DEFAULT 0.0;
DECLARE emp_sal DOUBLE;
DECLARE emp_count INT DEFAULT 0;
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
OPEN emp_cursor;
REPEAT
FETCH emp_cursor INTO emp_sal;
SET sum_sal = sum_sal + emp_sal;
SET emp_count = emp_count + 1;
UNTIL sum_sal >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
CLOSE emp_cursor;
END
DELIMITER ;
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;
本文详细介绍了MySQL中的变量类型（系统变量、用户变量），流程控制结构（IF、CASE、循环及异常处理），以及游标的基本使用方法。通过实例演示了如何设置、查看系统变量，处理错误和条件，以及如何运用这些技术进行数据操作和流程控制。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
