1.变量
1. 系统变量
- 定义：系统变量由数据库系统提供，用于存储与数据库服务器的状态或配置相关的信息。系统变量通常以
@@前缀表示。 - 作用域：全局可用，通常在整个会话中保持有效。
- 用途：用于获取数据库服务器的信息，如版本、当前数据库、连接信息等。
2. 用户变量
- 定义：用户变量是由用户定义的变量，通常用于在 SQL 会话中存储临时数据。用户变量以
@前缀表示。 - 作用域：会话级别，在创建它的会话中有效，直到会话结束或变量被重新赋值。
- 用途：用于在 SQL 语句之间共享临时数据。
3. 局部变量
- 定义：局部变量是在存储过程中定义的变量，通常在存储过程中使用，不同于用户变量和系统变量。
- 作用域：仅在定义它的存储过程内部有效，超出该存储过程后不可用。
- 用途：用于存储处理过程中需要的临时数据。
示例 SQL
以下是一个包含这三种变量的存储过程示例：
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
DECLARE local_var INT; -- 局部变量
SET local_var = 10; -- 设置局部变量的值
SET @user_var = 20; -- 用户变量
SET @@global.max_connections = 100; -- 系统变量（全局设置示例）
SELECT local_var AS LocalVariable,
@user_var AS UserVariable,
@@global.max_connections AS SystemVariable;
END //
DELIMITER ;
SQL 解释
-
创建存储过程：使用
CREATE PROCEDURE语句定义一个名为example_procedure的存储过程。 -
定义局部变量：
DECLARE local_var INT;：声明一个名为local_var的局部变量，类型为INT。该变量仅在存储过程内部有效。
-
设置变量的值：
SET local_var = 10;：将局部变量的值设置为 10。SET @user_var = 20;：设置用户变量@user_var为 20。这个变量在创建它的会话中都有效。SET @@global.max_connections = 100;：设置系统变量max_connections的全局值为 100（虽然在实际使用中，直接修改系统变量需要相应的权限和上下文）。
-
选择输出：
SELECT语句用于输出三个变量的值：local_var（局部变量）将返回 10。@user_var（用户变量）将返回 20。@@global.max_connections（系统变量）将返回设置的全局最大连接数。
使用示例
调用存储过程：
CALL example_procedure();
结果
当调用这个存储过程时，它将返回以下结果（假设系统变量 max_connections 的初始值为某个数字）：
LocalVariable将是 10（局部变量）。UserVariable将是 20（用户变量）。SystemVariable将是你设置的全局最大连接数（具体值取决于之前的设置）。
总结
- 系统变量：由系统定义，存储数据库的状态和配置，在整个会话中有效，通常使用
@@表示。 - 用户变量：用户定义的临时变量，在会话中有效，通常使用
@表示。 - 局部变量：在存储过程中定义的变量，仅在过程中有效，使用
DECLARE语句定义。
2.参数
在存储过程中，参数可以分为三种类型：IN、OUT和INOUT。每种参数类型的用途和行为不同。以下是对这三种参数的详细介绍及示例。
1. IN 参数
- 定义：
IN参数是输入参数，调用存储过程时传入的值。存储过程内部可以读取这个值，但不能修改它。 - 用途：用于从调用者向存储过程传递数据。
2. OUT 参数
- 定义：
OUT参数是输出参数，用于将值从存储过程返回给调用者。在存储过程中可以被修改，并且在调用结束后，调用者可以获得这个值。 - 用途：用于将计算结果或其他信息返回给调用者。
3. INOUT 参数
- 定义：
INOUT参数既可以用作输入参数，也可以用作输出参数。存储过程可以读取这个值，并在处理过程中修改它，最后将修改后的值返回给调用者。 - 用途：用于需要在过程内修改的输入数据，并希望将结果返回给调用者。
示例 SQL
以下是一个使用 IN、OUT 和 INOUT 参数的存储过程示例：
DELIMITER //
CREATE PROCEDURE example_procedure(
IN in_param INT,
OUT out_param INT,
INOUT inout_param INT
)
BEGIN
-- 处理输入参数
SET out_param = in_param * 2; -- 将出参设为输入参数的两倍
SET inout_param = inout_param + 10; -- 将入出参数加上10
END //
DELIMITER ;
SQL 解释
-
创建存储过程：使用
CREATE PROCEDURE语句定义一个名为example_procedure的存储过程，接受三个参数：in_param：输入参数 (IN)。out_param：输出参数 (OUT)。inout_param：输入输出参数 (INOUT)。
-
处理逻辑：
SET out_param = in_param * 2;：将out_param设置为in_param的两倍。SET inout_param = inout_param + 10;：在inout_param的基础上加 10。此时，如果在调用存储过程之前设置了inout_param的值，则它将在存储过程内被修改。
调用存储过程示例
下面是如何调用这个存储过程的示例：
SET @input = 5;
SET @output = 0;
SET @inout_value = 20;
CALL example_procedure(@input, @output, @inout_value);
SELECT @output AS OutputParam, @inout_value AS InOutParam;
调用说明
-
设置变量：
@input：设为 5，作为输入参数传入存储过程。@output：初始设为 0，存储过程将修改这个变量并返回结果。@inout_value：初始设为 20，存储过程将对这个值进行修改。
-
调用存储过程：使用
CALL语句来执行存储过程example_procedure。 -
查看结果：通过
SELECT语句查看输出参数和修改后的输入输出参数。
结果
OutputParam将会是 10（因为5 * 2 = 10）。InOutParam将会是 30（因为20 + 10 = 30）。
3.流程控制
1. IF 判断
IF 语句用于根据条件执行特定的 SQL 语句。如果条件为真，则执行相应的 SQL 语句。
语法
IF condition THEN
-- 语句块
ELSEIF another_condition THEN
-- 另外的语句块
ELSE
-- 其他情况的语句块
END IF;
示例
CREATE PROCEDURE check_status(IN status INT)
BEGIN
IF status = 1 THEN
SELECT 'Active' AS StatusMessage;
ELSEIF status = 0 THEN
SELECT 'Inactive' AS StatusMessage;
ELSE
SELECT 'Unknown Status' AS StatusMessage;
END IF;
END;
详细解释
- CREATE PROCEDURE：用于创建一个名为
check_status的存储过程，接受一个输入参数status（整数类型）。 - BEGIN … END：定义了存储过程的主体。所有逻辑都在这两者之间。
- IF … THEN：用于检测条件：
IF status = 1：如果输入参数status的值为 1，返回'Active'；ELSEIF status = 0：如果status为 0，返回'Inactive'；ELSE：如果status既不是 1 也不是 0，返回'Unknown Status'。
- SELECT：用于返回结果，数据以
StatusMessage列的形式显示。
2. CASE 语句
CASE 语句用于在多个条件中进行选择，与 IF 语句类似，但在条件较多的情况下更为简洁。
语法
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END;
示例 SQL
CREATE PROCEDURE get_grade(IN score INT)
BEGIN
DECLARE grade VARCHAR(10);
SET grade = CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END;
SELECT grade AS GradeMessage;
END;
详细解释
- DECLARE：声明一个局部变量
grade，用于存储计算的结果，类型为VARCHAR(10)。 - SET grade = CASE：根据输入的
score值来确定等级：WHEN score >= 90：如果分数大于或等于 90，等级为A；- 其余条件依次判断，直到
ELSE：如果所有条件都不满足，等级为F。
- SELECT：输出结果，通过
GradeMessage列返回等级。
3. WHILE 循环
WHILE 循环用于重复执行一段代码，直到条件不再满足。
语法
WHILE condition DO
-- 语句块
END WHILE;
示例 SQL
CREATE PROCEDURE count_down(IN start INT)
BEGIN
WHILE start > 0 DO
SELECT start AS Countdown;
SET start = start - 1;
END WHILE;
SELECT 'Liftoff!' AS Message;
END;
详细解释
- IN start INT：接收一个整数参数
start，定义倒计时起始值。 - WHILE start > 0 DO：只要
start大于 0，就执行循环体。 - SELECT start AS Countdown：输出当前的
start值，显示倒计时数字。 - SET start = start - 1：每次循环结束时，减少
start值 1。 - END WHILE：当
start小于等于 0 时，退出循环。 - SELECT ‘Liftoff!’ AS Message：在循环结束后输出“Liftoff!”作为消息。
4. LOOP 循环
LOOP 循环是一种无限循环，必须通过 LEAVE 语句显式退出。
语法
LOOP
-- 语句块
IF condition THEN
LEAVE; -- 退出循环
END IF;
END LOOP;
示例 SQL
CREATE PROCEDURE infinite_loop_example()
BEGIN
DECLARE counter INT DEFAULT 0;
LOOP
SET counter = counter + 1;
SELECT counter AS LoopCounter;
IF counter >= 5 THEN
LEAVE; -- 当计数器达到5时退出循环
END IF;
END LOOP;
SELECT 'Finished looping' AS Message;
END;
详细解释
- DECLARE counter INT DEFAULT 0：声明并初始化一个局部变量
counter为 0。 - LOOP：开始无限循环。
- SET counter = counter + 1：每次循环将
counter增加 1。 - SELECT counter AS LoopCounter：输出当前的
counter值，显示循环次数。 - IF counter >= 5 THEN LEAVE;：如果
counter值达到 5，则执行LEAVE语句，退出循环。 - SELECT ‘Finished looping’ AS Message：当循环结束后，输出“Finished looping”作为结束消息。
循环的区别
-
WHILE 循环：
- 以条件为基础，适合需要根据条件动态控制执行次数的情况。
- 通过布尔条件持续执行，条件满足时执行。
-
LOOP 循环：
- 是一个无限循环，必须通过
LEAVE语句显式退出。 - 适合不定次数的执行，但需要手动终止。
- 是一个无限循环，必须通过
-
游标结合循环：
- 游标用于遍历查询结果集，通常与
LOOP结合使用，以逐行处理查询结果，直到没有更多行可处理。
- 游标用于遍历查询结果集，通常与
5. 游标（CURSOR）
游标用于处理一组结果集，逐行处理数据。游标使得存储过程能在数据行之间进行遍历。
语法
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO variable_list;
CLOSE cursor_name;
示例 SQL
CREATE PROCEDURE process_items()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE item_name VARCHAR(100);
DECLARE item_cursor CURSOR FOR SELECT name FROM items;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN item_cursor;
read_loop: LOOP
FETCH item_cursor INTO item_name;
IF done THEN
LEAVE read_loop;
END IF;
SELECT item_name AS ProcessedItem;
END LOOP;
CLOSE item_cursor;
END;
详细解释
- DECLARE done INT DEFAULT FALSE：声明用于标记游标处理完成的变量
done，初始值为FALSE。 - DECLARE item_name VARCHAR(100)：声明用于存储从游标中提取的每个项目名称的变量
item_name。 - DECLARE item_cursor CURSOR FOR SELECT name FROM items：声明游标
item_cursor，用于遍历items表中的name列。 - DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE：创建条件处理程序，在游标未找到更多数据时将
done设置为TRUE。 - OPEN item_cursor：打开游标，准备开始提取数据。
- read_loop: LOOP：开始一个命名循环
read_loop。 - FETCH item_cursor INTO item_name：从游标中获取下一行数据，存储在变量
item_name中。 - IF done THEN LEAVE read_loop;：如果
done为TRUE，则退出循环。 - SELECT item_name AS ProcessedItem：输出当前处理的
item_name。 - CLOSE item_cursor：关闭游标，释放资源。
6. 条件处理程序（HANDLER）
条件处理程序用于处理存储过程中的异常或特定条件，如错误捕获。
语法
DECLARE handler_type HANDLER FOR condition_type
BEGIN
-- 处理代码块
END;
示例 SQL
CREATE PROCEDURE safe_division(IN numerator INT, IN denominator INT)
BEGIN
DECLARE result FLOAT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'Division by zero error' AS ErrorMessage;
END;
SET result = numerator / denominator;
SELECT result AS DivisionResult;
END;
详细解释
- IN numerator INT, IN denominator INT：接收两个输入参数
numerator和denominator。 - DECLARE result FLOAT：声明一个浮点类型的变量
result用于存储计算结果。 - DECLARE CONTINUE HANDLER FOR SQLEXCEPTION：声明一个条件处理程序，当 SQL 异常发生（如除以零）时，将执行处理块中的代码。
- BEGIN … END：定义处理块：
SELECT 'Division by zero error' AS ErrorMessage;：输出错误消息。
- SET result = numerator / denominator：尝试进行除法运算，如果发生错误，控制将跳转到处理程序。
- SELECT result AS DivisionResult：如果没有错误，输出计算结果。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
