create or replace procedure seq_update()
as
str_sql varchar2(500);
CurrValue integer;
LastValue integer;
BEGIN
str_sql:='select last_number-FLOOR(last_number/1000)*1000 INTO CurrValue from user_sequences
where sequence_name=''SEQ_AUTH'';
execute immediate str_sql;
str_sql:='DROP SEQUENCE SEQ_AUTH';
execute immediate str_sql;
str_sql:='CREATE SEQUENCE SEQ_AUTH MINVALUE 101 MAXVALUE 199 START WITH 101 INCREMENT BY 1 NOCACHE';
execute immediate str_sql;
alter sequence SEQ_AUTH increment by -1 nocache;
select SEQ_AUTH.nextval from dual;
alter sequence SEQ_AUTH increment by 1 nocache;
loop
select SEQ_AUTH.currval into LastValue from dual;
exit when LastValue >= CurrValue;
select SEQ_AUTH.nextval into LastValue from dual;
end loop;
alter sequence SEQ_AUTH increment by 1 nocache;
SELECT LAST_NUMBER from user_sequences where sequence_name='SEQ_AUTH'
end;
------错误如下
Text: procedure seq_update()
#13#10Error: PLS-00103: 出现符号 "DROP"在需要下列之一时:
. ( * @ % & = - + ; < / >
at in is mod not rem <an exponent (**)> <> or != or ~= >= <=
<> and or like between ||
Line: 10
Text: str_sql:='DROP SEQUENCE SEQ_AUTH';
这个存储过程的功能是从修改序列,并改变序列的当前值
as
str_sql varchar2(500);
CurrValue integer;
LastValue integer;
BEGIN
str_sql:='select last_number-FLOOR(last_number/1000)*1000 INTO CurrValue from user_sequences
where sequence_name=''SEQ_AUTH'';
execute immediate str_sql;
str_sql:='DROP SEQUENCE SEQ_AUTH';
execute immediate str_sql;
str_sql:='CREATE SEQUENCE SEQ_AUTH MINVALUE 101 MAXVALUE 199 START WITH 101 INCREMENT BY 1 NOCACHE';
execute immediate str_sql;
alter sequence SEQ_AUTH increment by -1 nocache;
select SEQ_AUTH.nextval from dual;
alter sequence SEQ_AUTH increment by 1 nocache;
loop
select SEQ_AUTH.currval into LastValue from dual;
exit when LastValue >= CurrValue;
select SEQ_AUTH.nextval into LastValue from dual;
end loop;
alter sequence SEQ_AUTH increment by 1 nocache;
SELECT LAST_NUMBER from user_sequences where sequence_name='SEQ_AUTH'
end;
------错误如下
Text: procedure seq_update()
#13#10Error: PLS-00103: 出现符号 "DROP"在需要下列之一时:
. ( * @ % & = - + ; < / >
at in is mod not rem <an exponent (**)> <> or != or ~= >= <=
<> and or like between ||
Line: 10
Text: str_sql:='DROP SEQUENCE SEQ_AUTH';
这个存储过程的功能是从修改序列,并改变序列的当前值
where sequence_name=''SEQ_AUTH'';
execute immediate str_sql INTO CurrValue ; TRY !!
select last_number-FLOOR(last_number/1000)*1000
INTO CurrValue
from user_sequences
where sequence_name='SEQ_AUTH'
建议你先看看pl/sql程序设计吧,或许收获更大些。
《pl/sql程序设计》 http://download.csdn.net/source/215387
CREATE OR REPLACE PROCEDURE "SEQ_UPDATE" (Seq_Name in varchar2,
Seq_Rng in number
)
as
str_sql varchar2(500);
CurrValue integer;
LastValue integer;
BEGIN
str_sql:='select last_number-FLOOR(last_number/1000)*1000 from user_sequences
where sequence_name=:1';
execute immediate str_sql INTO CurrValue using Seq_Name;
str_sql:='DROP SEQUENCE :1';
execute immediate str_sql using Seq_Name; --无效的序列名 str_sql:='CREATE SEQUENCE :n3 MINVALUE (:r1)+1 MAXVALUE (:r1)*2-1 START WITH :r1+1 INCREMENT BY 1 NOCACHE';
execute immediate str_sql using Seq_Name,Seq_Rng;
str_sql:='alter sequence :n4 increment by -1 nocache';
execute immediate str_sql using Seq_Name;
str_sql:='select :n5.nextval from dual';
execute immediate str_sql using Seq_Name;
str_sql:='alter sequence :n6 increment by 1 nocache';
execute immediate str_sql using Seq_Name;
loop
str_sql:='select :n7.currval into LastValue from dual';
execute immediate str_sql using Seq_Name;
exit when LastValue >= CurrValue;
str_sql:='select :n8.nextval into LastValue from dual';
execute immediate str_sql using Seq_Name;
end loop;
str_sql:='alter sequence :n9 increment by 1 nocache';
execute immediate str_sql using Seq_Name;
str_sql:='SELECT LAST_NUMBER from user_sequences where sequence_name=:n10';
execute immediate str_sql using Seq_Name;
end;
譬如你要drop掉那个sequence ,则可如此
str_Sql:='drop sequence '||seq_name;
execute immediate str_sql;
v_in_OnRampID IN CONSUMPTIONAL.Onrampid%Type,--入口ID
v_in_OffRampID IN CONSUMPTIONAL.Offrampid%Type,--出口ID
v_in_SimNumber IN OBUINFO.SIMNUMBER%Type,--Sim卡号码 v_out_Charge OUT CONSUMPTIONAL.TOLL%Type,--输出收费金额
v_out_PlateNumber OUT OBUINFO.PLATENUMBER%Type--输出车牌号
)
IS
v_ChargeType USERINFO.CHARGETYPE%TYPE;
BEGIN
v_out_Charge:=0; --查询车牌号码
SELECT PLATENUMBER INTO v_out_PlateNumber
FROM OBUINFO
WHERE SIMNUMBER=v_in_SimNumber;
--查询收费类型
SELECT CHARGETYPE INTO v_ChargeType
FROM USERINFO
WHERE PLATENUMBER=v_out_PlateNumber; CASE v_ChargeType
WHEN 'A' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_A
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
WHEN 'B' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_B
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
WHEN 'C' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_C
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
WHEN 'D' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_D
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
ELSE v_out_Charge:=0;
END CASE;
END Get_Charge_PlateNumber;