第八章 数据库

– 局部变量必须以@ 开头，而且必须先用DECLARE 命令说明 后才可使用。其说明形式如下：
• DECLARE @变量名 变量类型[，@变量名变量类型…]
– 在Transact-SQL 中不能像在一般的程序语言中一样使用“变 量=变量值”来给变量赋值，必须使用SELECT 或SET 命令来 设定变量的值。其语法如下：
--声明变量 DECLARE @no1 char(5),@no2 char(6),@fs char(2) --声明游标 DECLARE fs_cursor CURSOR FOR SELECT sno,cno, CASE WHEN grade>=90 THEN 'A' WHEN grade>=80 THEN 'B' WHEN grade>=70 THEN 'C' WHEN grade>=60 THEN 'D' WHEN grade<60 THEN 'E' END FROM sc WHERE grade IS NOT NULL ORDER BY sno --打开游标 OPEN fs_cursor --提取第一行数据 FETCH NEXT FROM fs_cursor INTO @no1,@no2,@fs --打印表标题 PRINT '学号 课程号 等级' PRINT '-----------------' WHILE @@FETCH_STATUS = 0 BEGIN --打印一行数据 PRINT @no1+' '+@no2+' '+@fs --提取下一行数据 FETCH NEXT FROM fs_cursor INTO @no1,@no2,@fs END --关闭游标 CLOSE fs_cursor --释放游标 DEALLOCATE fs_cursor GO
例，
EXEC SQL BEGIN DECLARE SECTION
int s_no; char s_name[30]; int s_age;
EXEC SQL END DECLARE SECTION
注：主变量出现于SQL语句中时，前面加 （:）以区别列名 示例：
EXEC SQL select SNAME , AGE from student where SNO = :s_no ;
1.定义部分 • DEபைடு நூலகம்LARE -----变量、常量、游标、异常等
– 定义的变量、常量等只能在该基本块中使用 – 当基本块执行结束时，定义就不再存在
2.执行部分
BEGIN ------SQL语句、PL/SQL的流程控制语句 EXCEPTION ------异常处理部分 END；
8.2.2 变量
• Transact-SQL 中可以使用两种变量：局部变量和全局 变量。 • 1．局部变量
8.2存储过程
• SQL-invoked routines：
– 存储过程(SQL-invoked procedure) – 函数(SQL-invoked function)
8.2.1 PL/SQL的块结构
• PL/SQL ：
– SQL的扩展 – 增加了过程化语句功能
• 基本结构是块 • 块之间可以互相嵌套 • 每个块完成一个逻辑操作
定义与使用游标的语句
• Declare： 定义一个游标，使之对应一个select语句
declare 游标名 cursor for select语句;
• Open：打开一个游标，执行游标对应的查询，结果集 合为该游标的活动集
open 游标名
• Fetch：在活动集中将游标移到特定的行，并取出该行 数据放到相应的宿主变量中
An Introduction to Database System
二、动态参数
使用参数符号(?)表示该位置的数据在运行 时设定 和主变量的区别 : • 动态参数的输入不是编译时完成绑定 • 而是通过 (prepare)语句准备主变量和执 行(execute)时绑定数据或主变量来完成
EXEC SQL BEGIN DECLARE SECTION;
第八章 数据库编程
Yusncun@163.com
本章主要内容：
• 嵌入式SQL • 存储过程 • Odbc编程
8.1 嵌入式SQL
1. 为什么使用嵌入式SQL？
– 交互式SQL不能适应事务处理的过程性. – 表达能力相比高级语言有一定的限制. – 有些动作如与用户交互、图形化显示数据等 只能用高级语言实现
3.嵌入SQL语句与主语言之间的数据传递
• SQL通讯区
– SQL通讯区SQLCA是一结构,每一嵌入SQL 语句的执行情况在其执行完成后写入 SQLCA结构中的各变量中. 必须在应用程序中包括: EXEC SQL INCLUDE SQLCA;
SQLCA中的变量
struct sqlca { char sqlcaid[8]; 标识一个SQL通讯区 long sqlabc; 保留SQL通讯区的长度 long sqlcode; 执行SQL语句的状态码 struct { unsigned short sqlerrml;错误信息文本长度 char sqlerrmc[70]; 错误信息文本 }sqlerrm; char sqlerrp[8]; 出错信息（按单元） long sqlerrd[6]; char sqlwarn[8]; 警告信息（按单元设置为W标志） char sqlext[8]; 未用 }; struct sqlca sqlca;
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; /* 执行语句 */
• delete语句
EXEC SQL delete from stduent where SNO＝ :s_no ;
• update语句
EXEC SQL update stduent set AGE = :s_age where SNO = : s_no ;
5.使用游标的SQL语句
• 查询结果中包含多个元组时，使用游标 可以逐个存取这些元组。 • Current 形式的UPDATE和DELETE语句 • 活动集：select语句返回的元组的集合 • 当前行：活动集中当前处理的那一行。 游标即是指向当前行的指针
• 例，Cousor 1 • 例，Cousor 2
动态SQL简介
• 允许在程序运行过程中临时“组 装”SQL语句 • 支持动态组装SQL语句和动态参数两种 形式
一、使用SQL语句主变量
• SQL语句主变量：
– 程序主变量包含的内容是SQL语句的内容， 而不是原来保存数据的输入或输出变量 – SQL语句主变量在程序执行期间可以设定不 同的SQL语句，然后立即执行
• 关闭数据库连接
– EXEC SQL DISCONNECT [connection]
例，
EXEC SQL BEGIN DECLARE SECTION; char id[7]; char title[81]; int royalty; EXEC SQL END DECLARE SECTION; /* 说明主变量结束*/ EXEC SQL INCLUDE sqlca; /* (1) 定义SQL 通信区*/ main() { EXEC SQL CONNECT TO PUBS USER SA ; EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles; EXEC SQL OPEN C1; while (SQLCODE == 0) { /* SQLCODE will be zero if data is successfully fetched */ EXEC SQL FETCH C1 INTO :id, :title, :royalty; if (SQLCODE == 0) { printf("%s %d %s", id, royalty,title); } } EXEC SQL CLOSE C1; EXEC SQL DISCONNECT PUBS; }
• 创建基本表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;
2. 嵌入式SQL处理过程
主语言 + 嵌入SQL RDBMS预处理
主语言 + 函数调用
主语言编译器编译 主语言执行程序
嵌入式SQL的一般形式:
区分SQL语句与主语言语句，嵌入的SQL 语句以EXEC SQL开始，以分号(;) 结束. 例，EXEC SQL delete from student where SNO = ’10’;
• 指示变量
– 用来指示返回给主变量的值是否为null值，以及返 回给主变量的字符串是否发生了截断
• 指示变量的返回值
– = 0：取到主变量的值不空，没有发生截断 – = 1：取到主变量的值为空值 – > 0：取到主变量的值发生了截断，指示变量的值是 截断前的字符串的实际长度
EXEC SQL BEGIN DECLARE SECTION char s_no; char s_name[30]; int s_age; short name_id; short age_id; EXEC SQL END DECLARE SECTION EXEC SQL select SNAME , AGE into :s_name : name_id , :s_age: age_id from student where SNO = :s_no ;
• 状态指示变量SQLCODE反映了SQL语句 执行情况. • SQLCODE为
– 零值时,表示SQL语句执行成功. – 正值（〉0）时表示警告信息. – 负值（〈0）时表示出现错误.
主变量
• 既可以用在主语句中，也可用在SQL语句中， 用来在两者之间传递数据 • 主变量的说明
– EXEC SQL BEGIN DECLARE SECTION – 说明主变量 – EXEC SQL END DECLARE SECTION
4.不需要游标的sql语句
• 不需要游标的数据操作，结果是一个元 组的select语句
EXEC SQL select SNAME , AGE into :s_name, :s_age from student where SNO = :s_no ;
• insert语句
EXEC SQL insert into stduent values (:s_no, :s_name , :s_age , :dept_no ) ;
fetch [next | prior | first | last 游标名 into [主变量表] 缺省值为NEXT
• Close： 关闭游标，释放活动集及其所占资源。需要 再使用该游标时，执行open语句
close 游标名
• 编写一个程序，采用游标方式输出所有 学号、课程号和成绩等级。
• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •
游标
• 执行方式的差别
– SQL：一次一集合 – 主语言：一次一记录
• 游标：在查询结果的记录集合中移动的指针 • 一个SQL语句返回单个元组，则不用游标 • 若一个SQL语句返回多个元组，则使用游标
建立和关闭数据库连接
• 建立数据库连接
– EXEC SQL CONNECT TO target [as connection-name] [USER user-name];