oracle之存储过程

*、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