Oracle练习——建表、查询、过程、函数的使用

Oracle练习——建表、查询、过程、函数的使用

题目：
1.类型表 t_type
      id 商品类型编号 主键
      name 类型名称

2.商品信息表 p_product
      id 商品编号   主键
      name 商品名称
      pro_date 生产日期
      eff_date 有效期
      price 商品单价
      p_unit 商品单位
      t_id 商品类型编号 ->关联类型表的id

3.订单表 o_orders
      id 订单编号   主键
      dealtime 成交时间
      p_id 商品编号 ->关联商品表的id
      p_num 商品数量
      c_name 客户名称
      o_oper 业务员名称
      tatol 订单总额

插入数据练习
一、类型表数据
    1 食品类
    2 生活用品
    3 电器
    4 厨房用品
二、商品表数据
    1，傻小子 ，20160215， 60天， 2.5， 包，1
    2，海飞丝 ，20160614， 730天数， 18.8， 瓶，2
    3，辣条 ，20161112， 30天， 1.5， 包，1
    4，苏泊尔电饭煲 ，20150803， null， 389， 台，3
    5，威猛先生 ，20160621， 730天， 36.8， 瓶，4
三、订单信息表
    001 20160610 1 200 傻小子工厂 张三 400
    002 20161120 1 300 傻小子工厂 张三 500
    003 20160820 2 60 海飞丝生产线 王语嫣 840
    004 20161201 3 100 辣条生产商 段誉 100
    005 20160606 4 100 苏泊尔总经销商 乔峰 3500
    006 20160411 5 200 威猛总代理    阿朱 5000

要求：

1.创建以上表，添加外键，添加如上数据，类型表数据用插入方法添加。

--创建类型表
create table t_type
(
  id number(2) primary key,    --将id设置为主键
  name varchar2(20)
)
--创建商品信息表
create  table p_product
(
  id number(2) primary key,    --将id设置为主键
  name varchar2(20),
  pro_date date,
  eff_date varchar2(5),
  price number(5,1),
  p_unit varchar2(4),
  t_id number(2),              --将t_id设置为外键，与类型表id关联
  constraint FK_t_id foreign key (t_id) references t_type(id)
)
--创建订单表
create table o_orders
(
  id varchar2(3) primary key,  --将id设置为主键
  dealtime date,
  p_num number(4),
  c_name varchar2(20),
  o_oper varchar2(20),
  tatol number(10), 
  p_id number(2),              --将p_id设置为外键，与商品信息表的id关联 
  constraint FK_p_id foreign key (p_id) references p_product(id)
)
添加数据：
向类型表添加数据
insert into t_type(id,name) values(1,'食品类');
insert into t_type(id,name) values(2,'生活用品');
insert into t_type values(3,'电器');
insert into t_type values(4,'厨房用品');
用for update 方法向商品表和订单信息表中添加数据
select * from p_product for update;
select * from o_orders for update;

查询出添加数据成功后的三张表格如下：





2.执行如下查询语句：
  2.1：查询商品名称和商品价格，商品类型。

select p.name ,p.price,t.name from p_product p,t_type t where t.id = p.t_id;

结果：


  2.2：查询由业务员王语嫣签订的订单，显示订单的编号，订单价格，订单中商品名称，商品数量。

select o.id,o.tatol,p.name,o.p_num  from o_orders o,p_product p 
 where o_oper = '王语嫣' and p.id = o.p_id;

结果：


  2.3：查询订单总额在1000元以上的所有订单，显示订单编号，订单商品名称，最后根据价钱排序。

select o.id,p.name,p.price,o.tatol from o_orders o,p_product p
 where o.tatol >1000 and o.p_id = p.id
 order by p.price desc;

结果：


  2.4：根据商品类型分类，查询出每个类型的商品各下个多次订单。

select t.name,x.count from t_type t,
(select count(c.id) count ,t_id from 
(select o.*,p.t_id from o_orders o,p_product p 
where o.p_id = p.id) c  group by t_id) x
where t.id = x.t_id;

结果：


  2.5：找出订单中金额最高的订单，显示此订单当中的商品名称，单价，订单总额。

select  p.name ,p.price ,o.tatol   from 
(select max(tatol) tatol from o_orders ) ta,p_product p,o_orders o 
where  p.id = o.p_id and ta.tatol = o.tatol;

结果：


  2.6：查询类型是食品类的所有订单信息，显示订单编号，订单成交的时间。

select o.id,o.dealtime, t.name from o_orders o, t_type t,p_product p
where  t.name = '食品类' and p.t_id = t.id and o.p_id = p.id;

结果：


  2.7：查询张三的所有订单，但是订单的编号，订单成交的时间，显示订单中商品的名称。

select o.id,o.dealtime,o.o_oper,p.name from o_orders o,p_product p
where o.o_oper = '张三' and o.p_id = p.id;

结果：


3.建立一个函数，输入商品编号，查询出商品的名称，如果无此商品编号抛出异常。

create or replace function checked(pid number)
return  varchar2 
is
   pname varchar2(10);
begin
  select p.name into pname from p_product p where p.id =pid;
  return  pname; 
  Exception
    when no_data_found then
     return '无此商品！';
end;

--调用
select checked(2) from dual;
结果：


4.建立一个存储过程，输入订单编号，打印出订单的金额，商品的数据，商品的名称，如果无此订单编号抛出异常。

create or replace procedure pro_tatol
(oid o_orders.id%type,otatol out number,oname out varchar2,opnum out number)
is
begin
  select o.tatol,o.p_num,p.name into otatol,opnum,oname 
  from o_orders o,p_product p
  where oid = o.id and p.id = o.p_id;
  dbms_output.put_line('订单的金额: '||otatol||' 商品的名称: '||oname||' 商品的数据: '||opnum);
  Exception 
    when no_data_found then
      dbms_output.put_line('无此订单！');
end;

--调用
declare
      otatol number;
      opnum  number;
      oname  varchar2(10);
begin
      pro_tatol('001',otatol,oname,opnum);
end;

结果：


5.建立一个存储过程，查询所有的商品信息，将所有商品单价+1元，单价超过300的
不进行涨价。

create or replace procedure cInfo
is
   myproduct  p_product%rowtype;
   cursor cur_c is select * from p_product ;    
begin    
   open cur_c;
     loop
         fetch cur_c into myproduct;
         exit when cur_c%notfound;
         if myproduct.price < 300 then
            update  p_product price set price = price+1  where id=myproduct.id;
            dbms_output.put_line(myproduct.name||'涨价了1元 ');
         else 
            dbms_output.put_line(myproduct.name||'不涨价');
         end if; 
     end loop; 
  close cur_c;
end;

--调用
  begin
        cInfo;
  end;
结果：


--查看
select * from p_product for update;
结果：