我请人做的一个package的功能是网数据库中插入数据时,第一条记录插入p_date字段的内容(用的是pk_myprocess.get_p_date)是一个初始时间的10点,第二条记录是这个时间的16点,第三条是下一天的10点,第四条是下一天的16点,第五条是第3天的10点,第六条是第3天的16点,依次类推,我请人给写的如果是在一次打开数据库时能正常执行,但是如果关闭后,重新打开就又从第一天的10点开始执行,请大狭们帮改改,如何能让他再次打开数据后,pk_myprocess.get_p_date的值仍得到下一个值,而不是重新开始.
代码如下.
SQL> drop package pk_myprocess;Package droppedSQL> drop package body pk_myprocess;drop package body pk_myprocessORA-04043: object PK_MYPROCESS does not existSQL> create or replace package pk_myprocess
2 is
3 function get_p_date return date;
4 function rollback_date return date;
5 end pk_myprocess;
6 /Package createdSQL> create or replace package body pk_myprocess
2 is
3 last_date date;
4 function get_p_date return date
5 is
6 v_pdate date;
7 begin
8 last_date := nvl(last_date,trunc(sysdate));
9 if last_date = to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss') then
10 v_pdate := to_date(to_char(last_date,'yyyy-mm-dd')||' 16:00:00','yyyy-mm-dd hh24:mi:ss');
11 else
12 if last_date < to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss') then
13 v_pdate := to_date(to_char(last_date ,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
14 else
15 v_pdate := to_date(to_char(last_date + 1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
16 end if;
17 end if;
18 last_date := v_pdate;
19 return v_pdate;
20 end get_p_date;
21 function rollback_date return date
22 is
23 v_pdate date;
24 begin
25 last_date := nvl(last_date,sysdate - 1);
26 if last_date = to_date(to_char(last_date,'yyyy-mm-dd')||' 16:00:00','yyyy-mm-dd hh24:mi:ss') then
27 v_pdate := to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
28 else
29 v_pdate := to_date(to_char(last_date - 1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
30 end if;
31 last_date := v_pdate;
32 return v_pdate;
33 end rollback_date;
34
35 end pk_myprocess;
36 /Package body createdSQL> drop table test_a;Table droppedSQL> create table test_a(id number,edit_date date,p_date date);Table createdSQL> insert into test_a values(1,sysdate,pk_myprocess.get_p_date);1 row insertedSQL> commit;Commit completeSQL> insert into test_a values(2,sysdate,pk_myprocess.get_p_date);1 row insertedSQL> insert into test_a values(3,sysdate,pk_myprocess.get_p_date);1 row insertedSQL> select id,to_char(edit_date,'yyyy-mm-dd hh24:mi:ss') as edit_date,
2 to_char(p_date,'yyyy-mm-dd hh24:mi:ss') as p_date from test_a; ID EDIT_DATE P_DATE
---------- ------------------- -------------------
1 2007-04-27 17:31:19 2007-04-27 10:00:00
2 2007-04-27 17:31:20 2007-04-27 16:00:00
3 2007-04-27 17:31:20 2007-04-28 10:00:00SQL>
代码如下.
SQL> drop package pk_myprocess;Package droppedSQL> drop package body pk_myprocess;drop package body pk_myprocessORA-04043: object PK_MYPROCESS does not existSQL> create or replace package pk_myprocess
2 is
3 function get_p_date return date;
4 function rollback_date return date;
5 end pk_myprocess;
6 /Package createdSQL> create or replace package body pk_myprocess
2 is
3 last_date date;
4 function get_p_date return date
5 is
6 v_pdate date;
7 begin
8 last_date := nvl(last_date,trunc(sysdate));
9 if last_date = to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss') then
10 v_pdate := to_date(to_char(last_date,'yyyy-mm-dd')||' 16:00:00','yyyy-mm-dd hh24:mi:ss');
11 else
12 if last_date < to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss') then
13 v_pdate := to_date(to_char(last_date ,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
14 else
15 v_pdate := to_date(to_char(last_date + 1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
16 end if;
17 end if;
18 last_date := v_pdate;
19 return v_pdate;
20 end get_p_date;
21 function rollback_date return date
22 is
23 v_pdate date;
24 begin
25 last_date := nvl(last_date,sysdate - 1);
26 if last_date = to_date(to_char(last_date,'yyyy-mm-dd')||' 16:00:00','yyyy-mm-dd hh24:mi:ss') then
27 v_pdate := to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
28 else
29 v_pdate := to_date(to_char(last_date - 1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
30 end if;
31 last_date := v_pdate;
32 return v_pdate;
33 end rollback_date;
34
35 end pk_myprocess;
36 /Package body createdSQL> drop table test_a;Table droppedSQL> create table test_a(id number,edit_date date,p_date date);Table createdSQL> insert into test_a values(1,sysdate,pk_myprocess.get_p_date);1 row insertedSQL> commit;Commit completeSQL> insert into test_a values(2,sysdate,pk_myprocess.get_p_date);1 row insertedSQL> insert into test_a values(3,sysdate,pk_myprocess.get_p_date);1 row insertedSQL> select id,to_char(edit_date,'yyyy-mm-dd hh24:mi:ss') as edit_date,
2 to_char(p_date,'yyyy-mm-dd hh24:mi:ss') as p_date from test_a; ID EDIT_DATE P_DATE
---------- ------------------- -------------------
1 2007-04-27 17:31:19 2007-04-27 10:00:00
2 2007-04-27 17:31:20 2007-04-27 16:00:00
3 2007-04-27 17:31:20 2007-04-28 10:00:00SQL>
解决方案 »
- session 修改日期格式不生效
- 怎么查询一串ID中,哪些是错误的?
- 求一条oracle的sql语句
- 比较难的一个统计
- 安装oracle之后,找不到tg4msql这个目录,是因为少安装了哪个项目?
- win2003下装9I,装好后,自己建库前要做那些工作?那里有这方面的资料啊
- 如何从分组纪录中找到每个组里面最小的记录数?
- 请问如何在一个数据库的存储过程中调用另一个数据库的存储过程?
- 想知道toad的序列号?
- 初学WebLogic,出现一个问题,求大神解答
- Could we alter the database to an 'backup status' and then copy the datafiles ,logfiles and controlfiles by OS copy?
- 索引调优问题
BEGIN
DECLARE
BEGIN
SELECT MAX(p_date) INTO last_date FROM test_a;
EXCEPTION
WHEN NO_DATA_FOUND THEN
last_date := TRUNC(SYSDATE);
END;
create or replace package body pk_myprocess
is
last_date date; function get_p_date return date
is
v_pdate date;
begin
last_date := nvl(last_date,trunc(sysdate));
if last_date = to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss') then
v_pdate := to_date(to_char(last_date,'yyyy-mm-dd')||' 16:00:00','yyyy-mm-dd hh24:mi:ss');
else
if last_date < to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss') then
v_pdate := to_date(to_char(last_date ,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
else
v_pdate := to_date(to_char(last_date + 1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
end if;
last_date := v_pdate;
return v_pdate;
end get_p_date; function rollback_date return date
is
v_pdate date;
begin
last_date := nvl(last_date,sysdate - 1);
if last_date = to_date(to_char(last_date,'yyyy-mm-dd')||' 16:00:00','yyyy-mm-dd hh24:mi:ss') then
v_pdate := to_date(to_char(last_date,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
else
v_pdate := to_date(to_char(last_date - 1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
last_date := v_pdate;
return v_pdate;
end rollback_date;/******************Add By DragonBill****************************/
BEGIN
DECLARE
BEGIN
SELECT MAX(p_date) INTO last_date FROM test_a;
EXCEPTION
WHEN NO_DATA_FOUND THEN
last_date := TRUNC(SYSDATE);
END;
/******************Add By DragonBill***************************/
end pk_myprocess;
/
if last_date is null then
BEGIN
SELECT MAX(p_date) INTO last_date FROM test_a;
EXCEPTION
WHEN NO_DATA_FOUND THEN
last_date := TRUNC(SYSDATE);
END;
end if;
从而实现如果是重起后就从表里取最大的时间作为上一个时间的功能