存储过程 1 创建存储过程 create proc stu_proc1 as select count(*) from student
exec stu_proc1 执行
2 修改、删除、重命名 alter proc stu_proc1 as select * from student
drop proc stu_proc1
sp_rename stu_proc1 old
3 参数存储过程 传入参数: create proc stu_proc3 @pname char(3) as select * from student where number=@pname
exec stu_proc3 '103'
传出参数: create proc stu_proc5 @pname char(3), @result char(8) output as select @result=name from student where number=@pname
declare @a char(8) exec stu_proc5 '103',@a output print @a//select @a
亦可命名参数、明确指定 declare @a char(8) exec stu_proc5 @result=@a output,@pname='103' print @a
4 存储过程的复杂操作 使用带有复杂 SELECT 语句的简单过程 下面的存储过程从两个表的联接中返回所有学生(提供了姓名)、所学课程。 该存储过程不使用任何参数。
USE students IF EXISTS (SELECT name FROM sysobjects WHERE name = 'info_all' AND type = 'P') DROP PROCEDURE info_all GO
CREATE PROCEDURE info_all AS SELECT stu.name,grade.course FROM stu INNER JOIN grade ON stu.number = grade.number
GO
info_all 存储过程可以通过以下方法执行:
EXECUTE info_all -- Or EXEC info_all
下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO
练习1:制作存储过程显示所有年龄大于26的学生 create proc age1 as select name from student where year(getdate())-year(birthday)>26
exec age1
练习2:制作存储过程显示所有年龄大于指定大小的学生 create proc age2 @pdata int as select name from student where year(getdate())-year(birthday)>@pdata
exec age2 26
存储过程的注意事项:可使用with recompile 和with encryption 两者都可放在参数之后,语句之前 其中with recompile会导致每次调用存储过程都会编译,效率变低,但执行效率可能优化, 或加入到exec age with recompile中,则会根据需要创造新的查询计划
多行储存过程 create proc temp as select * from stu select * from grade