函数名作为表的一个字段,如何通过这个字段调函数? id funName1 'fun_add'2 'fun_sub'....查询后得到的funName,如何调真正的fun_XXX函数? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 动态SQLdeclaretemp varchar(50);beginselect funname into temp from test where id=1;execute immediate 'select '||temp||'() from dual';end 给你的例子:CREATE TABLE FUN_TABLE (FID NUMBER,FUNNAME VARCHAR2(50));INSERT INTO FUN_TABLE VALUES(1,'UPDATED_NAME');COMMIT;SQL> SELECT * FROM FUN_TABLE; FID FUNNAME---------- -------------------------------------------------- 1 UPDATED_NAMESQL> SELECT * FROM SUNPING;NAME PASS USERADD---------- ---------- ----------AAA 11 XFBBB 22 MANTIS333 33 ORACLE444 44 DWH555 55 DI-- 要用到的FUNCTION(UPDATED_NAME):CREATE OR REPLACE FUNCTION UPDATED_NAME RETURN VARCHAR2 IS USERADDNEW VARCHAR2(20);BEGIN SELECT USERADD INTO USERADDNEW FROM SUNPING WHERE PASS = '33'; RETURN USERADDNEW;END;-- 存储过程调用代码:CREATE OR REPLACE PROCEDURE GET_FUN_RESULT(FUN_ID IN NUMBER) IS CUR_FUN SYS_REFCURSOR; FUN_NAME FUN_TABLE.FUNNAME%TYPE; V_EXAMPLE VARCHAR2(10); BEGIN SELECT FUNNAME INTO FUN_NAME FROM FUN_TABLE WHERE FID = FUN_ID; OPEN CUR_FUN FOR 'SELECT '||FUN_NAME||'() FROM DUAL'; FETCH CUR_FUN INTO V_EXAMPLE; DBMS_OUTPUT.PUT_LINE('VALUE: '||V_EXAMPLE); IF CUR_FUN%ISOPEN THEN CLOSE CUR_FUN; END IF; END;SQL> SELECT UPDATED_NAME() FROM DUAL;UPDATED_NAME()--------------------------------------------------------------------------------ORACLESQL> EXEC GET_FUN_RESULT(1);VALUE: ORACLEPL/SQL procedure successfully completedSQL> execute immediate 'select '||函数名||'() from 任意表' sqlstr:='select ' ||funName||'() from dual';execute immediate sqlstr; BIEE 11G 的企业安装和简单安装在功能上有何区别 大家帮助分析一下oracle 11g EM无法启动 求一函数 oracle存储过程传值问题 请教一个sql问题高手请进 如何在储发器中调用存储过程??? LOB存储问题 oracle数据库字段内含字符“‘”的问题 安装oracle软件?? 急需SQL语句、在线等、100分全送 Oracle 10g包括的几个独立安装的CD都是什么用处? 关于查询上的一个性能问题
declare
temp varchar(50);
begin
select funname into temp from test where id=1;
execute immediate 'select '||temp||'() from dual';
end
INSERT INTO FUN_TABLE VALUES(1,'UPDATED_NAME');COMMIT;
SQL> SELECT * FROM FUN_TABLE; FID FUNNAME
---------- --------------------------------------------------
1 UPDATED_NAMESQL> SELECT * FROM SUNPING;NAME PASS USERADD
---------- ---------- ----------
AAA 11 XF
BBB 22 MANTIS
333 33 ORACLE
444 44 DWH
555 55 DI-- 要用到的FUNCTION(UPDATED_NAME):
CREATE OR REPLACE FUNCTION UPDATED_NAME RETURN VARCHAR2 IS
USERADDNEW VARCHAR2(20);
BEGIN
SELECT USERADD
INTO USERADDNEW
FROM SUNPING
WHERE PASS = '33';
RETURN USERADDNEW;
END;-- 存储过程调用代码:
CREATE OR REPLACE PROCEDURE GET_FUN_RESULT(FUN_ID IN NUMBER) IS
CUR_FUN SYS_REFCURSOR;
FUN_NAME FUN_TABLE.FUNNAME%TYPE;
V_EXAMPLE VARCHAR2(10);
BEGIN
SELECT FUNNAME
INTO FUN_NAME
FROM FUN_TABLE
WHERE FID = FUN_ID;
OPEN CUR_FUN FOR 'SELECT '||FUN_NAME||'() FROM DUAL';
FETCH CUR_FUN INTO V_EXAMPLE;
DBMS_OUTPUT.PUT_LINE('VALUE: '||V_EXAMPLE);
IF CUR_FUN%ISOPEN THEN
CLOSE CUR_FUN;
END IF;
END;
SQL> SELECT UPDATED_NAME() FROM DUAL;UPDATED_NAME()
--------------------------------------------------------------------------------
ORACLESQL> EXEC GET_FUN_RESULT(1);VALUE: ORACLEPL/SQL procedure successfully completedSQL>
execute immediate sqlstr;