CREATE OR REPLACE PROCEDURE EAM.PROC_RESET_SEQUENCES IS
TSQL VARCHAR(200);
CURSOR CUR_SEQ IS SELECT SEQUENCE_NAME FROM USER_SEQUENCES;
CUR_SEQ_ITEM CUR_SEQ%ROWTYPE;
BEGIN
OPEN CUR_SEQ;
LOOP
FETCH CUR_SEQ INTO CUR_SEQ_ITEM;
EXIT WHEN CUR_SEQ%NOTFOUND;
TSQL := 'DROP SEQUENCE ' || CUR_SEQ_ITEM.SEQUENCE_NAME;
EXECUTE IMMEDIATE TSQL;
TSQL := 'CREATE SEQUENCE ' || CUR_SEQ_ITEM.SEQUENCE_NAME || ' INCREMENT BY 1 MAXVALUE 99999999999999999999';
EXECUTE IMMEDIATE TSQL;
END LOOP;
CLOSE CUR_SEQ;
END PROC_RESET_SEQUENCES;
/
TSQL VARCHAR(200);
CURSOR CUR_SEQ IS SELECT SEQUENCE_NAME FROM USER_SEQUENCES;
CUR_SEQ_ITEM CUR_SEQ%ROWTYPE;
BEGIN
OPEN CUR_SEQ;
LOOP
FETCH CUR_SEQ INTO CUR_SEQ_ITEM;
EXIT WHEN CUR_SEQ%NOTFOUND;
TSQL := 'DROP SEQUENCE ' || CUR_SEQ_ITEM.SEQUENCE_NAME;
EXECUTE IMMEDIATE TSQL;
TSQL := 'CREATE SEQUENCE ' || CUR_SEQ_ITEM.SEQUENCE_NAME || ' INCREMENT BY 1 MAXVALUE 99999999999999999999';
EXECUTE IMMEDIATE TSQL;
END LOOP;
CLOSE CUR_SEQ;
END PROC_RESET_SEQUENCES;
/
解决方案 »
- SQLPLUS登录时的问题
- 关于索引组织表的基础知识,希望大神来答疑下
- 年百万记录的表如何构建!!!!
- 导出表后,删除原表,再导入,竟然select不到任何信息
- 监听器无法启动专用服务器进程
- C#连oracle数据库
- 错误:oracle17002,The Network Adapter could not establish the connection
- 如何将应用程序与oracle服务器端一起打包
- oracle在客户端的运行的问题
- oracle中如何实现这句的功能(sql server下能通过)
- insert into select
- 数据库事务隔离级别Read Committed和Repeatable Read的区
既然名称放到CUR_SEQ_ITEM
那么DROP 和create语句里面的CUR_SEQ_ITEM.SEQUENCE_NAME就应该用CUR_SEQ_ITEM代替了。