Bobby Blog

large picture of the cover

数据库系统概论-设计与应用开发篇-数据库编程

标准SQL是非过程化的查询语言，具有操作统一、面向集合、功能丰富、使用简单等多项优点。但和程序设计语言相比，高度非过程化的优点也造成了它的一个弱点：缺少流程控制能力，难以实现应用业务中的逻辑控制。SQL编程技术可以有效克服SQL语言实现复杂应用方面的不足，提高应用系统和数据库管理系统间的互操作性。

存储过程和函数

存储过程

存储过程是由过程化SQL语句书写的过程，这个过程经过编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。

优点：

  • 运行效率高；
  • 降低了客户端和服务器之间的通信量；

缺点：

  • 难以调试和扩展，更没有移植性；

创建存储过程

CREATE
    PROCEDURE proc_name ([proc_parameter[,...]])
    routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
routine_body:
    Valid SQL routine statement

proc_name：存储过程名称，可以用过*db_name.*制定数据库。

proc_parameter：参数列表，没有参数时使用()，参数名称不区分大小写，必须指定类型。默认情况下，每个参数都是IN参数，若要以其他方式指定参数，请在参数名称之前使用关键字OUT或INOUT。对于每个OUT或INOUT参数，在调用过程的CALL语句中传递一个用户定义的变量，以便在过程返回时获得它的值。如果从另一个存储过程或函数内部调用该过程，还可以将它们的参数或局部变量作为OUT或INOUT参数传递。如果从触发器内部调用过程，也可以传递NEW.col_name作为OUT或INOUT参数。

routine_body：由一个有效的SQL例程语句组成。这可以是一个简单的语句，比如SELECT或INSERT，也可以是一个使用BEGIN和END编写的复合语句。复合语句可以包含声明、循环和其他控制结构语句。

例如：创建一个存储过程使其可以找出大于给定年龄的学生个数

delimiter //
CREATE PROCEDURE S_T.proc_test1(IN SageParam SMALLINT, OUT SNum INT)
BEGIN 
SELECT COUNT(*) INTO SNum FROM Student WHERE Sage > SageParam;
END//
delimiter ;

注意：当过程被定义时，使用mysql客户端分隔符命令delimiter更改语句分隔符;到//。这使得;是传递给服务器的过程主体中使用的分隔符，而不是由mysql本身解释。

执行存储过程

CALL sp_name([parameter[,...]])
CALL sp_name[()]

调用上面定义的存储过程：

CALL S_T.proc_test1(18, @Snums);
SELECT @Snums;

查看存储过程

查看存储过程状态

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr]

查看某个存储过程定义

SHOW CREATE PROCEDURE proc_name

删除存储过程

DROP PROCEDURE [IF EXISTS] proc_name

存储函数

函数和存储过程类似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回的类型。MySQL允许例程包含DDL语句，比如CREATE和DROP。MySQL还允许存储过程(但不允许存储函数)包含SQL事务语句，如COMMIT。存储函数不能包含执行显式或隐式提交或回滚的语句。

创建存储函数

CREATE
    FUNCTION func_name ([func_parameter[,...]])
    RETURNS type
    routine_body
    
func_parameter:
    param_name type

RETURNS：指定函数的返回值类型，并且函数体必须包含RETURN value 语句。

使用函数改造过程例子：

delimiter //
CREATE FUNCTION S_T.func_test1(SageParam SMALLINT)
RETURNS INT DETERMINISTIC
BEGIN 
DECLARE SNum INT;
SELECT COUNT(*) INTO SNum FROM Student WHERE Sage > SageParam;
RETURN SNum;
END//
delimiter ;

执行存储函数

SELECT func_name

查看存储函数

查看存储函数状态

SHOW FUNCTION STATUS [LIKE 'pattern' | WHERE expr]

查看某个存储函数定义

SHOW CREATE FUNCTION func_name

删除存储函数

DROP FUNCTION [IF EXISTS] func_name

复合语句语法

BEGIN ... END

[begin_label:] BEGIN
    [statement_list]
END [end_label]

BEGIN ... END语法用于编写复合语句，可以出现在存储程序(存储过程和函数、触发器和事件)中。复合语句可以包含多个语句，这些语句由BEGIN和END关键字包围。statement_list表示一个或多个语句的列表，每个语句以分号(;)语句分隔符结束。statement_list本身是可选的，因此空的复合语句(BEGIN END)是合法的。

BEGIN ... END语句可以进行嵌套。

在所有存储程序中，解析器将BEGIN [WORK]作为一个BEGIN ... END块的开始，要在此上下文中开始事务，请使用START TRANSACTION。

Statement Labels

[begin_label:] BEGIN
    [statement_list]
END [end_label]

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

允许在BEGIN…END块并用LOOP、REPEAT和WHILE语句。这些语句的标签使用遵循以下规则:

  • begin_label必须跟一个冒号；
  • begin_label可以不指定end_label。如果end_label存在，它必须与begin_label相同；
  • 同一嵌套层上的标签必须是不同的；
  • 标签最长可达16个字符；

DECLARE Statement

DECLARE语句用于定义程序的各种本地项：

  • Local variables
  • Conditions and handlers
  • Cursors

DECLARE仅允许在BEGIN...END复合语句中，并且必须在其开始处，在任何其他语句之前。

声明必须遵循一定的顺序。cursor声明必须出现在handler声明之前，variable和condition声明必须出现在cursor或handler声明之前。

存储程序中的变量

系统变量和用户定义的变量可以在存储程序中使用，就像它们可以在存储程序上下文之外使用一样。此外，存储程序可以使用DECLARE来定义局部变量。

声明局部变量：

DECLARE var_name [, var_name] ... type [DEFAULT value]

变量可以直接用set语句设置：

SET local_var_name = expr [, local_var_name = expr] ...

delimiter //
CREATE PROCEDURE test001() 
BEGIN
DECLARE var_age smallint DEFAULT 0;
SET var_age = 10;
SELECT var_age;
END //
delimiter ;

查询的结果可以使用SELECT ... INTO var_list或打开游标并使用 FETCH ... INTO var_list

delimiter //
CREATE PROCEDURE test002() 
BEGIN
DECLARE var_age smallint DEFAULT 0;
SELECT Sage INTO var_age FROM Student WHERE Sno = '201215121';
SELECT var_age;
END //
delimiter ;

流程控制语句

CASE Statement
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

case_value是一个表达式。将此值与每个WHEN子句中的when_value表达式进行比较，直到其中一个相等。当找到一个相等的when_value时，执行相应的THEN子句statement_list。如果when_value不相等，则执行ELSE子句statement_list(如果有的话)。

delimiter //
CREATE PROCEDURE test003(IN x INT) 
BEGIN
CASE x / 2
	WHEN 1 THEN
		SELECT 'A';
	ELSE
		SELECT 'B';
END CASE;
END //
delimiter ;

或

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

每个WHEN子句search_condition表达式都被求值，直到其中一个为真，此时它对应的THEN子句statement_list就会执行。如果没有相等的search_condition，则执行ELSE子句statement_list(如果有的话)。

delimiter //
CREATE PROCEDURE test004(IN x INT) 
BEGIN
CASE 
	WHEN x > 0 THEN
		SELECT 'A';
	ELSE
		SELECT 'B';
END CASE;
END //
delimiter ;
IF Statement
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

delimiter //
CREATE PROCEDURE test005(IN x INT) 
BEGIN
IF x = 1 THEN
	SELECT x * 10;
	ELSEIF x = 2 THEN
	SELECT x * 20;
ELSE
		SELECT x * 30;
END IF;
END //
delimiter ;
ITERATE Statement
ITERATE label

ITERATE只能出现在LOOP、REPEAT和WHILE语句中。ITERATE的意思是“再次开始循环”。

LEAVE Statement
LEAVE label

此语句用于退出具有给定标签的流控制构造。如果标签是最外层存储的程序块，则LEAVE退出程序。

LEAVE可以使用在BEGIN…END或循环结构(LOOP, REPEAT, WHILE)中。

LOOP Statement
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP实现了一个简单的循环构造，允许重复执行语句列表。循环中的语句将重复执行，直到循环结束。通常，这是通过LEAVE语句完成的。在存储的函数中，还可以使用RETURN，它会完全退出函数。

delimiter //
CREATE PROCEDURE test006(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SELECT p1;
END //
delimiter ;
REPEAT Statement
[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT语句中的语句列表将被重复，直到search_condition表达式为真。因此，REPEAT总是至少进入循环一次。

RETURN Statement
RETURN expr

RETURN语句终止存储函数的执行，并将值expr返回给函数调用者。存储函数中必须至少有一个RETURN语句。

此语句不用于存储过程、触发器或事件。可以使用LEAVE语句退出这些类型的存储程序。

WHILE Statement
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

只要search_condition表达式为真，WHILE语句中的语句列表就会重复。

Cursors

MySQL支持存储程序中的游标。其语法与嵌入式SQL相同。游标具有以下属性:

  • Asensitive：服务器可能会也可能不会复制其结果表；
  • Read only: 不能修改；
  • Nonscrollable：只能在一个方向上遍历，不能跳过行；

游标声明必须出现在handler声明之前，variable and condition声明之后。

Cursor DECLARE Statement
DECLARE cursor_name CURSOR FOR select_statement

该语句声明一个游标，并将其与一个SELECT语句关联，该SELECT语句检索游标要遍历的行。

Cursor OPEN Statement
OPEN cursor_name

打开生命的游标。

Cursor FETCH Statement
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

该语句获取与指定游标(必须打开)关联的SELECT语句的下一行，并推进游标指针。如果存在一行，则获取的列存储在指定的变量中。SELECT语句检索的列数必须与FETCH语句中指定的输出变量数匹配。

如果没有更多的行可用，则出现一个No Data条件，SQLSTATE值为'02000'。要检测此条件，可以为其设置handler。

Cursor CLOSE Statement
CLOSE cursor_name

该语句关闭以前打开的游标，如果游标未打开，则发生错误。

如果未显式关闭，光标将在声明它的BEGIN ... END块的末尾关闭。

Condition Handling

在存储程序执行期间可能会出现需要特殊处理的情况，例如退出当前程序块或继续执行。可以为警告或异常等一般条件或特定条件（如特定错误代码）定义处理程序。特定条件可以指定名称，并在处理程序中以这种方式引用。

DECLARE ... CONDITION Statement
DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

DECLARE ... CONDITION语句声明一个命名错误条件，将名称与需要特定处理的条件相关联。该名称可在随后的DECLARE ... HANDLER statement中引用。

Condition声明必须出现在cursor声明或handler声明之前。

condition_value：指示要与条件名相关联的特定条件或条件类。

SIGNAL或RESIGNAL语句中引用的条件名称必须与SQLSTATE值关联，而不是与MySQL错误代码关联。

DECLARE ... HANDLER Statement
DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE| EXIT | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

DECLARE ...HANDLER语句指定处理一个或多个条件的处理程序。如果出现这些条件之一，则执行指定的语句。语句可以是一个简单的语句，如SET var_name=value，也可以是使用BEGIN和END编写的复合语句。

handler声明必须出现在变量或condition声明之后。

handler_action值指示处理程序在执行处理程序语句后采取的动作:

  • CONTINUE：继续执行当前程序；
  • EXIT：声明处理程序的BEGIN...END复合语句的执行终止。即使条件发生在内部块中，也是如此。
  • UNDO：不支持；

condition_value：激活处理程序的特定条件或条件类。

例：

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

更多请查看 https://dev.mysql.com/doc/refman/8.0/en/condition-handling.html



参考文献：
数据库系统概论-第5版[王珊，萨师煊]
https://dev.mysql.com/doc/refman/8.0/en/

site logo

Hi,Developer

© 2025 Bobby Blog