create or replace function mi(ss varchar2) return number is ss1 varchar2(100); nn number; begin ss1:=trim(ss,','); ss1:='select min('||ss1||') from dual'; execute immediate ss1 into nn; return nn; end; select mi(role) from table1
函数举例: CREATE OR REPLACE FUNCTION F_GET_MIN ( p_IN_STR IN VARCHAR2 ) RETURN VARCHAR2 IS v_MIN_STR VARCHAR2(50); v_TEMP_STR VARCHAR2(100) := SUBSTR(p_IN_STR,2,50); BEGIN IF v_TEMP_STR IS NULL THEN RETURN NULL; END IF; v_MIN_STR := SUBSTR(v_TEMP_STR,1,INSTR(v_TEMP_STR,',')-1); v_TEMP_STR := SUBSTR(v_TEMP_STR,INSTR(v_TEMP_STR,',')+1); LOOP EXIT WHEN v_TEMP_STR IS NULL; IF TO_NUMBER(v_MIN_STR) > TO_NUMBER(SUBSTR(v_TEMP_STR,1,INSTR(v_TEMP_STR,',')-1)) THEN v_MIN_STR := SUBSTR(v_TEMP_STR,1,INSTR(v_TEMP_STR,',')-1); END IF; v_TEMP_STR := SUBSTR(v_TEMP_STR,INSTR(v_TEMP_STR,',')+1); END LOOP; RETURN v_MIN_STR; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR:'||SQLERRM,1,255)); RETURN NULL; END F_GET_MIN;执行: SQL> select f_get_min(',1,2,23,32,12,5,') from dual;F_GET_MIN(',1,2,23,32,12,5,') -------------------------------------------------------------- 1
感谢楼上的sbaz,我这几天去试试看
to sbaz(万神渡劫): 修改后的程序如下 create or replace function mi(ss varchar2) return number is ss1 varchar2(100); nn number; begin ss1:=rtrim(ltrim(ss1,','),','); ss1:='select least('||ss1||') from dual'; execute immediate ss1 into nn; return nn; end; 已调试通过;
return number is
ss1 varchar2(100);
nn number;
begin
ss1:=trim(ss,',');
ss1:='select min('||ss1||') from dual';
execute immediate ss1 into nn;
return nn;
end;
select mi(role) from table1
CREATE OR REPLACE FUNCTION F_GET_MIN
(
p_IN_STR IN VARCHAR2
)
RETURN VARCHAR2
IS
v_MIN_STR VARCHAR2(50);
v_TEMP_STR VARCHAR2(100) := SUBSTR(p_IN_STR,2,50);
BEGIN
IF v_TEMP_STR IS NULL THEN
RETURN NULL;
END IF;
v_MIN_STR := SUBSTR(v_TEMP_STR,1,INSTR(v_TEMP_STR,',')-1);
v_TEMP_STR := SUBSTR(v_TEMP_STR,INSTR(v_TEMP_STR,',')+1);
LOOP
EXIT WHEN v_TEMP_STR IS NULL;
IF TO_NUMBER(v_MIN_STR) > TO_NUMBER(SUBSTR(v_TEMP_STR,1,INSTR(v_TEMP_STR,',')-1)) THEN
v_MIN_STR := SUBSTR(v_TEMP_STR,1,INSTR(v_TEMP_STR,',')-1);
END IF;
v_TEMP_STR := SUBSTR(v_TEMP_STR,INSTR(v_TEMP_STR,',')+1);
END LOOP;
RETURN v_MIN_STR;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR:'||SQLERRM,1,255));
RETURN NULL;
END F_GET_MIN;执行:
SQL> select f_get_min(',1,2,23,32,12,5,') from dual;F_GET_MIN(',1,2,23,32,12,5,')
--------------------------------------------------------------
1
修改后的程序如下
create or replace function mi(ss varchar2)
return number is
ss1 varchar2(100);
nn number;
begin
ss1:=rtrim(ltrim(ss1,','),',');
ss1:='select least('||ss1||') from dual';
execute immediate ss1 into nn;
return nn;
end;
已调试通过;
但还是对不起大家,我本来是想偷点懒的,一个语句想把它查出来的.但是其实还是实现不了的.
编译可以通过但调用就有问题了.可能在LEAST里面它还是把参数当字符串处理的吧.
楼主就用乔的方法吧.