create or replace function getmyanswer4(request varchar2) return varchar2 is
response varchar2(50); cursor que is
select * from question;
cursor ans is
select * from answer; v_que que%rowtype;
v_ans ans%rowtype;
Deadlock_detected exception;
Pragma exception_init(Deadlock_detected, -06503);BEGIN
OPEN que;
LOOP
EXIT WHEN que%NOTFOUND;
fetch que
into v_que;
IF v_que.q_content = request THEN
OPEN ans;
Loop
EXIT WHEN ans%NOTFOUND;
fetch ans
into v_ans;
if v_ans.q_id = v_que.q_id then
response := v_ans.a_content;
return(response);
end if;
end loop;
close ans;
end if;
end loop;
close que;
exception
when Deadlock_detected then
response:='no data found';
dbms_output.put_line('aaaaaa');
return (response);
when others then
dbms_output.put_line('bbbbbb');
response:='error';
return (response);
end;
response varchar2(50); cursor que is
select * from question;
cursor ans is
select * from answer; v_que que%rowtype;
v_ans ans%rowtype;
Deadlock_detected exception;
Pragma exception_init(Deadlock_detected, -06503);BEGIN
OPEN que;
LOOP
EXIT WHEN que%NOTFOUND;
fetch que
into v_que;
IF v_que.q_content = request THEN
OPEN ans;
Loop
EXIT WHEN ans%NOTFOUND;
fetch ans
into v_ans;
if v_ans.q_id = v_que.q_id then
response := v_ans.a_content;
return(response);
end if;
end loop;
close ans;
end if;
end loop;
close que;
exception
when Deadlock_detected then
response:='no data found';
dbms_output.put_line('aaaaaa');
return (response);
when others then
dbms_output.put_line('bbbbbb');
response:='error';
return (response);
end;
解决方案 »
- 如何导出oracel数据脚本!!!!。。。。。。。
- distinct 取消重复行 是什么意思
- Oracle允许执行存储过程但不能查看或导出其定义
- Oracle10 急救问题
- oralce的datediff问题。
- 在oracle中如何调用存储过程
- 通过IMP/EXP将从低版本ORACLE导出的数据导入高版本ORACLE数据库的问题
- 如何在注册表修改 Oracle 客户端的字符集?
- 总是显示栏位的错误invalid column name,本来很简单的问题,却不能显示,大家来救命!在线等!!!
- 在Oracle 9i中,执行SELECT * FROM user_tab_columns,为什么不返回用户表的定义?
- 视图更新问题
- 在Oracle中,怎么解决动态的增加列和所对应的值
fetch ans
into v_ans;
这两条语句换一下吧!
response varchar2(50); cursor que is
select * from question;
cursor ans is
select * from answer; v_que que%rowtype;
v_ans ans%rowtype;
Deadlock_detected exception;
Pragma exception_init(Deadlock_detected, -06503);BEGIN
OPEN que;
LOOP
fetch que into v_que;
EXIT WHEN que%NOTFOUND; ----这句放在fetch语句之后
IF v_que.q_content = request THEN
OPEN ans;
Loop
fetch ans into v_ans;
EXIT WHEN ans%NOTFOUND; ----这句放在fetch语句之后
if v_ans.q_id = v_que.q_id then
response := v_ans.a_content;
return(response);
end if;
end loop;
close ans;
end if;
end loop;
close que;
exception
when Deadlock_detected then
response:='no data found';
dbms_output.put_line('aaaaaa');
return (response);
when others then
dbms_output.put_line('bbbbbb');
response:='error';
return (response);
end;
就是自定义异常上面的问题 函数无返回是OR-06503啊
搞不懂啊!~
后来不用这种方式自定义了
v_exception exception;
if response is null then
raise v_exception;
else
return ('the answer is '||response);
声明了异常变量,然后if判断就可以了
fetch que
into v_que;
应该是这样的啊,不然如果游标一行都没有呢?还去fetch啊?