oracle sql order by multiple point string

ปัญหา

ถ้าเรา order by string แบบธรรมดาๆ  เราจะเจอปัญหาแบบนี้

การ order by string  10 จะมาก่อน 2  เพราะรหัส ASCII ของ 1 อยู่ก่อน 2

วิธีแก้

เปลี่ยนมา order by ด้วย sql function ที่ผมเขียนขึ้นมาใหม่  ได้เป็น

Oracle SQL แยกหัวข้อ ออกจากข้อความ

code

select data.*,
       trim(replace(data.old_name, (numb), '')) new_name
from(
  select name old_name, 
         regexp_substr(name, '^[0-9ก-ฮ\.]+\)*\s') numb
  from MY_TABLE
) data

แก้ปัญหา multiple values sql IN : oracle

คำสั่ง IN เป็นคำสั่งที่ใช้สำหรับตรวจสอบว่า  ค่าที่เลือกมานั้น  มีอยู่ใน IN หรือไม่  เช่น

...
...
SELECT code
FROM ...
WHERE code IN('AAA', 'BBB', 'CCC')
...
...

ซึงเราสามารถตรวจสอบได้เพียง ครั้งละ 1 ค่าเท่านั้น (single value)
ในที่นี้ field code สามารถเป็น AAA หรือ BBB หรือ CCC ได้เท่านั้น (ถึงจะ match กัน)
ไม่สามารถเป็น multiple values เช่น 'AAA, BBB' หรือ 'BBB, CCC' ได้ เพราะมันจะไม่ตรงกับตัวไหนใน IN เลย

 'AAA, BBB' ไม่ match กับ 'AAA'
 'AAA, BBB' ไม่ match กับ 'BBB'
 'AAA, BBB' ไม่ match กับ 'CCC'

แล้วถ้าเราต้องการให้มัน match กันละ  เราจะทำยังไง?
เพราะตามความเป็นจริงแล้ว  'AAA, BBB' มันควรจะ match กับทั้ง  'AAA' และ 'BBB'

มาดูแนวคิด + วิธีแก้ของผมกันครับ

Split String to rows : Oracle SQL

บางครั้งเราอาจเจอโจทย์ที่ต้องแยก String ออกเป็นหลายๆ rows ครับ

SELECT trim(regexp_substr('String1,String2,String3', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <= regexp_count('String1,String2,String3', ',')+1

thank you : http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows

แก้ปัญหา Oracle SQL LISTAGG not support

LISTAGG เป็น aggregate function ที่เอาไว้ join multiple row ให้เป้น single row  เช่น

ถ้าเรา run SQL นี้ไม่ได้ (เนื่องจาก version ไม่ support)

select gp, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) name
from(
  select 'A' gp, 1 name from dual
  UNION
  select 'A' gp, 2 name from dual
  UNION
  select 'A' gp, 3 name from dual
  UNION
  select 'A' gp, 4 name from dual
   UNION
  select 'B' gp, 5 name from dual
  UNION
  select 'B' gp, 6 name from dual
  UNION
  select 'B' gp, 7 name from dual
  UNION
  select 'B' gp, 8 name from dual
)

group by gp

ลอง run แบบนี้ดูครับ

select gp, RTRIM (XMLAGG (XMLELEMENT (e, name || ', ')).EXTRACT ('//text()'), ', ') name
from(
  select 'A' gp, 1 name from dual
  UNION
  select 'A' gp, 2 name from dual
  UNION
  select 'A' gp, 3 name from dual
  UNION
  select 'A' gp, 4 name from dual
   UNION
  select 'B' gp, 5 name from dual
  UNION
  select 'B' gp, 6 name from dual
  UNION
  select 'B' gp, 7 name from dual
  UNION
  select 'B' gp, 8 name from dual
)

group by gp

ถ้าทำแล้วมันมี string ที่ซ้ำกันแล้วเราต้องการลบตัวที่ซ้ำ ใช้ ตัวนี้ช่วยครับ

...
... /* replace YOUR_STRING */
...
REGEXP_REPLACE(REGEXP_REPLACE(regexp_replace( YOUR_STRING ,'([^,]+)(, \1)+', '\1'),',{2,}',','),'^,|,$','')
...
...

Thank you : http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle

age computation oracle sql

code

select trunc(months_between(sysdate,dob)/12) year,
    trunc(mod(months_between(sysdate,dob),12)) month,
    trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day
from (
    Select to_date('15122000','DDMMYYYY') dob 
    from dual
);

output
year      month      day
13        10             29

define oracle pl/sql collection (object and table) types

1. Define Types

oracle pl/sql delete from starts with

ลบข้อมูลทุกๆ table ที่ขึ้นต้นด้วย XXX ภายใต้ schema ที่กำหนด (ไม่ได้เช็ค constraint)

procedure

create or replace procedure delete_from_starts_with(p_schema varchar2, p_prefix varchar2) as
begin
    for record in (select x.table_name origin_name
            from dba_tables x
            where x.owner = p_schema
                  and x.table_name like (p_prefix || '_%')
        ) loop
      execute immediate ('delete from ' || record.origin_name);
    
    end loop;
end;

example to use

begin
    delete_from_starts_with('MY_SCHEMA', 'MY_TABLE_NAME_PREFIX');
end;

Pagination Query Oracle SQL

ต้องการ query ข้อมูล โดยเริ่มต้นตั้งแต่ข้อมูลลำดับที่ 11 (>10) เป็นต้นไป  จนถึงลำดับที่ 20

SELECT * 
FROM 
    (
        SELECT /*+ FIRST_ROWS */ data.*, ROWNUM rnum  
        FROM (
            /* custom your query */
        ) data
        WHERE ROWNUM <= 20
    ) 
WHERE rnum > 10

oracle pl/sql backup data (อีก 1 ภาษาโปรแกรมมิ่ง ที่โปรแกรมเมอร์ต้องใช้)

        บทความนี้จะเป็นบทความเกี่ยวกับการเขียน oracle pl/sql เพื่อ backup ข้อมูลทั้งหมดใน schema แบบรายปี ซึ่งได้รับ requirement จริงมาจาก user ผู้อ่านสามารถนำไปประยุกต์ใช้ ดัดแปลง เพื่อให้เข้ากับงานหรือ  requirement ที่ตนเองได้รับมา  ตามความเหมาะสม

        ตอนนี้ผมกำลังพัฒนาโปรเจ็คนึง  ซึ่งตาม 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 ชุดนี้  คือเขาจะเอาข้อมูลไปสรุปทำรายงานประจำปี  หรือรายงานย้อมหลังไปยังปีต่างๆ ครับ

นี่จึงเป็นที่มาของบทความวันนี้

pl/sql oracle drop table starting with

        บทความนี้เป็นตัวอย่างของการเขียน pl/sql เพื่อทำการ drop table ครับ (ดู code ดีๆ น่ะครับ  ผม purge ทิ้งเลย คือ  drop แบบไม่ต้องเก็บไว้ใน recycle bin  ถ้าใครจะเอาไปใช้  ดูดีๆ ก่อนน่ะครับ)

โดยมีเป้าหมาย คือ
        เพื่อ drop ทุก table ใน schema ที่เราต้องการ เฉพาะ table ที่มีชื่อขึ้นต้นด้วย Prefix_XXX

        เวลาผม  พัฒนาโปรเจ็คใดๆ  ก็ตาม  ก่อนที่ผมจะตั้งชื่อ table จริงๆ  นั้น  ผมจะมีการตั้ง prefix นำหน้า table ก่อนเสมอครับ  เพื่อความง่ายในการจัดการในภายหลัง  เพราะบางที  เราอาจมีการเรียกใช้ table จากภายนอกที่เราไม่ได้เป็นคนสร้างเอง  ชื่อ table เราอาจซ้ำกับของคนอื่นก็ได้  เพื่อเป็นการป้องกันปัญหาดังกล่าว  ก็ตั้ง prefix ให้มันซ่ะเลยครับ

oracle sql function : get columns name of table

create or replace function get_columns_name(tab_name VARCHAR2) return VARCHAR2 is
   val VARCHAR2(3200);
   
begin
  for col in (Select COLUMN_NAME from user_tab_columns where table_name = tab_name) loop
    val := val || ',' || col.column_name;
  end loop;
  
  return substr(val, 2);
end;

example

set serveroutput on
begin
    dbms_output.put_line ('tb_user --> ' || get_columns_name('tb_user'));
    -- tb_user --> id,name,username,password,version
end;

select last value oracle sql

        พอดี เมื่อวันศุกร์ที่ผ่านมา ผมต้องเตรียมข้อมูล เกี่ยวกับข้อมูลผู้ป่วยครับ ทีนี้ข้อมูลในฐานข้อมูลของผู้ป่วย 1 คน มันดันมีหลายอัน ผมก็เลยโทรไปถามเขาว่า จะให้เอาข้อมูลตัวไหน เขาเลยบอกว่า ให้เอาอันล่าสุดแล้วกัน (last value หรือ last update) โดยใช้ข้อมูล date ล่าสุดที่ผู้ป่วยไปพบแพทย์ครับ ก็เลยเป็นที่มาของการใช้คำสั่งนี้  ตัวอย่างนี้อาจจะยังไม่ค่อยสื่อเท่าไหร่น่ะครับ  ต้องเอาไปประยุกต์ใช้เองครับ

my_table

no	my_group	group_count
1	A	1
2	A	2
3	A	3
4	A	4
5	B	1
6	B	2
7	B	3
8	C	1
9	C	2
10	D	1

call store procedure inside SELECT statement : oracle

ต้องการวน loop ใน table เพื่อเรียกใช้งาน store procedure ที่เราเขียนไว้

begin
    for record in (select columnA, columnB, columnC from TEST_TABLE) loop
        -- 
        -- call store procedure in store package
        -- and pass variable (columnA, columnB and columnC) into store procedure
        --
        store_package.store_procedure(record.columnA, record.columnB, record.columnC);
    end loop;
end;

วันนี้ผมเจอกับเหตุการณ์นี้ครับ  คือพอดีผมเขียนคำสั่ง sql ขึ้นมา
แล้วปล่อยให้มันทำงานไปตามเงื่อนไขที่มีอยู่ใน table  ซึ่งมีอยู่เกือบๆ 1,000 เงื่อนไข
ผมก็คิดว่า  เดี๋ยวปล่อยให้มัน commit ในทีเดียวเลยแล้วกัน   ไม่น่าจะมีปัญหาอะไร
เพราะตอนที่ test นั้น ข้อมูลผมมีอยู่นิดเดียว  เลย run เสร็จภายในไม่กี่นาทีครับ

แต่พอเอา code ไป run จริงๆ มันก็ run ไม่ผ่านทุกที  เพราะข้อมูลจริงๆ ที่มีอยู่นั้น มีปริมาณมหาศาลมาก เมื่อต้องทำกับ 1,000 เงื่อนไขที่ผมใส่เข้าไป

ถ้าจะให้ run SQL จริงๆ run ทั้งวันก็ run ไม่เสร็จครับ  แต่กรณีนี้ตายตั้งแต่ 3 ชั่วโมงแรกแล้ว
นั่นเป็นเพราะ DBA (database administrator) เขาตั้งเวลา (timeout) เอาไว้ว่าถ้า run code แล้วยังไม่ commit ใน 3 ชม.  ให้ถือว่าคำสั่งนั้น fail ไปโดยปริยายครับ

โจทย์ที่ผมได้รับมาวันนี้คือ  ต้อง  run SQL ให้เสร็จ (commit) ภายใน 3 ชั่วโมง

ก็เลยต้องเอา code กลับมาแก้ใหม่  โดยย้าย code ที่ต้อง run ในแต่ละเงื่อนไขเข้าไปไว้ใน store procedure  เมื่อมันทำเงื่อนไขนึงเสร็จแล้ว  ก็ให้มัน commit ใน store procedure ทันทีครับ

แล้วเราจะส่งทุกเงื่อนไขเข้าไปใน store procedure ยังไง?

เลยเป็นที่มาของบทความนี้ครับ  โดยให้มันวน loop เพื่อส่งเงื่อนไขเข้าไปทีละเงื่อนไขนั่นเอง

คำสั่ง SQL row_number() over (partition by) : oracle

เอาไว้ "แสดงลำดับ row" ของข้อมูลบางส่วน (partition) ตามเงื่อนไขที่ต้องการ เช่น
แสดงลำดับ row ของ drg_code (drg_sequence) โดยจัดเรียงตาม (หรือสัมพันธ์กับ) departmet_id

table drug_code

id	drg_code	department_id
1	0892	1
2	0694	1
3	0782	2
4	0321	1
5	0120	1
6	1001	1
7	0985	2
8	0325	2
9	0695	3
10	0127	4
11	0364	4
12	0697	5
13	0247	1
14	0324	1
15	0364	2

select id,
       drg_code,
       department_id,
       row_number() over (partition by drg_code order by department_id, drg_code) drg_sequence

from drug_code 

SQL Oracle random value

random ระหว่าง 0 - 1

select dbms_random.value
from dual

การประยุกต์ใช้งาน

merge table (resolve update join column) SQL Oracle

ต้องการ update ข้อมูลใน TABLE_A.columnA2 ให้เป็นค่าเดียวกันกับ TABLE_B.column2
โดยมีเงื่อนไข TABLE_A.columnA1 = TABLE_B.column1

merge into TABLE_A
using (
      select  b.column1 columnB1,
              b.column2 columnB2
             
      from TABLE_B b
) on (TABLE_A.columnA1 = columnB1)
when matched 
then update
set TABLE_A.columnA2 = columnB2;

oracle sql : to_char year thai buddha

--
--
--
select to_char(sysdate, 'YYYY', 'nls_calendar=''Thai Buddha'' nls_date_language = Thai') year
from dual
--
--
--

insert select oracle

//
INSERT INTO DATABASE_NAME.table_A (field1, field2, field3, ...)
SELECT DATABASE_NAME.table_B.field1, 
       DATABASE_NAME.table_B.field2, 
       DATABASE_NAME.table_B.field3,
       ...
FROM DATABASE_NAME.table_B
WHERE ...
//

drop all tables sql oracle

/*

*/
select 'drop table '||table_name||' cascade constraints;' from user_tables;
/*

*/