实验目的：进一步了解关于存储过程和触发器的定义及实现

实验名称：存储过程、触发器的定义以及验证

实验内容：完成以下关于存储过程和触发器的定义以及验证。

1.[例7.50] 输入某个同学的学号，统计该同学的平均分。

CREATE PROCEDURE proStudentByNo1(sNo char(7))

BEGIN

        SELECT a.studentNo, studentName, avg(score)

        FROM Student a, Score b

        WHERE a.studentNo=b.studentNo

              AND a.studentNo=sNo

        GROUP BY a.studentNo, studentName;

END

执行该存储过程：call proStudentByNo1('0800001');

2.输入某个同学的学号，统计该同学的平均分，并返回该同学的姓名和平均分。

CREATE PROCEDURE proStudentByNo21(@sNo char(7), @sName

varchar(20) OUTPUT,  @avg numeric(5, 1) OUTPUT )

AS

BEGIN      

     SELECT @sName=studentName

     FROM Student   WHERE studentNo=@sNo

     SELECT @avg=avg(score)

     FROM Score   WHERE studentNo=@sNo

     GROUP BY studentNo

END

执行存储过程proStudentByNo2 1

DECLARE  @sNamevarchar(20), @avg numeric(5, 1)

EXECUTE proStudentByNo21  '0800001',  @sName OUTPUT,  @avg OUTPUT

SELECT  @sName, @avg

把以上SQLServer版本的存储过程改为MYSQL版本并运行

3. 创建触发器，当输入某个同学选课成绩时，如果他是少数民族人，其成绩自动加5分。

CREATE TRIGGER ScoreIns BEFORE INSERT

ON Score FOR EACH ROW

BEGIN

      UPDATE Student SET NEW.score=NEW.score+5

      WHERE Student.studentNo=NEW.studentNo

and Student.nation<>'汉族';

END

实验步骤：

首先连接名为scoredbd的数据库，然后新建查询，输入SQL语句，运行。

1. 输入学号为0800001，统计该同学的平均分。SQL语句及运行结果如下：



执行存储过程：



2、输入学号为0800001，统计该同学的平均分，并返回该同学的姓名和平均分。





3. 创建触发器，当输入某个同学选课成绩时，如果他是少数民族人，其成绩自动加5分。



（1）往Score表插入一条记录，要求studentNo值所对应学生在Student表的民族为’汉族’，查看Score表中刚才插入的记录的score值是否有变化。







（2）再往Score表插入一条记录，要求studentNo值所对应学生在Student表的民族为’蒙古族’，查看Score表中刚才插入的记录的score值是否有变化。







 

 

--1 创建基于一个emp表的视图emp_view，并完成以下操作。
create view emp_view
as
select * from scott.emp where sal>1000 
with check option;

--1_1 对创建的视图进行查询
select * from emp_view;
--1_1 向视图中添加一条工资小于1000的新员工信息，观察结果。
insert into emp_view(empno,sal) values(8888,999);
--结果是不能插入数据，ORA-0142:视图 WITH CHECK OPTION where 子句违规



--2 创建无参存储过程：实现将员工中工资最低的三个人筛选出来，并把其job字段改为“试用期”。调用存储过程。
create or replace procedure procedure_2
is
begin
  update emp e set e.job = '试用期' where e.empno in (
         select ee.empno from (select eee.* from emp eee order by sal) ee where rownum < 4);
   commit;
end;
--存储过程调用
begin
  procedure_2;
end;



--3 创建无参存储过程，实现统计各部门的部门编号，员工人数、最高工资、最低工资，并输出（使用游标）。调用存储过程。
create or replace procedure procedure_3
is
  cursor cursor_3 is select e1.deptno, count(*), max(e1.sal), min(e1.sal) from emp e1 group by e1.deptno;
  deptno_3 emp.deptno%type;
  counts_3 number(4);
  max_sal_3 number(8);
  min_sal_3 number(8);
begin
  open cursor_3;
  loop
    fetch cursor_3 into deptno_3,counts_3,max_sal_3,min_sal_3;
    exit when cursor_3%notfound;
    dbms_output.put_line('部门编号：'||deptno_3||' 部门人数：'||counts_3||' 部门最高工资：'||max_sal_3||' 部门最低工资：'||min_sal_3);
  end loop;
  close cursor_3;
end;
--存储过程调用
begin
  procedure_3;
end;



--4 创建含有输入参数存储过程：向dept表添加新记录，查询是否编号已存在。
create or replace procedure procedure_4(deptno_4 dept.deptno%type, dname_4 dept.dname%type, loc_4 dept.loc%type)
is
  deptno_tmp dept.deptno%type;
begin
  select deptno into deptno_tmp from dept where deptno = deptno_4;
  raise_application_error(-20001, '编号已存在！');
  exception
    when no_data_found then
      insert into dept(deptno, dname, loc) values(deptno_4, dname_4, loc_4);
      commit;
end;
--存储过程调用
begin
  procedure_4(40, 'STUDENT', 'NEW YORK');
end;



--5 创建存储过程，利用out参数实现存储过程的返回值：根据输入的员工编号，返回员工总工资。调用存储过程。
create or replace procedure procedure_5(empno_5 emp.empno%type, sal_5 out emp.sal%type)
is
begin
  select sal into sal_5 from emp where empno = empno_5;
  exception
    when no_data_found then
      raise_application_error(-20002, '查无此人！');
end;
--存储过程调用
declare
  sal_tmp emp.sal%type;
begin
  procedure_5(7902, sal_tmp);
  dbms_output.put_line('员工的工资为：'||sal_tmp);
  dbms_output.put_line('18102108李玉田');
end;

实验六  存储过程建立与调用
一、实验目的
理解存储过程的概念、建立和调用方法。
二、实验环境
三、实验示例
1、定义一个函数，按性别计算所有学生的平均年龄。
CREATE FUNCTION aver_age(@sex char(2)) RETURNS int
AS
BEGIN
DECLARE @aver int
SELECT @aver=
( SELECT avg(年龄)
FROM s
WHERE sex=@SEX
)
RETURN @aver
END
GO
如下程序对上例定义的aver_age函数进行调用。
USE student                   /*用户函数在此数据库中已定义*/
/*定义局部变量*/
DECLARE @sex char(2)
DECLARE @aver1 int
/*给局部变量赋值*/
SELECT @sex = ‘女’
SELECT @aver1=dbo.aver_age(@sex) /*调用用户函数，并将返回值赋给局部变量*/
/*显示局部变量的值*/
SELECT @aver1 AS '男性学生的平均年龄'
2、利用student数据库中的S、SC表，编写一无参存储过程用于查询每个学生的选课情况，然后调用该存储过程。
CREATE PROCEDURE  xuanke_info
AS
SELECT  S.#,sname,sex,age,c#,score
FROM S INNER JOIN sc
ON s.s#=sc.s#
GO
3、使用带参数的存储过程
创建存储过程，根据student数据库的3个表查询指定学生的选课情况。
CREATE PROCEDURE stu_info @sname (8)
AS
SELECT  S.#,sname,sex,age,c#,cname,score
FROM S,SC,C
Where s.s#=sc.s# and sc.c#=c.c# and sname=@sname
GO
EXECUTE  stu_info '张三'
4、使用带OUTPUT参数的存储过程
编写存储过程，根据给定的学生学号，计算该生的平均成绩
Create procedure  stuavg @sno char(4) @s_sum float output,@s_avg  float output
As
Select @s_sum=sum(score),s_avg=avg(score)
From sc
Where s#=@sno
在调用存储过程stuavg时，存储过程定义时的形参名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹配。不过数据类型和参数位置必须匹配。执行语句如
DECLARE @x float,@y float
EXECUTE stuavg ‘s3’',@x OUTPUT,@y output
SELECT @x, @y
GO

Oracle 存储过程总结 基本应用技巧，大家可以学习下oracle存储过程最基本的东西。
1、创建存储过程
create or replace procedure test(var_name_1 in type,var_name_2 out type) as
--声明变量(变量名 变量类型)
begin
--存储过程的执行体
end test;
打印出输入的时间信息
E.g:
create or replace procedure test(workDate in Date) is
begin
dbms_output.putline('The input date is:'||to_date(workDate,'yyyy-mm-dd'));
end test;
2、变量赋值
变量名 := 值;
E.g：
create or replace procedure test(workDate in Date) is
x number(4,2);
begin
x := 1;
end test;
3、判断语句:
if 比较式 then begin end; end if;
E.g
create or replace procedure test(x in number) is
begin
if x >0 then
begin
x := 0 - x;
end;
end if;
if x = 0 then
begin
x: = 1;
end;
end if;
end test;
4、For 循环For ... in ... LOOP
--执行语句
end LOOP;
(1)循环遍历游标
create or replace procedure test() as
Cursor cursor is select name from student; name varchar(20);
begin
for name in cursor LOOP
begin
dbms_output.putline(name);
end;
end LOOP;
end test;
(2)循环遍历数组
create or replace procedure test(varArray in myPackage.TestArray) as
--(输入参数varArray 是自定义的数组类型，定义方式见标题6)
i number;
begin
i := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张
--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历
for i in 1..varArray.count LOOP
dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));
end LOOP;
end test;
5、While 循环
while 条件语句 LOOP
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i < 10 LOOP
begin
i:= i + 1;
end;
end LOOP;
end test;
6、数组首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。
使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。
(1)使用Oracle自带的数组类型
x array; --使用时需要需要进行初始化
e.g:
create or replace procedure test(y out array) is
x array;
begin
x := new array();
y := x;
end test;
(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，以便于管理)
E.g (自定义使用参见标题4.2) create or replace package myPackage is
-- Public type declarations type info is record( name varchar(20), y number);
type TestArray is table of info index by binary_integer; --此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是
name，一个是y。需要注意的是此处使用了Index by binary_integer 编制该Table的索引项，也可以不写，直接写成：type TestArray is
table of info，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
7.游标的使用 Oracle中Cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：
(1)Cursor型游标(不能用于参数传递)
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where ...; --Cursor的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...; --Cursor的使用方式2
可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor的遍历
end test;
(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR; name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能通过OPEN方法来打开和赋值
LOOP
fetch cursor into name --SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR中可使用三个状态属性： ---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息) ---%ROWCOUNT(然后当前游标所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：
现假设存在两张表，一张是学生成绩表(studnet)，字段为：stdId,math,article,language,music,sport,total,average,step 一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment
通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。
create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
begin
if record.comment = 'A' then
begin
total := total + 20;
go to next; --使用go to跳出for循环
end;
end if;
end;
end if;
end LOOP;
<> average := total / 5;
update student t set t.total=total and t.average = average where t.stdId = stdId;
end LOOP;
end;
end autocomputer;
--取得学生评论信息的存储过程
create or replace procedure get_comment(commentArray out myPackage.myArray) is
rs SYS_REFCURSOR；
record myPackage.stdInfo;
stdId varchar(30);
comment varchar(1);
i number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
fetch rs into stdId,comment; exit when rs%NOTFOUND;
record.stdId := stdId;
record.comment := comment;
recommentArray(i) := record;
i:=i + 1;
end LOOP;
end get_comment;
--定义数组类型myArray
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;
本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源：php中文网