oracle_day5_程序包

---1创建一个程序包，完成如下要求：
  --1：根据输入的商品编号，查找该商品价格和库存。
  --2：根据输入的用户名称，获取该用户的订单信息
  --3：获取当前时间的三天前时间 






  --4：查询当前时间的一周前时间
  select sYSDATE-7 from   DUAL;
  create or replace package package01
  is
  
  procedure  select_pro(vid number,info out sys_refcursor);
  procedure  select_orderinfo(vrelname VARCHAR2,oinfo out sys_refcursor);
  function    seltime1() return date;
    function    seltime2() return date;
  
  end package01;
  
    create or replace package body  package01
  is
  
  procedure  select_pro(vid number,info out sys_refcursor)
    is
    begin
    open info for  select price,stockcount from es_product where id=vid;
      end;
  procedure  select_orderinfo(vrelname VARCHAR2,oinfo out sys_refcursor)
    is
     begin
       open oinfo for  
         select es_order.* from 
  es_user,es_order
  where es_user.id=es_order.user_id
  and es_user.realname=vrelname;
       end;
       
         function  seltime1  return date
           is
           dat1  date;
           begin
            select sysdate-3 into dat1 from   DUAL; 
            return   dat1 ;
             end;
         
    function  seltime2 return date
      is
         dat2  date;
           begin
                select sYSDATE-7 into dat2 from   DUAL; 
                return    dat2;
             end;
  
  end package01;
  
  
  ----------------------------
  
  
  declare 
 info  sys_refcursor;
oinfo  sys_refcursor;
  vprice es_product.price%type;
   vso es_product.stockcount%type;
   vrelname es_user.realname%type;
   orderinfo es_order%rowtype;
   times1  date;
    times2  date;
 
  begin
    package01.select_pro(1,info);
    package01.select_orderinfo('张管',oinfo);
   times1:=package01.seltime1();
   times2:=package01.seltime2();
    loop
      fetch info into  vprice,vso;
      exit when info%notfound ;
     dbms_output.put_line(vprice); 
      end loop;
      loop
      fetch oinfo into  orderinfo;
      exit when oinfo%notfound ;
     dbms_output.put_line('订单编号：'||orderinfo.id||',下单人'||orderinfo.realname); 
        end loop;
        dbms_output.put_line(to_char(times1,'yyyy-mm-dd hh24:mi.ss')); 
          dbms_output.put_line(to_char(times2,'yyyy-mm-dd hh24:mi.ss'));
    end;
  
  
  
------------------------------------
-2 监控用户表的增删改操作，如果增加的
   用户usertype为2要在监控表中做一个记录


   如果删除usertype为2记录的用户，扔出系统异常不允许删除
   如果修改usertype为2记录的用户，修改成普通用户，也需要在监控表中记录
   
   create table jiank(
   dec varchar2(100),
   time date
   )
   
   
   create or replace trigger tri_userinfo
   before update or delete or insert on es_user for each row
  
   begin
      if inserting then
       insert into jiank values('记录添加',sysdate);
       elsif  deleting then
       insert into jiank values('记录删除',sysdate);
       elsif updating then
       insert into jiank values('记录修改了',sysdate);
     else
       insert into jiank values('xxxx',sysdate);
       end if;
     end;

--------------------------------------

create table userlog
(
  event varchar2(20),
  descx  varchar2(30),
  dotime date
)


create trigger user_add_trig
after insert  on es_user
for each row
begin
  if :new.usertype=2 then
    insert into userlog values ('insert','增加了一个管理员',sysdate);
  end if;
end;




create trigger user_del_trig
after delete  on es_user
for each row
begin
  if :old.usertype=2 then
     raise_application_error(-20010,'管理员不允许删');
  end if;
end;




create trigger user_mod_trig
after update  on es_user
for each row
begin
  if :old.usertype=2 then
     insert into userlog values ('update','修改管理员为其他用户',sysdate);
  end if;
end;

------------------------------------------

  
 insert into ES_USER (id, username, password, realname, tel, address, zip, email, usertype)
values (10, 'vipuser', 'vipuser', '陈红xxxx', '13801000104', '北京市中关村4号', '100100', 'hong_chen@aptech.com', 2);
  update  ES_USER set  realname='ssss' where id=10;
  
  delete from es_user where id=10
  select * from es_user;
  
  
  select * from jiank;
  
  --3 创建一个学员表，并且为学员表创建一个序列，插入5条数据，主键值用序列的值
  drop table stui;
  create table stui(
  id number(4) primary key,
  name varchar2(100)
  )
  drop sequence st_seqss;
  create sequence st_seqss
  start with 1
  increment by 1
  ;
  declare
  begin
    for ind in 1..5 loop
        insert   into stui  values(st_seqss.nextval,'sutdent');
      end loop;
    end;
    
    truncate table stui;


  select * from stui;
  
  
  
  
  


--4 在商品表的价格列上创建索引，在商品表的类型列上添加位图索引
drop index product_price_index;
create index index_product_price on es_product(price);
create bitmap index index_product_sort_id on ES_PRODUCT(sort_id);
select * from es_product;


--5 创建一个视图，显示商品名，价格，类型名，库存等

--5创建视图
create view  product_viewx as
select t.name,t.stockcount,t.price,s.sortname from es_product t ,es_sort s 
where t.sort_id= s.id




create trigger product_viewx_insert 
instead of insert on product_viewx
declare
  v_sortid  number;
begin
  --根据类别名查类别id
   select id into v_sortid from es_sort t 
   where t.sortname=:new.sortname; 
   --插入商品表数据
   insert into es_product(id,name,price,stockcount,sort_id)
   values(product_seq.nextval,:new.name,:new.price,:new.stockcount,v_sortid);
end;

--------------------

create sequence product_seq start with 16


insert into product_viewx values('xx',55,5,'类别8')