*、oracle之存储过程语法基础
create [or replace] procedure 存储过程名称 is|as 变量n 类型(范围); begin 操作 exception when others then rollback; end [存储过程名称]
*、oracle中的一个更新sequence的应用实例
create or replace procedure UpdateSeq is
dropCurSeqSql varchar2(300);
createCurSeqSql varchar2(300);
curSeqSql varchar2(400);
isExistSql varchar2(300);
existTableNum number;
curMaxIdSql varchar2(400);
curSeqName varchar2(100);
curSeqInCre number;
curTableName varchar2(100);
curMaxId number;
begin
for curSeqSql in (select * from user_sequences) loop
curSeqName := curSeqSql.Sequence_Name;
if(instr(curSeqName,'SEQ_')>0)
then
curTableName := substr(curSeqName,instr(curSeqName,'SEQ_')+4);
else
continue;
end if;
isExistSql := 'select count(1) from user_tables where table_name= '||chr(39)||curTableName||chr(39);
execute immediate isExistSql into existTableNum;
if (existTableNum <= 0)
then
continue;
end if;
if(instr(curTableName,'DC_')>0)
then
curMaxIdSql := 'select nvl(max(oid),0) from '|| curTableName;
else
curMaxIdSql := 'select nvl(max(id),0) from '|| curTableName;
end if;
dropCurSeqSql := 'drop sequence '||curSeqName;
execute immediate dropCurSeqSql;
execute immediate curMaxIdSql into curMaxId;
curSeqInCre := curSeqSql.Increment_By;
createCurSeqSql := 'create sequence '||curSeqName||' minvalue 1 maxvalue 99999999 start with '||concat((curMaxId+curSeqInCre),'')||' increment by '||concat(curSeqInCre,'')||' nocache order';
execute immediate createCurSeqSql;
dbms_output.put_line(createCurSeqSql);
end loop;
dbms_output.put_line('over!');
exception
when others then
dbms_output.put_line(sqlcode||'----'||sqlerrm);
Rollback;
end UpdateSeq;
总结:
由于sequence和表名之间没有直接联系的表,所以在设计sequence的时候一定要先设计好某种规则
,比如在表名前加SEQ_的前缀,规范大于设计理念。
然后根据该规则进行sequence的动态更新!
-----------------------------------------------------------资源列表------------------------------------------------------------------
*、零散的资源
https://www.cnblogs.com/goodbye305/archive/2008/06/23/1228073.html
http://blog.youkuaiyun.com/u011704894/article/details/44976557
https://www.cnblogs.com/oraclestudy/articles/5779189.html
http://blog.itpub.net/22678696/viewspace-1116535/
https://www.cnblogs.com/haldir/p/5533414.html