本人新学oracle,请教大家关于oracle 的异常处理:通过id 查询学生 姓名,如果表中不存在这样的记录返回填入的id, 但如果有人填入姓名,在这种情况下直接返回填入的姓名。
我是这样写的,但编译都过不去。CREATE OR REPLACE FUNCTION F_GET_NAME_BY_ID(p_user_id in varchar2)
RETURN varchar2 as
v_name varchar2(100);
v_date date;
v_id NUMBER;BEGIN
-- 开始异常处理
BEGIN
v_id:=to_number(p_user_id);--将输入值转换成数字。
SELECT MAX(T.TIME)--第一个select 语句查询数据库中是否存在记录
INTO v_date
FROM T_STUDENT T
WHERE T.USER_ID =v_id;
IF v_date IS NULL THEN
RETURN p_user_id; --如果记录不存在返回输入的id
ELSE
SELECT S.NAME INTO v_name FROM T_STUDENT S WHERE S.USER_ID=p_user_id; --记录存在返回name
RETURN v_name;
END IF;
EXCEPTION
RETURN p_user_id; --有异常返回输入值。--编译就是在这里报错“Compilation errors for FUNCTION LRMEDBU.F_COM_GET_NAME_BY_ID -- Error: PLS-00103: Encountered the symbol "RETURN" when expecting one of the following pragma when
Line: 22
Text: RETURN p_user_id;Error: PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
Line: 25
Text: end F_COM_GET_NAME_BY_ID;
”
END;
end F_GET_NAME_BY_ID;我这样写比较繁琐,请大家告诉我正确的写法,谢谢!另外也请大家帮我推荐一本写oracle的书籍,谢谢!
我是这样写的,但编译都过不去。CREATE OR REPLACE FUNCTION F_GET_NAME_BY_ID(p_user_id in varchar2)
RETURN varchar2 as
v_name varchar2(100);
v_date date;
v_id NUMBER;BEGIN
-- 开始异常处理
BEGIN
v_id:=to_number(p_user_id);--将输入值转换成数字。
SELECT MAX(T.TIME)--第一个select 语句查询数据库中是否存在记录
INTO v_date
FROM T_STUDENT T
WHERE T.USER_ID =v_id;
IF v_date IS NULL THEN
RETURN p_user_id; --如果记录不存在返回输入的id
ELSE
SELECT S.NAME INTO v_name FROM T_STUDENT S WHERE S.USER_ID=p_user_id; --记录存在返回name
RETURN v_name;
END IF;
EXCEPTION
RETURN p_user_id; --有异常返回输入值。--编译就是在这里报错“Compilation errors for FUNCTION LRMEDBU.F_COM_GET_NAME_BY_ID -- Error: PLS-00103: Encountered the symbol "RETURN" when expecting one of the following pragma when
Line: 22
Text: RETURN p_user_id;Error: PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
Line: 25
Text: end F_COM_GET_NAME_BY_ID;
”
END;
end F_GET_NAME_BY_ID;我这样写比较繁琐,请大家告诉我正确的写法,谢谢!另外也请大家帮我推荐一本写oracle的书籍,谢谢!
WHEN OTHERS
THEN
RETURN p_user_id;
-- 开始异常处理
BEGIN 多了一个begin
WHEN exception1 THEN
code1...;
WHEN exception2 THEN
code2...;
WHEN OTHERS THEN
code3...;
END;照1楼改改就行了。