MySQL中存储过程与函数总结

2023-11-01

1. 存储过程与函数的概念

2. 创建存储过程与函数

2.1. 参数列表

方法二，捕获mysql_error_code

1. 使用`SHOW STATUS`查看存储过程和存储函数：

2. 使用`SHOW CREATE`查看存储过程和存储函数的定义和创建语句：

3. 使用表中的元数据查看存储过程和存储函数：

MySQL中存储过程与函数是用于存储和执行SQL语句的一种方式，可以提高数据库的性能和安全性。

1. 存储过程与函数的概念

存储过程（Stored Procedure）是一组预编译的SQL语句集合，通过一个名称进行调用和执行，可以看做是批文件但是起作用不仅限于批处理。
函数（Function）是一个返回值的存储过程，可以作为一个表达式的一部分使用。

2. 创建存储过程与函数

创建存储过程使用 `CREATE PROCEDURE` 语句，示例：

CREATE PROCEDURE procedure_name ([参数列表])
BEGIN
  -- 存储过程的SQL语句
END;

`CREATE PROCEDURE`：用于创建存储过程的关键字。
`procedure_name`：存储过程的名称，用于唯一标识存储过程。
`[参数列表]`：可选项，用于指定存储过程的输入、输出或输入输出参数。
`BEGIN` / `END`：用于定义存储过程的开始和结束位置。
`-- 存储过程的SQL语句`：在`BEGIN`和`END`之间的部分为存储过程的具体实现，包括各种SQL语句、控制流语句等。

创建函数使用 `CREATE FUNCTION` 语句，示例：

CREATE FUNCTION function_name ([参数列表])
RETURNS return_type
BEGIN
  -- 函数的SQL语句
  RETURN return_value;
END;

`CREATE FUNCTION`：用于创建函数的关键字。
`function_name`：函数的名称，用于唯一标识函数。
`[参数列表]`：可选项，用于指定函数的输入参数。
`RETURNS`：指定函数的返回类型。
`return_type`：函数的返回数据类型。
`-- 函数的SQL语句`：在`BEGIN`和`END`之间的部分为函数的具体实现，包括各种SQL语句、控制流语句等。
`RETURN return_value`：用于指定函数的返回值。

2.1. 参数列表

存储过程可以接收零个或多个参数，并且每个参数都有一个数据类型。参数可以是输入参数、输出参数或输入输出参数。

(parameter_name1 data_type [IN|OUT|INOUT] [DEFAULT default_value],
 parameter_name2 data_type [IN|OUT|INOUT] [DEFAULT default_value], ...)

输入参数（IN）：存储过程接收的值只能在存储过程内部使用。使用`IN`关键字声明输入参数。
输出参数（OUT）：存储过程返回的值只能在存储过程外部使用。使用`OUT`关键字声明输出参数。
输入输出参数（INOUT）：既可以在存储过程内部使用，也可以在存储过程外部使用。使用`INOUT`关键字声明输入输出参数。
`parameter_name`：参数的名称，用于在存储过程或函数内部引用参数。
`data_type`：参数的数据类型。
`DEFAULT default_value`：可选项，用于指定参数的默认值。

3. 使用变量

在存储过程中可以使用变量来存储临时数据。使用`DECLARE`语句来声明变量，示例：

DECLARE variable_name data_type [DEFAULT value];

变量的作用范围限于存储过程内部，在存储过程结束后会被销毁。

4. 定义条件与处理程序

对于定义条件和处理程序，我们使用DECLARE和DECLARE HANDLER语法。

1. 定义条件：

DECLARE condition_name CONDITION FOR condition_value;

condition_name: 您为条件定义的名称。
condition_value: 表示条件的表达式或错误码。

示例：

DECLARE custom_error CONDITION FOR SQLSTATE '45000';

在这个示例中，我们使用DECLARE关键字定义了一个条件变量custom_error，与SQLSTATE '45000'相关联。

2. 定义处理程序：

DECLARE handler_type HANDLER FOR condition_value action;

handler_type: 特定的处理程序类型，如CONTINUE、EXIT或UNDO。
CONTINUE处理程序类型用于在遇到异常情况时，继续执行程序。它不会中断程序的执行，而是继续执行接下来的语句。
EXIT处理程序类型用于在遇到异常情况时，终止当前的存储过程、函数或触发器的执行。
UNDO处理程序类型用于遇到错误后撤销之前的操作（MySQL暂时不支持这种操作）
condition_value: 表示条件的表达式或错误码。
action: 在满足条件时要执行的操作。

示例：

DECLARE CONTINUE HANDLER FOR custom_error
BEGIN
    -- 执行操作，例如输出错误消息
    SELECT 'An error occurred: Custom Error' AS message;
END;

在这个示例中，我们定义了一个处理程序，当满足custom_error条件时，程序将继续执行，并输出一条自定义的错误消息。

注意，条件和处理程序必须在BEGIN...END语句块内使用。只有在DECLARE HANDLER语句之前定义的条件和处理程序才能在块内使用。

3.六种定义处理程序的方法

方法一，捕获sqlstate_value

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=’NO_SUCH_TABLE’;

如果，遇到sqlstate_value值为’42S02’，则执行CONTINUE操作，并且，输出”NO_SUCH_TABLE”信息

方法二，捕获mysql_error_code

DECLARE CONTINUE HANDLER FOR 1146 SET @info=’NO_SUCH_TABLE’;

如果，遇到mysql_error_code值为1146，则执行CONTINUE操作，并且，输出”NO_SUCH_TABLE”信息

方法三，先定义条件，然后调用

DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=’ERROR’;

这里先定义no_such_table条件，遇到1146错误，就执行CONTINUE操作

方法四，使用SQLWARNING

DECLARE EXIT HANDLER FOR SQLWARNING SET @info=’ERROR’;

SQLWARNING捕获所有以01开头的，sqlstate_value值，然后，执行EXIT操作，并且，输出ERROR信息

方法五，使用NOT FOUND

DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’NO_SUCH_TABLE’;

NOT FOUND捕获所有以02开头的sqlstate_value值，然后，执行EXIT操作，并且，输出”NO_SUCH_TABLE”信息

方法六，使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’ERROR’;

SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后，执行EXIT操作，并且，输出”ERROR”信息
定义条件和处理程序

5. 光标的使用

在MySQL中，光标用于在存储过程或函数中处理查询结果集。使用光标可以按行遍历结果集，以便对每一行进行操作。光标的使用包括声明光标、打开光标、使用光标和关闭光标。

1. 声明光标：

DECLARE cursor_name CURSOR FOR select_statement;

cursor_name：给光标指定的名称，用于后续引用。
select_statement：一个SELECT语句，用于定义光标的结果集。

示例：

DECLARE employee_cursor CURSOR FOR SELECT * FROM employees;

在上述示例中，我们声明了一个名为`employee_cursor`的光标，用于处理`employees`表的结果集。

2. 打开光标：

OPEN cursor_name;

cursor_name：要打开的光标的名称。

示例：

OPEN employee_cursor;

在上述示例中，我们打开之前声明的`employee_cursor`光标。

3. 使用光标：

使用光标的主要操作包括FETCH、LOOP和CLOSE。

FETCH：用于从结果集中获取下一行数据。

FETCH cursor_name INTO variable_list;

cursor_name：要使用的光标的名称。
variable_list：接收结果集列值的变量列表。

示例：

FETCH employee_cursor INTO @empId, @empName, @empSalary;

在上述示例中，我们使用`employee_cursor`光标从结果集中获取下一行数据，并将列值分别存储在`@empId`、`@empName`和`@empSalary`变量中。

LOOP：用于循环遍历光标的结果集。

LOOP_label: LOOP
    -- 执行操作
    -- 在每个迭代中，使用FETCH获取下一行数据
END LOOP;

LOOP_label：LOOP语句的标签，用于在需要的时候退出循环。示例：

employee_loop: LOOP
    FETCH employee_cursor INTO @empId, @empName, @empSalary;
    -- 执行操作，例如输出员工信息

    IF condition THEN
        LEAVE employee_loop; -- 根据条件跳出循环
    END IF;
END LOOP employee_loop;

在上述示例中，我们循环遍历`employee_cursor`光标的结果集，通过FETCH获取每一行数据并执行相应的操作。根据特定条件，我们使用LEAVE语句跳出循环。

4. 关闭光标：

CLOSE cursor_name;

cursor_name：要关闭的光标的名称。

示例：

CLOSE employee_cursor;

在上述示例中，我们关闭之前打开的`employee_cursor`光标。

6. 流程控制的使用

在MySQL中，流程控制语句用于控制程序的流程和执行顺序。常见的流程控制语句包括IF语句、CASE语句、LOOP语句、LEAVE语句、REPEAT语句和WHILE语句。

1. IF语句：

IF condition THEN
    statements;
ELSEIF condition THEN
    statements;
ELSE
    statements;
END IF;

condition：条件表达式，根据其计算结果来确定执行哪个代码块。
statements：执行的语句或代码块。

示例：

IF score > 90 THEN
    SELECT '优秀';
ELSEIF score > 80 THEN
    SELECT '良好';
ELSE
    SELECT '及格';
END IF;

在上述示例中，根据`score`的值，会执行不同的语句块并输出相应的结果。

2. CASE语句：

CASE expression
    WHEN value1 THEN
        statements;
    WHEN value2 THEN
        statements;
    ...
    ELSE
        statements;
END CASE;

expression：表达式或变量，用于与不同的值进行比较。
value1、value2等：可能的值。
statements：与每个值相关联的执行语句或代码块。

示例：

CASE department
    WHEN 'IT' THEN
        SELECT 'Information Technology';
    WHEN 'HR' THEN
        SELECT 'Human Resources';
    ELSE
        SELECT 'Other Department';
END CASE;

在上述示例中，根据`department`的值，会执行相应的语句块并输出相应的结果。

3. LOOP语句：

label: LOOP
    statements;
    IF condition THEN
        LEAVE label;
    END IF;
END LOOP label;

label：LOOP语句的标签，用于在需要的时候退出循环。
statements：执行的语句或代码块。
condition：循环退出的条件。

示例：

employee_loop: LOOP
    SELECT name INTO @employee_name FROM employees
    WHERE department = 'IT' LIMIT 1;
    -- 执行操作，例如输出员工信息
    
    IF @employee_name IS NULL THEN
        LEAVE employee_loop; -- 根据条件跳出循环
    END IF;
END LOOP employee_loop;

在上述示例中，通过LOOP语句循环遍历`employees`表中的IT部门员工，执行相应的操作。当没有符合条件的员工时，使用LEAVE语句跳出循环。

4. LEAVE语句：

LEAVE label;

label：要跳出的语句块的标签。

示例：

employee_loop: LOOP
    -- 执行操作
    IF condition THEN
        LEAVE employee_loop; -- 根据条件跳出循环
    END IF;
END LOOP employee_loop;

在上述示例中，使用LEAVE语句根据特定条件跳出循环。

5. REPEAT语句：

REPEAT
    statements;
    IF condition THEN
        LEAVE;
    END IF;
UNTIL condition;

statements：执行的语句或代码块。
condition：循环退出的条件。

示例：

SET @count = 0;

REPEAT
    SET @count = @count + 1;
    SELECT @count;
    IF @count = 5 THEN
        LEAVE;
    END IF;
UNTIL @count > 10;

在上述示例中，使用REPEAT语句循环输出数值，当`@count`等于5时，使用LEAVE语句跳出循环。

6. WHILE语句：

WHILE condition DO
    statements;
END WHILE;

condition：循环的条件表达式。
statements：执行的语句或代码块。

示例：

SET @count = 1;

WHILE @count <= 5 DO
    SELECT @count;
    SET @count = @count + 1;
END WHILE;

在上述示例中，使用WHILE语句循环输出数值，当`@count`小于等于5时，执行相应的语句，并每次循环后递增`@count`的值。

7. 调用存储过程与函数

在MySQL中，可以使用存储过程和存储函数来封装一系列SQL语句，并根据需要进行调用。存储过程用于执行一系列操作，而存储函数则返回单个值。：

1. 存储过程的基本语法：

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name data_type [, ...])
    [characteristics]
    BEGIN
        -- 存储过程的执行逻辑
    END;

procedure_name：存储过程的名称。
parameter_name：存储过程的参数名。
data_type：参数的数据类型。
IN：表示参数为输入参数。
OUT：表示参数为输出参数。
INOUT：表示参数既可作为输入参数，也可作为输出参数。
characteristics：存储过程的特性，如语言、事务特性。
BEGIN和END之间是存储过程中的执行逻辑。

示例：

CREATE PROCEDURE get_employee(IN empId INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = empId;
END;

在上述示例中，我们创建了一个名为`get_employee`的存储过程，它接收一个输入参数`empId`，并根据该参数查询符合条件的员工。

2. 存储函数的基本语法：

CREATE FUNCTION function_name ([parameter_name data_type [, ...]])
    RETURNS return_type
    [characteristics]
    BEGIN
        -- 存储函数的执行逻辑
        RETURN value;
    END;

function_name：存储函数的名称。
parameter_name：存储函数的参数名。
data_type：参数的数据类型。
return_type：函数返回值的数据类型。
characteristics：存储函数的特性，如语言、事务特性。
BEGIN和END之间是存储函数中的执行逻辑。
value：函数的返回值。

示例：

CREATE FUNCTION calculate_total(salary INT, bonus INT) RETURNS INT
BEGIN
    DECLARE total_salary INT;
    SET total_salary = salary + bonus;
    RETURN total_salary;
END;

在上述示例中，我们创建了一个名为`calculate_total`的存储函数，它接收两个输入参数`salary`和`bonus`，并返回它们的和作为函数的返回值。

3.调用存储过程和存储函数的语法如下：

调用存储过程：

CALL procedure_name([parameter_value, ...]);

示例：

CALL get_employee(1);

在上述示例中，我们调用了名为`get_employee`的存储过程，并传入参数值1，以获取员工ID为1的员工信息。

调用存储函数：

SELECT function_name([parameter_value, ...]);

示例：

SELECT calculate_total(3000, 500) AS total_salary;

在上述示例中，我们调用了名为`calculate_total`的存储函数，并传入参数值3000和500，以计算总工资，并将结果作为查询的结果进行返回。

8. 查看存储过程与函数

在MySQL中，您可以使用不同的方法来查看存储过程和存储函数的详细信息，包括通过`SHOW STATUS`查看，通过`SHOW CREATE`查看，以及通过表中的元数据查看。

1. 使用`SHOW STATUS`查看存储过程和存储函数：

SHOW STATUS LIKE 'procedure';
SHOW STATUS LIKE 'function';

procedure：用于查看存储过程的状态信息。
function：用于查看存储函数的状态信息。

示例：

SHOW STATUS LIKE 'procedure';
SHOW STATUS LIKE 'function';

这些示例语句将显示与存储过程和存储函数相关的状态信息，包括存储过程和存储函数的计数和其他属性。

2. 使用`SHOW CREATE`查看存储过程和存储函数的定义和创建语句：

SHOW CREATE PROCEDURE procedure_name;
SHOW CREATE FUNCTION function_name;

procedure_name：要查看的存储过程的名称。
function_name：要查看的存储函数的名称。

示例：

SHOW CREATE PROCEDURE get_employee;
SHOW CREATE FUNCTION calculate_total;

这些示例语句将显示存储过程和存储函数的定义和创建语句，包括存储过程和存储函数的名称、参数、执行逻辑和返回值等详细信息。

3. 使用表中的元数据查看存储过程和存储函数：

您可以通过查询数据库中的特定表来查看存储过程和存储函数的元数据。以下是涉及的基本表和它们的解释：

`information_schema.ROUTINES`表：包含了所有存储过程和存储函数的信息。
`information_schema.PARAMETERS`表：包含了所有存储过程和存储函数的参数信息。

示例：

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION';
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'get_employee';
SELECT * FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = 'calculate_total';

这些示例查询将返回存储过程和存储函数的详细信息，包括名称、参数、数据类型、执行逻辑等。

9. 修改存储过程与函数

在MySQL中，可以使用`ALTER PROCEDURE`和`ALTER FUNCTION`语句来修改已存在的存储过程和存储函数的定义。

1. 修改存储过程的基本语法：

ALTER PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name data_type [, ...])
    [characteristics]
    [COMMENT 'new_comment']
    BEGIN
        -- 修改后的存储过程的执行逻辑
    END;

procedure_name：要修改的存储过程的名称。
parameter_name：要修改的存储过程的参数名。
data_type：参数的数据类型。
IN：更新参数为输入参数。
OUT：更新参数为输出参数。
INOUT：更新参数为既可作为输入参数，也可作为输出参数。
characteristics：存储过程的特性，如语言、事务特性。
COMMENT 'new_comment'：可选，用于修改存储过程的注释。
BEGIN和END之间是修改后的存储过程的执行逻辑。

示例：

ALTER PROCEDURE get_employee(IN empId INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = empId;
    -- 修改后的逻辑
    UPDATE employees SET salary = salary + 1000 WHERE employee_id = empId;
END;

在上述示例中，我们修改了名为`get_employee`的存储过程，增加了在查询员工信息后更新员工工资的逻辑。

2. 修改存储函数的基本语法：

ALTER FUNCTION function_name ([parameter_name data_type [, ...]])
    RETURNS return_type
    [characteristics]
    [COMMENT 'new_comment']
    BEGIN
        -- 修改后的存储函数的执行逻辑
        RETURN new_value;
    END;

function_name：要修改的存储函数的名称。
parameter_name：要修改的存储函数的参数名。
data_type：参数的数据类型。
return_type：函数返回值的数据类型。
characteristics：存储函数的特性，如语言、事务特性。
COMMENT 'new_comment'：可选，用于修改存储函数的注释。
BEGIN和END之间是修改后的存储函数的执行逻辑。
new_value：修改后的函数的返回值。

示例：

ALTER FUNCTION calculate_total(salary INT, bonus INT) RETURNS INT
BEGIN
    DECLARE total_salary INT;
    SET total_salary = salary + bonus;
    -- 修改后的逻辑
    SET total_salary = total_salary * 1.1;
    RETURN total_salary;
END;

在上述示例中，我们修改了名为`calculate_total`的存储函数，增加了将总工资乘以1.1的逻辑，然后返回修改后的结果。

10. 删除存储过程与函数

在MySQL中，您可以使用`DROP PROCEDURE`和`DROP FUNCTION`语句来删除已存在的存储过程和存储函数。

1. 删除存储过程的基本语法：

DROP PROCEDURE IF EXISTS procedure_name;

procedure_name：要删除的存储过程的名称。

示例：

DROP PROCEDURE IF EXISTS get_employee;

在上述示例中，如果名为`get_employee`的存储过程存在，则将其删除。

2. 删除存储函数的基本语法：

DROP FUNCTION IF EXISTS function_name;

function_name：要删除的存储函数的名称。

示例：

DROP FUNCTION IF EXISTS calculate_total;

在上述示例中，如果名为`calculate_total`的存储函数存在，则将其删除。

使用`DROP PROCEDURE`和`DROP FUNCTION`语句，您可以轻松删除已存在的存储过程和存储函数。通过指定要删除的存储过程或存储函数的名称，并使用`IF EXISTS`可选关键字，以确保在对象不存在时不会出现错误。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

数据库