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;
解决方案 »
- 求解
- oracle 自关联表 根据i_parent_id排序
- 求oracle语句,查询出表相同的数据,然后删除,急求在线等
- 有关OEM的启动问题
- 关于 not in 里有 空值的处理方法
- 请问如何设置Oracle 的主机字符串
- 我在windows的服务里把oracle的几个服务改为手动,现在启动不了ORACLE了?
- 如何实现对一个用户下表的遍历循环复制?我按照书上的内容写的代码无法执行。
- 请问一个SQL查询的问题
- 数据库中我想在一个字段中存 ' 应该怎么写? 如:update table1 set a='abcd' 在ab后面想存一个'怎么写呢??
- 视图更新问题
- 在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啊?