求一oracle存储过程

Question

有三个字段，日期（date）和数量（int），学生（varchar），（每个学生有可能有两个日期，也可能只有一个日期）如：
 2010-03-01，30，lisa
 2011-04-09，50，lisa
我希望实现的是，如果这个学生有两个日期，那么我想用小的日期的数量-10，如果差值>=0,那么小日期的数量等于这个差值，大日期的数量不变。如果差值<0，那么日期小的数量=0，日期大的数量再减去这个差值。如果只有一个日期，就用这个数量-10谢谢了

Answer 1

SQL> create table tt as select * from
  2  (select to_date('2010-03-01','yyyy-mm-dd') tim,25 num,'aa' stu from dual
  3  union all select to_date('2011-04-09','yyyy-mm-dd') tim,39 num,'aa' stu from dual
  4  union all select to_date('2011-05-15','yyyy-mm-dd') tim,73 num,'bb' stu from dual
  5  );
 
Table createdSQL> select * from tt;
 
TIM                NUM STU
----------- ---------- ---
2010-3-1            25 aa
2011-4-9            39 aa
2011-5-15           73 bb
 
SQL> 
SQL> create or replace procedure p is
  2  cursor c is select stu, min(tim) a,max(tim) b,min(num) e,max(num) f from tt group by stu;
  3  begin
  4  for c1 in c loop
  5  if (c1.a  = c1.b) then
  6  update tt set num = c1.e -10 where stu = c1.stu;
  7  elsif (c1.e  - 10) >= 0 then
  8  update tt set num = c1.e  - 10 where tim = c1.a and stu = c1.stu;
  9  else
 10  update tt set num = 0 where tim = c1.a and stu = c1.stu;
 11  update tt set num = c1.f - c1.e  + 10 where tim = c1.b and stu = c1.stu;
 12  end if;
 13  end loop;
 14  commit;
 15  end;
 16  /
 
Procedure created
 
SQL> exec p;
 
PL/SQL procedure successfully completed
 
SQL> select * from tt;
 
TIM                NUM STU
----------- ---------- ---
2010-3-1            15 aa
2011-4-9            39 aa
2011-5-15           63 bb
 
SQL> exec p;
 
PL/SQL procedure successfully completed
 
SQL> select * from tt;
 
TIM                NUM STU
----------- ---------- ---
2010-3-1             5 aa
2011-4-9            39 aa
2011-5-15           53 bb
 
SQL> exec p;
 
PL/SQL procedure successfully completed
 
SQL> select * from tt;
 
TIM                NUM STU
----------- ---------- ---
2010-3-1             0 aa
2011-4-9            44 aa
2011-5-15           43 bb

Answer 2

create procedure pro_test()
is
v_minus number;
brgin--更新只有一个日期的学生的数量
update tab set 数量=数量-10
where 学生 in (select 学生 from tab group by 学生 having count(日期)=1);--更新2个日期的学生并且数量-10>0的记录
update tab  set 数量=数量-10
where (学生,日期) in
  (select 学生,日期 from 
    (select 学生,数量-10 as new数量,日期 from tab
     where (学生,日期) in 
           (select 学生,min(日期) from tab group by 学生 having count(日期)=2))
  where new数量>0);
--更新2个日期的学生并且数量-10<0的记录,日期小的记录为0
update tab  set 数量=0
where (学生,日期) in
  (select 学生,日期 from 
    (select 学生,数量-10 as new数量,日期 from tab
     where (学生,日期) in 
           (select 学生,min(日期) from tab group by 学生 having count(日期)=2))
  where new数量<0);--取得〈0的学生，数量 建立临时表
with temp as 
select 学生,日期,new数量 from 
    (select 学生,数量-10 as new数量,日期 from tab
     where (学生,日期) in 
           (select 学生,min(日期) from tab group by 学生 having count(日期)=2))
  where new数量<0;--更新2个日期的学生并且数量-10<0的记录,日期大的记录为
update tab  set 数量=数量-temp.new数量
      where (学生,日期) in 
      (select 学生,max(日期) from tab  where 学生 in (select 学生 from temp));end pro_test;

Answer 3

create or replace procedure proc
is
begin
   for i in 
  (select rowid,t.datetime,t.numbers, t.sname, 
          sum(1) over(partition by sname) sn, 
          row_number() over(partition by sname order by datetime) rn from t)
   loop
      -- if this student has only one record in the table, then update datetime with datetime - 10
      if i.sn = 1 then
         update t set numbers = numbers - 10 where rowid = t.rowid;
      -- if he/she has more than one record in the table
      else
         -- only operate on record whose date is older
         if i.rn = 1 then
            if i.numbers - 10 >= 0 then
               update t set numbers = numbers - 10 where rowid = t.rowid;
            else
               update t set numbers = 0 where rowid = t.rowid;
               update t set numbers = numbers - 10 where sname = i.sname and rowid <> i.rowid;
            end if;
         end if;
      end if;
   end loop;
   commit;
exception
   when others then raise;
end proc;
/

Answer 4

create or replace procedure pro_update()
is 
 Type emp_table_type is table Of emp%rowtype INDEX BY BIANRY_INTEGER;
 emp_table emp_table_type,emp_0 ;
 cursor cur_emp is select * from emp order by 日期 asc;
begin
 
 for rec in cur_emp
   select * bulk collect into emp_table from  emp where 学生=rec.学生;
   
   --只有一条数据
   if emp_table.limit = 1 then
      update emp set 数量=数量-10 where 学生=rec.学生;
   end if; 
   --2条数据
   if emp_table.limit = 2 then
      if emp_table(1).数量 >= 10 then 
         update emp set 数量=数量-10 where 学生=rec.学生 and 日期= emp_table(1).日期;
      else
         update emp set 0 where 学生=rec.学生 and 日期= emp_table(1).日期;
         update emp set 数量=数量 -(emp_table(1).数量-10） where 学生=rec.学生 and 日期= emp_table(2).日期;
      end if;   
   
   end if;   
  
 end loop;end procedure;我的思路大抵是这样的，看对您是否有参考意义。

Answer 5

create or replace procedure pro_update()
is 
 Type emp_table_type is table Of emp%rowtype INDEX BY BIANRY_INTEGER;
 emp_table emp_table_type,emp_0 ;
 cursor cur_emp is select * from emp order by 日期 asc;
begin
 
 for rec in cur_emp
   select * bulk collect into emp_table from  emp where 学生=rec.学生;
   
   --只有一条数据
   if emp_table.limit = 1 then
      update emp set 数量=数量-10 where 学生=rec.学生;
   end if; 
   --2条数据
   if emp_table.limit = 2 then
      if emp_table(1).数量 >= 10 then 
         update emp set 数量=数量-10 where 学生=rec.学生 and 日期= emp_table(1).日期;
      else
         update emp set 0 where 学生=rec.学生 and 日期= emp_table(1).日期;
         update emp set 数量=数量 -(emp_table(1).数量-10） where 学生=rec.学生 and 日期= emp_table(2).日期;
      end if;   
   
   end if;   
  
 end loop;end procedure;我的思路大抵是这样的，看对您是否有参考意义。

Answer 6

这csdn 提示失败了，还给发出来2条？！！！！！！！！！！！！！！！

Answer 7

create table testTable
(
      name varchar2(10),
      num number(8),
      enterDate date
);insert into testTable(Name,Num,Enterdate) values('a',25,to_date('20100401','YYYYMMDD') );insert into testTable(Name,Num,Enterdate) values('a',5,to_date('20100301','YYYYMMDD') );
insert into testTable(Name,Num,Enterdate) values('a',10,to_date('20100101','YYYYMMDD') );
insert into testTable(Name,Num,Enterdate) values('b',1,to_date('20100201','YYYYMMDD') );
insert into testTable(Name,Num,Enterdate) values('b',30,to_date('20100301','YYYYMMDD') );
insert into testTable(Name,Num,Enterdate) values('c',12,to_date('20100201','YYYYMMDD') );
create or replace procedure Demo as
  v_count number(8);
begin
  for cur in (select t.rowid, t.*
                from testTable t,
                     (select a.name, min(a.enterDate) date1
                        from testTable a
                       group by a.name) b
               where t.name = b.name
                 and t.enterDate = b.date1) loop
    if (cur.num - 10 >= 0) then
      update testTable t set t.num = t.num - 10 where t.rowid = cur.rowid;
    else
      select count(t.enterDate)
        into v_count
        from testTable t
       where t.name = cur.name;
      if (v_count > 0) then
        update testTable t set t.num = 0 where t.rowid = cur.rowid;
        update testTable t
           set t.num = t.num - (cur.num - 10)
         where t.name = cur.name
           and t.enterDate = (select max(a.enterDate)
                                from testTable a
                               where a.name = t.name);
      else
        update testTable t
           set t.num = cur.num - 10 - 10
         where t.rowid = cur.rowid;
      end if;
    end if;
  end loop;
  commit;
  return;
end;

Answer 8

本帖最后由 wildwave 于 2010-03-28 23:20:51 编辑

  

Answer 9

declare v_date date; begin select min(ruxue_date) into :v_date from test where student='A01' ;
update test set qty= qty-10 where student='A01' and ='690001'and ruxue_date=v_date;
end;
老是提示“并非所有变量都绑定”什么意思呢，有什么错误

Answer 10

declare v_date date; begin select min(ruxue_date) into :v_date from test where student='A01' ;
update test set qty= qty-10 where student='A01' and ruxue_date=v_date;
end;
上边的条件写错了