Posted by: siamnobita | 09/24/2015

NO_DATA_FOUND in SQL statements

หัวข้อวันที่มีที่มาจาก Nimish Garg’s blog

ใช้งาน oracle มาก็นานปี แต่ผมเพิ่งรู้จริง ๆ นะว่าคำสั่ง SQL ทั้งหมดมองข้าม exception NO_DATA_FOUND เหมือนไม่มีอะไรเกิดขึ้น

create or replace function test_err_1
return number as
begin
raise no_data_found ;
return 0;
end;
/

exec dbms_output.put_line (test_err_1)

Error starting at line 1 in command:
exec dbms_output.put_line (test_err_1)
Error report:
ORA-01403: no data found
ORA-06512: at “TOM.TEST_ERR_1”, line 4
ORA-06512: at line 1

select test_err_1 from dual ;

TEST_ERR_1
———-
จะเห็นว่าเมื่อทดสอบฟังก์ชั่น test_err_1 ด้วย pl/sql จะเกิด error ขณะที่เมื่อทดสอบด้วย sql จะคืนค่า null ออกมาเฉย นอกจากคำสั่ง select แล้วหากเราทดสอบด้วยคำสั่ง insert หรือ update ก็ได้ผลเช่นเดียวกัน

create table test1 (n number) ;

table TEST1 created.

insert into test1 values (test_err_1) ;

1 rows inserted.

update test1 set n = test_err_1 ;

1 rows updated.

ด้วยความอยากรู้ว่าทำไมจึงเป็นแบบนี้ และเป็นแบบนี้มาตั้งแต่เมื่อไร จึงค้นดู ได้คำตอบจาก Tom Kyte เจ้าเก่าตาม นี้

สรุปคือมันเป็นแบบนี้มานานแล้ว ด้วย oracle มองว่า exception ไม่ใช่ error หรือข้อผิดพลาด เป็นแค่การดักเหตุการณ์บางอย่างขึ้นอยู่กับผู้เขียนโปรแกรมเองที่จะมองว่าเหตุการณ์นั้นถือเป็น error หรือไม่ได้เป็น ซึ่งในกรณีนี้ SQL engine ไม่มองว่า no_data_found เป็น error เป็นแค่การแจ้งเตือนว่า ข้อมูลที่มีหมดแล้ว เลิกงานได้ มันจึงมองข้ามไปเฉย ๆ เท่านั้น ซึ่งหากเป็น exception ตัวอื่น มันก็จะแสดงข้อผิดพลาดออกมาตามปกติ ซึ่งพฤติกรรมนี้ไม่ได้บันทึกไว้ในเอกสารใด ๆ ของ oracle เลย เพราะเค้ามองว่ามันเป็นเรื่องปกติมากกกกก (แอบมึน ปกติตรงไหนเนี่ยะ)

หากเราต้องการให้ SQL มอง NO_DATA_FOUND เป็นข้อผิดพลาด แล้วหยุดการทำงานต้องดัก exception ไว้ แล้ว raise ตัวอื่นมาแทนแบบนี้

create or replace function test_err_2
return number as
begin
raise no_data_found ;
return 0;
exception
when no_data_found then raise program_error ;
end;
/

select test_err_2 from dual ;

ORA-06501: PL/SQL: program error
ORA-06512: at “TOM.TEST_ERR_2”, line 7
ORA-01403: no data found

วันนี้ก็ได้ความรู้ใหม่สำหรับตัวเองอีกแล้ว


ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

หมวดหมู่

%d bloggers like this: