【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程

程序员文章站 2024-03-15 18:07:06

...

第8章：数据库编程

SQL缺少流程控制能力，难以实现业务应用中的逻辑控制；SQL编程技术可以有效地克服SQL语言实现复杂应用方面的不足，提高应用系统和数据库管理系统之间的互操作性。

使用SQL编程来访问和管理数据库中数据的方式主要有：嵌入式SQL（ESQL），过程化SQL（PL/SQL），储存过程和自定义函数，开放数据库连接（ODBC）等。。。

8.1 嵌入式SQL

SQL的特点之一就是可以在交互式和嵌入式两种不同的使用方式下工作。

8.1.1 嵌入式SQL的处理过程

被嵌入的语言，如Java，被称为宿主语言。

嵌入式SQL的基本处理过程：

【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程

在嵌入式SQL中，为了能够快速区分SQL语言与主语言，所有SQL语句都必须加前缀，对于C语言，格式为：

EXEC SQL </*SQL语句*/>;

对于Java，格式为：

# SQL {</*SQL语句*/>};

8.1.2 嵌入式SQL与主语言之间的通信

数据库工作单元与源程序工作单元之间的通信主要包括：

向主语言传递SQL语句的执行状态信息，给主语言提供控制流程的依据，这部分主要由SQL通信区（SQLCA）来实现
主语言向SQL语句提供参数，主要用主变量（host variable）实现
将SQL语句的查询结果提交给主语言处理，主要用主变量和游标实现（cursor）

SQL通信区

SQL语句执行过后，需要返回主程序若干信息，包括当前的工作状态和各种运行环境的数据等，这些信息将被送到SQL通信区中，应用程序将从SQL通信区中读取这些信息作为流程控制的依据。

SQL通信区使用exec sql include sqlca加以定义，其中有一个变量sqlcode，用来存放每次执行SQL之后返回的代码。

应用程序每执行一次SQL就应检查一下sqlcode的值（success，其他）
主变量

嵌入式SQL中可以使用主语言程序中的变量来输入或者输入数据；

SQL语句中使用的主语言程序变量称为主变量；

根据作用不同分为输入主变量（主程序赋值，SQL引用）和输出主变量（同理）；

一个主变量可以附带一个任选的指示变量（indicator variable），为一个整数变量，用来指示其所属的主变量的值或者条件，例如只是或者检测主变量是否为空；

所有主变量必须在begin decalre section和end declare section之间进行说明，说明之后，主变量就可以在SQL语句中任何可以使用表达式的地方出现，为了与数据库对象名（表名，列名等）区分，嵌入式SQL语句中的主变量和指示变量都要在前面加上冒号；
游标

SQL语言面向集合，一条SQL语句可以返回多条记录；
主语言一般是面向记录的，一次只能处理一条记录；

为了协调，ESQL引入了游标的概念；

游标是系统为用户开设的一个数据缓冲区，存放SQL的执行结果，每一个游标区都有自己的名字，用户可以通过游标逐一获得记录并进行处理。
建立和关闭数据库连接
- 建立数据库连接
```
exec sql connect to target[AS connection-name][USER user-name]
```
  target：要连接的数据库服务器，可以是像<dbname>@<hostname>:<port>一样的服务器表示串，还可以是DEFAULT，还可以是包含服务器标识的SQL串常量。
  
  connection-name：可选的连接名，用来识别程序内建立的多个数据库连接，如果程序内只有一个数据库连接，也可以不指定。
- 关闭数据库连接
```
exec sql disconnect[connection];
```

程序实例

EXEC SQL BEGIN DECLARE SECTION; /*主变量说明开始*/
char Deptname[20];
char Hsno[9];
char Hsname[20];
char Hssex[2];
int HSage;
int NEWAGE;
EXEC SQL END DECLARE SECTION; /*主变量说明结束*/
long SQLCODE;
EXEC SQL INCLUDE SQLCA; /*定义SQL通信区*/

int main(void) /*C语言主程序开始*/
{
    int count = 0;
    char yn; /*变量yn代表yes或no*/

    printf("Please choose the department name(CS/MA/IS): ");
    scanf("%s", deptname); /*为主变量deptname赋值*/

    EXEC SQL CONNECT TO TEST @localhost : 54321 USER "SYSTEM" / "MANAGER"; /*连接数据库TEST*/
    EXEC SQL DECLARE SX CURSOR FOR /*定义游标SX*/
        SELECT Sno,
        Sname, Ssex, Sage /*SX对应的语句*/
        FROM Student 
        WHERE SDept = :deptname;

    EXEC SQL OPEN SX; /*打开游标SX，指向查询结果的第一行*/

    for (;;)          /*用循环结构逐条处理结果集中的记录*/
    {
        EXEC SQL FETCH SX INTO :HSno, :Hsname, :HSsex, :HSage;

        /*推进游标，将当前数据放入主变量*/
        if (SQLCA.SQLCODE != 0) /*SQLCODE != 0，表示操作不成功*/
            break;              /*利用SQLCA中的状态信息决定何时退出循环*/

        if (count++ == 0)       /*如果是第一行的话，先打出行头*/
            printf("\n%-10s %-20s %-10s %-10s\n", "Sno", "Sname", "Ssex", "Sage");

        printf("%-10s %-20s %-10s %-10d\n", "HSno", "Hsname", "Hssex", "HSage"); /*打印查询结果*/

        printf("UPDATE AGE(y/n)?"); /*询问用户是否要更新该学生的年龄*/
        do
        {
            scanf("%c", &yn);
        } while (yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
        if (yn == 'y' || yn == 'Y') /*如果选择更新操作*/
        {
            printf("INPUT NEW AGE:");
            scanf("%d", &NEWAGE);   /*用户输入新年龄到主变量中*/
            EXEC SQL UPDATE Student /*嵌入式SQL更新语句*/
                SET Sage = :NEWAGE
                WHERE CURRENT OF SX;
        } /*对当前游标指向的学生年龄进行更新*/
    }
    EXEC SQL CLOSE SX;        /*关闭游标SX，不再和查询结果对应*/
    EXEC SQL COMMIT WORK;     /*提交更新*/
    EXEC SQL DISCONNECT TEST; /*断开数据库连接*/
}

8.1.3 不用游标的SQL语句

以下ESQL语句不需要使用游标：

说明性语句
数据定义语句
数据控制语句
查询结果为单条记录的SELECT语句
非CURRENT形式的增删改语句

查询结果为单条记录的SELECT语句

因为查询结果只有一个，只需要一个into子句指定存放查询结果的主变量，无需游标。

注意事项：
1. into,where,having中的条件表达式均可以使用主变量；
2. 为了应对查询结果为空值的情况，会使用到指示变量，当出现空值的时候，指示变量会被赋值负值。
3. 如果实际结果不为单条记录，将导致错误
非CURRENT形式的增删改语句

（啥是current形式自行百度解决吧。。。）
如果是非current形式的增删改语句，无需使用游标，在update的set子句和where子句中均可以使用主变量，set子句还可以使用指示变量。

8.1.4 使用游标的SQL语句

必须使用游标的SQL语句包括：

查询结果为多条记录的select语句；

使用游标机制将查询结果一条一条的送给主程序处理；
1. 说明游标
```
exec sql declare <游标名> cursor for <select 语句>;
```
  此时并不会执行SQL语句；
2. 打开游标
```
exec sql open <游标名>;
```
  执行相应的select语句，并将结果放置到缓冲区。
3. 推进游标指针并获取当前的记录
```
exec sql fetch <游标名>
    into <主变量>[<指示变量>][,<指示变量>]...;
```
4. 关闭游标
```
exec sql close <游标名>;
```
  游标被关闭后就不再与原来的查询结果相关联；
  
  被关闭的游标可以被再次打开；
current形式的update和delete语句；

在update和delete中使用如下的子句：
```
where current of <游标名>;
```

8.1.5 动态SQL

前文使用的嵌入式SQL中，主变量、查询目标列等都是固定的，属于静态SQL。

如果某些应用需要等到执行时才能够确定需要提交的SQL语句，需要用到动态SQL。

动态SQL支持动态组装SQL和动态参数两种形式。

使用SQL语句主变量

程序主变量包含的内容是SQL语句的内容，称为SQL语句主变量。
动态参数

使用(?)表示SQL语句中的可变元素。

需要通过prepare语句来准备主变量或者execute语句来绑定数据。

（具体用法见教材）

过程化SQL

8.2.1 过程化SQL的快结构

基本的SQL是高度非过程化的语言；

嵌入式SQL借助高级语言实现了过程化；

过程化SQL是对SQL的扩展，使其增加了过程化语句的功能；

过程化SQL程序的基本结构是块，所有的过程化SQL程序都是由块组成的；

块之间可以相互嵌套，每个块完成一个逻辑操作；

8.2.2 变量和常量的定义

变量定义

变量名 数据类型 [[not null] := 初值表达式];
变量名 数据类型 [[not null] 初值表达式];

常量的定义

常量名 数据类型 constant := 常量表达式;

必须要赋初值。

赋值语句
```
变量名 := 表达式；
```

8.2.3 流程控制

条件控制语句

if condition then
    statement1;
else
    statement2;
end if;

if语句之间可以嵌套

循环控制语句

最简单的loop语句
```
loop
    some_statement;
end loop;
```

while-loop语句

while condition loop
    some_statement
end loop;

for-loop语句

for count in [reverse] bound1 .. bound2 loop
    some_statement
end loop;

错误处理

教材：请读者根据具体情况处理

8.3 存储过程和函数

过程化SQL主要有匿名块和命名块两种类型；

前面介绍的是匿名块，每次执行时都需要编译，不会被保存、调用；

过程和函数是命名块，它们被编译和保存在数据库中，称为持久性储存模块，可以被反复调用；

8.3.1 存储过程

存储过程由过程化SQL书写，被编译和优化后储存在数据库中，使用时只需要调用；

存储过程的优点
1. 运行效率高
2. 降低了客户端和服务器端之间的通信量（只需要发送存储过程的名字和参数）
3. 方便实施企业规则

存储过程的用户接口

创建存储过程

create or replace procedure 过程名 ([参数1, 参数2,...])
as <过程化SQL块>;

执行存储过程

call / perform procedure 过程名 ([参数列表])

重命名一个存储过程

alter procedure name_1 rename to name_2;

重新编译一个存储过程
```
alter procedure 过程名 compile;
```
删除存储过程
```
drop procedure 过程名;
```

8.3.2 函数

也称为自定义函数；

函数的定义与存储过程类似，但是函数必须指定返回类型；

函数的定义语句格式

create or replace function 函数名 ([param_1, param_2, ...]) returns <data_type>
as <过程化SQL块>;

函数的执行语句格式
```
call / select 函数名 ([param_1, param_2, ...]);
```
这么久了，还没提过这些方括号、尖括号在代码里啥意思，这里放个链接，以防有人不知道
修改函数

可以重命名和重编译，和上面的存储过程一样；

8.4 ODBC编程

ODBC是啥自行百度

优点：

程序可移植性好
能同时访问不同的数据库
共享多个数据资源

8.4.1 ODBC概述

ODBC有两重功效或者说约束力：

规范应用程序开发
规范关系数据库管理系统应用接口

8.4.2 ODBC工作原理概述

【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程

用户应用程序

提供用户界面、应用逻辑、事务逻辑；

ODBC应用程序包含的内容有：
- 请求连接数据库
- 向数据源发送SQL语句
- 为SQL语句执行结果分配存储空间，定义所读取的数据格式
- 获取数据库操作结果或处理错误
- 进行数据处理并向用户提交处理结果
- 请求事务的提交和回滚操作
- 断开与数据源的连接
ODBC驱动程序管理器
- 包含在ODBC32.DLL中
- 管理应用程序和驱动程序之间的通信
- 建立、配置或删除数据源，并查看系统当前所安装的数据库ODBC驱动程序
数据库驱动程序

提供应用系统与数据库平台的独立性
ODBC数据源管理

数据源是是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，是一种数据连接的抽象；

8.4.3 ODBC API基础

各个数据库厂商的ODBC应用程序编程接口都要符合两方面的一致性：

API一致性
语法一致性

函数概述
- 分配和释放环境句柄、连接句柄、语句句柄
- 连接函数（SQLDriverconnect等）
- 与信息相关的函数（SQLGetinfo、SQLGetFuction等）
- 事务处理函数（如SQLEndTran）
- 执行相关函数（SQLExecdirect、SQLExecute等）
- 编目函数，ODBC 3.0提供了11个编目函数，如SQLTables、SQLColumn等。应用程序可以通过对编目函数的调用来获取数据字典的信息，如权限、表结构等
句柄及其属性

句柄是32位整数值，代表一个指针；

分为：
- 环境句柄
- 连接句柄
- 语句句柄
- 描述符句柄
1. 每个ODBC应用程序需要建立一个ODBC环境，分配一个环境句柄，存取数据的全局性背景，如环境状态、当前环境状态诊断、当前在环境上分配的连接句柄等；
2. 一个环境句柄可以建立多个连接句柄，每一个连接句柄实现与一个数据源之间的连接；
3. 在一个连接中可以建立多个语句句柄，它不只是一个SQL语句，还包括SQL语句产生的结果集以及相关的信息等；
4. 在ODBC 3.0中又提出了描述符句柄的概念，它是描述SQL语句的参数、结果集列的元数据集合；

【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程

数据类型

ODBC定义了两套数据类型，一套是SQL数据类型，一套是C数据类型，两者之间可以相互转换。

8.4.4 ODBC工作流程

【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程

上一篇： c++中继承知识点详解

下一篇：第四周作业

【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程

第8章：数据库编程

8.1 嵌入式SQL

8.1.1 嵌入式SQL的处理过程

8.1.2 嵌入式SQL与主语言之间的通信

8.1.3 不用游标的SQL语句

8.1.4 使用游标的SQL语句

8.1.5 动态SQL

过程化SQL

8.2.1 过程化SQL的快结构

8.2.2 变量和常量的定义

8.2.3 流程控制

8.3 存储过程和函数

8.3.1 存储过程

8.3.2 函数

8.4 ODBC编程

8.4.1 ODBC概述

8.4.2 ODBC工作原理概述

8.4.3 ODBC API基础

8.4.4 ODBC工作流程

【课堂笔记】《数据库系统概论（第5版）》-第7章：数据库设计

【课堂笔记】《数据库系统概论（第5版）》-第8章：数据库编程