【笔记】数据库中简易JOB的创建使用

上一篇 / 下一篇  2014-01-16 09:57:33 / 个人分类：数据库

查看( 1762 ) / 评论( 0 ) / 评分( 0 / 0 )
首先将网上查到的一个简易的JOB过程放上去，额，找不到原帖了，所以请作者原谅没有贴转载说明。
--1、创建一张表wl_test
create table wl_TEST
(
   ID NUMBER(12),
   C_DATE DATE
)
 
--2、创建一个sequence
create sequence wl_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 141
increment by 1
cache 20;
 
--3、创建一个存储过程
create or replace procedure wl_prc_g_test is
begin
  insert into wl_test values (wl_seq.nextval, sysdate);
end wl_prc_g_test;
 
--4、创建job
declare
  job number;
begin
  sys.dbms_job.submit(job, 'wl_prc_g_test;', sysdate, 'sysdate+1/1440');
end;
 
--5、运行JOB
begin
dbms_job.run(22);
end;
select * from dba_jobs_running;
select * from wl_TEST;
 
--6、删除JOB
begin
  dbms_job.remove(22); --:job可以用dba_jobs.job的值代替如：1198
end;
以上是一个简单的JOB ，每一分钟向wl_TEST表里面插入序列号及系统时间。
 
接下来对其过程进行相应分析说明
一、关于Sequence
sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。
1、 create sequence

你首先要有create sequence或者create any sequence权限，

create sequence emp_sequence

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXVALUE -- 不设置最大值

NOCYCLE -- 一直累加，不循环

CACHE 10;

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL

CURRVAL=返回sequence的当前值

NEXTVAL=增加sequence的值，然后返回sequence值

比如：

emp_sequence.CURRVAL

emp_sequence.NEXTVAL

可以使用sequence的地方：

- 不包含子查询、snapshot、VIEW的 SELECT 语句

- INSERT语句的子查询中

- NSERT语句的VALUES中

- UPDATE 的 SET中

可以看如下例子：

INSERT INTO emp VALUES

(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SELECT empseq.currval FROM DUAL;

但是要注意的是：

- 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，

然后返回增加后的值。CURRVAL 总是返回当前sequence的值，但是在第一次NEXTVAL

初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次sequence的值，

所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？

- 如果指定CACHE值，oracle就可以预先在内存里面放置一些sequence，这样存取的快

些。

cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如

数据库突然不正常down掉（shutdownabort),cache中的sequence就会丢失. 所以可

以在create sequence的时候用nocache防止这种情况。

2、 Alter sequence

你或者是该sequence的owner，或者有ALTER ANY sequence权限才能改动sequence。 可

以alter除start值之外的所有sequence参数。如果想要改变start值，必须drop sequence

再re-create。例子：

ALTER sequence emp_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE -- 到10000后从头开始

NOCACHE;

影响sequence的初始化参数：

sequence_CACHE_ENTRIES =

设置能同时被cache的sequence数目。

可以很简单的Drop sequence

DROP sequence order_seq;

结束
 
二、关于存储过程
存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。
1.oracle存储过程结构 
CREATE OR REPLACE PROCEDURE oracle存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END oracle存储过程名字
 
2.无返回值的oracle存储过程
create or replace procedure xs_proc_no is
begin
  insert into xuesheng values (3, 'wangwu', 90, 90);
  commit;
end xs_proc_no;
 
3.有单个数据值返回的oracle存储过程
create or replace procedure xs_proc(temp_name in varchar2,
                                    temp_num  out number) is
  num_1 number;
  num_2 number;
begin
  select yu_wen, shu_xue
    into num_1, num_2
    from xuesheng
   where xing_ming = temp_name;
  --dbms_output.put_line(num_1 + num_2);
  temp_num := num_1 + num_2;
end;
其中，以上两种与sql server基本类似，而对于返回数据集时，上述方法则不能满足我们的要求。在Oracle中，一般使用ref cursor来返回数据集。示例代码如下：
 
4.有返回值的oracle存储过程(列表返回)
首先，建立我们自己的包。并定义包中的一个自定义ref cursor
create or replace package mypackage as
  type my_cursor is ref cursor;
end mypackage;
在定义了ref cursor后，可以书写我们的程序代码
create or replace procedure xs_proc_list(shuxue   in number,
                                         p_cursor out mypackage.my_cursor) is
begin
  open p_cursor for
    select * from xuesheng where shu_xue > shuxue;
end xs_proc_list;
 
5.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(若没有记录则抛出NO_DATA_FOUND)
  例子： 
  BEGIN
  SELECT name,age into 变量1,变量2 FROM user where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...
 
6.IF 判断
  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;
 
7.WHILE 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;
 
8.变量赋值
  V_TEST := 123;
 
9.用FOR IN 使用cursor
  ...
  IS
  CURSOR cur IS SELECT * FROM xxx;
  BEGIN
 FOR cur_result in cur LOOP
  BEGIN
   V_SUM :=cur_result.列名1+cur_result.列名2
  END;
 END LOOP;
  END;
 
10.带参数的CURSOR
  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
  LOOP
 FETCH C_USER INTO V_NAME;
 EXIT FETCH C_USER%NOTFOUND;
    do something
  END LOOP;
  CLOSE C_USER;
 
11.用pl/sql developer debug
  连接数据库后建立一个Test WINDOW
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
 

ORACLE存储过程的若干问题备忘

1.在oracle中，表别名不能加as，如：
select a.name from user a;-- 正确
select a.name from user as a;-- 错误
应该是防止和oracle存储过程中的关键字as有冲突，才会这样规定吧！
 
2.oracle存储过程中select某一字段时，后面必须要跟into，如果select整个记录，利用游标的话就另当别论了。
  select u.name into kn from user u where u.userid=uid and u.fatherid=fid;-- 有into，正确编译
  select u.name from user u where u.userid=uid and u.fatherid=fid;-- 没有into，编译报错，提示：Compilation 
  Error: PLS-00428: an INTO clause is expected in this SELECT statement
 
3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
   可以在该语法前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...
 
4.oracle在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
select name into kn from user where userid=aid and fatherid=fid;-- 正确运行
select u.name into kn from user u where u.userid=userid and u.fatherid=fatherid;-- 运行时报错，提示
ORA-01422:exact fetch returns more than requested number of rows
 
5.在oracle存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create table user(
id varchar2(50) primary key not null,
age number(5) not null,
fid varchar2(50) not null -- 外键 
);
如果在oracle存储过程中，使用如下语句：
select sum(age) into fage from user where fid='xxxxxx';
如果user表中不存在fid="xxxxxx"的记录，则fage=null(即使fage定义时设置了默认值，如：fage number(5):=0依然无效，fage还是会变成null)，这样以后使用fage时就可能有问题，所以在这里最好先判断一下：
if fage is null then
    fage:=0;
end if;
这样就一切ok了。
 
6.java调用oracle存储过程。其中，关键是使用CallableStatement这个对象，代码如下：
 
String racleDriverName = "oracle.jdbc.driver.OracleDriver";
 
        // 以下使用的Test就是Oracle里的表空间
        String racleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        Connection myConnection = null;
        try {
            Class.forName(oracleDriverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        try {
            myConnection = DriverManager.getConnection(oracleUrlToConnect,
                    "xxxx", "xxxx");//此处为数据库用户名与密码
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
             
            CallableStatement proc=null;
            proc=myConnection.prepareCall("{call xs_proc(?,?)}");
            proc.setString(1, "lisi");
            proc.registerOutParameter(2, Types.NUMERIC);
            proc.execute();
            String teststring=proc.getString(2);
            System.out.println(teststring);
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
对于列表返回值的oracle存储过程，在上述代码中做简单修改。如下
CallableStatement proc=null;
            proc=myConnection.prepareCall("{call getdcsj(?,?,?,?,?)}");
            proc.setString(1, strDate);
proc.setString(2, jzbh);
            proc.registerOutParameter(3, Types.NUMERIC);
            proc.registerOutParameter(4, OracleTypes.CURSOR);
            proc.registerOutParameter(5, OracleTypes.CURSOR);
            proc.execute();
            ResultSet rs=null;
            int total_number=proc.getInt(3);
            rs=(ResultSet)proc.getObject(4);
上述oracle存储过程修改完毕
 
7.Hibernate调用oracle存储过程
        this.userManager.getHibernateTemplate().execute(
                new HibernateCallback() {
                    public Object doInHibernate(Session session)
                            throws HibernateException, SQLException {
                        CallableStatement cs = session
                                .connection()
                                .prepareCall("{call modifyapppnumber_remain(?)}");
                        cs.setString(1, foundationid);
                        cs.execute();
                        return null;
                    }
                });
 
三、关于JOB
1、使用Submit（）过程，工作被正常地计划好。
　　这个过程有五个参数：job、what、next_date、interval与no_parse.
　　PROCEDURE Submit （ job OUT binary_ineger,
　　What IN varchar2,
　　next_date IN date,
　　interval IN varchar2,
　　no_parse IN booean:=FALSE）
　　job参数是由Submit（）过程返回的binary_ineger.这个值用来唯一标识一个工作。
　　what参数是将被执行的PL/SQL代码块。
　　next_date参数指识何时将运行这个工作。
　　interval参数何时这个工作将被重执行。
　　no_parse参数指示此工作在提交时或执行时是否应进行语法分析--TRUE
　　指示此PL/SQL代码在它第一次执行时应进行语法分析，
　　而FALSE指示本PL/SQL代码应立即进行语法分析。
 
2、查看创建的job
　　查看相关job信息
　　1、相关视图
　　dba_jobs
　　all_jobs
　　user_jobs
　　dba_jobs_running 包含正在运行job相关信息。
 
3、===如何停止一个正在运行的JOB====
　　1 相关表、视图
　　dba_jobs
　　all_jobs
　　user_jobs 包含登录用户所有的JOB信息
　　dba_jobs_running 包含正在运行job相关信息
　　注意
　　须使用oracle的sys用户登录到数据库，才能查看dba_jobs_running, v$process, v$session表的信息。同时，登录操作系统时，要用oracle用户。
　　2 问题描述
　　为同事解决一个因为网络连接情况不佳时，执行一个超长时间的SQL插入操作。
　　既然网络状况不好，就选择了使用一次性使用JOB来完成该插入操作。在JOB执行一段时间后，我发现被插入表有些问题（惭愧，当时也没有先检查检查就做了）。准备停止JOB,因为在JOB运行情况下，我的所有修改都会报系统资源忙的错误。
　　强行KILL SESSION是行不通的，因为过会儿，JOB还会重新启动，如果执行的SQL也被KILL了通过重新启动的JOB还是会被再次新执行的。
　　3 解决办法
　　比较好的方法应该是；
　　1. 首先确定要停止的JOB号
　　在10g中可通过Dba_Jobs_Running进行确认。
　　查找正在运行的JOB:
　　select sid from dba_jobs_running;
　　查找到正在运行的JOB的spid:
　　select a.spid from v$process a ,v$session b where a.addr=b.paddr and b.sid in （select sid from dba_jobs_running）；
　　2. Broken你确认的JOB
　　注意使用DBMS_JOB包来标识你的JOB为BROKEN.
　　SQL> EXEC DBMS_JOB.BROKEN（job#,TRUE）；
　　注意：当执行完该命令你选择的这个JOB还是在运行着的。
　　3. Kill 对应的Oracle Session
　　应为BROKEN后该JOB还在运行，如果要求该JOB立刻停止，就需要找到该job的对应SESSION（SID,SERIAL#），然后执行以下命令：
　　ALTER SYSTEM KILL SESSION 'sid,serial#';
　　或者直接KILL对应的操作系统的SESSION,如果使用ALTER SYSTEM KILL SESSION执行很长时间，其实可以使用OS的命令来快速KILL掉SESSION.
　　For Windows, at the DOS Prompt: orakill sid spid
　　For UNIX at the command line> kill –9 spid
　　4. 检查你的JOB是否还在运行
　　检查你要停止的JOB是否还在运行，其实多数情况下，应该都已经停止了。尤其执行的第三步的"杀手"命令。如果真的还是没有停止，只好从第一道第三步重新做一下了。
　　5. 将Job Queue Processes的数目修改为0
　　首先确认当前的Job Queue Processes的数目
　　SQL> col value for a10
　　SQL> select name,value from v$parameter where name ='job_queue_processes';
　　然后将Job Queue Processes的数目修改为0
　　SQL> ALTER SYSTEM SET job_queue_processes = 0;
　　保证所有的JOB都会停止。
　　6. 修改你要修改的任何东西，甚至是JOB内的内容。
　　7. 修改完成后，将job的BROKEN状态停止。
　　SQL>EXEC DBMS_JOB.BROKEN（job#,FALSE）：
　　8. 恢复job_queue_processes的原始值
　　ALTER SYSTEM SET job_queue_processes = original_value;
　　至此整个停止并修改JOB完成。
　　但是需要另外注意一下的是，在MARK一个BROKEN状态的时候，因为JOB内部执行SQL的原因，所以执行时或许要"煎熬"一段时间。所以 在建立JOB时一定要仔细考虑，同时如果可能可以在你设计的PL/SQL代码中，增加一些判断"停止信号"的部分。来避免费力执行上面的步骤。
　　毕竟，ORACLE在执行JOB时，还是很"倔强"的 -:）
　　4 附件：ORAKILL用法
　　很多时候遇到某个session一直处于active,使得CPU一直处于搞使用状态，虽然kill 了，但是却不能够使得使得线程结束。 kill session只是kill这个进程，但是线程一直处于活动状态。需要真正的kill线程才能够解决cpu使用率高的问题。
　　os :windows2003
　　ORAKILL用法是 orakill sid spid 其中sid为实例名，spid为线程号。
　　怎么样取得这个spid呢？
　　以下给出查找spid的语句。
　　select spid, osuser, s.program from v$process p, v$session s
　　where p.addr=s.paddr and s.sid = XXX;
　　--说明：XXX为session的sid （不是数据库SID 呵呵）。自己输入就可以。
　　orakill instance_name spid
　　这样就能够成功解决cpu使用率高的问题
 


收藏 举报

TAG: 数据库 job JOB Oracle oracle sequence Sequence 存储过程

 

评分：0

我来说两句

我的栏目

日历

« 2024-04-21  
日 一 二 三 四 五 六
 123456
78910111213
14151617181920
21222324252627
282930    

数据统计

  • 访问量: 17407
  • 日志数: 7
  • 建立时间: 2013-08-30
  • 更新时间: 2014-01-16

RSS订阅

Open Toolbar