使用游标在oracle中进行动态查询 [英] dynamic query in oracle procedure using cursor

问题描述

我正在使用存储过程使用光标选择记录。此过程获取记录ID作为输入。

I am using a stored procedure to select records using a cursor. This procedure gets record id as input.

这是代码：

create or replace
procedure GET_ITEM_DETAILS_ALL
(
  cur_out out sys_refcursor,
  PSTRING VARCHAR2 DEFAULT NULL
)
is
  query_string  VARCHAR2(1000);
Begin
  query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (:sl) order by IT.SL_NO';
  OPEN cur_out FOR query_string USING PSTRING;
End;

对于调用/测试过程，我使用以下代码：

And for calling / testing the procedure I use the below code:

VAR R REFCURSOR
EXEC GET_ITEM_DETAILS_ALL (:R, '4')
PRINT R 

问题是当我传递一个整数数字，如'4'或'2'时，程序返回数据，但是当我通过'1' 2'或'3,4'，它显示错误。

The problem is when I pass a single integer number such as '4' or '2', the procedure returns data, but when I pass '1,2' or '3,4', it shows error.

推荐答案

问题是PSTRING是单个变量，而不是数组。所以你的陈述实际上等于

The problem is PSTRING is a single variable not an array. So your statement actually equates to

.... where IT.SL_NO = PSTRING

这就是为什么当你通过 4 并失败时你通过 1,2 。

That's why it works when you pass 4 and fails where you pass 1,2.

您不会以使用动态SQL的方式获得任何价值（我们可以在不使用字符串的情况下打开ref cursor）。但是，利用动态SQL是解决您的问题的一种方法：

You get no value in using dynamic SQL the way you do (we can open ref cursor without using strings). However, taking advantage of dynamic SQL is one way of solving your problem:

query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('
                   ||PSTRING||
                   ') order by IT.SL_NO';
OPEN cur_out FOR query_string;

或者，您可以使用字符串标记器将字符串转换成令牌。不幸的是，Oracle没有内置的标准，但是对于不同版本的数据库有各种各样的解决方法。 Adrian Billington在他的网站上一个很好的整理。使用其中一种方法，您可以使用动态SQL：

Alternatively you can use a string tokenizer to turn the string into, well, into tokens. Unfortunately Oracle doesn't have a standard built-in but there are various workarounds for different versions of the database. Adrian Billington has a good round-up on his site. Using one of those approaches allows you to ditch the dynamic SQL:

OPEN cur_out FOR select IT.SL_NO from ITEM_DETAILS IT 
   where IT.SL_NO in ( select * from table ( your_string_tokenizer( PSTRING ) ) ) 
   order by IT.SL_NO;

这篇关于使用游标在oracle中进行动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！