Devart Forum

Код: Выделить всё

create table t01(id number primary key, type_id number);
create table t02(id number primary key, type_id number);

insert into t01(id, type_id)
select
    level,
    0
from
    dual
connect by
    level <= 200000;     

insert into t02(id, type_id)
select
    level,
    0
from
    dual
connect by
    level <= 2000;     
    
create or replace view vw_test(id1, id2)
as
select
    t1.id as id1,
    t2.id as id2
from
    t01 t1
    left join t02 t2 on t2.type_id = t1.type_id
group by
    t1.id,
    t2.id;             

Код: Выделить всё

create table plan_test1 (
  id number primary key,
  link_id number);

create table plan_test2 (
  id number primary key,
  name varchar2(100 char));

Код: Выделить всё

with my_cte as (
    select
        id, name
    from
        plan_test2
)
select
    t1.id, t2.name
from
    plan_test1 t1
    join my_cte t2 on t2.id = t1.link_id

Код: Выделить всё

CREATE TABLE TT2 (
  F01 NUMBER NOT NULL,
  F02 NUMBER NOT NULL);

Код: Выделить всё

CREATE OR REPLACE TRIGGER TT2_BIU
BEFORE INSERT OR UPDATE ON TT2 FOR EACH ROW
begin
    :new.f02 := coalesce(:new.f02, 0);
end;

Код: Выделить всё

create or replace trigger xxx.compound_trigger_sample for delete on xxx.t0
compound trigger

    type trec_deleted_record is record(
        id number,
        data number);
    type ttab_deleted_records is table of trec_deleted_record
        index by binary_integer;
    deleted_records ttab_deleted_records;

after each row is
    v_rec trec_deleted_record;
begin
    v_rec.id := :old.id;
    v_rec.data := :old.data;
    deleted_records(deleted_records.count + 1) := v_rec;
end after each row;

after statement is
    v_rec trec_deleted_record;
begin
    for i in 1 .. deleted_records.count loop
        v_rec := deleted_records(i);
        -- тут делаем обработку, связанную c mutating table
        xxx.process(v_rec.id, v_rec.data);
    end loop;
    deleted_records.delete;
end after statement;

end;

Код: Выделить всё

create or replace package xxx.aaa as

procedure do_something;

end aaa;

create or replace package body xxx.aaa as

/* commented for debug purpose
procedure do_something
is
begin
    null;
end;
*/

procedure do_something
is
begin
    null;
end;

end aaa;

Код: Выделить всё

create or replace package body xxx.aaa as

/* 123 */

procedure do_something is
begin
/* 456 */
    null;
end do_something;

end aaa;