PL/SQL编程：过程函数触发器题目分析

用到的表放到最后了！

过程

编写一个存储过程，能通过“类型名称”直接从商品信息表中获取对应类型的商品数据
题目中的关键字：过程，通过类型名称，直接获取，商品数据

分析：首先理清表中各个字段的关系，类型名称在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,'数学','刘冰');