Oracle12cR2函数、存储过程、Job及序列与重置序列综合案例

Oracle12cR2之函数、存储过程、Job及序列与重置序列综合案例

1. 创建表、序列、存储过程

1. 创建业务表

create table TB_TEST
(
  id         number primary key ,
  name       varchar2(100),
  product_no varchar2(50)
)
/
comment on table TB_TEST is '测试表'
/
comment on column TB_TEST.id is '主键'
/
comment on column TB_TEST.name is '名称'
/
comment on column TB_TEST.product_no is '产品编号'
/

2. 创建序列

-- id自增序列
create sequence SEQ_TEST_ID
start with 1
increment by 1
minvalue 1
maxvalue 99999
cache 100
cycle;


-- 产品编号序列
create sequence SEQ_TEST_NO
start with 1
increment by 1
minvalue 1
maxvalue 99999
cache 100
cycle;

3. 创建存储过程

1. 创建一个名称为PRO_MY_DEMO的存储过程，用来向TB_TEST表中插入数据;
2. 注意：在存储过程中执行sql语句时必须使用execute immediate your_sql 来执行，否则编译都无法通过

create or replace procedure PRO_MY_DEMO is
    -- 定义执行的sql语句变量
    exec_sql varchar2(200);
begin
    exec_sql := 'insert into TB_TEST(id, name, product_no)
    values (SEQ_TEST_ID.nextval, ''名称'' || SEQ_TEST_ID.currval, ''产品-'' || SEQ_TEST_NO.nextval)';
    -- 执行sql
    execute immediate exec_sql;
    -- 提交事务
    commit;
end;

2. 使用案例

1. 创建Job执行存储过程

declare
    JOB_PRODUCT_TEST number;
begin
    DBMS_JOB.SUBMIT(JOB => JOB_PRODUCT_TEST,
                    WHAT => 'PRO_MY_DEMO;',
                    NEXT_DATE => sysdate,
                    interval => 'trunc(sysdate,''mi'')+1/(24*60)');
    commit;
end;

2. 创建重置序列的函数

create or replace function FUN_RESET_SEQUENCE(SEQUENCE_NAME in varchar2)
    return integer is
    FUNCTION_RESULT integer;
    RESET_SQL      varchar2(200);
    CURRENT_VAL    integer;
    /**
    * author:jinshengyuan
    * date:2018-09-07
    * description:根据序列名称重置序列的值
    */
begin
    --1.查询当前序列值,放入CURRENT_VAL中
    execute immediate 'select ' || SEQUENCE_NAME || '.nextval from dual'
        into CURRENT_VAL;
    --2.给CURRENT_VAL-1后取负数
    CURRENT_VAL := - (CURRENT_VAL - 1);
    --3.将增量值重置为 -CURRENT_VAL
    RESET_SQL := 'alter sequence ' || SEQUENCE_NAME || ' increment by ' ||
                 CURRENT_VAL;
    execute immediate RESET_SQL;
    --4.再次查询时值为1
    execute immediate 'select ' || SEQUENCE_NAME || '.nextval from dual'
        into CURRENT_VAL;
    --5.再次重置增量值为1
    RESET_SQL := 'alter sequence ' || SEQUENCE_NAME || ' increment by 1';
    execute immediate RESET_SQL;
    FUNCTION_RESULT := 1; --1表示重置成功
    return (FUNCTION_RESULT);
exception
    when others then
        return null; --重置失败返回null
end FUN_RESET_SEQUENCE;

3. 输入序列名称来重置序列

select fun_reset_sequence('SEQ_TEST_NO') from dual;

4. 通过Job定时重置给定的序列

1. 定义重置序列测存储过程

create or replace procedure PRO_RESET_SEQUENCE(SEQUENCE_NAME in varchar2) is
begin
    -- 执行重置序列函数
    execute immediate 'select FUN_RESET_SEQUENCE(SEQUENCE_NAME) from dual';
end;

2. 创建Job定时调用存储过程来重置序列

TRUNC(sysdate+1)+1/24 : 表示每一天凌晨一点执行Job

 -- 每天凌晨1点执行一次，TRUNC(sysdate+1)+1/24
declare
    JOB_RESET_SEQUENCE number;
begin
    DBMS_JOB.SUBMIT(JOB => JOB_RESET_SEQUENCE,
                    WHAT => 'PRO_RESET_SEQUENCE(''SEQ_TEST_NO'');',
                    NEXT_DATE => sysdate,
                    interval => 'TRUNC(sysdate+1)+1/24');
    commit;
end;

-- 每分钟执行一次，trunc(sysdate,''mi'')+1/(24*60)
declare
    JOB_RESET_SEQUENCE number;
begin
    DBMS_JOB.SUBMIT(JOB => JOB_RESET_SEQUENCE,
                    WHAT => 'PRO_RESET_SEQUENCE(''SEQ_TEST_NO'');',
                    NEXT_DATE => sysdate,
                    interval => 'trunc(sysdate,''mi'')+1/(24*60)');
    commit;
end;