Лабораторная работа (СУБД Oracle) №3 / lab3/lab3.doc
Лабораторная работа №3
по курсу СУБД ORACLE
Введение
Данная лабораторная работа ориентированна на изучение основных встроенных функций, системных переменных и выражений языка PL/SQL:
1. Системные переменные:
— SYSDATE — текущая дата и время
— USER — имя текущего пользователя
— USERENV — значения системных параметров
2. Числовые функции
— ROUND — округление до заданного числа знаков после запятой
— TRUNC — отсечение до заданного числа знаков после запятой
— FLOOR — ближайшее целое, не превышающее value
— CEIL — ближайшее целое, не меньшее чем value
3. Математические функции
— ABS — модуль числа (абсолютная величина)
— SQRT — квадратный корень
— MOD — деление по модулю
— SIN, COS, TAN, ASIN, ACOS, ATAN, SINH, COSH, TANH — основные тригонометрические функции
— EXP — экспонента
— LOG(base, x), LN(x) — логарифмы
— POWER — степень
4. Текстовые функции
— UPPER — преобразовать строку к верхнему регистру
— LOWER — преобразовать строку к нижнему регистру
— INITCAP — преобразовать первые буквы слов к верхнему регистру
— LENGTH — длина строки символов
— SUBSTR — извлечь подстроку из строки
— INSTR — поиск подстроки в строке
— LTRIM — отсечение ведущих символов из набора
— RTRIM — отсечение концевых символов из набора
— TRIM — отсечение заданного символа
— REPLACE — замена символов в строке
— CONCAT — сцепление двух строк
5. Функции работы с данными типа DATE
— ROUND - округление даты
— TRUNC — отсечение информации о времени дня из даты
— ADD_MONTHS — добавить заданное число месяцев к дате
— LAST_DAY — получить последний день месяца указанной даты
— MONTHS_BETWEEN — число месяцев между датами
— EXTRACT — извлечь часть даты
6. Функции преобразования данных
— TO_CHAR - преобразовать выражение к строке
— TO_DATE - преобразовать выражение к типу дата
— TO_NUMBER - преобразовать выражение к типу число
— CAST - преобразование типов выражений
7. Специальные функции и выражения
— CASE — сравнение выражения с набором значений
— DECODE — сравнение выражения с набором значений
— NVL — замена NULL значения на заданную величину
— NVL2 — проверка на NULL и возврат заданного значения
— COALESCE — возврат первого не NULL значения в списке
Задание
1. Системные переменные:
— вывести на экран текущую дату, имя текущего пользователя и имя компьютера
2. Числовые функции:
— выполнить округление числа 3276.53 до 3276.50 и 3300.00
— выполнить отсечение числа 3276.53 до 3276.00 и 3000.00
— найти целые числа между которыми заключен результат выражения 5.32*<текущее число месяца>
3. Математические функции:
— вычислить результат выражения:
— Дана таблица, содержащая один столбец типа INT (пусть он называется x), заполненная некоторыми произвольными данными (например 1, 100, —32, 0, 123). Для всех значений x вычислить функцию:
4. Текстовые функции
— Преобразовать строку ‘test string FOR CONVERSION’ к виду ‘Test String For Conversion’ используя функции преобразования регистра
— Преобразовать строку ‘*_abcd 1234567890 efgh_*’ к виду ‘12345abcdefgh67890’ используя функции отсечения, извлечения и конкатенации строк
— Преобразовать строку ‘20/12/2004’ в ’20.12.2004’
8. Функции работы с данными типа DATE
— Вывести дату последнего дня текущего месяца (использовать функцию SYSDATE)
— Вычислить число месяцев между последним днем текущего месяца и 1 января 2010 года
— Вычислить число дней до конца года (вычисления должны работать корректно в любой день любого года)
— Вычислить число минут оставшихся до полуночи
9. Функции преобразования данных
— Преобразовать произвольно заданное число (например 139032) в число, в записи которого цифры идут в обратном порядке (230931)
— Дана таблица с единственной колонкой (строкового типа) с выражениями вида:’xx*xx*xx’ где xx — символы цифр 0..9, * - один из символов математических операций: *+-/ Например, таким выражениями являются ‘01*23+34’ ‘32Ч12’ и т.д. Требуется создать таблицу и заполнить ее строками содержащими такие выражения (не менее 5 строк), составить оператор SELECT который используя данные из этой таблицы выведет строки вида ‘<исходное выражение>=<вычисленный результат>’. Задание выполнить без учета приоритетности операций.
Примеры
--
SQL> -- СОЗДАНИЕ ТАБЛИЦЫ
SQL> --
SQL> DROP TABLE test_3;
DROP TABLE test_3
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE test_3 (
2 string_value VARCHAR2(20),
3 number_value NUMBER(6,2),
4 symbol_value CHAR(1),
5 date_value DATE );
Table created.
SQL>
SQL> -- ВСТАВКА ДАННЫХ
SQL> INSERT INTO test_3 VALUES ('AbcdeF', 1234.56, 'Z', '2004-01-12');
1 row created.
SQL> INSERT INTO test_3 VALUES ('fEDCBa', 6543.21, 'A', '2004-10-04');
1 row created.
SQL> INSERT INTO test_3 VALUES ('xyz. abc', 99.99, 'X', '2004-12-31');
1 row created.
SQL>
SQL> --
SQL> -- ИСПОЛЬЗОВАНИЕ СИСТЕМНЫХ ПЕРЕМЕННЫХ
SQL> --
SQL> SELECT 'Current date:' || SYSDATE FROM DUAL;
'CURRENTDATE:'||SYSDATE
-----------------------
Current date:2004-07-24
SQL> SELECT 'User name:' || USER FROM DUAL;
'USERNAME:'||USER
----------------------------------------
User name:SYSTEM
SQL> SELECT 'User environment parameter:' || USERENV('TERMINAL') FROM DUAL;
'USERENVIRONMENTPARAMETER:'||USERENV(`TERMI
-------------------------------------------
User environment parameter:NTSRV
SQL> --
SQL> -- ИСПОЛЬЗОВАНИЕ ЧИСЛОВЫХ ФУНКЦИЙ
SQL> --
SQL>
SQL> -- округление числа ROUND(числовое выражение, колическов знаков после запятой)
SQL> SELECT ROUND(1234.5678, 4) FROM DUAL;
ROUND(1234.5678,4)
------------------
1234.5678
SQL> SELECT ROUND(1234.5678, 3) FROM DUAL;
ROUND(1234.5678,3)
------------------
1234.568
SQL> SELECT ROUND(1234.5678, 2) FROM DUAL;
ROUND(1234.5678,2)
------------------
1234.57
SQL> SELECT ROUND(1234.5678, 1) FROM DUAL;
ROUND(1234.5678,1)
------------------
1234.6
SQL> SELECT ROUND(1234.5678, 0) FROM DUAL;
ROUND(1234.5678,0)
------------------
1235
SQL> SELECT ROUND(1234.5678,-1) FROM DUAL;
ROUND(1234.5678,-1)
-------------------
1230
SQL> SELECT ROUND(1234.5678,-2) FROM DUAL;
ROUND(1234.5678,-2)
-------------------
1200
SQL> SELECT ROUND(1234.5678,-3) FROM DUAL;
ROUND(1234.5678,-3)
-------------------
1000
SQL> SELECT ROUND(1234.5678,-4) FROM DUAL;
ROUND(1234.5678,-4)
-------------------
0
SQL>
SQL> -- отсечение числа TRUNC(числовое выражение, колическов знаков после запятой)
SQL> SELECT TRUNC(1234.5678, 4) FROM DUAL;
TRUNC(1234.5678,4)
------------------
1234.5678
SQL> SELECT TRUNC(1234.5678, 3) FROM DUAL;
TRUNC(1234.5678,3)
------------------
1234.567
SQL> SELECT TRUNC(1234.5678, 2) FROM DUAL;
TRUNC(1234.5678,2)
------------------
1234.56
SQL> SELECT TRUNC(1234.5678, 1) FROM DUAL;
TRUNC(1234.5678,1)
------------------
1234.5
SQL> SELECT TRUNC(1234.5678, 0) FROM DUAL;
TRUNC(1234.5678,0)
------------------
1234
SQL> SELECT TRUNC(1234.5678,-1) FROM DUAL;
TRUNC(1234.5678,-1)
-------------------
1230
SQL> SELECT TRUNC(1234.5678,-2) FROM DUAL;
TRUNC(1234.5678,-2)
-------------------
1200
SQL> SELECT TRUNC(1234.5678,-3) FROM DUAL;
TRUNC(1234.5678,-3)
-------------------
1000
SQL> SELECT TRUNC(1234.5678,-4) FROM DUAL;
TRUNC(1234.5678,-4)
-------------------
0
SQL> -- FLOOR(value) ближайшее целое, не превышающее value
SQL> SELECT FLOOR(100), FLOOR(100.5), FLOOR(99.1), FLOOR(99.5) FROM DUAL;
FLOOR(100) FLOOR(100.5) FLOOR(99.1) FLOOR(99.5)
---------- ------------ ----------- -----------
100 100 99 99
SQL>
SQL> -- CEIL(value) - ближайшее целое, не меньшее чем value
SQL> SELECT CEIL(100), CEIL(100.5), CEIL(99.1), CEIL(99.5) FROM DUAL;
CEIL(100) CEIL(100.5) CEIL(99.1) CEIL(99.5)
---------- ----------- ---------- ----------
100 101 100 100
SQL>
SQL>
SQL> --
SQL> -- ИСПОЛЬЗОВАНИЕ МАТЕМАТИЧЕСКИХ ФУНКЦИЙ
SQL> --
SQL> SELECT -10, +10, ABS(-1), ABS(10) FROM DUAL;
-10 +10 ABS(-1) ABS(10)
---------- ---------- ---------- ----------
-10 10 1 10
SQL> SELECT 4, SQRT(4) FROM DUAL;
4 SQRT(4)
---------- ----------
4 2
SQL> SELECT SIN(1), COS(1), ASIN(0), ACOS(0) FROM DUAL;
SIN(1) COS(1) ASIN(0) ACOS(0)
---------- ---------- ---------- ----------
.841470985 .540302306 0 1.57079633
SQL> SELECT EXP(LOG(10, 10)) FROM DUAL;
EXP(LOG(10,10))
---------------
2.71828183
SQL> SELECT POWER(2,3) + SQRT(4) FROM DUAL;
POWER(2,3)+SQRT(4)
------------------
10
SQL> SELECT MOD(5,3) FROM DUAL;
MOD(5,3)
----------
2
SQL> SELECT SQRT( POWER(number_value, 2)) FROM test_3;
SQRT(POWER(NUMBER_VALUE,2))
---------------------------
1234.56
6543.21
99.99
SQL> --
SQL> -- ТЕКСТОВЫЕ ФУНКЦИИ
SQL> --
SQL>
SQL> -- преобразование регистра текстовой строки
SQL> SELECT string_value,
2 UPPER(string_value) "Upper case",
3 LOWER(string_value) "Lower case"
4 FROM test_3;
STRING_VALUE Upper case Lower case
-------------------- -------------------- --------------------
AbcdeF ABCDEF abcdef
fEDCBa FEDCBA fedcba
xyz. abc XYZ. ABC xyz. abc
SQL>
SQL> -- преобразование регистра букв слов
SQL> SELECT string_value, INITCAP(string_value) "Initial capital" FROM test_3;
STRING_VALUE Initial capital
-------------------- --------------------
AbcdeF Abcdef
fEDCBa Fedcba
xyz. abc Xyz. Abc
SQL>
SQL> -- вычисление длины строки
SQL> SELECT string_value, LENGTH(string_value) "String length" FROM test_3;
STRING_VALUE String length
-------------------- -------------
AbcdeF 6
fEDCBa 6
xyz. abc 8
SQL>
SQL> -- извлечение подстроки SUBSTR(строка, начальный индекс, длина)
SQL> -- (начальный симовол имеет индекс = 1, отрицальный индекс означает
SQL> -- отсчет позиции с конца строки)
SQL> SELECT SUBSTR('ABCDEfgh', 4, 3) FROM DUAL;
SUB
---
DEf
SQL> SELECT string_value, SUBSTR(string_value, -4, 2) FROM test_3;
STRING_VALUE SU
-------------------- --
AbcdeF cd
fEDCBa DC
xyz. abc a
SQL>
SQL> -- поиск подстроки в строке INSTR(строка, что_искать, начальный индекс)
SQL> SELECT INSTR('abcdefg123456', 'ef', 1) FROM DUAL;
INSTR('ABCDEFG123456','EF',1)
-----------------------------
5
SQL> SELECT INSTR('abcdefg123456', 'ef', -3) FROM DUAL;
INSTR('ABCDEFG123456','EF',-3)
------------------------------
5
SQL> -- показать по три символа начиная с '.' из поля string_value
SQL> -- для строк таблицы поле где string_value содержит символ '.'
SQL> SELECT SUBSTR(string_value, INSTR(string_value, '.', 1), 3)
2 FROM test_3
3 WHERE string_value LIKE '%.%';
SUB
---
. a
SQL>
SQL> -- LTRIM(string, symbols_set) - отсечение ведущих символов из набора