oracle帮助

最新推荐文章于 2023-03-24 16:29:39 发布

niitsoftware

最新推荐文章于 2023-03-24 16:29:39 发布

阅读量586

点赞数

文章标签： oracle sql javascript insert delete search

本文链接：https://blog.csdn.net/niitsoftware/article/details/1757801

版权

1,创建任务

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"INTWORKS"."NEW_AUTOTIME"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
new_auto;
end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=600',
start_date => systimestamp at time zone '+8:00',
job_class => 'DEFAULT_JOB_CLASS',
comments => '更新索引',
auto_drop => FALSE,
enabled => true);
END;

drop index INTWORKS.NEW_AUTOTIME

sys.dbms_remove('"INTWORKS"."NEW_AUTOTIME"');

select * from user_jobs
begin
sys.dbms_scheduler.drop_job('"INTWORKS"."NEW_AUTOTM"');
end;

2，创建游标

begin
declare
cursor CurkeyField is
select serialno,note from searchcompstat where note is not null;
begin
if not CurkeyField%isopen then open CurkeyField; end if;
Loop
    fetch CurkeyField into vi_serialno,vs_note;
     exit when CurkeyField%notfound;
    begin
      vs_sql:='select count(*) as inum from' ||vs_note;
      vs_sql:=Replace(vs_sql,'#','''');
      vs_sql:='update searchcompstat set tablerows=('select count(*) as inum from') where serialno='||to_char(vi_serialno);
      Execute immediate(vs_sql);
      end;
end Loop;
if CurkeyField%isopen then close CurkeyField; end if;
end;
update searchcompstat set tablerows=(select count(*) as inum from companybase where 1=1 and contains(keyword,'ˮ',1)>0) where serialno=111

3，创建存储过程

create or replace procedure AUTO_STAT is
vi_serialno integer;
vs_sql varchar(700);
vs_note varchar(600);
begin
declare
cursor CurkeyField is
select serialno,note from searchcompstat where note is not null;
begin
if not CurkeyField%isopen then open CurkeyField; end if;
Loop
    fetch CurkeyField into vi_serialno,vs_note;
     exit when CurkeyField%notfound;
    begin
      vs_sql:='select count(*) as inum from ' ||vs_note;
      vs_sql:=Replace(vs_sql,'#','''');
      vs_sql:='update searchcompstat set tablerows=('||vs_sql||') where serialno='||to_char(vi_serialno);
      Execute immediate(vs_sql);
      end;
end Loop;
if CurkeyField%isopen then close CurkeyField; end if;
end;

declare
cursor CurkeyField is
select serialno,note from searchmanagerstat where note is not null;
begin
if not CurkeyField%isopen then open CurkeyField; end if;
Loop
    fetch CurkeyField into vi_serialno,vs_note;
     exit when CurkeyField%notfound;
    begin
      vs_sql:='select count(*) as inum from ' ||vs_note;
      vs_sql:=Replace(vs_sql,'#','''');
      vs_sql:='update searchmanagerstat set tablerows=('||vs_sql||') where serialno='||to_char(vi_serialno);
      Execute immediate(vs_sql);
      end;
end Loop;
if CurkeyField%isopen then close CurkeyField; end if;
end;

declare
cursor CurkeyField is
select serialno,note from searchprostat where note is not null;
begin
if not CurkeyField%isopen then open CurkeyField; end if;
Loop
    fetch CurkeyField into vi_serialno,vs_note;
     exit when CurkeyField%notfound;
    begin
      vs_sql:='select count(*) as inum from ' ||vs_note;
      vs_sql:=Replace(vs_sql,'#','''');
      vs_sql:='update searchprostat set tablerows=('||vs_sql||') where serialno='||to_char(vi_serialno);
      Execute immediate(vs_sql);
      end;
end Loop;
if CurkeyField%isopen then close CurkeyField; end if;
end;

declare
cursor CurkeyField is
select serialno,note from searchprovidestat where note is not null;
begin
if not CurkeyField%isopen then open CurkeyField; end if;
Loop
    fetch CurkeyField into vi_serialno,vs_note;
     exit when CurkeyField%notfound;
    begin
      vs_sql:='select count(*) as inum from ' ||vs_note;
      vs_sql:=Replace(vs_sql,'#','''');
      vs_sql:='update searchprovidestat set tablerows=('||vs_sql||') where serialno='||to_char(vi_serialno);
      Execute immediate(vs_sql);
      end;
end Loop;
if CurkeyField%isopen then close CurkeyField; end if;
end;

declare
cursor CurkeyField is
select serialno,note from searchreqstat where note is not null;
begin
if not CurkeyField%isopen then open CurkeyField; end if;
Loop
    fetch CurkeyField into vi_serialno,vs_note;
     exit when CurkeyField%notfound;
    begin
      vs_sql:='select count(*) as inum from ' ||vs_note;
      vs_sql:=Replace(vs_sql,'#','''');
      vs_sql:='update searchreqstat set tablerows=('||vs_sql||') where serialno='||to_char(vi_serialno);
      Execute immediate(vs_sql);


    end;
end Loop;
if CurkeyField%isopen then close CurkeyField; end if;
end;
end AUTO_STAT;

2
create or replace procedure HOT_AUTO is
begin
delete from hot_research_info where classtype=1;
insert into hot_research_info (serialno,classtype,linenum,keyname,url)
select s_hot_search.nextval,'1',num,keyword,'javascript:search('''||keyword||''');' from (select * from searchreqstat where keyword not like '%=%' order by num desc) where rownum<=5;
delete from hot_research_info where classtype=2;
insert into hot_research_info (serialno,classtype,linenum,keyname,url)
select s_hot_search.nextval,'2',num,keyword,'javascript:search('''||keyword||''');' from (select * from searchprovidestat where keyword not like '%=%' order by num desc) where rownum<=5;
delete from hot_research_info where classtype=3;
insert into hot_research_info (serialno,classtype,linenum,keyname,url)
select s_hot_search.nextval,'3',num,keyword,'javascript:search('''||keyword||''');' from (select * from searchcompstat where keyword not like '%=%' order by num desc) where rownum<=5;
delete from hot_research_info where classtype=4;
insert into hot_research_info (serialno,classtype,linenum,keyname,url)
select s_hot_search.nextval,'4',num,keyword,'javascript:search('''||keyword||''');' from (select * from searchprostat where keyword not like '%=%' order by num desc) where rownum<=5;
delete from hot_research_info where classtype=5;
insert into hot_research_info (serialno,classtype,linenum,keyname,url)
select s_hot_search.nextval,'5',num,keyword,'javascript:search('''||keyword||''');' from (select * from searchmanagerstat where keyword not like '%=%' order by num desc) where rownum<=5;
end HOT_AUTO;