【HNU数据库实验】实验6：存储过程

实验6：存储过程

实验环境：SQL Server

（1）实验目的

掌握数据库PL/SQL编程语言，以及数据库存储过程的设计和使用方法。

（2）实验内容和要求

存储过程定义、存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。掌握PL/SQL编程语言和编程规范，规范设计存储过程。

（3）实验重点和难点

实验重点：存储过程定义和运行。

实验难点：存储过程的参数传递方法。

（4）实验过程

SQL是非过程化的查询语言，具有操作统一、面向集合、功能丰富、使用简单等优点，但和程序设计语言相比，高度非过程化造成了它缺少流程控制的能力，难以实现应用业务中的逻辑控制。SQL变成技术就是克服了这个缺点，提高了应用系统和数据库管理系统的互操作性。

存储过程是由过程化SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程,使用时只要调用即可。它具有以下优点：

(1）由于存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作，因而运行效率高，它提供了在服务器端快速执行SQL语句的有效途径。一次编译优化，之后直接调用

(2）存储过程降低了客户机和服务器之间的通信量。客户机上的应用程序只要通过网络向服务器发出调用存储过程的名字和参数，就可以让关系数据库管理系统执行其中的多条SQL语句并进行数据处理。只有最终的处理结果才返回客户端。

(3）方便实施企业规则。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由关系数据库管理系统管理，既有利于集中控制，又能够方便地进行维护。当企业规则发生变化时只要修改存储过程即可,无须修改其他应用程序。

1. 无参数的存储过程

在学生选课系统的数据库上创建一个名为stu_pr的存储过程，该存储过程能查询出所有学生的所有信息

SQL Server语法不需要REPLACE，且不带参数的存储过程不需要括号，否则会报错。

CREATE PROCEDURE stu_pr
AS
BEGIN
	SELECT * FROM Student;
END;

调用过程：语句格式 exec + 存储过程名

2. 有参数的存储过程

在学生选课系统的数据库上创建一个名为stu_proc1的存储过程，该存储过程能查询出指定学号的学生的所有个人信息。注：sql server的参数不需要写在括号中

create procedure stu_proc1 @Sno char(9)
as 
    begin 
        select * from Student where Sno=@Sno 
    end

查询学号为“201215121”和“201215123”的学生信息。

3. 有局部变量的存储过程

在学生选课系统的数据库上创建一个名为stu_proc2的存储过程，该存储过程能修改指定学生的课程2成绩为指定分数。

未更新前的成绩表，201215121和201215122选修了课程2，并且成绩分别为85和90。

create procedure stu_proc2 @Sname char(20),@Grade SMALLINT
as 
declare
@L_Sno char(9);
    begin 
        select @L_Sno=Sno --使用实验指导书上的into语句会报错
		from Student --先通过姓名查找学号
		where Sname=@Sname;
		
		update SC set Grade=@Grade--再根据学号和课程号更改成绩
		where Sno = @L_Sno and Cno = '2';
    end

执行代码：exec stu_proc2 '李勇',100;命令成功执行，查看执行结果（李勇学号为201215121）

4. 有输出参数的存储过程

在学生选课系统的数据库上创建一个名为stu_proc3的存储过程，该存储过程能根据指定学生的学号和SC表中的成绩信息，输出该学生的选课记录和成绩信息，并输出该学生共选修了几门课程。

create proc stu_proc3 @Sno char(9),
@recordCount int output --关键字代表输出参数
as
begin
    select * from SC where Sno=@Sno
    --把查询的记录条数赋值给变量@recordCount
    set @recordCount = (select count(*) from SC where Sno=@Sno)
end 

调用带有参数的存储过程，需要定义变量，并把变量传递给参数

declare @num int
exec stu_proc3 @Sno='201215121',@recordCount=@num output
select @num as 选课记录

5. 修改存储过程

修改存储过程名“stu_pr”为"stu_proc"

exec sp_rename 'stu_pr','stu_proc';

编译存储过程stu_proc

exec sp_recompile N'stu_proc';--N和名字之间没有空格

6. 删除存储过程

drop procedure stu_proc;

实验总结

本次实验量不大，过程也相对简单，当然主要原因是我使用的数据库和例子都很简单，重点只体现了各种存储过程的不同点，内部的执行语句不存在任何的难度，但因为sql server使用的是T-SQL语言，所以在一部分的声明上有一些出入，不过通过查询SQL-Server官方文档都得到了解决。此次实验让我更深刻了理解了什么是存储过程，更直观的体会到了存储过程的三大优势。