oracle基础实例

在scott用户下创建表

--创建表Goods
create table Goods
(
   Gid number(4),
   Gname varchar2(20),
   Gprice number(10,2),
   Gscock number(4),
   GtypeId number(4)
)

--创建表
create table Gtype(
       GtypeId number(4) primary key,
       Gtypename varchar(20)
)
--插入测试数据
insert into Gtype values(seq_tid.nextval,'百货类');
insert into Gtype values(seq_tid.nextval,'服装类');
select * from Goods;
select * from Gtype;
1,为goods 与gtype 表创建序列，实现自动增长
create sequence seq_id
       start with 1
       increment by 1
       maxvalue 30 cycle ;
create sequence seq_tid
       start with 1
       increment by 1
       maxvalue 30 cycle ;
2，创建zhangs/zhangs用户，为其分配访问goods,gtype的权限，
create user zhangs
       identified by zhangs
       default tablespace users
       temporary tablespace temp;
grant select on goods to zhangs;
grant select on gtype to zhangs;
      
3,为两张表创建外键引用关系
alter table Goods add
      constraint fk_Goods_Gtype foreign key(GtypeId) references Gtype(GtypeId);
3，创建存储过程addgoods 实现新商品添加
create or replace procedure addgoods
(
   Gname varchar2,
   Gprice number,
   Gscock number,
   GtypeId number
)
as
begin
  insert into Goods values( seq_id.nextval,Gname,Gprice,Gscock,GtypeId);
end;
select seq_id.nextval from dual;
--调用存储过程
begin
  addgoods('刷子',220,100,1);
 
end;

  insert into Goods values(seq_id.nextval,'盆子',20,300,1);
  insert into Goods values(seq_id.nextval,'牛仔裤',260,150,2);
  insert into Goods values(seq_id.nextval,'衣服',100,110,2);
4，创建函数，实现通过商品类型计算该类商品总价值，产生查询：某类商品及其总价值
create or replace function good_price
(
       tid Gtype.Gtypeid%type
)
return Gtype.Gtypename%type
as
tname Gtype.Gtypename%type;
sumprice Goods.Gprice%type;
begin
  select Gtypename,sum(Gprice*Gscock)into tname,sumprice from Goods,Gtype where Goods.GtypeId=tid and Goods.Gtypeid=Gtype.Gtypeid group by Gtypename;
  return '商品类型：'||tname||'   商品总价：'||sumprice;
end;

--调用函数
begin
  dbms_output.put_line(good_price(1));
end;

select * from Goods;
select * from Gtype;
5，创建过程selectGoodsByTypeId(typeno number),使用游标，查询出该类商品详细信息
create or replace procedure selectGoodsByTypeId
(
       typeno number
)
as
       cursor g_cursor---此处省略了declare
       is
       select gname,gprice,gscock from Goods where gtypeId=typeno;
       g_name goods.gname%type;
       g_price goods.gprice%type;
       g_scock goods.gprice%type;
begin
  open g_cursor;
  loop
    fetch g_cursor into g_name,g_price,g_scock;
    exit when g_cursor%notfound;
   dbms_output.put_line('商品名称：'||g_name||' 商品价格：'||g_price||'  商品库存：'||g_scock);
  end loop;
  close g_cursor;
end;

--调用存储过程
begin
  selectGoodsByTypeId(1);
end;
6，创建过程 addscock,实现当商品数量小于100时，添加该商品200件；小于200时，添加该商品100件，并输出添加信息;
create or replace procedure addscock

as
       cursor s_cursor
       is
       select gname,gscock from goods;
       names goods.gname%type;
       snum goods.gscock%type;
begin
  open s_cursor;
       loop
         fetch s_cursor into names,snum;
         if snum<100 then
           update goods set gscock=gscock+200;
         elsif snum>100 and snum<200 then
               update goods set gscock=gscock+100 ;
         end if;
         dbms_output.put_line('商品名称：'||names||'添加后商品的数量：'||snum);
         exit when s_cursor%notfound;
       end loop;
  close s_cursor;
end;
--调用存储过程
begin
  addscock;
end;
7,实现触发器，当添加，修改商品时，记录该商品的编号，维护日期

create or replace trigger tri_good
before insert or update on goods
for each row
declare
begin
  if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then
    case
      when inserting then
         raise_application_error(-20002,'不能在休息日添加员工信息！输入的商品编号为：'||:old.gid);
      
      when updating then
        raise_application_error(-20002,'不能在休息日更改员工信息！输入的商品编号为：'||:old.gid);
      
    end case;
 end if;
end;
--触发器执行成功，不在规定的时间内，不能对表进行修改
update goods set gscock=99 where gid=2;

--修改密码
alter user scott identified  by giger;