目录
分享
是否可以将列作为参数传递给 ODER BY 子句？
oraclefunctionplsqlparameters
浏览量:99
编辑于:2023-04-12 21:53:09
我想在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） ---------------------
我的代码中的问题是我忘记将选择强制转换为我的对象。
这样代码就可以工作了。
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
解决方案:
Here in the below example i show you how to do that in a function by only ordering only 1 column. You can modify the function as per your need;
--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;
/
--Function
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;
--Result:
SQL> select * from table( FOO_FUNCTION (1));
COLUMN_VALUE
------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EDIT: WITH 2 COLUMNS Ordering
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;
Execution: 1) Way 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.
- WAY 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.
