如何在oracle中实现动态字符串查询 [英] how to implement dynamic string query in oracle

问题描述

PROCEDURE SP_SEARCH_ORDERS（

O_RESULT OUT SYS_REFCURSOR，

P_ORDERDATE DATE，

P_USER VARCHAR2，

P_ORDERSTATUS VARCHAR2 ，

P_PAYMENTSTATUS VARCHAR2

）

AS

STMT VARCHAR2（4000）;



BEGIN



STMT：='SELECT * FROM TBL_ORDERS ODR JOIN TBL_ORDERBILLING_SHIPPING ODRBILLSHIP';



如果P_ORDERDATE不为空那么

STMT：= STMT || '和ODR.ORDER_DATE一样''％'|| P_ORDERDATE || '％''';

结束IF;



如果P_USER不为空那么

STMT：= STMT || '和ODRBILLSHIP.S_FNAME喜欢''％'|| P_USER || '％''';

结束IF;



如果P_ORDERSTATUS不为空那么

STMT：= STMT || '和ODR.ORDER_STATUS一样''％'|| P_ORDERSTATUS || '％''';

结束IF;



如果P_PAYMENTSTATUS不为空那么

STMT：= STMT || '和ODR.PAYMENT_STATUS一样''％'|| P_PAYMENTSTATUS || '％''';



结束IF;



执行即时STMT;



- DBMS_OUTPUT.PUT_LINE（STMT）;







END SP_SEARCH_ORDERS;







如何执行字符串查询，其给出的错误是缺少关键字。

任何帮助，

解决方案

好的。当您使用加入（内部联接）时，您必须指定交叉条件。

TVE更正条款：

选择...

从table1连接table2到table1.column = table2.columnas

哪里...



你错过了ON子句

PROCEDURE SP_SEARCH_ORDERS(
O_RESULT OUT SYS_REFCURSOR,
P_ORDERDATE DATE,
P_USER VARCHAR2,
P_ORDERSTATUS VARCHAR2,
P_PAYMENTSTATUS VARCHAR2
)
AS
STMT VARCHAR2(4000);

BEGIN

STMT := 'SELECT * FROM TBL_ORDERS ODR JOIN TBL_ORDERBILLING_SHIPPING ODRBILLSHIP WHERE';

IF P_ORDERDATE IS NOT NULL THEN
STMT := STMT || ' and ODR.ORDER_DATE like ''%' || P_ORDERDATE || '%''';
END IF;

IF P_USER IS NOT NULL THEN
STMT := STMT || ' and ODRBILLSHIP.S_FNAME like ''%' || P_USER || '%''';
END IF;

IF P_ORDERSTATUS IS NOT NULL THEN
STMT := STMT || ' and ODR.ORDER_STATUS like ''%' || P_ORDERSTATUS || '%''';
END IF;

IF P_PAYMENTSTATUS IS NOT NULL THEN
STMT := STMT || ' and ODR.PAYMENT_STATUS like ''%' || P_PAYMENTSTATUS || '%''';

END IF;

EXECUTE IMMEDIATE STMT;

-- DBMS_OUTPUT.PUT_LINE(STMT);



END SP_SEARCH_ORDERS;



how to execute string query ,its giving error that missing keyword.
any help appreciated,

解决方案

Ok. When you use a "join" (inner join) you must specify the cross condition.
TVE correcta clase es:
Select ...
From table1 join table2 en table1.column = table2.columnas
Where...

You hace missed the ON clause

这篇关于如何在oracle中实现动态字符串查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！