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

[align=center][size=large][b]Oracle练习——建表、查询、过程、函数的使用[/b][/size][/align]
[size=medium][b]题目：[/b][/size]
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 订单总额

[size=medium][b]插入数据练习[/b][/size]
一、类型表数据
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

[size=medium][b][color=red]要求：[/color][/b][/size]

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

--创建类型表
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;

查询出添加数据成功后的三张表格如下：
[img]http://dl2.iteye.com/upload/attachment/0121/7057/31048b1d-b8bb-334c-bc71-6c1e074508e3.png[/img]
[img]http://dl2.iteye.com/upload/attachment/0121/7071/d0f1305f-5f0c-3289-814d-82ac4139bd07.png[/img]
[img]http://dl2.iteye.com/upload/attachment/0121/7073/5c49cb36-a1f1-347e-961a-057e3d6b40cb.png[/img]


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

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

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7075/cef03d50-412c-3373-80a1-389e35faf7db.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7077/cba97a20-f770-3e52-bc12-f7be26f64188.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7079/12b073a3-df88-3524-a805-e232eb568111.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7081/b563a565-d125-3590-8d8f-30263ac59010.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7083/c997a9eb-9de9-3f06-b8bf-95105ab12d84.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7085/33689968-f796-3bca-870d-25d3ddc206eb.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7087/47727e0e-e44b-3e9d-b646-1caf51c63ac3.png[/img]

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;
结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7089/5e1539dc-0a79-390c-841d-9740e8a4f0b1.png[/img]

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;

结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7091/9849307f-cf01-3a19-a66c-de11659af5b8.png[/img]

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;
结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7095/63943e57-8c2a-38aa-8f19-e4d943ef1f85.png[/img]

--查看
select * from p_product for update;
结果：
[img]http://dl2.iteye.com/upload/attachment/0121/7093/191c0d3e-9b5c-38f0-88f7-62a0b0d8c603.png[/img]