第5章 关系数据库编程基础本章要点嵌入式SQL的实现方式、使用规定及使用技术面向对象应用程序访问数据库的常用接口ODBCJDBCADO.NE
TSQL Server的存储过程SQL Server的触发器5.1 应用程序访问数据库SQL语言提供了两种不同的使用方式在终端
交互方式下直接运行将SQL语句嵌入到其他程序设计语言程序的源代码中SQL语言提供了两种不同的使用方式嵌入SQL语句的高级语言称为主
语言或宿主语言。将SQL语言嵌入到某种高级语言中使用，利用高级语言的过程性结构弥补SQL语言在实现复杂应用方面的不足。SQL语句负
责对数据库中数据的提取及操作，它所提取的数据提交给程序，程序中其他语句负责数据的处理和传递。嵌入式SQL：通常采用专用的SQL预编
译程序将嵌入的SQL语句转换为能被程序设计语言的编译器识别的函数调用，然后程序设计语言的编译器将源代码编译为可执行程序。应用程序编
程接口（ API ）：通过API的调用，可以将SQL语句传递到DBMS，并用API调用返回结果。这种方法不需要专用的SQL预编译程
序。应用程序可以通过嵌入式SQL或应用程序编程接口（API）实现对数据库的访问。5.1.1 嵌入式SQLSQL语言是基于关系数据
模型的语言，而高级语言是基于整数、实数、字符、数组等数据类型的语言，因此两者之间有很大差别。例如，SQL语句不能直接使用指针、数组
等数据结构，而高级语言一般不能直接进行集合运算。为了能在宿主语言的程序中嵌入SQL语句，嵌入式SQL必须解决一些问题如何将嵌有SQ
L的宿主语言程序编译成可执行码宿主语言和数据库管理系统之间如何传递数据和信息如何协调SQL的集合处理方式与宿主语言的单记录处理方式
等。5.1.1 嵌入式SQL虽然在程序设计的环境下，SQL语句要做某些必要的扩充，但嵌入式SQL和交互式SQL的大部分语法是相同
的。1.在宿主语言中标识SQL语句在嵌入式SQL中，为了使预处理程序能够区分SQL语句与主语言语句，所有SQL语句都必须加前缀“E
XEC SQL”和SQL语句的结束标志。ESQL/C的SQL语句结束标志是分号，一般格式如下：
EXEC SQL ;连接到SQL Server在ESQL/C中，连接到SQL Server使用CONNECT
TO语句，语法如下： CONNECT TO <服务器名>. <数据库名> <连接的名称> USER <登录名>. <登录
密码>使用另外一个连接：SET CONNECTION <连接的名称>关闭与数据的连接用DISCONNECT语句，语法如下DISC
ONNECT <连接的名称> | ALL | CURRENT连接到SQL Server【例5.1】连接服务器myServer上数
据库名为SaleProduct和test的SQL Server数据库，数据库登录名为sa，密码为123456，然后分别关闭它们。E
XEC SQL CONNECT TO myServer.SaleProduct AS con1 USER sa.123456;EX
EC SQL CONNECT TO myServer.test AS con2 USER sa.123456;EXEC SQL D
ISCONNECT con1EXEC SQL DISCONNECT con2【例5.2】建立基本表Customer。EXEC SQ
L CREATE TABLE Customer (custID varchar(5) PRIMARY KEY, cu
stName varchar(20) NOT NULL UNIQUE, custCity VARCHAR(10),
custPhone VARCHAR(13));【例5.3】删除基本表Customer。 EXEC SQL
DROP TABLE Customer;2.数据库工作单元与宿主程序工作单元之间的通信主变量：在嵌入式SQL语句中引用宿主语言说明
的程序变量。SQL与C之间数据的传送可以通过主变量实现。在SQL语句中引用主变量时，为了区别数据库变量，主变量前须加“：” 。在宿
主语言语句中，主变量可与其它变量一样使用，不须加冒号。2.数据库工作单元与宿主程序工作单元之间的通信程序中的主变量都应在程序说明部
分使用DECLARE语句加以声明。一般在BEGIN DECLARE SECTION 和END DECLARE SECTION之间声
明主变量。需要注意的是，在ESQL/C中关键字和SQL语句都是不区分大小写的，但是对于宿主变量的大小写敏感。【例5.4】在商品销售
管理数据库SaleProduct中，查询903号客户的名称和电话号码，分别将其存储到宿主变量xCustName，xCustPhon
e中。EXEC SQL BEGIN DECLARE SECTION; /说明语句开头/ char xCus
tID[5]; char xCustName[20]; char xCustCity[10]; char
xCustPhone[13];EXEC SQL END DECLARE SECTION; /说明语句结束/EXEC
SQL SELECT custName，custPhone INTO :xCustName，:xCustPhone
FROM Customer WHERE custID=''903'';【例5.5】在商品销售管理数据库S
aleProduct中，向Customer表插入一个元组，元组的各分量值分别来自宿主变量xCustID、xCustName、xCu
stPhone。EXEC SQL INSERT INTO Customer (custID，custName，c
ustPhone) VALUES (:xCustID, :xCustName, :xCustPhone);SQL通
信区（SQLCA）SQL语句执行后，数据库管理系统需要反馈给应用程序一些状态信息，包括描述系统当前工作状态和运行环境的各种数据。这
些信息被送到SQL通信区（SQLCA）。SQLCA是一个含有错误变量和状态指示符的数据结构，使用语句EXEC SQL INCLUD
E SQLCA加以定义，它是全局变量。SQLCA.SQLCODE：它是一个整数，用于DBMS向应用程序报告SQL语句的执行情况。S
QLCODE>0，表示SQL已执行，但有异常SQLCODE<0，表示SQL没有执行，有错误SQLCODE=0，表示执行成功，无异常
3. SQL的集合处理方式与宿主语言单记录处理方式之间的转换嵌入式SQL引入游标机制，把集合操作转换成单记录方式。游标是系统为用户
开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字，用户可以通过游标逐一获取记录，并将其赋给主变量，交由主语言
进一步处理。3. SQL的集合处理方式与宿主语言单记录处理方式之间的转换游标分为静态游标和动态游标静态游标包含完整的SQL语句静态
游标可以包含宿主变量，当打开它时，宿主变量就被读到SQL语句中，形成一个完整的SQL语句。动态游标则需要在运行时指定参数。动态游标
中的参数用？标记符来指定，当打开游标时，根据提供的？标记符代码的数据来构造SQL语句。3. SQL的集合处理方式与宿主语言单记录处
理方式之间的转换在使用游标之前必须要定义游标，定义游标语法如下： DECLARE <游标名称> CURSOR FOR
执行定义游标语句时，不会执行其中的SELECT语句。打开游标使用OPEN语句，此时执行游标定义中的SEL
ECT语句，同时游标缓冲区中含有SELECT语句执行后返回的所有记录，游标处于活动状态，其指针指向游标区第一行记录之前。3. SQ
L的集合处理方式与宿主语言单记录处理方式之间的转换使用FETCH语句推进游标。FETCH语句使游标指针向前推进一行，并把当前记录读
出，放到指定的宿主变量中。FETCH语句常置于主语言程序的循环结构中，利用循环逐一处理游标中的多条记录。对于打开的游标，可以用UP
DATE语句来更新当前记录，DELETE语句来删除当前记录。此时，使用子句“WHERE CURRENT OF <游标名>”获取当前
记录。3. SQL的集合处理方式与宿主语言单记录处理方式之间的转换游标使用完后，用CLOSE语句来关闭它。关闭游标，使它不再和原来
的查询结果相联系，同时释放游标占用的资源。【例5.6】在商品销售管理数据库SaleProduct中，返回Customer表中的所有
记录，并输出这些信息到屏幕上。EXEC SQL DECLARE C1 CURSOR FOR SELECT custID, c
ustName, custCity, custPhone FROM Customer;EXEC SQL OPEN C1;while
(SQLCODE==0){ EXEC SQL FETCH C1 INTO :xCustID, :xCustName, :x
CustCity, :xCustPhone; printf("%s ,%s ,%s ,%s ", xCustID, xCus
tName, xCustCity, xCustPhone); }EXEC SQL CLOSE C1; /关闭游标
/【例5.7】在商品销售管理数据库SaleProduct中，定义一个动态游标，查询名称为“张松林”的客户，返回其客户编号。EXE
C SQL BEGIN DECLARE SECTION; CHAR SQLCommand[]="SELECT custID
FROM Customer WHERE custName =?"; CHAR xCustName[]="张松林"; C
HAR xCustID[5];EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE C2 C
URSOR FOR SELECT_STATEMENT;EXEC SQL PREPARE SELECT_STATEMENT FROM
:SQLCommand;EXEC SQL OPEN C2 USING :xCustName;EXEC SQL FETCH C2
INTO :xCustSID;EXEC SQL CLOSE C2; 嵌入式SQL有两种主要的形式 编写程序时要定义完整的SQ
L语句，语句中主变量的个数与数据类型在预编译时都是确定的，只有主变量的值是程序运行过程中动态输入的。 SQL语句可以被应用程序
在运行时构造。 需要在运行时指定条件变量，在程序运行过程中临时“组装”SQL语句。 在预编译时SQL语句的正文、主变量个数、主
变量的数据类型、SQL语句中引用的数据库对象（列、索引、基本表、视图等）等信息可以不确定。5.1.4 动态SQL语句ESQL/C
提供了处理在编译时未知的SQL语句的手段。动态SQL中使用参数代替了宿主变量，这些参数可以嵌入在SQL语句中，并且在以后被值替换。
5.1.4 动态SQL语句动态SQL语句的说明与执行分为两步。第一步，使用PREPARE语句准备SQL，并且在必要时建立一个程序
计划； PREPARE语句将一字符串解释成一组SQL语句，并赋给它一个语句标识符，以后的动态管理语句通过语句标识来引用这组SQL语
句。第二步，用EXECUTE语句或定义游标来执行被预备好的SQL语句。5.1.4 动态SQL语句PREPARE语句的一般形式为：
PREPARE <语句标识名> FROM ；或 PREPARE <语句标识名> FROM
:<宿主变量>；EXECUTE语句的功能是运行与其语句标识符对应的事先准备好的SQL语句，其一般形式为： EXECU
TE <被预备好的语句标识名> [USING <宿主变量列表>]；【例5.8】构造一个动态
SQL语句，插入一条记录到Customer表。EXEC SQL BEGIN DECLARE SECTION CHAR stm
tcomm[]="INSERT INTO Customer(custID, custName) values(?,?)";
CHAR xCustID[5]; CHAR xCustName[20];EXEC SQL END DECLARE SECT
ION/提示用户输入数据/printf ("输入客户编号：");scanf ("%s", xCustID);printf ("
输入客户名称：");scanf("%s", xCustName);EXEC SQL PREPARE stmt FROM :stmt
comm;EXEC SQL EXECUTE stmt USING :xCustID,:xCustName;5.1.2 DAO和O
LE DB第四代开发工具或语言一般都是面向对象的，通常借助于数据库操作组件或对象传递SQL命令来操作数据库。DAO（Data Ac
cess Object，数据访问对象）是一种应用程序编程接口，是微软的第一个面向对象的数据库接口。DAO体现了面向对象的思想，它将
数据的值作为属性，数据的查询作为方法，数据值的变化作为事件，完全封装在DAO对象中。它提供了完成管理关系数据库所需的全部操作的属性
和方法，包括创建数据库，定义表、字段和索引，建立表间的关系，更新和查询数据库等工具。5.1.2 DAO和OLE DB随着数据源日
益复杂化，应用程序很可能需要从不同的数据源取得数据，加以处理，再把处理过的数据输出到另外一个数据源中。标准的关系型数据库中的数据邮
件数据Excel文件Web上的文本或图形5.1.2 DAO和OLE DBMicrosoft为了让应用程序能够以统一的方式存取各种
不同的数据源，在1997年提出了UDA（Universal Data Access）架构。UDA以COM技术为核心，协助程序员存取
企业中各类不同的数据源。UDA以OLE DB（Object Link and Embed DataBase，对象链接和嵌入数据库）
做为技术的骨架。OLE DB是目前应用更为广泛的一种数据技术标准接口，定义了统一的COM接口做为存取各类异质数据源的标准，并且封装
在一组COM对象之中。5.1.2 DAO和OLE DBOLE DB将传统的数据库系统划分为多个逻辑组件，这些组件之间相对独立又相
互通信。这种组件模型中主要是由三个部分组合而成。数据提供者（Data Providers）是通过OLE DB提供数据存储的软件组件
，例如文本文件、主机上的数据库，电子邮件存储，都可以是数据提供者。有的文档把这些软件组件的开发商也称为数据提供者。数据使用者（Da
ta Consumers）是指任何需要访问数据的系统程序或应用程序，除了典型的数据库应用程序之外，还包括需要访问各种数据源的开发工
具或语言。数据服务组件（Service Components）可以执行数据提供者以及数据使用者之间数据传递的工作，例如，数据使用者
要向数据提供者要求数据时，是通过OLE DB服务组件的查询处理器执行查询的工作，而查询到的结果则由游标引擎（Cursor Engi
ne）来管理。5.1.2 DAO和OLE DB虽然OLE DB允许程序员存取各类数据，是一个非常良好的架构，但是由于OLE DB
太底层化，而且在使用上非常复杂，对程序员的技能要求较高。为了解决这个问题，Microsoft以COM技术封装OLE DB为ADO对
象，极大简化了程序员数据存取的工作，使其获得了更为广泛的使用。5.1.3 ODBC1992年Microsoft和Sybase、D
igital共同制定了ODBC（Open Database Connectivity，开放数据库互连）接口标准，以单一的ODBC
API来存取各种不同的关系数据库。ODBC它建立了一组规范，并提供了一组对数据库访问的标准API。这些API独立于不同厂商的DBM
S，也独立于具体的编程语言，利用SQL语言来完成其大部分任务。一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS，所有的数
据库操作由对应DBMS的ODBC驱动程序完成，其最大的优点就是能以统一的方式处理所有的数据库。5.1.3 ODBC要访问一个数据
库，首先必须用ODBC管理器注册一个ODBC数据源，管理器根据这个数据源提供的数据库位置、数据库类型及ODBC驱动程序等信息，建立
起ODBC与具体数据库的联系。这样，只要应用程序将数据源名称提供给ODBC，ODBC就能建立起与相应数据库的连接。目前，ODBC已
经是一个稳定并且执行效率良好的数据存取引擎。ODBC仅支持关系数据库，以及传统的数据库数据类型，并且只以C/C++语言API形式提
供服务。5.1.3 ODBC【例5.9】有SQL Server数据源，服务器名为Server1，数据库名为SaleProduct
，采用SQL Server方式身份验证，登录名为sa，密码为123456。在C#程序中利用ODBC实现连接数据库，并在窗口中列出全
部客户的信息。本例设置ODBC数据源名称为LinkToSaleProduct。窗口名称为FormListCustomer，客户的信
息利用dataGridView控件显示，控件名称为dataGridViewListCustomer。程序的主要代码private
void FormListCustomer_Load(object sender, EventArgs e){ string
strConn ="DSN=LinkToSaleProduct;UID=sa;PWD=123456;"; //创建
ODBC连接对象： OdbcConnection con = new OdbcConnection(strConn);
DataTable dt = new DataTable(); con.Open(); //发送一条SQL语
句并执行： OdbcDataAdapter odbcda = new OdbcDataAdapter("SELECT cus
tID, custName, custCity, custPhone FROM Customer;",con); DataS
et ds = new DataSet(); //处理结果集： odbcda.Fill(ds); thi
s.dataGridViewListCustomer.DataSource = ds.Tables[0].DefaultView;
}运行结果5.1.4 JDBCJDBC（Java DataBase Connectivity，Java数据库连接）是一个面向对
象的应用程序接口（API），由一组用Java语言编写的类和接口组成，通过它可访问各类关系数据库。JDBC是Java核心类库的一部分
。JDBC与ODBC类似，JDBC API中定义了一些Java类分别用来表示与数据库的连接（Connections）、SQL语句（
SQL Statements）、结果集（Result Sets）以及其它的数据库对象，使得Java程序能方便地与数据库交互并处理所
得的结果。使用JDBC，所有Java程序都能通过SQL语句或数据库中的存储过程来存取数据库。5.1.4 JDBC要通过JDBC来
存取某一特定的数据库，必须有相应的JDBC驱动程序，它通常由生产数据库的厂家提供，是连接JDBC API与具体数据库之间的桥梁。通
常，Java程序首先使用JDBC API来与JDBC Driver Manager交互，由JDBC Driver Manager载
入指定的JDBC drivers，以后就可以通过JDBC API来操纵数据库。5.1.4 JDBC【例5.10】有SQL Ser
ver数据源，服务器名为Server1，数据库名为SaleProduct，采用SQL Server方式身份验证，登录名为sa，密码
为123456。在Java程序中利用JDBC实现连接数据库，并在窗口中列出全部客户的信息。本例JFrame窗口的类名称为Custo
mer_List，客户的信息利用JTable控件显示，控件名称为jTable1。程序由两个类文件组成，DBConnection类用
于实现与数据库的连接， Customer_List类用于实现数据的查询和窗口显示。5.1.4 JDBCpublic class
DBConnection { public static Connection getConnection() throws
SQLException { try { Class.forName("com.mi
crosoft.sqlserver.jdbc.SQLServerDriver"); } catch (ClassNo
tFoundException ex) { ex.printStackTrace();
return null; } String connectionUrl = "jdbc:sqlser
ver://localhost:1433;" + "databaseName=SaleProduct;use
r=sa;password=123456;"; Connection con = DriverManager.get
Connection(connectionUrl); return con; }}} catch (SQLEx
ception ex1) { } } } //其余代码略}5.1.4
JDBCpublic class Customer_List extends javax.swing.JFrame { D
efaultTableModel dm = null; Connection conn = null; StringB
uffer sBuf= new StringBuffer(); public Customer_List() {
initComponents(); dm = (DefaultTableModel) this.jTable1.
getModel(); dm.setRowCount(0); try {
//创建JDBC连接对象： conn = DBConnection.getConnection()
; //发送一条SQL语句并执行： PreparedStatement p
Stat = conn.prepareStatement("SELECT custID, custName, custCity,
custPhone FROM Customer ORDER BY custCity;"); Resul
tSet rs = pStat.executeQuery(); 5.1.4 JDBC//处理结果集：
while (rs.next()) { Vector vc =
new Vector(); vc.add(rs.getString("custID"));
vc.add(rs.getString("custName"));
vc.add(rs.getString("custCity")); vc.add(rs
.getString("custPhone")); dm.addRow(vc);
} } catch (SQLException ex) { ex.p
rintStackTrace(); }finally { try {
if (conn!= null) { conn.close();
conn = null; }
} catch (SQLException ex1) { } }
} //其余代码略}运行结果5.1.5 ADO和ADO.NETADO（ActiveX Data Objects，Activ
eX数据对象）是Microsoft提出的一种面向对象的应用程序接口（API）是一个用于存取数据源的COM组件，用以实现访问关系或非
关系数据库中的数据。ADO是对当前微软所支持的数据库进行操作的最有效和最简单直接的方法之一。一般的方法是利用ADO Connect
ion对象创建一个到达某个数据源的开放连接，通过此连接可以对一个数据库进行访问和操作。ADO是应用程序和数据底层的一个中间层，AD
O对象通过OLE DB间接取得数据库中的数据。5.1.5 ADO和ADO.NETADO.NET也是一种数据访问技术，使得应用程序
可以连接到数据存储，并以各种方式操作存储在其中的数据。该技术基于.NET Framework，与.NET Framework类库的
其余部分高度集成，是在.NET编程环境中优先使用的数据访问接口。ADO.NET在.NET Framework中提供最直接的数据访问
方法。ADO.NET软件栈包含两个主要部分：提供者和服务。传统ADO与ADO.NET存在很大的区别。5.1.5 ADO和ADO.
NET在有连接模式下ADO.NET的数据库访问通常包括以下步骤：(1) 通过数据库连接类（Connection）连接到数据库，如S
QL Server服务器、Access数据库文件等。(2) 通过数据库命令类（Command）在数据库上执行SQL语句，包括更新（
Update）、插入（Insert）、删除（Delete）、查询（Select）等。Command表示一个能被数据提供者处理的命令
，可以返回一个Recordset记录集或执行一个查询动作。(3) 如果是查询语句，还可以通过数据读取器类（DataReader）进
行只读向前读取数据记录。(4) 数据库操作完成后通过连接类（Connection）关闭数据库连接。5.1.5 ADO和ADO.N
ET【例5.11】有SQL Server数据源，服务器名为Server1，数据库名为SaleProduct，采用SQL Serve
r方式身份验证，登录名为sa，密码为123456。在C#程序中利用ADO实现连接数据库，并在窗口中实现新客户的添加。本例窗口名称为
FormInsertCustomer，客户的信息利用TextBox控件输入，控件名称分别为txtCustID、txtCustNam
e、txtCustCity、txtCustPhone实现添加新纪录的主要代码string strConn = "Data Sour
ce= Server1;Initial Catalog=SaleProduct; uid=sa;pwd=123456"; //创建
JDBC连接对象：SqlConnection con = new SqlConnection(strConn);con.Open(
);try{ //发送一条SQL语句并执行： StringBuilder strSQL = new
StringBuilder(); strSQL.Append("INSERT INTO Customer(custID,cu
stName,custCity, custPhone)"); strSQL.Append(" VALUES(''" + thi
s.txtCustID.Text.Trim().ToString() + "'',''" + this.txtCustName.Tex
t.Trim().ToString() + "'',"); strSQL.Append("''" + this.txtCustC
ity.Text.Trim().ToString() + "'',''" + this.txtCustPhone.Text.Trim(
).ToString() +"'')"); 实现添加新纪录的主要代码(续)using (SqlCommand cmd = ne
w SqlCommand(strSQL.ToString(), con)) { cmd.Exec
uteNonQuery(); MessageBox.Show("信息增加成功！"); }
strSQL.Remove(0, strSQL.Length);} catch (Exception ex) {
MessageBox.Show("错误：" + ex.Message, "错误提示", MessageBoxButtons. OK
Cancel, MessageBoxIcon.Error);} finally { if (con.State == Con
nectionState.Open) { con.Close(); con.Dispose();
}}运行结果5.2 存储过程5.2.1 基本概念存储过程通常用来执行管理任务或应用复杂的业务规则，由SQL语句和过程控制语
句组成。存储过程存储在数据库服务器上，是独立于数据表之外的数据库对象。存储过程通常在第一次执行时进行编译，编译好的代码保存在高速缓
存中以便供用户调用，只需编译一次，可多次执行。存储过程------------------------存储过程同其它编程语言中的过
程或函数相似，有一个作为标识的过程名，包含执行数据库操作的编程语句，可接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理
。存储过程的分类(1) 系统存储过程系统存储过程由系统定义，其名称以“sp_”为前缀，主要用于从系统表中获取信息，如列出服务器上的
所有数据库、查看某个表的所有信息、添加或修改登录帐户的密码等。这些存储过程为用户管理数据库、获取系统信息、查看系统对象提供了很大的
帮助。在SQL Server中，当创建一个新数据库时，会在新数据库中自动创建一些系统存储过程。在使用以sp_开头的系统存储过程时，
SQL Server首先在当前数据库中寻找，如果没有找到，则再到master数据库中查找并执行。存储过程的分类(2) 用户自定义存
储过程用户自定义存储过程类似编程语言中的用户自定义函数，是由用户在自己的数据库中创建，并能完成某一特定功能的存储过程。本节主要介绍
用户自定义存储过程。存储过程的分类(3) 扩展存储过程扩展存储过程是指SQL Server的实例动态加载和运行的动态链接库DLL，
这些DLL通常是使用外部程序语言编写的。扩展存储过程的名称以“xp_”为前缀。扩展存储过程主要用于获取系统信息，如系统上可用的磁盘
空间、查看某个目录下所有子目录的结构、获取某个文件的属性等。存储过程的分类(4) 临时存储过程临时存储过程的名称以“#”和“##”
为前缀，“#”表示本地临时存储过程，“##”表示全局临时存储过程，它们存储在tempdb数据库中。存储过程的分类(5) 远程存储过
程远程存储过程是在远程服务器的数据库中创建和存储的过程。这些存储过程可被各种服务器访问，向具有相应许可权限的用户提供服务。远程存储
过程不参与事务处理。例如，如果执行对远程SQL Server实例进行更改的远程存储过程，则不能回滚这些更改。通常可以使用分布式查询
和EXECUTE命令执行一个远程存储过程。存储过程的优点 改善SQL语句的执行性能 减少网络流量 增强SQL语言的功能和灵活性 提
高系统的安全性 提高开发效率和应用程序的可维护性把过多的业务写入存储过程也是不合适的SQL语言的处理功能相对简单，不利于调试复杂功
能的程序，当存储过程太多时维护会比较困难。存储过程会给服务器带来额外的压力，其移植性也较差，在升级到不同的数据库时比较困难。在实际
运用中把经常用到的查询动作编写成一个存储过程，并利用参数实现动态查询过程来响应客户的要求；实现在服务器端进行批量数据处理等操作；利
用系统为用户定义的管理级别存储过程实现数据的管理、配置和监控等。5.2.2 Transact-SQL基本语法Transact-S
QL（以下简称T-SQL）是SQL Server附带的语言，用于创建和管理数据库对象，以及插入、删除、修改、查询数据。它是对SQL
标准语言的扩展。1．变量的声明和赋值在SQL Server中，声明变量的语法形式是： DECLARE <变量名> <数据
类型> [ = value ] [,...n]变量名必须以“@”字符开头，变量名必须符合标识符规则。数据类型可以是由系统提供的或用
户定义的数据类型，但是变量不能是text、ntext或image数据类型。可以一次仅仅声明一个变量，也可以一次声明多个变量。1．变
量的声明和赋值为变量赋值的语法形式是： SET <变量名> = 常量表达式使用SET不能将查询得到
的值赋给变量，如果要将查询得到的值赋给变量，需要使用SELECT设置变量值。【例5.12】 声明两个变量，并为其赋值。 DECLA
RE @teacher_id char(4), @order_date datetime SET @teacher_id = ''0
541'' SET @order_date = ''1/01/2010'' 【例5.13】 声明一个变量，并使用查询结果为其赋值。
DECLARE @test numeric(10,2); SELECT @test=(SELECT MAX(pdPrice) FR
OM Product) PRINT @test 2．控制流语句(1) 定义语句块 使用控制流关键字BEGIN和END，可以将一组T
-SQL语句定义为语句块，语法格式为： BEGIN sql_statement [ .
..n ] END 2．控制流语句(2) 条件控制语句 条件控制语句的语法格式为： IF <条件表达
式> { sql_statement | statement_block } [ ELSE
{ sql_statement | statement_block } ](3) 循环控制语句循环控制语句的语法格式为：
WHILE <条件表达式> { sql_statement | statement_block
| BREAK | CONTINUE } BREAK语句的作用是结束W
HILE循环，将执行出现循环结束的标记（或END关键字）后面的语句。CONTINUE语句的作用是忽略CONTINUE关键字后面的任
何语句，使 WHILE 循环重新开始执行。3．USE语句USE语句用于设置当前数据库。【例5.14】 将当前数据库设置为SaleP
roduct。 USE SaleProduct GO4．GO语句SQL Server应用程序可以将
多个T-SQL语句作为一个批处理发送到SQL Server的实例来执行，该批中的语句被编译成一个执行计划。SQL Server实用
工具将GO解释为应该向SQL Server实例发送当前批T-SQL语句的信号。当前批语句由上一个GO命令后输入的所有语句组成，如果
是第一条GO命令，则由会话或脚本开始后输入的所有语句组成。GO不是T-SQL语句，它是sqlcmd和osql实用工具以及SQL S
erver Management Studio代码编辑器识别的命令。GO命令和T-SQL语句不能在同一行中。4．GO语句语法格式：
GO [count] 其中count 为正整数，指定GO之前的批处理执行的次数。需要
注意的是，用户必须遵守使用批处理的规则。例如，在同一批处理中，创建数据库之后不能直接使用其新建的数据库；局部变量的作用域限制在一个
批处理中，不可在GO命令后引用。5．注释单行注释可以使用注释符“--”多行注释可以使用注释符“/ … /”5.2.3 创建和
执行存储过程数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。注意：不能将CREATE PROCEDURE语句与其它
SQL语句组合到单个批处理中。通常创建存储过程时，应指定所有输入参数所有向调用过程或批处理返回的输出参数执行数据库操作的编程语句返
回至调用过程或批处理以表明成功或失败的状态值。1．不带参数的存储过程使用CREATE PROCEDURE语句创建不带参数的存储过
程，语法格式如下： CREATE PROCEDURE <存储过程名> [ WITH { RECOMPI
LE | ENCRYPTION | RECOMPILE, ENCRYP
TION } ] AS sql_statement [ ...n ] 1．不带参数的存储过程说明存储过程的
名称必须符合标识符规则，且对于数据库及其所有者必须唯一；RECOMPILE选项用于指定SQL Server不缓存该过程的计划，该过
程将在运行时重新编译；ENCRYPTION选项用于指定SQL Server将CREATE PROCEDURE语句的原始文本转换为加
密格式，可防止将存储过程作为SQL Server复制的一部分发布；sql_statement [ ...n ]是指构成过程主体的一
个或多个T-SQL语句，可以使用可选的BEGIN和END关键字将这些语句括起来。1．不带参数的存储过程对存储在服务器上的存储过程，
可以使用EXECUTE 命令执行它，其语法格式如下： EXECUTE <存储过程名> 【例
5.15】在商品销售管理数据库SaleProduct中，创建一个存储过程up_TopOrders，要求查询总金额排名前5位和后5位
的订单号、订单总金额和这些订单的客户名称、所在城市。CREATE PROCEDURE up_TopOrders AS BEGIN
SELECT TOP 5 Orders.orderID ,orderAmount,
Orders.custID , custName, custCit
y FROM Customer, Orders WHERE Customer.cus
tID = Orders.custID ORDER BY orderAmount DESC; SE
LECT TOP 5 Orders.orderID ,orderAmount,
Orders.custID , custName, custCity
FROM dbo.Customer, Orders WHERE Customer.custID =
Orders.custID ORDER BY orderAmount ASC; END【例5.16】在
SQL Server Management Studio中执行存储过程up_TopOrders。 USE SaleProdu
ct GO EXECUTE up_TopOrders GO2．带参数的存储过程使用CREATE PROCED
URE语句创建带参数的存储过程，语法格式如下： CREATE PROCEDURE <存储过程名> [
{ <参数名称> <数据类型> } [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n ]
2．带参数的存储过程说明可以声明一个或多个参数。必须使用“@”符号作为第一个字符来指定参数名称，参数名称必须符合标识符的规则。默认
情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。default为参数的默认值。如果为参数定义了默认值，则
无需指定此参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符(%、
_)OUTPUT选项用于指定参数是输出参数。该参数的值可以返回给存储过程的调用方。通常text、ntext和image参数不能用作
OUTPUT参数。除了使用OUTPUT参数，也可以使用return语句返回值。2．带参数的存储过程【例5.17】在商品销售管理数据
库SaleProduct中，创建一个存储过程up_SearchProduct，要求该存储过程有一个输入参数，用于接收商品名称。执行
该存储过程时，将根据输入的商品名称列出所有的商品编号、生产厂商、价格，并计算出该商品的平均价格。 2．带参数的存储过程CREATE
PROCEDURE up_SearchProduct @productName varchar(20)AS DECLA
RE @avg numeric(10,2); SELECT pdID, pdSupplier, pdPrice
FROM product WHERE pdName =@productName; SELECT @
avg=AVG(pdPrice) FROM product WHERE pdName =@pr
oductName; SELECT ''该商品的平均价格为：''+STR(@avg)2．带参数的存储过程对于带参数的存储过程，执
行时应该提供参数值。可以采用以下两种方式：(1) 按位置传递参数在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要
与定义的参数的顺序一致。(2) 使用参数名称传递参数在调用存储过程时，可以使用@parameter = value的形式给出参数值
。采用此方式，参数如果多于一个时，给出的参数顺序可以与定义的参数顺序不一致，还可以省略那些已提供默认值的参数，但是必须按此种形式提
供所有的参数值。2．带参数的存储过程【例5.18】在SQL Server Management Studio中执行存储过程up_S
earchProduct。USE SaleProductGOEXECUTE up_SearchProduct ''羽毛球''GO或
：USE SaleProductGOEXECUTE up_SearchProduct @productName=''羽毛球''GO2
．带参数的存储过程【例5.19】在商品销售管理数据库SaleProduct中，创建一个存储过程up_SearchProductOu
tput，要求该存储过程有一个输入参数，用于接收商品名称。有一个输出参数，用于返回该商品的平均价格。执行该存储过程时，将根据输入的
商品名称列出该商品的所有商品编号、生产厂商、价格。2．带参数的存储过程CREATE PROCEDURE up_SearchProd
uctOutput @productName varchar(20),@avgPrice numeric(10,2) OUT
PUTAS SELECT pdID, pdSupplier, pdPrice FROM product
WHERE pdName =@productName; SELECT @avgPrice=AVG(pdPrice)
FROM product WHERE pdName =@productName;2．带参数的存储过程【例5.20
】执行存储过程up_SearchProductOutput，并打印其输出参数。USE SaleProductGODECLARE
@avg numeric(10,2)EXECUTE up_SearchProductOutput ''羽毛球'',@avg OUT
PUTPRINT ''该商品的平均价格为：''+STR(@avg)GO2．带参数的存储过程【例5.21】在商品销售管理数据库SaleP
roduct中，创建一个存储过程up_SearchProductReturn，要求该存储过程带一个输入参数，用于接收商品名称。执行
该存储过程时，将根据输入的商品名称列出该商品的所有商品编号、生产厂商、价格，并使用return语句返回该类商品的平均价格。2．带参
数的存储过程CREATE PROCEDURE up_SearchProductReturn @productName varc
har(20)AS DECLARE @avg numeric(10,2); SELECT pdID, pdSupp
lier, pdPrice FROM product WHERE pdName =@productName;
SELECT @avg=AVG(pdPrice) FROM product WHERE pdNam
e =@productName; RETURN @avg;2．带参数的存储过程【例5.22】在SQL Server Mana
gement Studio中执行存储过程up_SearchProductReturn。USE SaleProductGODECLA
RE @avg numeric(10,2)EXECUTE @avg=up_SearchProductReturn ''羽毛球''
PRINT ''该类商品的平均价格为：''+STR(@avg)GO5.2.4 存储过程的修改和删除1．修改存储过程使用ALTER P
ROCEDURE语句修改存储过程，其语法格式如下 ALTER PROCEDURE <存储过程名> [ {
<参数名称> <数据类型> } [ = default ] [ OUTPUT ]] [ ,...n ] [
WITH { RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION } ] AS sql_statement [ ...n
]5.2.4 存储过程的修改和删除【例5.23】修改例5.17创建的存储过程up_SearchProduct，要求该存储过程的
输入参数，用于接收生产厂商名称。执行该存储过程时，列出该厂商生产的所有商品的编号、商品名称、价格。ALTER PROCEDURE
up_SearchProduct @productName varchar(10)AS SELECT pdID, pdNa
me, pdPrice FROM product WHERE pdSupplier =@productName;2．删除存储
过程使用DROP语句删除存储过程，其语法格式为： DROP PROCEDURE <存储过程名>2．删除存储过程
【例5.24】 删除例5.17中创建的存储过程up_SearchProduct USE SaleProduct GO
DROP PROCEDURE up_SearchProduct; GO5.2.5 获得有关存储过程的信息存储过程
被创建后，可以使用SQL Server提供的系统存储过程查看其信息使用sp_help查看存储过程的的名称、拥有者、类型和创建时间使
用sp_helptext查看存储过程的定义信息使用sp_depends查看存储过程的相关性。5.2.5 获得有关存储过程的信息【
例5.25】在SQL Server Management Studio中执行系统存储过程，查看存储过程up_TopOrders的信
息。USE SaleProductEXEC sp_help up_TopOrders EXEC sp_helptext u
p_TopOrders EXEC sp_depends up_TopOrders GO运行结果5.3 触发器5.3.1
基本概念 触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊存储过程。触发器不能通过名称被用户直接调用，不允许带
参数。一旦定义了触发器，任何用户对数据表进行更新操作时，服务器都会自动激活相应的触发器，在数据库管理系统核心层进行集中的完整性控制
。触发器可以包含复杂的T-SQL语句，可用于强制业务规则和数据完整性，其控制能力比第3章介绍的完整性约束更灵活，可以实施更为复杂的
检查和操作，具有更精细和更强大的数据控制能力。触发器与表紧密相连，可以看作表定义的一部分。按触发被激活的时机可以分为两种类型AFT
ER触发器，又称为后触发器该类触发器是在引起触发器执行的修改语句（INSERT、UPDATE、或DELETE）成功完成之后执行。如
果修改语句因语法错误或违反约束而失败，触发器将不会执行。因此，这些触发器不能用于违反约束的处理。此类触发器只能定义在表上，不能创建
在视图上。可以为每个触发操作（INSERT、UPDATE或DELETE）创建多个AFTER触发器。按触发被激活的时机可以分为两种类
型INSTEAD OF触发器，又称为替代触发器当引起触发器执行的修改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可在表
上定义，也可在视图上定义。对于每个触发操作（INSERT、UPDATE和DELETE）只能定义一个INSTEAD OF触发器。IN
STEAD OF触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。触发器的优点：触发器自动执行
触发器能够对数据库中的相关表实现级联更改触发器可以实现比CHECK约束更为复杂的数据完整性约束触发器可以包含复杂的处理逻辑，能够实
现复杂的数据完整性约束。触发器可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中可以存在多个同类触发器（INSERT、U
PDATE或DELETE），对于同一个修改语句可以有多个不同的对策以响应。5.3.2 创建触发器一个触发器由两部分组成：触发事件
及触发条件触发事件是指对数据库的插入、删除、修改等操作，在这些操作进行时，触发器被激发触发器条件用于确定DELETE、INSERT
或UPDATE语句是否导致触发器动作。条件成立，则由数据库管理系统执行触发器动作。动作这些动作可以是一系列对数据库的操作，如撤销触
发事件所做的操作等。5.3.2 创建触发器在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名称、激发时机、激活触发器的
修改语句（INSERT、UPDATE或DELETE）。只能在当前数据库中创建触发器，表的所有者具有创建触发器的默认权限，不能将该权
限转给其他用户。5.3.2 创建触发器使用CREATE TRIGGER语句定义触发器，其一般格式为： CREATE TR
IGGER <触发器名> ON <表名|视图名> { FOR | AFTER | INSTEAD OF }
<触发事件> AS <触发器动作体>说明(1) <触发器名>是要建立的触发器名字。触发器名必须符合标识符规
则，但不能以#或##开头。(2) <表名或视图名>指触发器的目标表或目标视图，当这个表的数据发生变化时，将激活定义在该表上相应触发
事件的触发器。(3) AFTER选项用于指定触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。此类型触发器不能在视图
上定义。(4) INSTEAD OF选项用于指定触发器的优先级高于触发语句的操作。INSTEAD OF 触发器不可以用于使用 WI
TH CHECK OPTION 的可更新视图。(5) 触发事件可以是INSERT、DELETE或UPDATE，也可以是这几个事件的
组合。(6) 触发动作体可以是一组T-SQL语句，也可以是对已创建存储过程的调用。5.3.2 创建触发器【例5.26】在商品销售
管理数据库SaleProduct中，为订单表(Orders)建立一个UPDATE触发器，规定订购日期列(orderDate)的值不
允许修改。5.3.2 创建触发器CREATE TRIGGER Orderdate_update ON Orders
FOR UPDATEAS BEGIN IF UPDATE(orderDate) RAI
SERROR(''操作错误，订购日期不允许修改！''，10，1) ROLLBACK END被触发时的提示信息5.3
.2 创建触发器【例5.27】在商品销售管理数据库SaleProduct中，为订单表（Orders）建立一个DELETE触发器，
其作用是当删除订单表中的记录时，检查“订单明细表（OrderDetail）中是否存在该订单的细节记录，如果存在则提示不允许删除该订
单的信息。 5.3.2 创建触发器CREATE TRIGGER Order_delete ON Orders FOR DELETEAS DECLARE @orderId varchar(16) SELECT @orderId=orderId FROM Deleted IF EXISTS (SELECT FROM OrderDetail WHERE orderId=@orderId) BEGIN PRINT ''该订单存在细节信息，不能被删除！'' ROLLBACK ENDGO被触发时的提示信息deleted表和inserted表SQL Server触发器经常使用两个特殊的临时数据表：deleted表和inserted表。这两个表都存在于高速缓存中，由SQL Server自动创建和管理，不允许用户直接对其修改。用户可以使用这两个临时表来检测某些修改操作所产生的效果，以及设置触发器操作条件。例如，可以使用SELECT语句来检查INSERT和UPDATE语句执行的插入操作是否成功，触发器是否被这些语句触发等。deleted表deleted表中存储着被DELETE和UPDATE语句影响的旧数据行。在执行DELETE和UPDATE语句过程中，指定的数据行被用户从基本表中删除，然后转移到了delete表中。一般来说，在基本表和delete表中不会存在有相同的数据行。inserted表inserted表中存储着被INSERT和UPDATE语句影响的新的数据行。当用户执行INSERT和UPDATE语句时，新的数据行被添加到基本表中，同时这些数据行的副本被复制到inserted临时表中。一个典型的UPDATE事务实际上是由两个操作组成。首先，旧的数据行从基本表中转移到delete表中，然后将新的数据行同时插入基本表和inserted临时表。5.3.3 禁止和启用触发器针对某个表创建的触发器，可以根据需要，禁止或启用其执行。禁止触发器或启用触发器执行只能在SQL Server Management Studio中进行，其语法格式为： ALTER TABLE <表名> { ENABLE | DISABLE } TRIGGER { ALL | <触发器名> [ ,...n ] }其中，ENABLE选项为启用触发器，DISABLE选项为禁用触发器。5.3.3 禁止和启用触发器【例5.28】在商品销售管理数据库SaleProduct中，禁用订单表（Orders）的Order_delete触发器。 ALTER TABLE Orders DISABLE TRIGGER Order_delete5.3.4 修改和删除触发器修改触发器的定义，可以使用ALTER TRIGGER语句。 ALTER TRIGGER <触发器名> ON <表名|视图名> { FOR | AFTER | INSTEAD OF } <触发事件> AS <触发器动作体>5.3.4 修改和删除触发器只有触发器的所有者才有权删除触发器。删除一个或多个触发器，可以使用DROP TRIGGER语句，语法如下： DROP TRIGGER <触发器名> [ ,...n ]当某个表被删除后，该表上的所有触发器将同时被删除，但是删除触发器不会对表中数据有影响。5.3.4 修改和删除触发器【例5.29】 删除在订单表上的Orderdate_update触发器。DROP TRIGGER Orderdate_update5.4 小结5.4 小结嵌入式SQL的实现方式、使用规定及使用技术，面向对象应用程序访问数据库的常用接口及使用方法，面向对象应用程序一般都通过API接口实现嵌入式SQL的功能，这种方法不需要专用的SQL预编译程序。DAOODBCJDBCOLE DBADOADO.NETT-SQL基本语法SQL Server的存储过程SQL Server的触发器。重点掌握基本概念、方法和设计思想，并能运用这些方法解决实际问题。 |
|
