一、存储过程的创建

定义:

存储过程是为了完成特定功能的SQL语句集合，存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

优点：
1、方便修改。
  因为存储过程是存储在数据库中的，如果需要涉及到修改SQL语句，那么数据库专业人员只需要去修改数据库中的存储过程就可以，对程序毫无影响，如果用SQL语句的话，SQL语句是写在程序中的，如果涉及到修改SQL语句，那么就需要去修改源程序。

2、存储过程比SQL语句执行更快速:

  存储过程是为了完成特定功能的SQL语句的集合，如果为了完成某一功能，使用了大量的SQL语句，那么执行存储过程只执行一次就可以，而SQL语句呢，则是需要执行多个。就类似于c语言中的自定义函数,甚至比自定义函数还要灵活很多.

1、创建简单存储过程

(1)创建一个名为stu_pr的存储过程，该存储过程能查询出o51班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息)。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除! p信息，否则就给出“不存在，可创建! ”的信息。

先进行判断是否存在:

if exists (select * from sysobjects where name='stu_pr' and type='P')begin
drop procedure stu_pr print '已删除! '
end
else
print '不存在，可创建! '

执行结果:

  

创建语句:

create procedure stu_pr
as
select distinct * from student s
left join sc on s.sno=sc.sno
left join course c on c.Cno=sc.Cno where classno='051'

使用刚刚创建的存储过程:

exec stu_pr

执行结果:

  

2、创建带参数的存储过程

(1)创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与"林%”。执行该存储过程，用多种参数加以测试。

if exists (select * from sysobjects where name='stu_proc1' and type='P')
begin
drop procedure stu_proc1 print '已册删除!'
end
else
print '不存在，可创建! '

执行结果:

  
创建语句:

create procedure stu_proc1
@sdept varchar(10)='%', @sname varchar(10)='林%'
as
select Sname , s.Sno, YEAR(getdate ( ) ) -YEAR(Birth) Age, Cname ,Grade from student s, Course c,sc
where s.Sno=sc.sno and c.Cno=sc.Cno
and s.Sname like @sname and s.sdept like @sdept

执行结果:

  
执行已经创建好的存储过程

执行1:

exec stu_proc1

  
执行2:

exec stu_proc1 @sdept='%', @sname ='林%'

  
执行结果:


(2）创建一个名为Student_sc的存储过程，可查询出某段学号的同学的学号、姓名、总成绩。(学号起始号与终止号在调用时输入，可设默认值)。执行该存储过程。

if exists (select name from sysobjects where name='student_sc' and type='P')
    drop procedure student_sc
go
create procedure student_sc
    @sno_begin varchar (10)='20110001 ',@sno_end varchar (10)='20110103'
as
    select s.Sno,Sname , SUM (grade) total_grade from student s,sc
    where s.sno=sc.sno and s.sno between @sno_begin and @sno_end
    group by s.sno ,sname

执行:

exec student_sc

3、创建带输出参数的存储过程
(1）创建一个名为Course_sum的存储过程，可查询某门课程考试的总成绩。总成绩可以输出，以便进一步调用。

if exists (select name from sysobjects where name='Course_sum' and type='P ')
drop procedure course_sum

  

创建:

create procedure course_sum @cname varchar(10)='告'
as
select SUM(grade) total_grade , COUNT(sno)sno from course c, sc
where c.Cno=sc.Cno and Cname like cname
执行:
exec course_sum '高数'

  

(2）创建一执行该存储过程的批处理，要求当总成绩小于100时，显示信息为:“XXX课程的总.成绩为:XX,其总分未达100分”。超过100时，显示信息为。“XX课程的总成绩为:XX”。

create procedure sum_grade2
@cname varchar (10) , @@sum smallint output
as
select @@sum=sum ( grade)from course c , sc
where c.Cno=sc.Cno and Cname like cname

创建:

declare @@sumgrade smallint
exec sum_grade2 '高数',@@sumgrade output if @@sumgrade<100
begin
print '高数的总成绩为:'+CAST(@@sumgrade as varchar(20))+'，其总分未达到分。'
end
else
print '高数的总成绩为:'+CAST(@@sumgrade As varchar (20))+'。'

  

创建:

create procedure update_sc
@cno varchar(10),@sno varchar (10) , @grade int with recompile,encryption--重编译，加密
as
update sc
set grade=@grade
where sc.cno=@cno and sc.sno=@sno
exec update_sc '001','20110001','80'

二 、使用T一SQL语句管理和维护存储过程

2.1 使用sp_helptext查看存储过程student_sc的定义脚本

语句:

sp_helptext student_sc

  

2.2 使用select语句查看student_sc存储过程的定义脚本（提示:通过查询表sysobjects和表

语句:

syscomments)
select o.id, c.text
from sysobjects o inner join syscomments c
on o.id = c.id
where o.type = 'p' and o.name = 'student_sc'

  

2.3 修改存储过程

将存储过程stu_pr改为查询学号为2011001的学生的详细资料。

alter procedure stu_pr
@sno varchar (10)
as
select distinct * from student
where sno='20110001'

执行:

exec stu_pr @sno=’2011001’

2.4 删除存储过程

drop procedure stu_pr

6、使用sQL Server Management Studi管理存储过程
(1）在SQL Server Management Studio中重新创建刚删除的存储过程stu_pr
选择数据库student_info→可编程性→存储过程，右击“存储过程”→新建存储过程

  

  

  

(2）查看存储过程`stu_pr`，并将该过程修改为查询051班女生的所有资料。可编程性→存储过程→>stu _pr，右击stu _pr→>修改.

  

(3） 修改sQL语句，使之能查询051班所有女生的资料的存储过程

  
(3）删除存储过程stu_pr
选择存储过程stu _pr，右击,选择“删除”

  

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

《数据治理行业实践白皮书》下载地址：https://fs80.cn/4w2atu

《数栈V6.0产品白皮书》下载地址：https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs

同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack