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;
/*
重置序列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;
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 语句中的注释是 /* */ 或 -- ,不是能是 //