调用脚本的方式自动的创建或者是更新oracle数据库自带的Seq序列号的值

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

目录

• 执行脚本：
• 脚本调用
  • 使用plsql 的命令窗口调用：

 

正文

回到顶部

执行脚本：

CREATE OR REPLACE PACKAGE PKG_QUERY IS

  -- Author  : ADMINISTRATOR
  -- Created : 2016/12/8 星期四 10:28:37
  -- Purpose : 用做查询游标

  -- Public type declarations
  TYPE CUR_QUERY IS REF CURSOR;

END PKG_QUERY;
/

create or replace function create_seq_func(
   seq_name in  varchar2 ,/*seq名称*/
   bus_table_name in varchar2,/*业务表名*/
   pkcloumn_name in varchar2 /*业务表主键名*/
)
return varchar2
is
result varchar2(30000);
type ref_cursor_type is ref cursor;
cursor_ids  ref_cursor_type;
cursor_ids_1  ref_cursor_type;
V_NUM number:=0;
v_max_seq number :=1;
v_max_seq_1 number :=1;

v_max_sql varchar(4000);
v_max_sql_1 varchar(4000);

v_sql_alter varchar(4000);
v_sequence_sql varchar(4000);
begin

   select count(0) into V_NUM from user_sequences where sequence_name = seq_name;
    if V_NUM > 0 then

          v_max_sql_1:='select to_number(max('||pkcloumn_name||')+100)   from '||bus_table_name||' t   ';
          dbms_output.put_line('_____0_______'||v_max_sql_1);
          open cursor_ids_1 for v_max_sql_1 ;
          fetch cursor_ids_1   into  v_max_seq_1;
          /**  判断 是否有id 值查询出来  */
          if cursor_ids_1%found    then
             while cursor_ids_1%found loop
                dbms_output.put_line('_____1_______'||v_max_seq_1);

                  ---把自动增长值设置为 业务表的最大值
                  v_sql_alter :='ALTER SEQUENCE '||seq_name||'  INCREMENT BY '|| v_max_seq_1;
                  dbms_output.put_line('_____3_______'||v_sql_alter);
                  execute immediate v_sql_alter;
                 ---获取下一个值
                  v_sql_alter :=' SELECT '||seq_name||'.NEXTVAL     FROM DUAL ';
                  dbms_output.put_line('_____4_______'||v_sql_alter);
                  -- execute immediate v_sql_alter;
                  open cursor_ids_1 for v_sql_alter ;
                  fetch cursor_ids_1   into  v_max_seq_1;
                  /**  判断 是否有id 值查询出来  */
                  if cursor_ids_1%found    then
                     while cursor_ids_1%found loop
                        dbms_output.put_line('_____4_1_______'||v_max_seq_1);
                        v_max_seq:=v_max_seq_1;

                        ---把自动增长值设置为1
                        v_sql_alter :='ALTER SEQUENCE '||seq_name||'   INCREMENT BY 1 ' ;
                        dbms_output.put_line('_____5_______'||v_sql_alter);
                        execute immediate v_sql_alter;
                        ---获取下一个值
                        v_max_sql_1 :=' SELECT '||seq_name||'.NEXTVAL    FROM DUAL  ';
                        dbms_output.put_line('_____6_______'||v_max_sql_1);

                        open cursor_ids_1 for v_max_sql_1 ;
                        fetch cursor_ids_1   into  v_max_seq_1;
                        /**  判断 是否有id 值查询出来  */
                        if cursor_ids_1%found    then
                           while cursor_ids_1%found loop
                            dbms_output.put_line('_____2_______'||v_max_seq_1);
                            v_max_seq:=v_max_seq_1;
                         fetch cursor_ids_1   into  v_max_seq_1;
                          exit when cursor_ids%notfound or cursor_ids%notfound is null ;
                           end loop;
                        end if;


                   fetch cursor_ids_1   into  v_max_seq_1;
                    exit when cursor_ids%notfound or cursor_ids%notfound is null ;
                     end loop;
                  end if;


             fetch cursor_ids_1   into  v_max_seq_1;
                dbms_output.put_line('_____2_______'||v_max_seq_1);
           exit when cursor_ids%notfound or cursor_ids%notfound is null ;
             end loop;
          end if;


     else
            v_sequence_sql :='create sequence '||seq_name||' minvalue  1 maxvalue 999999 start with  1 increment by 1 cache 20';
            dbms_output.put_line('_____11_______'||v_sequence_sql);
            execute immediate v_sequence_sql;

            v_max_sql_1:='select to_number(max('||pkcloumn_name||')+100)   from '||bus_table_name||' t   ';
            dbms_output.put_line('_____10_______'||v_max_sql_1);
            open cursor_ids_1 for v_max_sql_1 ;
            fetch cursor_ids_1   into  v_max_seq_1;
            /**  判断 是否有id 值查询出来  */
            if cursor_ids_1%found    then
               while cursor_ids_1%found loop
                  dbms_output.put_line('_____11_______'||v_max_seq_1);

                    ---把自动增长值设置为 业务表的最大值
                    v_sql_alter :='ALTER SEQUENCE '||seq_name||'  INCREMENT BY '|| v_max_seq_1;
                    dbms_output.put_line('_____13_______'||v_sql_alter);
                    execute immediate v_sql_alter;
                   ---获取下一个值
                    v_sql_alter :=' SELECT '||seq_name||'.NEXTVAL     FROM DUAL ';
                    dbms_output.put_line('_____4_______'||v_sql_alter);
                    -- execute immediate v_sql_alter;
                    open cursor_ids_1 for v_sql_alter ;
                    fetch cursor_ids_1   into  v_max_seq_1;
                    /**  判断 是否有id 值查询出来  */
                    if cursor_ids_1%found    then
                       while cursor_ids_1%found loop
                          dbms_output.put_line('_____4_1_______'||v_max_seq_1);
                          v_max_seq:=v_max_seq_1;

                          ---把自动增长值设置为1
                          v_sql_alter :='ALTER SEQUENCE '||seq_name||'   INCREMENT BY 1 ' ;
                          dbms_output.put_line('_____5_______'||v_sql_alter);
                          execute immediate v_sql_alter;
                          ---获取下一个值
                          v_max_sql_1 :=' SELECT '||seq_name||'.NEXTVAL    FROM DUAL  ';
                          dbms_output.put_line('_____6_______'||v_max_sql_1);

                          open cursor_ids_1 for v_max_sql_1 ;
                          fetch cursor_ids_1   into  v_max_seq_1;
                          /**  判断 是否有id 值查询出来  */
                          if cursor_ids_1%found    then
                             while cursor_ids_1%found loop
                              dbms_output.put_line('_____2_______'||v_max_seq_1);
                              v_max_seq:=v_max_seq_1;
                           fetch cursor_ids_1   into  v_max_seq_1;
                            exit when cursor_ids%notfound or cursor_ids%notfound is null ;
                             end loop;
                          end if;


                     fetch cursor_ids_1   into  v_max_seq_1;
                      exit when cursor_ids%notfound or cursor_ids%notfound is null ;
                       end loop;
                    end if;


               fetch cursor_ids_1   into  v_max_seq_1;
                  dbms_output.put_line('_____17_______'||v_max_seq_1);
             exit when cursor_ids%notfound or cursor_ids%notfound is null ;
               end loop;
            end if;



    end if;

return  v_max_seq;
EXCEPTION
  WHEN OTHERS THEN

    RETURN v_max_seq;

end;
/

回到顶部

脚本调用

使用plsql 的命令窗口调用：

variable seq varchar2  ;
execute  :seq  :=create_seq_func('GK_PAYREPORT_SEQ','GK_PAYREPORT','GK_PAYREPORT_ID')  ;