2005/08/02 | 闲来无事写一个任务玩玩，顺便学一下PL/SQL
类别(数据库[原创]) | 评论(0) | 阅读(169) | 发表于 09:41
--
create or replace procedure te5_updatebytime
is
curr_date date;
col_count number;
col_id te5_test.colid%type;
id_temp number;
id_length number;
begin
select sysdate into curr_date from dual;
--select te5_GetColId(curr_date) into col_id from dual;
col_id := te5_GetColId(curr_date);

Dbms_Output.put_line('The inivalue of id is:'||col_id);
select count(*) into id_length from te5_test where colid = col_id;
if id_length = 0 then
insert into te5_test (colid) values(col_id);
end if;


select te5_test.colcount into col_count from te5_test where colid = col_id;
if col_count is null then
col_count := 1;
else
col_count := col_count + 1;
end if;
update te5_test set colcount = col_count ,coltime = curr_date where colid = col_id;
Dbms_Output.put_line('the col''id was changed is:'||col_id||';the current time is:'||to_char(curr_date,'yyyy-mm-dd hh:mi:ss'));
end;

--------------------------------------------------------------------------------------------
--获取要修改的关键字
create or replace function te5_GetColId(curr_time date) return varchar2
is
temp_str varchar2(20);
temp_id varchar2(20);
temp_exception exception;
--curr_time date;
begin
--select sysdate into curr_time from dual;
temp_str := to_char(curr_time,'yyyy-mm-dd hh:mi:ss');
dbms_output.put_line(temp_str);
temp_str := substr(temp_str,15,2);
--select colid into temp_id from te5_test where colid = temp_str;
return temp_str;
end;

--------------------------------------------------------------------------------------------
--创建job
variable update_te5_test_permi number;
begin
--每天1440分钟，即一分钟运行test过程一次
dbms_job.submit(:update_te5_test_permi,'te5_updatebytime;',sysdate,'sysdate+1/1440');
commit;
end;
--print 任务名 输出任务号
--------------------------------------下面是生成job后系统的SQL
begin
sys.dbms_job.change(job => 1,
what => 'te5_updatebytime;',
next_date => to_date('02-08-2005 08:58:48', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/1440');
commit;
end;
--------------------------------------
--删除
dbms_job.remove(jobno);
--------------------------------------------------------------------------------------------

--测试1
begin
    te5_updatebytime;
    commit;
end;
--测试2
declare
a date;
b varchar2(20);
begin
select sysdate into a from dual;
b := te5_GetColId(a);
Dbms_Output.put_line(b);
end;
0

评论Comments

日志分类
首页[148]
小新的博客[原创][70]
我的文档[原创][33]
杂项[5]
企业信息化[9]
数据库[原创][12]
文档转载[17]
配置管理[2]