有两张表 question1 和 answer1
表的结构如下
question1(Q_ID为primary key):
Q_ID Q_CONTENT
1 100+100
2 100-100
3 100*100
answer1(A_ID为primary key,Q_ID为foreign key):
A_ID A_CONTENT Q_ID
1 10 2
2 20 1
现使用游标编写函数，输入为一字符串，如果与Q_CONTENT相同，则输出相应的A_CONTENT
1 create or replace function getanswer(Content in varchar2) return varchar2 is
2 Res varchar2(50);
3 r_que question1%rowtype;
4 r_ans answer1%rowtype;
5 cursor c_que is select * from question1 q;
6 cursor c_ans is select * from answer1 a;
7 e_que_ans exception;
8
9 begin
10 open c_que;
11 loop
12 exit when c_que%notfound;
13 fetch c_que into r_que;
14 if r_que.q_content = Content then
15 open c_ans;
16 loop
17 exit when c_ans%notfound;
18 fetch c_ans into r_ans;
19 if r_que.q_id=r_ans.q_id then
20 Res := r_ans.a_content;
21 end if;
22 end loop;
23 close c_ans;
24 end if;
25 end loop;
26 close c_que;
27
28 if Res is null then
29 raise e_que_ans;
30 else
31 return (Res);
32 end if;
33
34
35
36 exception
37 when e_que_ans then
38 Res := 'No Answer!';
39 return(Res);
40 end getanswer;
create or replace function getanswer1(Content in varchar2) return varchar2 is
Res varchar2(50);
q_q_id number;
a_q_id number;
q_q_content varchar2(50);
a_a_content varchar2(50);
cursor c_que is
select q.q_id,q.q_content from question1 q;
cursor c_ans is
select a.q_id,a.a_content from answer1 a;
begin
open c_que;
loop
fetch c_que into q_q_id,q_q_content;
if q_q_content = Content then
open c_ans;
loop
fetch c_ans into a_q_id,a_a_content;
if q_q_id = a_q_id then
Res := a_a_content;
end if;
exit when c_ans%notfound;
end loop;
close c_ans;
end if;
exit when c_que%notfound;
end loop;
close c_que;
return(Res);
end getanswer1;
create or replace function getanswer1(request varchar2)
return varchar2 is
response varchar2(50);
type question_recordx is record(v_q_content varchar2(50),v_q_id number);
type answer_recordx is record(v_id number,v_content varchar2(50));
question_record question_recordx;
answer_record answer_recordx;
type que_array is table of question_recordx;
type ans_array is table of answer_recordx;
q_arr que_array := que_array();
a_arr ans_array := ans_array();
CURSOR request_1 IS
SELECT q_content,q_id FROM question1;
cursor response_1 is
select q_id,a_content from answer1;
BEGIN
OPEN request_1;
LOOP
FETCH request_1 INTO question_record;
q_arr.extend;
q_arr(1):=question_record;
IF q_arr(1).v_q_content=request THEN
OPEN response_1;
Loop
fetch response_1 into answer_record;
a_arr.extend;
a_arr(1):=answer_record;
if a_arr(1).v_id=q_arr(1).v_q_id then
response:=a_arr(1).v_content;
end if;
EXIT WHEN response_1%NOTFOUND;
end loop;
close response_1;
end if;
EXIT WHEN request_1%NOTFOUND;
end loop;
close request_1;
return (response);
end ;
create or replace function getanswer1(request varchar2) return varchar2 is
response varchar2(50);
type que_type is ref cursor return question1%rowtype;
type ans_type is ref cursor return answer1%rowtype;
v_que que_type;
v_ans ans_type;
v_que1 question1%rowtype;
v_ans1 answer1%rowtype;
BEGIN
OPEN v_que for select * from question1;
LOOP
fetch v_que into v_que1;
IF v_que1.q_content = request THEN
OPEN v_ans for select * from answer1 ;
Loop
fetch v_ans into v_ans1;
if v_ans1.q_id = v_que1.q_id then
response := v_ans1.a_content;
end if;
EXIT WHEN v_ans%NOTFOUND;
end loop;
close v_ans;
end if;
EXIT WHEN v_que%NOTFOUND;
end loop;
close v_que;
return (response);
end;
1 create or replace function test1(Require in varchar2) return varchar2 is
2 Res varchar2(50);
3
4 type t_que is record
5 (q_q_id question1.q_id%type,
6 q_q_content question1.q_content%type
7 );
8
9 type t_ans is record
10 (a_q_id answer1.q_id%type,
11 a_a_content answer1.a_content%type
12 );
13
14 type r_que is ref cursor return t_que;
15 type r_ans is ref cursor return t_ans;
16
17 rr_que r_que;
18 rr_ans r_ans;
19 que t_que;
20 ans t_ans;
21
22 e_que_ans exception;
23
24 begin
25 open rr_que for
26 select q.q_id,q.q_content
27 from question1 q;
28 loop
29 fetch rr_que into que;
30 exit when rr_que%notfound;
31 if que.q_q_content=Require then
32 open rr_ans for
33 select a.q_id,a.a_content
34 from answer1 a;
35 loop
36 fetch rr_ans into ans;
37 exit when rr_ans%notfound;
38 if ans.a_q_id=que.q_q_id then
39 Res := ans.a_a_content;
40 end if;
41 end loop;
42 close rr_ans;
43 end if;
44 end loop;
45 close rr_que;
46
47 if Res is null then
48 raise e_que_ans;
49 else
50 return (Res);
51 end if;
52
53 exception
54 when e_que_ans then
55 Res := 'No Answer!';
56 return(Res);
57
58 end test1;
1 create or replace function test1(Require in varchar2) return varchar2 is
2 Res varchar2(50);
3
4 cursor cursor_question is
5 select q.q_id, q.q_content from question1 q;
6
7 cur_q cursor_question%rowtype;
8
9 cursor cursor_ans is
10 select a.q_id, a.a_content from answer1 a;
11
12 cur_a cursor_ans%rowtype;
13
14 e_que_ans exception;
15
16 begin
17 Res := 'wu';
18 open cursor_question;
19
20 loop
21 fetch cursor_question
22 into cur_q;
23 exit when cursor_question%notfound;
24 if cur_q.q_content = Require then
25 open cursor_ans;
26
27 loop
28 fetch cursor_ans
29 into cur_a;
30 exit when cursor_ans%notfound;
31 if cur_a.q_id = cur_q.q_id then
32 Res := cur_a.a_content;
33 exit;
34 end if;
35 end loop;
36 close cursor_ans;
37 end if;
38 end loop;
39 close cursor_question;
40 return(Res);
41
42 end test1;
PL/SQL都快忘记了，今天实习第一天要用这个，顿时觉得囧大了。这些权当温习了
