帮忙看看这个存储过程是什么错误

[nancliu]

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';
这个存储过程的功能是从修改序列,并改变序列的当前值

[dencyzhang]

str_sql:='select last_number-FLOOR(last_number/1000)*1000 from user_sequences
where sequence_name=''SEQ_AUTH'';
execute immediate str_sql INTO CurrValue ;

TRY !!

[robin_ares]

select into 直接写就行了，不能作为sql执行
select last_number-FLOOR(last_number/1000)*1000
INTO CurrValue
from user_sequences
where sequence_name='SEQ_AUTH'

[baifanSailing]

DDL语句不能写在存储过程里, 可以象二楼的哪样用动态SQL语句.

[多壮志]

常识性错误，对于oracle中的对象，在动态语句中是不能使用绑定变量表示的。
譬如你要drop掉那个sequence ,则可如此
str_Sql:='drop sequence '||seq_name;
execute immediate str_sql;

[nancliu]

按照2楼那样做了,仍然是原来那个错误

[flg_inwind]

过程写成这样也够为难的了。
建议你先看看pl/sql程序设计吧，或许收获更大些。
《pl/sql程序设计》 http://download.csdn.net/source/215387

[nancliu]

修改之后,编译通过了,但调用时显示,无效的序列名
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;

[nancliu]

修改结果如下，正确

(
       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;