更新时间:2023-02-10 17:35:06
我猜想您要将那组数字作为字符串传递,并拆分成单独的数字.这比您想像的要难,因为Oracle没有附带内置的令牌生成器.奇怪吧?
I am guessing you want to pass that set of numbers as a string and split into into individual numbers. This is harder than you might think, because Oracle doesn't come with a built-in tokenizer. Weird, huh?
有许多PL/SQL标记生成器解决方案可以解决Das Interwabs的问题.我使用的是 Anup Pani的实现的变体,它使用Regex(因此仅Oracle 10g或更高版本).我的变体返回一个数字数组,我已将其声明为SQL类型:
There are a number of PL/SQL tokenizer solutions knocking around Das Interwabs. I am using a variant of Anup Pani's implementation, which uses Regex (hence only Oracle 10g or higher). My variant returns an array of numbers which I have declared as a SQL type:
SQL> create or replace type numbers as table of number
2 /
Type created.
SQL>
这意味着我可以将其用作SELECT语句中TABLE()函数的输入:
This means I can use it as an input to a TABLE() function in a SELECT statement:
SQL> select * from table (str_to_number_tokens('20000, 240004, 375000, 255000'))
2 /
COLUMN_VALUE
------------
20000
240004
375000
255000
SQL>
这意味着我可以将您的数字字符串转换成可以在查询中加入的表,如下所示:
This means I can turn your string of numbers into a table which I can join to in a query, like this:
SQL> select val
2 from t23
3 , ( select column_value as i_no
4 from table (str_to_number_tokens('20000, 240004, 375000, 255000')) ) sq
5 where t23.year = 2010
6 and sq.i_no between t23.r_min and t23.r_max
7 order by t23.priority
8 /
VAL
----------
82
50
52
SQL>