TÓM TẮT
#apex: true, auditcols: true
projects /insert 5
name /nn
owner
milestones /history /insert 10
name /nn
status /check open completed closed /values open, open, open, open, closed, completed
owner
started date
closed date
links /insert 5
name /nn
url
attachments
contributed by
attachment file
action items /insert 12
action
desc clob
owner
status /check open completed closed
view project_ms projects milestones
view project_ai projects action_items
CHI TIẾT:
-- create tables
create table projects (
name varchar2(255) not null,
owner varchar2(4000),
created date not null,
created_by varchar2(255) not null,
updated date not null,
updated_by varchar2(255) not null
)
;
create table milestones (
project_id number
constraint milestones_project_id_fk
references projects on delete cascade,
name varchar2(255) not null,
status varchar2(60) constraint milestones_status_cc
check (status in ('OPEN','COMPLETED','CLOSED')),
owner varchar2(4000),
started date,
closed date,
created date not null,
created_by varchar2(255) not null,
updated date not null,
updated_by varchar2(255) not null
)
;
create table links (
project_id number
constraint links_project_id_fk
references projects on delete cascade,
name varchar2(255) not null,
url varchar2(4000),
created date not null,
created_by varchar2(255) not null,
updated date not null,
updated_by varchar2(255) not null
)
;
create table attachments (
project_id number
constraint attachments_project_id_fk
references projects on delete cascade,
contributed_by varchar2(4000),
attachment blob,
attachment_filename varchar2(512),
attachment_mimetype varchar2(512),
attachment_charset varchar2(512),
attachment_lastupd date,
created date not null,
created_by varchar2(255) not null,
updated date not null,
updated_by varchar2(255) not null
)
;
create table action_items (
project_id number
constraint action_items_project_id_fk
references projects on delete cascade,
action varchar2(4000),
the_desc clob,
owner varchar2(4000),
status varchar2(60) constraint action_items_status_cc
check (status in ('OPEN','COMPLETED','CLOSED')),
created date not null,
created_by varchar2(255) not null,
updated date not null,
updated_by varchar2(255) not null
)
;
-- triggers
create or replace trigger projects_biu
before insert or update
on projects
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end projects_biu;
/
create or replace trigger action_items_biu
before insert or update
on action_items
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end action_items_biu;
/
create or replace trigger attachments_biu
before insert or update
on attachments
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end attachments_biu;
/
create or replace trigger links_biu
before insert or update
on links
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end links_biu;
/
create or replace trigger milestones_biu
before insert or update
on milestones
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end milestones_biu;
/
-- indexes
create index action_items_i1 on action_items (project_id);
create index attachments_i1 on attachments (project_id);
create index links_i1 on links (project_id);
create index milestones_i1 on milestones (project_id);
-- history tracking
create sequence history_seq;
create table history (
id number primary key,
table_name varchar2(128),
column_name varchar2(128),
action varchar2(1) check (action in ('I','U','D')),
action_date date,
action_by varchar2(255),
data_type varchar2(255),
pk1 number,
tab_row_version integer,
old_vc varchar2(4000),
new_vc varchar2(4000),
old_number number,
new_number number,
old_date date,
new_date date,
old_ts timestamp,
new_ts timestamp,
old_tswtz timestamp with time zone,
new_tswtz timestamp with time zone,
old_tswltz timestamp with local time zone,
new_tswltz timestamp with local time zone,
old_clob clob,
new_clob clob,
old_blob blob,
new_blob blob
)
/
create index history_idx1 on history (pk1);
create index history_idx2 on history (table_name, column_name);
create or replace view history_v as
select id,
table_name,
column_name,
decode(action,'U','Update','D','Delete') action,
action_date,
action_by,
pk1 table_primary_key,
tab_row_version table_row_version,
decode(data_type,
'NUMBER',old_number||' > '||new_number,
'VARCHAR2',substr(old_vc,1,50)||' > '||substr(new_vc,1,50),
'DATE',to_char(old_date,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_date,'YYYYMMDD HH24:MI:SS'),
'TIMESTAMP',to_char(old_ts,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_ts,'YYYYMMDD HH24:MI:SS'),
'TIMESTAMP WITH TIMEZONE',to_char(old_tswtz,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_tswtz,'YYYYMMDD HH24:MI:SS'),
'TIMESTAMP WITH LOCAL TIMEZONE',to_char(old_tswltz,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_tswltz,'YYYYMMDD HH24:MI:SS'),
'BLOB','length '||sys.dbms_lob.getlength(old_blob)||' > '||' length '||sys.dbms_lob.getlength(new_blob),
'CLOB',sys.dbms_lob.substr(old_vc,50,1)||' > '||sys.dbms_lob.substr(new_vc,50,1)
) change
from history
/
create or replace trigger milestones_aud
after update or delete on milestones
for each row
declare
t varchar2(128) := 'MILESTONES';
u varchar2(128) := nvl(sys_context('APEX$SESSION','APP_USER'),user);
begin
if updating then
if (:old.project_id is null and :new.project_id is not null) or
(:old.project_id is not null and :new.project_id is null) or
:old.project_id != :new.project_id then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'PROJECT_ID', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.project_id, :new.project_id);
end if;
if (:old.name is null and :new.name is not null) or
(:old.name is not null and :new.name is null) or
:old.name != :new.name then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'NAME', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.name, :new.name);
end if;
if (:old.status is null and :new.status is not null) or
(:old.status is not null and :new.status is null) or
:old.status != :new.status then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'STATUS', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.status, :new.status);
end if;
if (:old.owner is null and :new.owner is not null) or
(:old.owner is not null and :new.owner is null) or
:old.owner != :new.owner then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'OWNER', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.owner, :new.owner);
end if;
if (:old.started is null and :new.started is not null) or
(:old.started is not null and :new.started is null) or
:old.started != :new.started then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
) values (
history_seq.nextval, t, 'STARTED', :old.id, null, 'U', sysdate, u, 'DATE', :old.started, :new.started);
end if;
if (:old.closed is null and :new.closed is not null) or
(:old.closed is not null and :new.closed is null) or
:old.closed != :new.closed then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
) values (
history_seq.nextval, t, 'CLOSED', :old.id, null, 'U', sysdate, u, 'DATE', :old.closed, :new.closed);
end if;
elsif deleting then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'PROJECT_ID', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.project_id, :new.project_id);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'NAME', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.name, :new.name);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'STATUS', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.status, :new.status);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'OWNER', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.owner, :new.owner);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
) values (
history_seq.nextval, t, 'STARTED', :old.id, null, 'D', sysdate, u, 'DATE', :old.started, :new.started);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
) values (
history_seq.nextval, t, 'CLOSED', :old.id, null, 'D', sysdate, u, 'DATE', :old.closed, :new.closed);
end if;
end milestones_aud;
/
-- create views
create or replace view project_ms as
select
projects.name project_name,
projects.owner project_owner,
projects.created project_created,
projects.created_by project_created_by,
projects.updated project_updated,
projects.updated_by project_updated_by,
milestones.name milestone_name,
milestones.status status,
milestones.owner milestone_owner,
milestones.started started,
milestones.closed closed,
milestones.created milestone_created,
milestones.created_by milestone_created_by,
milestones.updated milestone_updated,
milestones.updated_by milestone_updated_by
from
projects,
milestones
where
milestones.project_id(+) = projects.id
/
create or replace view project_ai as
select
projects.name name,
projects.owner project_owner,
projects.created project_created,
projects.created_by project_created_by,
projects.updated project_updated,
projects.updated_by project_updated_by,
action_items.action action,
action_items.the_desc the_desc,
action_items.owner action_item_owner,
action_items.status status,
action_items.created action_item_created,
action_items.created_by action_item_created_by,
action_items.updated action_item_updated,
action_items.updated_by action_item_updated_by
from
projects,
action_items
where
action_items.project_id(+) = projects.id
/
-- load data
insert into projects (
name,
owner
) values (
'Mac Prevention',
'Gricelda Luebbers'
);
insert into projects (
name,
owner
) values (
'Personal Information Security Review',
'Dean Bollich'
);
insert into projects (
name,
owner
) values (
'Employee Automation',
'Milo Manoni'
);
insert into projects (
name,
owner
) values (
'Deadlock Detection Review',
'Laurice Karl'
);
insert into projects (
name,
owner
) values (
'Hyper Ledger Project',
'August Rupel'
);
commit;
-- load data
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Mac Prevention',
'OPEN',
'Gricelda Luebbers',
sysdate - 7,
sysdate - 21
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Personal Information Security Review',
'OPEN',
'Dean Bollich',
sysdate - 48,
sysdate - 61
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Employee Automation',
'OPEN',
'Milo Manoni',
sysdate - 11,
sysdate - 63
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Deadlock Detection Review',
'OPEN',
'Laurice Karl',
sysdate - 47,
sysdate - 67
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Hyper Ledger Project',
'CLOSED',
'August Rupel',
sysdate - 1,
sysdate - 26
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Documentation Review',
'COMPLETED',
'Salome Guisti',
sysdate - 43,
sysdate - 25
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Predictive Analysis Super Cluster',
'OPEN',
'Lovie Ritacco',
sysdate - 54,
sysdate - 19
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Harvard Study Review',
'OPEN',
'Chaya Greczkowski',
sysdate - 66,
sysdate - 31
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Wireless Analysis',
'OPEN',
'Twila Coolbeth',
sysdate - 33,
sysdate - 24
);
insert into milestones (
project_id,
name,
status,
owner,
started,
closed
) values (
1,
'Database R19 Upgrade',
'OPEN',
'Carlotta Achenbach',
sysdate - 63,
sysdate - 44
);
commit;
-- load data
insert into links (
project_id,
name,
url
) values (
1,
'Mac Prevention',
'aaaf.pendueles.gov'
);
insert into links (
project_id,
name,
url
) values (
1,
'Personal Information Security Review',
'aaag.huntingdon.gov'
);
insert into links (
project_id,
name,
url
) values (
1,
'Employee Automation',
'aaab.bruni.com'
);
insert into links (
project_id,
name,
url
) values (
1,
'Deadlock Detection Review',
'aaab.cantaloube.com'
);
insert into links (
project_id,
name,
url
) values (
1,
'Hyper Ledger Project',
'aaae.cunningham.net'
);
commit;
-- load data
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Porttitor ligula. Nam.',
'x',
'Gricelda Luebbers',
'OPEN'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Lectus. Nulla placerat iaculis aliquam. Vestibulum lacinia arcu in massa pharetra, id mattis risus rhoncus.Cras vulputate.',
'x',
'Dean Bollich',
'COMPLETED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Et malesuada fames ac ante ipsum primis in faucibus. Ut.',
'x',
'Milo Manoni',
'CLOSED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Malesuada fames ac ante ipsum primis in faucibus. Ut id.',
'x',
'Laurice Karl',
'OPEN'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'In massa pharetra, id mattis risus rhoncus.Cras vulputate porttitor ligula. Nam semper diam suscipit elementum sodales. Proin sit amet massa eu lorem commodo ullamcorper.Interdum et malesuada fames ac ante ipsum primis in faucibus. Ut id nulla ac sapien suscipit tristique.',
'x',
'August Rupel',
'COMPLETED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsumprimis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinialectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leoeget malesuada.',
'x',
'Salome Guisti',
'CLOSED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Nam semper diam suscipit elementum sodales. Proin sit amet massa eu lorem commodo ullamcorper.Interdum et malesuada fames ac ante ipsum.',
'x',
'Lovie Ritacco',
'OPEN'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsumprimis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinialectus, quis consectetur mi venenatis nec. Donec convallis.',
'x',
'Chaya Greczkowski',
'COMPLETED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Pharetra, id mattis risus rhoncus.Cras.',
'x',
'Twila Coolbeth',
'CLOSED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinialectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leoeget malesuada.',
'x',
'Carlotta Achenbach',
'OPEN'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Proin sit amet massa eu lorem commodo ullamcorper.Interdum et malesuada fames ac ante ipsum primis in faucibus. Ut id nulla ac sapien suscipit tristique ac volutpat risus.Phasellus vitae ligula commodo, dictum.',
'x',
'Jeraldine Audet',
'COMPLETED'
);
insert into action_items (
project_id,
action,
the_desc,
owner,
status
) values (
1,
'Rhoncus.Cras vulputate porttitor ligula. Nam semper.',
'x',
'August Arouri',
'CLOSED'
);
commit;
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
hoặc
https://bit.ly/oaz_fp
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
học oracle database
Các tìm kiếm liên quan đến học oracle
database
Tự học Oracle
Tài liệu Oracle 12c tiếng Việt
Hướng dẫn sử dụng Oracle Database
Oracle SQL cơ bản
Oracle SQL là gì
Khóa học Oracle Hà Nội
Học chứng chỉ Oracle ở đầu
Khóa học Oracle online
khóa học pl/sql
học dba
học dba ở việt nam
khóa học dba
khóa học dba sql
tài liệu học dba oracle
Khóa học Oracle online
học oracle sql
học oracle ở đâu tphcm
học oracle bắt đầu từ đâu
học oracle ở hà nội
oracle database tutorial
oracle database 12c
oracle database là gì
oracle database 11g
oracle download
oracle database 19c
oracle dba tutorial
oracle tunning
sql tunning
oracle 12c
oracle dataguard
oracle goldengate
oracle weblogic
oracle exadata
hoc solaris
hoc linux
hoc aix
