执行脚本：
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') ;
