月亮代表我的心

个人博客,跟月亮无关,跟代表无关,无关谁的心

Sql Server 复习笔记(5)

存储过程

一组使用一个名称存储的预编译T-SQL语句
在存储过程中可使用2100个参数
局部变量数目受内存限制
存储过程最大可达128M
可创建和引用临时表,在存储过程创建的临时表随存储过程退出而消失
可在存储过程中创建其它数据库对象
不能使用下列语句:
create DEFAULT
create TRIGGER
create PROCEDURE
create VIEW
create RULE

创建存储过程
create PROCEDURE procedure_name
@Parameter_name data_type
AS
sql_statement [ …n ]

执行存储过程
EXECUTE procedure_name 参数

修改存储过程
alter PROCEDURE 过程名 AS …
这种修改操作不会影响用户权限

删除存储过程
drop PROCEDURE 过程名

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
/*********--带参数过程--***************/
--筹建/修改过程
ALTER PROC P_TEST @stdname VARCHAR(10),@age INT=0
AS
SELECT * FROM student WHERE stud_name LIKE @stdname+'%'
AND age>@age
GO
--执行带参数的过程
EXEC p_test 'w'

--参数有默认值
CREATE PROC P_table @tblname VARCHAR(10)='student'
AS
EXECUTE('select * FROM '+ @tblname)
GO

EXEC p_table 'score'

/*************--带输出参数过程--***************/
ALTER PROC P_table @tblname VARCHAR(10)='student',
@age INT=0,
@ROWS INT output
AS
EXECUTE('select * FROM '+ @tblname)
SET @ROWS=@@rowcount
GO

--执行带output参数的过程
--声明变量
DECLARE @rrr INT
--执行
EXEC P_table @tblname='score',@ROWS=@rrr output
SELECT @rrr

DECLARE @rrr INT
--格式:exec 过程名 @形参=@实参,...
EXEC P_table @ROWS=@rrr output,@tblname='score'
SELECT @rrr

--return有返回值的过程
CREATE PROC P_TEST @stdname VARCHAR(10)='',
@ROWS INT output
AS
SELECT * FROM student WHERE stud_name LIKE @stdname+'%'
SET @ROWS=@@rowcount
IF @ROWS=0
RETURN (0)
ELSE
RETURN (1)
GO

--格式:exec @过程返回值变量=过程名 @形参=@实参,...
DECLARE @rrr INT,@RETURN VARCHAR(10)
EXEC @RETURN=p_test @ROWS=@rrr output,@stdname='李aa'
SELECT @rrr,@RETURN

--重新编译
CREATE proc p_test1 WITH recompile
AS
SELECT * FROM test

EXEC p_test1 WITH recompile

sp_recompile p_test1

--修改过程
ALTER proc p_test1 ...AS ...

--删除过程
DROP proc t_test1

--错误
ALTER PROC P_TEST @stdname VARCHAR(10)='',
@ROWS INT output
AS
SELECT * FROM student WHERE stud_name LIKE @stdname+'%'
SET @ROWS=@@rowcount
IF @ROWS=0
raiserror('没有数据!',16,1)
ELSE
RETURN (1)
GO

DECLARE @rrr INT,@RETURN VARCHAR(10)
EXEC @RETURN=p_test @ROWS=@rrr output,@stdname='李a'
SELECT @rrr,@RETURN

重新编译存储过程

当存储过程使用的表被修改后,会进行优化,但是当向表添加新索引后,只有重新启动SQL SERVER 时才会执行优化过程,如不重启动,则须手动重新编译过程

1.使用系统存储过程sp_recompile
系统存储过程强制在下次执行存储过程的时候后对该存储过程进行重新编译

2.使用create PROCEDURE时指定WITH RECOMPILE
每次执行存储过程都会进行编译,这会减低效率

3.使用EXECUTE 时指定WITH RECOMPILE
只对过程进行一次重新编译,如果创建存储过程后对数据进行了大量修改,应使用此方法

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注