请大家看看这个存储过程

[fenglaile]

create procedure or replace update_form
(
date1 date,
date2 date
) as
st:varchar2(10);
begin
declare cursor sss is (select ppp from formtable where pdate between
date1 and date2);
open sss;
loop
fetch sss into st;
exit when sss%notfound;
update kkform set forma=st;
end loop;
close sss;
end;

从loop 开始就出、错！头大了，

[nicholaz]

你写过程的方式很奇怪嘛！

create procedure or replace update_form
(
date1 date,
date2 date
) as
st varchar2(10);
cursor(date3 date, date4 date) sss is select ppp from formtable where pdate between date3 and date4;

begin
open sss(date3, date4);
loop
fetch sss into st;
exit when sss%notfound;
update kkform set forma=st;
end loop;
close sss;
end;

[jiezhi]

create procedure or replace update_form
(
date1 date,
date2 date
) as
st formtable.ppp%type;
cursor sss is select ppp from formtable where pdate between
date1 and date2;
begin

open sss;
loop
fetch sss into st;
exit when sss%notfound;
update kkform set forma=st where ...;
end loop;
close sss;
end update_form;

[nicholaz]

open sss(date3, date4);
改为
open sss(date1, date2);

[jiezhi]

REM exitwhen.sql
REM Version 1.0, last updated 4/7/97
REM This block illustrates an incorrect usage of the EXIT WHEN statement,
REM as described in Chapter 6 of _Oracle8 PL/SQL Programming_
REM by Scott Urman.

DECLARE
-- Declare variables to hold information about the students
-- majoring in History.
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;

-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major = 'History';
BEGIN
-- Open the cursor and initialize the active set
OPEN c_HistoryStudents;
LOOP
-- Retrieve information for the next student
FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

-- Process the fetched rows, in this case sign up each
-- student for History 301 by inserting them into the
-- registered_students table. Record the first and last
-- names in temp_table as well.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentID, 'HIS', 301);

INSERT INTO temp_table (num_col, char_col)
VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);

-- Exit loop when there are no more rows to fetch
EXIT WHEN c_HistoryStudents%NOTFOUND;

END LOOP;

-- Free resources used by the cursor
CLOSE c_HistoryStudents;

-- Commit our work
COMMIT;
END;
/

[beckhambobo]

create procedure or replace update_form
(
date1 date,
date2 date
) as
st varchar2(10);
begin
fro sss in (select ppp from formtable where pdate between
date1 and date2) loop
update kkform set forma=sss.ppp;
end loop;
end;
/

[nicholaz]

不要加declare

[fenglaile]

我把declare 放在begin 之前就会出错