欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-11 09:54:44
...
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;

查询出添加数据成功后的三张表格如下:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 


2.执行如下查询语句:
  2.1:查询商品名称和商品价格,商品类型。
select p.name ,p.price,t.name from p_product p,t_type t where t.id = p.t_id;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

  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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

  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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

  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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

  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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

  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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

  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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

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;
结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

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;

结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

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;
结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 

--查看
select * from p_product for update;
结果:
Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 




  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 1.2 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 3.9 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 5 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 2.7 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 866 Bytes
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 1.5 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 1.4 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 857 Bytes
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 1.6 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 1.5 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 633 Bytes
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 6.3 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 3.9 KB
  • Oracle练习——建表、查询、过程、函数的使用
            
    
    博客分类: Oracle Oraclecursorprocedurefunctionselect 
  • 大小: 10.7 KB