изучение основных встроенных функций, системных переменных и выражений языка PL/SQL

Лабораторная работа №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. Математические функции:

– вычислить результат выражения:

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. Функции преобразования данных

– Дана строка ’(50 plus 90) divided by 10’ используя текстовые функции и функции преобразования данных вычислить ее результат в виде числа

 

Примеры

--

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)

------------------

 

SQL> SELECT ROUND(1234.5678,-1) FROM DUAL;

 

ROUND(1234.5678,-1)

-------------------

 

SQL> SELECT ROUND(1234.5678,-2) FROM DUAL;

 

ROUND(1234.5678,-2)

-------------------

 

SQL> SELECT ROUND(1234.5678,-3) FROM DUAL;

 

ROUND(1234.5678,-3)

-------------------

 

SQL> SELECT ROUND(1234.5678,-4) FROM DUAL;

 

ROUND(1234.5678,-4)

-------------------

 

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)

------------------

 

SQL> SELECT TRUNC(1234.5678,-1) FROM DUAL;

 

TRUNC(1234.5678,-1)

-------------------

 

SQL> SELECT TRUNC(1234.5678,-2) FROM DUAL;

 

TRUNC(1234.5678,-2)

-------------------

 

SQL> SELECT TRUNC(1234.5678,-3) FROM DUAL;

 

TRUNC(1234.5678,-3)

-------------------

 

SQL> SELECT TRUNC(1234.5678,-4) FROM DUAL;

 

TRUNC(1234.5678,-4)

-------------------

 

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)

------------------

 

SQL> SELECT MOD(5,3) FROM DUAL;

 

MOD(5,3)

----------

 

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)

-----------------------------

 

SQL> SELECT INSTR('abcdefg123456', 'ef', -3) FROM DUAL;

 

INSTR('ABCDEFG123456','EF',-3)

------------------------------

 

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) - отсечение ведущих символов из набора

SQL> -- RTRIM(string, symbols_set) - отсечение концевых символов из набора

SQL> -- если набор не указан, то подразумевается пробел

SQL> SELECT LTRIM(' abcABC') FROM DUAL;

 

LTRIM(

------

abcABC

 

SQL> SELECT LTRIM(' abcABC', ' abc') FROM DUAL;

 

LTR

---

ABC

 

SQL> SELECT RTRIM('abcABC ') FROM DUAL;

 

RTRIM(

------

abcABC

 

SQL> SELECT RTRIM('abcABC ', ' ABC') FROM DUAL;

 

RTR

---

abc

 

SQL> -- TRIM - универсальная функция отсчечения символов

SQL> SELECT TRIM(' ' FROM ' abcABCxyz ') FROM DUAL;

 

TRIM(``FR

---------

abcABCxyz

 

SQL> SELECT TRIM(LEADING ' ' FROM ' abcABCxyz ') FROM DUAL;

 

TRIM(LEADI

----------

abcABCxyz

 

SQL> SELECT TRIM(TRAILING ' ' FROM ' abcABCxyz ') FROM DUAL;

 

TRIM(TRAIL

----------

abcABCxyz

 

SQL> SELECT TRIM(BOTH ' ' FROM ' abcABCxyz ') FROM DUAL;

 

TRIM(BOTH

---------

abcABCxyz

 

SQL> SELECT TRIM('a' FROM 'abcABCxyz ') FROM DUAL;

 

TRIM(`A`F

---------

bcABCxyz

 

SQL> SELECT TRIM('b' FROM 'bbbABCbb') FROM DUAL;

 

TRI

---

ABC

 

SQL> SELECT TRIM(LEADING 'z' FROM 'zzzABCxyz') FROM DUAL;

 

TRIM(L

------

ABCxyz

 

SQL>

SQL> -- замена символов в строке REPLACE(строка, что_искать, на_что_заменить)

SQL> SELECT REPLACE('ab_1_cd_1_ef', '_1_', ' ') FROM DUAL;

 

REPLACE(

--------

ab cd ef

 

SQL>

SQL> -- сцепление строк

SQL> SELECT CONCAT('abc', 'XYZ') FROM DUAL;

 

CONCAT

------

abcXYZ

 

SQL> SELECT CONCAT('xyz', CONCAT('abc', 'XYZ')) FROM DUAL;

 

CONCAT(`X

---------

xyzabcXYZ

 

SQL> SELECT 'abc' || 'XYZ' FROM DUAL;

 

'ABC'|

------

abcXYZ

 

SQL> SELECT 'xyz' || 'abc' || 'XYZ' FROM DUAL;

 

'XYZ'||`A

---------

xyzabcXYZ

 

SQL>

SQL> --

SQL> -- Функции работы с данными типа DATE

SQL> --

SQL>

SQL> -- округление даты ROUND(дата, строка_формата)

SQL> SELECT date_value, ROUND(date_value, 'YEAR') FROM test_3;

 

DATE_VALUE ROUND(DATE

---------- ----------

2004-01-12 2004-01-01

2004-10-04 2005-01-01

2004-12-31 2005-01-01

 

SQL> SELECT date_value, ROUND(date_value, 'MONTH') FROM test_3;

 

DATE_VALUE ROUND(DATE

---------- ----------

2004-01-12 2004-01-01

2004-10-04 2004-10-01

2004-12-31 2005-01-01

 

SQL>

SQL> -- отсечение информации о времени TRUNC(дата, строка_формата)

SQL> SELECT SYSDATE, TRUNC(SYSDATE) FROM DUAL;

 

SYSDATE TRUNC(SYSD

---------- ----------

2004-07-24 2004-07-24

 

SQL> SELECT date_value, TRUNC(date_value, 'YEAR') FROM test_3;

 

DATE_VALUE TRUNC(DATE

---------- ----------

2004-01-12 2004-01-01

2004-10-04 2004-01-01

2004-12-31 2004-01-01

 

SQL> SELECT date_value, TRUNC(date_value, 'MONTH') FROM test_3;

 

DATE_VALUE TRUNC(DATE

---------- ----------

2004-01-12 2004-01-01

2004-10-04 2004-10-01

2004-12-31 2004-12-01

 

SQL> -- добавим строку содержащую текущую дату и время

SQL> INSERT INTO test_3 VALUES ('str', 10, 'A', SYSDATE);

 

1 row created.

 

SQL> -- попробуем найти строки с текущей ДАТОЙ

SQL> -- не корректно:

SQL> SELECT date_value FROM test_3 WHERE date_value = SYSDATE;

 

DATE_VALUE

----------

2004-07-24

 

SQL> -- корректно:

SQL> SELECT date_value FROM test_3 WHERE TRUNC(date_value) = TRUNC(SYSDATE);

 

DATE_VALUE

----------

2004-07-24

 

SQL>

SQL> -- ADD_MONTH - добавить заданное число месяцев к дате

SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;

 

SYSDATE ADD_MONTHS

2004-07-24 2004-09-24   SQL>

Not found

SQL> SELECT DECODE(symbol_value, 'A', 'Letter A', 2 'Z', 'Letter Z', 3 'Unknown letter')