原帖:
http://topic.csdn.net/u/20101115/11/f44f01f7-6c55-4d78-b8de-c843b5dd9794.html这个问题应该不难,请哪位兄弟帮忙看下,谢了!
http://topic.csdn.net/u/20101115/11/f44f01f7-6c55-4d78-b8de-c843b5dd9794.html这个问题应该不难,请哪位兄弟帮忙看下,谢了!
调试欢乐多
可以這樣
declare
c ref_usrext_list ;
begin
open c for
SELECT u.usrid ,un.usrname,u.usrbranchid,ue.extprovalue
FROM user u ,usrext ue,user_nls un
WHERE u.usrid!=my_usrid AND u.usrbranchid=my_branchid
and u.usrid = ue.usrid
and u.usrid = un.usrid;
end;
FROM user u ,usrext ue,user_nls un
WHERE u.usrid<>my_usrid AND u.usrbranchid=my_branchid
and u.usrid = ue.usrid
and u.usrid = un.usrid
and ue.extproname='QULIFY';
这样不行吗?红字部分拟可以指定extproname,如果是多个你就用OR关键字,我觉得这已经达到你的需求了啊
--怎么又发 分多
declare
TYPE r_usrext_list IS RECORD(
usrid user.usrid%TYPE,
branchid user.usrbranchid%TYPE,
usrname user_nls.usrname%TYPE,
proname usrext.extproname%TYPE,
qulify usrext.extprovalue%TYPE
);
r_userrec r_usrext_list;
type c1 is ref cursor;
begin
open c1 for select a.usrid,a.usrbranchid,b.usrname,c.extproname,c.extprovalue from user a,user_nls b,usrext c
where a.usrid!=my_usrid AND a.usrbranchid=my_branchid and a.usrid=b.usrid and a.usrid=c.usrid;
fetch c1 into r_userrec;
while c1%found loop
.....--做你循环的目标
fetch c1 into r_userrec;
end loop;
close c1;
end;
fetch c1 into r_userrec;
while c1%found loop
.....--做你循环的目标
fetch c1 into r_userrec;
楼上的,我真的看不懂,为什么两个fetch?
你试试看就知道了,那样取出来,1个用户id有n条记录!我需要的是1个用户id,一条记录,但是每条记录有n个列,这n个列就是usrext表中该用户对应的n行中的属性值!
loop
fetch c1 into r_userrec;.....--做你循环的目标
exit when c1%notfound;
end loop
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> drop table users;Table droppedSQL>
SQL> create table users
2 (
3 usrid number,
4 usrbranchid number
5 );Table createdSQL> drop table user_nls;Table droppedSQL>
SQL> create table user_nls
2 (
3 usrid number,
4 usrname varchar2(1000)
5 )
6 ;Table createdSQL> drop table userext;Table droppedSQL>
SQL> create table userext
2 (
3 usrid number,
4 extproname varchar2(1000),
5 extprovalue varchar2(1000)
6 )
7 ;Table createdSQL>
SQL> insert into scott.users values(1,2);1 row insertedSQL> insert into scott.user_nls values(1,'scott');1 row insertedSQL> insert into scott.userext values(1,'indate',to_char(sysdate));1 row insertedSQL> commit;Commit completeSQL> set serveroutput on;
SQL>
SQL> declare
2 TYPE r_usrext_list IS record(
3 usrid users.usrid%type,
4 usrname user_nls.usrname%TYPE,
5 branchid users.usrbranchid%TYPE,
6 extproname userext.extproname%TYPE,
7 qulify userext.extprovalue%TYPE);
8 --type res_usrext_list is table of r_usrext_list;
9 TYPE ref_usrext_list IS REF CURSOR RETURN r_usrext_list;
10 r_usrext_list_1 r_usrext_list;
11 cst_usrext_list ref_usrext_list;
12 begin
13 open cst_usrext_list for select a.usrid, b.usrname,a.usrbranchid, c.extproname, c.extprovalue
14 from users a, user_nls b, userext c
15 where a.usrid = b.usrid
16 and b.usrid = c.usrid;
17 LOOP
18 FETCH cst_usrext_list INTO r_usrext_list_1;
19 EXIT WHEN cst_usrext_list%NOTFOUND;
20 dbms_output.put_line('usrid:'||r_usrext_list_1.usrid);
21 dbms_output.put_line('usrname:'||r_usrext_list_1.usrname);
22 dbms_output.put_line('branchid:'||r_usrext_list_1.branchid);
23 dbms_output.put_line('extproname:'||r_usrext_list_1.extproname);
24 dbms_output.put_line('extprovalue:'||r_usrext_list_1.qulify);
25 END LOOP;
26 CLOSE cst_usrext_list;
27 end;
28 /usrid:1
usrname:scott
branchid:2
extproname:indate
extprovalue:15-11月-10PL/SQL procedure successfully completedSQL>
不要9条了,你试试看2条吧
1
scott
2
15-11月-10
高级工程师
...
最后一个属性值
2、即使这样设计,也可以不用游标来处理,使用游标较慢,尽量用SQL完成自已所要取到的数据。
那你还必须在类型r_usrext_list里再建立个数组啊就可以实现你的功能了。
你也没有一个用户有多个扩展信息。。我没考虑。