SQL Server 7.0 入门

最新推荐文章于 2024-04-29 23:47:34 发布

xietangz

最新推荐文章于 2024-04-29 23:47:34 发布

阅读量1.2k

点赞数

分类专栏：数据库-SqlServer 文章标签： sql server 存储 table 数据库 join 服务器

数据库-SqlServer 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

SQL Server 7.0 入门（一）
--------------------------------------------------------------------------------
2001-08-16 00:00:00

交互式SQL（Transact SQL，TSQL）
它是SQL Server的查询语言。提供以下命令：
· 创建和数据库对象。
· 访问和修改数据。
· 数据聚合（aggregation，又称聚集）。
· 实施安全措施。数据库对象
 数据库对象是数据库中的物理对象。这些对象拥有唯一的名字，并保存数据和数据关系信息。SQL Server定义了如下几种对象：
1、表（table）
表是用来存储数据的两维数组，它有行和列。列也称为表属性或字段，表中的每一列拥有唯一的名字，每一列包含具体的数据类型，这个数据类型由列中的数据类型定义。
2、视图（view）
视图是虚拟的表，它只包含表的一部分。与表不同的是，保存在视图中的数据并不是物理存储的数据，它是由表派生的，派生表被称为视图的基本表。视图的定义存储在数据库中。
3、约束条件（constraint）
约束条件定义数据的完整性和有效性。约束条件为列中的值建立规则。换而言之，如果在某一列上定义了结束条件，则插入这一列的每一个值需要通过约束条件检查。约束条件是在触发器和规则上保证数据完整性和有效性的较佳选择。SQL Server Query Optimizer利用约束条件产生出低成本的查询计划。约束条件有如下类型：
· NOT NULL 这个约束条件要求列中不能有NULL值。
· CHECK 为列指定能拥有的值的集合后，检查约束条件。列中任何在定义之外的数据都为无效数据。有效值集合称为列的域。
· PRIMARY KEY 主关键字是列或列组合，它用来唯一标识一行。
· FOREIGN KEY 用来定义两个表之间的父子关系。如果一个关键字既是一个表的主关键字的一部分，同时又是另一个表的主关键字，则称它为外来关键字。外来关键字用来定义数据的引用完整性。
· UNIQUE 唯一约束条件是指无任何两行在列中有相同的NON-NULL值。唯一性由主关键字保证，但他们不允许NULL值，并且每一个表只能有一个关键字。
4、默认值（default）
默认值是为列定义的值，如果在插入一行时没有提供某一列的值，则此列使用默认值，默认值可以是以下的一种：
· 常量
· 数学表达式
· 内部函数（Built-in function）
5、规则（rule）
规则执行和CHECK约束条件同样的功能。但规则和约束条件不同的是：规则作为独立的对象存在，它可以用于多个表，而约束条件作为表的一部分存储。然而，规则是作为向后兼容特点而提供的，建议用户使用约束条件。
6、触发器和存储过程
触发器和存储过程是一个TSQL命令集，它们作为一个对象存储在数据库中。对象命名约定
SQL Server用三段式（three-part）名字标识对象：
<数据库名>.<所有者名>.<对象名>
前两部分可以省略，系统有一个默认值。数据库名的默认值是当前数据库，所有者名的默认值是数据库所有者（dbo）。数据类型
 任何包含数据的对象都有一个数据类型和它关联。数据类型是指定对象能包含何种数据的属性。
 SQL Server数据类型
数据类型
说 明
同 义 Bit
1位，值为0或1
Int Integer
4字节，值为-2^31~2^31-1 
Smallint
2字节，值为-2^15~2^15-1 
Tinyint
1字节，值为0~255 
Decimal (p,s)
数字数据，固定精度为P，宽度为S
Numeric Money
8字节，存放货币类型，值为-2^63~2^63-1 
Small money
4字节，存放货币类型，值为-214748.3648~+214748.3647近似数值数据类型 
Float (n)
N在1~24之间，4字节，7位精度
N=1~7为real 
N在25~53之间，8字节，15位精度
=8~15为float Datetime
8字节，描述某天的日期和时刻，值的精确度为1/300秒 
Smalldatetime
4字节，描述某天的日期和时刻，精度为分钟 
Cursor
对游标的引用 
Timestamp
8字节，存放在数据库内唯一的数据 
Uniqueidentifier
16字节，存放全局唯一标识（GUID） 
Char (n)
非unicode字符串的固定长度，n=1~8000
Character (n) Varchar (n)
可变长度，非unicode字符串n=1~8000
Char varying(n) Text
服务器代码页中可变长度非unicode数据。最大长度为231-1个字符 
Nchar
固定长度unicode字符串n=1~4000
National character (n),
National char(n) Nvarchar
固定长度unicode字符串n=1~4000
National character varying(n) Ntext
可变长度unicode数据，最大长度为230-1个字符
National text Binary (n)
固定长度二进制数据，n在1~8000之间，存储空间为n+4字节 
Varbinary (n)
可变长度二进制数据，n=1~8000
Binary varying (n) Tmage
可变长度二进制数据，大小为0~231-1 
注意：
1） 对于数值型数据类型，宽度（scale）是指存储在小数点后的数字位数，而精度（precision）是指能存储的包含小数点在内的所有数字位数。
2） money和small money的存储宽度为4。
3） 时间戳列值在每一行更新时系统自动更新，时间戳列不能是关键字或关键字的一部分。
4） 唯一标识数据类型不能使用算术操作符（如+、-等），这种数据类型只能使用相等比较操作。Unicode是所有字符集的一致存储数据的标准。它要使用两倍于非Unicode数据存储的存储空间。

存取数据
select语句
1．选择表中的所有列
“*”对Select语句有特殊意义。它指定表中的所有列，而不用列出列的名字。列的顺序和表中的顺序相同。
2．选择不同的值
如果被选择列表中的列有重复值，这时“distinct”关键字可以用来忽略重复值。
注意：如果Select list中多于一列，则distinct关键字对它们总体有效。如果一列有重复值，而其他列的值是唯一的，则有重复值的一列包含在结果中。
3．在结果数据集合中对列重命名
结果中列的默认名字是源数据库中的列名，用户可以用自己指定的列名来代替默认的列名。
Select AuthorFirstName = au_fname from authors
--将列名由“au_fname”改成“AuthorFirstName”
4．选择计算值
在Select list中可以包含计算值或常量。计算值是在算术表达式的基础上计算而来的，它可包含在表中的一列或多列。
Select totalSale = price * ytd_sales from titles

条件选择（Where子句）
使用一个比较或逻辑操作在Where子句中指定过滤条件，来生成表中想得到的行。
1． 比较操作
比较操作能比较数值、字符和日期数据，返回TRUE或FALSE。
比较操作符
<（小于）
>（大于）
=（等于）

<>（不等于）
>=（大于等于）
<=（小于等于）

!=（不等于）
!<（不小于）
!>（不大于）

2． 逻辑操作
逻辑操作测试某些条件是否正确，并根据测试结果返回TRUE或FALSE。
· LIKE 如果操作和指定的字符串相同，则返回TRUE，指定的字符串也可包含通配符。
有通配符的like操作更有用。
 · “%”规定所有字符串可代替字符“%”的位置。任何在“%”之前或之后的指定字符串视为常量。如：“New%”表示所有以“New”开头的字符串，“%New”表示以“New”结尾的字符串。
 · “_”规定任何单个字符可代入“_”的位置。这在只有一个字符不同的相近字符的情况下非常有用。
 · “[]”规定使用方括号中定义的字符代替一个字符。方括号中可能是独立的字符（如[ahg]），也可是字符范围（如[c-i]）。
· “[^]”规定方括号中“^”之后的字符为不能用来代入的字符，它可以是独立字符（如[ahg]），也可是字符范围（如[c-i]）。
注意：可以在一个表达式中组合运用这些通配符。
· BETWEEN 如果操作数在提供的范围之内，则返回TRUE。
BETWEEN可和NOT操作符一同使用，如果数据在指定范围之外，则返回TRUE。
注意：BETTEN操作符是指定范围。例如，BETWEEN A and B表示所有在A和B之间的值，包括A和B。
· IN 如果操作数和任意的指定值相匹配，则IN操作符返回TRUE。指定值可以是一个常量值列表，也可由另外的查询（称为子查询）产生。
 IN操作符也可和NOT操作符联合使用，如果数据不在指定值中，它返回TRUE。
· AND 它结合两个表达式，如果两个表达式都为TRUE，则它返回TRUE，否则返回FALSE。
· OR 它结合两个表达式，如果两个表达式中有一个为TRUE，则它返回TRUE，如果两个都为FALSE，则返回FALSE。
· NOT 它对逻辑操作值求反，它可和大多数逻辑操作联合使用，对操作返回的值求反。
· SOME|ANY 这两个操作有相同的格式。它们把操作数和指定值的集合比较。如果操作数与任意指定值的比较返回TRUE，则它返回TRUE。比较操作可以是任意SQL Server定义的比较操作。指定值列表可以由另一个查询产生。
· ALL ALL操作和SOME及ANY有相似的格式。不同的是，如果所有操作数和指定值的比较返回TRUE，它才返回TRUE。

数据排序
 ORDER BY子句按指定的顺序对数据排序（ordering data）。它要求一个列名字列表或非负整数列表来指定列的位置。分别用ASC代表升序，DESC代表降序，默认为ASC。

限制返回行的数目
 不使用WHERE子句而限制结果中的行数是可能的。“TOP”子句能按指定数目或百分值来限制行数。

数据分组和计算聚合函数
 聚合（aggregate）函数计算表中数据的总和。SQL Server提供以下的聚合函数：
 · AVG 这个函数计算平均值。语法如下：
 AVG ([ALL | DISTINCT] expression)
 关键字DISTINCT只用来计算不同值的平均值，如果有许多重复值，这些值只计算一次，默认为ALL。
 Expression可以是涉及一列或多列的算术表达式。
 · MIN 这个函数查找所提供表达式中的最小值。语法如下：
 MIN (expression)
 · MAX 此函数的功能是在提供的表达式中查找最大值。语法如下：
 MAX (expression)
 注意：如果地字符串类型使用MIN和MAX，则输出依赖于为SQL Server定义的顺序。MIN和MAX不能在位上使用。
 · SUM SUM计算所有数据值的和。语法如下：
 SUM ([ALL | DISTINCT] expression)
 注意：SUM和AVG只能用于数值数据类型。
 · COUNT 计算表达式值的数目。语法如下：
 COUNT ([ALL | DISTINCT] expression)
 COUNT有另一种用法，它可以返回被选择的行数。
 如：SELECT NumRows = COUNT (*) FROM titles
 聚合函数忽略所有空值，但COUNT(*)除外。尽管所有聚合函数的计算基于无空值的情况，然而COUNT(*)计算所有的行（包括有空值的行）。

1． GROUP BY子句
GROUP BY子句在被定义的数据的基础上建立比较小的组，并且对每一个组进行聚合函数计算。换句话说，它产生每一组的总体信息。GROUP BY可以把多于一列当成组合列（Grouping Columns）。它总结组合列中不重复值的信息。
使用了GROUP BY子句的选择列表中只能包含以下项：
· 常量值。
· 组合列。
· 表达式。每个表达式为每组返回一个值（如聚合函数）。如果一列除了在组合列中外，还在选择列表中，则它有多个值给组合列的每一个不重复值，这种结构类型是不允许的。
2． GROUP BY和HAVING
HAVING子句用来向使用GROUP BY子句的查询中增加数据过滤准则。HAVING的用法和SELECT中的WHERE子句一样。在一个包含GROUP BY子句的查询中使用WHERE子句是可以的。HAVING和WHERE有相同的语法。HAVING和WHERE的不同这处是：
· 在WHERE子句中，在分组进行以前，去除不满足条件的行，在HAVING子句中，在分组之后条件被应用。
· HAVING可在条件中包含聚合函数，但WHERE不能。
注意：GROUP BY和HAVING子句不能使用文本或图像数据类型。
3． COMPUTE BY子句
COMPUTE BY子句可以得到详细或总的记录。它把数据分成较小的组，然后为每组建立详细记录结果数据集（象SELECT），它也可为每组产生总的记录（象GROUP BY）。
在COMPUT BY中，定义BY子句不是必要的。如果没有定义BY子句，则认为整个表为一个组，并且只有两个结果数据集产生，一个拥有所有详细记录，另一个只有一行，它拥有总记录。
注意：当在COMPUTE中使用BY时，则要求在所有组合列中包含ORDER BY。

Cube和Rollup操作
 CUBE和ROLLUP操作可比简单的GROUP BY产生更多的聚合值。在产生交叉标签报告（cross tab reports）时，这些操作非常有用。如果查询使用n个组合列，则有2n个计算聚合的组合。

从多个表中访问数据
 我们已讨论了如何访问单个表中的数据。从多个表中访问数据也是可能的。从多个表中访问数据称为连接表（joining a table）。
1、 CROSS JOIN（笛卡尔积）
CROSS JOIN是简单地、不加任何约束条件地把表组合。CROSS JOIN后结果的行数是连接前两个表行数的乘积。如果对两个分别有好几千行的表进行连接，则结果是不可想象的。
2、 INNER JOIN
INNER JOIN是组合两个表最常用的方法。INNER JOIN是基于一个判别式进行的，这个判别式称为连接条件。连接条件和WHERE子句一起定义。连接条件由来自两个表中的列组成，并使用一个比较条件来对列的值进行比较。通过比较的值包含在结果数据集中，以下是Inner JOIN的语法：
语法1：（ANSI 92）
Select <select_list>
FROM <table1> INNER JOIN <table2>
ON <table1>.<column name> = <table2>.<column name>
语法2：
Select <select_list>
FROM <table1>,<table2> WHERE <table1>.<column name> = <table2>.<column name>
在FROM 子句中可为表定义别名，并在任何地方都可用别名代替真名。
注意：如果作为连接条件的列中有空值，则空值不能和任何值匹配，因此结果中不包含有空值的行。
3、 Left Outer JOIN
在Inner JOIN中，只有在两个表中匹配的行才能在结果数据集中。但在Left Outer JOIN中，所有左边表中的行都出现在结果数据集中，如果左边表中的某一行在右边表中没有匹配的行，则以空值取代右边表中的值和它连接。
语法如下：（ANSI 92）
Select <select_list>
FROM <table1> LEFT OUTER JOIN <table2>
ON <table1>.<column name> = <table2>.<column name>
4、 Right Outer JOIN
Right Out JOIN和Left Outer JOIN相似，不同的是把右边的表作为外部表（所有右边表中的行包含在结果数据集中）。
语法如下：
Select <select_list>
FROM <table1> RIGHT OUTER JOIN <table2>
ON <table1>.<column name> = <table2>.<column name>
5、 Full Outer JOIN
在Full Outer JOIN中，所有两个表中的行都包含在结果数据集中。
语法如下：
Select <select_list>
FROM <table1> FULL OUTER JOIN <table2>
ON <table1>.<column name> = <table2>.<column name>

Case语句
 当对不同条件产生不同的结果值时，可使用Case语句。
 Case语句计算所有定义的条件，并按条件是否为真而返回结果。
语法如下：
CASE [<input_expression>]
WHEN <when_expression> THEN <result_expression>
[ELSE <else_expression>]
END
Input_expression是任何有效的SQL Server表达式或布尔表达式。
When_expression是任何有效的SQL Server表达式或布尔表达式。这个表达式和Input_expression比较，如果Input_expression没有定义，则When_expression应该是一个布尔表达式。
Result_expression是任何有效的SQL Server表达式。如果When_expression和Input_expression的比较返回TRUE（如果定义了Input_expression）或When_expression的值为TRUE，则计算表达式，并返回其结果。否则计算Else_expression中的表达式，并返回其结果。
例如：
SELECT au_fname,au_lname,
State=CASE state
 WHEN ‘CA’ THEN ‘California’
 WHEN ‘KS’ THEN ‘Kansas’
 END
FROM authors

UNION

 UNION语句把两个或多个查询的结果组合成一个结果集。
 语法如下：
 SELECT <select_list>
 FROM <table_list>
 WHERE <join_condition>
 UNION [ALL]
 SELECT <table_list>
 FROM <table_list>
 WHERE <join_condition>
 ALL关键字指定重复的数据也将包含在最终结果数据集中。如果需要，一个查询中可以有许多UNION语句。所有Select_list应该有相同数目的列，且是相同或兼容的数据类型。

Go命令 Go命令用来标志一个查询批处理（query batch）的结束。查询批处理是TSQL语句的集合，这些语句集合在一起执行。Go与Osql或SQL Server Query Analyzer一起使用。

管理文本和图像数据
 文本和图像数据类型列可以象其它列一样包含在SELECT语句的Select_list中。通过SELECT语句检索的最大文本大小由TEXTSIZE设置来控制。默认的TEXTSIZE的值是4096字节，使用SET TEXTSIZE语句可重新设置。当前的TEXTSIZE设置的值存放在全局变量@@TEXTSIZE中。
 另一种检索文本和图像数据的方法是通过TEXTPTR和READTEXT函数。TEXTPTR函数把列名字作为输入参数并以二进制格式返回文本指针。
 这个指针和被称为偏移量的读开始指针以及要读的字节数量一起传给READTEXT函数，以检索文本和图像数据。

修改数据
 TSQL也提供从数据库插入、修改、删除的命令。
插入
 INSERT命令用来在表中插入数据，语法如下：
 INSERT [INTO] <table_name> [(column_list)] VALUES (value_list)
 Value_list是和在Column_list中列相应的值。这些值可以是常量、TSQL变量或SQL Server内部函数。值的顺序应和column_list中列的顺序相对应。如果没有定义column_list，则值的顺序应和表中列的顺序对应。
 INSERT的Values_list也可通过SELECT语句或一个存储过程来提供。语法如下：
 INSERT [INTO] <table_name> [(column_list)]
 SELECT <select_list> FROM <table_name>

 INSERT [INTO] <table_name> [(column_list)]
 EXECUTE <procedure_name>
 这里的SELECT语句可包含多种连接。如果使用存储过程，则存储过程应返回能作为column_list的结果。
 时间戳（timestamp，又称时标）值不应被提供。同时，如果列的IDENTITY属性被定义，则这一列的值也不能提供。这些值都由系统产生。如果列有默认值，则它们的值不被提供，而使用默认值。

修改
 UPDATE语句用来修改行，语法如下：
 UPDATE <table_name> SET <column_name> = <value> [,<column_name> = <value>_n]
 WHERE <search_condition>]
 Serach_condition是要修改的行应满足的条件。没有WHERE子句的UPDATE语句将修改表中的所有行。在UPDATE语句中也可使用连接。

删除
 DELETE语句用来从表中删除行。语法如下：
 DELETE [FROM] <table_name>
 [WHERE <search_condition>] Serach_condition是删除行要满足的条件。一个无条件DELETE语句将删除表中的所有行。在DELETE语句中也可使用连接。

使用SQL Server开发应用程序
编写存储过程与触发器
       存储过程和触发器是由用户创建的、驻留在服务器的一组Transact SQL查询语句。触发器是系统在特定条件下执行的。存储过程能够改善应用程序的性能。当客户程序需要访问数据时，一般要经过5个步骤才能访问到数据：
1）  查询语句被发送到服务器。
2）  服务器编译SQL代码。
3）  优化产生查询的执行计划。
4）  数据引擎执行查询。
5）  结果发回客户程序。
存储过程是在创建时编译的，当存储过程第一次执行时，SQL Server产生查询执行计划并将其存储进来，以利于将来使用。当通过存储过程发出一个请求时，上述的第2和第3步就没有了，这能大大改善系统的性能。即使在第1步上也能提高性能。因为此时发送到服务器的语句只是一条存储过程的EXECUTE语句，而不是庞大的、复杂的查询。这种特性能降低网络的流量。
除了性能方面的改善外，存储过程还提供了方便地集中维护应用程序的功能。如果将查询嵌入到应用程序中。而又需要对查询进行改变，则应用程序需要重新编译，并重新发布到所有的客户端。而在存储过程中，修改对用户而言是透明的，它只需要在服务器上重新编译存储过程。
存储过程还能提供安全机制，尽管用户可能无权执行存储过程中的命令，但它却可能有权执行存储过程本身。有时候，系统管理员不会给用户以数据修改（UPDATE、INSERT和DELETE）的权力。创建的存储过程却能进行这些操作。当然用户需要拥有执行该存储过程的权力。

建立存储过程
       存储过程可以达到以下目的：
       · 带参数。
       · 返回状态值。
       · 调用其它存储过程。
       · 在远程服务器上执行。
       存储过程在“sysobjects”系统表中有一个表项，其类型为“P”。存储过程的文本存储在“syscomments”系统表中。创建存储过程需要使用Transact SQL命令CREATE PROCEDURE。
       例如：
       USE pubs
       GO

       CREATE PROCEDURE ap_GetAuthorsForPublisher
       AS
       SELECT a.au_lname,a.au_fname
       FROM authors a, titleauthor ta, titles t, publishers p
       WHERE a.au_id = ta.Au_id
       AND ta.Title_id = t.title_id
       AND t.pub_id = p.pub_id
       AND p.pub_name = ’New Moon Books’
       GO

       CREATE PROCEDURE语句的语法如下：
       CREATE PROC[DURE] procedure_name [;number]
              [@parameter_name ][OUTPUT] [,_n] ]
              [WITH {RECOMPILE | ENCRYPTION}]
              [FOR REPLICATION]
       AS
       Number是用来对相同名字的过程进行分组的整数。分组是将所有的过程通过drop procedure语句组合到一个分组中。
       @parameter_name指定参数的名称。
       RECOMPILE表示每次执行过程时都要进行编译。
       ENCRYPTION表示过程的文本在“syscomments”表中要加密。
       FOR REPLICATION表示过程不能在提交服务器上执行。

将参数传递给存储过程
       存储过程能够接受参数。
       注意：过程的参数也可以是用户定义的数据类型。

给参数一个缺省值
       用户还可以为存储过程中的参数定义缺省值。当在执行时没有提供所需的参数时，系统就使用缺省值作为参数。如果既没有定义缺省值，又没有在执行时提供参数，则SQL Server就会返回一个错误。在存储过程中定义缺省值，并使用一些逻辑检测是否指定了参数从而采取相应的行动，这是一种很好的习惯。
       例如：
       USE pubs
       GO

       CREATE PROCEDURE ap_GetAuthorsForPublisher
       @PublisherName varchar(40) = ‘New Moon Books’
       AS
       SELECT a.au_lname,a.au_fname
       FROM authors a, titleauthor ta, titles t, publishers p
       WHERE a.au_id = ta.Au_id
       AND ta.Title_id = t.title_id
       AND t.pub_id = p.pub_id
       AND p.pub_name = @PublisherName
       GO

建立存储过程体
       存储过程逻辑驻留在存储过程体中。一个存储过程体中可以包含任意条Transact SQL语句。下面的Transact SQL语句不能在任何存储过程体中出现：
       · CREATE DEFAULT
       · CREATE TRIGGER
       · CREATE PROCEDURE
       · CREATE VIEW
       · CREATE RULE
1、  局部变量
局部变量保持存储过程中间值。当一个值在存储过程中需要多次，或者某个查询的结果需要在随后的查询中使用时，需要使用局部变量。在这些情形下，值被存储在局部变量中，并可用于将来的使用。本地变量的名称以“@”符号开头。变量的名称可以包含字符和数值。局部变量在使用前需要进行类型声明。对局部变量进行赋值需要使用SELECT语句。SELECT可以从一个表中检索出值并将其赋给某个变量，也可以给变量赋一个常量值。一个简单的SELECT语句可以给多个局部变量赋值。
例如：
DECLARE @var1 integer, @var2 varchar(20)
SELECT @var1 = 32,
           @var2 = ‘MyAge’
如果从SELECT查询中没有返回任何数据，而SELECT又要将数据的值赋予局部变量，则该局部变量的值将不会发生改变。
2、  条件词句
存储过程中提供的条件语句包括：
· IF……ELSE语句。
· WHILE语句。
1）  IF……ELSE语句。在该语句中包含三个部分：布尔运算表达式，IF语句块和ELSE语句块。语法如下：
IF (boolen_expr)
  {statements}
ELSE
  {statements}
在IF或ELSE语句块中可以有多条语句，这种情形下，需要语句BEGIN和END来标志语句块。
2）  WHILE语句。WHILE语句用于处理直到某个条件为TRUE前重复执行的语句。语法如下：
WHILE (boolen_expr)
  BEGIN
  statement(s)
BREAK
Statement(s)
CONTINUE
              END
            BEGIN和END语句标志循环体。BREAK语句结束循环的执行（即走到END语句之后）。CONTINUE语句将控制处理过程回到循环的开始处（即BEGIN语句的右边）。
            注意：如果有两个或多个WHILE循环被嵌套，则内部的BREAK退出的是次外层的循环。内部循环结束之后的所有语句在内部循环执行之后才能继续执行。

3、  GOTO语句
在存储过程的执行中，语句是顺序执行的。GOTO语句则是用来打破这种语句执行的顺序，它立即跳到某条语句上执行，而这条语句往往不紧跟在前一语句之后。GOTO语句与一个标志（Label）一起使用，该标志用来标识一条语句。
例如：
USE pubs
GO

DECLARE @num int
SELECT
IF @num = 0
       GOTO Err
ELSE
       BEGIN
       PRINT ‘authors found’
       SELECT * FROM authors
       GOTO Last
       END
Err: PRINT ‘no authors found’
Last: PRINT ‘Finish execution’
GO
4、  RETURN语句
RETURN语句用于无条件的退出存储过程。RETURN之后的任何语句都不再执行。RETURN语句可以给调用语句返回一个值，但不能返回NULL值。SQL Server经常为存储过程返回一个状态值。如果成功地执行，则返回一个0，如果出现了错误，则返回一个为负数的错误码。
存储过程返回的错误码
值
说        明
值
说        明

0
过程执行成功
-8
发生了非致命的内部问题

-1
漏掉了对象
-9
达到了系统极限

-2
发生了数据类型错误
-10
发生了致命的内部不一致错误

-3
该处理被选择成了死锁的牺牲者
-11
发生了致命的内部不一致错误

-4
发生了权限错误
-12
表或索引被破坏

-5
发生了语法错误
-13
数据库被破坏

-6
发生了混杂的用户错误
-14
发生了硬件错误

-7
资源错误，如空间不够等

5、  使用游标（CURSOR）在需要一行一行处理时，游标十分有用。游标可以打开一个结果集合（按照指定的标准选择的行），并提供在结果集中一行一行处理的功能。基于游标的类型，可以对其进行回滚或者前进。在使用游标时需要5个步骤：
                         i.              声明游标
在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。
形式1  （ANSI 92）
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
形式2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。
Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
READ ONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意：
· 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
· 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
· 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
                       ii.              打开游标
打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
                      iii.              从游标中取值
在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
INTO @cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。@@fetch_status可以用来构造游标处理的循环。
例如：
DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
                     iv.              关闭游标
CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
                       v.              释放游标
游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

       下面给出游标的一个完整的例子：
       USE master
       GO
       CREATE PROCEDURE sp_BuildIndexes
       AS
       DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)

       DECLARE table_cur CURSOR FOR
       SELECT name FROM sysobjects WHERE type=’u’

       OPEN table_cur
       FETCH NEXT FROM table_cur INTO @TableName

       WHILE @@fetch_status = 0
       BEGIN
              IF @@fetch_status = -2
                     CONTINUE
              SELECT @msg = “Building indexes for table”+@TableName+”…”
              PRINT @msg
              SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”')”
              EXEC (@cmd)
              PRINT “    “
              FETCH NEXT FROM table_cur INTO @TableName
       END
       DEALLOCATE table_cur
       GO
       下面的脚本将为PUBS数据库执行sp_BuildIndexes
       USE pubs
       GO
       EXEC ap_BuildIndexes
       注意：上面也是创建用户定义的系统存储过程的示例。

使用临时表
       临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。       临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。

存储过程中返回结果
 从存储过程中返回结果有三种方式：
1、 返回结果集
这是客户端应用程序返回结果的最通用的方法。结果集是通过使用SELECT语句选择数据产生的。结果集可以从永久表、临时表或局部变量中产生。将结果返回到另一个存储过程不是一种有效的方法。存储过程不能访问另一个存储过程建立的结果集。
例如从永久表中返回结果集：
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromPermtable
AS
SELECT au_iname FROM authors
GO
例如从局部变量中创建结果集：
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromVariable
AS
DECLARE @au_iname char(20)
SELECT @au_iname = au_iname FROM authors
WHERE au_id = ‘172-32-1176’
SELECT @au_id
GO
2、 设置OUTPUT参数的值
输出参数经常用来从存储过程中检索出结果。如果某个参数在传输到存储过程中时被定义成OUTPUT，则对该参数的任何修改在退出存储之后仍然有效。
例如：
USE pubs
GO
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT
AS
SELECT @count = count(*) FROM authors
GO
从输出参数中检索出值：
USE pubs
GO
CREATE PROCEDURE ap_GetOutputVar
AS
DECLARE @num integer
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT “the count is”+convert(char,@num)
GO
· 将游标使用成OUTPUT参数。游标可以使用OUTPUT（输出）参数，但不能使用成输入参数。也就是说，游标可以作为结果返回，但却不能传输到过程中去。当游标被用作参数时，需要限定其为OUTPUT和VARYING。VARYING关键字指出该结果集要用来支持输出参数。这样就提供了将结果集返回到调用过程的能力。
例如：
USE pubs
GO
CREATE PROCEDURE GetTitleCount @count_cursor CURSOR VARYING OUTPUT
AS
SET @count_cursor = CURSOR
FOR
SELECT au_id,count(*)
FROM titleauthors
GROUP BY au_id
OPEN @count_cursor
GO
3、 通过RETURN参数返回状态
这是一种从存储过程返回错误码的方法。存储过程总是返回一个状态值，用户也可以使用RETURN语句返回自己的状态。
例如：
USE pubs
GO
CREATE PROCEDURE ap_SetReturnStatus
AS
DECLARE @count integer
SELECT @count = count(*) FROM authors
IF @count = 0
RETURN(1)
ELSE
RETURN (0)
GO
例如检索出返回的状态：
USE pubs
GO
CREATE PROCEDURE ap_GetReturnStatus
AS
DECLARE @status integer
EXECUTE @status = ap_SetReturnStatus
IF @status = 1
PRINT “No rows found”
ELSE
PRINT “successful”
GO

在存储过程中进行错误处理
 如同其它程序一样，在存储过程中进行错误处理是非常重要的。系统变更@@error在执行每一个Transact SQL语句之后都会得到一个值。对于成功的执行，@@error的值为0，如果出现错误，则@@error中将包含错误信息。@@error系统变量对存储过程的错误处理是非常重要的。
 注意：为了防止错误，@@error所能设置的值在sysmessages表的“error”中反映了出来。
在存储过程中的错误有两种类型：
1、 数据库相关的错误
这些错误是由数据库的不一致性引起的，系统使用非0的@@error值表示特定的数据库问题。在Transact SQL执行之后，可以通过@@error获得所出现的错误。如果发现@@error不为0，则必须采取必要的行动，大多数情况下，存储将不再继续进行处理而返回。下面的示例展示了典型的获取数据库错误的方法。该过程将错误代码放置到输出变量中，这样，调用程序就能够访问到。
USE pubs
GO
CREATE PROCEDURE ap_TrapDatabaseError @return_code integer OUTPUT
AS
UPDATE authors SET au_iname = “Jackson”
WHERE au_iname = “Smith”
IF @@error <> 0
BEGIN
 SELECT @return_code = @@error
 RETURN
END
ELSE
 @return_code = 0
GO
2、 业务逻辑错误
这些错误是由于违反了业务规则而引起的。要获取这些错误，首先需要定义业务规则，基于这些规则，需要在存储过程中增加必要的错误检测代码。人们经常使用RAISERROR语句通报这些错误。RAISERROR提供了返回用户定义错误及将@@error变量设置成用户定义错误号的能力。错误消息可以被动态地建立，或者基于错误号从“sysmessages”表中检索到。一旦出现了错误，错误就会以一种服务器错误消息的方式返回到客户机。下面是RAISERROR命令的语法：
RAISERROR (msg_id | msg_str, severity, state
[, argument ][,…n]])
[WITH options]
Msg_id指明用户定义消息的id，该消息存储在“sysmessages”系统表中。
Msg_str用于动态创建消息的消息字符串。这与C语言中的“printf”非常相似。
Severity定义用户赋值的错误消息严重程度。
State是从1到127的任意整数值，它表示错误的调用状态信息。负数的state值将缺省为1。
OPTIONS指明错误的定制选项。OPTIONS的有效值如下：
1） LOG。
将错误记录到服务器错误日志和NT事件日志中。该选项需要消息带有从19到25的严重程度。而只有系统管理员才能发出这种消息。
2） NOWAIT。
将消息立即发送到客户端服务器。
3） SETERROR。
不管其严重级别如何，将@@error的值设置为msg_id或5000。

远程过程调用
 SQL Server提供了调用驻留在不同服务器上的存储过程的能力。调用这样的存储过程称谓远程存储过程调用。为了使得调用能从一个SQL Server转移到另一个服务器，两个服务器应该相互定义成对方的有效远程服务器。
 设置远程服务器的配置：
 · 扩展某个服务器的组。
 · 右击该服务器并点击“Properties”。
 · 设置选项“Allow other SQL Servers to connect remotely to this SQL server via RPC”。
 · 设置“Query time out”选项的值，该值指定从一个查询处理返回所能等待的秒数。缺省值为0，表示允许无限的等待时间。
 · 设置完成配置选项之后，点击“OK”。
 · 重新启动服务器之后，修改将会生效。
 · 在另一台远程服务器上重复相同的步骤。
 调用远程存储过程需要指明服务器的名称，后带数据库的名称和拥有者的名称。下面是在不同的服务器（Server2）上调用一个存储过程的示例。
Exec server2.pubs.dbo.myproc

豆豆的后话：
 这里只是粗浅的介绍了SQL Server常用的知识，对象也是基于SQL Server数据库编写应用程序的编程人员，而非数据库管理者。但对于应用程序编程者，了解数据库的管理也是非常有用的。建议在以后的时间自行去了解数据库的管理，这对于优化程序也是相当有用的。

重庆市读书香教育服务公司

首页:http://www.dushuxiang.com 网络实名：重庆家教公司

xietangz

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 7.0 入门

SQL Server 7.0 入门（一） --------------------------------------------------------------------------------2001-08-16 00:00:00 交互式SQL（Transact SQL，TSQL）它是SQL Server的查询语言。提供以下命令：· 创建和数据库对象。· 访问和修改数据。·
复制链接

扫一扫