代码之家 › 专栏 › 技术社区 › Odilon

是否可以将列作为参数传递给ORDER BY子句?

parameters plsql function oracle

Odilon · 技术社区 · 7 年前

我想在Oracle中使用函数。但我需要根据用户传递的参数进行相应的排序。

例子:

FUNCTION foo_function(p_date IN DATE, p_column_number IN NUMBER)
RETURN foo_bar
IS
BEGIN
   SELECT * FROM bar WHERE date = p_date ORDER BY p_column_number
   <...other code...>
END;

此块不工作。是否可以这样做,将列作为参数传递给ORDER BY子句?

更新----------

@XING展示了一种很好的解决问题的方法,与其他问题的答案一样,无需解码。

但问题是我遗漏了一些东西。现在我遇到了以下错误: "inconsistent datatypes: expected %s got %s"

-- Creating my object with 2 columns.
CREATE OR REPLACE TYPE MY_OBJECT AS OBJECT(IDOP NUMBER, EMISSION_DATE DATE);

-- Creating the table of MY_OBJECT type.
CREATE OR REPLACE TYPE TB_OBJECT AS TABLE OF MY_OBJECT;

-- Creating my function
CREATE OR REPLACE FUNCTION GET_OBJECT(
   P_INITIAL_DATE IN DATE,
   P_FINAL_DATE IN DATE,
   P_COLUMN_NUMBER   IN   NUMBER)
   RETURN TB_OBJECT
IS
   V_TB       TB_OBJECT;
   V_SQL      VARCHAR2(1000);
BEGIN
   V_SQL :=
      'SELECT IDOP, EMISSION_DATE FROM OP WHERE EMISSION_DATE BETWEEN :p_initial_date AND :p_final_date ORDER BY :p_column_number';

   EXECUTE IMMEDIATE V_SQL
   BULK COLLECT INTO V_TB
             USING P_COLUMN_NUMBER;

   RETURN (V_TB);
END;

-- Calling the function
SELECT * FROM TABLE (GET_OBJECT(TO_DATE('01/06/2017','dd/MM/yyyy'), TO_DATE('09/01/2018', 'dd/MM/yyyy'), 1));

更新(2)---------------------

我的代码中的问题是,我忘记将select强制转换为我的对象。

代码就是这样工作的。

V_SQL :=
      'SELECT MY_OBJECT(IDOP, EMISSION_DATE) FROM OP WHERE EMISSION_DATE BETWEEN :p_initial_date AND :p_final_date ORDER BY :p_column_number';

谢谢@XING

1 回复 | 直到 7 年前

XING 7 年前

在下面的示例中,我向您展示了如何在函数中仅对1列进行排序。您可以根据需要修改功能;

--Created a type of number to return the ordered result. You can create an object with the column same as used in your select statement.
CREATE OR REPLACE TYPE VAR_RET AS TABLE OF NUMBER;
/

--功能

CREATE OR REPLACE FUNCTION FOO_FUNCTION (     
     P_COLUMN_NUMBER   IN   NUMBER)
     RETURN VAR_RET
IS
     V_RES                         VAR_RET;
     v_sql                         VARCHAR2(1000);
BEGIN
     V_SQL :=
          'SELECT EMPNO FROM EMP   ORDER BY :p_column_number'; --<-- Create Object with all the columns you are selecting here and then a type of your object to hold result

     EXECUTE IMMEDIATE V_SQL
     BULK COLLECT INTO V_RES
                 USING P_COLUMN_NUMBER;

     RETURN (V_RES);
END;

--结果:

SQL>  select * from table( FOO_FUNCTION (1));

COLUMN_VALUE
------------
        7369
        7499
        7521
        7566
        7654
        7698
        7782
        7788
        7839
        7844
        7876

编辑:带2列排序

CREATE OR REPLACE TYPE MY_TAB AS OBJECT
(
 V_EMPNM   VARCHAR2(100),
 V_EMPNO   NUMBER
);

CREATE OR REPLACE  TYPE VAR_RET AS TABLE OF MY_TAB;
/



CREATE OR REPLACE FUNCTION FOO_FUNCTION (     
     P_COLUMN_NUMBER   IN   NUMBER)
     RETURN VAR_RET
IS
     V_RES                         VAR_RET;
     v_sql                         VARCHAR2(1000);
BEGIN
     V_SQL := --You need to cast your select statement as per Object
          'SELECT MY_TAB(ENAME,EMPNO) FROM EMP ORDER BY :p_column_number'; --<-- Create Object with all the columns you are selecting here and then a type of your object to hold result

     EXECUTE IMMEDIATE V_SQL
     BULK COLLECT INTO V_RES
                 USING P_COLUMN_NUMBER;

     RETURN (V_RES);
END;

执行: 1) 方式1

DECLARE
     VAR                           VAR_RET := VAR_RET ();
BEGIN
     VAR := FOO_FUNCTION (1);

     FOR I IN 1 .. VAR.COUNT
     LOOP
          DBMS_OUTPUT.PUT_LINE (VAR (I).V_EMPNM ||'  ' ||VAR (I).V_EMPNO);
     END LOOP;
END;

SQL> /
SMITH  7369
ALLEN  7499
WARD  7521
JONES  7566
MARTIN  7654
BLAKE  7698
CLARK  7782
SCOTT  7788
KING  7839
TURNER  7844
ADAMS  7876
JAMES  7900
FORD  7902
MILLER  7934

PL/SQL procedure successfully completed.

2) 方式2

 select * from table( FOO_FUNCTION (1));

V_EMPNM                                                                                                 V_EMPNO
----------------------------------------------------------------------------------------------------
SMITH                                                                                                      7369
ALLEN                                                                                                      7499
WARD                                                                                                       7521
JONES                                                                                                      7566
MARTIN                                                                                                     7654
BLAKE                                                                                                      7698
CLARK                                                                                                      7782
SCOTT                                                                                                      7788
KING                                                                                                       7839
TURNER                                                                                                     7844
ADAMS                                                                                                      7876

V_EMPNM                                                                                                 V_EMPNO
----------------------------------------------------------------------------------------------------
JAMES                                                                                                      7900
FORD                                                                                                       7902
MILLER                                                                                                     7934

14 rows selected.