数据库编程

SQL编程技术可以有效克服SQL语言实现复杂应用方面的不足，提高应用系统和数据库管理系统间的互操作性。

嵌入式SQL（Embedded SQL,ESQL）

嵌入式SQL的处理过程

嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言。

处理过程：预编译方法 由数据库管理系统的预处理程序对源程序进行扫描，识别出嵌入式SQL语句，把它们转换成主语言调用语句，以使主语言编译程序能识别它们，然后由主语言的编译程序将纯的主语言程序编译成目标码。

为了区分SQL语句与主语言语句，所有SQL语句都必须加前缀。

• 例如当主语言为C语言时，语法格式为

EXEC SQL<SQL语句>

• 例如当主语言为Java时，语言格式为

#SQL{<SQL语句>}

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

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

SQL通信区（SQL Communication Area）

SQLCA的用途

• SQL语句执行后，DBMS反馈给应用程序信息

  • 描述系统当前工作状态

  • 描述运行环境

• 这些信息将送到SQL通信区中

• 应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句

SQLCA的使用方法

• 定义SQLCA

EXEC SQL INCLUDE SQLCA

• 使用SQLCA

  • SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE

  • 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错

  • 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理

主变量

在SQL语句中使用的主语言程序变量简称为主变量。

主变量的类型

• 输入主变量 由应用程序对其赋值，SQL语句引用

• 输出主变量 由SQL语句赋值或设置状态信息，返回给应用程序

一个主变量有可能既是输入主变量又是输出主变量

指示变量

• 一个主变量可以附带一个指示变量（Indicator Variable）

• 什么是指示变量

  • 整型变量

  • 用来“指示”所指主变量的值或条件

• 指示变量的用途

  • 输入主变量可以利用指示变量赋空值

  • 输出主变量可以利用指示变量检测出是否空值，值是否被截断

在SQL语句中使用主变量和指示变量的方法

• 说明主变量和指示变量

BEGIN DECLARE SECTION
	......... 
	.........     (说明主变量和指示变量)
	.........
END DECLARE SECTION

• 使用主变量

  • 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现

  • 为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志

• 使用指示变量

  • 指示变量前也必须加冒号标志

  • 必须紧跟在所指主变量之后

• 在SQL语句之外(主语言语句中)使用主变量和指示变量的方法

  • 可以直接引用，不必加冒号

游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

建立和关闭数据库连接

• 建立数据库连接

EXEC SQL CONNECT TO target[AS connection-name][USER user-name]; 

• target是要连接的数据库服务器

• 关闭数据库连接

EXEC SQL DISCONNECT [connection];

动态SQL

1. 静态嵌入式SQL

• 用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。

• 语句中主变量的个数与数据类型在预编译时都是确定的，只有是主变量的值是程序运行过程中动态输入的。

1. 动态嵌入式SQL

• 动态SQL方法允许在程序运行过程中临时“组装”SQL语句。

过程化SQL（Procedural Language/SQL,PL/SQL）

过程化SQL的块结构

过程化SQL块的基本结构

1. 定义部分

• DECLARE 变量、常量、游标、异常等

  • 定义的变量、常量等只能在该基本块中使用

  • 当基本块执行结束时，定义就不再存在

1. 执行部分

BEGIN
  /*SQL语句、过程化SQL的流程控制语句*/
EXCEPTION
  /*异常处理部分*/
END;

变量和常量的定义

• 变量定义

变量名 数据类型 [[NOT NULL]:=初值表达式]
变量名 数据类型 [[NOT NULL]初值表达式]

• 常量定义

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

• 赋值语句

变量名:=表达式

流程控制

• 条件控制结构

  • IF-THEN

  • IF-THEN-ELSE

  • 嵌套的IF语句

  • CASE语句

WHEN <常量1> THEN 语句序列1
……
WHEN <常量n> THEN 语句序列n
ELSE THEN 语句序列n+1
END CASE;

• 循环控制结构

  • LOOP-EXIT(EXIT WHEN)-END LOOP

  • WHILE-LOOP

  • FOR-LOOP

• 错误处理

存储过程和函数

过程化SQL块类型

• 命名块 编译后保存在数据库中，可以被反复调用，运行速度较快，过程和函数是命名块

• 匿名块 每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用

存储过程

存储过程是由一组SQL语句组成，预先编译后存储在数据库中，可供应用程序多次调用，类似于高级语言中的函数。 优点

• 提高了效率

• 降低了客户机与服务器之间的通信量

• 方便实施企业规划

存储过程的用户接口：

1. 创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...])
AS <过程化SQL块>

1. 执行存储过程

CALL/PREFORM PROCEDURE 过程名([参数1,参数2,...])

1. 修改存储过程

ALTER PROCEDURE 过程1 RENAME TO 过程2;
ALTER PROCEDURE 过程名 COMPILE;  /*重新编译一个存储过程*/

1. 删除存储过程

DROP PROCEDURE 过程名();

函数

函数和存储过程的异同

• 同：都是持久性存储模块

• 异：函数必须指定返回的类型

1. 函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> 
AS <过程化SQL块>;

1. 函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,…]);

1. 修改函数

• 重命名

ALTER FUNCTION 过程名1 RENAME TO 过程名2;

• 重新编译

ALTER FUNCTION 过程名 COMPILE;

ODBC编程（Open Data Base Connectivity）

ODBC优点：

• 移植性好

• 能同时访问不同的数据库

• 共享多个数据资源

ODBC概述

ODBC产生的原因：

• 由于不同的数据库管理系统的存在，在某个RDBMS下编写的应用程序就不能在另一个RDBMS下运行

• 许多应用程序需要共享多个部门的数据资源，访问不同的RDBMS

ODBC：

• 是微软公司开放服务体系(Windows Open Services Architecture，WOSA)中有关数据库的一个组成部分

• 提供了一组访问数据库的标准API

ODBC约束力：

• 规范应用开发

• 规范RDBMS应用接口

ODBC工作原理概述

ODBC应用系统的体系结构 由4部分构成：用户应用程序、ODBC驱动程序管理器、数据库驱动程序、数据源（如关系数据库管理系统和数据库）。

用户应用程序

ODBC应用程序包括的内容:

• 请求连接数据库；

• 向数据源发送SQL语句；

• 为SQL语句执行结果分配存储空间，定义所读取的数据格式；

• 获取数据库操作结果，或处理错误；

• 进行数据处理并向用户提交处理结果；

• 请求事务的提交和回滚操作；

• 断开与数据源的连接。

ODBC驱动管理器

主要功能：

• 装载ODBC驱动程序

• 选择和连接正确的驱动程序

• 管理数据源

• 检查ODBC调用参数的合法性

• 记录ODBC函数的调用等

数据库驱动程序

ODBC通过驱动程序来提供应用系统与数据库平台的独立性

ODBC应用程序不能直接存取数据库

• 其各种操作请求由驱动程序管理器提交给某个RDBMS的ODBC驱动程序

• 通过调用驱动程序所支持的函数来存取数据库。

• 数据库的操作结果也通过驱动程序返回给应用程序。

• 如果应用程序要操纵不同的数据库，就要动态地链接到不同的驱动程序上。

ODBC驱动程序类型：

• 单束

  • 数据源和应用程序在同一台机器上

  • 驱动程序直接完成对数据文件的I/O操作

  • 驱动程序相当于数据管理器

• 多束

  • 支持客户机/服务器、客户机/应用服务器/数据库服务器等网络环境下的数据访问

  • 由驱动程序完成数据库访问请求的提交和结果集接收

  • 应用程序使用驱动程序提供的结果集管理接口操纵执行后的结果数据

ODBC数据源管理

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

数据源对最终用户是透明的

• ODBC给每个被访问的数据源指定唯一的数据源名（Data Source Name，简称DSN）,并映射到所有必要的、用来存取数据的低层软件

• 在连接中，用数据源名来代表用户名、服务器名、所连接的数据库名等

• 最终用户无需知道DBMS或其他数据管理软件、网络以及有关ODBC驱动程序的细节

ODBC API 基础

ODBC应用程序接口的一致性

• API一致性 API一致性级别有核心级、扩展1级、扩展2级

• 语法一致性 语法一致性级别有最低限度SQL语法级、核心SQL语法级、扩展SQL语法级

函数概述

ODBC 3.0 标准提供了76个函数接口，大致可分为：

• 分配和释放环境句柄、连接句柄、语句句柄；

• 连接函数（SQLDriverconnect等）；

• 与信息相关的函数（如获取描述信息函数SQLGetinfo、SQLGetFuction）；

• 事务处理函数（如SQLEndTran）；

• 执行相关函数（SQLExecdirect、SQLExecute等）；

• 编目函数，ODBC 3.0提供了11个编目函数如SQLTables、SQLColumn等，应用程序可以通过对编目函数的调用来获取数据字典的信息如权限、表结构等

句柄及其属性

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

ODBC 3.0中句柄分类：

• 环境句柄

• 连接句柄

• 语句句柄

• 描述符句柄

应用程序句柄之间的关系：

1. 每个ODBC应用程序需要建立一个ODBC环境，分配一个环境句柄，存取数据的全局性背景如环境状态、当前环境状态诊断、当前在环境上分配的连接句柄等；

2. 一个环境句柄可以建立多个连接句柄，每一个连接句柄实现与一个数据源之间的连接；

3. 在一个连接中可以建立多个语句句柄，它不只是一个SQL语句，还包括SQL语句产生的结果集以及相关的信息等；

4. 在ODBC 3.0中又提出了描述符句柄的概念，它是描述SQL语句的参数、结果集列的元数据集合。

数据类型

ODBC数据类型：

• SQL数据类型：用于数据源

• C数据类型 ：用于应用程序的C代码

SQL数据类型和C数据类型之间的转换规则

|SQL数据类型|C数据类型 -|-----------|--------- SQL数据类型|数据源之间转换|应用程序变量传送到语句参数（SQLBindparameter） C数据类型|从结果集列中返回到应用程序变量（SQLBindcol）|应用程序变量之间的转换

ODBC的工作流程

ODBC的工作流程：

《数据库系统概论（第5版）》王珊 萨师煊 著