关于循环处理，急，先谢谢了！

Question

Oracle比较菜，这个问题应该不难的，兄弟姐妹们帮忙看看哈，先谢过了！
我有一个用户信息表user:
usrid
usrbranchid
有一个用户信息国际化表user_nls;
usrid
usrname现在我要查询除了我自己外的同部门的所用用户
SELECT u.usrid FROM user u WHERE u.usrid!=my_usrid AND u.usrbranchid=my_branchid;然后我还有一个用户信息扩展表usrext:
usrid
extproname
extprovalue例如用户id=00888，在扩展信息表中的入职日期的extproname=INDATE，extprovalue=2009-10-01，专业资格的extproname=QULIFY，extprovalue为工程师；
要单个查询该用户的所有扩展信息不难：
SELECT ext.extprovalueINTO v_indate FROM usrext ext WHERE ext.usrid='00888' AND ext.extproname='INDATE'; 
SELECT ext.extprovalueINTO v_qulify FROM usrext ext WHERE ext.usrid='00888' AND ext.extproname='QULIFY'; 现在，我想先查询出除了我自己外的同部门的所用用户后，循环处理，去查询每个用户的扩展信息，放到一个自定义的结构中
  TYPE r_usrext_list IS RECORD( 
    usrid           user.usrid%TYPE, 
    usrname         user_nls.usrname%TYPE, 
    branchid        user.usrbranchid%TYPE, 
    qulify          usrext.extprovalue%TYPE);   TYPE ref_usrext_list IS REF CURSOR RETURN r_usrext_list; 应该怎么做呢？

Answer 1

没有必要什么type，你直接在你的存储过程里
for ref_usrext_list in (sql_statement) loop
 ...........to do...........
end loop;

Answer 2

不用循環，直接一個關聯查詢就好了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;

Answer 3

--这样可以吗？
SELECT  usrid,usrname,usrbranchid,extprovalue FROM USER a,user_nls b,usrext c
WHERE a.usrid=b.usrid AND a.usrid=c.usrid AND a.usrid!=my_usrid AND a.usrbranchid=my_branchid; 

Answer 4

SELECT u.usrid ,un.usrname,u.usrbranchid branchid,ue.extprovalue qulify
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';
这一句就能查出你想要循环出来的东西

Answer 5

谢谢各位，可能我没有表达清楚。在用户信息扩展表usrext:
usrid
extproname
extprovalue一个usrid，对应的是多个扩展属性，我要一次查出该用户的多有扩展属性呀！

Answer 6

例如用户id=00888，在扩展信息表中占用两条记录：
入职日期的extproname=INDATE，extprovalue=2009-10-01
专业资格的extproname=QULIFY，extprovalue为工程师；可能还有3条、4条，当然最终这个是确定的。如果有5、6个的话，也要支持，当然它们的extproname都是确定的。

Answer 7

等值连接取出来的是n条记录，n等于usrext表每个用户的扩展属性个数，而我需要的是：
我想先查询出除了我自己外的同部门的所用用户后，循环处理，去查询每个用户的扩展信息，放到一个自定义的结构中
  TYPE r_usrext_list IS RECORD( 
  usrid user.usrid%TYPE, 
  usrname user_nls.usrname%TYPE, 
  branchid user.usrbranchid%TYPE, 
  qulify usrext.extprovalue%TYPE);   TYPE ref_usrext_list IS REF CURSOR RETURN r_usrext_list;  

Answer 8

為什麼一定要循環呀？不是一個SQL就能select出來了嗎？如果非得要用的話
可以這樣
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;

Answer 9

SELECT u.usrid ,un.usrname,u.usrbranchid branchid,ue.extprovalue qulify
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';这一句就能查出你想要循环出来的东西啊，你要改动的只是红色的部分 
 

Answer 10

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;

Answer 11

CREATE OR REPLACE TYPE r_usrext_list AS OBJECT(  
  usrid           user.usrid%TYPE,  
  usrname         user_nls.usrname%TYPE,  
  branchid        user.usrbranchid%TYPE,  
  qulify          usrext.extprovalue%TYPE
  )  
/
CREATE OR REPLACE TYPE r_usrext_list_table AS TABLE OF r_usrext_list
/
  
CREATE OR REPLACE TYPE FUNCTION usrext_list_fun(inp_myusrid user.usrid%TYPE)
RETURN  r_usrext_list_table
PIPELINED AS
    CURSOR cur_user(my_usrid user.usrid%TYPE) IS
    SELECT usrid
    FROM   USER 
    WHERE  NOT EXISTS( SELECT * 
                       FROM USER 
                       WHERE user.usrid = my_usrid 
                       OR user.usrbranchid != ( SELECT user.usrbranchid 
                                                FROM   user 
                                                WHERE  user.usrid = my_usrid));
    
    user_record cur_user%ROWTYPE;
    
    CURSOR cur_usrext(v_usrid user.usrid%TYPE) IS
    SELECT user.usrid usrid,
           user_nls.usrname usrname,
           user.usrbranchid branchid,
           usrext.extprovalue qulify
    FROM   user, user_nls,usrext 
    WHERE  user.usrid = user_nls.usrid
    AND    user.usrid = usrext.usrid
    AND    user.usrid = v_usrid;
    
    usrext_record cur_userext%ROWTYPE;begin
    v_myUsrId = TRIM(inp_myusrid);
    
    
    FOR user_record IN cur_user(inp_myusrid)
    LOOP
    
        FOR usrext_record IN cur_usrext(user_record.usrid) 
        LOOP
        
        PIPE ROW(r_usrext_list(
             user_record.usrid,
             user_record.usrname,
             user_record.usrid,
             user_record.usrid
             )                   
        )
        
        END LOOP;
    END LOOP;END usrext_list_fun;
--大概写了一下，可能会有错，抛砖引玉了

Answer 12

------修改下里面的SQL就行了
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> 

Answer 13

--发现了错误：CREATE OR REPLACE TYPE r_usrext_list AS OBJECT(  
  usrid           user.usrid%TYPE,  
  usrname         user_nls.usrname%TYPE,  
  branchid        user.usrbranchid%TYPE,  
  qulify          usrext.extprovalue%TYPE
  )  
/
CREATE OR REPLACE TYPE r_usrext_list_table AS TABLE OF r_usrext_list
/
  
CREATE OR REPLACE TYPE FUNCTION usrext_list_fun(inp_myusrid user.usrid%TYPE)
RETURN  r_usrext_list_table
PIPELINED AS
    CURSOR cur_user(my_usrid user.usrid%TYPE) IS
    SELECT usrid
    FROM   USER 
    WHERE  NOT EXISTS( SELECT * 
                       FROM USER 
                       WHERE user.usrid = my_usrid 
                       OR user.usrbranchid != ( SELECT user.usrbranchid 
                                                FROM   user 
                                                WHERE  user.usrid = my_usrid));
    
    
    CURSOR cur_usrext(v_usrid user.usrid%TYPE) IS
    SELECT user.usrid usrid,
           user_nls.usrname usrname,
           user.usrbranchid branchid,
           usrext.extprovalue qulify
    FROM   user, user_nls,usrext 
    WHERE  user.usrid = user_nls.usrid
    AND    user.usrid = usrext.usrid
    AND    user.usrid = v_usrid;
    begin
    v_myUsrId = TRIM(inp_myusrid);
    
    
    FOR user_record IN cur_user(v_myUsrId)
    LOOP
    
        FOR usrext_record IN cur_usrext(user_record.usrid) 
        LOOP
        
        PIPE ROW(r_usrext_list(
             user_record.usrid,
             user_record.usrname,
             user_record.usrid,
             user_record.usrid
             )                   
        )
        
        END LOOP;
    END LOOP;END usrext_list_fun;