刚看pl/sql和存储过程这一段,不太懂,望懂的指教.
drop table newtest;
create table newtest(vcol varchar2(10),ncol number(10));
insert into newtest values('12312',1);
insert into newtest values('1232',2);
insert into newtest values('12312',3);
insert into newtest values('12312',1);
select * from newtest;
--this example is about how to create and use procedure
create or replace procedure P_UpdateRecord
(v_ncol number, v_vcol varchar2) as
begin
update newtest set vcol = v_vcol where ncol= v_ncol;
commit;
end P_UpdateRecord;
-- when the procedure has been created we can use it
declare
n_col newtest.ncol%type;
v_col newtest.vcol%type;
cursor c_test1 is select * from newtest;
begin
open c_test1;
LOOP
fetch c_test1 into v_col,n_col;
if v_col=1 then
dbms_output.put_line(n_col||' '||v_col);
P_UpdateRecord(n_col,'杨红');
dbms_output.put_line(n_col||' '||v_col);
end if;
end LOOP;
close c_test1;
end;
/
请问这段代码哪出错了呢,为什么执行完后会报错 buffer overflow limit of 20000bites, 这么一段小小的代码不可能导致溢出吧.
但是虽然报错了,我执行select * from newtest之后又发现结果是正确的,也就是说n_col=1的记录的v_col被改为杨红了..谁能告诉我怎么了,我感觉有哪里写错了.
drop table newtest;
create table newtest(vcol varchar2(10),ncol number(10));
insert into newtest values('12312',1);
insert into newtest values('1232',2);
insert into newtest values('12312',3);
insert into newtest values('12312',1);
select * from newtest;
--this example is about how to create and use procedure
create or replace procedure P_UpdateRecord
(v_ncol number, v_vcol varchar2) as
begin
update newtest set vcol = v_vcol where ncol= v_ncol;
commit;
end P_UpdateRecord;
-- when the procedure has been created we can use it
declare
n_col newtest.ncol%type;
v_col newtest.vcol%type;
cursor c_test1 is select * from newtest;
begin
open c_test1;
LOOP
fetch c_test1 into v_col,n_col;
if v_col=1 then
dbms_output.put_line(n_col||' '||v_col);
P_UpdateRecord(n_col,'杨红');
dbms_output.put_line(n_col||' '||v_col);
end if;
end LOOP;
close c_test1;
end;
/
请问这段代码哪出错了呢,为什么执行完后会报错 buffer overflow limit of 20000bites, 这么一段小小的代码不可能导致溢出吧.
但是虽然报错了,我执行select * from newtest之后又发现结果是正确的,也就是说n_col=1的记录的v_col被改为杨红了..谁能告诉我怎么了,我感觉有哪里写错了.
declare
n_col newtest.ncol%type;
v_col newtest.vcol%type;
cursor c_test1 is select * from newtest;
begin
open c_test1;
LOOP
fetch c_test1 into v_col,n_col;
exit when c_test1%notfound; --加这一句啊,不退出一直loop了
if v_col=1 then
dbms_output.put_line(n_col||' '||v_col);
P_UpdateRecord(n_col,'杨红');
dbms_output.put_line(n_col||' '||v_col);
end if;
end LOOP;
close c_test1;
end;