//创立三个从0起初的队列
drop sequence seq_sss_id;
create sequence seq_sss_id
increment by 1
start with 0
minvalue 0 
maxvalue 999999999;

重新恢复生机设置oracle种类从内定数字起首 复制代码
代码如下: declare n number(10卡塔尔国; v_startnum
number(10State of Qatar:=10000001;–从多少开首 v_step number(10):=1;–步进 tsql
varchar2(200); v_seqname varchar2(200):=’MIP_JF_SEQUENCE’;–序列名
begin execute immediate ‘select ‘||v_seqname||’.nextval from dual’ into
n; n:=v_startnum-n-v_step;–从10000001开始 tsql:=’alter sequence
‘||v_seqname||’ increment by ‘|| n; execute immediate tsql; execute
immediate ‘select ‘||v_seqname||’.nextval from dual’ into n;
tsql:=’alter sequence ‘||v_seqname||’ increment by ‘||v_step; execute
immediate tsql; end; 不通过删除重新建立立模型式 重新苏醒设置连串值得轻易方法。
日常来说,种类在其实支出进度中是时常应用的大器晚成种对象,通过它来变化主键是不行便于的,可是多少时候大家供给将其重新置零,平时选用的章程就是剔除后再一次创立。
上面大家来看一下其它豆蔻年华种方式: 复制代码 代码如下: SQL create sequence seq_1
increment by 1 start with 1 maxvalue 999999999; 类别已创建。 SQL create
or replace procedure seq_reset(v_seqname varchar2) as 2 n number(10);
3 tsql varchar2(100); 4 begin 5 execute immediate ‘select
‘||v_seqname||’.nextval from dual’ into n; 6 n:=-(n-1); 7 tsql:=’alter
sequence ‘||v_seqname||’ increment by ‘|| n; 8 execute immediate tsql;
9 execute immediate ‘select ‘||v_seqname||’.nextval from dual’ into n;
10 tsql:=’alter sequence ‘||v_seqname||’ increment by 1′; 11 execute
immediate tsql; 12 end seq_reset; 13 / 进程已开立。 SQL select
seq_1.nextval from dual; NEXTVAL ——— 2 SQL / NEXTVAL ——— 3
SQL / NEXTVAL ——— 4 SQL / NEXTVAL ——— 5 SQL exec
seq_reset(‘seq_1’State of Qatar; PL/SQL 进程已成功落成。 SQL select seq_1.currval
from dual; CU路虎极光EnclaveVAL ——— 1 SQL
那样能够经过每六日调用此进度,来完毕种类重新设置的指标。
此存款和储蓄进度写的可比仓促,还足以进一层康健,在这里就不再进一层陈诉
Oracle重新设置种类(不删除重新建设布局格局卡塔尔(قطر‎
Oracle中通常将自增sequence重新载入参数为开首1时,都以删除再重新建立,这种艺术有一点不知凡几缺欠,正视它的函数和存款和储蓄进度将失效,必要再行编译。
但是还也会有种高超的点子,不用删除,利用步长参数,先摸清sequence的nextval,记住,把依次增加改为负的这一个值,然后再改回来。
倘若须求修正的行列名:seq_name 1、select seq_name.nextval from dual;
//假诺获得结果5656 2、alter sequence seq_name increment by -5655;
//注意是-(n-1) 3、select seq_name.nextval from
dual;//再查二次,走一下,重新初始化为1了 4、alter sequence seq_name increment
by 1;//还原 能够写个存款和储蓄进程,以下是完好的积攒进度,然后调用传参就可以:
复制代码 代码如下: create or replace
procedure seq_reset(v_seqname varchar2) as n number(10); tsql
varchar2(100); begin execute immediate ‘select ‘||v_seqname||’.nextval
from dual’ into n; n:=-(n-1); tsql:=’alter sequence ‘||v_seqname||’
increment by ‘|| n; execute immediate tsql; execute immediate ‘select
‘||v_seqname||’.nextval from dual’ into n; tsql:=’alter sequence
‘||v_seqname||’ increment by 1′; execute immediate tsql; end
seq_reset;

游标报错,技能大牌扶持看看
create or replace procedure seq_name(v_seq out number) is
cursor c_seq_name is select sequence_name from dba_sequences where
sequence_name = ‘CMS’;
v_seq_name c_seq_name%rowtype;
begin
open c_seq_name;
loop
fetch c_seq_name into v_seq_name;
if c_seq_name%notfound then
execute immediate ‘alter sequence’||v_seq_name||’nocache nocycle’;
execute immediate ‘alter sequence’||v_seq_name||’increment by
-‘||v_seq_name.nextval||’minvalue 0 maxvalue 999999999’;
v_seq:=v_seq_name.nextval;
execute immediate ‘alter sequence’||v_seq_name||’increment by 1 cache
20 cycle’;
v_seq :=v_seq_name.nextval ;
else
exit;
end if;
end loop;
close c_seq_name;
end;
那是连串重新苏醒设置,作者单个写没难点,循环收取来就报错,哪个大神帮助看看啊

oracle矫正种类中的increment by的值时,报ORA-01722不行数字错误!
后日在写了贰个仓库储存函数,用来重新苏醒设置体系的值为”1″最初,作者在函数中改动系列的值时,使用自身定义的变量“seq_currval”(类型为number卡塔尔(قطر‎,代替increment
by后的活灵活现数值,编写翻译能由此(整个函数未有语法问题),不过只要在java中调用此函数,就能报ORA-01722不算数字错误!(但只要改为切实数值,就不会报错)

多年来在职业中遇到了要在oracle里面创造二个准时job,从远程数据Curry面定期把某张表里面包车型地铁数目更新到地方服务器上某些表里,具体操作:

//重新苏醒设置体系的存款和储蓄进度

在网络试过比相当多办法,都无法化解!有权威知道原因吧?谢谢

1、在友好数据Curry面先创制一张表test

create or replace procedure reset_jis_sequence as
n number(10);
tsql varchar2(100);
begin
execute immediate ‘select seq_sss_id.nextval from dual’ into n;
n:=-n;
tsql:=’alter sequence seq_sss_id increment by ‘|| n;
execute immediate tsql;
execute immediate ‘select seq_sss_id.nextval from dual’ into n;
tsql:=’alter sequence seq_sss_id increment by 1′;
execute immediate tsql;
end reset_jis_sequence;

–重新初始化体系的值,增加值为1,开首值为1
select seq_cus_id.nextval into seq_currval from dual;
execute immediate ‘select seq_cus_id.nextval from dual’;
alter_sql:=’alter sequence seq_cus_id

create table TEST

//job按期(天天0:00准期运营reset_jis_sequence 累积进度)
declare
job number;
begin
sys.dbms_job.submit(job => job,
what => ‘reset_jis_sequence;’,
next_date => to_date(’21-04-2018′, ‘dd-mm-yyyy’),
interval => ‘TRUNC( SYSDATE + 1)’);
commit;
end;

increment by -seq_currval’;

execute immediate alter_sql;

(

//查询job的id
select job,what,failures,broken from user_jobs

ID     NUMBER(12),

//根据job的ID执行job
begin
dbms_job.run(jobId);
end;

C_DATE DATE

//根据job的ID删除job
BEGIN

)

相关文章