做一个视图,把长语句固定在视图内,CREATE OR REPLACE VIEW v_a AS SELECT str1, str2, str3+str4+..+str300 as stradd FROM a;这样以后只要执行 SELECT str1, str2, stradd FROM v_a 就可以了
declare sqlstr varchar2(4000):='create view v_a as '; begin for i in 1..300 loop sqlstr:=sqlstr||'str'||i||'+'; end loop; sqlstr:=rtrim(sqlstr,'+')||' as stradd from a'; execute immediate sqlstr;end; 试试看
上边定义的create.. as 后面添上 select 后面要留个空格
ths,发现sql的功能还是不够强大,oracle也是,碰到个dblink中select远程blob的问题,竟然select不行,但是却能create .. as select弄到本地来,奇怪的呀,oracle!
不,我只是想缩短这个语句,我把这个语句存到某blob中报错,太长!
这个语句的长度应该是2000多,varchar2(3000)就够存了
在pl/sql里用循环来拼
AS
SELECT str1, str2, str3+str4+..+str300 as stradd
FROM a;这样以后只要执行
SELECT str1, str2, stradd FROM v_a
就可以了
sqlstr varchar2(4000):='create view v_a as ';
begin
for i in 1..300 loop
sqlstr:=sqlstr||'str'||i||'+';
end loop;
sqlstr:=rtrim(sqlstr,'+')||'
as stradd from a';
execute immediate sqlstr;end;
试试看
后面要留个空格