非常感谢 ,我原来只在实参上下功夫 ,没有考虑形参。但 b in (r); -------------------- b in (substr(r,0,instr(r,',')-1),substr(r,instr(r,',')+1)) 仅这样使用,还是不行. 因为 open c1('aa,bb'); 中'aa,bb' 是动态产生的 也可能是'aa,bb,cc' 或 'bb' 都有可能。—————————————————— 'aa,bb' 就是 r , 它是这样产生的。 r := field; i := 1; WHILE i < 10 LOOP SELECT REPLACE(r, ' ', ' ') INTO r FROM DUAL; i := i + 1; END LOOP; SELECT REPLACE(r, ' ', ',') INTO r FROM DUAL; —————————————————— 所以 我还是不会改。
你用动态SQL啊,这样的话,只要这样 'CURSOR c1 (r varchar2) IS select a from tt where b in ('||r||')' 这样的话,也不需要拆分了
'CURSOR c1 (r varchar2) IS select a from tt where b in ('||r||')' 这样做也不好用, 我曾试过如下的写法 但也不行 。真是 费尽。CREATE OR REPLACE FUNCTION s(tab IN VARCHAR2,field in varchar2) RETURN VARCHAR2 IS r_emp emp%rowtype; type c_type is ref cursor; c1 c_type; BEGIN r := field; i := 1; WHILE i < 10 LOOP SELECT REPLACE(r, ' ', ' ') INTO r FROM DUAL; i := i + 1; END LOOP; SELECT REPLACE(r, ' ', ',') INTO r FROM DUAL; open c1 for 'select a from tt where b in (:1)' using r; loop fetch c1 into r_emp; exit when c1%notfound; r:=r || r_emp || ','; end loop; close c1; RETURN(r); END s;
CREATE OR REPLACE FUNCTION s(tab IN VARCHAR2, field in varchar2) RETURN VARCHAR2 IS r VARCHAR2(3000); x VARCHAR2(3000); BEGIN r := field; x :=''; for c in (select a from tt where b in (r)) loop x := x || c.a || ','; end loop; RETURN(x); END s;
CREATE OR REPLACE FUNCTION s(tab IN VARCHAR2, field in varchar2) RETURN VARCHAR2 IS r VARCHAR2(3000); x VARCHAR2(3000);
TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec tt%ROWTYPE; sql_stmt VARCHAR2(200); my_sql VARCHAR2(1000) ; BEGIN r := field; while instr(r,',',1) > 0 loop if length(my_sql) > 0 then my_sql := my_sql||' union all select * from tt where b = '''||substr(r,1,instr(r,',',1)-1)||''''; else my_sql := 'select * from tt where b = '''||substr(r,1,instr(r,',',1)-1)||''''; end if; r := substr(r,instr(r,',',1)+1,length(r)-instr(r,',',1)); end loop; if instr(field,',',1) > 0 then my_sql := my_sql||' union all select * from tt where b = '''||r||''''; else my_sql := 'select * from tt where b = '''||r||''''; end if; sql_stmt := my_sql;
OPEN emp_cv FOR sql_stmt; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; x :=x||emp_rec.a||','; END LOOP; CLOSE emp_cv; RETURN(x); END s;
谢谢参与 r := field; for c in (select a from tt where b in (r)) 你用的这个方法,真的很帅。我没用过,学一招。我现在的 r := 'aa,bb' r 的取值有语法错误 。即select a from tt where b in ('aa,bb') 若写成 select a from tt where b in ('aa','bb') ok 但要写成 这样 r 的取值 我还是不会。 总不能这样写吧 r := 'aa','bb' :) wo yao feng le
b in (r);
--------------------
b in (substr(r,0,instr(r,',')-1),substr(r,instr(r,',')+1))
仅这样使用,还是不行.
因为 open c1('aa,bb'); 中'aa,bb' 是动态产生的 也可能是'aa,bb,cc' 或 'bb' 都有可能。——————————————————
'aa,bb' 就是 r , 它是这样产生的。 r := field;
i := 1;
WHILE i < 10 LOOP
SELECT REPLACE(r, ' ', ' ') INTO r FROM DUAL;
i := i + 1;
END LOOP;
SELECT REPLACE(r, ' ', ',') INTO r FROM DUAL;
——————————————————
所以 我还是不会改。
'CURSOR c1 (r varchar2) IS select a from tt where b in ('||r||')'
这样的话,也不需要拆分了
这样做也不好用, 我曾试过如下的写法 但也不行 。真是 费尽。CREATE OR REPLACE FUNCTION s(tab IN VARCHAR2,field in varchar2) RETURN VARCHAR2 IS
r_emp emp%rowtype;
type c_type is ref cursor;
c1 c_type;
BEGIN
r := field;
i := 1;
WHILE i < 10 LOOP
SELECT REPLACE(r, ' ', ' ') INTO r FROM DUAL;
i := i + 1;
END LOOP;
SELECT REPLACE(r, ' ', ',') INTO r FROM DUAL; open c1 for 'select a from tt where b in (:1)' using r; loop
fetch c1 into r_emp;
exit when c1%notfound;
r:=r || r_emp || ',';
end loop; close c1; RETURN(r);
END s;
RETURN VARCHAR2 IS
r VARCHAR2(3000);
x VARCHAR2(3000);
BEGIN
r := field;
x :='';
for c in (select a from tt where b in (r))
loop
x := x || c.a || ',';
end loop;
RETURN(x);
END s;
RETURN VARCHAR2 IS
r VARCHAR2(3000);
x VARCHAR2(3000);
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec tt%ROWTYPE;
sql_stmt VARCHAR2(200);
my_sql VARCHAR2(1000) ;
BEGIN
r := field;
while instr(r,',',1) > 0
loop
if length(my_sql) > 0 then
my_sql := my_sql||' union all select * from tt where b = '''||substr(r,1,instr(r,',',1)-1)||'''';
else
my_sql := 'select * from tt where b = '''||substr(r,1,instr(r,',',1)-1)||'''';
end if;
r := substr(r,instr(r,',',1)+1,length(r)-instr(r,',',1));
end loop;
if instr(field,',',1) > 0 then
my_sql := my_sql||' union all select * from tt where b = '''||r||'''';
else
my_sql := 'select * from tt where b = '''||r||'''';
end if;
sql_stmt := my_sql;
OPEN emp_cv FOR sql_stmt;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
x :=x||emp_rec.a||',';
END LOOP;
CLOSE emp_cv;
RETURN(x);
END s;
r := field;
for c in (select a from tt where b in (r))
你用的这个方法,真的很帅。我没用过,学一招。我现在的 r := 'aa,bb' r 的取值有语法错误 。即select a from tt where b in ('aa,bb')
若写成 select a from tt where b in ('aa','bb') ok
但要写成 这样 r 的取值 我还是不会。 总不能这样写吧 r := 'aa','bb' :) wo yao feng le