Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 как сделать при создании таблицы...  [new]
timergaliev
Member

Откуда:
Сообщений: 247
как сделать при создании таблицы, поле, которое будет является результатом суммы других полей
например
create table "group"
(
id_group integer primary key,
people_count integer,   /*вот это поле должно быть результатом суммы полей из другой таблицы*/
...)

3 апр 10, 11:44    [8574713]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
AmKad
Member

Откуда:
Сообщений: 5222
timergaliev,

Вешать триггер на другую табличку.
3 апр 10, 11:49    [8574717]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
-2-
Member

Откуда:
Сообщений: 15330
timergaliev,

create [materialized] view
3 апр 10, 11:55    [8574721]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
comphead
Member

Откуда: Киев
Сообщений: 3390
AmKad
timergaliev,

Вешать триггер на другую табличку.


и чем это поможет?
3 апр 10, 11:58    [8574723]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18371
Зависит от версии.
В 11g можно сделать вычисляемое поле,
в 10g можно сделать view+instead of trigger
в предыдущих - триггер, view, mat.view

В любой версии можно поручить заполнение такого поля самому приложению, модифицирующему таблицу.
3 апр 10, 11:59    [8574724]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
comphead
Member

Откуда: Киев
Сообщений: 3390
timergaliev
как сделать при создании таблицы, поле, которое будет является результатом суммы других полей
например
create table "group"
(
id_group integer primary key,
people_count integer,   /*вот это поле должно быть результатом суммы полей из другой таблицы*/
...)



триггер, матвьюха. а просто вьюшка с подзапросом не поможет?
3 апр 10, 12:00    [8574725]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
comphead
Member

Откуда: Киев
Сообщений: 3390
andrey_anonymous
Зависит от версии.
В 11g можно сделать вычисляемое поле,
в 10g можно сделать view+instead of trigger
в предыдущих - триггер, view, mat.view

В любой версии можно поручить заполнение такого поля самому приложению, модифицирующему таблицу.


вы имеете ввиду нововведение в 11, что можно делать свои виртульные колонки в таблице?
3 апр 10, 12:01    [8574727]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
-2-
Member

Откуда:
Сообщений: 15330
andrey_anonymous
В 11g можно сделать вычисляемое поле
Это как?:
 people_count as (select sum(....) from другая_таблица where ...)
Два нюанса. Откуда оно узнает про изменение другая_таблица, ну выражение должно быть deterministic.
3 апр 10, 12:17    [8574744]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
AmKad
Member

Откуда:
Сообщений: 5222
comphead
AmKad
timergaliev,

Вешать триггер на другую табличку.


и чем это поможет?

create table grp(id_group number primary key, people_count number);
create table peolpe(id number primary key, id_group number);
alter table  peolpe add foreign key(id_group) references grp(id_group);

create or replace trigger trg
before delete or insert
on peolpe 
referencing new as new old as old
for each row
declare

begin
   case when inserting then 
            update grp set people_count = nvl(people_count, 0) + 1;
        when deleting  then 
            update grp set people_count = nvl(people_count, 0) - 1;
        else null;    
   end case;
   
   exception
     when others then       
       raise;
end trg;

insert into grp(id_group, people_count) values(1,0);

insert into peolpe(id, id_group) values (1,1);
insert into peolpe(id, id_group) values (2,1);
insert into peolpe(id, id_group) values (3,1);

select * from grp
id_group peolpe_count
13
3 апр 10, 12:18    [8574746]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
-2-
Member

Откуда:
Сообщений: 15330
AmKad,

insert into peolpe(id, id_group) values (300,100);
3 апр 10, 12:21    [8574751]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
AmKad
Member

Откуда:
Сообщений: 5222
AmKad
comphead
AmKad
timergaliev,

Вешать триггер на другую табличку.


и чем это поможет?

create table grp(id_group number primary key, people_count number);
create table peolpe(id number primary key, id_group number);
alter table  peolpe add foreign key(id_group) references grp(id_group);

create or replace trigger trg
before delete or insert
on peolpe 
referencing new as new old as old
for each row
declare

begin
   case when inserting then 
            update grp set people_count = nvl(people_count, 0) + 1 where id_group = :new.id_group;
        when deleting  then 
            update grp set people_count = nvl(people_count, 0) - 1 where id_group = :old.id_group;
        else null;    
   end case;
   
   exception
     when others then       
       raise;
end trg;

insert into grp(id_group, people_count) values(1,0);

insert into peolpe(id, id_group) values (1,1);
insert into peolpe(id, id_group) values (2,1);
insert into peolpe(id, id_group) values (3,1);

select * from grp
id_group peolpe_count
13
3 апр 10, 12:25    [8574754]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
AmKad
Member

Откуда:
Сообщений: 5222
-2-
AmKad,

insert into peolpe(id, id_group) values (300,100);

ORA-02291: нарушено ограничение целостности - исходный ключ не найден
3 апр 10, 12:30    [8574759]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
comphead
Member

Откуда: Киев
Сообщений: 3390
AmKad
-2-
AmKad,

insert into peolpe(id, id_group) values (300,100);

ORA-02291: нарушено ограничение целостности - исходный ключ не найден


триггер это стремно, он может быть развален, отключен, дропнут в конце концов.

мне кажется что лучше сделать вьюху с
sum() over (partition by)
3 апр 10, 12:34    [8574761]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54390
Было бы интересно послушать ТС на тему: ЗАЧЕМ?
3 апр 10, 13:13    [8574795]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
Хохлов
Member

Откуда:
Сообщений: 1169
timergaliev,
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
 
SQL> 
SQL> drop table peolpe purge;
 
Table dropped
SQL> drop table grp purge;
 
Table dropped
SQL> create table peolpe(id number primary key, id_group number);
 
Table created
SQL> create or replace function GetPeople_Count(pId_Group number) return number result_cache relies_on(peolpe) deterministic is
  2    lCounter number;
  3  begin
  4   select count(*) into lCounter from peolpe where id_group=pId_Group;
  5   return lCounter;
  6  end;
  7  /
 
Function created
SQL> create table grp( id_group number primary key
  2                  , people_count number generated always as (GetPeople_Count(id_group)) not null
  3                  );
 
Table created
SQL> alter table  peolpe add foreign key(id_group) references grp(id_group);
 
Table altered
SQL> insert into grp(id_group) values (1);
 
1 row inserted
SQL> select * from grp;
 
  ID_GROUP PEOPLE_COUNT
---------- ------------
         1            0
SQL> insert into peolpe(id,id_group) values (1,1);
 
1 row inserted
SQL> select * from grp;
 
  ID_GROUP PEOPLE_COUNT
---------- ------------
         1            1
SQL> insert into grp(id_group,people_count) values (2,2);
 
insert into grp(id_group,people_count) values (2,2)
 
ORA-54013: INSERT operation disallowed on virtual columns
 
SQL> 
?
3 апр 10, 16:58    [8575046]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
Хохлов

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 


That is OK as long as you understand it is not transactional. Your solution will display people count in a group not at the time of

select * from grp;

But at the time of:

select count(*) into lCounter from peolpe where id_group=pId_Group;

So if some other session commits its changes to table people after your session selects from GRP but before select in virtual column calculation function you will get inconsistent results:

Session 1.
SQL> drop table people purge;

Table dropped.

SQL> drop table grp purge;

Table dropped.

SQL> create table people(
  2                      id number primary key,
  3                      id_group number
  4                     );

Table created.

SQL> create or replace
  2    function GetPeople_Count(pId_Group number)
  3      return number
  4      result_cache relies_on(people)
  5      deterministic
  6      is
  7          lCounter number;
  8      begin
  9          dbms_lock.sleep(10);
 10          select  count(*)
 11            into  lCounter
 12            from  people
 13            where id_group=pId_Group;
 14         return lCounter;
 15  end;
 16  /

Function created.

SQL> create table grp(
  2                   id_group number primary key,
  3                   people_count number generated always as (GetPeople_Count(id_group)) not null
  4                  );

Table created.

SQL> alter table people
  2    add foreign key(id_group)
  3      references grp(id_group);

Table altered.

SQL> insert
  2    into grp(id_group)
  3    values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select  *
  2    from  grp;

Now Session 2 issues the following rigth after Session 1 last select:

SQL> insert
  2    into people(id,id_group)
  3    select rownum + 1,1 from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> 

And last select is Session 1 produces:


  ID_GROUP PEOPLE_COUNT
---------- ------------
         1           14

SQL> 

SY.
3 апр 10, 17:44    [8575110]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
Хохлов
Member

Откуда:
Сообщений: 1169
SY, спасибо за коммент.
session 1
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 

SQL> 
SQL> drop table peolpe purge;
 
Table dropped
SQL> drop table grp purge;
 
Table dropped
SQL> create table peolpe(id number primary key, id_group number);
 
Table created
SQL> create or replace function GetPeople_Count(pId_Group number) return number result_cache relies_on(peolpe) deterministic is
  2    lCounter number;
  3  begin
  4   dbms_lock.sleep(10);
  5   select count(*) into lCounter from peolpe where id_group=pId_Group;
  6   return lCounter;
  7  end;
  8  /
 
Function created
SQL> create or replace operator GetPeople_Count_Oper binding (Number) return Number using GetPeople_Count;
 
Operator created
SQL> create table grp( id_group number primary key
  2                  , people_count number generated always as (GetPeople_Count_Oper(id_group)) not null
  3                  );
 
Table created
SQL> alter table  peolpe add foreign key(id_group) references grp(id_group);
 
Table altered
SQL> insert into grp(id_group) values (1);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> select  * from grp;
 
  ID_GROUP PEOPLE_COUNT
---------- ------------
         1            0
 
SQL> 
session 2
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 

SQL> 
SQL> insert into peolpe(id,id_group)  select rownum + 1,1 from SCOTT.emp;
 
14 rows inserted
SQL> commit;
 
Commit complete
SQL> select * from grp;
 
  ID_GROUP PEOPLE_COUNT
---------- ------------
         1           14
 
SQL>  
3 апр 10, 18:33    [8575164]     Ответить | Цитировать Сообщить модератору
 Re: как сделать при создании таблицы...  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
andrey_anonymous
В 11g можно сделать вычисляемое поле,
Пока лучше их избегать - есть весьма неприятные баги.
15 апр 10, 15:44    [8635005]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: как сделать при создании таблицы...  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
Хохлов,

Огромное СПАСИБА за решение, очень помогло! Инфо: в таблице присутствовало поле с текстовым значением, надо было сделать 2 справочника на основе данных этого поля. Что бы не переделывать отчёты и прочее в клиенте сделал по вышеизложенной схеме 2 детерминированных функции + операторы к ним и вычисляемое поле, всё работает!
27 сен 12, 10:22    [13230002]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить