数据库编程

2017-11-27 本文已影响15人 iOS_愛OS

数据库编程

嵌入式 SQL

嵌入式 SQL 的处理过程
将 SQL 语句嵌入到程序设计语言中，如 C,C++,Java

为了能区分 SQL 语句与主语言语句，所有 SQL 语句都必须加前缀 EXEC SQL, 以（；）结束成为一个程序片段
EXEC SQL <SQL 语句>;

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

向主语言床底 SQL 语句的执行状态信息，使主语言能够据此信息控制程序流程，主要用 SQL 通信区（SQL communication Area ，简称 SQLCA）实现
主语言向SQL 语句提供参数，主要用主变量（Host Variable）实现
将 SQL 语句查询数据库的结果交主语言处理，主要用主变量和游标（Cursor）实现。

SQL 通信区
SQLCA 中有一个变量 SQLCODE ，用来存放每次执行 SQL 语句后返回的代码
应用程序每执行完一条 SQL 语句之后都应该测试一下 SQLCODE 的值，以了解该 SQL 语句执行情况并做相应处理，如果SQLCODE 等于预定于的常量 SUCCESS, 则表示 SQL 语句成功，否则 SQLCODE 存放错误代码，程序员可以根据错误代码查找问题。
主变量
SQL 语句中使用的主语言程序变量称为主变量
输入主变量：应用程序对其赋值，SQL 语句引用
输出主变量：由SQL 语句对其赋值或设置状态信息，返回给应用信息
指示变量：属于整形变量，用来“指示”所指变量的值或者条件。可以指示输入主变量是否为空值，可以检测输出主变量是否为空值，值是否被截断。

主变量和指示变量的表现形式：
:变量名
游标
游标是系统为用户开设一个数据缓冲区，存放 SQL 语句的执行结果，每个游标区都有一个名字，用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理
建立和关闭数据库链接
建立链接语句
EXEC SQL CONNECT TO target[AS connection-name,[USER user-name]]
target : 数据服务器
connect-name 可选链接名，链接必须是一个有效的标识符，主要用来识别一个程序内同时建立的多个链接，如果在整个程序内只有一个链接也可以不指定链接名。
关闭数据库链接
EXEC SQL DISCONNECT [connection]；

程序示实例
例: 一次检查某个系的学生记录，交互式更新某些学生年龄

EXEC SAL BEGIN DECLARE SECTION; /* 主变量说明开始*/
char deptname[20];
char HSno[9];
char HSage;
int NEWAGE;
EXEC SQL END DECLARE SECTION /* 主变量说明结束 */
long SQLCODE;
EXEC SQL INCLUDE sqlea; /* 定义 SQL 通信区 */
int main(void)          /* C 语言主程序开始 */
{
int count =0;
char yn;                /* 变量 yn 代表 yes 或 no */
printf("Please choose the department name(CS/MA/IS):");
sacnf("%s",&deptname);  /* 为主变量 deptname 赋值 */
EXEC SQL CONNECT TO TEST@localhost:54321 USER "SYSYTEM"/"MANACER" /* 链接数据库 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,:HSno,:HSname,:HSnex,:HSage; /* 推进游标，将当前数据放入主变量 */
    if (sqlea,sqlcode != 0)         /* sqlcode != 0 表示操作不成功 */
        break;                      /* 利用 SQLCA 中的状态信息决定何时退出循环 */

    if (count ++==0)                /* 如果是第一行的话，先打出行头 */
    printf("\n %-10s %20s  % -10s% -10s\n","Sno","Sname","Sage");
    print("%-10s% - 20s% - 10s % - 10d\n",Hsno,HSname,HSex,HSage); /* 打印查询结果 */
    pintf("UPDATE ACE(y/n)?");  /* 询问用户是否要更新该学生的年龄 */
    do{
    scanf(%c,&yn);
    }
    while(yn!='N'&&yn!="n"&& YN!= 'Y'&&yn!="y");
    if(yn=='y'||yn=="Y")/* 如果选择更新操作 */
    {
        printf("INPUT NEW ACE:");
        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;   /* 断开数据库链接 */

不用游标的 SQL 语句

不需要使用游标的 SQL 语句：说明性语句、数据定义语句、数据控制语句、查询结果为单记录的SELECT 语句、非 CURRENT 形式的增删改语句。

查询结果为单位记录的 SELECT 语句
例：根据学生号码查询学生信息，假设已经把要查询的学生的学号赋给了主变量 givensno
```
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
            INTO :Hsno,:Hname,:Hsex,:Hdept
            FROM Student
            WHERE Sno-:givensno;
```
要注意：
1. INTO 子句、WHERE 子句和 HAVING 短语的条件表达式中均可以使用主变量
2. 查询结果为空值的处理
指示变量只能用于 INTO 子句中
非 CURRENT 形式的增删改语句
有些增删改语句不需要使用游标，不是 CURRENT 形式，在 UPDATE 的 SET 子句和 WHERE 子句中可以使用主变量，SET 子句还可以使用指示变量

例：修改某个学生选修 1 号课程的成绩
```
    EXEC SQL UPDATE SC
        SET GRADE = :newgrade       /* 修改的成绩已赋给主变量 */
        WHERE Sno = :givensno;      /* 学号已赋给主变量 givensno */
```
例: 将计算机系全体学生年龄置 NULL 值
```
Sageid = -1;
EXEC SQL UPDATE Student 
    SET Sage = :Raise :Sageid
    WHERE Sdept = 'CS'
```
将指示变量 Sageid 赋一个负值后，无论主变量 Raise 为何值，RDBMS 都会将 CS 系所有学生的年龄置空值等价于：
```
EXEC SQL UPDATE Student
        SET Sage = NULL
        WHERE Sdept = 'CS';
```

例：某个学生退学了，现要将有关他的所有选课记录删除掉。假设该学生的姓名已赋给主变量 stdname

    EXEC SQL DELETE 
            FROM SC
            WHERE Sno = 
                        (SELECT Sno
                         FROM Student
                         WHERE Sname = :stdnome);

例：某个学生新选修了某门课程，将有关记录插入 SC 表中，假设插入的学号已赋给主变量 stdno, 课程号已赋给主变量 couno

    gradeid = -1                /* 用作只是变量，赋为负值 */
    EXEC SQL INSERT 
            INTO SC(Sno,Cno,Grade)
            VALUES(:stdno,:couno,:gr:gradeid);

由于该学生刚选修课程，成绩应为空，所以要把只是变量赋为负值

使用游标的 SQL 语句

必须使用游标的 SQL 语句有：查询结果为多条记录的 SELECT 语句、CURRENT 形式的 UPDATE 和 DELETE 语句

查询记过为多条记录的 SELECT 语句
使用游标的步骤
- 说明游标：用 DECLARE 语句为一条 SELECT A语句定义游标
  EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT 语句>;
  定义游标仅仅是一条说明性语句，这时 RDBMS 并不执行 SELECT 语句
- 打开游标。用 OPEN 语句将定义的游标打开
  EXEC SQL OPEN <游标名>;
  打开游标实际上是执行相应的 SELECT 语句，把查询结果取到缓冲区中，这时游标处于活动状态，指针指向查询结果集中的第一条记录。
- 推进游标指针并取当前记录
  EXEC SQL FETCH <游标名> INTO <主变量> [<只是变量>][,<主变量>[<指示变量>]]...;
- 关闭游标。用 CLOSE 语句关闭游标，释放结果集占用的缓冲区及其他资源。
  EXEC SQL CLOSE <游标名>
CURRENT 形式的 UPDATE 和 DELETE 语句
UPDATE 语句和 DELETE 语句都是集合操作，如果只想修改或删除其中某个记录，则需要用带游标的 SELECT 语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用 CURRENT 形式的 UPDATE 和 DELETE 语句修改或删除，即UPDATE 语句和 DELETE 语句中要用子句
```
WHERE CURRENT OF <游标名>
```
来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录

动态 SQL

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

使用 SQL 语句主变量
程序主变量包含的内容是 SQL 语句的内容，而不是原来保存数据的输入或输出变量，这样的变量称为 SQL 语句主变量。 SQL 语句主变量在程序执行期间可以设定不同的 SQL 语句，然后立即执行

例：创建基本表 TEST
```
EXEC SQL BEGIN DECLARE SECTION;
const char * stmt = "CREATE TABLE test(a int);"; /* SQL 语句主变量 */
EXEC SQL END DECLARE SECTION 
...
EXEC SQL EXECUTE IMMEDIATE :stmt;               /* 执行语句 */
```
动态参数
动态参数是 SQL 语句中的可变元素，使用参数符号(?)表示该位置的数据在运行时设定。和前面使用的主变量不同，动态参数的输入不是编译时完成绑定，而是通过（prepare）语句准备主变量和执行(execute) 时绑定数据或主变量来完成。使用动态参数的步骤
- 声明 SQL 语句主变量
  变量的 SQL 内容包含动态参数（?）.
- 准备 SQL 语句（PREPARE）.
  PREPARE 将分析含主变量的 SQL 语句内容，建立语句中包含的动态参数的内部描述符，并用<语句名>标识他们的整体
  EXEC SQL PREPARE <语句名> FROM <SQL 语句主变量>
- 执行准备好的语句(EXECUTE)
  EXECUTE 将 SQL 语句中分析出的动态参数和主变量或数据常量绑定作为语句的输入或输出变量
  EXEC SQL EXECUTE <语句名> [INTO <主变量表>][USINC <主变量或常量>];
例：向TEST 中插入元祖
```
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test VALUES(?);";   /* 声明SQL主变量 */
EXEC SQL END DECLARE SECTION;
...
EXEC SQL PREPARE mystmt FROM :stmt;             /* 准备语句 */
...
EXEC SQL EXECUTE mystmt USING 100;              /* 执行语句 */
EXEC SQL EXECUTE mystmt USING 200;              /* 执行语句 */
```

存储过程

PL/SQL (Procedural Language/ SQL, PL/SQL) 是编写数据库存储过程的一种过程语言。它结合了 SQL 的数据操作能力和过程化语言的流程控制能力，是SQL 的过程化扩展。

PL/SQL 的块结构

PL/SQL 程序的基本结构是块。所有的 PL/SQL 程序都是由块组成的。这些块之间可以互相嵌套，每个块完成一个逻辑操作。

变量常量的定义

PL/SQL 中定义变量的语法形式是：
变量名数据类型 [[NOT NULL]:= 初值表达式]
或
变量名数据类型 [[NOT NULL] 初值表达式]
常量的定义类似于变量的定义：
常量名数据类型 CONSTANT := 常量表达式
赋值语句
变量名称 := 表达式

                |DECLARE                /* 定义的变量，常量等职能在该基本块中使用 */   
定义部分  <--    |
                |----变量，常量，游标，异常等 /* 当基本块执行结束时，定义就不再存在 */

                | BEGIN
                |--------SQL 语句，PL/SQL 的流程控制语句
执行部分    <--- |EXCEPTION         /* 遇到不能继续执行的情况称为异常 */
                |--------异常处理部分 /* 在出现异常时，采取措施来纠正错误或报告错误 */
                |END;

控制结构

条件控制语句
IF-THEN-ELSE

IF 单支

IF condition THEN
    Sequence_of_statement1;     /* 条件为真时语句序列才被执行 */
END IF                          /* 条件为假或 NULL 时 什么也不做，控制转移至下一个语句 */

IF 双分支

IF condition THEN
    Sequence_of_statements1;    /* 条件为真时执行语句序列1 */
ELSE
    Sequence_of_statements2;    /* 条件为假或 NULL 时执行语句序列2 */
END IF;

IF 多分支，在THEN 和 ELSE 子句中还可以再包括 IF 语句，即 IF 语句可以嵌套

循环控制语句
三种循环结构: LOOP,WHILE-LOOP 和 FOR-LOOP.

最简单的循环语句 LOOP

LOOP
    Sequence_of_statements: /* 循环体，一组 PL/SQL 语句 */
END LOOP;

使用 EXIT,BREAK,LEAVE 挑出循环

WHILE-LOOP

WHILE condition LOOP
    Sequence_of_statements;
END LOOP;

FOR-LOOP

FOR count IN [REVERSE] bound1...bound2 LOOP
    Sequence_of_statements;
END LOOP;

错误处理

存储过程

存储过程的有点
- 由于存储过程不像解释执行的 SQL 语句那样在提出操作请求时才进行语法分析和优化工作，因此运行效率高，它提供了在服务器端快速执行SQL语句的有效途径。
- 存储过程降低了客户机和服务器之间的通信量。客户机上的应用程序只要通过网络向服务器发出存储过程的名字和参数，就可以让RDBMS执行许多条的 SQL 语句，并执行数据处理。只有最终处理结果才返回客户端
- 方便实施企业规划。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由RDBMS管理，即有利于集中控制，又能够方便地进行维护，当用户规则发生变化时只要修改存储过程，无须修改其他应用程序。

存储过程的用户接口

创建存储过程

CREATE Procedure 过程名（[参数1，参数2，。。。]） /* 存储过程首部 */
AS
<PL/SQL 块>;                                     /* 存储过程体，描述该存储过程的操作 */

存储过程包括首部和过程体
过程名：是数据库服务器合法的对象标识
参数列表：用名字来标识调用时给出的参数值，必须制定值得数据类型。存储过程的参数也可以定义输入参数、输出参数或输入/输出参数。默认为输入参数。
过程体：是一个<PL/SQL 块> 包括声明部分和可执行语句部分。

例：利用存储过程来实现下面的应用：从一个账户指定数额的款项到另一个账户中。

CREATE PROCEDURE TRANSFER(inAccount INT.outAccount INT.amount FLOAT)
    AS DECLARE
        totalDeposit FLOAT:
        BEGIN           /* 检查转出账户的余额 */
            SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM = outAccount;
            IF totalDeposit IS NULL THEN  /* 账户不存在或账户中没有存款 */
                ROLLBACK;
                RETURN;
            END IF
            IF totalDeposit < amount THEN /* 账户存款不足 */
                ROLLBACK;
                RETURN;
            END IF
            UPDATE account SET total = total - amount WHERE ACCOUNTNUM=outAccount; /* 修改转出账户，减去转出额 */
            UPDATE account SET total = total + amount WHERE ACCOUNTNUM = inAccount; /* 修改转入账户，增加转出额 */
                commit;     /* 提交转账事物 */
            END;

可以使用 ALTER Procodure 重命名一个存储过程
ALTER Procedure 过程1 RENAME TO 过程2

2.执行存储过程
CALL/PERFORM Procedure 过程名([参数1，参数2，...])

 例：从账户 01003815868 转一万元到 01003813828
 `CALL Procedure TRANSFER(01003815868,01003813828,10000);`

删除存储过程
DROP PROCEDURE 过程名()

游标
和嵌入式 SQL 一样，在 PL/SQL 中如果 SELECT 语句只返回同一条记录，可以将该结果存放变量中。当查询返回多条记录时，就要用游标对结果集进行处理。一个游标与一个 SQL 语句相关联。

ODBC 编程

使用ODBC 编写的应用程序可移植性好，能同事访问不同的数据库，共享多个数据资源

数据库互连概述

ODBC 工作原理概述

组成
- 用户应用程序
- 驱动程序管理器（ODBC Driver Manager）
- 数据库驱动程序（ODBC Driver）
- 数据源

应用程序
- 请求链接数据库
- 向数据库发送 SQL 语句
- 为 SQL 语句执行结果分配存储空间，定义所读取的数据格式
- 获取数据库操作结果，或处理错误
- 进行数据处理并向用户提交处理结果
- 请求事物的提交和回滚操作
- 断开与数据源的链接
驱动程序管理器
管理各种驱动程序

主要功能：装在 ODBC 驱动程序，选择和链接争取的驱动程序、管理数据源、检查ODBC调用参数的合法性及记录ODBC函数的调用，当应用层需要时返回驱动程序的有关信息

ODBC 驱动程序管理器可以建立、配置或删除数据源，并查看系统当前所安装的数据库 ODBC 驱动程序
数据库驱动程序
ODBC 通过驱动程序来提供应用系统与数据库平台的独立性
ODBC 应用程序不能直接存取数据库，其各种操作请求由驱动程序管理器提交给某个 RDBMS 的 ODBC 驱动程序，通过调用驱动程序所支持的函数来存取数据库。数据库的操作结果也通过驱动程序返回给应用程序。如果应用程序要操纵不同的数据库，就要动态地链接到不同的驱动程序上

ODBC 数据源管理
数据源是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，实际上是一种数据链接的抽象
ODBC 给每个被访问的数据源指定唯一的，数据源名（Data Source Name ，简称 DSN），并映射到所有必要的，用来存取数据的底层软件。
用户只针对数据源做处理和操作，不需要关系数据库细节驱动程序细节网络等其他细节。简化了用户的操作，便于理解和使用

ODBC API 基础

ODBC 应用程序接口（DDBC Application Intertace, ODBC API）都要符合两方面的一致性

API 一致性，API 一致性级别有核心级、扩展1级、扩展2级
语法一致性，语法一致性级别有最低限度 SQL 语法级、核心 SQL 语法级、扩展 SQL 语法级

函数概述
- 分配和释放环境句柄、链接句柄、语句句柄
- 链接函数（SQLDriverconnect等）
- 与信息相关的函数（如获取描述信息函数 SQLGetinfo、SQLCetFuetion）
- 事物处理函数（如 SQLEndTran）
- 执行相关函数（SQLExecdirect、SQLExecute 等）
- 编目函数
句柄及其属性

数据库编程