数据库原理——第八章:数据库编程

参考书目《数据库系统概论(第5版)》

嵌入式SQL

嵌入式SQL是将SQL语句嵌入程序设计语言中,被嵌入的语言如C、C++、Java等称为宿主语言,简称主语言
SQL语言负责操纵数据库,高级程序语言负责控制逻辑流程
数据库工作单元和源程序工作单元之间的通信主要包括:

  • 向主语言传递SQL语句的执行信息,使主语言能够据此信息控制程序流程,主要用SQL通信区实现
  • 主语言向SQL语句提供参数,主要用主变量实现(SQL语句中使用的主语言程序变量简称主变量
  • 将SQL语句查询数据库的结果交主语言处理,主要用*主变量游标实现

游标:游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。游标类似于数据缓冲区内的指针,用户可以通过游标逐一获取缓冲区内的数据


过程化SQL

过程化SQL(Procedural Language/SQL, PL/SQL),其基本结构是块

定义方式

1
2
3
4
5
6
DECLARE 变量、常量、游标、异常等
BEGIN
SQL语句、过程化SQL的流程控制语句
EXCEPTION
异常处理部分
END;

流程控制

  • IF语句
1
2
3
IF condition THEN
Sequence_of_statements;
END IF;
  • IF-THEN语句
1
2
3
4
5
IF condition THEN
Sequence_of_statements1;
ELSE
Sequence_of_statements2;
END IF;
  • LOOP语句
1
2
3
LOOP
Sequence_of_statements;
END LOOP;
  • WHILE-LOOP语句
1
2
3
WHILE condition LOOP
Sequence_of_statements;
END LOOP;
  • FOR-LOOP语句
1
2
3
FOR count IN [REVERSE] bound1 .. bound2 LOOP
Sequence_of_statements;
END LOOP;

存储过程和函数

过程化SQL块分为两种:命名块匿名块

  • 匿名块:每次执行时都要进行编译,它不能被存储到数据库中,也不能在其他过程化SQL块中调用
  • 命名块:过程和函数是命名块,它们被编译后保存在数据库中,称为持久性存储模块(Persistent Stored Module, PSM),可以被反复调用,运行速度较快

———后续语句实现基于MySQL———

存储过程

使用SQL语句书写,经过编译和优化后存储在数据库中,因此称为存储过程,使用时直接调用即可
存储过程的优点:

  • 语句已经经过优化,所其以运行效率高
  • 降低了客户机和服务器之间的通信量,只有最终的处理结果才会返回客户端
  • 方便实施企业规则
  1. 存储过程定义
    存储过程的通用格式如下

    1
    2
    3
    4
    5
    CREATE PROCEDURE <存储过程名> ([[参数模式 参数名 参数类型],...])
    [characteristics...]
    BEGIN
    <存储过程体>
    END

    其中characteristics指定存储过程特性,取值如下:

    • LANGUAGE SQL:说明body部分由SQL语句组成,LANGUAGE可选值只有SQL
    • [NOT] DETERMINISTIC:指明存储过程执行结果是否确定。默认值:NOT DETERMINISTIC
      • DETERMINISTIC:结果确定,每次执行存储过程时,相同的输入会得到相同的输出
      • NOT DETERMINISTIC:结果不确定,相同输入可能得到不同输出。
    • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指定子程序使用存储过程的限制。默认值:CONTAINS SQL
      • CONTAINS SQL:说明子程序包含SQL语句,但是不包含写数据语句
      • NO SQL:说明子程序不包含SQL语句
      • READS SQL DATA:说明子程序包含读数据读数据语句
      • MODIFIES SQL DATA:说明子程序包含写数据语句
    • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限执行,默认值:DEFINER
      • DEFINER:只有定义者才能执行
      • INVOKER:拥有权限的调用者才可以执行
    • COMMNET:注释信息

      如下定义了一个名叫exp6_1的存储过程,它的功能为如果没有名叫test的表,则创建一个test表

      1
      2
      3
      4
      5
      6
      7
      DELIMITER $  /*告诉mysql语句的结尾符号换成以$结束*/
      CREATE PROCEDURE exp6_1 ()
      BEGIN
      DROP TABLE IF EXISTS test;
      CREATE TABLE test(Num INT);
      END $
      DELIMITER ; /*将结尾符号换回以;结束*/
  2. 存储过程运行
    存储过程的运行的通用格式如下

    1
    2
    /*存储过程的调用只能用CALL*/
    CALL <存储过程名>([参数,...]);

    调用存储过程exp6_1

    1
    2
    3
    /*调用exp6_1*/
    CALL exp6_1();
    SHOW TABLES;

    可以看到存储过程exp6_1创建了test表,如下图所示

  3. 存储过程更名
    由于MySQL并不支持存储过程更名,只能删掉并重新建立存储过程,这里简单介绍一下修改存储过程内容的过程
    修改存储过程的通用格式如下

    1
    2
    3
    /*使用ALTER存储过程*/
    ALTER PROCEDURE <存储过程名>
    [characterustic...];

    其中characteristics指定存储过程特性,取值如下:

    • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指定子程序使用存储过程的限制。
      • CONTAINS SQL:说明子程序包含SQL语句,但是不包含写数据语句
      • NO SQL:说明子程序不包含SQL语句
      • READS SQL DATA:说明子程序包含读数据读数据语句
      • MODIFIES SQL DATA:说明子程序包含写数据语句
    • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限执行,默认值:DEFINER
      • DEFINER:只有定义者才能执行
      • INVOKER:拥有权限的调用者才可以执行
    • COMMNET:注释信息
  4. 存储过程删除
    存储过程删除的通用格式如下

    1
    2
    /*存储过程的删除使用DROP*/
    DROP PROCEDURE <存储过程名>;

    如下展示了如何删除我们之前定义的存储过程exp6_1

    1
    2
    /*删除存储过程exp6_1*/
    DROP PROCEDURE exp6_1;
  5. 存储过程的参数传递
    我们假定这样一个场景,设计一个LowerBound的存储过程,它的功能是统计之前定义的test表中比输入的参数的值大的数据的个数,并将统计结果和该参数以及插入时间存入到counts表中
    (1)首先我们创建counts表

    1
    2
    /*创建counts表*/
    CREATE TABLE counts(Total INT, Boud INT, TimeInsert DATETIME);

    (2)然后我们创建LowerBound存储过程,它的输入参数只有一个bound参数,模式为IN,类型为INT

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    /*创建LowerBound存储过程*/
    DELIMITER $
    CREATE PROCEDURE LowerBound(IN bound INT)
    BEGIN
    INSERT INTO counts VALUES (
    (SELECT COUNT(*) FROM test WHERE Num>bound),
    bound,
    CURRENT_TIME()
    );
    END $
    DELIMITER ;

    (3)之后我们调用LowerBound存储过程,bound值传入为150

    1
    2
    /*调用LowerBound存储过程*/
    CALL LowerBound(150);

    (3)最后我们查看一下调用LowerBound的结果,如果没有出错,统计结果应该为49

    1
    2
    /*查看LowerBound的调用结果*/
    SELECT * FROM counts;

    可以看到统计结果为49,边界值Bound为150,插入时间如图所示

函数

  1. 自定义函数定义
    定义函数的通用格式如下

    1
    2
    3
    4
    5
    CREATE FUNCTION <函数名> ([[参数名 参数类型],...]) RETURNS <返回值类型>
    [characteristics...]
    BEGIN
    <函数体>
    END

    注意自定义函数与存储过程的区别之一在于函数需要函数体内写RETURN值。区别之二在于,存储过程的参数可有INOUTINOUT模式,而函数的参数没有
    其中characteristics指定函数特性,取值如下:

    • LANGUAGE SQL:说明body部分由SQL语句组成,LANGUAGE可选值只有SQL
    • [NOT] DETERMINISTIC:指明函数执行结果是否确定。默认值:NOT DETERMINISTIC
      • DETERMINISTIC:结果确定,每次执行函数时,相同的输入会得到相同的输出
      • NOT DETERMINISTIC:结果不确定,相同输入可能得到不同输出。
    • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指定子程序使用函数的限制。默认值:CONTAINS SQL
      • CONTAINS SQL:说明子程序包含SQL语句,但是不包含写数据语句
      • NO SQL:说明子程序不包含SQL语句
      • READS SQL DATA:说明子程序包含读数据读数据语句
      • MODIFIES SQL DATA:说明子程序包含写数据语句
    • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限执行,默认值:DEFINER
      • DEFINER:只有定义者才能执行
      • INVOKER:拥有权限的调用者才可以执行
    • COMMNET:注释信息

      如下定义了一个名叫exp6_2的函数,它的功能为向test表中插入给定参数n往后的100个整数,并返回最终停止累加时的数字

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      DELIMITER $  /*告诉mysql语句的结尾符号换成以$结束*/
      CREATE FUNCTION exp6_2 (P1 INT) RETURNS INT
      BEGIN
      DECLARE tmp INT DEFAULT P1+100;
      WHILE P1 < tmp DO
      INSERT INTO test VALUES (P1);
      SET P1 = P1 + 1;
      END WHILE;
      RETURN tmp;
      END $
      DELIMITER ; /*将结尾符号换回以;结束*/
  2. 自定义函数运行
    自定义函数运行的通用格式如下

    1
    2
    /*自定义函数的调用只能用SELECT*/
    SELECT <自定义函数名>([参数,...]);

    如下展示了如何调用我们之前定义的函数exp6_2
    接着调用自定义函数exp6_2

    1
    2
    3
    /*调用exp6_2*/
    SELECT exp6_2(100);
    SELECT * FROM test;

    可以看到调用自定义函数exp6_2的返回值为200,因为是从100开始往后的100个数

    最后累加到199停止,共100行,累加停止时的数为200

  3. 自定义函数更名
    由于MySQL并不支持函数更名,只能删掉并建立新的函数,这里简单介绍一下修改函数内容的过程
    修改函数的通用格式如下

    1
    2
    3
    /*使用ALTER修改函数*/
    ALTER FUNCTION <函数名>
    [characterustic...];

    其中characteristics指定函数特性,取值如下:

    • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指定子程序使用函数的限制。
      • CONTAINS SQL:说明子程序包含SQL语句,但是不包含写数据语句
      • NO SQL:说明子程序不包含SQL语句
      • READS SQL DATA:说明子程序包含读数据读数据语句
      • MODIFIES SQL DATA:说明子程序包含写数据语句
    • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限执行,默认值:DEFINER
      • DEFINER:只有定义者才能执行
      • INVOKER:拥有权限的调用者才可以执行
  4. 自定义函数删除
    自定义函数删除的通用格式如下

    1
    2
    /*自定义函数的删除使用DROP*/
    DROP PROCEDURE <函数名>;

    如下展示了如何删除我们之前定义的自定义函数exp6_2

    1
    2
    /*删除自定义函数exp6_2*/
    DROP FUNCTION exp6_2;
  5. 自定义函数的参数传递
    我们设计一个A+B函数,功能是输入两个INT类型的值,返回值为这两个输入参数的和
    (1)首先创建自定义函数A+B,函数名为AplusB,输入参数为两个INT类型的参数P1和P2,返回值也为INT类型

    1
    2
    3
    4
    5
    6
    7
    8
    9
    /*创建自定义函数AplusB*/
    DELIMITER $
    CREATE FUNCTION AplusB (P1 INT, P2 INT) RETURNS INT
    BEGIN
    DECLARE result INT;
    SET result = P1 + P2;
    RETURN result;
    END $
    DELIMITER ;

    (2)调用AplusB并查看结果

    1
    2
    /*调用AplusB函数*/
    SELECT AplusB(10, 20);

    可以看到相加结果为10+20=30,结果正确

游标

  1. 游标定义
    游标简介:

    • 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
    • 游标充当指针的作用。
    • 尽管游标能遍历结果中的所有行,但它一次只指向一行。
    • 游标的作用就是用于对查询数据库所返回的记录进行遍历,以便进行相应的操作。
    • 游标必须声明在处理程序之前,并且声明在变量和条件之后

      MySQL中使用DECLARE来声明游标,通用格式如下

      1
      DECLARE <游标名字> CURSOR FOR <SELECT语句>;

      我们如下创建一个名为exp6_3的游标,它的功能是选出那些在test表中Num值比170大的元组

      1
      2
      DECLARE exp6_3 CURSOR FOR
      SELECT 数量 FROM test WHERE Num>170;
  2. 游标使用
    由于游标需要配合存储过程或自定义函数来使用,这里我们创建一个存储过程Transform,设计一个游标,将test表中Num比bound参数大的元组插入到另一个表test2中
    详细的运行过程已经以注释的形式写在代码中

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    DELIMITER $
    CREATE PROCEDURE Transform(IN bound INT)
    BEGIN
    DECLARE tmp INT;
    DECLARE done int;
    /*声明游标exp6_3*/
    DECLARE exp6_3 CURSOR FOR SELECT Num FROM test WHERE Num>bound;
    /*游标中的内容执行完后将done设置为1*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    /*打开游标*/
    OPEN exp6_3;
    /*使用REPEAT循环从游标中读取数据*/
    REPEAT
    FETCH exp6_3 INTO tmp;
    INSERT INTO test2 VALUES (tmp);
    UNTIL done END REPEAT;
    /*关闭游标*/
    CLOSE exp6_3;
    END $
    DELIMITER ;

    之后我们调用存储过程Transform

    1
    CALL Transform(190);

    最后我们检查运行结果,查看表test2中的内容

    1
    SELECT * FROM test2;

    可以看到该运行结果符合我们的预设的功能


ODBC编程

开放数据库连接(Open Database Connectivity,ODBC)是为解决异构数据库间的数据共享而产生的,它建立了一组规范,并提供了一组访问数据库的应用程序编程接口(API)
ODBC具有两重约束力:一方面规范应用开发,另一方面规范关系数据库管理系统应用接口
ODBC有4部分:用户驱动程序管理器、数据库驱动程序管理器、数据库驱动程序、数据源