create or replace procedure TB_Sequences is
LastValue integer;
newValue integer;
varSql varchar2(100);
sequence_no varchar2(50);
cursor cur_seq is select t.sequence_name, t.last_number
from all_sequences@zrhis0512 t
where t.sequence_owner = 'ZRHIS0512'
and t.sequence_name = 'AA';
begin
open cur_seq;
loop
fetch cur_seq into sequence_no,LastValue;
exit when cur_seq%notfound;
loop
execute immediate 'select '||sequence_no||'.nextval from dual';
varSql:='select '||sequence_no||'.currval into newValue from dual';*********执行这句话时出错 newValue是变量,我想把值存入这个变量中
execute immediate varSql;
exit when newValue >=LastValue; end loop;
end loop;
DBMS_OUTPUT.put_line(newValue);
close cur_seq;
end;上面是我的存储过程内容运行时出错我用*标出来了,缺失关键字,不知这块怎么写,高人指典
LastValue integer;
newValue integer;
varSql varchar2(100);
sequence_no varchar2(50);
cursor cur_seq is select t.sequence_name, t.last_number
from all_sequences@zrhis0512 t
where t.sequence_owner = 'ZRHIS0512'
and t.sequence_name = 'AA';
begin
open cur_seq;
loop
fetch cur_seq into sequence_no,LastValue;
exit when cur_seq%notfound;
loop
execute immediate 'select '||sequence_no||'.nextval from dual';
varSql:='select '||sequence_no||'.currval into newValue from dual';*********执行这句话时出错 newValue是变量,我想把值存入这个变量中
execute immediate varSql;
exit when newValue >=LastValue; end loop;
end loop;
DBMS_OUTPUT.put_line(newValue);
close cur_seq;
end;上面是我的存储过程内容运行时出错我用*标出来了,缺失关键字,不知这块怎么写,高人指典
newValue:=sequence_no.CURRVAL试试吧,你直接赋值好了,不需要select ... from dual的。
create or replace procedure TB_Sequences is
mycursor INTEGER;
ignore INTEGER;
LastValue integer;
newValue integer;
varSql varchar2(100);
sequence_no varchar2(50);
cursor cur_seq is select t.sequence_name, t.last_number
from all_sequences@zrhis0512 t
where t.sequence_owner = 'ZRHIS0512'
and t.sequence_name = 'AA';
begin
open cur_seq;
loop
fetch cur_seq into sequence_no,LastValue;
exit when cur_seq%notfound;
loop
execute immediate 'select '||sequence_no||'.nextval from dual';
varSql:='select '||sequence_no||'.currval from dual';
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor, varSql,1);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,newValue,1000);
ignore := dbms_sql.execute(mycursor);
while DBMS_SQL.FETCH_ROWS(mycursor)<> 0 loop
DBMS_SQL.COLUMN_VALUE(mycursor,1,newValue);
END loop;
dbms_sql.close_cursor(mycursor);
exit when newValue >=LastValue; end loop;
end loop;
DBMS_OUTPUT.put_line(newValue);
close cur_seq;
end;
LastValue integer;
newValue integer;
varSql varchar2(100);
sequence_no varchar2(50);
cursor cur_seq is select t.sequence_name, t.last_number
from all_sequences@zrhis0512 t
where t.sequence_owner = 'ZRHIS0512'
and t.sequence_name = 'AA';
begin
open cur_seq;
loop
fetch cur_seq into sequence_no,LastValue;
exit when cur_seq%notfound;
loop
execute immediate 'select '||sequence_no||'.nextval from dual';
varSql:='select '||sequence_no||'.currval from dual';
execute immediate varSql into newValue;
exit when newValue >=LastValue; end loop;
end loop;
DBMS_OUTPUT.put_line(newValue);
close cur_seq;
end;