Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

LOWER(рядок) – перетворить кожну букву параметра рядок у

рядкову.

UPPER(рядок) – перетворить кожну букву параметра рядок у

прописну.

LPAD(рядок, число_символів, [символ_заповнення]) – повертає рядок,

доповнений зліва символом_заповнення кількістю число_символів. За

замовчуванням символом заповнення є пробіл.

RPAD(рядок, число_символів, [символ_заповнення]) – те саме, але

зправа.

LTRIM(рядок, [шаблон]) – повертає обрізане зліва значення параметра

рядок. З рядка вилучаються символи, які входить у множину

символів параметра шаблон. За замовчуванням шаблон складається

із символа пробілу.

RTRIM(рядок, [шаблон]) – те саме, але зправа.

TRANSLATE(рядок, символи_пошуку, символи_заміни) повертає значення

параметра рядок, у якому всі значення параметра символ_пошуку

будуть заміщені значенням символ_заміни. Якщо в рядку

символи_пошуку міститься більше символів, ніж у рядку

символи_заміни, то символи, яким немає відповідності,

виключаються з рядка-результату. Функція TRANSLATE може

застосовуватися, зокрема, для обробки текстів, виготовлених на

пристроях з різними розкладками клавіатур.

REPLACE(рядок, рядок_пошуку, [рядок_заміни]) повертає значення

параметра рядок, у якому всі значення параметра рядок_пошуку

будуть заміщені значеннями рядок_заміни. Якщо рядок_заміни не

заданий, то всі значення параметра рядок_пошуку вилучаються.

SUBSTR(рядок, позиція [, довжина_підрядка]) – повертає підрядок

параметра рядок, починаючи з номера символу, заданого

параметром позиція, і довжиною, заданою параметром

довжина_підрядка. Якщо параметра довжина_підрядка немає, то

повертається весь підрядок до кінця рядка.

101

LENGTH(рядок) – повертає довжину рядка.

INSTR(рядок, рядок_пошуку [, позиція_початку_пошуку[, число_входжень]])

– повертає позицію входження рядка_пошуку в рядок. Значення за

замовчуванням для необов’язкових параметрів дорівнює 1. При

відсутності входження рядка пошуку в основний рядок функція

повертає значення 0.

Числові функції

ЕХР(число) – експонента, число е, піднесене до степеня число.

LN(число) – натуральний логарифм параметра число.

POWER(основа, показник_степеня) – піднесення до степеня. Якщо

основа – від’ємне число, то показник_степеня повинен бути цілим.

SQRT(число) – квадратний корінь числа.

LOG(основа, число) – логарифм числа.

ROUND(число [, позиція]) – заокругляє число з точністю, заданою

параметром позиція – число десяткових знаків після коми. Позиція за

замовчуванням дорівнює 0. Приклад:

SQL> SELECT ROUND(-0.65,1) FROM dual;

ROUND(-0.65,1)

-.7

TRUNC(число [, позиція]) – обрізає число з точністю, заданою

параметром позиція. Значення параметра позиція за замовчуванням

дорівнює 0. Приклад:

SQL> SELECT TRUNC(-0.65,1) FROM dual;

TRUNC(-0.65,1)

-.6

FLOOR(число) – ціле, заокруглене до меншого.

CEIL(число) – ціле, заокруглене до більшого.

ABS(число) – абсолютне значення числа.

102

SIGN(число) – сигнатура числа, повертає -1, якщо параметр число <

0, 0, якщо число = 0, і 1, якщо число > 0.

MOD(число, основа) повертає залишок від ділення параметра число на

значення, задане параметром основа.

Функції для обробки дати

Таблиця 4.1 Окремі значення параметра формат для дати

Параметр Тип заокруглення

YYYY

До року, заокруглення в більшу сторону відбувається

з 1 липня

MON

До місяця, заокруглення в більшу сторону

відбувається з 16 числа

До того ж дня тижня, що перший день місяця

DDD

До дня

DAY

До початкового дня тижня

До години

SYSDATE – системна дата.

ROUND(дата [,формат]) – заокруглює значення параметра дата за

шаблоном, заданим параметром формат. Найуживаніші значення

параметра формат представлені в таблиці 4.1.

TRUNC(дата [,формат]) – усікає значення параметра дата за

шаблоном, заданим параметром формат.

Тригонометричні функції

SIN(число) – синус числа. Параметр число задається в радіанах.

COS(число) – косинус числа.

ТАN(число) – тангенс числа.

ASIN(число) – арксинус числа.

103

АС0S(число) – арккосинус числа.

АTAN(число) – арктангенс числа.

Функції перетворення типів даних

TO_CHAR(аргумент [,формат]) повертає аргумент типу NUMBER або

DATE, перетворений у символьний рядок. Деякі найуживаніші

значення параметра формат для чисел представлені в таблиці 4.2

Таблиця 4.2 Значення параметра формат для чисел

Формат Вид виведеного результату

9 Виводиться цифра. Провідний нуль не виводиться

0 Виводиться цифра. Провідний 0 виводиться

ЕЕЕЕ Результат виводиться в експонентній нотації

G Виводиться символ-роздільник (наприклад, кома)

Приклади застосування функції TO_CHAR:

SQL> SELECT TO_CHAR(1000000,'9G999G999') FROM dual;

TO_CHAR

1,000,000

SQL> SELECT TO_CHAR(1000000,'999EEEE') FROM dual;

TO_CHAR

1E+06

TO_DATE(символъный_аргумент [,формат]) – перетворює символьного

типу символьний_аргумент в тип DATE. Якщо параметр формат

опущений, то символьний_аргумент повинен відповідати формату

дати, прийнятому за замовчуванням. Найбільш уживані значення

параметра формат для дат представлені вище в таблиці 4.1.

TO_NUMBER(символьный_аргумент) перетворює символьний_аргумент

символьного типу в значення типу NUMBER.

4.5.4 Табличні області

104

БД Oracle складається з табличних просторів (областей),

кожна з яких відповідає одному або більше фізичних файлів даних.

Кожний файл може належати лише одному табличному простору.

По суті, вся інформація БД знаходиться в табличних просторах.

Застосування табличних просторів надає такі можливості:

– відокремлювати дані користувача від службових даних

словника;

– розокремлювати дані різних користувачів;

– розташовувати файли даних різних табличних областей на

різних дисках для зменшення суперництва між ними;

– відокремлювати сегменти відкоту від користувацьких даних і

попередити втрату інформації під час одиничного збою;

– переводити індивідуальні табличні простори в пасивний стан,

не припиняючи нормальну роботу табличних просторів інших

користувачів;

– забезпечувати спеціальні режими роботи БД, резервуючи

табличні простори для даних під час періодів високої

активності, тільки для читання, для розміщення тимчасових

сегментів тощо;

– виконувати резервне копіювання окремих індивідуальних

табличних просторів, залишаючи решту доступними для

користувачів.

Оператор утворення табличної області має такий синтаксис:

CREATE TABLESPACE ім'я_табличної_області

DATAFILE ім’я_файлу

[SIZE ціле_число [К або М]]

[AUTOEXTEND OFF або ON [NEXT ціле_число К або М]]

[MAXSIZE UNLIMITED ціле_число К|М], ...]

[DEFAULT STORAGE розмір_пам'яті]

[ONLINE або OFFLINE]

[PERMANENT або TEMPORARY];

Параметр ім'я_табличної_області визначає ім’я створюваної

табличної області. Параметр ім’я_файлу визначає повне ім’я файлу

операційної системи, у якому будуть розміщуватися дані табличної

області. Файл має системне розширення *.dbf, яке обов’язкове,

(можна – *.ora).

Ключове слово SIZE визначає об’єм простору в байтах,

кілобайтах (K) чи в мегабайтах (М). Якщо файл з ім’ям, визначеним

параметром ім’я_файлу, ще не існує, то вказувати ключове слово

SIZE потрібно обов’язково.

105

Ключове слово AUTOEXTEND означає дозвіл (ON) або заборону

(OFF) автоматичного розширення табличного простору. Ключове

слово NEXT задає нарощення простору в процесі експлуатації БД.

За допомогою ключового слова MAXSIZE можна визначити

максимальний розмір простору, виділеного під табличну область.

Якщо зазначене ключове слово UNLIMITED, то розмір простору не

має обмежень.

Значення ключового слова ONLINE (задається за замовчуванням)

забезпечує автоматичне переведення табличної області в

оперативний режим.

Значення ключового слова PERMANENT (за замовчуванням)

визначає створення постійної табличної області.

Установлені режими табличних областей можна переглянути в

представленні словника даних dba_tablespaces. Нагадаємо, що

приклад виклику цього представлення був поданий у розділі 4.1.

Розглянемо приклад створення табличної області nafta, дані якої

розміщуються у файлі E:\ORANTA\DATABASE\nafta.dbf. Під файл для

табличної області резервується один мегабайт дискового простору

і допускається його автоматичне нарощення фрагментами по

одному мегабайту.

SQL>CREATE TABLESPACE nafta

2 DATAFILE ‘E:\ORANTA\DATABASE\nafta.dbf’

3 SIZE 1M AUTOEXTEND ON NEXT 1M

4 MAXSIZE UNLIMITED;

Для зміни параметрів табличної області використовується

оператор ALTER TABLESPACE. Він дозволяє:

– додати файли даних;

– перейменувати файли даних;

– змінити параметри зберігання за замовчуванням;

– перевести табличну область в автономний або оперативний

режим;

– почати або завершити копіювання;

– дозволити або заборонити запис у табличну область та ін.

У якості прикладу заборонимо запис у табличну область nafta.

Ця операція використовується, наприклад, для захисту даних від

змін у неробочий час.

SQL> ALTER TABLESPACE nafta READ ONLY;

106

Оператор вилучення табличної області Oracle використовує

такий синтаксис:

DROP TABLESPACE ім'я_табличної_області [INCLUDING CONTENTS];

Для виконання даної операції необхідно володіти привілеєм

DROP TABLESPACE. Таблична область SYSTEM не може бути

вилучена.

Конструкція INCLUDING CONTENTS визначає, що таблична

область повинна бути вилучена, навіть якщо вона містить дані.

Якщо даний параметр опущений і таблична область не містить

даних, вона вилучається. Якщо ж при опущеному ключовому слові

INCLUDING CONTENTS таблична область містить дані, то

повертається повідомлення про помилку і таблична область не

вилучається.

Перед вилученням табличну область слід перевести в

автономний режим (OFFLINE). Ця рекомендація по’вязана з тим, що

таблична область не може бути вилучена доти, поки користувачі

працюють з її об’єктами.

Нижче наведений приклад вилучення табличної області nafta

разом зі всіма її об’єктами.

SQL> DROP TABLESPACE nafta INCLUDING CONTENTS;

4.5.5 Таблиці

Оператор створення таблиці Oracle містить досить велике

число ключових слів і параметрів. Розглянемо таку дещо

скорочену його конструкцію:

CREATE TABLE [ім’я_схеми.]ім’я_таблиці(ім’я_поля тип [DEFAULT вираз]

[обмеж_цілісності_поля], ...)

TABLESPACE імя_табличної_області

[CACHE | NOCACHE];

Тут: ім’я_схеми – ім’я схеми (ім’я користувача);

ім’я_таблиці – ім’я таблиці, яка створюється;

ім’я_поля – ім’я поля таблиці;

тип – тип поля;

обмеж_цілісності_поля – обмеження цілісності поля.

107

Ключове слово DEFAULT означає, що при вводі даних за

допомогою оператора INSERT за замовчуванням цьому полю буде

привласнено значення виразу, якщо в операторі INSERT не зазначене

явно інше значення. Тип виразу повинен відповідати типу даних

поля і він не повинен містити посилань на інші вирази.

Окремі обмеження цілісності полів будуть показані нижче в

прикладах.

Коли таблиця створюється, то система автоматично резервує

місця як для даних, так і для індексів. Ці місця називаються

сегментами: сегмент даних і сегмент індексів. Сегменти

складаються з блоків. Записи заносяться в таблицю у фізичній

послідовності. Перший запис зберігається в першому блоці

першого екстента, наступні записи заносяться в перший блок доти,

поки він не заповниться. Блок вважається заповненим, коли для

чергового запису не вистачає вільного простору. Коли

заповнюються всі блоки першого екстента, виділяється перший

додатковий екстент, коли буде заповнений і цей екстент, сервер

виділить наступний.

Після виділення екстента для таблиці або індексу він не

звільняється доти, поки таблиця не буде знищена. Однак, якщо

вилучити з блоку даних всі дані, то цей блок стане доступним для

повторного використання цією ж таблицею чи кластером.

Ключове слово TABLESPACE визначає ім’я табличної області, у

якій буде розміщена таблиця. Якщо цей параметр не вказаний, то

таблиця розміщується в табличній області, заданій за

замовчуванням для користувача, який є власником схеми.

Ключове слово CACHE вказує на те, що блоки таблиці,

позначаються в системному кеші як такі, що найбільше

використовуються, застосовувати цей параметр рекомендується

для маленьких та часто вживаних таблиць. Тоді за рахунок втрат

пам’яті кеша, можна виграти час на багаторазовому читанні

таблиці з магнітного диска. За замовчуванням приймається

параметр NOCACHE, тоді ім’я таблиці поміщається в кінець списку

(у таблиці обліку частоти звертань до кеша) і стає першим

кандидатом на вилучення з кеша даних.

Розглянемо приклади створення таблиць, відомих з розділу 3.3,

у схемі (в заздалегідь створеному табличному просторі, тому його

можна не задавати) користувача user1. Таблиця Oblik має 4 таких

поля: Kodpp цілочислового типу, Kodpr теж цілочислового типу, Kil з

числом дійсного типу i Data типу дати. Обмеження nn_Oblik_Kodpp,

108

означає, що поле Kodpp не допускає наявності невизначених

значень, це ж стосується й поля Kodpr. Поле Kil не має обмежень, а

поле data за замовчуванням приймає значення поточної системної

дати.

SQL> CREATE TABLE user1.Oblik

2 (Kodpp INTEGER CONSTRAINT nn_Oblik_Kodpp NOT NULL,

3 Kodpr INTEGER CONSTRAINT nn_Oblik_Kodpr NOT NULL,

4 Kil NUMBER(9,3),

5 data DEFAULT SYSDATE);

Наступний приклад демонструє створення таблиці Pidpr із

двома полями: Kodpp цілочислового типу і Nazpp літерного типу,

розміщеної в тій же табличній області nafta. Обмеження

pk_Pidpr_Kodpp, означає, що поле Kodpp є первинним ключем.

SQL> CREATE TABLE user1.Pidpr

2 (Kodpp INTEGER CONSTRAINT pk_Pidpr_Kodpp PRIMARY KEY,

3 Nazpp CHAR(30));

Таблиця Gazpr має такі поля: Kodpr цілочислового типу, Nazpr

літерного типу та Kilnyt і Diam – обидва цілочислового типу.

Подібно до поля Kodpp таблиці Pidpr поле Kodpr теж є первинним

ключем. Команда, яка її створює, має вигляд:

SQL> CREATE TABLE user1.Gazpr

2 (Kodpr INTEGER CONSTRAINT pk_Gazpr_Kodpr PRIMARY KEY,

3 Nazpr CHAR(50),

4 Kilnyt INTEGER,

5 DIAM INTEGER);

Таблицю можна модифікуватиза командою ALTER TABLE. Це

такі роботи, як додати один або декілька нових полів, змінити

обмеження цілісності, модифікувати параметри поля (тип даних,

довжину, обмеження цілісності) та параметри збереження.

Оператор вилучення таблиці має такий синтаксис:

DROP TABLE [ім’я_схеми.]ім’я_таблицi [CASCADE CONSTRAINTS];

Коли таблиця знищується, усі блоки стають вільними для

використання під дані чи індекси інших таблиць. Для знищення

таблиці необхідно бути або її власником або мати привілей DROP

ANY TABLE. Всі індекси і тригери, асоційовані з таблицею, навіть

109

якщо вони були створені іншим користувачем, вилучаються. Усі

збережені програми, що залежать від таблиці, залишаються, але

стають недійсними (непридатними для використання). Усі

синоніми вилученої таблиці залишаються, але повертають помилку

при звертанні до них. Представлення, синоніми і програми знову

стають актуальними, якщо таблиця створюється заново (після

їхньої перекомпіляції).

Якщо задано ключове слово CASCADE CONSTRAINTS, то

вилучаються всі обмеження цілісності, що посилаються на

первинні й унікальні ключі даної таблиці. Якщо такі посилання

існують, а CASCADE CONSTRAINTS відсутнє, то вилучення таблиці

не виконується і сервер повертає повідомлення про помилку.

Перед вилученням таблиці рекомендується визначити через

представлення словника даних user_cross_refs залежність інших

таблиць від даної таблиці. Приклад знищення таблиці Tab:

SQL> DROP TABLE Tab CASCADE CONSTRAINTS;

4.5.6 Представлення

Представленням є іменована вибірка з однієї або декількох

таблиць чи інших представлень, яка динамічно підтримується

сервером. Над оператором SELECT (який – теж вибірка)

представлення має ту перевагу, що дозволяє ефективно обмежити

дані, які користувач може модифікувати. Адміністратор бази даних

може обмежити доступну користувачам частину логічного

простору бази даних тільки тими даними, які реально їм необхідні.

Оператор утворення представлення Oracle має такий синтаксис:

CREATE [OR REPLACE][FORCE | NO FORCE]

VIEW [ім'я_схеми.]ім'я_представлення AS запит

WITH READ ONLY | CHECK OPTION [CONSTRAINT обмеження цілісності];

Ключові слова команди означають таке:

OR REPLACE – примусове заміщення старого представлення

новим. Використання цього параметра дозволяє не надавати

повторно привілеї, що було б необхідно при використанні команд

DROP VIEW і CREATE VIEW при знищенні представлення і створенні

його заново;

110