create function a(a1 varchar2,a2 varchar2,a3 varchar2,a4 varchar2) return number is num number; begin execute immediate 'select '||a1||'+'||a2||'+'||a3||'+'||a4||' from table' into num; return num; end;select a(col1,col2,col3,col4) from dual; 是这样的吗?
TO bzszp(SongZip) 知道列名,可是列名不固定但是有规律比如是tar1,tar2......这样相加!TO wfeng7907(无风) 如果是对表操作,我找到答案,但现在是对一条记录操作,我就不会了
return number
is
num number;
begin
execute immediate 'select '||a1||'+'||a2||'+'||a3||'+'||a4||' from table' into num;
return num;
end;select a(col1,col2,col3,col4) from dual;
是这样的吗?
知道列名,可是列名不固定但是有规律比如是tar1,tar2......这样相加!TO wfeng7907(无风)
如果是对表操作,我找到答案,但现在是对一条记录操作,我就不会了
EXECUTE IMMEDIATE 'total := lv_rec.tar1+lv_rec.tar2+lv_rec.tar3+lv_rec.tar4';
可是编译不能通过
:(SQL> declare
2 v_temp test%rowtype;
3 v_num number;
4 begin
5 select * into v_temp from test where rownum=1;
6 execute immediate 'begin
7 :1:=:2.tar1+:2.tar2+:2.tar3+:2.tar4;
8 end;' into v_num using v_num,v_temp;
9 dbms_output.put_line(v_num);
10 end;
11 /
end;' into v_num using v_num,v_temp;
*
ERROR 位于第 8 行:
ORA-06550: 第 8 行, 第 30 列:
PLS-00457: 表达式必须是 SQL 类型
ORA-06550: 第 6 行, 第 1 列:
PL/SQL: Statement ignored
已用时间: 00: 00: 00.50
SQL> declare
2 v_temp test%rowtype;
3 v_num number;
4 begin
5 select * into v_temp from test where rownum=1;
6 execute immediate 'declare
7 v_test test%rowtype;
8 begin
9 v_test:=:2;
10 :1:=v_test.tar1+v_test.tar2;
11 end;' into v_num using v_num,v_temp;
12 dbms_output.put_line(v_num);
13 end;
14 /
end;' into v_num using v_num,v_temp;
*
ERROR 位于第 11 行:
ORA-06550: 第 11 行, 第 30 列:
PLS-00457: 表达式必须是 SQL 类型
ORA-06550: 第 6 行, 第 1 列:
PL/SQL: Statement ignored
已用时间: 00: 00: 00.40
谢谢您的测试!
难道真的对记录没有什么办法了?
2 v_a tableaaa%rowtype;
3 begin
4 execute immediate 'select * from tableaaa where rownum=1' into v_a;
5 dbms_output.put_line(v_a.total_charge);
6 end;
7 /44PL/SQL procedure successfully completedSQL>
SQL> declare
2 v_a tableaaa%rowtype;
3 begin
4 execute immediate 'select * from tableaaa where rownum=1' into v_a;
5 dbms_output.put_line(v_a.total_charge+v_a.current_charge);
6 end;
7 /88PL/SQL procedure successfully completed
上面这样可以实现啊
2 v_a star_16366%rowtype;
3 v_b number;
4 begin
5 execute immediate 'select * from star_16366 where rownum=1' into v_a;
6 execute immediate 'select '||v_a.total_charge||'+'||v_a.current_charge||' from dual' into v_b;
7 dbms_output.put_line(v_b);
8 end;
9 /88PL/SQL procedure successfully completed这样也可以的。
要改成
EXECUTE IMMEDIATE 'select '||lv_rec.tar1||'+'||lv_rec.tar2||'+'||lv_rec.tar3||'+'||lv_rec.tar4||' from dual';
这样做是可以执行
但是不能够动态,构造lv_rec.tar1||'+'||lv_rec.tar2||'+'||lv_rec.tar3||
因为他们不是字符串