有如下代码:declare
outStr long:='';
begin
select outStr||a.book_type_code||'@' into outStr from fa_book_controls a where to_number(substr(a.book_type_code,4,3)) between 101 and 105 and instr(book_type_code,'固定')>0;
print outStr;
end;此代码稍作修改(outStr||a.book_type_code||'@' into outStr ==》 outStr=outStr+a.book_type_code+‘@’)即可在SQL Server中直行通过,为什么在 Oracle中不能通过?求高人,我想通过这个查询,最后得到outStr这个变量是所有book_type_code 的连接串,Oracle下如何做?
outStr long:='';
begin
select outStr||a.book_type_code||'@' into outStr from fa_book_controls a where to_number(substr(a.book_type_code,4,3)) between 101 and 105 and instr(book_type_code,'固定')>0;
print outStr;
end;此代码稍作修改(outStr||a.book_type_code||'@' into outStr ==》 outStr=outStr+a.book_type_code+‘@’)即可在SQL Server中直行通过,为什么在 Oracle中不能通过?求高人,我想通过这个查询,最后得到outStr这个变量是所有book_type_code 的连接串,Oracle下如何做?
应该是varchar吧
declare
outStr long:='';
begin
select outStr||a.book_type_code||'@'
from fa_book_controls a
where (to_number(substr(a.book_type_code,4,3)) >= 101 )
and (to_number(substr(a.book_type_code,4,3)) <= 105 )
and (instr(book_type_code,'固定')>0)[/color] into outStr ;
dbms_ouput.put_line( outStr );
end;
declare
outStr long:='';
begin
select outStr||a.book_type_code||'@'
from fa_book_controls a
where (to_number(substr(a.book_type_code,4,3)) >= 101 )
and (to_number(substr(a.book_type_code,4,3)) <= 105 )
and (instr(book_type_code,'固定')>0)
into outStr ;
dbms_ouput.put_line( outStr );
end;
在select语句中给变量赋值,楼主的写法是对的,必须是select 字段 into 变量 from table
而不是 select 字段 from table into 变量
oracle中输出函数是dbms_output.put_line这个没错
定义类型用varchar2
outstr varchar2(20) := '';
declare
v_str varchar2(4000);
begin
for i in (select a.book_type_code from fa_book_controls a
where to_number(substr(a.book_type_code,4,3)) between 101 and 105
and instr(book_type_code,'固定')>0)
loop
v_str:=i.book_type_code||'@'||v_str;
end loop;
dbms_output.put_line(v_str);
end;--拿emp表举例
declare
v_str varchar2(4000);
begin
for i in (select ename from emp)
loop
v_str:=i.ename||'@'||v_str;
end loop;
dbms_output.put_line(v_str);
end;--结果如下
MILLER@FORD@JAMES@ADAMS@TURNER@KING@SCOTT@CLARK@BLAKE@MARTIN@JONES@WARD@ALLEN@SMITH@aspen@
outStr varchar2(3000):='';
begin
select a.book_type_code||'@'||outStr into outStr from fa_book_controls a where to_number(substr(a.book_type_code,4,3)) between 101 and 105 and instr(book_type_code,'固定')>0;
print outStr ;
end;
ORA-06550: 第 6 行, 第 11 列:
PLS-00103: 出现符号 "OUTSTR"在需要下列之一时:
:= . ( @ %
;
符号 ":=" 被替换为 "OUTSTR" 后继续。
我知道你这种办法,使用 游标循环。但是我现在想使用集合运算的方式快速得出结论,这样性能上会提高很多,而且代码量也会少很多。
在 SQL Server中这样做都没有问题的
不过你也可以用sql语句 select replace(wm_concat(ename),',','@')
from (select 1 no,ename from emp)
group by noREPLACE(WM_CONCAT(ENAME),',','@')
-------------------------------------------------------------------------------------------
aspen@SMITH@ALLEN@JONES@BLAKE@SCOTT@TURNER@JAMES@MILLER@FORD@ADAMS@KING@CLARK@MARTIN@WARD
declare
outStr clob:='';
beginselect replace(wm_concat(a.book_type_code),',','@') into outStr from fa_book_controls a where to_number(substr(a.book_type_code,4,3)) between 101 and 105 and instr(book_type_code,'固定')>0;dbms_output.put_line(outstr); end;
2 outStr clob:='';
3 begin
4
5 select replace(wm_concat(a.book_type_code),',','@') into outStr from fa_book_controls a where to_number(substr(a.book_type_code,4,3)) between 101 and 105 and instr(book_type_code,'固定')>0;
6
7 dbms_output.put_line(outstr);
8
9 end;
10 /
declare
outStr clob:='';
beginselect replace(wm_concat(a.book_type_code),',','@') into outStr from fa_book_controls a where to_number(substr(a.book_type_code,4,3)) between 101 and 105 and instr(book_type_code,'固定')>0;dbms_output.put_line(outstr); end;
ORA-06550: 第 6 行, 第 16 列:
PL/SQL: ORA-00904: "WM_CONCAT": 标识符无效
ORA-06550: 第 6 行, 第 1 列:
PL/SQL: SQL Statement ignoredWM_CONCAT 是个什么函数啊?我查了资料也没找到啊
用于把几列数据合并成一列显示