利用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.创存储过程

(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
:存储过程的名目,在前边加#为片临时存储过程,加##也全局临时存储过程。

  1. 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
语句。但发生有范围。

相关文章