Бази данни: Една реалистична задача

Тук е споделен PL/SQL скрипт, който може да е полезен (в съответните вариации разбира се) за задача като описаната тук.
Да предположим, че разполагаме с няколко-стотин таблици на Oracle DB Server (с версия примерно > 8.0i). На някои (приятел, колега, клиент, ...) е нужно да разполага със списък на стойностите от някои от колоните на част от таблиците в базата. Иначе казано предоставил ни е списък със следния формат:

име_таблица1, име_колона1
име_таблица2, име_колона1
...
име_таблицаN, име_колона1
име_таблицаN, име_колона2
...

Нашата скромна задачка е да разширим този списък с трета колонка, така че формата му да се промени на:

име_таблица1, име_колона1, стойност1
име_таблица1, име_колона1, стойност2
име_таблица1, име_колона1, стойност3
...
име_таблица2, име_колона1, стойност1
...
име_таблицаN, име_колона1, стойност1
име_таблицаN, име_колона1, стойност2
...
име_таблицаN, име_колона2
...

Единственото ограничение е всеки ред от списъка да е уникален, т.е. за всяка двойка таблица-колона да се вземат само неповтарящите се стойности от базата.

Остава преди да пристъпим към самото изпълнение на скрипта, да премислим как ще отработим входа и изхода му. Първо ще споменем за изхода, тъй като решението там е тривиално.
Изход: Може би най-удобния начин за представяне на таблични данни е ... таблицата. Затова още в началото на скрипта (преди още да се декларират променливите) се отделя една секция за следната DDL дефиниция:

DROP TABLE output PURGE;

CREATE TABLE output (
table_name VARCHAR2(100) NOT NULL,
column_name VARCHAR2(100) NOT NULL,
initial_value VARCHAR2(4000) NOT NULL,
CONSTRAINT unique_row UNIQUE (table_name, column_name, initial_value)
);

Разбира се ограничението за уникалност не е задължително да присъства, още повече, че самия скрипт я гарантира.
От друга страна това не може да е крайния изход, който да е необходим на човека дал ни задачата. За щастие работим с Oracle SQL Developer, при който в контекстното меню на всяка таблица ни е предоставена възможността да изведем данните в редица текстови формати. Изборът на крайния формат е според личните предпочитания и нужди и не изисква особено разяснение.
Вход: Нека няма предявено изискване за оптимизирано решение на задачата, но въпреки това като стъпка към него да използваме структурата на Oracle асоцииран масив. Особеността, която ще приложим е свързана с размерността на входните данни - ще ги превърнем от двумерни в едномерни. Това означава, че ще разположим последователно двойките "таблица-колона" в масива по такъв начин, че самото индексиране да държи сметка за семантиката на данните, т.е. на нечетните индекси ще се намират таблиците, а на четните - колоните. Смисълът на това изкуствено намаляване на размерността на данните е, че се съкръщава използването на един цикъл. Приемаме, че някоя добра фея се погрижила за трансформирането на входните данни (които мога да наброяват хиляди), до инициализирания асоциативен масив показан по-долу. Би могла да се обсъди и възможността за използване на друг скрипт или разширяване на настоящия да върши и тази дейност, но това няма да стане точно тук.

DECLARE
TYPE Extraction IS TABLE OF VARCHAR2(100) INDEX BY binary_integer;
population Extraction;
it NUMBER; -- The iterator
nextit NUMBER; -- The iterator to be incremented with 1
intit NUMBER; -- iterator for the internal loop
countdist NUMBER := 0; -- Number of distinct records of current table
counter NUMBER := 0; -- Number of all the records of current table

TYPE DistinctiveCursor IS REF CURSOR;
dcursor DistinctiveCursor;
val VARCHAR2(4000);

BEGIN

-- Populating the massive associative array:
population(1) := 'table1';
population(2) := 'column1';
population(3) := 'table2';
population(4) := 'column1';
population(5) := 'table2';
population(6) := 'column2';
population(7) := 'table2';
population(8) := 'column3';
population(9) := 'table3';
population(10) := 'column1';

FOR it IN 1..10
LOOP
nextit := it + 1;
IF population.EXISTS(it) AND (MOD(it, 2) != 0) THEN -- iterates only the table names (odd members of the array)
--dbms_output.put_line('Current value for ' || it || ': ' || population(it) || '.');
IF population.EXISTS(nextit) THEN
--dbms_output.put_line('The next value for ' || nextit || ': ' || population(nextit) || '.');
EXECUTE IMMEDIATE ('SELECT COUNT(' || population(nextit) || ') FROM ' || population(it)) INTO counter ;
EXECUTE IMMEDIATE ('SELECT COUNT(DISTINCT(' || population(nextit) || ')) FROM ' || population(it)) INTO countdist;

OPEN dcursor FOR 'SELECT DISTINCT(' || population(nextit) || ') FROM ' || population(it);
LOOP
FETCH dcursor INTO val;
EXIT WHEN dcursor%NOTFOUND;
--dbms_output.put_line('Stoinost: ' || val);

-- Here goes the actual work of the script:
EXECUTE IMMEDIATE 'INSERT INTO output (table_name, column_name, initial_value) VALUES (:v1, :v2, :v3)'
USING population(it), population(nextit), COALESCE(val, '--- NO VALUE WAS FOUND IN THIS OBJECT! ---');

END LOOP;
CLOSE dcursor;
COMMIT;

END IF;

--dbms_output.put_line('### In this table there are total ' || counter || ' rows and ' || countdist || ' of them are unique.');

END IF;

END LOOP;

END;

Тънките моменти в скрипта са коментирани. В коментар е сложен и изхода към конзолата, който може да се използва за дебъг при случай на нужда.
Разбира се написването на този скрипт нямаше да стане без съответния уеб трафик генериран с помощта на Google. От всичко открито (има стотици наръчници за най-продваната база данни) най-голяма помощ ми оказа този блог.