1楼只是告诉你怎么个思路 其实稍微变通一下就好了 declare bb varchar2(10); bb1 number; begin select a into bb from abc; execute immediate 'select ' || bb || ' from dual' into bb1; update abc set b = bb1; commit; end;
可以啊,测试如下: CREATE TABLE ttt (n VARCHAR2(100)); INSERT INTO ttt VALUES('1*2*3'); INSERT INTO ttt VALUES('1*4*3');DECLARE VV NUMBER; xx VARCHAR2(100); BEGIN EXECUTE IMMEDIATE 'select n from ttt where rownum=1 ' INTO xx; EXECUTE IMMEDIATE 'SELECT '||xx||' FROM dual' INTO VV; DBMS_OUTPUT.PUT_LINE(VV); update tt set itmstd=vv; commit; END;
也可以把这个做成一个函数,然后调用。思路大体类似。 CREATE OR REPLACE function fun1(inexp varchar2) return number as v number; begin execute immediate 'select '||inexp||' from dual' into v; return v; end; /然后直接调用就行 create table TestTable (a,b) as select '3*4',5 from dualupdate TestTable set b = fun1(a)
declare
vv number;
begin
execute immdediate 'SELECT 1*2*6 FROM dual' into vv;
update tt set itmstd=vv;
commit;
end;
请问1楼这是从dual取回来的1*2*3,数据类型就是number的
上面你写的那个没有错误
但是当我建立了一个表表中有个字段是varchar2型并且
赋为'1*2*3' 的时候
在用你写的那个就不对了
请教下该怎么写?
其实稍微变通一下就好了
declare
bb varchar2(10);
bb1 number;
begin
select a into bb from abc;
execute immediate 'select ' || bb || ' from dual' into bb1;
update abc set b = bb1;
commit;
end;
可以啊,测试如下:
CREATE TABLE ttt (n VARCHAR2(100));
INSERT INTO ttt VALUES('1*2*3');
INSERT INTO ttt VALUES('1*4*3');DECLARE
VV NUMBER;
xx VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'select n from ttt where rownum=1 ' INTO xx;
EXECUTE IMMEDIATE 'SELECT '||xx||' FROM dual' INTO VV;
DBMS_OUTPUT.PUT_LINE(VV);
update tt set itmstd=vv;
commit;
END;
CREATE OR REPLACE function fun1(inexp varchar2) return number
as
v number;
begin
execute immediate 'select '||inexp||' from dual' into v;
return v;
end;
/然后直接调用就行
create table TestTable
(a,b)
as
select '3*4',5 from dualupdate TestTable
set b = fun1(a)