1 --10、 分页存储过程
2 ---存储过程、row_number完成分页
3 if (object_id('pro_page', 'P') is not null)
4 drop proc pro_page
5 go
6 create proc pro_page
7 @startIndex int,
8 @endIndex int
9 as
10 select count(*) from t1;
11 select * from (
12 select row_number() over(order by id1) as rowId, * from t1
13 ) temp
14 where temp.rowId between @startIndex and @endIndex
15 go
16 --drop proc pro_page
17 exec pro_page 1, 4
18 --
19 --分页存储过程
20 if (object_id('pro_page', 'P') is not null)
21 drop proc pro_page
22 go
23 create procedure pro_page(
24 @pageIndex int,
25 @pageSize int
26 )
27 as
28 declare @startRow int, @endRow int
29 set @startRow = (@pageIndex - 1) * @pageSize +1 --求出pageIndex前的行数求出
30 set @endRow = @startRow + @pageSize -1
31 select * from (
32 select *, row_number() over (order by id1 asc) as number from t1
33 ) t
34 where t.number between @startRow and @endRow;
35
36 exec pro_page 2, 7;
37
38
39 --Ø Raiserror
40 --Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。
41 --语法如下：
42 --Raiserror({msg_id | msg_str | @local_variable}
43 -- {, severity, state}
44 -- [,argument[,…n]]
45 -- [with option[,…n]]
46 --)
47
48 --# msg_id:在sysmessages系统表中指定的用户定义错误信息
49 --# msg_str:用户定义的信息，信息最大长度在2047个字符。
50 --# severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。
51 --任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。
52 --# state：介于1至127直接的任何整数。State默认值是1。
53 --raiserror('is error', 16, 1);
54 --select * from sys.messages;
55 --使用sysmessages中定义的消息
56 raiserror(33003, 16, 1);
57 raiserror(33006, 16, 1);
58 /*-----------------------------------------------------------------------------------------------------*/
59 --函数
60 --标量函数
61 use ReviewDatabase
62 go
63 if exists(select* from sysobjects where type='fn' and name='fun_t1')
64 drop function fun_t1
65 go
66 create function dbo.fun_t1(@id1 int)
67 returns int
68 as
69 begin
70 declare @id2 int
71 select @id2=id3 from t1 where id1=@id1
72 return @id2
73 end
74 go
75 select dbo.fun_t1(t2.id1+1)as id,t1.name,t1.tel from t1,t2 where t1.id2=t2.id2 and dbo.fun_t1(t2.id1+1)<>''
76 --内联表值函数
77 create function fun_t1_2(@id1 int)
78 returns table
79 as
80 return (select * from t1 where id1=@id1)
81 go
82 select * from dbo.fun_t1_2(1)
83 --多语句表值函数
84 create function fun_t1_3()
85 returns @t1 table(a int,b int)
86 as
87 begin
88 insert @t1
89 select id1,id2 from t1
90 return
91 end
92 go
93 select * from fun_t1_3()
94 /*-----------------------------------------------------------------------------------------------------*/
95 --游标
96 /*
97 DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 全局或局部
98 [ FORWARD_ONLY | SCROLL ]
99 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
100 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
101 [ TYPE_WARNING ]
102 FOR select_statement
103 [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
104 [;]
105
106 STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四选一
107 这四个关键字是游标所在数据集所反应的表内数据和游标读取出的数据的关系
108 STATIC意味着，当游标被建立时，将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容.
109 DYNAMIC是和STATIC完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变
110 KEYSET可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据
111 FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好.
112 READ_ONLY SCROLL_LOCKS OPTIMISTIC 三选一
113 READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作
114 SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功
115 OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新
116 */
117 declare rs insensitive cursor
118 for
119 select * from t1
120 open global rs --打开游标
121 IF @@ERROR=0
122 BEGIN
123 PRINT '游标打开成功。'
124 PRINT '学生总数为：'+ CONVERT(VARCHAR(3),@@CURSOR_ROWS)
125 END
126 close rs --关闭游标
127 deallocate rs --释放游标
128 GO
129
130 --定义后直接赋值
131 declare t1_cursor cursor --如果不指定游标作用域，默认作用域为GLOBAL
132 for select * from t1
133 --先定义后赋值
134 declare @t1_cursor1 cursor
135 set @t1_cursor1=cursor for select * from t1
136
137 --LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文
138 declare t1_cursor3 cursor global--全局
139 for select * from t1
140
141 declare t1_cursor4 cursor local--局部
142 for select * from t1
143
144 --go结束上面的作用域
145 go
146 open t1_cursor3
147 close t1_cursor3
148 open t1_cursor4--错误,在处理结束后被隐匿释放,
149 --实例
150 --游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作
151 --只有支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:
152
153
154 declare t1_curosor5 cursor global scroll--必须指定scrool否则只支持next只进选项
155 for select tel from t1
156
157 open t1_curosor5--打开游标
158 declare @tel int
159 --取下一行
160 fetch next from t1_curosor5 into @tel
161 print @tel
162 --取最后一行
163 fetch last from t1_curosor5 into @tel
164 print @tel
165 --取第一行
166 fetch first from t1_curosor5 into @tel
167 print @tel
168 --取上一行
169 fetch prior from t1_curosor5 into @tel
170 print @tel
171 --取第三行
172 fetch absolute 3 from t1_curosor5 into @tel
173 print @tel
174 --取相对目前来说上一行
175 fetch relative -1 from t1_curosor5 into @tel
176 print @tel
177 close t1_curosor5
178 deallocate t1_curosor5
179
180 -- 对于未指定SCROLL选项的游标来说，只支持NEXT取值.
181 declare test_cursor6 cursor global forward_only--可省
182 for select id1,tel,name from t1
183 open test_cursor6
184 declare @id1 int
185 declare @te int
186 declare @name varchar(10)
187 fetch next from test_cursor6 into @id1,@te,@name
188 print @id1+@te
189 close test_cursor6
190 deallocate test_cursor6
191
192 --游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的
193 declare t1_cursor8 cursor scroll
194 for select id2,tel from t1
195 open t1_cursor8
196 declare @id2 int
197 declare @te2 int
198 fetch next from t1_cousor into @id2,@te2
199 while @@fetch_status=0
200 begin
201 print @id2
202 print @te2
203 fetch next from t1_cousor into @id2,@te2
204 end
205 close t1_cursor8
206 deallocate t1_cursor8
2 ---存储过程、row_number完成分页
3 if (object_id('pro_page', 'P') is not null)
4 drop proc pro_page
5 go
6 create proc pro_page
7 @startIndex int,
8 @endIndex int
9 as
10 select count(*) from t1;
11 select * from (
12 select row_number() over(order by id1) as rowId, * from t1
13 ) temp
14 where temp.rowId between @startIndex and @endIndex
15 go
16 --drop proc pro_page
17 exec pro_page 1, 4
18 --
19 --分页存储过程
20 if (object_id('pro_page', 'P') is not null)
21 drop proc pro_page
22 go
23 create procedure pro_page(
24 @pageIndex int,
25 @pageSize int
26 )
27 as
28 declare @startRow int, @endRow int
29 set @startRow = (@pageIndex - 1) * @pageSize +1 --求出pageIndex前的行数求出
30 set @endRow = @startRow + @pageSize -1
31 select * from (
32 select *, row_number() over (order by id1 asc) as number from t1
33 ) t
34 where t.number between @startRow and @endRow;
35
36 exec pro_page 2, 7;
37
38
39 --Ø Raiserror
40 --Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。
41 --语法如下：
42 --Raiserror({msg_id | msg_str | @local_variable}
43 -- {, severity, state}
44 -- [,argument[,…n]]
45 -- [with option[,…n]]
46 --)
47
48 --# msg_id:在sysmessages系统表中指定的用户定义错误信息
49 --# msg_str:用户定义的信息，信息最大长度在2047个字符。
50 --# severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。
51 --任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。
52 --# state：介于1至127直接的任何整数。State默认值是1。
53 --raiserror('is error', 16, 1);
54 --select * from sys.messages;
55 --使用sysmessages中定义的消息
56 raiserror(33003, 16, 1);
57 raiserror(33006, 16, 1);
58 /*-----------------------------------------------------------------------------------------------------*/
59 --函数
60 --标量函数
61 use ReviewDatabase
62 go
63 if exists(select* from sysobjects where type='fn' and name='fun_t1')
64 drop function fun_t1
65 go
66 create function dbo.fun_t1(@id1 int)
67 returns int
68 as
69 begin
70 declare @id2 int
71 select @id2=id3 from t1 where id1=@id1
72 return @id2
73 end
74 go
75 select dbo.fun_t1(t2.id1+1)as id,t1.name,t1.tel from t1,t2 where t1.id2=t2.id2 and dbo.fun_t1(t2.id1+1)<>''
76 --内联表值函数
77 create function fun_t1_2(@id1 int)
78 returns table
79 as
80 return (select * from t1 where id1=@id1)
81 go
82 select * from dbo.fun_t1_2(1)
83 --多语句表值函数
84 create function fun_t1_3()
85 returns @t1 table(a int,b int)
86 as
87 begin
88 insert @t1
89 select id1,id2 from t1
90 return
91 end
92 go
93 select * from fun_t1_3()
94 /*-----------------------------------------------------------------------------------------------------*/
95 --游标
96 /*
97 DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 全局或局部
98 [ FORWARD_ONLY | SCROLL ]
99 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
100 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
101 [ TYPE_WARNING ]
102 FOR select_statement
103 [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
104 [;]
105
106 STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四选一
107 这四个关键字是游标所在数据集所反应的表内数据和游标读取出的数据的关系
108 STATIC意味着，当游标被建立时，将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容.
109 DYNAMIC是和STATIC完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变
110 KEYSET可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据
111 FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好.
112 READ_ONLY SCROLL_LOCKS OPTIMISTIC 三选一
113 READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作
114 SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功
115 OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新
116 */
117 declare rs insensitive cursor
118 for
119 select * from t1
120 open global rs --打开游标
121 IF @@ERROR=0
122 BEGIN
123 PRINT '游标打开成功。'
124 PRINT '学生总数为：'+ CONVERT(VARCHAR(3),@@CURSOR_ROWS)
125 END
126 close rs --关闭游标
127 deallocate rs --释放游标
128 GO
129
130 --定义后直接赋值
131 declare t1_cursor cursor --如果不指定游标作用域，默认作用域为GLOBAL
132 for select * from t1
133 --先定义后赋值
134 declare @t1_cursor1 cursor
135 set @t1_cursor1=cursor for select * from t1
136
137 --LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文
138 declare t1_cursor3 cursor global--全局
139 for select * from t1
140
141 declare t1_cursor4 cursor local--局部
142 for select * from t1
143
144 --go结束上面的作用域
145 go
146 open t1_cursor3
147 close t1_cursor3
148 open t1_cursor4--错误,在处理结束后被隐匿释放,
149 --实例
150 --游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作
151 --只有支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),例如:
152
153
154 declare t1_curosor5 cursor global scroll--必须指定scrool否则只支持next只进选项
155 for select tel from t1
156
157 open t1_curosor5--打开游标
158 declare @tel int
159 --取下一行
160 fetch next from t1_curosor5 into @tel
161 print @tel
162 --取最后一行
163 fetch last from t1_curosor5 into @tel
164 print @tel
165 --取第一行
166 fetch first from t1_curosor5 into @tel
167 print @tel
168 --取上一行
169 fetch prior from t1_curosor5 into @tel
170 print @tel
171 --取第三行
172 fetch absolute 3 from t1_curosor5 into @tel
173 print @tel
174 --取相对目前来说上一行
175 fetch relative -1 from t1_curosor5 into @tel
176 print @tel
177 close t1_curosor5
178 deallocate t1_curosor5
179
180 -- 对于未指定SCROLL选项的游标来说，只支持NEXT取值.
181 declare test_cursor6 cursor global forward_only--可省
182 for select id1,tel,name from t1
183 open test_cursor6
184 declare @id1 int
185 declare @te int
186 declare @name varchar(10)
187 fetch next from test_cursor6 into @id1,@te,@name
188 print @id1+@te
189 close test_cursor6
190 deallocate test_cursor6
191
192 --游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的
193 declare t1_cursor8 cursor scroll
194 for select id2,tel from t1
195 open t1_cursor8
196 declare @id2 int
197 declare @te2 int
198 fetch next from t1_cousor into @id2,@te2
199 while @@fetch_status=0
200 begin
201 print @id2
202 print @te2
203 fetch next from t1_cousor into @id2,@te2
204 end
205 close t1_cursor8
206 deallocate t1_cursor8