| 失效链接处理 |
|
存储过程、触发器的例子 PDF 下载
本站整理下载：
提取码：yrn8
相关截图：
主要内容：
存储过程：
1.CREATE PROCEDURE loving
AS
BEGIN
SELECT * from student where ssex='女'
END：
执行
Exec loving
2. CREATE PROCEDURE loving1
as
BEGIN
SELECT * from student
end
3. CREATE PROCEDURE pr1_sc_ins
@Param1 char(10),@Param2 char(2),@Param3 real
AS
BEGIN
insert into sc(sno,cno,grade) values(@Param1,@Param2,@Param3)
END
执行:
EXEC pr1_sc_ins '200215121','4',85或
EXEC pr1_sc_ins @Param1='200215121',@Param2='6', @Param3=85
4. CREATE PROCEDURE s_grade
@sname char(8),@sgrade real output
AS
SELECT @sgrade =grade from sc join student on student.sno=sc.sno
where sname=@sname
执行：
DECLARE @sgrade real
EXEC s_grade
@sname = '李勇',
@sgrade = @sgrade OUTPUT
5. CREATE PROCEDURE s_grade1
@ssno char(9),@sgrade real output
AS
SELECT @sgrade =avg(grade) from sc
where sno=@ssno
执行：
DECLARE @sgrade real
EXEC s_grade1
@ssno = '200215121',
@sgrade = @sgrade OUTPUT
SELECT @sgrade
6. CREATE PROCEDURE s_grade
@youbiao cursor varying output
AS
set @youbiao =cursor
for
SELECT sno,avg(grade) from sc
group by sno
open @youbiao
go
declare @youbiao cursor,@ssno char(9), @sgrade real
exec s_grade @youbiao output
Fetch Next From @youbiao InTo @ssno ,@sgrade
While(@@Fetch_Status = 0)
begin
select @ssno ,@sgrade
Fetch Next From @youbiao InTo @ssno ,@sgrade
end
Close @youbiao
Deallocate @youbiao
7.if exists(select * from sysobjects where name='guocheng' and type='p') ---判定存储过程是否存在
drop proc guocheng
go
create proc guocheng
@id int ,
@name nvarchar(50)
as
set nocount on
set rowcount @id --设置要查询多少条数据
--select * from adminurl where urlname like @name --(1)这里的通配符是在传参数的时候带上的
select * from student where urlname like '%'+@name+'%' --(2)这里的通配符是程序自带的，推荐这个
go
8.加密存储过程和实现另外一种模糊查询（用到系统函数）-------------
if exists(select name from sysobjects where name='guocheng2' and type='p')
drop proc guocheng2 --判断是否存在
go
create proc guocheng2
@name nvarchar(100)
with encryption ---实现对存储过程加密，以后谁也看不到内容，所以事先要有备份
as
set nocount on
select * from student where charindex(@name,urlname)>0 --chaindex的作用相当于Like @name
go
drop proc guocheng2
exec guocheng2 '功能'
----------------存储过程的几种返回值（output,return,select）-----------------
--(1)output存储过程[注意在.NET中是怎样接受的]
if exists(select name from sysobjects where name='guocheng3' and type='p')
drop proc guocheng3 --判断是否存在
go
create proc guocheng3
@n int output, ---申明是输出参数
@name nvarchar(50)
with encryption ---加密
as
set nocount on --不显示记录数，提高网络
select * from student where urlname like '%'+@name+'%'
set @n=@@rowcount --赋值
go
--开始测试
declare @n int --定义输出参数
exec guocheng3 @n output ,'管理'
print @n --验证是否输出参数已经赋值
----（2）return存储过程,切记return返回的必须是整型值[注意在.NET中是怎样接受的]
if exists(select name from sysobjects where name='guocheng4' and type='p')
drop proc guocheng4
go
create proc guocheng4
@name nvarchar(50),
@n int
with encryption
as
set nocount on
set rowcount @n
select * from student where urlname like '%'+@name+'%'
if(@@rowcount>0)
return 1
else
return 0
go
----开始测试
declare @m int
exec @m=guocheng4 '管理',4
print @m
------(3):带返回游标的存储过程,并且游标只能是output类型
--【1.定义】
if exists(select * from sysobjects where name='guocheng5' and type='p')
drop proc guocheng5 ---判断存在否
go
create proc guocheng5
@youbiao cursor varying output ---定义一个游标输出参数，varying表示可以变化的
as
set @youbiao=cursor forward_only --forward_only表示从第一条开始往下
--[static]（这里可以添加）
for select comment from adminurl --static表示建立一个临时副本，不允许修改基表，如果没有就可以修改基表
open @youbiao --打开游标
go
--【2.使用】
if exists(select name from sysobjects where name='guocheng6' and type='p')
drop proc guocheng6
go
create proc guocheng6 --用来调用guocheng5
as
declare @n nvarchar(100) --定义一个变量用于接收游标的移动的每条记录
declare @youbiao2 cursor --定义一个游标作参数，用于上面那个存储过程
exec guocheng5 @youbiao=@youbiao2 output --赋值给定义个游标
fetch next from @youbiao2 into @n --每条记录赋值
while(@@fetch_status=0)
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
