使用PL/SQL编写存储过程访问数据库

一、实验目的

熟悉使用存储过程来进行数据库应用程序的设计。

二、实验内容

对学生-课程数据库，编写存储过程，完成下面功能：

 

1.统计离散数学的成绩分布情况，即按照各分数段统计人数；

2.统计任意一门课的平均成绩；

3.将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

要求：提交源程序并表示必要的注释。保证程序能正确编译和运行，认真填写实验报告。

 

三、实验步骤

实验之前，已经建立数据库，有student，course和sc三张基本表。

 

（一）统计离散数学的成绩分布情况，即按照各分数段统计人数。

 

1.建立表 Rank，其中第一列 division 显示成绩分段划分，第二列 number 显示的是成绩在该分数 段的学生人数。

 

CREATE TABLE Rank(

 division CHAR(20), 

  number      INT);

 

2.编写存储过程。

 

create procedure statistic_mark(@name char(50))

AS

DECLARE

@less60  INT,                 

@b60a70  INT,

@b70a80  INT,

@b80a90  INT,

@more90  INT,

@curcno VARCHAR(8);

begin

select @curcno = cno 

from Course

where cname =@name;

 

if(@curcno is null)

raiserror('课程号为空',16,1);

else

SELECT  @less60=count(*)    

FROM SC

WHERE cno =@curcno AND grade <60;

 

SELECT @b60a70=count(*)        

FROM SC

WHERE cno =@curcno AND grade >=60 AND grade<70;

 

SELECT @b70a80=count(*)     

FROM SC

WHERE cno =@curcno AND grade >=70 AND grade<80;

 

SELECT @b80a90=count(*)       

FROM SC

WHERE cno =@curcno AND grade >=80 AND grade<90;

 

SELECT @more90=count(*)

FROM SC

WHERE cno =@curcno AND grade >=90 ;

INSERT  INTO  RANK VALUES('[0,60)',@less60);   

INSERT  INTO  RANK VALUES('[60,70)',@b60a70);

INSERT  INTO  RANK VALUES('[70,80)',@b70a80);

INSERT  INTO  RANK VALUES('[80,90)',@b80a90);

INSERT  INTO  RANK VALUES('[90,100)',@more90);

END;

 

3.执行存储过程

编写好存储过程statistic_mark之后，在“查询分析器”中选择菜单中的“单事务执行”命令，这样系统就创建好了存储过程

然后使用PERFORM调用该过程，在表rank中查看执行的结果。

exec statistic_mark '离散'

select *

from rank

 

 (二)统计任意一门课程的平均成绩

1.创建存储过程

（1）创建需要的表结构。

根据实验要求，要统计任意一门课程的平均成绩，因此需要建立表avggrade，其中第一列cname 显示被统计的课程名称，第二列avg显示选修了该课程的学生的平均成绩。

create table avggrade（

cname char(50),

avg numeric(10,6)）；

 

（1）编写存储过程

create or replace procedure collect_avg（）

as

declare                //声明变量

curname   char(50);

curno     char(4);

curavgg   char(10,6);

cursor mycursor for               //声明游标mycursor查询课程号和课程名称

select cno,cname from course;

begin

   open mycursor;                   //打开游标   

   IF  mycursor%ISOPEN THEN       //条件控制，游标打开时进行以下处理

            LOOP                  //循环控制

     FETCH  mycursor INTO curcno,curname;   //游标推进一行取结果送变量

     EXIT WHEN(mycursor%NOTFOUND);   //如果没有返回值，则退出循环

 SELECT AVG(grade)INTO curavgg FROM SC   //求该课程的平均值送变量

 WHERE cno = curcno;

        //向avggrade //表中插入记录，显示课程名称和平均成绩

 INSERE INTO avggrade VALUES(curname,curavgg);

END LOOP;              //结束循环控制

END IF;                 //结束条件控制

CLOSE mycursor;

END;

 

2.执行存储过程

首先执行编写好的存储过程collect_avg,然后在表avggrade中查看执行结果。

PERFORM PROCEDURE collect_avg();

SELECT * FROM avggrade;

(三)在表SC中将学生选课成绩从百分制改为等级制

1.创建存储过程

根据实验要求，本实验中存储过程的执行不需要在客户端返回结果，因此不需要建立相应的表结构来存放存储过程的执行结果。直接编写存储过程。

create or replace procedure change_critical()

AS

DECLARE

  chgrade CHAR(1);

  currecord record;

BEGIN

ALTER TABLE SC ADD COLUMN(newgrade CHAR(1));

FOR currecord IN SELECT*FROM SC LOOP

IF currecord.grade<60 then

  chgrade ='E';

ELSIF  currecord.grade<70 then

  chgrade ='D';

ELSIF  currecord.grade<80 then

  chgrade ='C';

ELSIF  currecord.grade<90 then

  chgrade ='B';

ELSE

  chgrade ='A';

END IF;

UPDATE SC SET newgrade =chgrade

WHERE sno =currecord.sno AND cno=currecord.cno;

END LOOP;

 

ALTER TABLE SC DROP COLUMN grade;

ALTER TABLE SC RENAME newgrade TO grade;

END;

2执行存储过程

PERFORM PROCEDURE change_critical();

 

(四)删除存储过程

存储过程一旦建立，则将被保存在数据库中，便于用户随时，反复地调用和执行。如果不再需要该存储过程，可以将其删除。

删除存储过程statistic_mark。

 DROP PROCEDURE statistic_mark;

(1)删除存储过程collect_avg,

DROP PROCEDURE collect_avg,

(2)删除存储过程 change_critical;

DROP PROCEDURE change_critical;

四、实验总结

推荐阅读博客：http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html

http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

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

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

存储过程的优点

1.存储过程允许标准组件式编程；

2.存储过程能够实现较快的执行速度；

3.存储过程减轻网络流量；

4.存储过程可被作为一种安全机制来充分利用。

常用系统存储过程有：

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

创建存储过程的参数：

1.procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。

2. number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。

3.@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。

使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。

4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。

说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。

5.VARYING： 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

6.default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT ：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

11.AS :指定过程要执行的操作。

12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。