ORACLE訂單管理（實例)

create table stock
(
 Icode varchar2(20) primary key,
 Idescrip varchar2(50),
 Pqty number(4)
);
create table input
(
 IID varchar2(20),
 Icode varchar2(20),
 Idescrip varchar2(50),
 Pqty number(4),
 IDate date,
 constraint input_pk primary key(iid,icode)
);
create table sales
(
 sID varchar2(20),
 Icode varchar2(20),
 Idescrip varchar2(50),
 pqty number(4),
 sDate date,
 constraint sales_pk primary key(sid,icode)
);

create table return
(
 rID varchar2(20),
 Icode varchar2(20),
 Idescrip varchar2(50),
 pqty number(4),
 rDate date,
 constraint return_pk primary key(rid,icode)
);
3、 创建Input表和Sales表的触发器，当进货和销售产品时，系统自动增、减Stock库存表的Icode的数量。
create or replace trigger input
before insert
on input
for each row
declare
 cursor c1 is select * from stock where Icode=:new.Icode for update;
 v_stock stock%rowtype;
begin
 open c1;
 fetch c1 into v_stock;
 if(c1%found) then
  update stock set pqty=pqty+:new.pqty where current of c1;
 else
  insert into stock(icode,pqty,Idescrip) values(:new.icode,:new.pqty,:new.Idescrip);
 end if;
 close c1; 
end;

--测试
insert into input(iid,icode,Idescrip,pqty,idate) values('001','a1','aaaaa',100,sysdate);
insert into input(iid,icode,Idescrip,pqty,idate) values('005','a1','aaaaa',100,sysdate);
insert into input(iid,icode,Idescrip,pqty,idate) values('003','a1','aaaaa',100,sysdate);

insert into input(iid,icode,Idescrip,pqty,idate) values('004','a2','aaaaa',100,sysdate);

create or replace trigger sales_trigger
before insert
on sales
for each row
declare
 cursor c1 is select * from stock where icode=:new.icode for update;
 v_stock stock%rowtype;
begin
 open c1 ;
 fetch c1 into v_stock;
 if(c1%found) then
  if(:new.pqty<v_stock.pqty) then
   --update stock set pqty=pqty-:new.pqty where icode=:new.icode;
   update stock set pqty=pqty-:new.pqty where current of c1;
  elsif(:new.pqty=v_stock.pqty) then
   delete from stock where current of c1;
  else
   raise_application_error(-20001,'库存不足，无法销售');
  end if;
 else
  raise_application_error(-20001,'没有库存，无法销售');
 end if;
end;

--

insert into sales(sid,icode,Idescrip,pqty,sdate) values('001','a1','aaaaa',200,sysdate);

insert into sales(sid,icode,Idescrip,pqty,sdate) values('002','a1','aaaaa',200,sysdate);

insert into sales(sid,icode,Idescrip,pqty,sdate) values('003','a1','aaaaa',100,sysdate);

insert into sales(sid,icode,Idescrip,pqty,sdate) values('004','a1','aaaaa',100,sysdate);

insert into sales(sid,icode,Idescrip,pqty,sdate) values('004','a2','aaaaa',10,sysdate);

insert into sales(sid,icode,Idescrip,pqty,sdate) values('004','a1','aaaaa',10,sysdate);

insert into sales(sid,icode,Idescrip,pqty,sdate) values('004','a2','aaaaa',100,sysdate);
4、 创建一个存储过程SalesProduct，当输入销售单的单号时，显示指定单号的所有销售明细。
create or replace procedure SalesProduct(p_sid varchar2)
is
 cursor c1 is select * from sales where sid=p_sid;
 v_sales sales%rowtype;
begin
 open c1;
 fetch c1 into v_sales;
 while(c1%found) loop
  dbms_output.put_line(v_sales.sid||'   '||v_sales.icode||'  '||v_sales.Idescrip||'  '||v_sales.pqty||'  '||v_sales.sdate||'  ');
  fetch c1 into v_sales;
 end loop;
 close c1;
end;
5、 创建一个函数ProductOnHand，当给出某产品的Icode时，显示该产品的当前库存数量。
create or replace function ProductOnHand(p_icode varchar2) return number
is
 v_stock stock%rowtype;
begin
 select * into v_stock from stock where icode=p_icode;
 return v_stock.pqty;
exception
 when no_data_found then raise_application_error(-20001,'没有'||p_icode||'对应的库存信息');
 when too_many_rows then raise_application_error(-20001,p_icode||'对应的库存信息太多');
end;
6、 创建一个包ReturnProductType，该对象包括一个过程和一个函数：
create or replace package ReturnProductType
is
 procedure ReturnProduct(p_sid varchar2,p_icode varchar2);
 function ProductInfo(p_icode varchar2) return number;
end;
a) 过程ReturnProduct：当顾客返回某部分产品的退货时，增加该部分产品的库存数量

b) 函数ProductInfo：当给出退货产品代码时，显示该产品的库存信息
create or replace package body ReturnProductType
is
 procedure ReturnProduct(p_sid varchar2,p_icode varchar2)
 is
  v_sales sales%rowtype;
     v_stock stock%rowtype;
  cursor c1 is select * from stock where icode=p_icode for update;
 begin
  --取出销售信息
  select * into v_sales from sales where sid=p_sid and icode=p_icode;
    --取出库库信息
  open c1;
    fetch c1 into v_stock;
  if(c1%found) then
     --库存有记录，增加库存数量
   update stock set pqty=pqty+v_sales.pqty where icode=v_sales.icode;
  else
      --库存中无记录，增加新的记录
   insert into stock(icode,pqty,Idescrip) values(v_sales.icode,v_sales.pqty,v_sales.Idescrip);
  end if;
    --向退贷表中插入数
  insert into return(rid,icode,pqty,Idescrip,rdate)values(v_sales.sid,v_sales.icode,v_sales.pqty,v_sales.Idescrip,sysdate);
    --删除销信息
  delete from sales where sid=p_sid and icode=p_icode;
  close c1;
    commit;
 exception
  when no_data_found then raise_application_error(-20001,'没有'||p_sid||'   '||p_icode||'对应的销售信息');
  when too_many_rows then raise_application_error(-20001,p_sid||'   '||p_icode||'对应的库存信息太多');
 end;
 function ProductInfo(p_icode varchar2) return number
 is
  v_stock stock%rowtype;
 begin
  select * into v_stock from stock where icode=p_icode;
  return v_stock.pqty;
 exception
  when no_data_found then raise_application_error(-20001,'没有'||p_icode||'对应的库存信息');
  when too_many_rows then raise_application_error(-20001,p_icode||'对应的库存信息太多');
 end;
end;