drop table UserAccount;create table UserAccount
(
id Number,
value number,
Account_time Date
);create or replace procedure MoneyMaintain
(
Accountid in number
)
is
money_temp Number;
money_time Date;
begin
select max(Account_time) into money_time
from useraccount where id=Accountid;
select value into money_temp
from UserAccount where Account_time=money_time and id=Accountid;
insert into UserAccount(id, value, Account_time)
values(Accountid,
money_temp-0.05*(sysdate-money_time)*24*60,
sysdate);
end MoneyMaintain;
(
id Number,
value number,
Account_time Date
);create or replace procedure MoneyMaintain
(
Accountid in number
)
is
money_temp Number;
money_time Date;
begin
select max(Account_time) into money_time
from useraccount where id=Accountid;
select value into money_temp
from UserAccount where Account_time=money_time and id=Accountid;
insert into UserAccount(id, value, Account_time)
values(Accountid,
money_temp-0.05*(sysdate-money_time)*24*60,
sysdate);
end MoneyMaintain;
表名为UserAccount
每列的数据类型分别为(与下行对应) Number、 number、 Date 他分别有3列 id 、 value 、 Account_time
(
id Number, 数字型的字段 id
value number, 数字型的字段 value
Account_time Date 日期型字段 Account_time
);
create or replace procedure MoneyMaintain 建立或者如果已有更新一个存储过程MoneyMaintain
(
Accountid in number 这个存储过程有一个数字型参数Accountid
)
is
money_temp Number; 声明数字型变量 money_temp
money_time Date; 声明日期型变量 money_time
begin 存储过程程序开始
select max(Account_time) into money_time select查询语句取最大的日期字段值并赋给变量
from useraccount where id=Accountid; 来自表seraccount条件是id等于参数Accountid的值
select value into money_temp select查询value字段值到变量money_temp
from UserAccount where Account_time=money_time and id=Accountid;
来自表UserAccount条件是Account_time=money_time并且id=Accountid
insert into UserAccount(id, value, Account_time) 插入表UserAccount,字段id/value/ Account_timevalues(Accountid, 值来自Accountid
money_temp-0.05*(sysdate-money_time)*24*60, 和money_temp-0.05*(系统日期-money_temp)
*24*60
sysdate); 和系统日期
end MoneyMaintain; 存储过程MoneyMaintain结束。