用到的表放到最后了!
过程
编写一个存储过程,能通过“类型名称”直接从商品信息表中获取对应类型的商品数据
题目中的关键字:过程,通过类型名称,直接获取,商品数据
分析:首先理清表中各个字段的关系,类型名称在goodstype表中,商品数据在GoodS表中
确定过程的参数,根据关键词“通过类型名称”可以知道类型名称是输入参数。
题目要求根据“类型名称”查询相关商品,同一类型的商品有很多,查询结果会有很多条,所以需要用cursor游标。
获取的数据需要输出,所以要定义参数接收查询到的数据。
总体思路:1.定义一个参数type_Id接收通过类型名称查询到的typeid(商品类型)。
2.在过程中定义一个游标,通过type_Id查询对应类型的商品数据,将查询的结果赋给游标。
3. 用循环语句遍历输出游标中我们需要的数据。
实现:(我习惯先写begin语句,参数定义部分先空着)
第一部分:
Create Or Replace Procedure Search(type_name goodstype.typename%type)--search是过程名,参数是我们分析出来的
Is
--这里是写参数定义
Type_Id Goodstype.Typeid%Type;--这是总体思路1中所说的参数
Cursor Goods_Data Is Select *From Goods Where Typeid=Type_Id;--我们已经分析得出需要一个游标类型的参数,所以先定义一个游标,并赋值上select语句
--其余参数等begin中用到了再在这里定义
Begin--这里是数据操作
Select Typeid Into Type_Id From Goodstype Where Typename=Type_Name;--总体思路步骤1,这一步执行完type_id中就有具体的值了
Open Goods_Data;--打开游标,游标只有打开的时候赋值的select语句才回执行
--下边就是遍历游标了
Loop
Fetch Goods_Data Into ??;--这里需要参数接收从游标中获取的数据,所以需要定义参数
Exit When Goods_Data%Notfound;--一定不要忘记写终止语句。当游标找不到数据时退出循环。
Sys.Dbms_Output.Put_Line(???);--这里将获取的数据输出,因为还没有定义参数,所以没有写。
End Loop;--结束循环
Close Goods_Data;--关闭游标
End;
第二部分:(这里就得加上参数了)
Create Or Replace Procedure Search(Type_Name Goodstype.Typename%Type)
Is
--前两个参数在步骤一中年已经定义了
Type_Id Goodstype.Typeid%Type;
Cursor Goods_Data Is Select *From Goods Where Typeid=Type_Id;
--参数定义;游标中的数据字段是goods表中所有的字段(根据游标赋值的select语句判断的),也就是Goods%Rowtype,所以我们接收数据时也需要Goods%Rowtype类型的参数
--所以我们定义一个Goods%Rowtype类型的参数
Goods_Info Goods%Rowtype;--Goods_Info里的字段类型,字段名和goods表中的字段完全一样
Begin
Select Typeid Into Type_Id From Goodstype Where Typename=Type_Name;
Open Goods_Data;
Loop
Fetch Goods_Data Into Goods_Info;--这里就可以将游标中的值赋值给定义的参数变量了
Exit When Goods_Data%Notfound;
--输出参数中的字段值
Sys.Dbms_Output.Put_Line(Goods_Info.gid||' '||Goods_Info.gname ||' '||Goods_Info.typeid ||' '||Goods_Info.price||' '|| Goods_Info.stock ||' '||Goods_Info.soldnum ||' '||Goods_Info.scrq);
End Loop;
Close Goods_Data;
End;
第三部分:(加上异常处理,游标打开后没有存入数据怎么办?所以加一个异常处理来处理没查到数据的情况)
Create Or Replace Procedure Search(Type_Name Goodstype.Typename%Type)
Is
Type_Id Goodstype.Typeid%Type;
Cursor Goods_Data Is Select *From Goods Where Typeid=Type_Id;
Goods_Info Goods%Rowtype;
No_Data_Exception Exception;--异常定义
Begin
Select Typeid Into Type_Id From Goodstype Where Typename=Type_Name;
Open Goods_Data;
If (Goods_Data%Notfound) Then
Raise No_Data_Exception;--如果Goods_Data游标中的数据为空,就引发异常
else--如果不为空就循环遍历游标
Loop
Fetch Goods_Data Into Goods_Info;--这里就可以将游标中的值赋值给定义的参数变量了
Exit When Goods_Data%Notfound;
--输出参数中的字段值
Sys.Dbms_Output.Put_Line(Goods_Info.gid||' '||Goods_Info.gname ||' '||Goods_Info.typeid ||' '||Goods_Info.price||' '|| Goods_Info.stock ||' '||Goods_Info.soldnum ||' '||Goods_Info.scrq);
End Loop;
end if;
Close Goods_Data;
Exception
When No_Data_Exception Then--这里是异常处理,自定义异常被触发了就会执行这里的代码。
SYS.Dbms_Output.Put_Line('没有找到数据');
End;
–过程的调用
begin
search('摄影机');
end;
这是一个简单的过程题:例:创建一个存储过程,根据提供的雇员姓名(作为过程的参数),将该雇员的工资改为2000;
Create Or Replace Procedure Change_Sal (emp_name emp.ename%type)
Is
Begin
Update Emp Set Sal=2000 where Ename='emp_name';--根据参数更新
end;
函数
创建存储函数,实现如下功能:输入学号,根据该学生选课的平均分显示提示信息:
平均分大于等于90,显示“该生成绩优秀”,平均分小于90但大于等于80,显示“该生成绩良好”,
平均分小于80但大于等于60,显示“该生成绩合格”,小于60,则显示“该生成绩不合格”。
题目中的关键字:函数,输入学号,根据平均分,显示提示信息
分析:函数要比过程简单的多,只需要接收参数,输出参数就好了
这个题里输入参数是学号,输出提示信息。
学号的类型是sc_Cid Sc.Cid%Type就不用解释了,输出的提示信息是文字,所以定义成varchar2类型的就可以.
总体思路:通过学号查出这个学生的平均成绩,将查到的平均成绩放到定义的参数avg_Grade中,
使用if…Elsif…Else或者case When …Then 的方式判断这个学生的平均成绩所在的等级,输出显示信息。
有一些细节我写在代码里,要注意看。
实现:
Create Or Replace Function grade_Function(Sc_Cid Sc.Cid%Type) Return varchar--这里的return语句是说明返回值类型,不加精度!!很重要
Is
Avg_Grade Sc.Grade%Type;--这个参数是接收提示信息的,返回值就是这个参数,在倒数第二行返回
Stu_Grade Varchar2(20);--这个参数是平均成绩
Begin
Select Avg(Grade) Into Avg_Grade From Sc Where Sid=Sc_Cid;--这是查询该学生的平均成绩,赋值给Avg_Grade参数
If(Avg_Grade>90) Then --这中间是一大段判断语句
Stu_Grade:='该生成绩优秀';--赋值不要忘了加冒号
Elsif(Avg_Grade>80) Then
Stu_Grade:='该生成绩良好';
Elsif(Avg_Grade>60) Then
Stu_Grade:='该生成绩合格';
Else
Stu_Grade:='该生成绩不合格';
End If;
Return Stu_Grade;--这儿~
end;
–一下是函数的调用,因为有输出语句所以用匿名块的方式比较简单
Begin
Sys.Dbms_Output.Put_Line(Grade_Function(103));--直接返回值输出
end;
触发器
创建一个触发器,当student表中的学号变更时,同时修改sc表中相应学生的学号信息
触发器没有参数,没有返回值。
题目中的关键字:触发器,student学号变更时,修改sc表中学号。
分析:触发器拿到题目就要分析:时间,操作,对象,级别
这个触发器是发生在操作之前(Before)还是之后(After)?是进行什么操作时会触发这个触发器?是对于哪一个对象(一般是表)进行的?是行触发还是表触发?
这个题是student学号变更后,再修改sc表的学号,所以是after。
修改操作触发触发器update
对student操作后会触发触发器 On Student
Update是对某一行操作,所以是行触发
注释:
1.
行触发:对触发事件影响的每一行执行触发器
语句触发:对于触发事件只能触发一次,而且不能访问受触发器影响的每一行的值
2.
行级触发器中有两个伪变量,用于识别值的状态
:old 操作之前,是记录变量,使用形式::old.字段名
:new 操作之后,是记录变量,使用形式::new.字段名
只在dml触发表中字段时才有效,只能在触发器内部使用
这个题里就可以用:new.sid 表示修改后的学生学号,用:old.sid 表示修改前的学生学号
总体思路:创建触发器,判断:new.sid和:old.sid是否相同(相同就是没变更,不同就是变更了),来决定要不要修改sc表中的sid
实现:
Create Or Replace Trigger Change_Sno After Update On Student For Each Row
begin
If(:New.Sid!=:Old.Sid) Then
Update Sc Set Sid=:New.Sid Where Sid=:Old.Sid;
End If;
end;
验证:
Update Student Set Sid=10 Where Sid=100;
select *from sc;
以下是建表语句:
过程题的商品表:
订单表,表结构为(订单号,货号,订货单位,售价,订购量,送货地址),插入6条记录。
create table dingd(ddid number(5),huohao char(4),dhunit varchar2(12),price number(6,2),dgl number(3),shadd varchar2(10));
insert into dingd values(97001,'SL02','阳光公司',800.50,500,'甲地');
insert into dingd values(97002,'DH03','友谊商城',500.88,400,'乙地');
insert into dingd values(97003,'DS01','和平饭店',4000.99,600,'乙地');
insert into dingd values(97004,'DS02','五环实业',5000.99,100,'丙地');
insert into dingd values(97005,'SL02','大地宾馆',800.50,50,'丙地');
insert into dingd values(97006,'DS01','蓝天乐园',4000.99,80,'甲地');
商品表,表结构为(货号,品名,库存量,仓库地点,进价),插入7条记录
create table goods(huohao char(4),gname varchar2(12),stock number(4),ckadd varchar2(6),buyprice number(6,2));
INSERT into goods values('DH03','手机A',1000,'乙地',450.00);
INSERT into goods values('DH04','手机B',800,'甲地',300.00);
INSERT into goods values('SL01','收录机A',1200,'甲地',666.90);
INSERT into goods values('SL02','收录机B',300,'丙地',750.50);
INSERT into goods values('LX88','录像机A',2600,'乙地',2000.00);
INSERT into goods values('DS01','电视机B',3000,'甲地',3050.00);
INSERT into goods values('DS02','电视机C',1200,'丙地',4600.00);
函数和触发器用的的学生选课表:
学生表,表结构(学号,姓名,年龄,性别,系别,出生地),插入几条记录
create table s(sid number(3),sname varchar2(10),age number(2),sex char(3),department varchar2(10),birthplace varchar2(10));
insert into s values(100,'曾华',21,'男','信息系','威海');
insert into s values(105,'张明',20,'男','信息系','烟台');
insert into s values(108,'王小丽',21,'女','旅游系','威海');
insert into s values(101,'李军',19,'男','金融系','青岛');
insert into s values(107,'王芳',24,'女','机电系','临沂');
insert into s values(102,'刘红英',22,'男','信息系','枣庄');
insert into s values(109,'唐奇',21,'男','机电系','威海');
insert into s values(103,'赵晨',21,'女','金融系','济南');
成绩表,表结构(学号,课程号,成绩),插入几条记录
create table sc(sid number(3),cid number(4),grade number(5,2));
insert into sc values(103,3245,86);
insert into sc values(105,3245,75);
insert into sc values(109,3245,68);
insert into sc values(103,3105,92);
insert into sc values(105,3105,88);
insert into sc values(109,3105,76);
insert into sc values(101,3105,64);
insert into sc values(107,3105,91);
insert into sc values(108,3105,78);
insert into sc values(101,6166,85);
insert into sc values(107,6166,58);
insert into sc values(108,6166,81);
insert into sc values(100,9888,66);
insert into sc values(102,9888,59);
课程表,表结构(课程号,课程名,教师)
create table c(cid number(4),cname varchar2(20),teacher varchar2(10));
insert into c values(3105,'计算机导论','王萍');
insert into c values(3245,'操作系统','李诚');
insert into c values(6166,'英语','张旭');
insert into c values(9888,'数学','刘冰');