高级 SQL 主题

高级SQL主题 • 临时表 • 游标 • 存储过程 • 嵌入式SQL语言

第三章介绍的SQL语言是作为独立语言 使用的，是面向集合的描述性语言，是非过程性的。即大多数语言都是独立执行的，与上下文无关。而许多事务处理应用都是过程性的，需要根据不同条件来执行不同的任务，因此单纯用SQL语言很难处理。为此，SQL语言提供了另一种形式，将SQL语言嵌入到某种高级语言中使用，利用高级语言的过程性结构来弥补SQL语言实现复杂应用的不足，称为嵌入式SQL 语言，而嵌入SQL的高级语言称为主语言或宿主语言。

1.创建临时表 • 临时存储于数据库中的表，用户退出或与数据库的连接终止时就自动消失。只对它的创建者有效。 • 一般用途：临时存储从查询中返回的数据。 • SQL Server创建临时表的两种格式： CREATE TABLE #table_name( field1 datatype, … fieldn datatype) CREATE TABLE tempdb.tablename( field1 datatype, … fieldn datatype)

2. 游标 SQL语言面向集合，主语言面向记录，为了解决冲突，嵌入式SQL引入游标的概念。 • 系统为用户开设的数据缓冲区，可以存放SQL的执行结果。 • 可以选择一组数据，可以在这组记录上滚动。 • 保存查询的结果，以便日后使用。创建、使用和关闭数据库游标的步骤： • 创建游标； • 打开游标以便在过程或应用中使用它； • 一次取出(FETCH)一个记录数据的一行，直到游标记录集的最后一行； • 用完后关闭游标； • 去掉分配给游标的内存并将它完全删掉。

创建游标： DECLARE <游标名> CURSOR FOR <SELECT 语句>；例子：CREATE ARTISTS_CURSOR CURSOR FOR SELECT * FROM ARTISTS；打开游标： OPEN <游标名> ；例子：OPEN ARTISTS_CURSOR；

滚动游标： FETCH <游标名> INTO<主变量>[<指示变量>][，<主变量>[<指示变量 >]]… ；关闭游标： CLOSE <游标名> ；注：关闭后游标仍然存在，然而它必须重新打开才能使用。关闭游标实际上关闭了它的结果集。DEALLOCATE命令释放和游标相关的存储空间。 DEALLOCATE CURSOR <游标名> ；

游标的使用域 • 游标不是数据库的对象，而是有一个限定的使用域。 • 游标使用的3个区域： • 在终端使用时间内——从用户注册到数据库，并创建游标开始，直到用户离开主机为止。 • 存储过程——在存储过程内创建的游标，只有在存储过程的执行期间才能用。退出过程，则游标无效。 • 触发器——同存储过程。

3.存储过程(Stored Procedures)

4.嵌入式SQL语言 • 嵌入式SQL的一般形式对于嵌入式SQL语言，DBMS可以采用两种方法处理，一种是预编译，另一种是修改和扩充主语言，使之能处理SQL语句。目前较多采用前者，也就是预编译的方法。由DBMS的预处理程序对源程序进行扫描，识别出SQL语句，把它转换为主语言调用语句，以使主语言编译程序能够识别它，最后由主语言的编译程序将整个源程序编译成目标码。在嵌入式SQL中，为了区分SQL语句和主语句，所有SQL语句都必须加前缀EXEC SQL。至于结束标志则和宿主语言有关。

例如在PL/1和C中以(；)结束。 EXEC SQL <SQL语句>；在COBOL中以END-EXEC结束。 EXEC SQL <SQL语句> END-EXEC；一条交互形式的SQL语句 DROP TABLE Course ；嵌入到C语言中，应该写作 EXEC SQL DROP TABLE Course ；嵌入式SQL语言根据作用不同，可以分为可执行语句和说明性语句，而前者又可分为数据定义、数据控制、数据操纵三种。在主语言中，任何允许出现可执行的高级语言的地方，都可以写可执行语句；任何允许出现说明性高级语言的地方，都可以写说明性语句。

嵌入式SQL语句与主语言的通信 SQL语句被嵌入到高级语言当中，SQL语句负责操纵数据库，主语言负责控制程序流程。前者是描述性的面向集合的语言，后者是过程性的语言。数据库工作单元和源程序工作单元之间的通信主要包括：向主语言传递SQL语句的执行状态信息，以便主语言能够依据此来控制程序流程。主要用SQL通信区 (SQLCA)来实现。主语言向SQL语句提供参数，主要用主变量来实现。将SQL语句查询结果交主语言进一步处理，主要用主变量和游标实现。

SQL通信区 SQL语句执行后，系统将当前工作状态和运行环境的各种数据送到SQL通信区SQLCA中。应用程序从SQLCA中取出这些信息来决定接下来执行的语句。 SQLCA是一个数据结构，在应用程序中用EXEC SQL INCLUDE SQLCA加以定义。SQLCA用SQLCODE来存放每次执行SQL语句后返回的代码，如果值为预定义常量SUCCESS，则SQL语句执行成功，否则在其中存放错误代码。

例如在执行DELETE后，不同的执行情况SQLCA中 有不同的信息。 • 成功删除，并有删除的行数。(SQLCODE=SUCCESS) • 无条件删除警告信息。 • 违反数据保护规则，操作被拒绝。 • 没有满足条件的元组，一行也没有删除。 • 由于各种原因而出错。二主变量主语言程序变量称为主变量。主变量根据作用的不同，分为输入主变量和输出主变量。输入主变量由应用程序赋值，SQL语句使用；输出主变量由SQL语句赋值或设置状态信息。输入主变量可以指定向数据库中插入数据，修改数据，可以指

定执行的操作，制定WHERE和HAVING子句中的条 件；输出主变量可以得到SQL语句的结果数据和状态。一个主变量还可以附带一个任选的指示变量。指示变量是一个整型变量，用来“指示”主变量的值或条件，可以指示输入主变量是否为空值，检测输出主变量是否为空值等。所有主变量和指示变量在SQL语句BEGIN DECLARE SECTION与END DECLARE SECTION 之间说明。SQL语句中的主变量和指示变量前都要加(：)作为标志，且指示变量要紧跟在主变量之后。 SQL语句外，主变量和指示变量均可以直接引用，不必加冒号。

嵌入式SQL语句的工作原理 SQL语句用主变量从主语言中接受执行参数，操纵数据库；SQL语句的执行状态送到SQLCA中；主语言程序从SQLCA中取出状态信息，据此决定下一步操作；如果SQL语句从数据库中成功检索出数据，则通过主变量传给主语言进一步处理。

不用游标的SQL语句 一说明性语句用来说明主变量等而设置的。 EXEC SQL BEGIN DECLARE SECTION ； EXEC SQL END DECLARE SECTION ；以上两条语句成对出现，中间是主变量说明。二数据定义语句例子：建立一个“选课表”SC。 EXEC SQL CREATE TABLE SC (Sno CHAR(5) NOT NULL UNIQUE , Cno CHAR(5) , Grade INT) ；注：数据定义语言中不能使用主变量。

三数据控制语句 EXEC SQL GRANT SELECT ON TABLE Course TO U1 ；四查询结果为单记录的SELECT语句一般格式： EXEC SQL SELECT [ALL/DISTINCT] <目标列表达式>[，<目标列表达式>]… INTO<主变量>[<指示变量>][,<主变量> [指示变量]] … FROM <表名或视图名> [，<表名或视图名>]… [WHERE <条件表达式>] [GROUP BY <列名1>[HAVING<条件表达式>]] [GROUP BY <列名2>[ASC/DESC]] ；

注：1. INTO子句、WHERE子句的条件表达式、 HAVING短语的条件表达式均可使用主变量。 2. 查询的结果中，某些列值为NULL。如果INTO 子句中主变量后跟有指示变量，当某个数据项为NULL时，系统会自动给相应主变量后面的指示变量置为负值，而不向该主变量赋值，也就是说主变量仍为执行SQL语句之前的值。指示变量只能用于INTO子句中。 3. 如果数据库中没有满足条件的记录，则 DBMS将SQLCODE的值置为100。 4. 如果查询结果是多条记录，则程序出错， SQLCA中会返回错误信息。

例：根据学生号码查询学生信息。假设已将要查询学生的学号赋给了主变量givensno。 EXEC SQL SELECT Sno, Sname, Ssex, Sage, Sdept INTO :Hsno, :Hname, :Hsex, :Hage, :Hdept FROM Student WHERE Sno = :givensno ；查询某个学生选修某门课程的成绩。假设已将学生的学号赋给givensno，课程号赋给givencno。 EXEC SQL SELECT Sno, Cno, Grade INTO :Hsno, :Hcno, :Hgrade:Gradeid FROM SC WHERE Sno = : givensno AND Cno = : givencno ；

五非CURRENT形式的UPDATE语句 例：将全体学生1号课程的考试增加若干分。假设增加的分数已赋给主变量Raise。 EXEC SQL UPDATE SC SET Grade = Grade + :Raise WHERE Cno = ‘1’；修改某个学生1号课程的成绩，假设该学生的学号赋给givensno，修改后的成绩赋给newgrade。 EXEC SQL UPDATE SC SET Grade = : newgrade WHERE Sno = : givensno AND Cno = ‘1’；

将信息系全体学生的年龄置NULL。 Sageid = -1 ； EXEC SQL UPDATE Student SET Sage = : Raise:Sageid WHERE Sdept = ‘IS’；六非CURRENT形式的DELETE语句例：将某学生所有选课记录删除，姓名赋给 stdname。 EXEC SQL DELETE FROM SC WHERE Sno = (SELECT Sno from Student WHERE Sname = : stdname) ；

另一种方法是： EXEC SQL DELETE FROM SC WHERE : stdname = (SELECT Sname from Student WHERE Student.Sno = SC.Sno) ；七 INSERT语句例子：将某学生新选课的记录插入SC表中，学号赋给stdno，课程号赋给couno。 gradeid = -1 ； EXEC SQL INSERT INTO SC(Sno,Cno,Grade) VALUES (:stdno, :couno, :gr:gradeid)；

使用游标的SQL语句 • 查询结果为多条记录的SELECT语句此时需要使用游标,将多条记录一次一条送至主语言处理，从而把对集合的操作转换为对单个记录的处理。使用游标的步骤：说明游标。打开游标。推进游标指针并取当前记录。关闭游标。

例子： 查询某个系全体学生的信息。 EXEC SQL BEGIN DECLARE ； // 说明主变量deptname, HSno, HSname等。 EXEC SQL END DECLARE ； GETS(deptname) EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = : deptname ； EXEC SQL OPEN SX While(1) { EXEC SQL FETCH SX INTO : HSno , :HSname

, :HSsex, :HSage; if (sqlca.sqlcode <> SUCCESS) break ; //主语句进一步处理 } ； EXEC SQL CLOSE SX ；查询某些系全体学生的选课信息。 EXEC SQL BEGIN DECLARE ； // 说明主变量deptname, HSno, HCno等。 EXEC SQL END DECLARE ； EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname,Cno,Cname,G

FROM Student,SC WHERE Sdept = : deptname And Student.Sno = SC.Sno WHILE(gets(deptname) != NULL) { EXEC OPEN SX WHILE(1) { EXEC SQL FETCH SX INTO : HSno, : HSname, :HCno,:HCname,:HG ； if(sqlca.sqlcode<> SUCCESS) break ； //主语言进一步处理 } ； EXEC SQL CLOSE SX ； } ；

CURRENT形式的UPDATE语句和DELETE语句 UPDATE语句和DELETE语句都是集合操作，如果只想修改或删除其中的某个记录,则需要用带游标的 SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用CURRENT形式的UPDATE 和DELETE语句修改或删除。具体步骤：用DECLARE说明游标，UPDATE则SELECT语句中要用 FOR UPDATE OF <列名>，DELETE不需要。用OPEN语句打开游标，所有满足条件的记录送至缓冲区。使用FETCH语句推进游标指针，将当前记录送至主变量。

检查该记录是否是要删除或修改的记录，如果 是，则用UPDATE语句或DELETE语句修改或删除。这时需要加上子句WHERE CURRENT OF <游标名>来表示修改或删除的是最近一次取出的记录。处理完毕后用CLOSE关闭游标。例：查询某个系全体学生的信息，然后根据用户的要求修改其中某些记录的年龄。

EXEC SQL BEGIN DECLARE ； // 说明主变量deptname, HSno, HSname等。 EXEC SQL END DECLARE ； GETS(deptname) EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = : deptname ； FOR UPDATE OF Sage ； EXEC SQL OPEN SX While(1) { EXEC SQL FETCH SX INTO : HSno , :HSname, : HSsex, : HSage ； if(sqlca.sqlcode <> SUCCESS)

break ； printf(“%s,%s,%s,%d,Sno,Sname,Ssex,Sage) ； printf(“UPDATE AGE?”) ； scanf(“%c’, &yn) ； if(yn = ‘y’or yn = ‘Y’) { printf(“INTPUT NEW AGE”) ； scanf(“%d’, &NEWAge) ； EXEC SQL UPDATE Student SET Sage = : NEWAge WHERE CURRENT OF SX ； } ； } ； EXEC SQL CLOSE SX ；

查询某个系全体学生的信息，然后根据用户的 要求删除其中某些记录。 EXEC SQL BEGIN DECLARE ； // 说明主变量deptname, HSno, HSname等。 EXEC SQL END DECLARE ； GETS(deptname) EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = : deptname ； EXEC SQL OPEN SX

While(1) { EXEC SQL FETCH SX INTO : HSno , :HSname, : HSsex, : HSage ； if(sqlca.sqlcode <> SUCCESS) break ； printf(“%s,%s,%s,%d,Sno,Sname,Ssex,Sage) ； printf(“DELETE?”) ； scanf(“%c’, &yn) ； if(yn = ‘y’or yn = ‘Y’)

EXEC SQL DELETE FROM Student WHERE CURRENT OF SX ； } ； EXEC SQL CLOSE SX ；

高级 SQL 主题