幻灯片 1

Transcription

1 数据库系统原理 Database System Principles 四川大学计算机学院 段磊

2 第八章数据库编程 标准 SQL 缺少流程控制能力, 难以实现应用业务中的逻辑控制 SQL 编程技术可以有效克服 SQL 语言实现复杂应用方面的不足, 提高应用系统和 RDBMS 间的互操作性

3 本章目录 8.1 嵌入式 SQL 8.2 存储过程 8.3 ODBC 编程 数据库系统概论 - 第 8 章 3/38

4 8.1 嵌入式 SQL SQL 语言提供了两种不同的使用方式 一种是在终端交互式方式下使用, 我们前面介绍的就是做为独立语言由用户在交互环境下使用的 SQL 语言 另一种是将 SQL 语言嵌入到某种高级语言如 Pascal COBOL FORTRAN C 中使用, 利用高级语言的过程性结构来弥补 SQL 语言在实现复杂应用方面的不足, 这种方式下使用的 SQL 语言称为嵌入式 SQL(Embedded SQL), 而嵌入 SQL 的高级语言称为主语言或宿主语言 数据库系统概论 - 第 8 章 4/38

5 8.1.1 一般形式 1. 语句级接口如 PB Script 2. 调用级接口如 JDBC ODBC 在有的嵌入式 SQL 中, 为了能够区分 SQL 语句与主语言语句, 所有 SQL 语句都必须加前缀 EXEC SQL 如 :C 中嵌入可能形式 EXEC SQL DROP TABLE Student; 数据库系统概论 - 第 8 章 5/38

6 8.1.2 嵌入式 SQL 与主语言间的通信 将 SQL 嵌入到高级语言中混合编程,SQL 语句负责操纵数据库, 高级语言语句负责控制程序流程 最重要的是两者之间的通信问题 : 1. 向主语言传递 SQL 语句的执行状态 ; 2. 主语言向 SQL 提供参数 ( 主变量实现 ); 3. SQL 查询结果交还主语言处理 数据库系统概论 - 第 8 章 6/38

7 嵌入式 SQL 与主语言间的通信 一 SQL 通信区 SQL 语句执行后, 系统要反馈给应用程序若干信息, 主要包括描述系统当前工作状态和运行环境的各种数据, 这些信息将送到 SQL 通信区 SQLCA 中 应用程序从 SQLCA 中取出这些状态信息, 据此决定接下来执行的语句 如下所示的 SQLCA 数据连接字 数据库系统概论 - 第 8 章 7/38

8 嵌入式 SQL 与主语言间的通信 SQLCA.DBMS = "MSS" SQLCA.Database = Student" SQLCA.LogId = "sa" SQLCA.LogPass = "" SQLCA.ServerName ="ZTQ\ZTQ_TEST" CONNECT USING SQLCA; 数据库系统概论 - 第 8 章 8/38

9 嵌入式 SQL 与主语言间的通信 二 主变量 SQL 语句中使用主语言的变量 SQL 语句中主变量前加冒号 输入主变量, 输出主变量 ( 相对于 SQL 语句 ) 输入主变量 : 应用程序赋值,SQL 使用 实际上 SQL 把它当常量处理 输出主变量 : 由 SQL 语句对其赋值或设置状态信息, 返回应用程序 指示变量 : 附加在主变量上, 指示所指主变量的值 ( 空值 ) 或条件 数据库系统概论 - 第 8 章 9/38

10 嵌入式 SQL 与主语言间的通信 三 游标 为处理集合结果而引入 四 程序实例 注意 : 定义游标后的 Select 目标列是表 属性名, 不是主变量 数据库系统概论 - 第 8 章 10/38

11 8.1.3 不用游标的 SQL 语句 适用于全部说明性语句 数据定义 控制语句,Select 结果为单个记录的查询语句, 非 CURRENT 形式的 UPDATE 和 DELETE 语句 数据库系统概论 - 第 8 章 11/38

12 8.1.4 使用游标的 SQL 语句 一 查询结果为多条记录的 SELECT 语句 本例要查询 deptname 系的所有学生的学号 姓名 性别和年龄 首先定义游标 SX, 将其与查询结果集 ( 即 deptname 系的所有学生的学号 姓名 性别和年龄 ) 相联系 ( 步骤 1) 这时相应的 SELECT 语句并没有真正执行 然后打开游标 SX, 这时 DBMS 执行与 SX 与相联系的 SELECT 语句, 即查询 deptname 系的所有学生的学号 姓名 性别和年龄 ( 步骤 2), 之后 SX 处于活动状态 数据库系统概论 - 第 8 章 12/38

13 使用游标的 SQL 语句 接下来在一个循环结构中逐行取结果集中的数据, 分别将学号 Sno 姓名 Sname 性别 Ssex 和年龄 Sage 送至主变量 HSno HSname HSsex 和 HSage 中 ( 步骤 3) 主语言语句将对这些主变量做进一步处理 最后关闭游标 SX( 步骤 4) 这时 SX 不再与 deptname 系的学生数据相联系 被关闭的游标 SX 实际上可以再次被打开, 与新的查询结果相联系 例如, 可以在例 1 中再加上一层外循环, 每次对 deptname 赋新的值, 这样 SX 就每次和不同的系的学生集合相联系 数据库系统概论 - 第 8 章 13/38

14 使用游标的 SQL 语句 String Cname,Cno='1'; DECLARE CX CURSOR FOR SELECT Cname FROM course; OPEN CX; FETCH CX INTO :cname; do while SQLCA.SQLCODE = 0 ddlb_1.additem(cname); FETCH CX INTO :cname; loop close CX; 数据库系统概论 - 第 8 章 14/38

15 使用游标的 SQL 语句 二 CURRENT 形式的 UPDATE 和 DELETE 语句 数据库系统概论 - 第 8 章 15/38

16 8.1.5 动态 SQL 查询对象 查询条件 要查询的属性列不确定 特点 : 在查询执行时临时组装 实现技术 : 实际不是 SQL 技术, 是程序设计技术 数据库系统概论 - 第 8 章 16/38

17 动态 SQL Power Builder 的四种动态 SQL 格式 无参数 无返回结果的 SQL 语句 如 建表 插入元组值等场合 String Mysql Mysql= INSERT INTO Course & + VALUES( 5, JAVA ) ; EXECUTE IMMEDIATE : Mysql ; 数据库系统概论 - 第 8 章 17/38

18 动态 SQL 需要参数, 无返回值 INT Emp_id_var = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING : Emp_id_var ; 数据库系统概论 - 第 8 章 18/38

19 动态 SQL 有返回值的 编译时能确定参数个数和返回结果字段 有返回值的 编译时可能无法确定参数个数和返回结果字段 JDBC 相关内容参阅 实验环节 数据库系统概论 - 第 8 章 19/38

20 本章目录 8.1 嵌入式 SQL 8.2 存储过程 8.3 ODBC 编程 数据库系统概论 - 第 8 章 20/38

21 8.2 存储过程 SQL:1999 标准 SQL-invoked routines SQL-invoked function SQL-invoked procedure PL/SQL(Procedural Language/SQL) 最常用的一种编写存储过程语言 结合了 SQL 数据操纵能力和过程化语言的流程控制能力 数据库系统概论 - 第 8 章 21/38

22 8.2.1 PL/SQL 块结构 块是 PL/SQL 程序的基本结构 DECLARE /* 变量 常量 游标 异常等的声明定义 */ BEGIN /* 主要处理功能部分 */ EXECEPTION /* 异常处理部分 */ END; 数据库系统概论 - 第 8 章 22/38

23 8.2.2 变量常量的定义 常量定义 常量名 数据类型 CONSTANT:= 常量值表达式 ; sage0 INT CONSTANT:=25; 变量定义 变量名 数据类型 [NOT NULL] [[:=] 初始值表达式 ]; 数据库系统概论 - 第 8 章 23/38

24 变量常量的定义 sage INT; sage INT 25; sage INT :=25+sage0; sage INT NOT NULL :=25; 赋值语句 变量名 := 表达式 ; sage:=sage+4; 数据库系统概论 - 第 8 章 24/38

25 8.2.3 控制结构 条件语句 ( 可嵌套 ) IF 条件 THEN 语句块 [ELSE 语句块 ] END IF; 循环 LOOP WHILE-LOOP FOR-LOOP 错误 ( 异常 ) 处理 数据库系统概论 - 第 8 章 25/38

26 8.2.4 存储过程 存储过程 : 命名 PL/SQL 块 匿名和命名 PL/SQL 块 优点 高效率 ( 预解析 预编译 ) 低通信量 ( 提交请求简单 ) 方便规则实施和变化 数据库系统概论 - 第 8 章 26/38

27 存储过程 创建 修改 执行和删除 创建 CREATE PROCEDURE 过程名 ([ 参数 1, 参数 2, ]) AS PL/SQL 块 修改 : 执行 删除 ALTER PROCEDURE CALL PERFORM PROCEDURE 过程名 ([ 参数 1, 参数 2, ]); DROP PROCEDURE 过程名 ; 数据库系统概论 - 第 8 章 27/38

28 触发器 ( 复习 ) 触发器的要点 事件 insert update delete 条件 满足条件后执行的动作 例子 银行存款透支后开一等额贷款账户 数据库系统概论 - 第 8 章 28/38

29 触发器 ( 复习 ) create trigger overdraft-trigger after update on account referencing new row as nrow //nrow 引用新值 for each row when nrow.balance < 0 // 等待的时机余额为负 begin atomic insert into loan values (n.row.account-number, nrow.branch-name, nrow.balance); update account set balance = 0 // 把存款余额改为 0 where account.account-number = nrow.account-number end 数据库系统概论 - 第 8 章 29/38

30 本章目录 8.1 嵌入式 SQL 8.2 存储过程 8.3 ODBC 编程 数据库系统概论 - 第 8 章 30/38

31 8.3 ODBC 编程 数据库互联概述 DB 种类繁多, 接口不一 ODBC Open Database Connectivity, 开放式数据库连接性 一组 DB 访问规范 API 接口定义 开放式连接 传送查询和更新并得到结果 数据库系统概论 - 第 8 章 31/38

32 8.3.2 ODBC 原理 应用程序 ( 后面有例子 ) 驱动程序管理器 ODBC32.DLL, 微软的 ODBC 管理 ( 连接驱动程序 管理数据源 ) 数据库驱动程序 一般由 DBMS 厂商提供 数据源 在连接中, 用 DSN 代表用户名 服务器名 数据库名等 数据库系统概论 - 第 8 章 32/38

33 8.3.3 ODBC API 基础 打开数据库联机 SQLConnect(). 参数包括 connection handle 连接句柄 the server to which to connect 服务器 the user identifier 用户标识 password 口令 一种重要的参数类型 SQL_NTS SQL_NTS 表示 null 结束的字符串 数据库系统概论 - 第 8 章 33/38

34 代码实例 (1) int ODBCexample() // C 函数 { RETCODE error; HENV env; // environment 环境 HDBC conn; /* database connection */ // 连接 SQLAllocEnv(&env); // 分配环境 SQLAllocConnect(env, &conn); 连接 连接服务器用户, 指定口令, 类型 数据库系统概论 - 第 8 章 34/38

35 代码实例 (2) SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS); { char branchname[80];// 支行名称 float balance; 余额 ; HSTMT stmt; int lenout1, lenout2 SQLAllocStmt(conn, &stmt); 分配语句空间 char * sqlquery = select branch_name, sum (balance) from account group by branch_name ; 数据库系统概论 - 第 8 章 35/38

36 代码实例 (3) error = SQLExecDirect(stmt, sqlquery, SQL_NTS); // 真正送 SQL 到 DB Server if (error == SQL_SUCCESS) { SQLBindCol(stmt, 1, SQL_C_CHAR, branchname, 80, &lenout1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &balance, 0, &lenout2); } } while (SQLFetch(stmt) >= SQL_SUCCESS) { printf (" %s %g\n", branchname, balance); 数据库系统概论 - 第 8 章 36/38

37 代码实例 (4) // 善后工作 : SQLFreeStmt(stmt, SQL_DROP); } SQLDisconnect(conn); // 断开连接 SQLFreeConnect(conn); // 释放空间 SQLFreeEnv(env); // 释放环境空间 } 数据库系统概论 - 第 8 章 37/38

38 Any Question? Thank you! 数据库系统概论 - 第 8 章 38/38