Oracle SQL (RU)

    Чтобы соединиться с БД Oracle, нужно знать: username, password и server.
Принят такой синтаксис передачи информации о соединении:
username/password@server.
Username также называют словом “схема”. 
Когда пишешь кому-то, в какой схеме что-то происходит, 
нужно написать об этом в формате “на username/password@server” 
или “на username@server”, если не хочется говорить пароль.
    Для проверки связи с сервером нужно по тнс пинговать сервер из командной 
строки (cmd): tnsping myserver
Команда tnsping выдаст путь к файлам sqlnet.ora и tnsnames.ora из которых 
клиент получает информацию, куда физически нужно подключаться, 
чтобы попасть на заданный сервер Oracle (в данном случае myserver). 
Tnsping выдаст либо время в милисекундах, за которое установлено соединение с 
сервером (например 30 msec), либо выругается на недоступность или 
некорректное имя сервера.
    Далее соединиться с сервером через sql-plus:
1) Сначала из командной строки:
C:\>start sqlplus myusername/mypassword@myserver 
2) И выполнить запрос: 
SELECT SYSDATE FROM DUAL

Таблица DUAL всегда есть в Oracle и всегда имеет единственную запись.
Ее можно использовать для тестирования системных функций.

Длина имен таблиц, полей и прочих объектов в oracle не может превышать 30 символов.

Основные типы данных oracle таковы:
INTEGER – целочисленный тип,
DATE – дата (которую можно форматировать множеством образов),
NUMBER(M, N) – число с плавающей точкой. M знаков всего, из них N после плавающей точки.
VARCHAR2(M) – строка длиной не более M символов, память тратится оптимально,
CHAR(M) – строка длиной ровно N символов, если вставлять более 
короткую строку, она дополнится пробелами.

Задача 1.1.
    Перечислите DML, DDL, DCL и TC команды, а также расшифруйте эти аббревиатуры.
Решение:
--1) Data manipulation language (DML)
SELECT
INSERT
UPDATE
DELETE
MERGE
--2) Data definition language (DDL)
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT
--3) Data control language (DCL)
GRANT
REVOKE
--4) Transaction control (TC)
COMMIT
ROLLBACK
SAVEPOINT

/* 1 Создание таблицы с самогенерируемым полем COL_ID и указанием этого поля 
в качестве первичного ключа. Плюс создать: колонку числовую, со значением
по-умолчанию 99.99, колонку строковую с длиной 100, колонку с датой, в которой
недопустимы NULL значения */

(
CREATE TABLE TAB1(COL_ID NUMBER GENERATED AS IDENTITY,
                  COL_NUMB NUMBER(8,2) DEFAULT 99.99,
                  COL_DATE DATE NOT NULL,
                  COL_STR VARCHAR2(100),
                  CONSTRAINT TAB1 PRIMARY KEY (COL_ID));
)


/* 2 Создание таблицы из другой таблицы с данными в колонках */
(
CREATE TABLE TAB1_COPY AS SELECT * FROM TAB1;
)



/* 3 Создание таблицы из другой таблицы без данных в колонках */
(
CREATE TABLE TAB1_COPY_EMPTY AS SELECT *
                                    FROM TAB1
                                        WHERE 0 = 1;
)


/* Комментарий к таблице */
(
COMMENT ON TABLE TAB1 IS 'МойКомментарийКТаблице';
)

/* Комментарий к колонкам */
(
COMMENT ON COLUMN TAB1.COL_NUMB IS 'МояКолонкаNumber';
COMMENT ON COLUMN TAB1.COL_DATE IS 'МояКолонкаDate';
)



/* Вставка значений в колонки таблицы вручную через код */
(
INSERT INTO TAB1(COL_NUMB,
                 COL_DATE,
                 COL_STR) VALUES(100.10,
                                 TO_DATE('01.01.2019','DD.MM.YYYY'),
                                 'один');
COMMIT;
)

    

/* Вставка значений в колонки таблицы из колонок другой таблицы */
(
INSERT INTO TAB1_COPY(COL_ID,
                      COL_NUMB,
                      COL_DATE,
                      COL_STR) SELECT COL_ID,
                                      COL_NUMB,
                                      COL_DATE,
                                      COL_STR
                                   FROM TAB1;
COMMIT;
)                     



/* Добавление колонки к таблице */
(
ALTER TABLE TAB1 ADD COL_4 VARCHAR2(100);
)



/* Изменить тип колонки */
(
ALTER TABLE TAB1 MODIFY COL_4 NUMBER;
)                     


                      
/* Изменить колонку, чтобы она могла принимать значения NULL, либо NOT NULL */
(
ALTER TABLE TAB1 MODIFY COL_4 NULL /* NOT NULL */;
)                     


/* Переименование колонки */
(
ALTER TABLE TAB1 RENAME COLUMN COL_4 TO COL_5;
)

/* Переименование таблицы */
(
ALTER TABLE TAB1 RENAME TO TAB2;
)

/* Удаление колонки */
(
ALTER TABLE TAB1 DROP COLUMN COL_4;
)

/* Удаление таблицы */
(
DROP TABLE TAB1;        
)

Задача 1. Удаление дубликатов.
Цель:
Удалить дубликаты строк из таблицы.
(
Решение:
1. Создадим таблицу.
CREATE TABLE USERS(FAMILY VARCHAR2(100));
2. Заполним таблицу данными.
INSERT INTO USERS(FAMILY) VALUES('Иванов');
INSERT INTO USERS(FAMILY) VALUES('Петров');
INSERT INTO USERS(FAMILY) VALUES('Сидоров');
INSERT INTO USERS(FAMILY) VALUES('Иванов');
INSERT INTO USERS(FAMILY) VALUES('Иванов');
INSERT INTO USERS(FAMILY) VALUES('Петров');
COMMIT;
3. Далее посмотрим таблицу. Причем выведем системную колонку ROWID. В ней записан
физический адрес строки. При UPDATE строк их ROWID не меняются. Также очень быстро
выполняется поиск строки по значению ROWID.
SELECT ROWID,
       FAMILY
    FROM USERS t
4. Так как у каждой строки есть свой ROWID, то применим функцию MIN и 
получим минимальные ROWID для каждой строки, тем самым оставив уникальные строки.
SELECT MIN(ROWID),
       FAMILY
    FROM USERS
        GROUP BY FAMILY
5. Удалим строки с ROWID, которые не входят в запрос из п.4
DELETE 
    FROM USERS 
        WHERE ROWID NOT IN (SELECT MIN(ROWID)
                                FROM USERS
                                    GROUP BY FAMILY);
COMMIT;                                 
P.S.
Если в таблице есть первичный ключ или другие уникальные ключи, то можно
использовать их.
)


Задача 2. Удаление по условию NOT IN
Цель:
Создать таблицу PROD с колонками PRODUCT, DATE1, PRICE.
Оставить в таблице строки с максимальным значением DATE1 для каждого PRODUCT.
(
Решение:
1. Создадим таблицу.
CREATE TABLE PROD(PRODUCT VARCHAR2(100),
                  DATE1 DATE,
                  PRICE NUMBER(3));
2. Вставим строки в таблицу.
INSERT INTO PROD(PRODUCT,
                 DATE1,
                 PRICE) VALUES ('Клавиатура',
                                TO_DATE('01.01.2019','DD.MM.YYYY'),
                                100);
INSERT INTO PROD(PRODUCT,
                 DATE1,
                 PRICE) VALUES ('Клавиатура',
                                TO_DATE('02.01.2019','DD.MM.YYYY'),
                                110);
INSERT INTO PROD(PRODUCT,
                 DATE1,
                 PRICE) VALUES ('Мышь',
                                TO_DATE('01.01.2019','DD.MM.YYYY'),
                                50);
INSERT INTO PROD(PRODUCT,
                 DATE1,
                 PRICE) VALUES ('Монитор',
                                TO_DATE('03.01.2019','DD.MM.YYYY'),
                                800);       
INSERT INTO PROD(PRODUCT,
                 DATE1,
                 PRICE) VALUES ('Монитор',
                                TO_DATE('10.01.2019','DD.MM.YYYY'),
                                900);
COMMIT;                                 
3. Оставим только строки с максимальным значением DATE1 для каждого PRODUCT.                             
DELETE
    FROM PROD
        WHERE (PRODUCT, DATE1) NOT IN (SELECT PRODUCT,
                                              MAX(DATE1)
                                           FROM PROD 
                                               GROUP BY PRODUCT);
COMMIT;         
4. Сделаем запрос и проверим.
SELECT *
    FROM PROD
)


Задача 3. Замена EXISTS/NOT EXISTS на LEFT JOIN + ROWID
Цель: 
Создать таблицу PROD_2 продуктов с одной колонкой PRODUCT, 
и сформировать запрос, который выдаст строки из таблицы из задачи 2, 
которые по колонке PRODUCT совпадают с новой таблицей.
(
Решение:
1. Создадим таблицу.
CREATE TABLE PROD_2(PRODUCT VARCHAR2(100));
2. Вставим строки.
INSERT INTO PROD_2(PRODUCT) VALUES('Клавиатура');
INSERT INTO PROD_2(PRODUCT) VALUES('Мышь');
COMMIT;
3. Сделаем запрос к таблице PROD используя EXISTS. Результатом которого 
будут сущности, которые содержатся в таблице PROD_2.
SELECT t1.*
    FROM PROD t1
        WHERE EXISTS (SELECT t2.* 
                          FROM PROD_2 t2
                              WHERE t1.PRODUCT = t2.PRODUCT)
4. Перепишем запрос из п.3, заменив EXISTS на LEFT JOIN + ROWID IS NOT NULL.
SELECT t1.*
    FROM PROD t1
        LEFT JOIN PROD_2 t2
        ON t1.PRODUCT = t2.PRODUCT
            WHERE t2.ROWID IS NOT NULL
5. Сделаем запрос к таблице PROD используя NOT EXISTS. Результатом которого 
будут сущности, которые не содержатся в таблице PROD_2.     
SELECT t1.*
    FROM PROD t1
        WHERE NOT EXISTS (SELECT t2.*
                             FROM PROD_2 t2
                                 WHERE t1.PRODUCT = t2.PRODUCT)
6. Перепишем запрос из п.5, заменив NOT EXISTS на LEFT JOIN + ROWID IS NULL.
SELECT t1.*
    FROM PROD t1
        LEFT JOIN PROD_2 t2
        ON t1.PRODUCT = t2.PRODUCT
            WHERE t2.ROWID IS NULL
P.S. следует сравнивать производительность двух способов и выбирать наибыстрейший.         
)

Функции по работе со строками.
Строки в Оracle нумеруются с 1, а не с 0.
Выполним запрос, чтобы получить результат функций.
SELECT SUBSTR('myoracle', 2, 3), --подстрока, начиная со 2ой позиции длиной 3 символа
       SUBSTR('myoracle', 3), --подстрока, начиная с 3ей позиции и до конца
       SUBSTR('myoracle', -4, 2), --подстрока, начиная с 4 позиции с конца длиной 2 символа
       SUBSTR('myoracle', -5), --подстрока, начиная с 5 позиции с конца и до конца
       INSTR('myoracleora', 'ora'), --позиция первого символа подстроки 'ora' в строке 'myoracle',
                                    --если подстрока не найдется, то результат = 0
       INSTR('clmyoracle', 'cl', 2), --позиция первого символа подстроки 'cl' в строке 'myoracle',
                                     --но поиск выполняется со 2ой позиции.    
       REPLACE('myoracleora', 'ora', '2'), --замена подстроки 'ora' на '2' 
       REPLACE('myoracleora', 'ora'), --удаление подстроки 'ora'
       TRANSLATE('myoraclec', 'cl', '15'), --замена символов из подстроки 'cl' на символы из
                                           --подстроки '15' посимвольно
       TRANSLATE('myoracleca', 'cl', '  '), --замена символов из подстроки 'cl' на пробелы из
                                            --подстроки '  ' посимвольно
       REPLACE(TRANSLATE('myoracleca', 'cl', '  '), ' '), --замена символов из подстроки 'cl' на
                                                          --пробелы, а затем их удаление
       ASCII('Z'), --ascii-код символа 'Z'
       ASCII(' '), --ascii-код символа ' '
       ASCII('Я'), --ascii-код символа 'Я'
       ASCII('Яфы51ф3 =+'), --ascii-код последовательности равен ascii-коду первого символа
                            --этой последовательности
       CHR('223'), --символ с кодом 223
       'ora'||' '||'cle', -- конкатенация (сцепление) 3 строк
       UPPER('orA'), --преобразуем строки в верхний регистр
       LOWER('orA'), --преобразуем строки в нижний регистр
       LTRIM('rrracle', 'r'), --удалим символы 'r' слева
       LTRIM('   oracle'), --удалим пробелы слева
       RTRIM('oracleee', 'e'), --удалим символы 'e' справа
       RTRIM('oracle   '), --удалим пробелы справа
       TRIM(' oracle  '), --удалим пробелы слева и справа
       LPAD('oracle', 12, '1'), --дополним строку слева до длина 12 символов символами '1' 
       LPAD('oracle', 12), --дополним строку слева до длины 12 символов пробелами
       RPAD('oracle', 12, '1'), --дополним строку справа до длины 12 символов символами '1'
       RPAD('oracle', 12), --дополним строку справа до длины 12 символов пробелами
       REVERSE('Я иду с мечем судия') --прочтение строки в обратном порядке
    FROM DUAL;

Функции по работе с числами.
SELECT ROUND(1.125, 2), --округление до двух знаков после запятой
       ROUND(1.125), --округление до целого числа
       TRUNC(1.125), --отсечь (получить) целую часть числа
       TRUNC(1.125, 2), --отсечь (получить) все цифры до 2 знака после запятой
       CEIL(1.125), --следующее целое число
       TO_CHAR(1.125, '0.99'), --преобразование в текстовый тип к формату 0.00
       TO_CHAR(1.125, '$0.99'), --преобразование в текстовый тип к формату 0.00 со знаком
       TO_NUMBER('1.125'), --перевод строки в число (разделитель целой и дробной части
                           --определяется настройками Excel).
       TO_NUMBER('1.125', '0.999'), --преобразование строки в число с форматом
       CAST('1.125' AS NUMBER), --другой способ преобразования строки в число
       GREATEST(1, 2, 3), --наибольшее из совокупности чисел
       LEAST(1, 2, 3), --наименьшее из совокупности чисел
       MOD(5, 2), --остаток от деления 9 на 4
       POWER(2, 4), --2 в степени 4
       SIGN(2), --знак числа      
       SIGN(0), --знак числа
       SIGN(-2), --знак числа
       TO_NUMBER('1,125', '999D999', 'NLS_NUMERIC_CHARACTERS='', '''), --преобразование
       --строки в число с параметрами, где запятая - разделитель целой и дробной части,
       --а пробел - разделитель групп разрядов
       TO_NUMBER('1 000 000,125', '999G999G999D999', 'NLS_NUMERIC_CHARACTERS='', ''')
    FROM DUAL;

Логический тип, конструкция WITH, функции NVLCOALESCECASEDECODE
В Oracle для неопределенного/пустого значения в полях используется NULL.
NULL может быть результатом логического сравнения, например X > 1, где X = NULL.
Результаты логических сравнений:
    TRUE  AND TRUE  -> TRUE
    TRUE  AND FALSE -> FALSE
    FALSE AND FALSE -> FALSE
    TRUE  AND NULL  -> NULL
    FALSE AND NULL  -> FALSE
    TRUE  OR  TRUE  -> TRUE
    TRUE  OR  FALSE -> TRUE
    FALSE OR  FALSE -> FALSE
    TRUE  OR  NULL  -> TRUE
    FALSE OR  NULL  -> NULL

Подзапросы в ORACLE можно именовать с помощью конструкции WITH.
Например:
WITH TEMP1 AS
(SELECT 1 COL1 FROM DUAL),
     TEMP2 AS
(SELECT 2 COL2 FROM DUAL)

Также с помощью WITH можно создавать временные функции:
1) Создадим временную функцию и получим значение с помощью нее.
WITH 
FUNCTION FNC_TEMP (x IN NUMBER DEFAULT 0) RETURN NUMBER IS
    INFO VARCHAR2(100) := 'Функция прибавляющая 5 к числу';
    y NUMBER;
BEGIN
    y := x + 5;
    RETURN y;
END FNC_TEMP;
SELECT FNC_TEMP(1)
    FROM DUAL


Функции NVLNVL COALESCE и конструкция CASEDECODE
WITH TEMP1 AS
(
SELECT NULL C1,
       NULL C2,
       1 C3,
       2 C4,
       3 C5
    FROM DUAL
)
SELECT /* выдать первый аргумент, если он IS NULL, то второй аргумент */
       NVL(C1, C3), 
       NVL(C4, C5),
       /* выдать NULL, если оба аргумента равны, иначе выдать первый аргусент */
       NULLIF(C3, C4),
       /* выдать первый NOT NULL аргумент из списка */
       COALESCE(C1, C2, C3, C4),
       /* получить результат по первому совпавшему условию  */
       CASE
           WHEN C1 IS NULL THEN
               C4
           WHEN C3 = 1 THEN
               C5
           ELSE
               0
       END
       /* альтернатива CASE'у: WHEN С1 = NULL THEN С4, WHEN C1 = 2 THEN С5 ELSE 0 */
       DECODE(C1, NULL, C4,
                  2, C5,
                  0)
    FROM TEMP1

Так как NULL служит результатом логического выражения и значением пустого поля, то
перечислим особенности его применения.
1. 
Запрос-поиск пустого значения строкового поля
SELECT *
    FROM TAB1
        WHERE COL1 = ''
и
запрос-поиск значения NULL
SELECT *
    FROM TAB1
        WHERE COL1 IS NULL
дадут одинаковые результаты.
2. 
В арифметических операциях результат выражения будет равен NULL.
Пример:
SELECT 1 + NULL,
       1 - NULL,
       1 * NULL,
       1 / NULL
    FROM DUAL
3. 
В агрегатных и аналитических функциях NULL игнорируется.
WITH TEMP1 AS
(
SELECT 1 COL1
    FROM DUAL
UNION ALL   
SELECT 3 COL1
    FROM DUAL
UNION ALL
SELECT NULL COL1
    FROM DUAL
)
SELECT MIN(COL1) "Минимум",
       MAX(COL1) "Максимум",
       AVG(COL1) "СреднееАрифм",
       SUM(COL1) "Сумма",
       COUNT(COL1) "КоличествоЗначений",
       LISTAGG(COL1, '; ')
              WITHIN GROUP (ORDER BY COL1) "НеNULLЗначенияЧерезРазделитель"
    FROM TEMP1
4.
Для указания порядка NULL в результате сортировки ORDER BY существуют опции 
NULLS FIRST и NULLS LAST
(
WITH TEMP1 AS
(
SELECT 1 COL1
    FROM DUAL
UNION ALL
SELECT 2 COL1
    FROM DUAL
UNION ALL
SELECT NULL COL1
    FROM DUAL
)
SELECT COL1
    FROM TEMP1
        ORDER BY COL1 NULLS FIRST /* или NULLS LAST */

        
Основы PL/SQL
PL/SQL код оформляется в блок, состоящий из: объвление переменных, выполняемый код и 
обработка исключений.
Пример анонимного блока, в котором в переменную I записывается системная дата:
DECLARE
    I DATE;
BEGIN
    SELECT SYSDATE INTO I
        FROM DUAL;
EXCEPTION
    WHEN OTHERS THEN
    NULL;
END;

Пример простой функции, возвращающей системную дату:
CREATE OR REPLACE FUNCTION MY_FUNCTION RETURN DATE AS
    I DATE;
BEGIN
    SELECT TO_DATE(SYSDATE,'DD.MM.YYYY') INTO I 
        FROM DUAL;
    RETURN I;
END MY_FUNCTION;

Использование фукнции:
SELECT MY_FUNCTION 
    FROM DUAL
    
ЗАДАЧА 4. 
    Реализовать PL/SQL функции (с рекурсией и без), считающие факториал числа:
    Решение:
Вариант 1 с использованием цикла 
    CREATE OR REPLACE FUNCTION FNC_FACTORIAL (intX IN INTEGERRETURN INTEGER IS
        INFO VARCHAR2(1000) := 'Функция вычисляющая факториал числа';
        intY INTEGER :=1;
    BEGIN
        FOR i IN 1 .. intX LOOP
            intY := intY * i;
        END LOOP;
        RETURN intY;
    END FNC_FACTORIAL;

Вариант 2 с использованием условного оператора IF и рекурсии
    CREATE OR REPLACE FUNCTION FNC_FACTORIAL_RECURSIVE (intX IN INTEGERRETURN INTEGER IS
        INFO VARCHAR2(1000) := 'Функция, вычисляющая факториал числа';
    BEGIN
        IF intX = 0 THEN
            RETURN 1;
        ELSE
            RETURN intX * FNC_FACTORIAL_RECURSIVE(intX - 1);
        END IF;
    END FNC_FACTORIAL_RECURSIVE;        

ЗАДАЧА 5. 
Реализовать PL/SQL фукнцию, которая считает количество вхождений символа Z (именно
в верхнем регистре) в строке "Model Z-150 have better zoom then model Z-140". 
Вариант 1. Перебирая все буквы по очереди с помощью цикла.
Вариант 2. Постепенно отсекаем строку, после каждого совпадения.
Вариант 3. Вычитаем длины строк.
Решение:
Вариант 1. Перебирая все буквы по очереди с помощью цикла.
CREATE OR REPLACE FUNCTION MY.FNC_Z(x IN VARCHAR2RETURN INTEGER IS
    y INTEGER := 0;
BEGIN
    FOR i IN 1.. LENGTH(x) LOOP
        IF SUBSTR(x, i, 1) = 'Z' THEN
            y:= y + 1;
        END IF;
    END LOOP;
    RETURN y;
END FNC_Z;

Вариант 2. Постепенно отсекаем строку, после каждого совпадения.
CREATE OR REPLACE FUNCTION MY.FNC_Z2 (x IN VARCHAR2RETURN INTEGER IS
    y INTEGER := 0;
    z VARCHAR2(200) := x;
BEGIN
    WHILE INSTR(z,'Z') > 0 LOOP
        Y := Y + 1;
        z := SUBSTR(z,INSTR(z,'Z') + 1);
    END LOOP;
    RETURN Y;
END FNC_Z2;

Вариант 3. Вычитаем длины строк.
CREATE OR REPLACE FUNCTION MY.FNC_Z3 (x IN VARCHAR2RETURN INTEGER IS
    y INTEGER;
BEGIN
    Y := NVL(LENGTH(x), 0) - NVL(LENGTH(REPLACE(x, 'Z')), 0);
    RETURN Y;
END FNC_Z3;

ЗАДАЧА 6.
Реализовать PL/SQL функцию, которая считает количество вхождений подстроки в строку.
Например "ере" в "перерезать". Вариант 1: перекрытия разрешены. Вариант 2: перекрытия 
не разрешены.
Решение:
Вариант 1. Перекрытия разрешены.
CREATE OR REPLACE FUNCTION FNC_FIND (my_str IN VARCHAR2,
                                     my_substr IN VARCHAR2RETURN INTEGER IS
    y INTEGER := 0;
BEGIN
    IF my_str IS NULL THEN
        RETURN 0;
    END IF;
    FOR i IN 1.. LENGTH(my_str) LOOP
        IF INSTR(my_str, my_substr, i) = i THEN
            y:= y + 1;
        END IF;
    END LOOP;
    RETURN y;
END FNC_FIND;

Вариант 2. Перекрытия не разрешены.
CREATE OR REPLACE FUNCTION FNC_FIND2 (in_str IN VARCHAR2
                                      in_substr IN VARCHAR2RETURN INTEGER IS
    res INTEGER := 0;
    x VARCHAR2(4000) := in_str;
BEGIN
    IF in_str IS NULL THEN
        RETURN 0;
    END IF;
    WHILE INSTR(x, in_substr) > 0 LOOP
        res := res + 1;
        x := SUBSTR(x, INSTR(x, in_substr) + LENGTH(in_substr));
    END LOOP;
    RETURN res;
END FNC_FIND2;    
    
ЗАДАЧА 7.
Написать процедуру копирования строк таблицы 1 в таблицу 2 при условии 
одинаковой структуры этих таблиц. Процедура должна иметь параметры 1 = сделать commit
и 0 = не делать commit.
Любая DDL команда, делает commit автоматически.
Решение:
1) Создадим таблицу-источник TABLE_OUT с двумя колонками (COL1, COL2).
CREATE TABLE TABLE_OUT (COL1 VARCHAR2(500), COL2 VARCHAR2(500));
И заполним её данными:
INSERT INTO TABLE_OUT(COL1, COL2) VALUES ('Иванов', 'Иван');
INSERT INTO TABLE_OUT(COL1, COL2) VALUES ('Павлов', 'Павел');
COMMIT;
                                        
2) И еще одну таблицу-приемник TABLE_IN с двумя колонками (COL1, COL2) на
основе TABLE_OUT (только структура, без данных).
CREATE TABLE TABLE_IN AS SELECT COL1,
                                COL2
                             FROM TABLE_OUT
                                 WHERE 0 = 1;
3) Создадим процедуру с входящим параметром
CREATE OR REPLACE PROCEDURE PRC_COPY_TABLE (chrParam IN INTEGERAS
BEGIN
    INSERT INTO TABLE_IN(COL1, COL2)
        SELECT COL1,
               COL2
            FROM TABLE_OUT;
    IF chrParam = 1 THEN
    COMMIT;
    END IF;
END PRC_COPY_TABLE;
4) Выполним процедуру:
BEGIN
    PRC_COPY_TABLE(1);
END;
5) Проверим результат
SELECT COL1, COL2 FROM TABLE_OUT
UNION ALL
SELECT COL1, COL2 FROM TABLE_IN    

ЗАДАЧА 8. 
Создать анонимный блок кода, который для таблицы из двух колонок вставляет
во вторую колонку число, равное количеству одинаковых строк из колонки 1.
Решение:
1) Создадим таблицу 
CREATE TABLE MY_PRODUCTS (COL_ID NUMBER GENERATED AS IDENTITY,
                          PRODUCT VARCHAR2(100),
                          PRODUCT_COUNT NUMBER,
                          CONSTRAINT MY_PRODUCTS PRIMARY KEY (COL_ID));
2) Вставим данные
/* Три раза вставим "Продукт1" */
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт1');
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт1');
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт1');
/* Два раза вставим "Продукт2" */
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт2');
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт2');
/* Добавим другие продукты */
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт3');
INSERT INTO MY_PRODUCTS (PRODUCT) VALUES ('Продукт4');
COMMIT;
3) Напишем анонимный блок кода
BEGIN
    FOR I IN (SELECT PRODUCT, COUNT(*) CNT
                  FROM MY_PRODUCTS
                      GROUP BY PRODUCT) LOOP
        UPDATE MY_PRODUCTS
            SET MY_PRODUCTS.PRODUCT_COUNT = I.CNT
                WHERE MY_PRODUCTS.PRODUCT = I.PRODUCT;
    END LOOP;
END;
4) Посмотрим результат
SELECT *
    FROM MY_PRODUCTS    

ЗАДАЧА 9.
Создать процедуру с циклом, которая будет из колонки таблицы-источника вставлять 
нечетные значения в одну таблицу, а четные значения в другую. Структура таблиц
одинаковая.
Решение:
1) Создадим таблицы.
CREATE TABLE MY_TABLE (COL1 NUMBER);
CREATE TABLE MY_TAB1 (COL1 NUMBER);
CREATE TABLE MY_TAB2 (COL1 NUMBER);
2) Вставим значения в MY_TABLE
INSERT INTO MY_TABLE (COL1) VALUES (1);
INSERT INTO MY_TABLE (COL1) VALUES (2);
INSERT INTO MY_TABLE (COL1) VALUES (3);
INSERT INTO MY_TABLE (COL1) VALUES (4);
INSERT INTO MY_TABLE (COL1) VALUES (5);
COMMIT;
3) Создадим процедуру
CREATE OR REPLACE PROCEDURE PRC_INSERT AS
BEGIN
    FOR i IN (SELECT COL1 FROM MY_TABLE) LOOP
        IF MOD(i.COL1, 2) > 0 THEN
            INSERT INTO MY_TAB1(COL1) VALUES(i.COL1);
            COMMIT;
        ELSE
            INSERT INTO MY_TAB2(COL1) VALUES(i.COL1);
            COMMIT;
        END IF;
    END LOOP;
END PRC_INSERT;
4) Выполним процедуру и проверим результат:
BEGIN
    PRC_INSERT;
END;

SELECT 'MY_TABLE' MY_SOURCE, 
       t.* 
    FROM MY_TABLE t
UNION ALL
SELECT 'MY_TAB1' MY_SOURCE, 
       t1.* 
    FROM MY_TAB1 t1
UNION ALL
SELECT 'MY_TAB2' MY_SOURCE, 
       t2.* 
    FROM MY_TAB2 t2

Использование сиквенсов и получение случаных значений с помощью DBMS_RANDOM.
Сиквенс - это генератор возрастающей последовательности уникальных целых чисел.
Из сиквенса можно выбирать следующее и текущее значение:
SELECT MY_SEQ.CURRVAL FROM DUAL;
SELECT MY_SEQ.NEXTVAL FROM DUAL;

Случайное число от 0 до 1 можно получить с помощью функции DBMS_RANDOM.VALUE():
SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Получить случайную строку можно с помощью DBMS_RANDOM.STRING(x, y), где
x может принимать значения:
'a' или 'A'  alpha characters only (mixed case) --только буквы
'l' или 'L'  lower case alpha characters only --только буквы в нижем регистре
'p' или 'P'  any printable characters --любые печатные символы
'u' или 'U'  upper case alpha characters only --только буквы в верхнем регистре
'x' или 'X'  any alpha-numeric characters (upper), --любые цифры и буквы в верх. регистре
а y - это длина строки, например 3.
SELECT DBMS_RANDOM.STRING('A', 5),
       DBMS_RANDOM.STRING('L', 6),
       DBMS_RANDOM.STRING('P', 7),
       DBMS_RANDOM.STRING('U', 8),
       DBMS_RANDOM.STRING('X', 9)
    FROM DUAL;

ЗАДАЧА 10.
Создать сиквенс. Создать таблицу с двумя колонками: в колонку MY_ID запишем значения
сиквенса. В колонку MY_VAL запишем случайные целые числа о 0 до 999.
Решение:
1) Создадим сиквенс
CREATE SEQUENCE MY_SEQ
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
CACHE 20;
2) Создадим таблицу 
CREATE TABLE MY_TAB (MY_ID INTEGER, MY_VAL INTEGER);
3) Напишем анонимный блок, решающий задачу
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO MY_TAB (MY_ID, MY_VAL) VALUES (MY_SEQ.NEXTVAL,
                                                   TRUNC(1000 * DBMS_RANDOM.VALUE()));
    END LOOP;
END;
4) Посмотрим результат
SELECT *
    FROM MY_TAB
    
ЗАДАЧА 11.
    Написать функцию с входящими параметрами, которая считает число букв-параметра в
строке-параметре. Использовать ее в процедуре.
    Написать процедуру с двумя IN параметрами - фамилией и именем, а также 2 OUT параметрами:
числом букв 'а' в фамилии и числом букв 'б' в имени (без различия регистра букв). 
    Написать анонимный блок, где вывести результат, то есть OUT параметры 
процедуры в DBMS_OUTPUT, причем показать именованную нотацию и неименованную.
    Решение:
1) Создадим функцию
CREATE OR REPLACE FUNCTION FNC_FIND (my_word IN VARCHAR2,
                                     my_chr IN VARCHAR2RETURN INTEGER IS
    info VARCHAR2(200):= 'Функция, возвращающая количество найденных букв в 
                          параметре my_word';
    res INTEGER := 0;
BEGIN
    FOR i IN 1.. LENGTH(my_word) LOOP
        IF SUBSTR(my_word, i, 1) = my_chr THEN
            res := res + 1;
        END IF;
    END LOOP;
    RETURN res;
END FNC_FIND; 
2) Создадим процедуру двумя вариантами с использованием функции FNC_FIND
CREATE OR REPLACE PROCEDURE PRC_FIND (xxx IN VARCHAR2
                                      yyy IN VARCHAR2,
                                      x OUT INTEGER
                                      y OUT INTEGERIS
BEGIN
    x:= FNC_FIND(UPPER(xxx),'А');
    y:= FNC_FIND(UPPER(yyy),'Б');
END PRC_FIND;
3) Выполним процедуру двумя вариантами указания параметров:
Позиционная нотация. Параметры перечисляются в порядке, как в процедуре.
DECLARE
    x1 INTEGER := 0;
    x2 INTEGER := 0;
BEGIN
    DBMS_OUTPUT.ENABLE;
    PRC_FIND('Антонов','Глеб',x1,x2);
    DBMS_OUTPUT.PUT_LINE('Антонов Глеб'||x1||' '||x2);
    PRC_FIND('Глебов','Батон',x1,x2);
    DBMS_OUTPUT.PUT_LINE('Глебов Батон'||x1||x2);
END;
4) Именованная нотация. Перечислять параметры можно в понравившемся порядке, а также
удобно пользоваться значениями по умолчанию.
Пересоздадим процедуру PRC_FIND с со значениями по-умолчанию.
CREATE OR REPLACE PROCEDURE PRC_FIND (xxx IN VARCHAR2 DEFAULT 'Иванов', 
                                      yyy IN VARCHAR2 DEFAULT 'Иван',
                                      x OUT INTEGER,
                                      y OUT INTEGERIS
BEGIN
    x:= FNC_FIND(UPPER(xxx),'А');
    y:= FNC_FIND(UPPER(yyy),'Б');
END PRC_FIND;

Выполним процедуру в именованной нотации
DECLARE
    x1 INTEGER := 0;
    x2 INTEGER := 0;
BEGIN
    DBMS_OUTPUT.ENABLE;
    PRC_FIND(x => x1, 
             y => x2, 
             yyy => 'Борис');
    DBMS_OUTPUT.PUT_LINE('Иванов Борис'||x1||' '||x2);
END;
                                      

ЗАДАЧА 12.
Создать пакет с процедурой и функцией из задачи 11.
Пакеты используются для логической группировки объектов.
Пакет состоит из заголовка и тела.
Решение:
1) Создадим заголовок пакета
CREATE OR REPLACE PACKAGE MY_PCK IS
    FUNCTION FNC_FIND (my_word IN VARCHAR2, my_chr IN VARCHAR2RETURN INTEGER;
    PROCEDURE PRC_FIND (xxx IN VARCHAR2
                        yyy IN VARCHAR2,
                        x OUT INTEGER
                        y OUT INTEGER);
END MY_PCK;
2) Создадим тело пакета
CREATE OR REPLACE PACKAGE BODY MY_PCK IS
    FUNCTION FNC_FIND (my_word IN VARCHAR2,
                       my_chr IN VARCHAR2RETURN INTEGER IS
        info VARCHAR2(200):= 'Функция, возвращающая количество найденных букв в 
                              параметре my_word';
        res INTEGER := 0;
    BEGIN
        FOR i IN 1.. LENGTH(my_word) LOOP
            IF SUBSTR(my_word, i, 1) = my_chr THEN
                res := res + 1;
            END IF;
        END LOOP;
    RETURN res;
    END FNC_FIND; 
    
    PROCEDURE PRC_FIND (xxx IN VARCHAR2
                        yyy IN VARCHAR2,
                        x OUT INTEGER
                        y OUT INTEGERIS
    BEGIN
        x:= FNC_FIND(UPPER(xxx),'А');
        y:= FNC_FIND(UPPER(yyy),'Б');
    END PRC_FIND;
END MY_PCK;
3) Проверим результат.
DECLARE 
    x1 INTEGER := 0;
    x2 INTEGER := 0;
BEGIN
    /* процедуры в пакете вызываются вместе с именем пакета */
    DBMS_OUTPUT.ENABLE;
    MY_PCK.PRC_FIND('Антонов','Глеб',x1,x2);
    DBMS_OUTPUT.PUT_LINE('Антонов Глеб'||x1||' '||x2);
    MY_PCK.PRC_FIND('Глебов','Батон',x1,x2);
    DBMS_OUTPUT.PUT_LINE('Глебов Батон'||x1||x2);
END;

ЗАДАЧА 13.
    Написать чем отличаются COUNT(COL1), COUNT(*), COUNT(1) и COUNT(DISTINCT COL1).
    Решение:
WITH TEMP1 AS
(
SELECT NULL COL1
    FROM DUAL
UNION ALL
SELECT NULL COL1
    FROM DUAL
UNION ALL
SELECT 1 COL1
    FROM DUAL
UNION ALL
SELECT 1 COL1
    FROM DUAL
UNION ALL
SELECT 2 COL1
    FROM DUAL   
)
SELECT COUNT(COL1) /* Будет подсчитано количество строк в COL1, кроме NULL */,
       COUNT(*) /* Будет подсчитано количество всех строк и NULL тоже */,
       COUNT(1) /* Будет подсчитано количество всех строк и NULL тоже */,
       COUNT(DISTINCT COL1) /* Количество строк в COL1 с учетом DISTINCT, кроме NULL */
    FROM TEMP1


ЗАДАЧА 14.
    DATABASE LINKS. Линки используются для связи между базами данных.
Создать DATABASE LINK. Выполнить запрос SELECT * FROM DUAL с помощью линка.
Решение:
1) Создадим линк.
CREATE DATABASE LINK MY_LINK 
CONNECT TO MY_USER 
IDENTIFIED BY MY_PASSWORD 
USING MY_NEW_DBASE;
2) Выполним запрос, используя линк.
SELECT * 
    FROM DUAL@MY_LINK;

ЗАДАЧА 15.
    Создать таблицу с четырьмя колонками: число с автогенерацией, число, строка, дата. 
Заполнить данными и вставить строку с NULL значениями во всех колонках. Написать запрос,
который выдаст скрипт заполнения таблицы из запроса уже имеющимися данными в этой 
таблице.
Решение:
1) Создадим таблицу и наполним ее данными:
CREATE TABLE MY_TAB (COL_ID NUMBER GENERATED AS IDENTITY,
                     COL_NUM NUMBER,
                     COL_CHR VARCHAR2(100),
                     COL_DAT DATE,
                     CONSTRAINT MY_TAB PRIMARY KEY (COL_ID));
INSERT INTO MY_TAB (COL_NUM, COL_CHR, COL_DAT) 
    VALUES (1, 'один', TO_DATE('01.01.2019','DD.MM.YYYY'));
INSERT INTO MY_TAB (COL_NUM, COL_CHR, COL_DAT) 
    VALUES (2, 'два', TO_DATE('02.01.2019','DD.MM.YYYY'));
INSERT INTO MY_TAB (COL_NUM, COL_CHR, COL_DAT) 
    VALUES (3, 'три', TO_DATE('03.01.2019','DD.MM.YYYY'));    
COMMIT;
2) Создадим запрос
SELECT 'INSERT INTO MY_TAB(COL_NUM, COL_CHR, COL_DAT) 
        VALUES ('||COL_NUM||','''
                ||COL_CHR||''',
                TO_DATE('''||TO_CHAR(COL_DAT,'DD.MM.YYYY')||''',''DD.MM.YYYY''));' SCRIPT
    FROM MY_TAB
3) Добавим NULL значения, для усложнения запроса
INSERT INTO MY_TAB (COL_NUM, COL_CHR, COL_DAT) VALUES (NULLNULLNULL);
COMMIT;
4) Перепишем запрос с учетом NULL значений. Причем NVL для NUMBER выдал бы ошибку при
попытке возварата NULL (т.е. NVL(COL_NUM,'NULL') = ошибка), поэтому заменим на DECODE.
SELECT 'INSERT INTO MY_TAB(COL_NUM, COL_CHR, COL_DAT) 
        VALUES ('||DECODE(COL_NUM,NULL,'NULL',COL_NUM)||','
                 ||NVL2(COL_CHR,''''||COL_CHR||'''','NULL')||','
                 ||NVL2(COL_DAT,'TO_DATE('''||TO_CHAR(COL_DAT,'DD.MM.YYYY')||''',''DD.MM.YYYY'')',
                        'NULL')||');' SCRIPT
    FROM MY_TAB
    
    
ЗАДАЧА 16.
    Выдать другому пользователю права на: выполнение запроса к таблице; вставку, изменение,
удаление строк для таблицы; изменение таблицы; все права на таблицу; права на выполнение 
пакета; на создание линка. Забрать права на вставку, изменение, удаление строк таблицы.
Решение:
Права доступа к объектам текущей схемы для других пользователей устанавливаются командой
GRANT. Забираются командой REVOKE.                    
/* Дать права на SELECT из таблицы MY_TAB */
GRANT SELECT 
    ON MY_SCHEMA.MY_TAB 
        TO MY_SCHEMA2;
/* Дать права на INSERTUPDATEDELETE таблицы MY_TAB */
GRANT INSERTUPDATEDELETE 
    ON MY_SCHEMA.MY_TAB 
        TO MY_SCHEMA2;
/* Заберем права на INSERTUPDATEDELETE таблицы MY_TAB */
REVOKE INSERTUPDATEDELETE
    ON MY_SCHEMA.MY_TAB
        FROM MY_SCHEMA2;
/* Дать права на ALTER таблицы TAB1 */
GRANT ALTER
    ON MY_SCHEMA.MY_TAB 
        TO MY_SCHEMA2;
/* Дать все права на таблицу MY_TAB */
GRANT ALL
    ON MY_SCHEMA.MY_TAB
        TO MY_SCHEMA2;
/* Дать права на выполнение пакета MY_PCK */
GRANT EXECUTE
    ON MY_SCHEMA.MY_PCK
        TO MY_SCHEMA2;
/* Дать права создавать DATABASE LINK */
GRANT CREATE DATABASE LINK 
    TO MY_SCHEMA2;    
    
    
ЗАДАЧА 17.
    Показать операции над множествами: объединить с исключением дубликатов;
объединить без исключения дубликатов; разность множеств; пересечение множеств.
    Решение:
1) Создадим две таблицы с одинаковой структурой.
DROP TABLE MY_TAB1;
DROP TABLE MY_TAB2;
CREATE TABLE MY_TAB1 AS 
    SELECT 1 COL1 FROM DUAL
    UNION ALL
    SELECT 1 COL1 FROM DUAL
    UNION ALL
    SELECT 2 COL1 FROM DUAL
    UNION ALL
    SELECT 2 COL1 FROM DUAL 
    UNION ALL
    SELECT NULL COL1 FROM DUAL; 
CREATE TABLE MY_TAB2 AS
    SELECT 2 COL1 FROM DUAL
    UNION ALL
    SELECT 2 COL1 FROM DUAL 
    UNION ALL
    SELECT 3 COL1 FROM DUAL
    UNION ALL
    SELECT 3 COL1 FROM DUAL
    UNION ALL
    SELECT NULL COL1 FROM DUAL;     
2) Объединение с исключением дубликатов и автоматической сортировкой результата ASC.
Cовпадающие NULL-строки останутся в виде одной NULL-строки
SELECT COL1 FROM MY_TAB1
UNION
SELECT COL1 FROM MY_TAB2
3) Объединение без исключения дубликатов. 
SELECT COL1 FROM MY_TAB1
UNION ALL
SELECT COL1 FROM MY_TAB2
4) Разность множеств. Совпадающие строки уберуться и вернутся значения, имеющиеся 
только в первой таблице (дубликаты уберутся).
SELECT COL1 FROM MY_TAB1
MINUS
SELECT COL1 FROM MY_TAB2
5) Пересечение множеств. Вернутся только совпадающие строки (дубликаты уберутся, 
но совпадающие NULL-строки останутся в виде одной NULL-строки).
SELECT COL1 FROM MY_TAB1
INTERSECT
SELECT COL1 FROM MY_TAB2    


ЗАДАЧА 18.
    Сравнение множеств. Создать две таблицы. 
Таблица1 называется "WAREHOUSE1" содержит колонки "ITEM" и "QUANTITY". 
Наполнить значениями соответственно: 'ITEM1' - 10, 'ITEM2' - 20, 'ITEM3' - 30. 
Таблица2 называется "WAREHOUSE2" с такими же колонками, но со значениями:
'ITEM2' - 20, 'ITEM3' - 30, 'ITEM4' - 40. Сделать запрос, сравнивающий две таблицы.
Результат показать в трёх колонках: ITEM, QUANTITY_WAREHOUSE1, QUANTITY_WAREHOUSE2.
    Решение:
1) Создадим таблицы и наполним их данными.
CREATE TABLE WAREHOUSE1 (COL_ID NUMBER GENERATED AS IDENTITY,
                         ITEM VARCHAR2(100),
                         QUANTITY NUMBER,
                         CONSTRAINT WAREHOUSE1 PRIMARY KEY (COL_ID));
INSERT INTO WAREHOUSE1 (ITEM,QUANTITY) VALUES ('ITEM1',10);
INSERT INTO WAREHOUSE1 (ITEM,QUANTITY) VALUES ('ITEM2',20);
INSERT INTO WAREHOUSE1 (ITEM,QUANTITY) VALUES ('ITEM3',30);
COMMIT;
CREATE TABLE WAREHOUSE2 (COL_ID NUMBER GENERATED AS IDENTITY,
                         ITEM VARCHAR2(100),
                         QUANTITY NUMBER,
                         CONSTRAINT WAREHOUSE2 PRIMARY KEY (COL_ID));
INSERT INTO WAREHOUSE2 (ITEM,QUANTITY) VALUES ('ITEM2',20);
INSERT INTO WAREHOUSE2 (ITEM,QUANTITY) VALUES ('ITEM3',30);
INSERT INTO WAREHOUSE2 (ITEM,QUANTITY) VALUES ('ITEM4',40);
COMMIT;
2) Напишем запрос первым способом, в котором получим только наличие ITEM.
    (SELECT ITEM, 'Y' QUANTITY_WAREHOUSE1, 'N' QUANTITY_WAREHOUSE2 FROM WAREHOUSE1
    MINUS
    SELECT ITEM, 'Y' QUANTITY_WAREHOUSE1, 'N' QUANTITY_WAREHOUSE2 FROM WAREHOUSE2)
UNION
    (SELECT ITEM, 'Y' QUANTITY_WAREHOUSE1, 'Y' QUANTITY_WAREHOUSE2 FROM WAREHOUSE1
    INTERSECT
    SELECT ITEM, 'Y' QUANTITY_WAREHOUSE1, 'Y' QUANTITY_WAREHOUSE2 FROM WAREHOUSE2)
UNION
    (SELECT ITEM, 'N' QUANTITY_WAREHOUSE1, 'Y' QUANTITY_WAREHOUSE2 FROM WAREHOUSE2
    MINUS
    SELECT ITEM, 'N' QUANTITY_WAREHOUSE1, 'Y' QUANTITY_WAREHOUSE2  FROM WAREHOUSE1)
3) Напишем запрос вторым способом, применив FULL JOIN, что более производительнее, т.к.
таблицы не обрабатываются несколько раз. Также, соединив таблицы, можно делать вычисления,
например просуммировать, вычесть разность.
SELECT NVL(w1.ITEM, w2.ITEM) ITEM,
       w1.QUANTITY QUANTITY_WAREHOUSE1,
       w2.QUANTITY QUANTITY_WAREHOUSE2
    FROM WAREHOUSE1 w1
        FULL JOIN WAREHOUSE2 w2
        ON w1.ITEM = w2.ITEM
            ORDER BY NVL(w1.ITEM, w2.ITEM)


ЗАДАЧА 19.
    Индексы и загрузка данных с помощью FULL JOIN.
Индексы служат для быстрого поиска в таблице по условию. Например, для быстрого поиска
в таблице по колонкам - товар и цвет товара, то целецообразно создать составной индекс
на эти две колонки. Индексы бывают уникальные (запрещают вставлять в проиндексированную
колонку дубликаты значений (кроме NULL) и неуникальные. Также индексы бывают различных
типов: NORMAL, BITMAP, FUNCTION_BASED и т.п.
    Создать таблицу TAB1 с колонками NUM, FAMILY, MY_DATE. 
Создать TAB2 с такими же колонками + колонка MARK. Колонку NUM определить,
как первичный ключ, а также создать на ней индекс. Написать процедуру по загрузке
данных из TAB1 в TAB2 по правилам:
1. если запись NUM есть в TAB1, но нет в TAB2, то вставляем ее в TAB2 со значением
для колонки MARK = Y.
2. если запись NUM есть в TAB2, но нет в TAB1, то просто вставляем в колонку MARK 
значение N.
3. если записи NUM совпадают в обоих таблицах, то значение записи из TAB2 заменить 
на TAB1 и в MARK поставить Y.
Процедура должна иметь три выходных параметра: количество Y из п.1, количество N из
п.2 и количество замен из п.3.
    Решение:
1) Создадим таблицы, индексы и наполним данными:
CREATE TABLE TAB1 (NUM NUMBER,
                   FAMILY VARCHAR2(100),
                   MY_DATE DATE,
                   CONSTRAINT TAB1 PRIMARY KEY (NUM));
CREATE UNIQUE INDEX MY_IND1 ON TAB1 (NUM);                 
INSERT INTO TAB1 (NUM, FAMILY, MY_DATE) 
            VALUES (1, 'Иванов', TO_DATE('01.01.2019','DD.MM.YYYY'));
INSERT INTO TAB1 (NUM, FAMILY, MY_DATE) 
            VALUES (2, 'Петров', TO_DATE('02.01.2019','DD.MM.YYYY'));
INSERT INTO TAB1 (NUM, FAMILY, MY_DATE) 
            VALUES (3, 'Сидоров', TO_DATE('03.01.2019','DD.MM.YYYY'));
COMMIT;
CREATE TABLE TAB2 (NUM NUMBER,
                   FAMILY VARCHAR2(100),
                   MY_DATE DATE,
                   MARK VARCHAR2(100),
                   CONSTRAINT TAB2 PRIMARY KEY (NUM));                          
CREATE UNIQUE INDEX MY_IND2 ON TAB2 (NUM);                 
INSERT INTO TAB2 (NUM, FAMILY, MY_DATE) 
            VALUES (2, 'Петров', TO_DATE('02.01.2019','DD.MM.YYYY'));
INSERT INTO TAB2 (NUM, FAMILY, MY_DATE) 
            VALUES (3, 'Сидоров222', TO_DATE('03.01.2019','DD.MM.YYYY'));
INSERT INTO TAB2 (NUM, FAMILY, MY_DATE) 
            VALUES (4, 'Тургенев', TO_DATE('04.01.2019','DD.MM.YYYY'));
COMMIT;
2) Напишем процедуру:
CREATE OR REPLACE PROCEDURE TAB1_TAB2 (INS OUT VARCHAR2,
                                       DEL OUT VARCHAR2,
                                       UPD OUT VARCHAR2IS
BEGIN
    INS := 0;
    UPD := 0;
    DEL := 0;
    FOR i IN (SELECT t1.NUM NUM_OLD,
                     t1.FAMILY,
                     t1.MY_DATE,
                     t2.NUM NUM_NEW
                  FROM TAB1 t1
                      FULL JOIN TAB2 t2
                      ON t1.NUM = t2.NUM) LOOP
        /* если запись NUM есть в TAB1, но нет в TAB2 */
        IF i.NUM_NEW IS NULL THEN
            INSERT INTO TAB2 (NUM, 
                              FAMILY, 
                              MY_DATE,
                              MARK) VALUES (i.NUM_OLD,
                                            i.FAMILY,
                                            i.MY_DATE,
                                            'Y');
            INS := INS + 1;
        /* если запись NUM есть в TAB2, но нет в TAB1 */
        ELSIF i.NUM_OLD IS NULL THEN
            UPDATE TAB2
            SET MARK = 'N'
                WHERE NUM = i.NUM_NEW;
            DEL := DEL + 1;
        /* если записи NUM совпадают в обоих таблицах */
        ELSIF i.NUM_OLD = i.NUM_NEW THEN
            UPDATE TAB2
            SET FAMILY = i.FAMILY,
                MY_DATE = i.MY_DATE,
                MARK = 'Y'
                WHERE NUM = i.NUM_OLD;
            UPD := UPD + 1;
        END IF;
    END LOOP;
    COMMIT;
END TAB1_TAB2;
3) Выполним процедуру и выведем результат в DBMS_OUTPUT
DECLARE 
    x INTEGER;
    y INTEGER;
    z INTEGER;
BEGIN
    TAB1_TAB2(x, y, z);
    DBMS_OUTPUT.PUT_LINE('INS '||x);
    DBMS_OUTPUT.PUT_LINE('DEL '||y);
    DBMS_OUTPUT.PUT_LINE('UPD '||z);
END;
4) Посмотрим, что произошло с таблицей TAB2 
    SELECT NUM,
           FAMILY,
           MY_DATE,
           'MARK'
        FROM TAB1
UNION ALL
    SELECT NUM,
           FAMILY,
           MY_DATE,
           MARK
        FROM TAB2


ЗАДАЧА 20.
    Применение LEFT JOIN.
    Создать таблицу WRH1 с колонками ITEM, PRICE. Создать таблицу WRH2 такой же структуры. 
    Создать таблицу PRICE_LIST такой же структуры. Вставить в WRH1 в колонку ITEM 2 
значения: 'Товар1', 'Товар2'. Вставить в WRH2 в колонку ITEM 2 значения: 'Товар3', 
'Товар4'. Вставить в PRICE_LIST в колонку ITEM значения 'Товар1', 'Товар2', 'Товар3',
'Товар4', а в колонку PRICE значения 10, 20,30, 40.
    Написать анонимный блок, вставляющий значения из колонки PRICE_LIST.PRICE в 
колонки WRH1.PRICE и WRH2.PRICE, используя ROWID для ускорения выполнения блока.
    Решение:
1) Создадим таблицы и заполним их данными:
CREATE TABLE WRH1 (ITEM VARCHAR2(100),
                   PRICE NUMBER);
CREATE TABLE WRH2 AS SELECT * FROM WRH1;
CREATE TABLE PRICE_LIST AS SELECT * FROM WRH1;
INSERT INTO WRH1 (ITEM) VALUES ('Товар1');
INSERT INTO WRH1 (ITEM) VALUES ('Товар2');
INSERT INTO WRH2 (ITEM) VALUES ('Товар3');
INSERT INTO WRH2 (ITEM) VALUES ('Товар4');
INSERT INTO PRICE_LIST (ITEM, PRICE) VALUES ('Товар1', 10);
INSERT INTO PRICE_LIST (ITEM, PRICE) VALUES ('Товар2', 20);
INSERT INTO PRICE_LIST (ITEM, PRICE) VALUES ('Товар3', 30);
INSERT INTO PRICE_LIST (ITEM, PRICE) VALUES ('Товар4', 40);
COMMIT;
2) Напишем анонимный блок кода:
BEGIN
    FOR i IN (SELECT p.ITEM,
                     p.PRICE,
                     w1.ROWID ROWID1,
                     w2.ROWID ROWID2
                  FROM PRICE_LIST p,
                       WRH1 w1,
                       WRH2 w2
                      WHERE p.ITEM = w1.ITEM(+) AND
                            p.ITEM = w2.ITEM(+)) LOOP
        /* заполним WRH1.PRICE */
        IF i.ROWID1 IS NOT NULL THEN
            UPDATE WRH1
                SET PRICE = i.PRICE
                    WHERE ROWID = i.ROWID1;
        END IF;
        /* заполним WRH2.PRICE */
        IF i.ROWID2 IS NOT NULL THEN
            UPDATE WRH2
                SET PRICE = i.PRICE
                    WHERE ROWID = i.ROWID2;
        END IF;
        COMMIT;
    END LOOP;
END;
3) Проверим результат
    SELECT ITEM,
           PRICE
        FROM WRH1
UNION ALL
    SELECT ITEM,
           PRICE
        FROM WRH2
UNION ALL
    SELECT ITEM,
           PRICE
        FROM PRICE_LIST


    Оператор MERGE. Позволяет загружать данные из таблицы-источника в таблицу-
приемник на условиях: если ключ в таблице-приемнике найден, то UPDATE-им поля в
таблице приемнике нужным способом, иначе INSERT-им в таблицу-приемник нужным
способом. MERGE называют 'UPDATE else INSERT' или 'UPSERT'.
ЗАДАЧА 21.
    Создать пустую таблицу ALL_WRH с такой же структурой, как в таблице WAREHOUSE1
из задачи 18. С помощью MERGE внести в неё данные из WAREHOUSE1, и потом из WAREHOUSE2.
    Решение:
1) Создадим таблицу 
CREATE TABLE ALL_WRH AS SELECT * 
                            FROM WAREHOUSE1
                                WHERE 0=1;
2) Выполним MERGE в таблицу ALL_WRH из WAREHOUSE1, будут только INSERT:
MERGE INTO ALL_WRH DEST
USING (SELECT COL_ID,
              ITEM,
              QUANTITY
           FROM WAREHOUSE1) SRC                     
ON (DEST.ITEM = SRC.ITEM)
WHEN MATCHED THEN
    UPDATE SET DEST.QUANTITY = SRC.QUANTITY
WHEN NOT MATCHED THEN
    INSERT (DEST.COL_ID,
            DEST.ITEM,
            DEST.QUANTITY) VALUES (SRC.COL_ID,
                                   SRC.ITEM,
                                   SRC.QUANTITY);
COMMIT;                                   
3) Проверим результат:
SELECT *
    FROM ALL_WRH
4) Сменим запись колонки WAREHOUSE2.QUANTITY равную 30 на 35.
UPDATE WAREHOUSE2
SET QUANTITY = 35
    WHERE ITEM LIKE 'ITEM3';
COMMIT;
5) Выполним MERGE в таблицу ALL_WRH из WAREHOUSE2:
MERGE INTO ALL_WRH DEST
USING (SELECT COL_ID,
              ITEM,
              QUANTITY
           FROM WAREHOUSE2) SRC
ON (DEST.ITEM = SRC.ITEM)
WHEN MATCHED THEN
    UPDATE SET DEST.QUANTITY = SRC.QUANTITY
WHEN NOT MATCHED THEN 
    INSERT (DEST.COL_ID,
            DEST.ITEM,
            DEST.QUANTITY) VALUES (SRC.COL_ID,
                                   SRC.ITEM,
                                   SRC.QUANTITY);
COMMIT;                                   
6) Проверим результат
    SELECT 'WAREHOUSE1' TAB_SOURCE,
           COL_ID,
           ITEM,
           QUANTITY
        FROM WAREHOUSE1
UNION ALL
    SELECT 'WAREHOUSE2' TAB_SOURCE,
           COL_ID,
           ITEM,
           QUANTITY
    FROM WAREHOUSE2        
UNION ALL    
    SELECT 'ALL_WRH' TAB_SOURCE,
           COL_ID,
           ITEM,
           QUANTITY
        FROM ALL_WRH

ОПЕРАЦИИ С ДАТАМИ. При работе с датой рекомендуется применять функцию TO_DATE(), 
приводить даты к одному формату.

Получить текущую дату на сервере:
SELECT SYSDATE 
    FROM DUAL
Для дат можно задавать формат:
SELECT SYSDATE,
       TO_DATE('31.01.2012 22:56:17','DD.MM.YYYY HH24:MI:SS'),
       TO_DATE('2019-01-31','YYYY-MM-DD')
    FROM DUAL
Даты можно вычитать, в результате получим количество дней между ними:
SELECT TO_DATE('01.02.2019','DD.MM.YYYY') - TO_DATE('01.01.2019','DD.MM.YYYY')
    FROM DUAL
SELECT TO_DATE('01.02.2019','DD.MM.YYYY') - TO_DATE('03.02.2019','DD.MM.YYYY')
    FROM DUAL    
К дате можно прибавить(отнять) число, т.е. прибавится(отнимется) количество дней.
SELECT TO_DATE('01.01.2019','DD.MM.YYYY') + 5
    FROM DUAL

Следующий запрос следует выполнить с различными настройками сессии и увидеть различия
(NLS-настройки, действующие по умолчанию, можно прописывать на уровне базы, 
в переменных среды windows и в реестре):
Способ 1:
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
    ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
    ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
Способ 2:
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
    ALTER SESSION SET NLS_TERRITORY = 'CIS';
    ALTER SESSION SET NLS_DATE_LANGUAGE = 'RUSSIAN';
      
SELECT SYSDATE + 1, /* текущая дата + 1 день */
       SYSDATE - 1.5, /* текущая дата - 1,5 дня */
       SYSDATE + 1 / 24 /60, /* текущая дата + 1 минута */
       MYDATE, /* колонка даты из какого-нибудь подзапроса */
       SYSDATE - MYDATE, /* разность SYSDATE и MYDATE в днях */
       TRUNC(SYSDATE), /* округление SYSDATE до целых дней, без часов, минут и секунд */
       TRUNC(SYSDATE, 'YYYY'), /* округляем дату до первого дня года */
       TRUNC(SYSDATE, 'MM'), /* округляем дату до первого дня месяца */
       TRUNC(SYSDATE, 'IW'), /* округляем дату до первого дня недели по ISO 8601 */
       TRUNC(SYSDATE, 'WW'), /* округляем дату до первого дня недели (неделя со вторника) */    
       TRUNC(SYSDATE, 'DD'), /* округление даты к 00 часам дня */
       TRUNC(SYSDATE, 'HH'), /* округление даты к 00 минутам часа */     
       TRUNC(SYSDATE, 'MI'), /* округление даты к 00 секундам часа */
       TO_CHAR(SYSDATE), /* конвертация в строку с форматом, зависящим от NLS_DATE_FORMAT */
       TO_CHAR(SYSDATE, 'YYYY'), /* извлечь год */
       TO_CHAR(SYSDATE, 'YY'), /* извлечь последние две цифры года */
       TO_CHAR(SYSDATE, 'MM'), /* извлечь месяц */
       TO_CHAR(SYSDATE, 'MON'), /* краткое название месяца (зависит от NLS_DATE_LANGUAGE) */
       TO_CHAR(SYSDATE, 'MONTH'), /* полное название месяца (зависит от NLS_DATE_LANGUAGE) */
       TO_CHAR(SYSDATE, 'IW'), /* извлечь номер недели по ISO 8601 */
       TO_CHAR(SYSDATE, 'WW'), /* извлечь номер недели (неделя начинается со вторника) */
       TO_CHAR(SYSDATE, 'DD'), /* извлечь номер дня */
       TO_CHAR(SYSDATE, 'D'), /* номер дня недели (зависит от NLS_TERRITORY) */     
       TO_CHAR(SYSDATE, 'DY'), /* краткое название дня недели (зависит от NLS_DATE_LANGUAGE) */
       TO_CHAR(SYSDATE, 'DAY'), /* полное название дня недели (зависит от NLS_DATE_LANGUAGE) */    
       TO_CHAR(SYSDATE, 'HH24'), /* извлечь часы */
       TO_CHAR(SYSDATE, 'MI'), /* извлечь минуты */
       TO_CHAR(SYSDATE, 'SS'), /* извлечь секунды */
       TO_CHAR(SYSDATE, 'YYYY/MM/DD'), /* конвертировать дату в строку с форматированием */
       TO_CHAR(SYSDATE
               'DD-MON-YYYY',
               'NLS_DATE_LANGUAGE=''AMERICAN'''), --конвертировать дату в строку с форматом и
                                                  --на английcком
       TO_CHAR(SYSDATE
               'DD-MON-YYYY',
               'NLS_DATE_LANGUAGE=''RUSSIAN'''), --конвертировать дату в строку с форматом и
                                                 --на русском
       TO_CHAR(TO_DATE('11-sep-99',
                       'DD-MON-RR',
                       'NLS_DATE_LANGUAGE=''AMERICAN'''),
               'YYYY'), --еще одно представление года в виде 
                        --RR, где 99 отобразится, как 1999
       TO_CHAR(TO_DATE('11-sep-17',
                       'DD-MON-RR',
                       'NLS_DATE_LANGUAGE=''AMERICAN'''),
               'YYYY'), --еще одно представление года в виде 
                        --RR, где 17 отобразится, как 2017
       EXTRACT(YEAR FROM SYSDATE), /* извлечь год из даты */
       EXTRACT(MONTH FROM SYSDATE), /* извлечь месяц из даты */
       EXTRACT(DAY FROM SYSDATE), /* извлечь день из даты */ 
       ADD_MONTHS(SYSDATE, 1), /* добавление к дате 1 месяца (по ISO 8601) */
       ADD_MONTHS(SYSDATE, -1.9), --отнятие от даты 1 месяца, нецелое число округляется
                                  --до ближайшего меньшего (по ISO 8601) */
       MONTHS_BETWEEN(TO_DATE('31.01.2019','DD.MM.YYYY'),
                      TO_DATE('31.01.2020','DD.MM.YYYY')), --количество месяцев между
                                                           --месяцами (по ISO 8601)
       MONTHS_BETWEEN(TO_DATE('31.07.2019','DD.MM.YYYY'),
                      TO_DATE('31.01.2019','DD.MM.YYYY')), --количество месяцев между
                                                           --месяцами (по ISO 8601)      
       MONTHS_BETWEEN(TO_DATE('31.01.2019','DD.MM.YYYY'),
                      TO_DATE('01.01.2019','DD.MM.YYYY')) --количество месяцев между
                                                          --месяцами (по ISO 8601)
    FROM (SELECT (TO_DATE('01.01.2019','DD.MM.YYYY')) MYDATE FROM DUAL)

СЛОВАРЬ ДАННЫХ.
Объекты Oracle находятся в словаре данных в виде различных представлений (VIEW).
VIEW по объектам своей схемы (по объектам юзера, под которым сейчас залогинен) начинаются
с "USER_". По объектам всех схем начинаются с префикса "ALL_", а в остальном пишутся
также. По объектам всех схем (с админскими правами) начинаются с префикса "DBA_", а
в остальном пишутся также. В нижеприведенных запросах можно поменять "USER_" на 
"ALL_", чтобы делалась выборка по всем схемам. При замене "USER_" на "ALL_", чтобы
получить выборку по конкретной схеме, можно указывать конкретную схему, например:
WHERE OWNER = 'MY_SCHEMA'. Все объекты в словаре данных хранятся написанными
большими буквами, если только при их создании не использовались двойные кавычки, что 
делается нечасто.
SELECT * FROM USER_TABLES /* Все таблицы */
SELECT * FROM USER_TAB_COLUMNS 
    WHERE TABLE_NAME = 'MY_TABLE' --Столбцы конкретной таблицы
                                                             --(например MY_TABLE) или 
                                                             --VIEW, с порядком следования 
                                                             --столбцов (COLUMN_ID), 
                                                             --типами данных. */
SELECT * FROM USER_SOURCE --Код функций, процедур, пакетов, триггеров (хранятся 
                          --построчно с указанием номеров строк)
SELECT * FROM USER_VIEWS  --Код обычных VIEW, тела VIEW хранятся в поле TEXT типа LONG
SELECT * FROM USER_MVIEWS --Код MVIEW
SELECT * FROM USER_OBJECTS --Все объекты
SELECT * FROM USER_SEGMENTS --Занимаемое таблицами и инлексами место на диске
SELECT * FROM USER_TABLESPACES --Инфо о TABLESPACE (физические пространства для 
                               --таблиц и индексов)
SELECT * FROM USER_INDEXES --Индексы
SELECT * FROM USER_IND_COLUMNS --Столбцы индексов
SELECT * FROM USER_SYNONYMS --Синонимы (доп. имена для объектов), при изменени названия
                            --таблицы синоним все равно будет ссылаться на таблицу.
SELECT * FROM USER_DB_LINKS --линки на базы данных (DATABASE LINKS)
SELECT * FROM USER_CONSTRAINTS --Ограничения (CONSTRAINTS)                           
SELECT * FROM USER_TAB_PARTITIONS --Партиции (PARTITIONS)
SELECT * FROM USER_TAB_SUBPARTITIONS --Субпартиции

О партициях.
Таблица может состоять из нескольких вложенных таблиц одинаковой структуры - партиций.
Партиции также могут содержать в себе субпартиции, то есть таблицы второго уровня 
вложенности. Третьего уровня вложенности нет. Разделение таблицы на партиции
осуществляется согласно значениям определенной колонки таблицы (по которому производится
партицирование). Отнесение каждой строки к конкретной партиции может осуществляться по
интервалам (BY RANGE) или по перечню значений (BY LIST), которые может принимать колонка
(по которой производится партицирование).

SELECT * FROM USER_DEPENDENCIES 
    WHERE NAME = 'MY_PCK' --какие объекты используются в объекте(например, пакете MY_PCK)
SELECT * FROM USER_DEPENDENCIES 
    WHERE REFERENCED_NAME = 'MY_TAB' --какие объекты используют объект (таблицу) 'MY_TAB'

    Зависимости объектов можно смотреть в PL/SQL DEVELOPER-е, раскрыв в дереве объектов узел
с именем интересующего объекта, а затем раскрыв узлы "References" и "Referenced by".
    
ЗАДАЧА 22.
    Получить список объектов из USER_OBJECTS, модифицированных за последние полгода.
SELECT * 
    FROM USER_OBJECTS
        WHERE MONTHS_BETWEEN(SYSDATE, LAST_DDL_TIME) < 6
            ORDER BY LAST_DDL_TIME DESC

ЗАДАЧА 23.
    Отчет об объектах текущей схемы.
    С помощью цикла для всех объектов из USER_OBJECTS вывести в DBMS_OUTPUT отчет со
следующими колонками: "ТИП_ОБЪЕКТА", "НАЗВАНИЕ_ОБЪЕКТА", "ДАТА_СОЗДАНИЯ". Сортировку 
сделать по возрастанию по дате создания.
    Решение:
BEGIN
    DBMS_OUTPUT.ENABLE;
    FOR i IN (SELECT OBJECT_TYPE,
                     OBJECT_NAME,
                     CREATED
                  FROM USER_OBJECTS
                      ORDER BY CREATED) LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(i.OBJECT_TYPE,31) ||
                             RPAD(i.OBJECT_NAME,31) ||
                             TO_CHAR(i.CREATED, 'DD.MM.YYYY HH24:MI:SS'));
    END LOOP;                
END;

ЗАДАЧА 24.
    Применение явного курсора.
    Создать три таблицы и одну вью, начинающиеся с 'MY_'. И в DBMS_OUTPUT вывести 
SELECT к колонкам этих таблиц и вью (т.е. начинающиеся с 'MY_'). Например:
SELECT ... FROM ... и т.д.
    Решение:
1) Создадим таблицы и вью:
CREATE TABLE MY_TAB1 (COl11 NUMBER, COL12 VARCHAR2(100), COL13 DATE);
CREATE TABLE MY_TAB2 (COL21 NUMBER, COL22 VARCHAR2(100));
CREATE TABLE MY_TAB3 (COL31 NUMBER);
CREATE OR REPLACE VIEW MY_VIEW1 AS
    SELECT *
        FROM MY_TAB1;
2) Напишем анонимный блок:
DECLARE
    CURSOR MY_CUR IS
        SELECT TABLE_NAME,
               COLUMN_NAME
            FROM USER_TAB_COLUMNS 
                WHERE TABLE_NAME LIKE 'MY/_%' ESCAPE '/'
                    ORDER BY TABLE_NAME,
                             COLUMN_ID;
    row_My_cur MY_CUR%ROWTYPE;
    chr_Prev_table VARCHAR2(30);
    chr_Columns VARCHAR2(3000);   
BEGIN
    OPEN MY_CUR;
    LOOP
        FETCH MY_CUR
            INTO row_My_cur;
        /* Если произошла смена таблицы или список таблиц закончился, то выводим SELECT */
        IF chr_Prev_table <> row_My_cur.TABLE_NAME OR
           MY_CUR%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('SELECT ' || SUBSTR(chr_Columns,2)
                                 ||' FROM ' || chr_Prev_table);
            chr_Columns := '';
        END IF;
        /* Формируем строку со всеми столбцами одной таблицы */
        chr_Columns := chr_Columns || ',' ||row_My_cur.COLUMN_NAME;
        /* Записываем имя последенй таблицы */
        chr_Prev_table := row_My_cur.TABLE_NAME;
        EXIT WHEN MY_CUR%NOTFOUND;
    END LOOP;
    CLOSE MY_CUR;
END;
    
    Компиляция схемы с целью устранения инвалидных объектов.
    Если функции, процедуры или пакеты содержат ошибки или находятся в неоткомпилированном
состоянии в результате изменений в объектах от которых они зависят, то они видны в 
USER_OBJECTS со статусом = 'INVALID'.
    Компилировать их можно следующими командами:
ALTER FUNCTION MY_FNC COMPILE;
ALTER PROCEDURE MY_PRC COMPILE;
ALTER PACKAGE MY_PCK COMPILE;
ALTER PACKAGE MY_PCK COMPILE BODY;
    При изменении объектов следует не забывать о компиляции зависящих от них объектов.
ЗАДАЧА 25.
    1. Написать блок с командой компиляции всей схемы.
    2. Написать скрипт компиляции инвалидных объектов, используя USER_OBJECTS.
    Решение:
1)    Всю схему можно откомпилировать вызовом:
BEGIN
    DBMS_UTILITY.COMPILE_SCHEMA('MY_SCHEMA');
END;
2) Напишем запрос, решающий задачу
    SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' COMPILE;' SCRIPT_OF_COMPILE
        FROM USER_OBJECTS
            WHERE STATUS = 'INVALID' AND 
                  OBJECT_TYPE <> 'PACKAGE BODY'
UNION ALL
    SELECT 'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;' SCRIPT_OF_COMPILE
        FROM USER_OBJECTS
            WHERE STATUS = 'INVALID' AND
                  OBJECT_TYPE = 'PACKAGE BODY'


    Особенности поиска текста во вью.
    В функциях, процедурах, пакетах и триггерах можно найти интересующий текст с помощью 
вью словаря данных USER_SOURCE. SELECT * FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%MY_TEXT%'
    Но тексты вью хранятся в отдельной вью USER_VIEWS в колонке TEXT типа LONG. Причем поиск 
текста до 32000 символов в запросе с помощью LIKE выдаст ошибку, из-за особенности типа LONG:
SELECT * FROM USER_VIEWS WHERE TEXT LIKE '%MY_TEXT%'
Поэтому такой поиск выполняют внутри PL/SQL блока с помощью LIKE.
Поиск текста с количеством символов 32000 выполняют просматривая результат запроса.
ЗАДАЧА 26.
    Найти имена всех вью, содержащие в своем тексте выражение 'ID', и вывести их 
через DBMS_OUTPUT.
    Решение:
1) Найдем VIEW с длиной текста < 32000 символов.
BEGIN
    FOR i IN (SELECT VIEW_NAME,
                     TEXT
                 FROM USER_VIEWS 
                     WHERE TEXT_LENGTH < 32000) LOOP
        IF UPPER(i.TEXT) LIKE '%ID%' THEN
            DBMS_OUTPUT.PUT_LINE(i.VIEW_NAME);
        END IF;
    END LOOP;
END;
2) Для VIEW с текстом >= 32000 посмотрим результат запроса вручную:
SELECT VIEW_NAME,
       TEXT 
    FROM USER_VIEWS
        WHERE TEXT_LENGTH >= 32000
    
    
    JOBS. Джобы.
    Процедуры можно регулярно запускать по расписанию. Для этого существует пакет DBMS_JOB.
С помощью вызова DBMS_JOB.SUBMIT создадим джоб, запускающий процедуру MY_PROCEDURE(). 
При указании параметров в джобе удобнее пользоваться именованной нотацией. 
1) Создадим таблицу, в которую с помощью джоба будем добавлять данные.
CREATE TABLE TAB_JOB (COL1 NUMBER);
2) Создадим процедуру, вставляющую строку в таблицу TAB_JOB со значением, которое на 1
больше, чем уже имеющееся самое большое значение в таблице.
CREATE OR REPLACE PROCEDURE MY_PROCEDURE (x NUMBER, y NUMBERIS
    z NUMBER;
BEGIN
    /* Выберем макс. значение */
    SELECT MAX(t.COL1) INTO z
        FROM TAB_JOB t;
    --Вставим условие, которое позволит избежать NULL. Ведь если первый раз 
    --вставляется строка, то там не будет макс. знач-я, а будет NULL
    IF z > 0 THEN
        z:= z + 1;
    ELSE 
        z := 1;
    END IF;
    INSERT INTO TAB_JOB VALUES(z);
    COMMIT;
END MY_PROCEDURE;
3) И напишем анонимный блок кода с джобом для этой процедуры:
DECLARE
    my_job  NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(JOB => my_job,
                    WHAT => 'DECLARE
                                 x NUMBER;
                                 y NUMBER;
                             BEGIN
                                MY_PROCEDURE(x, y);     
                             END;',
                    NEXT_DATE => SYSDATE + 1 / 24 / 60 / 2,
                    INTERVAL => 'TRUNC(SYSDATE,''MI'') + 1 / 24 / 60');
    /* Выведем в DBMS_OUTPUT номер джоба, посмотрите его после первого выполнения джоба */
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_job));
    COMMIT;
END;
    Параметр JOB - это автосоздаваемый уникальный идентификатор джоба.
    Параметр WHAT определяет какую процедуру выполним. В разделе DECLARE указали параметры
процедуры просто для показа того, что можно передавать параметры и они поучаствуют в джобе.
    Параметр NEXT_DATE имеет тип DATE, это показатель в какой момент времени джоб запустится 
в первый раз, например, DBMS_JOB.SUBMIT(NEXT_DATE => SYSDATE + 1/24/60/2) означает, что
джоб запустится через полминуты после выполнения анонимного блока.
    Параметр INTERVAL имеет строковый тип данных, это показатель, с какой частотой будет
выполняться джоб. В данном блоке конструкция 'TRUNC(SYSDATE,''MI'') + 1 / 24 / 60' 
означает, что каждый раз после завершения джоба он снова запустится в следующий ближайший 
момент с целым количеством минут и нулем секунд. Если процедура MY_PROCEDURE() будет
выполняться быстрее минуты, то джоб будет запускаться раз в минуту. 
    Отметим, что на сервере может быть установлено ограничение на кол-во разрешенных 
джобов, таким образом джоб может не стартовать, несмотря на запуск.
Выполним некоторые действия:
1) Найти информацию по созданному джобу можно так: 
SELECT *
    FROM USER_JOBS
        WHERE JOB = 41 --наш номер джобы из DBMS_OUTPUT
2) Теперь поменяем джоб с помощью команды DBMS_JOB.CHANGE на выполнение пустого блока 
кода один раз в час:
BEGIN
    DBMS_JOB.CHANGE(JOB => 41,
                    WHAT => 'BEGIN
                                 NULL;
                             END;',
                    NEXT_DATE => SYSDATE,
                    INTERVAL => 'TRUNC(SYSDATE,''HH24'')+1/24');
    COMMIT;
END;
3) Отключим джоб с помощью DBMS_JOB.BROKEN, оставив его в памяти:
BEGIN
    DBMS_JOB.BROKEN(JOB => 41,
                    BROKEN => TRUE);
    COMMIT;
END;    
4) Включим джоб обратно:
BEGIN
    DBMS_JOB.BROKEN(JOB => 41,
                    BROKEN => FALSE);
    COMMIT;
END;    
5) Удалим джоб навсегда
BEGIN
    DBMS_JOB.REMOVE(JOB => 41);
    COMMIT;
END;
    Отметим, что DBMS_JOB.CHANGE, DBMS_JOB.BROKEN, DBMS_JOB.REMOVE следует выполнять, когда
джоб не занимается выполнением поставленной на расписание процедуры, а бездействует, ожидая
следующего запуска.


    Динамический SQL.
    Для объектов с фиксированной структурой используется простой SQL. Если же требуется
сформировать текст команды динамически, т.е. с использование изменяющихся или появившихся
данных, то мы используем динамический SQL с помощью команды EXECUTE IMMEDIATE.
    Кроме того, DDL команды в PL/SQL-блоке выполняются только через EXECUTE IMMEDIATE.
ЗАДАЧА 27.
    Выполнить следующие операции с помощью динамического SQL.
    Все имена предварительно объявить в виде переменных и использовать их в блоке.
    Создать таблицу TAB_DSQL с колонками с типами "COL1" NUMBER, "COL2" VARCHAR2(100). 
Затем добавить в таблицу колонку "COL3" DATE. Далее создадим VIEW с именем VIEW_TAB_DSQL.
Затем добавить в таблицу в колонки COL1, COL2, COL3 значения 100, 'сто' и SYSDATE.
    Решение:
1) Создадим анонимный блок с этим операциями:
DECLARE
    tbl_name VARCHAR2(100) := 'TAB_DSQL';
    col1_name VARCHAR2(100) := 'COL1';
    col2_name VARCHAR2(100) := 'COL2';
    col3_name VARCHAR2(100) := 'COL3';
    view_name VARCHAR2(100);
BEGIN
    /* Создадим таблицу */
    EXECUTE IMMEDIATE 'CREATE TABLE '||tbl_name||'('||col1_name||' NUMBER,'
                                                    ||col2_name||' VARCHAR2(100))';
    /* Добавим колонку */
    EXECUTE IMMEDIATE 'ALTER TABLE '||tbl_name||' ADD '||col3_name||' DATE';
    /* Создадим вью */
    view_name := 'VIEW_TAB_DSQL';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW '||view_name||' AS 
    SELECT * FROM '||tbl_name||'';
    /* Добавим значения в таблицу */
    EXECUTE IMMEDIATE 'INSERT INTO '||tbl_name
                                    ||' ('||col1_name||','
                                          ||col2_name||','
                                          ||col3_name||') 
                                          VALUES (100,
                                                  ''сто'',
                                                  TO_DATE('''||SYSDATE||''',''DD.MM.YYYY''))';
    COMMIT;
END;    
2) Проверим таблицу и вью
SELECT * FROM TAB_DSQL
SELECT * FROM VIEW_TAB_DSQL

ЗАДАЧА 28.
    Создать таблицу.
    Удалить конкретную таблицу, если она существует, двумя способами:
    1. Применив явный курсор.
    2. Применив неявный курсор.
    Решение:
1) Во вью USER_TABLES можно увидеть таблицы залогиненого пользователя.
Создадим таблицу.
CREATE TABLE TBL_FIND(COL1 NUMBER(4,2));
2) Напишем анонимный блок (с неявным курсором), проверяющий существует ли таблица 
и удаляющий ее.
DECLARE
    i NUMBER := 0;
BEGIN
    SELECT COUNT(1) INTO i 
        FROM USER_TABLES
            WHERE TABLE_NAME LIKE 'TBL_FIND';
    IF i = 1 THEN
        EXECUTE IMMEDIATE 'DROP TABLE TBL_FIND';
    END IF;
END;
3) Напишем анонимный блок (с явным курсором), проверяющий существует ли таблица
и удаляющий её.
DECLARE
    CURSOR MY_CUR IS
        SELECT TABLE_NAME
            FROM USER_TABLES
                WHERE TABLE_NAME LIKE 'TBL_FIND';
    row_cur MY_CUR%ROWTYPE;
BEGIN
    OPEN MY_CUR;
        FETCH MY_CUR INTO row_cur;
        IF MY_CUR%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('TBL_FIND найдена');
            EXECUTE IMMEDIATE ('DROP TABLE TBL_FIND');
            DBMS_OUTPUT.PUT_LINE('Таблица удалена');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Таблица не найдена');
        END IF;
    CLOSE MY_CUR;
END;

    Применение конструкции %ROWCOUNT в курсоре и конструкции SQL%ROWCOUNT.
ЗАДАЧА 29.
    Создать таблицу с текстовой колонкой. Написать анонимный блок со следующими операциями:
1) С помощью динамического SQL выполнить операцию TRUNCATE и в DBMS_OUTPUT отразить
текст "Table truncated".
2) Открыть явный курсор объектов текущей схемы с типом таблица. Перебрать курсор и
отразить в DBMS_OUTPUT количество строк в курсоре. 
3) В созданную таблицу вставить имена всех объектов текущей схемы. С помощью SQL%ROWCOUNT 
отразить в DBMS_OUTPUT количество вставленных строк (конструкцию SQL%ROWCOUNT 
можно использовать не только поле INSERT, но и после UPDATEDELETEMERGE).
    Решение:
1) Создадим таблицу 
CREATE TABLE TAB_ROW (COL1 VARCHAR2(500));
2) Напишем анонимный блок
DECLARE
    /* Объявим курсор для пункта 2 */
    CURSOR my_cur IS SELECT *
                         FROM USER_OBJECTS
                             WHERE OBJECT_TYPE = 'TABLE';
    my_row_cur my_cur%ROWTYPE;    
BEGIN
    /* 1) Делаем TRUNCATE таблицы */
    EXECUTE IMMEDIATE ('TRUNCATE TABLE TAB_ROW');
    DBMS_OUTPUT.PUT_LINE('TAB_ROW TRUNCATED');
    -- 2) Откроем курсор, переберем с помощью FETCH строки. Полностью переберем курсор
    -- с помощью FETCH. Только после полного FETCH всех строк можно вывести полное 
    -- количество строк в курсоре с помощью %ROWCOUNT.
    OPEN my_cur;
        FETCH my_cur INTO my_row_cur; 
            WHILE my_cur%FOUND LOOP
                FETCH my_cur INTO my_row_cur;
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('Найдено именно таблиц '||TO_CHAR(my_cur%ROWCOUNT));
    CLOSE my_cur;
    /* 3) Вставим в созданную таблицу TAB_ROW названия всех объектов текущей схемы */
    INSERT INTO TAB_ROW (COL1) SELECT OBJECT_NAME
                                   FROM USER_OBJECTS;
    DBMS_OUTPUT.PUT_LINE('В таблицу вставлено '|| TO_CHAR(SQL%ROWCOUNT) ||
                         ' имен объектов'); 
                         --значение SQL%ROWCOUNT забудется после COMMIT
    COMMIT;
END;
3) Проверим результат в таблице 
SELECT *
    FROM TAB_ROW


ЗАДАЧА 30. Удаление всех таблиц и вью.
    Создать в текущей схеме две таблицы с именами, начинающимися на "TT", и две вьюхи 
с именами, начинающимися на "VV".
    Написать блок удаления таблиц, начинающихся на "TT".
    Написать блок, в котором вывести скрипт удаления вьюх, начинающихся на "VV".
    Решение:
1) Создадим две таблицы и две вьюхи.
CREATE TABLE TT_TAB1 (COL1 NUMBER);
CREATE TABLE TT_TAB2 (COL1 NUMBER);
CREATE OR REPLACE VIEW VV_VIEW1 AS 
    SELECT COL1 FROM TT_TAB1;
CREATE OR REPLACE VIEW VV_VIEW2 AS
    SELECT COL1 FROM TT_TAB2;
2) Напишем блок удаления таблиц
BEGIN
    FOR i IN (SELECT 'DROP TABLE '||TABLE_NAME AS DROP_STATEMENT
                  FROM USER_TABLES
                      WHERE TABLE_NAME LIKE 'TT%') LOOP
        EXECUTE IMMEDIATE i.DROP_STATEMENT;
    END LOOP;
END;
3) Напишем блок, выдающий текст скрипта удаления вью.
BEGIN 
    FOR i IN (SELECT VIEW_NAME
                  FROM USER_VIEWS 
                      WHERE VIEW_NAME LIKE 'VV%') LOOP
        DBMS_OUTPUT.PUT_LINE('DROP VIEW '||TO_CHAR(i.VIEW_NAME)||';');
    END LOOP;
END;


ЗАДАЧА 31. 
    Создать таблицу, начинающуюся с "MY_", с самогенерируемым полем ID и колонкой COL1. 
Написать блок, в котором наполнить таблицу числами с -1 по -5 умноженными на 2.
Создать на основе этой таблицы вью, начинающуюся с "MY_", в которой значения колонок
умножить на 2, колонки назвать, как в таблице. Создать итоговую таблицу с колонками 
TABLE_NAME, MAX_ID. Написать блок, где собрать информацию о максимальных ID в таблице и 
вью, наименования которых начинаются с "MY_" и поместить в итоговую таблицу.
    Решение:
1) Создадим таблицу и вью
CREATE TABLE MY_TAB1(ID NUMBER GENERATED AS IDENTITY
                     COL1 NUMBER);
CREATE OR REPLACE VIEW MY_VIEW1 AS
    SELECT ID * 2 AS ID,
           COL1 * 2 AS COL1
        FROM MY_TAB1;
CREATE TABLE RESULT_TAB(TABLE_NAME VARCHAR2(30),
                        MAX_ID NUMBER);                     
2) Наполним таблицы данными.
BEGIN
    FOR i IN REVERSE -5..-1 LOOP
        INSERT INTO MY_TAB1(COL1) VALUES(i*2);
    END LOOP;
    COMMIT;
END;
3) Наполним итоговую таблицу
BEGIN
    --очистим итоговую таблицу
    EXECUTE IMMEDIATE('TRUNCATE TABLE RESULT_TAB');
    --напишем курсор получающий имя таблицы и вью
    FOR i IN 
    (
    SELECT TABLE_NAME
        FROM USER_TAB_COLUMNS
            WHERE TABLE_NAME LIKE '%MY/_%' ESCAPE '/' AND
                  COLUMN_NAME = 'ID' AND
                  DATA_TYPE = 'NUMBER'
    ) LOOP
    --переберем каждую строку, получим макс. ID и заполним итоговую таблицу
    EXECUTE IMMEDIATE('INSERT INTO RESULT_TAB(TABLE_NAME, MAX_ID) 
                           SELECT '''||i.TABLE_NAME||''', 
                                     MAX(ID)
                                  FROM '||i.TABLE_NAME||'');
    END LOOP;
    COMMIT;
END;


ЗАДАЧА 32. Переименование колонок.
    Создать две таблицы и одну вью названия которых начинаются с "MY_" с колонками 'ID'.
    Переименовать у таблиц начинающихся с "MY_" колонки ID в ID2.
    Решение:
1) Создадим две таблицы и одну вью, начинающиеся с "MY_" и с колонками ID.
CREATE TABLE MY_TAB1(ID NUMBER);
CREATE TABLE MY_TAB2(ID NUMBER);
CREATE OR REPLACE VIEW MY_VIEW1 AS
SELECT * FROM MY_TAB1;
2) Напишем блок, выполняющий переименование.
BEGIN   
    FOR i IN 
            (
            --получим объекты с типом "таблица", начинающиеся с MY_ и имеющие
            --колонку ID
            SELECT utc.TABLE_NAME
                FROM USER_OBJECTS uo
                    LEFT JOIN USER_TAB_COLUMNS utc
                    ON uo.OBJECT_NAME = utc.TABLE_NAME
                        WHERE uo.OBJECT_TYPE = 'TABLEAND
                              utc.TABLE_NAME LIKE 'MY/_%' ESCAPE '/' AND
                              utc.COLUMN_NAME = 'ID'
            MINUS
            --уберем таблицы, содержащие колонку ID2, чтобы при переименовывании из
            --ID в ID2 не было ошибки.
            SELECT TABLE_NAME 
                FROM USER_TAB_COLUMNS
                    WHERE COLUMN_NAME = 'ID2'
            ) LOOP
    EXECUTE IMMEDIATE ('ALTER TABLE '||i.TABLE_NAME||' RENAME COLUMN ID TO ID2');
    END LOOP;
END;
3) Проверим результат
SELECT uo.OBJECT_TYPE,
       utc.TABLE_NAME,
       utc.COLUMN_NAME
    FROM USER_OBJECTS uo
        LEFT JOIN USER_TAB_COLUMNS utc
        ON uo.OBJECT_NAME = utc.TABLE_NAME 
            WHERE utc.TABLE_NAME LIKE 'MY_%' AND
                  utc.COLUMN_NAME IN ('ID', 'ID2')


ЗАДАЧА 33. Создание вью-справочников.
    Создать три таблицы c одной колонкой "MY_COL": 
    1 таблица с типом колонки CHAR
    2 таблица с типом колонки VARCHAR2
    3 таблица с типом колонки NUMBER.   
    С помощью динамического SQL создать вью из таблиц, содержащих колонки с типом
VARCHAR2 или CHAR. Вью должны запрашивать уникальные значения из колонки "MY_COL".
    Решение:
1) Создадим таблицы и наполним их данными
CREATE TABLE TAB1(MY_COL CHAR(100));
CREATE TABLE TAB2(MY_COL VARCHAR2(100));
CREATE TABLE TAB3(MY_COL NUMBER);
INSERT INTO TAB1(MY_COL) VALUES('one');
INSERT INTO TAB1(MY_COL) VALUES('one');
INSERT INTO TAB1(MY_COL) VALUES('two');
INSERT INTO TAB2(MY_COL) VALUES('five');
INSERT INTO TAB2(MY_COL) VALUES('six');
INSERT INTO TAB2(MY_COL) VALUES('six');
INSERT INTO TAB3(MY_COL) VALUES(7);
INSERT INTO TAB3(MY_COL) VALUES(7);
INSERT INTO TAB3(MY_COL) VALUES(8);
COMMIT;
2) Напишем анонимный блок
DECLARE 
    my_text VARCHAR2(4000);
BEGIN
    FOR i IN (SELECT utc.TABLE_NAME
                  FROM USER_TAB_COLUMNS utc
                      --присоединяем, чтобы отобрать объекты с типом 'TABLE'
                      LEFT JOIN USER_OBJECTS uo
                      ON utc.TABLE_NAME = uo.OBJECT_NAME
                          WHERE utc.COLUMN_NAME = 'MY_COL' AND
                                utc.DATA_TYPE IN ('VARCHAR2', 'CHAR') AND
                                uo.OBJECT_TYPE = 'TABLE') LOOP
        my_text := 'CREATE OR REPLACE VIEW VW_'||i.TABLE_NAME||
                   ' AS SELECT DISTINCT MY_COL FROM '||i.TABLE_NAME;
        --создадим вью
        EXECUTE IMMEDIATE(my_text);
        --выведем скрипт создания в DBMS_OUTPUT
        DBMS_OUTPUT.PUT_LINE(my_text);
    END LOOP;
END;
 

    Триггеры.
    Триггеры обычно создаются для таблиц. В триггерах пишут действия, которые нужно
совершить перед изменением или после изменения данных в таблице. Исходя из этого
выделяют триггеры BEFORE и AFTER. Триггеры можно привязать к действиям INSERTUPDATE,
DELETE. В триггерах можно проверять, какое именно из действий совершается:
IF INSERTING THEN ..., 
IF UPDATING THEN ...,
IF DELETING THEN ...
    Также можно обращаться к предыдущим и новым значениям полей, например:
my_old_id := :old.ID, 
:new.ID := my_new_id    

ЗАДАЧА 34. Создать автозаполняемое поле.
    Создать таблицу с двумя колонками, одну из них назвать ID с числовым типом. 
    Создать сиквенс.
    Создать триггер, в котором перед вставкой строк в таблицу, если не указан ID, 
будет вставляться значение ID из сиквенса. То есть поле будет автозаполняемым.
    Решение:
1) Создадим таблицу и сиквенс.
CREATE TABLE MY_TAB(ID NUMBER, COL1 VARCHAR2(100));
CREATE SEQUENCE MY_SEQ;
2) Создадим триггер BEFORE INSERT
CREATE OR REPLACE TRIGGER MY_TRG_BI 
    BEFORE INSERT ON MY_TAB
        FOR EACH ROW
BEGIN
    IF :NEW.ID IS NULL THEN
        SELECT MY_SEQ.NEXTVAL 
        INTO :NEW.ID 
            FROM DUAL;
    END IF;
END;
3) Вставим строки в MY_TAB
INSERT INTO MY_TAB(COL1) VALUES ('one');
INSERT INTO MY_TAB(COL1) VALUES ('two');
INSERT INTO MY_TAB(COL1) VALUES ('five');
COMMIT;
4) Проверим результат
SELECT *
    FROM MY_TAB

Добавить комментарий


Защитный код
Обновить

Форма авторизации