Строки и символьные функции в Оracle

Строковые литералы в Оracle - это последовательность из нуля, одного или более символов, заключенных в одинарные кавычки.
У новичков часто встречается ошибка, когда строки заключаются в двойные кавычки или наоборот, названия объектов в одинарные.

insert into 'MY_DOC' ('DOC_ID', 'DOC_NAME') values ("1", "Документ 1");

Error at Command Line:1 Column:12
SQL Error: ORA-00903: неверно имя таблицы
00903. 00000 - "invalid table name"

Error at Command Line:1 Column:57
SQL Error: ORA-00984: употребление столбца здесь недопустимо
00984. 00000 - "column not allowed here"


Строка нулевой длины, т.е. '' это не то же самое что NULL. Если в строке NULL, это говорит о том, что значение не известно или
не установлено. Если в строке '', это значит что строка пустая, но ее значение известно.

Конкатенация (соеднинение) строк выполняется оператором || (две вертикальные черты) или функцией CONCAT

select 'Employee: ' || initcap(ename), concat('Dept: ',deptno)
from emp;

Oracle предлагает обширный набор функций для манипулирования строковыми данными:
CHR(N) - Возвращает символ ASCII кода для десятичного кода N
ASCII(S) - Возвращает десятичный ASCII код первого символа строки
INSTR(S2.S1.pos[,N] - Возвращает позицию строки S1 в строке S2 большую или равную pos.N - число вхождений
LENGHT(S) - Возвращает длину строки
LOWER(S) - Заменяет все символы строки на прописные символы
INITCAP(S) - Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные
SUBSTR(S,pos,[,len]) - Выделяет в строке S подстроку длиной len, начиная с позиции pos
UPPER(S) - Преобразует прописные букцвы в строке на заглавные буквы
LPAD(S,N[,A]) - Возвращает строку S, дополненную слева симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
RPAD(S,N[,A]) - Возвращает строку S, дополненную справа симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
LTRIM(S,[S1]) - Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел)
RTRIM(S,[S1]) - Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел
TRANSLATE(S,S1,S2) - Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия,
исключаются из результирующей строки
REPLACE(S,S1,[,S2]) - Возвращает строку S, для которой все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1
удаляются из результирующей строки
NVL(X,Y) - Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y
SOUNDEX(S) - Возвращает фонетическое представление строки
REGEX_INSTR(S,S1,[N1],[N2],[N3],[S2]) - Возвращает позицию в строке S текста, удовлетворяющего регулярному выражению S1
REGEX_REPLACE(S,S1,S2,[N1],[N2],[S3]) - Возвращает строку S, в которой все вхождения строк, удовлетворяющих регулярному выражению S1, замещены на S2
REGEX_SUBSTR(S,S1,[N1],[N2],[S2]) - Возвращает подстроку из строки S, удовлетворяющего регулярному выражению S1

Что такое ROWNUM

Rownum – это псевдостолбец, значения которого можно увидеть, включив его в любой результирующий набор, например в список столбцов оператора SELECT.
Значениям столбца ROWNUM присваиваются номера 1, 2, 3, 4, ... N, где N – число строк результирующего набора запроса.

select rownum, doc_name from docs;

ROWNUM -- DOC_NAME
1 -- Тестовый документ
2 -- Договор подряда
3 -- Документ 3

В основном стоблец rownum применяется для ограничения в запросе первых N записей. Это похоже на использование предложения LIMIT в других СУБД, например в SQL Server. К примеру, запрос

select *
from my_table
where ROWNUM < 5;

отберет первые 4 записи из таблицы my_table.

Автоинкрементные поля (счетчики) в Oracle

В Oracle нельзя сделать поле, автоматически увеличивающее свое значение напрямую, как например поле Identity в SQL Server. Поэтому для этих целей обычно используются последовательности (сиквенсы, sequences).
Конечно, сиквенсы не так удобны, но и имеют определенные преимущества. К примеру, сиквенс замечательно подходит для сквозных нумераций в нескольких таблицах.

Синтаксис создания последовательности выглядит так:
CREATE SEQUENCE имя
[START WITH начальное_значение]
[INCREMENT BY значение_инкремента];

Реализуем автоинкремент в Oracle. Cначала создадим сиквенс:

CREATE SEQUENCE my_seq;

CREATE TABLE my_doc (
doc_id NUMBER(20)
, doc_name VARCHAR2(256)
, CONSTRAINT pk_my_table PRIMARY KEY (doc_id)
);

Для получения очередного уникального значения сиквенс может быть использован напрямую, либо через триггер:

1. напрямую

INSERT INTO my_doc (doc_id, doc_name) VALUES (my_seq.nextval, 'Документ 1');

2. через триггер

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_doc
FOR EACH ROW
BEGIN
SELECT my_seq.NEXTVAL INTO :NEW.doc_id FROM dual;
END;
/

INSERT INTO my_doc (doc_name) VALUES ('Документ 2');
INSERT INTO my_doc (doc_id, doc_name) VALUES (null, 'Документ 3');

В версии Oracle 11g также появилась возможность делать вот так:

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_doc FOR EACH ROW
BEGIN
:NEW.doc_id := my_seq.NEXTVAL;
END;


Любопытно, что атрибут NEXTVAL, вычисляется не при каждом использовании, а только один раз для каждой очередной строки результата, независимо от того,
сколько раз вызывается NEXTVAL на одну строку.
Попробуйте:

CREATE SEQUENCE seq_1;

CREATE SEQUENCE seq_2 INCREMENT BY 2;

SELECT seq_1.NEXTVAL, seq_1.CURRVAL, seq_1.NEXTVAL, seq_2.CURRVAL, seq_2.NEXTVAL, seq_2.NEXTVAL
FROM all_objects
WHERE ROWNUM <= 10;

NEXTVAL CURRVAL NEXTVAL CURRVAL NEXTVAL NEXTVAL
------- ------- ------- ------- ------- -------
1 1 1 1 1 1
2 2 2 3 3 3
3 3 3 5 5 5
4 4 4 7 7 7
5 5 5 9 9 9
6 6 6 11 11 11
7 7 7 13 13 13
8 8 8 15 15 15
9 9 9 17 17 17
10 10 10 19 19 19

Таблица DUAL и для чего она нужна

В Oracle, в отличие от того же MS SQL Server, нельзя сделать запрос "из ниоткуда".

SELECT 1;

SELECT 1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Поэтому в большинстве запросов должна быть ссылка на таблицу или представление. Как правило, для таких запросов используется таблица DUAL. Таблица DUAL - это реальная таблица в схеме SYS, содержащая только одну запись.

SELECT * FROM DUAL;

DUMMY
-----
X

1 rows selected


Как можно использовать таблицу DUAL в запросах? К примеру мы хотим получить системное время. Тогда, раз что-то во FROM указывать надо - укажем таблицу DUAL:

SELECT SYSDATE FROM DUAL;


SYSDATE
-------------------------
10.04.08

1 rows selected