from ( select strings,

translate( strings,

abcdefghi]klmnopqrstuvwxyz0123456789, rpad(#,26,#) rpad(*,10,*)) translated

from v ) x

where instr(translated,#) > 0 and instr(translated,*) > 0

В качестве альтернативы оператору WITH можно использовать вложенный запрос или просто создать представление.

Обсуждение

Функция TRANSLATE чрезвычайно упрощает решение этой задачи. Первый шаг - с помощью TRANSLATE заменить все буквы и цифры символом фунта (#) и звездочкой (*) соответственно. Вот промежуточные результаты (возвращаемые вложенным запросом Х):

with v as (

select ClassSummary strings from dual union

select 3453430278 from dual union

select findRow 55 from dual union

select 1010 switch from dual union

select 333 from dual union

select threes from dual

select strings,

translate( strings,

abcdefghijklmnopqrstuvwxyz0123456789, rpad(#,26,#)rpad(*,10,*)) translated

from v

STRINGS TRANSLATED

1010 switch .... ######

333 ...

3453430278 ..........

ClassSummary C####S######

findRow 55 ####R##

threes ######

Теперь осталось только выбрать строки, имеющие, по крайней мере, по одному экземпляру # и * . С помощью функции INSTR определите, присутствуют ли эти символы в строке. Если да, возвращаемое значение будет больше нуля. Окончательные строки, которые должны быть получены (для ясности вместе с их транслированными значениями), показаны далее:

with v as (

select ClassSummary strings from dual union

select 3453430278 from dual union

select findRow 55 from dual union

select 1010 switch from dual union

select 333 from dual union

select threes from dual

select strings, translated

from ( select strings,

translate( strings,

abcdefghijklmnopqrstuvwxyz0123456789, rpad(#,26,#)rpad(*,10,*)) translated

from

where and

STRINGS

instr(translated,#) > 0 instr(translated,*) > 0

TRANSLATED

1010 switch **** ###### findRow 55 ####R## **

Преобразование целых чисел в их двоичное представление с использованием Oracle

Задача

Требуется преобразовать целое число в его двоичное представление всистеме Oracle. Например, необходимо получить все заработные платы таблицы EMP в двоичном виде как часть следующего результирующего множества:

Решение

В этом решении используется оператор MODEL, поэтому оно подходит только для Oracle Database 10g и более поздних версий. MODEL обладает возможностью выполнять итерации и обеспечивать доступ к значениям строк как к элементам массива, поэтому естественно было выбрать его для этой операции (исходя из предположения, что задача должна быть решена в SQL, поскольку пользовательская функция была бы здесь более уместна). Как уже говорилось в отношении всех решений данной книги, если вы не видите практического применения этому коду, сосредоточьтесь на технике. Полезно знать, что оператор MODEL может выполнять процедурные задачи, сохраняя при этом ориентированную на работу с множествами природу и мощь, свойственные SQL. Возможно, вы говорите себе: Я бы никогда не делал этого в SQL . Я ни в коем случае не указываю, что вы должны делать, а что не должны. Я только призываю сосредоточиться на технике, чтобы суметь применить ее, если вдруг столкнетесь с возможностью более практического ее применения.

Следующее решение возвращает все значения столбцов ENAME и SAL из таблицы EMP. При этом в скалярном подзапросе вызывается оператор MODEL (здесь он выполняет роль обычной функции, работающей с таблицей EMP, которая просто получает входные данные, обрабатывает их и возвращает значения, во многом как это делала бы любая другая функция):

1 select ename,

2 sal,

4 select bin

5 from dual

6 model

7 dimension by ( 0 attr )

8 measures ( sal num,

9 cast(null as varchar2(30)) bin,

10 0123456789ABCDEF hex

11 )

12 rules iterate (10000) until (num[0] <= 0) (

13 bin[0] = substr(hex[cv()],mod(num[cv()],2)+1,1)bin[cv()],

14 num[0] = trunc(num[cv()]/2)

15 )

16 ) sal binary

17 from emp

Обсуждение

В разделе Решение я говорил, что данную задачу лучше решать с помощью пользовательской функции. В самом деле, идея этого рецепта возникла из функции. Фактически он является адаптацией функции TO BASE, написанной Томом Кайтом, сотрудником корпорации Oracle. Как и остальные рецепты данной книги, этот рецепт, даже если вы