CREATE OR REPLACE PROCEDURE prc_selTest (inPhone IN CHAR DEFAULT 'null', cityName OUT CHAR) AS
/*“OR REPLACE”自动删除已经存在的同名存储过程,“IN”输入参数,“OUT”输出参数*/
phoneNum VARCHAR(15);
BEGIN
IF (substr(inPhone,1,2)<>'86') THEN
phoneNum:='86' || inPhone;
ELSE
phoneNum:=inPhone;
END IF;
SELECT city_name
INTO cityName
FROM tb_phone_no
WHERE phone_no=substr(phoneNum,3,7);
EXCEPTION
WHEN no_data_found THEN /*未找到记录时的异常处理*/
DBMS_OUTPUT.PUT_LINE('INPUT: ' || inPhone || '. No data found for SELECT .. INTO!');
WHEN too_many_rows THEN /*查询到多条记录时的异常处理*/
DBMS_OUTPUT.PUT_LINE('Too many rows found for SELECT .. INTO!');
END prc_selTest;
select max(col_name) into v_col from ...;
if v_col is null then
'not found';
else
...
end if;
或者
begin
select .. into .. from ...;
...
exception when no_data_found then
...
end;