在Oracle中执行动态SQL查询 [英] Executing Dynamic Sql query in Oracle
问题描述
我有一个SELECT
查询,其中有一个动态WHERE
条件.问题是当我尝试将WHERE
条件PARAMETER
与SQL查询连接在一起时,不允许我保存PROCEDURE
.
I have a SELECT
query in which i will have a a Dynamic WHERE
condition. The thing is when I try to concatenate the WHERE
condition PARAMETER
with the SQL Query its not allowing me to save the PROCEDURE
.
例如:
CREATE PROCEDURE usp_MySearchQuery
(
QTYPE IN INT,
OUT_CUR OUT SYS_REFCURSOR
)
IS
DYN_QUERY VARCHAR2;
BEGIN
IF QTYPE=1 THEN
DYN_QUERY :=' BETWEEN 1 AND 2';
ELSE
DYN_QUERY :='=10';
END IF;
OPEN OUT_CUR FOR
SELECT * FROM MYTABLE WHERE TYPE=QTYPE AND ID || DYN_QUERY;
END;
这是我的程序的样子.
我尝试了EXECUTE IMMEDIETE
,但是在其文档本身中,它编写的内容不适用于多行查询.
This is how my procedure looks like.
I tried EXECUTE IMMEDIETE
but in its documentation itself, its written it wont work for multiple row query.
在MSSQL
中,我们有EXEC
(不确定)命令,该命令可以execute
发送给该命令的文本.同样,我们是否有任何命令可以在Oracle
In MSSQL
we have EXEC
(not sure) command which can execute
the text sent to the command. In the same way do we have any commands which can run the dynamic query in Oracle
UPDATE: Answer
我尝试过这样.
I tried like this.
OPEN OUT_CUR FOR
' SELECT * FROM MYTABLE WHERE TYPE=:QTYPE AND ID ' || DYN_QUERY
USING QTYPE;
它奏效了
推荐答案
动态字符串必须包含在单引号"内
The Dynamic String has to be enclosed within 'Single Quotes'
OPEN OUT_CUR FOR
'SELECT * FROM MYTABLE WHERE ID '|| DYN_QUERY;
EXECUTE IMMEDIATE
如果使用BULK COLLECT
,则允许多行结果
示例:
EXECUTE IMMEDIATE
allows Multi row result, if you use BULK COLLECT
Example:
DECLARE
TYPE myarray IS TABLE OF VARCHAR2(100);
v_array myarray;
BEGIN
EXECUTE IMMEDIATE 'select ''x'' from dual union all select ''y'' from dual'
BULK COLLECT INTO v_array;
--Or you could use the alternative quoting mechanism to avoid doubling quotation marks.
--EXECUTE IMMEDIATE q'[select 'x' from dual union all select 'y' from dual]'
-- BULK COLLECT INTO v_array;
FOR i IN 1..v_array.count
LOOP
DBMS_OUTPUT.PUT_LINE(v_array(i));
END LOOP;
END;
EXECUTE IMMEDIATE
使用绑定变量;
String := 'SELECT * FROM EMP WHERE name = :name AND age = :age AND :name <> ''Mahesh''';
EXECUTE IMMEDIATE String USING 'Mahi',21,'Mahi';
<OR>
EXECUTE IMMEDIATE String USING proc_variable1,proc_variable2,proc_variable1;
这篇关于在Oracle中执行动态SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!