oracle存储过程每天重置序列问题

[码农小灰]

CREATE OR REPLACE PROCEDURE p_reset_seq IS
/*
重置序列ID
*/
PN_ID NUMBER;
n number(10);
tsql varchar2(100);

BEGIN
--重置序列1
select seq_finance_income_id.nextval into n from dual;
n:=-(n-1);
tsql:='alter sequence seq_finance_income_id increment by' || (n);
execute immediate tsql;

select seq_finance_income_id.nextval into n from dual;
tsql:='alter sequence seq_finance_income_id increment by 1';//这里运行有时会报错是什么问题？
execute immediate tsql;
commit;
--重置序列2
select seq_finance_settlement_id.nextval into n from dual;
n:=-(n-1);
tsql:='alter sequence seq_finance_settlement_id increment by' || (n);
execute immediate tsql;

select seq_finance_settlement_id.nextval into n from dual;
tsql:='alter sequence seq_finance_settlement_id increment by 1';
execute immediate tsql;

commit;
END p_reset_seq;

[卖水果的net]

tsql:='alter sequence seq_finance_income_id increment by' || (n); execute immediate tsql; select seq_finance_income_id.nextval into n from dual; tsql:='alter sequence seq_finance_income_id increment by 1';//这里运行有时会报错是什么问题？ execute immediate tsql; commit; --重置序列2 select seq_finance_settlement_id.nextval into n from dual; n:=-(n-1); tsql:='alter sequence seq_finance_settlement_id increment by' || (n); 以上标红的3 行，都有问题。 1和 3、by 后要写一个空格 ， 以拼接成 by 1， 没有空格就是 by1 了。 2、// 要换成 -- ，SQL 语句中的注释是 /* */ 或 -- ，不是能是 //

[卖水果的net]

另外有一个问题，你要注意 tsql:='alter sequence seq_finance_income_id increment by ' || (n); n = 0 的时候，你要处理一下。