SQL server存储过程，触发器，游标相关实例
时间：2021-07-01 10:21:17
帮助过：4人阅读
use MySchool
2 go
3
4 alter proc P_stu
5 @pass int= 60
6 as
7 select AVG(StudentResult)
as 平均分
8 from Result
9
10 select * from Result
11 where StudentResult
< @pass
12 go
13
14
15 exec P_stu
16
17 go
18
19 ---创建带输出参数的存储过程
20
21 select * from Result
22 go
23
24 alter procedure p_res
25 @newId varchar(
20) output
26 as
27 declare @max varchar(
20)
28
29 select @max=MAX(StudentNo)
from Result
30 where YEAR(ExamDate)
= YEAR(
GETDATE())
31 and month(ExamDate)
= month(
GETDATE())
32 and day(ExamDate)
= day(
GETDATE())
33
34 if (
@max is null)
35 set @newId = CONVERT(
varchar(
100),
GETDATE(),
23)
+‘001‘
36 else
37 begin
38 declare @right int
39 set @right = CONVERT(
int,
right(
@max,
3))
40 set @right = @right +1
41 set @newId = CONVERT(
varchar(
100),
GETDATE(),
23)
+‘-‘+(
case when @right < 10 then ‘00‘ when @right<100 then ‘0‘ end)
+ CONVERT(
varchar(
10),
@right)
42
43 end
44 go
45
46 declare @rst varchar(
20)
47 exec p_res
@rst output
48 print @rst
49
50
51 --动态存储过程的数据处理
52
53
54 --分页存储过程
55 --要求
56 --可输入每页显示条数，默认每页条
57 --可输入页码进行查询
58 --可输入条件查询
59 --可输入表名
60 --可输入列名
61 --可输入排序列
62 --返回总记录数
63 --返回总页数
64 --返回查询到的结果
65 select * from Subject
66 go
67
68 if exists(
select * from sysobjects
69 where name
= ‘p_Mypage‘)
70 drop procedure p_Mypage
71 go
72
73 create proc p_Mypage
74 @tableName varchar(
20),
75 @Fields varchar(
200),
76 @orderFields varchar(
200),
77 @where varchar(
200),
78 @pageSize int = 5,
79 @pageIndex int = 1,
80 @RecordCount int output,
81 @PageCount int output
82
83 with encryption
--文本加密
84 --with recomple--每次都重新编译
85 as
86
87 declare @sql nvarchar(
4000)
88
89 set @sql = ‘select @RecordCount=count(*) from‘+ @tableName+‘where 1= 1‘+ISNULL(
@where,
‘ ‘)
90
91 exec sp_executesql
@sql,N
‘@RecordCount int output‘,
@RecordCount output
92 set @PageCount = ceiling((
@RecordCount+0.0)
/@pageSize)、
93
94 set @sql = ‘
95 select * from(
96 select top ‘+ CONVERT(
varchar(
10),
@pageSize)
+‘ ‘+@Fields+‘ from(
97 select top ‘+ CONVERT(
varchar(
10),
@pageSize*@pageIndex)
+‘ * from ‘+@tableName+‘
98 where 1= 1‘+ISNULL(
@where,
‘ ‘)
+‘
99 order by ‘+@orderFields+‘ )as a
100 order by a.‘+@orderFields+‘ desc) as b
101 order by b.SubjectNo‘
102
103 exec (
@sql)
104
105
106 declare @a int ,
@b int
107 exec p_Mypage
@tableName= ‘Subject‘,
@Fields = ‘*‘,
108 @orderFields = ‘SubjectNo‘,
@where=null,
109 @pageSize = 5,
@pageIndex = 2,
@RecordCount = @a output,
110 @PageCount = @b output
111 print @a
112 print @b
113
114
115 --游标
116
117
118
119
120 declare sur_stu
cursor scroll
for
121 select StudentName
from Student
for read only
122 open sur_stu
123
124 declare @name varchar(
20)
125 declare @i int
126 set @i = 1;
127 fetch next from cur_stu
into @name
128 while(
@@FETCH_STATUS<> -1)
129 begin
130 if(
@i = 3)
131 begin
132 print @name
133 set @i=0
134 end
135 fetch next from cur_stu
into @name
136 set @i=@i +1
137
138 end
139
140 close sur_stu
141 deallocate sur_stu
142
143
144 --使用游标和查存储过程创建分页 性能最差
145 create procedure sqlPage
146 @sql nvarchar (
4000),
147 @pageIndex int,
148 @pageSize int,
149 @recordCount int output,
150 @pageCount int output
151 as
152 declare @p1 int
153 exec sp_cursoropen
@p1 output,
@sql,
@scrollopt= 1,
@ccopt = 1,
@rowcount= @recordCount output
154 set @pageCount = CEILING((
@recordCount+0.0)
/@pageSize)
155 exec sp_cursorfetch
@p1,
16,
@pageIndex,
@pageSize
156 exec sp_cursorclose
@p1
157
158 declare @a int ,
@b int
159 exec sqlPage
‘select * from student‘,
1,
5,
@a output,
@b output
160 print @a
161 print @b
SQL server存储过程，触发器，游标相关实例
标签：case mys bsp read lin 表名 roc 文本 加密
