CREATE or REPLACE PROCEDURE set_death_age(name president.pres_name%TYPE, age NUMBER,message OUT VARCHAR2) IS BEGIN UPDATE PRESIDENT SET death_age=age where pres_name=name; message:='death age entered.'; --return message; EXCEPTION WHEN no_data_found THEN RAISE_APPLICATION_ERROR(-20004,'The president doesn''t exist!'); when OTHERS THEN RAISE_APPLICATION_ERROR(-20005,'The VALUE has already exist.'); end;
--函数中才能有return的 --试试这样看看 CREATE or REPLACE PROCEDURE set_death_age(name president.pres_name%TYPE, age NUMBER,message OUT VARCHAR2) IS BEGIN UPDATE PRESIDENT SET death_age=age where pres_name=name; message:='death age entered.'; commit; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('The president doesn''t exist!'); when OTHERS THEN dbms_output.put_line('The VALUE has already exist.'); end;
age NUMBER,message OUT VARCHAR2)
IS
BEGIN
UPDATE PRESIDENT
SET death_age=age
where pres_name=name;
message:='death age entered.';
--return message;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20004,'The president doesn''t exist!');
when OTHERS THEN
RAISE_APPLICATION_ERROR(-20005,'The VALUE has already exist.');
end;
存储过程是不能有返回值的,有返回值的是函数。看看楼主的SQL语句,确实最后缺少一个'END;'关键字哦。
--函数中才能有return的
--试试这样看看
CREATE or REPLACE PROCEDURE set_death_age(name president.pres_name%TYPE,
age NUMBER,message OUT VARCHAR2)
IS
BEGIN
UPDATE PRESIDENT
SET death_age=age
where pres_name=name;
message:='death age entered.';
commit;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('The president doesn''t exist!');
when OTHERS THEN
dbms_output.put_line('The VALUE has already exist.');
end;
就行了