高分求job,修改更新sql语句写出job.

Question

大家新年好！！！新的一年工作开始了。。我这边也有新任务了。。还恳请大家帮忙。
谢谢大家去年的指导。写的update语句在系统使用中发挥了很大的作用。
现在老大让我把sql语句写出job脚本执行。
脚本是什么样都不清楚请问怎么写。。谢谢大家了。--修改操作：A品牌,经销价改变后，价格表中的最高/最低价修改
update sc_tt_price price set (price.max_price, price.min_price) = 
                        (select pi.sale_price*ptd.max_make_up_rate/100, ptd.min_make_up_rate*pi.sale_price/100
                          from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_tmp_dtl ptd
                          where (price.max_price <>ptd.max_make_up_rate*pi.sale_price/100 or price.min_price<>ptd.min_make_up_rate*pi.sale_price/100)
                          and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
                          and p.sc_tt_price_template_id = ptd.sc_tt_price_template_id
                          and pi.bland = '1'
                          and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
                          and pi.grade = ptd.product_level           --档次
                          and pi.uni_sale_price_flg= 0--非统一零售价商品
                          and p.status in (1 ,2) --已生成1或者已发布2
                          and price.sc_tt_price_id = p.sc_tt_price_id
                        )   
where exists (select 1  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_tmp_dtl ptd
                        where (p.max_price <>ptd.max_make_up_rate*pi.sale_price/100 or p.min_price<>ptd.min_make_up_rate*pi.sale_price/100)
                        and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
                        and p.sc_tt_price_template_id = ptd.sc_tt_price_template_id
                        and pi.bland = '1'
                        and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
                        and pi.grade = ptd.product_level           --档次
                        and pi.uni_sale_price_flg= 0--非统一零售价商品
                        and p.status in (1 ,2) --已生成或者已发布
                        and price.sc_tt_price_id = p.sc_tt_price_id
             )  

Answer 1

一个相关的重要参数,可同时运行的job上限值
SQL> show parameter job_queue_processes;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10
 
SQL> 
1.dbms_job.submit 创建job
SQL> create or replace procedure resch as
  2  begin
  3  delete from emp where empno = 2;
  4  COMMIT;
  5  delete from emp where empno = 3;
  6  COMMIT;
  7  delete from emp where empno = 4;
  8  COMMIT;
  9  end;
 10  /
 
Procedure created
 
SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2  v_job NUMBER(20);
  3  BEGIN
  4  dbms_job.submit(v_job,'begin resch; end;',SYSDATE,'SYSDATE+1/24/60');
  5  dbms_output.put_line(v_job);
  6  END;
  7  /
 
70
 
PL/SQL procedure successfully completed
--注:next_date参数决定下次运行时间.下次运行前会调用interval参数计算再下次的运行时间.
SQL> select * from dba_jobs t where t.JOB=70;
 
       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER                    LAST_DATE   LAST_SEC         THIS_DATE   THIS_SEC         NEXT_DATE   NEXT_SEC         TOTAL_TIME BROKEN INTERVAL                                                                           FAILURES WHAT                                                                             NLS_ENV                                                                          MISC_ENV                                                           INSTANCE
---------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------- ----------- ---------------- ----------- ---------------- ---------- ------ -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------
        87 SCOTT                          SCOTT                          SCOTT                                                                                    2010-8-25 1 17:09:25                  0 N      SYSDATE+1/24/60                                                                             begin resch; end;                                                                NLS_LANGUAGE='SIMPLIFIED CHINESE' NLS_TERRITORY='CHINA' NLS_CURRENCY='￥' NLS_ISO 0102000200000000                                                          0
 
SQL>
2.dbms_job.broken 停止job或唤醒jobSQL> exec dbms_job.broken(job => 87,broken => true,next_date => sysdate);--停止job
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL> exec dbms_job.broken(job => 87,broken => false,next_date => sysdate);--唤醒job
 
PL/SQL procedure successfully completed
 
SQL> commit
  2  ;
 
Commit complete
 
SQL>  
3.dbms_job.remove 删除job
SQL> exec dbms_job.remove(job => 87);--删除job
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL> select * from dba_jobs t where t.JOB=87;--已无数据
 
       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER                    LAST_DATE   LAST_SEC         THIS_DATE   THIS_SEC         NEXT_DATE   NEXT_SEC         TOTAL_TIME BROKEN INTERVAL                                                                           FAILURES WHAT                                                                             NLS_ENV                                                                          MISC_ENV                                                           INSTANCE
---------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------- ----------- ---------------- ----------- ---------------- ---------- ------ -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------
 
SQL> 

Answer 2

高手啊！！！谢谢楼上的。。可惜看不懂。。
我上面这个update语句，老大要让他每天晚上零点定时执行。要我写一个脚本
没有脚本，只能自己手动去执行update语句。

Answer 3

--建立一个存储过程 ,来执行你的update操作
create or replace procedure p_jobtest
is
begin
  --修改操作：A品牌,经销价改变后，价格表中的最高/最低价修改
  update sc_tt_price price set (price.max_price, price.min_price) = 
                        (select pi.sale_price*ptd.max_make_up_rate/100, ptd.min_make_up_rate*pi.sale_price/100
                          from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_tmp_dtl ptd
                          where (price.max_price <>ptd.max_make_up_rate*pi.sale_price/100 or price.min_price<>ptd.min_make_up_rate*pi.sale_price/100)
                          and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
                          and p.sc_tt_price_template_id = ptd.sc_tt_price_template_id
                          and pi.bland = '1'
                          and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
                          and pi.grade = ptd.product_level           --档次
                          and pi.uni_sale_price_flg= 0--非统一零售价商品
                          and p.status in (1 ,2) --已生成1或者已发布2
                          and price.sc_tt_price_id = p.sc_tt_price_id
                        )   
  where exists (select 1  from sc_tt_price p, sc_tm_product_info pi, sc_tt_price_tmp_dtl ptd
                        where (p.max_price <>ptd.max_make_up_rate*pi.sale_price/100 or p.min_price<>ptd.min_make_up_rate*pi.sale_price/100)
                        and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
                        and p.sc_tt_price_template_id = ptd.sc_tt_price_template_id
                        and pi.bland = '1'
                        and pi.sc_tt_bak3 = ptd.dimesion_2         --中类
                        and pi.grade = ptd.product_level           --档次
                        and pi.uni_sale_price_flg= 0--非统一零售价商品
                        and p.status in (1 ,2) --已生成或者已发布
                        and price.sc_tt_price_id = p.sc_tt_price_id
             )  ;
  commit;
end;
/--提交一个JOB ,每天晚上零点定时执行
declare
  v_jobno number;
begin
  sys.dbms_job.submit(job => v_jobno,
  what => ' begin p_jobtest; end;',
  next_date => to_date('2011-02-11 00:00:00','yyyy-mm-dd hh24:mi:ss'),
  interval => 'trunc(sysdate+1)') ;
end;
/ 

Answer 4

谢谢潘哥啊～～～
潘哥还有印象么，这条update语句就是在你的指导下写好的。尤其是后面“where exists...”语句。尤为重要。
sql语句写好后，再写存储过程是不是很简单了。sql语句都不用修改了？
还有就是这个存储过程，job，直接在“SQL window"下执行即可么？

Answer 5

将update写到procedure中只是为了job好调用，你也可以用匿名块的形式来执行what参数，不过这样比较难看，代码的分层次结构不好管理。
你运行我上面的就行了，要查看相关JOB信息，可参考minitoy给你的方法，你可以多了解下job相关资料

Answer 6

把你的sql封装到一个过程里，通过pl/sql创建一个job，按时调那个过程即可。定时的方式可以到网上搜搜

Answer 7

怎么修改脚本呢？我要修改时间
next_date => to_date('2011-02-11 16:00:00','yyyy-mm-dd hh24:mi:ss'),
把零点改成下午4点。
还有，怎么删除脚本呢？

Answer 8

修改 interval 参数就行了：interval => 'trunc(sysdate+1)+2/3')

Answer 9

DBMS_JOB里面的JOB已经落伍了,现在都使用dbms_scheduler包提供的job
和dbms_job相比,dbms_scheduler的job更加方便,比如你可以指定每天/月/星期/年的固定时间,
比如00:00:00时刻执行,而且提供了job运行的详细地历史纪录
使用plsql developer 8.0以上版本,可以很方便的添加job

Answer 10

谢谢潘哥，
还有我提交的job怎么在看不到呢？在pl/sql developer菜单栏的右侧的jobs中，没有任何脚本。

Answer 11

select * from user_jobs;
看看呢

Answer 12

谢谢潘哥，刚定义了一个job，15：40执行了。看了下执行结果。已经Update数据了。。
现在就到定义到生产库中。

Answer 13

就是，如果要删除job,怎么删除呢？
我在plsql develope中使用select * from user_jobs for update;
提交时，显示“权限不足”

Answer 14

删除job用remove命令：
如：假设你的v_jobno是121
exec dbms_job.remove(121);

Answer 15

我在sql window下输入exec dbms_job.remove(22);
报错，无效语句

Answer 16

看来你还需要加深啊啊！ 呵呵
exec 是在command命令窗口或者SQLPLUS窗口执行的你换成这样试试：
begin 
dbms_job.remove(22);
end;

Answer 17

谢谢大家了。。是需要加深。
现在是oracle开发了。。

Answer 18

测试库的job都删除掉了。谢谢潘哥～～