查询时如何根据一个自定义的函数来进行排序

Question

sql

Answer 1

func_iptonum是啥？你的函数名不是IPTONuM吗？

Answer 2

order by func_iptonum(RXRG_7420)
改为
order by IPTONuM(ip)

Answer 3

写错了，应该是这样：
CREATE OR REPLACE FUNCTION IPTONuM(ipaddress  VARCHAR2) RETURN NUMBER AS
     m_return     NUMBER;          
     m_ip            VARCHAR2(1000) := ipaddress; 
BEGIN
  FOR i IN 1 .. 3 LOOP
      m_ip := SUBSTR(m_ip, 1, INSTR(m_ip, '.') - 1) || '*POWER(256, ' ||
                   (4 - i) || ') + ' || SUBSTR(m_ip, INSTR(m_ip, '.') + 1);
  END LOOP;
 
  EXECUTE IMMEDIATE 'SELECT ' || m_ip || ' FROM iptable'
      INTO m_return;
  RETURN m_return;
END;
select * from iptable order by IPTONuM(ip)

Answer 4

函数中的动态语句那里，应该会返回多个值，这样会导致select into 失败
不太理解动态语句的作用是什么，直接return m_ip不就好了？

Answer 5

应该怎样改？我第一次用PL/SQL语言编程

Answer 6

CREATE OR REPLACE FUNCTION IPTONuM(ipaddress  VARCHAR2) RETURN NUMBER AS
     m_return     NUMBER;          
     m_ip            VARCHAR2(1000) := ipaddress; 
BEGIN
select sum(to_number(regexp_substr(ipaddress,'[^.]+',1,rownum))*power(256,4-rownum))
into m_return
from dual connect by rownum<=4;
  RETURN m_return;
END;
函数有问题，帮你改了下

Answer 7

CREATE OR REPLACE FUNCTION IPTONuM(ipaddress  VARCHAR2) RETURN NUMBER AS
     m_return     NUMBER;          
     m_ip            VARCHAR2(1000) := ipaddress; 
BEGIN
    select sum(to_number(regexp_substr(ipaddress,'[^.]+',1,rownum))*power(256,4-rownum))
    into m_return
    from iptable connect by rownum<=4;
  RETURN m_return;
END;
select * from iptable order by IPTONuM(ip)没有数据显示还是报了那个错：
Warnings: ---> 
   W (1): Warning: 执行完毕, 但带有警告
          <---  0 record(s) affected
还有不懂你为什么要用rownum

Answer 8

检查下你的数据吧，你的ip字段是否全部符合ip地址的规范，应该是存在错误数据导致的

Answer 9

我晕，你抄也不抄全啊，
我的是from dual，你改成from iptable了

Answer 10

检查下你的数据吧，你的ip字段是否全部符合ip地址的规范，应该是存在错误数据导致的
首先非常感谢你的指导，可是这东西让我纠结了一天。我改了一下方法的思路，换成返回IP地址
以下结合了业务的代码：create or replace  function ipTonumber(ip in varchar) return number is
     s_IP_Section  varchar2(32) ;
           n_IP_Section  number:= 0;
           s_Ret_IP      number;
  begin
           s_Ret_IP := 0;
            for i in 1..4 loop
                s_IP_Section := RegExp_SubStr(ip, '[0-9]{1,3}', 1, i);
                n_IP_Section := TO_NUMBER(s_IP_Section);
                s_Ret_IP := (s_Ret_IP * 256)  + s_IP_Section;
            end loop;
            if (s_Ret_IP is null) then
               s_Ret_IP:=0;
            end if;
            return(s_Ret_IP);
  end;select RXRG_7420 as RX from STORAGE2.T_HWQU_6292_DATA order by ipTonumber(RX );
可是我不明白为什么还是没有结果显示，还报了个警告

Answer 11

function里加个exception处理，看看会不会抛出异常或者不加exception直接调试下这个function，看下输出结果，这样更能解决问题吧

Answer 12

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> CREATE OR REPLACE FUNCTION IPTONuM(ipaddress  VARCHAR2) RETURN NUMBER AS
  2       m_return     NUMBER;
  3       m_ip            VARCHAR2(1000) := ipaddress;
  4  BEGIN
  5      select sum(to_number(regexp_substr(ipaddress,'[^.]+',1,rownum))*power(256,4-rownum))
  6      into m_return
  7      from dual connect by rownum<=4;
  8    RETURN m_return;
  9  END;
 10  /函数已创建。SQL> select iptonum('1.1.1.1') from dual;IPTONUM('1.1.1.1')
------------------
          16843009SQL> select iptonum('192.168.1.1') from dual;IPTONUM('192.168.1.1')
----------------------
            3232235777SQL>
帮你写的函数测试结果，你可以自己去试试你的函数，单独调用是否好用
如果好用的话，就肯定是数据的问题了