ตอนนี้ผมกำลังพัฒนาโปรเจ็คนึง ซึ่งตาม requirement ของ user เขาต้องการให้ผมทำการ backup ข้อมูลทุกอย่าง ทุก tables ทำเป็นข้อมูลรายปีเก็บไว้ ซึ่งข้อมูล มีประมาณ 80 tables ถ้าจะให้ backup มือก็คงจะไม่สะดวก ผมก็เลยปรึกษากันในทีมว่าจะเอายังไง จนได้มติหรือผลออกมาว่า ถ้าอย่างนั้น เรา clone table ทั้งหมดไว้ เรียกว่า "log table" คือตั้งชื่อเป็น ylog_XXX (XXX คือชื่อ table เก่าที่เรา clone มา) แล้วเพิ่ม column ปีเข้าไป น่าจะโอเคกว่า ylog_XXX คือเก็บข้อมูลของ table XXX แต่มีปีกำกับ ส่วน table XXX เราก็เก็บเฉพาะข้อมูลปีล่าสุด หรือปีปัจจุบัน เพื่อเอาไปแสดงผลให้ user เห็น
เหตุผลที่ผมได้รับมาจาก user สำหรับ requirement ชุดนี้ คือเขาจะเอาข้อมูลไปสรุปทำรายงานประจำปี หรือรายงานย้อมหลังไปยังปีต่างๆ ครับ
นี่จึงเป็นที่มาของบทความวันนี้
Concept ของการ backup ข้อมูล
มี 2 step ครับ คือ
- เขียน pl/sql เพื่อ clone table หลักทั้งหมด แล้วก็ insert ข้อมูลลงไป เป็นรายปี (ข้อมูลมีปีกำกับ)
- การ backup ข้อมูลรายปี เราจะใช้ feature หนึ่งของ oracle คือ oracle scheduler (ตั้งเวลา backup)
ทำความเข้าใจ ก่อนการอ่าน code
ปกติ เวลาที่ผมพัฒนาโปรเจ็คอะไรก็ตาม ผมจะต้องคิดชื่อย่อโปรเจ็คนั้น แล้วนำมาตั้งเป็น prefix name ของ table ครับ เพื่อความง่ายในการ management เช่นการ clone การ drop การลบข้อมูล หรือบางทีเราอาจมีการเรียกใช้ table จากภายนอก ซึ่งชื่อ table อาจจะซ้ำกัน และเพื่อความง่ายในการทำความเข้าใจว่า table นี้มาจากระบบใด ทุกระบบที่ผมทำเลยต้องมี prefix name นำหน้าชื่อ table เสมอ เช่นระบบ ABC_XXX, ABC_YYY, ABC_ZZZ เป็นต้น prefix name ในที่นี้คือ ABC ครับ
เริ่มต้นด้วยการเขียน pl/sql
ดู code เต็มก่อนครับ เดี๋ยวผมจะไล่อธิบายทีละ procedure หรือ function ซึ่งในที่นี้ผมเขียนเป็น package ไว้
package header : transaction_log_pack
create or replace PACKAGE trasaction_log_pack AS
procedure run_sql(p_sql varchar2);
function is_table_exist(p_schema varchar2, p_table_name varchar2) return number;
procedure drop_table_constraint(p_schema varchar2, p_table_name varchar2);
procedure drop_from(p_schema varchar2, p_prefix varchar2);
procedure copy_table(p_schema varchar2, origin_name varchar2, new_name varchar2);
procedure copy_table_comment(p_schema varchar2, origin_name varchar2, new_name varchar2);
procedure copy_column_comment(p_schema varchar2, origin_name varchar2, new_name varchar2);
procedure copy_from_to_one(p_schema varchar2, origin_name varchar2, new_name varchar2);
procedure copy_from_to(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2);
function get_columns_name(p_name varchar2) return varchar2;
procedure insert_into_from(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2, p_year number);
END trasaction_log_pack;
package body : transaction_log_pack
create or replace PACKAGE BODY trasaction_log_pack AS
procedure run_sql(p_sql varchar2) as
begin
execute immediate p_sql;
end;
------------------------------------------------------------------------------------
function is_table_exist(p_schema varchar2, p_table_name varchar2) return number as
exist number(1) := 0;
begin
select count(x.table_name) into exist
from dba_tables x
where x.owner = p_schema
and x.table_name = p_table_name;
if exist > 0 then
exist := 1;
end if;
return exist;
end;
------------------------------------------------------------------------------------
procedure drop_table_constraint(p_schema varchar2, p_table_name varchar2) as
begin
for cur in (
select
owner,
constraint_name,
table_name
from all_constraints
where owner = p_schema and
table_name = p_table_name
) loop
run_sql('ALTER TABLE ' || cur.owner || '.' || cur.table_name || ' MODIFY CONSTRAINT "' || cur.constraint_name || '" DISABLE ');
end loop;
end;
------------------------------------------------------------------------------------
procedure drop_from(p_schema varchar2, p_prefix varchar2) as
begin
for tab in (
select x.table_name origin_name
from dba_tables x
where x.owner = p_schema
and x.table_name like (p_prefix || '_%')
) loop
run_sql('drop table "' || p_schema || '"."' || tab.origin_name || '" cascade constraints PURGE');
end loop;
exception
when others then dbms_output.put_line('drop_from exception --> ' || DBMS_UTILITY.format_error_stack);
end;
------------------------------------------------------------------------------------
procedure copy_table(p_schema varchar2, origin_name varchar2, new_name varchar2) as
begin
dbms_output.put_line('copy_table : copy table from ' || origin_name || ' to ' || new_name);
run_sql('create table "' || p_schema || '".' || new_name || ' as select 2557 as year, tab.* from "' || p_schema || '"."' || origin_name || '" tab where ' || 11 || ' = 1');
drop_table_constraint(p_schema, new_name);
exception
when
others then dbms_output.put_line('copy_table : create table exception --> ' || DBMS_UTILITY.format_error_stack);
end;
------------------------------------------------------------------------------------
procedure copy_table_comment(p_schema varchar2, origin_name varchar2, new_name varchar2) as
c_tab_comment varchar2(32767);
begin
select comments into c_tab_comment
from sys.all_TAB_comments
where owner = p_schema
and table_name = origin_name and comments is not null;
run_sql('comment on table ' || p_schema || '.' || new_name || ' is ' || '''' || c_tab_comment || '''');
exception
when others then dbms_output.put_line('copy_table_comment : copy table comment exception --> ' || DBMS_UTILITY.format_error_stack);
end;
------------------------------------------------------------------------------------
procedure copy_column_comment(p_schema varchar2, origin_name varchar2, new_name varchar2) as
begin
for tc in (
select column_name
from sys.all_tab_cols
where owner = p_schema
and table_name = origin_name
) loop
for c in (
select comments
from sys.all_col_comments
where owner = p_schema
and table_name = origin_name
and column_name = tc.column_name
) loop
run ('comment on column ' || p_schema || '.' || new_name || '.' || tc.column_name || ' is ' || '''' || c.comments || '''');
end loop;
end loop;
exception
when others then dbms_output.put_line('copy_column_comment : copy column comment exception --> ' || DBMS_UTILITY.format_error_stack);
end;
------------------------------------------------------------------------------------
procedure copy_from_to_one(p_schema varchar2, origin_name varchar2, new_name varchar2) as
begin
copy_table(p_schema, origin_name, new_name);
copy_table_comment(p_schema, origin_name, new_name);
copy_column_comment(p_schema, origin_name, new_name);
end;
------------------------------------------------------------------------------------
procedure copy_from_to(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2) as
begin
for tab in (
select x.table_name origin_name,
replace(x.table_name, p_prefix, p_new_prefix) new_name
from dba_tables x
where x.owner = p_schema
and x.table_name like (p_prefix || '_%')
) loop
copy_from_to_one(p_schema, tab.origin_name, tab.new_name);
end loop;
exception
when others then dbms_output.put_line('copy_from_to exception --> ' || DBMS_UTILITY.format_error_stack);
end;
------------------------------------------------------------------------------------
function get_columns_name(p_name varchar2) return varchar2 is
val varchar2(32767);
begin
for col in (select column_name from user_tab_columns where table_name = p_name) loop
val := val || ',' || col.column_name;
end loop;
return substr(val, 2);
end;
------------------------------------------------------------------------------------
procedure insert_into_from(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2, p_year number) as
l_sql varchar(32767);
l_columns varchar(32767);
begin
for tab in (
select x.table_name origin_name,
replace(x.table_name, p_prefix, p_new_prefix) new_name
from dba_tables x
where x.owner = p_schema
and x.table_name like (p_prefix || '_%')
) loop
if (is_table_exist(p_schema, tab.new_name) = 0) then
copy_from_to_one(p_schema, tab.origin_name, tab.new_name);
end if ;
l_columns := get_columns_name (tab.origin_name);
l_sql := 'insert into ' || tab.new_name || '(' || l_columns || ', YEAR) ';
l_sql := l_sql || '(select ' || l_columns || ',' || p_year || ' YEAR from ' || tab.origin_name || ' org)';
dbms_output.put_line('insert_into_from : insert sql --> ' || l_sql);
run_sql(l_sql);
commit;
end loop;
exception
when others then dbms_output.put_line(DBMS_UTILITY.format_error_stack);
end;
end trasaction_log_pack;
ไม่ยากใช้มั้ยครับ ^^ ผมก็พึ่งลองเขียนวันนี้เอง ฮ่าๆๆมาดูคำอธิบายทีละส่วนกันครับ
คำอธิบาย code
1. procedure run_sql(p_sql varchar2)
อันนี้ไม่มีอะไรครับ เป็น code ที่ใช้สำหรับ execute sql หรือ run sql ที่เป็น string ผ่าน pl เท่านั้นครับ
procedure run_sql(p_sql varchar2) as
begin
execute immediate p_sql;
end;
2. function is_table_exist(p_schema varchar2, p_table_name varchar2) return number
เป็น code สำหรับ check ว่ามี table นั้นอยู่ใน schema แล้วเราไม่ ซึ่งผมจะเอาไปเช็คเกี่ยวกับการ clone table ครับ ถ้ามันมีอยู่แล้วก็ไม่ต้อง clone มา (มี return 1 ไม่มี return 0)
function is_table_exist(p_schema varchar2, p_table_name varchar2) return number as
exist number(1) := 0;
begin
select count(x.table_name) into exist
from dba_tables x
where x.owner = p_schema
and x.table_name = p_table_name;
if exist > 0 then
exist := 1;
end if;
return exist;
end;
3. procedure drop_table_constraint(p_schema varchar2, p_table_name varchar2)
สำหรับ drop constraint ทุกอย่าง ของ table ที่เรา clone มา (log) ซึ่งถ้าเราไม่ drop constraint ก่อน เราอาจจะไม่สามารถ insert ข้อมูลได้
procedure drop_table_constraint(p_schema varchar2, p_table_name varchar2) as
begin
for cur in (
select
owner,
constraint_name,
table_name
from all_constraints
where owner = p_schema and
table_name = p_table_name
) loop
run_sql('ALTER TABLE ' || cur.owner || '.' || cur.table_name || ' MODIFY CONSTRAINT "' || cur.constraint_name || '" DISABLE ');
end loop;
end;
4. procedure drop_from(p_schema varchar2, p_prefix varchar2)
อันนี้เป็น code เสริมที่ผมเขียนขึ้นมา สำหรับเอาไว้ drop table ครับ คือ drop ทุก table ใน p_schema ซึ่งมีชื่อขึ้นต้นด้วยคำว่า p_prefix เรื่องนี้ ผมเขียนไว้ในบทความก่อนหน้านี้แล้วครับ (pl/sql oracle drop table starting with)
procedure drop_from(p_schema varchar2, p_prefix varchar2) as
begin
for tab in (
select x.table_name origin_name
from dba_tables x
where x.owner = p_schema
and x.table_name like (p_prefix || '_%')
) loop
run_sql('drop table "' || p_schema || '"."' || tab.origin_name || '" cascade constraints PURGE');
end loop;
exception
when others then dbms_output.put_line('drop_from exception --> ' || DBMS_UTILITY.format_error_stack);
end;
5. procedure copy_table(p_schema varchar2, origin_name varchar2, new_name varchar2)
สำหรับ copy หรือ clone table หลัก โดยเปลี่ยนชื่อเป็น new_name แล้วเพิ่ม column ปี เข้าไป จะสังเกตเห็นว่าหลังจากที่เรา clone เสร็จเราจะ drop constraint ทันที
procedure copy_table(p_schema varchar2, origin_name varchar2, new_name varchar2) as
begin
dbms_output.put_line('copy_table : copy table from ' || origin_name || ' to ' || new_name);
run_sql('create table "' || p_schema || '".' || new_name || ' as select 2557 as year, tab.* from "' || p_schema || '"."' || origin_name || '" tab where ' || 11 || ' = 1');
drop_table_constraint(p_schema, new_name);
exception
when
others then dbms_output.put_line('copy_table : create table exception --> ' || DBMS_UTILITY.format_error_stack);
end;
6. procedure copy_table_comment(p_schema varchar2, origin_name varchar2, new_name varchar2)
สำหรับ copy comment ของ table ครับ จะได้รู้ว่า table ที่เรา copy ไปมีคำอธิบายว่าอะไร ตอนนี้เราอาจจะไม่ลืม แต่พอผ่านไปสักพัก ผมเชื่อว่า เราจะจำไม่ได้ว่า table นั้นๆเก็บข้อมูลอะไรไว้ ถึงชื่อ table ที่เราตั้งจะสื่อแค่ไหน แต่บางที มันก็ต้องการคำอธิบายความที่มากกว่า แค่ชื่อน่ะครับ
procedure copy_table_comment(p_schema varchar2, origin_name varchar2, new_name varchar2) as
c_tab_comment varchar2(32767);
begin
select comments into c_tab_comment
from sys.all_TAB_comments
where owner = p_schema
and table_name = origin_name and comments is not null;
run_sql('comment on table ' || p_schema || '.' || new_name || ' is ' || '''' || c_tab_comment || '''');
exception
when others then dbms_output.put_line('copy_table_comment : copy table comment exception --> ' || DBMS_UTILITY.format_error_stack);
end;
7. procedure copy_column_comment(p_schema varchar2, origin_name varchar2, new_name varchar2)
สำหรับ copy column comment คำอธิบายก็เหมือนข้อ 6 ครับ
procedure copy_column_comment(p_schema varchar2, origin_name varchar2, new_name varchar2) as
begin
for tc in (
select column_name
from sys.all_tab_cols
where owner = p_schema
and table_name = origin_name
) loop
for c in (
select comments
from sys.all_col_comments
where owner = p_schema
and table_name = origin_name
and column_name = tc.column_name
) loop
run ('comment on column ' || p_schema || '.' || new_name || '.' || tc.column_name || ' is ' || '''' || c.comments || '''');
end loop;
end loop;
exception
when others then dbms_output.put_line('copy_column_comment : copy column comment exception --> ' || DBMS_UTILITY.format_error_stack);
end;
8. procedure copy_from_to_one(p_schema varchar2, origin_name varchar2, new_name varchar2)
เป็นการ รวบคำสั่งในข้อ 5 6 7 ให้เหลือข้อเดียว ถ้ามองในมุมมองของการออกแบบโปรแกรม เราก็คงเรียกมันว่า facade design pattern ครับ - การรวมงานไว้ในจุดเดียว (ไปหาอ่านเอาเองน่ะครับ ^^)
procedure copy_from_to_one(p_schema varchar2, origin_name varchar2, new_name varchar2) as
begin
copy_table(p_schema, origin_name, new_name);
copy_table_comment(p_schema, origin_name, new_name);
copy_column_comment(p_schema, origin_name, new_name);
end;
9. procedure copy_from_to(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2)
จากข้อ 8 เป็นการ copy หรือ clone table ต่อ table แต่ข้อนี้ เป็นการ clone ทุก table ใน schema ครับ โดยเปลี่ยน prefix_name ก่อนหน้านั้น (p_prefix) ไปเป็น prefix_name ใหม่ (p_new_prefix)
procedure copy_from_to(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2) as
begin
for tab in (
select x.table_name origin_name,
replace(x.table_name, p_prefix, p_new_prefix) new_name
from dba_tables x
where x.owner = p_schema
and x.table_name like (p_prefix || '_%')
) loop
copy_from_to_one(p_schema, tab.origin_name, tab.new_name);
end loop;
exception
when others then dbms_output.put_line('copy_from_to exception --> ' || DBMS_UTILITY.format_error_stack);
end;
10. function get_columns_name(p_name varchar2) return varchar2สำหรับดึง column name ของ table นั้นๆ ออกมา แล้ว join ด้วย comma ครับ
มีรูปแบบเป็น column1, column2, column3, ...
function get_columns_name(p_name varchar2) return varchar2 is
val varchar2(32767);
begin
for col in (select column_name from user_tab_columns where table_name = p_name) loop
val := val || ',' || col.column_name;
end loop;
return substr(val, 2);
end;
11. procedure insert_into_from(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2, p_year number)
สุดท้าย ตัวนี้คือตัว backup ข้อมูลจริงๆ โดย มันจะทำการ check table ก่อนว่ามีอยู่ใน schema แล้วหรือไม่ ซึ่งถ้ายังไม่มี มันก็จะทำการ clone หรือ copy table นั้นๆ ก่อน โดย table ที่ถูก clone จะมีการเพิ่ม column ปี (year) เข้าไป จากนั้นก็ทำการ copy ข้อมูลจาก table หลักมาใส่ table clone ด้วยคำสั่ง insert into พร้อมทั้งใส่ ปีเข้าไปด้วย
procedure insert_into_from(p_schema varchar2, p_prefix varchar2, p_new_prefix varchar2, p_year varchar2) as
l_sql varchar(32767);
l_columns varchar(32767);
begin
for tab in (
select x.table_name origin_name,
replace(x.table_name, p_prefix, p_new_prefix) new_name
from dba_tables x
where x.owner = p_schema
and x.table_name like (p_prefix || '_%')
) loop
if (is_table_exist(p_schema, tab.new_name) = 0) then
copy_from_to_one(p_schema, tab.origin_name, tab.new_name);
end if ;
l_columns := get_columns_name (tab.origin_name);
l_sql := 'insert into ' || tab.new_name || '(' || l_columns || ', YEAR) ';
l_sql := l_sql || '(select ' || l_columns || ',' || p_year || ' YEAR from ' || tab.origin_name || ' org)';
dbms_output.put_line('insert_into_from : insert sql --> ' || l_sql);
run_sql(l_sql);
commit;
end loop;
exception
when others then dbms_output.put_line(DBMS_UTILITY.format_error_stack);
end;
เวลาใช้งานจริงๆ ก็จะใช้ procedure ในข้อ 11 ครับ สมมติ ว่าเราต้องการ backup ข้อมูลใน
schema MY_SCHEMA
เฉพาะ table ที่มีชื่อขึ้นต้นด้วย ABC_XXX
จากนั้นเปลี่ยนชื่อเป็น YABC_XXX
ตั้งเป็นข้อมูลปี 2557
begin
trasaction_log_pack.insert_into_from('MY_SCHEMA', 'ABC', 'YABC', 2557);
end;
ABC_XXX --> YABC_XXX
ไม่มีความคิดเห็น:
แสดงความคิดเห็น