求一oracle存储过程
有三个字段，日期（date）和数量（int），学生（varchar），（每个学生有可能有两个日期，也可能只有一个日期）如：
2010-03-01，30，lisa
2011-04-09，50，lisa
我希望实现的是，如果这个学生有两个日期，那么我想用小的日期的数量-10，如果差值>=0,那么小日期的数量等于这个差值，大日期的数量不变。如果差值<0，那么日期小的数量=0，日期大的数量再减去这个差值。如果只有一个日期，就用这个数量-10
谢谢了
[解决办法]
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
[解决办法]
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 created
SQL> 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
[解决办法]
- SQL code
create procedure pro_test()isv_minus number;brgin--更新只有一个日期的学生的数量update tab set 数量=数量-10where 学生 in (select 学生 from tab group by 学生 having count(日期)=1);--更新2个日期的学生并且数量-10>0的记录update tab set 数量=数量-10where (学生,日期) 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的记录,日期小的记录为0update tab set 数量=0where (学生,日期) 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;
[解决办法]
- SQL code
create or replace procedure procisbegin 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;/
[解决办法]
- SQL code
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;
[解决办法]
- SQL code
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;
[解决办法]
这csdn 提示失败了，还给发出来2条？！！！！！！！！！！！！！！！
[解决办法]
- SQL code
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;
[解决办法]
- SQL code
with tt as( select date'2010-3-1' dt,30 num,'lisa' name from dual union all select date'2011-4-9',50,'lisa' from dual union all select date'2010-1-1',20,'lucy' from dual)--测试数据select name, sum(num) num_total from (select dt, name, case when exists (select 1 from tt where name = t1.name and dt > t1.dt) then - decode(sign(num - 10), -1, 0, num - 10) when exists (select 1 from tt where name = t1.name and dt < t1.dt) then num else num - 10 end num from tt t1) group by name
[解决办法]
[解决办法]
这是什么需求啊。。。。。。