5 CONVERSION FUNCTIONS Data type conversion Implicit data type - PowerPoint PPT Presentation

www.itsci.mju.ac.th/sayan LEC 04: SQL FUNCTIONS SAYAN UNANKARD 1/2558 5

CONVERSION FUNCTIONS Data type conversion Implicit data type Explicit data type conversion conversion 2

IMPLICIT DATA TYPE CONVERSION Oracle server สามารถเปลี่ยนแปลงค่าได้โดยอัตโนมัติ ดังนี้ From To NUMBER VARCHAR2 or CHAR VARCHAR2 or CHAR DATE From To VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE 3

EXPLICIT DATA TYPE CONVERSION TO_NUMBER TO_DATE NUMBER DATE CHARACTER TO_CHAR TO_CHAR 4

USING THE TO_CHAR FUNCTION WITH DATES TO_CHAR( date, ' format_model ') รูปแบบของ format_model • ต้องอยู่ภายในเครื่องหมาย single quotation marks • เป็น case-sensitive • สามารถก าหนดด้วยรูปแบบวันที่ตามที่ต้องการได้ • มีค าสั่ง fm element เพื่อจัดการช่องว่างหรือเลขศูนย์ที่น าหน้า ออกไปได้ เช่น 01/03/2008 5

ELEMENTS OF THE DATE FORMAT MODEL Element Result YYYY Full year in numbers YEAR Year spelled out (in English) MM Two-digit value for the month MONTH Full name of the month MON Three-letter abbreviation of the month DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week DD Numeric day of the month 6

ELEMENTS OF THE DATE FORMAT MODEL รูปแบบของเวลา HH24:MI:SS AM 15:45:32 PM การแทรกข้อความเข้าไปในรูปแบบ โดยอยู่ภายในเครื่องหมาย double quotation marks DD "of" MONTH 12 of OCTOBER การสะกดค าต่อท้ายของตัวเลข ddspth fourteenth 7

USING THE TO_CHAR FUNCTION WITH DATES SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees; … 8

USING THE TO_CHAR FUNCTION WITH NUMBERS TO_CHAR( number, ‘ format_model ’ ' ) สามารถจัดการรูปแบบของตัวเลข โดยสามารถใช้ค าสั่ง TO_CHAR function Element Result 9 Represents a number 0 Forces a zero to be displayed $ Places a floating dollar sign L Uses the floating local currency symbol . Prints a decimal point , Prints a comma as a thousands indicator 9

USING THE TO_CHAR FUNCTION WITH NUMBERS SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst'; to_char(1210.73, '9999.9') would return '1210.7' to_char(1210.73, '9,999.99') would return '1,210.73' to_char(1210.73, '$9,999.00') would return '$1,210.73' to_char(21, '000099') would return '000021' 10

NESTING FUNCTIONS ฟังก์ชันสามารถที่จะเรียกใช้ ซ้อน ๆ กันได้ การประมวลผลจะเรียกจากระดับข้างในก่อน F3(F2(F1(col,arg1),arg2),arg3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3 11

NESTING FUNCTIONS SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US')) FROM employees WHERE department_id = 60; 12

NVL FUNCTION การแปลงค่าว่างให้กลายเป็นค่าตามที่ต้องการ • ชนิดของข้อมูลที่สามารถใช้ในฟังก์ชันนี้ได้คือ date, character, และ number • ตัวอย่างการเรียกใช้ โดยที่ชนิดของข้อมูลที่เปลี่ยนต้องตรงกับข้อมูลเดิม • NVL(commission_pct,0) • NVL(hire_date,'01-JAN-08') • NVL(job_id,'No Job Yet') 13

USING THE NVL FUNCTION 1 SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; 2 … 1 2 14

CONDITIONAL EXPRESSIONS ในค าสั่ง SQL สามารถก าหนดเงื่อนไขในการแสดงผล โดยใช้ค าสั่ง IF-THEN-ELSE ประกอบด้วย 2 วิธีคือ • CASE expression • DECODE function 15

SIMPLE CASE Simple CASE Syntax SELECT Field, Field, CASE Field | Expression WHEN value THEN result WHEN value THEN result ELSE result END As alias FROM Table Examples Select Firstname, Case Gender When 'F' Then 'woman' When 'M' Then 'man' End As Sex From Members 16

SIMPLE CASE EXAMPLE Select Artistname, Region, Case Region When 'NC' Then 'South' When 'VA' Then 'South' When 'IL' Then 'Midwest' When 'VT' Then 'New England' Else 'Somewhere Else' End As Area From Artists 17

SIMPLE CASE EXAMPLE Artistname Region Area -------------- --------------- ------------- The Neurotics NC South Louis Holiday IL Midwest Word IN Somewhere Else Sonata VA South The Bullets TX Somewhere Else Jose MacArthur CA Somewhere Else Confused GA Somewhere Else The Kicks NY Somewhere Else Today ONT Somewhere Else 21 West Elm VT New England Highlander OH Somewhere Else 18

SWITCHED CASE Switched CASE Syntax SELECT Field, Field, CASE WHEN Field | Expression comparison Value | Field | Expression THEN result WHEN Field | Expression comparison Value | Field | Expression THEN result ELSE result END As alias FROM Table Select TrackNum, TrackTitle, LengthSeconds, Case When TrackNum=1 And LengthSeconds<240 Then 'Short 1st' When TrackNum=1 And LengthSeconds>480 Then 'Long 1st' Else 'Another Track' End as Eval From Tracks Where TrackNum<3 19

SWITCHED CASE EXAMPLE Select TrackNum, TrackTitle, LengthSeconds, Case When TrackNum=1 And LengthSeconds<240 Then 'Short 1st Track' When TrackNum=1 And LengthSeconds>480 Then 'Long 1st Track' Else 'Another Track' End as Eval From Tracks Where TrackNum<3 20

SWITCHED CASE EXAMPLE TrackNum TrackTitle LengthSeconds Eval ------------- ---------- ------------------- ------------ 1 Bob's Dream 185 Short 1st Track 2 My Wizard 233 Another Track 1 Fat Cheeks 352 Another Track 1 Hottie 233 Short 1st Track 2 GoodtimeMarch 293 Another Track 2 Rocky and Natasha 283 Another Track 1 Violin Sonata No.1 in D Major 511 Long 1st Track 2 Violin Sonata No. 2 in A Major 438 Another Track 1 Song 1 285 Another Track 2 Song 2 272 Another Track 1 I Don't Know 201 Short 1st Track 2 What's the Day 332 Another Track 21

DECODE FUNCTION การใช้ค าสั่ง DECODE เพื่อก าหนดเงื่อนไขได้เช่นเดียวกับค าสั่ง CASE DECODE( col|expression, search1, result1 [ , search2, result2,..., ] [ , default ]) 22

USING THE DECODE FUNCTION SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees; … … … 23

USING THE DECODE FUNCTION ตัวอย่างการแสดงค่าภาษี จากเงื่อนไขที่ก าหนดจากเงินเดือน เฉพาะแผนกหมายเลข 80 SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHERE department_id = 80; 24

WHAT ARE GROUP FUNCTIONS? ฟังก์ชันกลุ่มคือ ฟังก์ชันที่ได้จากการประมวลผลข้อมูลหลาย ๆ แถว EMPLOYEES Maximum salary in EMPLOYEES table … 25

TYPES OF GROUP FUNCTIONS • AVG • COUNT • MAX Group • MIN functions • STDDEV • SUM • VARIANCE 26

GROUP FUNCTIONS: SYNTAX SELECT group_function(column), ... FROM table [WHERE condition ] [ORDER BY column ]; 27

USING THE AVG AND SUM FUNCTIONS AVG ค่าเฉลี่ย SUM ค่าผลรวม ซึ่งจะใช้กับข้อมูลตัวเลขเท่านั้น SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%'; 28

USING THE MIN AND MAX FUNCTIONS MIN ค่าน้อยที่สุด MAX ค่ามากที่สุด สามารถใช้ได้กับ ตัวเลข ตัวอักษร และ วันที่ ได้ SELECT MIN(hire_date), MAX(hire_date) FROM employees; 29

USING THE COUNT FUNCTION COUNT(*) นับจ านวนแถวข้อมูลที่ตรงเงื่อนไข ที่ก าหนด SELECT COUNT(*) 1 FROM employees WHERE department_id = 50; COUNT( expr ) นับจ ำนวนแถวที่ไม่เป็นค่ำว่ำง ส าหรับคอลัมน์ expr SELECT COUNT(commission_pct) FROM employees 2 WHERE department_id = 80; 30