17,086
社区成员
发帖
与我相关
我的任务
分享
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';
--这样可以吗?
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;
--发现了错误:
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;
------修改下里面的SQL就行了
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> drop table users;
Table dropped
SQL>
SQL> create table users
2 (
3 usrid number,
4 usrbranchid number
5 );
Table created
SQL> drop table user_nls;
Table dropped
SQL>
SQL> create table user_nls
2 (
3 usrid number,
4 usrname varchar2(1000)
5 )
6 ;
Table created
SQL> drop table userext;
Table dropped
SQL>
SQL> create table userext
2 (
3 usrid number,
4 extproname varchar2(1000),
5 extprovalue varchar2(1000)
6 )
7 ;
Table created
SQL>
SQL> insert into scott.users values(1,2);
1 row inserted
SQL> insert into scott.user_nls values(1,'scott');
1 row inserted
SQL> insert into scott.userext values(1,'indate',to_char(sysdate));
1 row inserted
SQL> commit;
Commit complete
SQL> 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月-10
PL/SQL procedure successfully completed
SQL>
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;
--大概写了一下,可能会有错,抛砖引玉了
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;