首页 > sql > 如何将输入传递给Oracle SQL中的动态查询?

如何将输入传递给Oracle SQL中的动态查询? (How to pass inputs to a dynamic query in Oracle SQL?)

2012-06-29 sqloracleplsql

问题

我正在尝试创建一个基于输入动态运行查询的函数。函数input_id的第一个输入是动态查询的参数。第二个输入IN_QUERY_ID指定要使用的查询。

create or replace  
FUNCTION getResultID(  
INPUT_ID NUMBER,  
IN_QUERY_ID NUMBER  
)  
RETURN VARCHAR2  
AS  
 RESULT_ID VARCHAR2(256);  
 query_str VARCHAR2(256);  
 BEGIN  
  select CONSTRUCTOR INTO query_str from query_str_ref     
   where QUERY_ID=IN_QUERY_ID;  
  EXECUTE IMMEDIATE query_str INTO RESULT_ID  USING INPUT_ID;  
   RETURN Result_ID;  
END getResultID; 

我收到一条错误,我在“RESULT_ID = IN_QUERY_ID”之后没有正确结束语句 我想知道我是否错过了其他一些步骤。

解决方法

好消息是,这不是你的功能是错的。根据dbms_output@sebas鼓励你生成的,你试图动态执行的字符串是:

select FIRST_NAME||LAST_NAME||to_char(BIRTH_DATE,'yyyy/mm/dd') as HOST_ID FROM INPUT_DATA_TABLE WHERE INPUT_ID=NEW:INPUT_ID;

有两件事是错的。这NEW:INPUT_ID是造成的ORA-00933,因为NEW看起来很虚伪; 如果你删除它会识别:INPUT_ID为绑定变量。(NEW看起来它来自触发器,但可能是巧合)。你不应该;在字符串上有一个尾随,execute不需要它,它会因无效的字符错误而中断。

因此,如果query_str_ref条目更改为:

select FIRST_NAME||LAST_NAME||to_char(BIRTH_DATE,'yyyy/mm/dd') as HOST_ID FROM INPUT_DATA_TABLE WHERE INPUT_ID=:INPUT_ID

问题

I am trying to create a function that dynamically runs a query based on inputs. The first input for the function, input_id, is the argument for the dynamic query. The second input, IN_QUERY_ID, specifies which query to use.

create or replace  
FUNCTION getResultID(  
INPUT_ID NUMBER,  
IN_QUERY_ID NUMBER  
)  
RETURN VARCHAR2  
AS  
 RESULT_ID VARCHAR2(256);  
 query_str VARCHAR2(256);  
 BEGIN  
  select CONSTRUCTOR INTO query_str from query_str_ref     
   where QUERY_ID=IN_QUERY_ID;  
  EXECUTE IMMEDIATE query_str INTO RESULT_ID  USING INPUT_ID;  
   RETURN Result_ID;  
END getResultID; 

I'm getting an error that I'm not properly ending the statement after "RESULT_ID=IN_QUERY_ID;" I'm wondering if I'm missing some other step.

解决方法

The good news is that it's not your function that's wrong. According to the dbms_output that @sebas encouraged you to produce, the string you're trying to execute dynamically is:

select FIRST_NAME||LAST_NAME||to_char(BIRTH_DATE,'yyyy/mm/dd') as HOST_ID FROM INPUT_DATA_TABLE WHERE INPUT_ID=NEW:INPUT_ID;

There are two thing wrong with that. The NEW:INPUT_ID is causing the ORA-00933, because the NEW looks spurious; if you remove that it will recognise the :INPUT_ID as a bind variable. (NEW looks like it's come from a trigger but is probably a coincidence). And you should not have a trailing ; on the string, execute doesn't need it and it will break with an invalid character error.

So it should work if the query_str_ref entry is changed to:

select FIRST_NAME||LAST_NAME||to_char(BIRTH_DATE,'yyyy/mm/dd') as HOST_ID FROM INPUT_DATA_TABLE WHERE INPUT_ID=:INPUT_ID
相似信息