create or replace procedure XSJK -- 创建存储过程XSJK is vcount1 integer; vcount11 integer; vcount2 integer; vcount21 integer; cursor cur is select distinct posno,flowno from lsbuy1@hdmall where tag = 0 and realamt <> 0;begin -- tag,0未处理,99已处理,1异常 update lsbuy1@hdmall set tag = 0 where tag not in (0,99); update lsbuy1@hdmall set tag = 99 where tag = 0 and realamt = 0; update lsbuy1@hdmall set tag = 99 where tag = 0 and (posno,flowno) in(select posno,flowno from lsbuy1bck); update lsbuy1@hdmall set tag = 99 where tag = 0 and (posno,flowno) in(select posno,flowno from lsbuy1); update lsbuy1@hdmall set tag = 1 where (posno,flowno) not in(select posno,flowno from lsbuy2@hdmall); update lsbuy1@hdmall set tag = 1 where (posno,flowno) not in(select posno,flowno from lsbuy11@hdmall); commit; -- 处理lsbuy1的busdate和backdate update lsbuy1@hdmall set busdate = trunc(fildate), backdate = trunc(fildate) where tag = 0; -- 处理lsbuy2的lesbt update lsbuy2@hdmall set lesbt = '-' where lesbt = '' or lesbt is null; commit; for cur1 in cur loop insert into lsbuy1(POSNO,FLOWNO,CASHIER,FILDATE,STDTOTAL,SCRTOTAL,REALAMT,RECCNT,MEMO,TAG,DEALER, BUSDATE,OCRDATE,CARDNO,SCORE,CARRIER,BACKDATE) select distinct * from lsbuy1@hdmall where (posno,flowno) not in(select posno,flowno from lsbuy1) and posno=cur1.posno and flowno=cur1.flowno; insert into lsbuy11(POSNO,FLOWNO,ITEMNO,CURRENCY,AMOUNT,DISCHANGE,CARDCODE,BANKCODE,CARDTYPE) select distinct * from lsbuy11@hdmall where posno=cur1.posno and flowno=cur1.flowno; insert into lsbuy2(POSNO,FLOWNO,ITEMNO,LES,GROUND,LESPT,LESBT,GID,IQTY,QTY,RTLPRC,STDTOTAL, SCRPRICE,SCRTOTAL,FAVAMT,REALAMT,ASSISTANT,TAG,INVNO, DEALER,GDCODE,LESNUM,SALEVOUCHER,SCORE) select distinct * from lsbuy2@hdmall where posno=cur1.posno and flowno=cur1.flowno; insert into lsbuy21(POSNO,FLOWNO,ITEMNO,FAVTYPE,FAVAMT,PROMNUM,PROMCLS,PROMLINE,PROMID) select distinct * from lsbuy21@hdmall where posno=cur1.posno and flowno=cur1.flowno; select count(1) into vcount1 from lsbuy1 where posno=cur1.posno and flowno=cur1.flowno; select count(1) into vcount11 from lsbuy11 where posno=cur1.posno and flowno=cur1.flowno; select count(1) into vcount2 from lsbuy2 where posno=cur1.posno and flowno=cur1.flowno; select count(1) into vcount21 from lsbuy21 where posno=cur1.posno and flowno=cur1.flowno; if vcount1*vcount11*vcount2 = 0 then rollback; else update lsbuy1@hdmall set tag=99 where posno=cur1.posno and flowno=cur1.flowno; commit; end if; end loop; end ;找不出哪里有错误了
1.创建表 create table TEST ( CARNO VARCHAR2(30), CARINFOID NUMBER )2.创建存储过程 create or replace procedure pro_test AS carinfo_id number; BEGIN select s_CarInfoID.nextval into carinfo_id from dual; insert into test(test.carno,test.carinfoid) values(carinfo_id,'123'); commit; end pro_test;3.在SQL>下执行以下代码,启动任务;每分钟执行一次。一天1440分钟。 每天午夜12点 'TRUNC(SYSDATE + 1)' 每天早上8点30分 'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)' 每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24' 每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)' 每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24' 每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6*60+10)/(24*60)'VARIABLE jobno number; begin DBMS_JOB.SUBMIT(:jobno, 'pro_test;', SYSDATE,'sysdate+1/1440'); commit; end; /4.查看当前运行的任务 select job,next_date,next_sec,failures,broken from user_jobs;select * from user_jobs; 5.停止job号为1的任务 begin dbms_job.remove(1); commit; end; /
有错误或者不懂再发上来看看..
网上找了一个例子给你看看吧:http://www.360doc.com/content/10/0519/00/1121193_28313920.shtml
可以看到各函数说明;
is
vcount1 integer;
vcount11 integer;
vcount2 integer;
vcount21 integer;
cursor cur is
select distinct posno,flowno from lsbuy1@hdmall where tag = 0 and realamt <> 0;begin
-- tag,0未处理,99已处理,1异常
update lsbuy1@hdmall set tag = 0 where tag not in (0,99);
update lsbuy1@hdmall set tag = 99 where tag = 0 and realamt = 0;
update lsbuy1@hdmall set tag = 99 where tag = 0 and (posno,flowno) in(select posno,flowno from lsbuy1bck);
update lsbuy1@hdmall set tag = 99 where tag = 0 and (posno,flowno) in(select posno,flowno from lsbuy1);
update lsbuy1@hdmall set tag = 1 where (posno,flowno) not in(select posno,flowno from lsbuy2@hdmall);
update lsbuy1@hdmall set tag = 1 where (posno,flowno) not in(select posno,flowno from lsbuy11@hdmall);
commit; -- 处理lsbuy1的busdate和backdate
update lsbuy1@hdmall set busdate = trunc(fildate), backdate = trunc(fildate) where tag = 0;
-- 处理lsbuy2的lesbt
update lsbuy2@hdmall set lesbt = '-' where lesbt = '' or lesbt is null;
commit;
for cur1 in cur loop
insert into lsbuy1(POSNO,FLOWNO,CASHIER,FILDATE,STDTOTAL,SCRTOTAL,REALAMT,RECCNT,MEMO,TAG,DEALER,
BUSDATE,OCRDATE,CARDNO,SCORE,CARRIER,BACKDATE)
select distinct * from lsbuy1@hdmall
where (posno,flowno) not in(select posno,flowno from lsbuy1)
and posno=cur1.posno and flowno=cur1.flowno;
insert into lsbuy11(POSNO,FLOWNO,ITEMNO,CURRENCY,AMOUNT,DISCHANGE,CARDCODE,BANKCODE,CARDTYPE)
select distinct * from lsbuy11@hdmall where posno=cur1.posno and flowno=cur1.flowno;
insert into lsbuy2(POSNO,FLOWNO,ITEMNO,LES,GROUND,LESPT,LESBT,GID,IQTY,QTY,RTLPRC,STDTOTAL,
SCRPRICE,SCRTOTAL,FAVAMT,REALAMT,ASSISTANT,TAG,INVNO,
DEALER,GDCODE,LESNUM,SALEVOUCHER,SCORE)
select distinct * from lsbuy2@hdmall where posno=cur1.posno and flowno=cur1.flowno;
insert into lsbuy21(POSNO,FLOWNO,ITEMNO,FAVTYPE,FAVAMT,PROMNUM,PROMCLS,PROMLINE,PROMID)
select distinct * from lsbuy21@hdmall where posno=cur1.posno and flowno=cur1.flowno;
select count(1) into vcount1 from lsbuy1 where posno=cur1.posno and flowno=cur1.flowno;
select count(1) into vcount11 from lsbuy11 where posno=cur1.posno and flowno=cur1.flowno;
select count(1) into vcount2 from lsbuy2 where posno=cur1.posno and flowno=cur1.flowno;
select count(1) into vcount21 from lsbuy21 where posno=cur1.posno and flowno=cur1.flowno;
if vcount1*vcount11*vcount2 = 0 then
rollback;
else
update lsbuy1@hdmall set tag=99 where posno=cur1.posno and flowno=cur1.flowno;
commit;
end if;
end loop;
end ;找不出哪里有错误了
create table TEST
(
CARNO VARCHAR2(30),
CARINFOID NUMBER
)2.创建存储过程
create or replace procedure pro_test
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id
from dual;
insert into test(test.carno,test.carinfoid) values(carinfo_id,'123');
commit;
end pro_test;3.在SQL>下执行以下代码,启动任务;每分钟执行一次。一天1440分钟。
每天午夜12点 'TRUNC(SYSDATE + 1)'
每天早上8点30分 'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)'
每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6*60+10)/(24*60)'VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'pro_test;',
SYSDATE,'sysdate+1/1440');
commit;
end;
/4.查看当前运行的任务
select job,next_date,next_sec,failures,broken from user_jobs;select * from user_jobs;
5.停止job号为1的任务
begin
dbms_job.remove(1);
commit;
end;
/