资海网络一年做多少网站,舞钢市住房和城乡建设局网站,临沂手机网站,2018年互联网大会交互式SQL#xff08;Transact SQL#xff0c;TSQL#xff09;它是SQL Server的查询语言。提供以下命令#xff1a; 创建和数据库对象。 访问和修改数据。 数据聚合#xff08;aggregation#xff0c;又称聚集#xff09;。 实施安全措施。数据库对象 数据库对象是…交互式SQLTransact SQLTSQL它是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 function5、 规则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位精度 N1~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字符串的固定长度n1~8000 Character (n) Varchar (n) 可变长度非unicode字符串n1~8000 Char varying(n) Text 服务器代码页中可变长度非unicode数据。最大长度为231-1个字符 Nchar 固定长度unicode字符串n1~4000 National character (n),National char(n) Nvarchar 固定长度unicode字符串n1~4000 National character varying(n) Ntext 可变长度unicode数据最大长度为230-1个字符 National text Binary (n) 固定长度二进制数据n在1~8000之间存储空间为n4字节 Varbinary (n) 可变长度二进制数据n1~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和HAVINGHAVING子句用来向使用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 JOININNER JOIN是组合两个表最常用的方法。INNER JOIN是基于一个判别式进行的这个判别式称为连接条件。连接条件和WHERE子句一起定义。连接条件由来自两个表中的列组成并使用一个比较条件来对列的值进行比较。通过比较的值包含在结果数据集中以下是Inner JOIN的语法语法1ANSI 92Select select_listFROM table1 INNER JOIN table2ON table1.column name table2.column name语法2Select select_listFROM table1,table2 WHERE table1.column name table2.column name在FROM 子句中可为表定义别名并在任何地方都可用别名代替真名。注意如果作为连接条件的列中有空值则空值不能和任何值匹配因此结果中不包含有空值的行。3、 Left Outer JOIN在Inner JOIN中只有在两个表中匹配的行才能在结果数据集中。但在Left Outer JOIN中所有左边表中的行都出现在结果数据集中如果左边表中的某一行在右边表中没有匹配的行则以空值取代右边表中的值和它连接。语法如下ANSI 92Select select_listFROM table1 LEFT OUTER JOIN table2ON table1.column name table2.column name4、 Right Outer JOINRight Out JOIN和Left Outer JOIN相似不同的是把右边的表作为外部表所有右边表中的行包含在结果数据集中。语法如下Select select_listFROM table1 RIGHT OUTER JOIN table2ON table1.column name table2.column name5、 Full Outer JOIN在Full Outer JOIN中所有两个表中的行都包含在结果数据集中。语法如下Select select_listFROM table1 FULL OUTER JOIN table2ON table1.column name table2.column nameCase语句 当对不同条件产生不同的结果值时可使用Case语句。 Case语句计算所有定义的条件并按条件是否为真而返回结果。语法如下CASE [input_expression]WHEN when_expression THEN result_expression[ELSE else_expression]ENDInput_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,StateCASE state WHEN ‘CA’ THEN ‘California’ WHEN ‘KS’ THEN ‘Kansas’ ENDFROM authorsUNION 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 RULE1、 局部变量局部变量保持存储过程中间值。当一个值在存储过程中需要多次或者某个查询的结果需要在随后的查询中使用时需要使用局部变量。在这些情形下值被存储在局部变量中并可用于将来的使用。本地变量的名称以“”符号开头。变量的名称可以包含字符和数值。局部变量在使用前需要进行类型声明。对局部变量进行赋值需要使用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)BREAKStatement(s)CONTINUE END BEGIN和END语句标志循环体。BREAK语句结束循环的执行即走到END语句之后。CONTINUE语句将控制处理过程回到循环的开始处即BEGIN语句的右边。 注意如果有两个或多个WHILE循环被嵌套则内部的BREAK退出的是次外层的循环。内部循环结束之后的所有语句在内部循环执行之后才能继续执行。3、 GOTO语句在存储过程的执行中语句是顺序执行的。GOTO语句则是用来打破这种语句执行的顺序它立即跳到某条语句上执行而这条语句往往不紧跟在前一语句之后。GOTO语句与一个标志Label一起使用该标志用来标识一条语句。例如USE pubsGODECLARE num intSELECTIF num 0 GOTO ErrELSE BEGIN PRINT ‘authors found’ SELECT * FROM authors GOTO Last ENDErr: PRINT ‘no authors found’Last: PRINT ‘Finish execution’GO4、 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 92DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSORFOR select_statement[FOR {READ ONLY | UPDATE ][OF column_list]}]形式2DECLARE 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_curFETCH FIRST FROM author_cur INTO iname, fnameWHILE fetch_status 0BEGINIF fname ‘Albert’PRINT “Found Albert Ringer”ELSEPrint “Other Ringer”FETCH NEXT FROM author_cur INTO iname, fnameEND 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 pubsGOCREATE PROCEDURE ap_CreateResultFromPermtableASSELECT au_iname FROM authorsGO例如从局部变量中创建结果集USE pubsGOCREATE PROCEDURE ap_CreateResultFromVariableASDECLARE au_iname char(20)SELECT au_iname au_iname FROM authorsWHERE au_id ‘172-32-1176’SELECT au_idGO2、 设置OUTPUT参数的值输出参数经常用来从存储过程中检索出结果。如果某个参数在传输到存储过程中时被定义成OUTPUT则对该参数的任何修改在退出存储之后仍然有效。例如USE pubsGOCREATE PROCEDURE ap_SetOutputVar count integer OUTPUTASSELECT count count(*) FROM authorsGO从输出参数中检索出值USE pubsGOCREATE PROCEDURE ap_GetOutputVarASDECLARE num integerEXECUTE ap_SetOutputVar num OUTPUTPRINT “the count is”convert(char,num)GO· 将游标使用成OUTPUT参数。游标可以使用OUTPUT输出参数但不能使用成输入参数。也就是说游标可以作为结果返回但却不能传输到过程中去。当游标被用作参数时需要限定其为OUTPUT和VARYING。VARYING关键字指出该结果集要用来支持输出参数。这样就提供了将结果集返回到调用过程的能力。例如USE pubsGOCREATE PROCEDURE GetTitleCount count_cursor CURSOR VARYING OUTPUTASSET count_cursor CURSORFORSELECT au_id,count(*)FROM titleauthorsGROUP BY au_idOPEN count_cursorGO3、 通过RETURN参数返回状态这是一种从存储过程返回错误码的方法。存储过程总是返回一个状态值用户也可以使用RETURN语句返回自己的状态。例如USE pubsGOCREATE PROCEDURE ap_SetReturnStatusASDECLARE count integerSELECT count count(*) FROM authorsIF count 0RETURN(1)ELSERETURN (0)GO例如检索出返回的状态USE pubsGOCREATE PROCEDURE ap_GetReturnStatusASDECLARE status integerEXECUTE status ap_SetReturnStatusIF status 1PRINT “No rows found”ELSEPRINT “successful”GO在存储过程中进行错误处理 如同其它程序一样在存储过程中进行错误处理是非常重要的。系统变更error在执行每一个Transact SQL语句之后都会得到一个值。对于成功的执行error的值为0如果出现错误则error中将包含错误信息。error系统变量对存储过程的错误处理是非常重要的。 注意为了防止错误error所能设置的值在sysmessages表的“error”中反映了出来。在存储过程中的错误有两种类型1、 数据库相关的错误这些错误是由数据库的不一致性引起的系统使用非0的error值表示特定的数据库问题。在Transact SQL执行之后可以通过error获得所出现的错误。如果发现error不为0则必须采取必要的行动大多数情况下存储将不再继续进行处理而返回。下面的示例展示了典型的获取数据库错误的方法。该过程将错误代码放置到输出变量中这样调用程序就能够访问到。USE pubsGOCREATE PROCEDURE ap_TrapDatabaseError return_code integer OUTPUTASUPDATE authors SET au_iname “Jackson”WHERE au_iname “Smith”IF error 0BEGIN SELECT return_code error RETURNENDELSE return_code 0GO2、 业务逻辑错误这些错误是由于违反了业务规则而引起的。要获取这些错误首先需要定义业务规则基于这些规则需要在存储过程中增加必要的错误检测代码。人们经常使用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数据库编写应用程序的编程人员而非数据库管理者。但对于应用程序编程者了解数据库的管理也是非常有用的。建议在以后的时间自行去了解数据库的管理这对于优化程序也是相当有用的。————————全文完————————— 转载于:https://www.cnblogs.com/cnLiou/archive/2005/08/13/213866.html
