Создание [осуществленного] представления от универсальных данных в Oracle/Mysql
У меня есть универсальная модель данных с 3 таблицами
CREATE TABLE Properties
(
propertyId int(11) NOT NULL AUTO_INCREMENT,
name varchar(80) NOT NULL
)
CREATE TABLE Customers
(
customerId int(11) NOT NULL AUTO_INCREMENT,
customerName varchar(80) NOT NULL
)
CREATE TABLE PropertyValues
(
propertyId int(11) NOT NULL,
customerId int(11) NOT NULL,
value varchar(80) NOT NULL
)
INSERT INTO Properties VALUES (1, 'Age');
INSERT INTO Properties VALUES (2, 'Weight');
INSERT INTO Customers VALUES (1, 'Bob');
INSERT INTO Customers VALUES (2, 'Tom');
INSERT INTO PropertyValues VALUES (1, 1, '34');
INSERT INTO PropertyValues VALUES (2, 1, '80KG');
INSERT INTO PropertyValues VALUES (1, 2, '24');
INSERT INTO PropertyValues VALUES (2, 2, '53KG');
То, что я хотел бы сделать, создают представление, которое имеет как столбцы все СТРОКИ в Свойствах и имеет, как располагает записи в ряд в Клиентах. Значения столбцов заполняются от PropertyValues., например.
customerId Age Weight
1 34 80KG
2 24 53KG
Я думаю, что мне нужна хранимая процедура, чтобы сделать это, и возможно осуществленное представление (записи в таблице "Properties" редко изменяются). Какие-либо подсказки?
1 ответ
Сгенерировать представление с динамическим SQL достаточно просто:
create or replace procedure gen_view
as
cols_stmt varchar2(32767);
from_stmt varchar2(32767);
subq_name varchar2(30);
begin
for r in ( select * from properties
order by propertyid )
loop
subq_name := 'pv_'||trim(to_char(r.propertyid));
cols_stmt := cols_stmt || ', '|| subq_name ||'.value as '||r.name;
from_stmt := from_stmt || ' left join ( select value, customerid from propertyvalues where propertyid = '
||trim(to_char(r.propertyid))||') '||subq_name
||' on '||subq_name||'.customerid = customers.customerid';
end loop;
execute immediate 'create or replace view eav_view as select customers.customerid, customers.customername'
|| cols_stmt
|| ' from customers '
|| from_stmt;
end gen_view;
/
Вот это работает:
SQL> exec gen_view
PL/SQL procedure successfully completed.
SQL> select * from eav_view
2 /
CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
1
Bob
34
80KG
2
Tom
24
53KG
SQL>
Давайте создадим новое свойство и вставим для него значения для некоторых клиентов ...
SQL> insert into properties values (3, 'FavouriteIceCream')
2 /
1 row created.
SQL> insert into propertyvalues values (3, 1, 'Cherry Garcia')
2 /
1 row created.
SQL> exec gen_view
PL/SQL procedure successfully completed.
SQL> select * from eav_view
2 /
CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
FAVOURITEICECREAM
--------------------------------------------------------------------------------
1
Bob
34
80KG
Cherry Garcia
2
Tom
24
53KG
SQL>
"Я думаю, мне нужно процедура для этого и, возможно, материализованное представление (записи в таблица «Свойства» меняются редко). »
Проблема в том, что Свойства изменятся, и я предполагаю, что вы не будете контролировать, когда это произойдет. Так что вы найдете это очень сложно применить изменения к материализованному представлению. Это имеет значение, потому что изменение проекции материализованного представления требует его отбрасывания. Поэтому довольно сложно сделать это без перерыва в обслуживании. Аналогичное соображение применимо и к обычному представлению, но с отключением почти ноль.
Если вы действительно хотите преобразовать оператор представления в материализованное представление, обратите внимание, что Oracle, похоже, не нравится синтаксис ANSI-92, когда дело доходит до материализованных представлений (он бросает ORA-12054). Я не уверен, почему это должно быть, но проблема исчезла, когда я перешел на старую технику соединения, которая раздражает, потому что синтаксис внешнего соединения более неуклюжий.
Решением без необходимости повторного создания объектов базы данных было бы использование динамического SQL в функции, которая возвращает Ref Cursor, который сопоставляется с JDBC ResultSet:
create or replace function get_eav_view
return sys_refcursor
as
cols_stmt varchar2(32767);
from_stmt varchar2(32767);
subq_name varchar2(30);
return_value sys_refcursor;
begin
for r in ( select * from properties
order by propertyid )
loop
subq_name := 'pv_'||trim(to_char(r.propertyid));
cols_stmt := cols_stmt || ','|| subq_name ||'.value as '||r.name;
from_stmt := from_stmt || ' left join ( select value, customerid from propertyvalues where propertyid = '
||trim(to_char(r.propertyid))||') '||subq_name
||' on '||subq_name||'.customerid = customers.customerid';
end loop;
open return_value for
'select customers.customerid, customers.customername'
|| cols_stmt
|| ' from customers '
|| from_stmt;
return return_value;
end get_eav_view;
/
Это всегда будет возвращать последний прогноз:
SQL> var rc refcursor
SQL> exec :rc := get_eav_view
PL/SQL procedure successfully completed.
SQL> print rc
CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
FAVOURITEICECREAM
--------------------------------------------------------------------------------
1
Bob
34
80KG
Cherry Garcia
2
Tom
24
53KG
SQL>
Теперь, если мы добавим новое свойство, оно будет немедленно использовано:
SQL> insert into properties values (4, 'StarSign')
2 /
1 row created.
SQL> insert into propertyvalues values (4, 2, 'Aries')
2 /
1 row created.
SQL> exec :rc := get_eav_view
PL/SQL procedure successfully completed.
SQL> print rc
CUSTOMERID
----------
CUSTOMERNAME
--------------------------------------------------------------------------------
AGE
--------------------------------------------------------------------------------
WEIGHT
--------------------------------------------------------------------------------
FAVOURITEICECREAM
--------------------------------------------------------------------------------
STARSIGN
--------------------------------------------------------------------------------
1
Bob
34
80KG
Cherry Garcia
2
Tom
24
53KG
Aries
SQL>