במדריך זה נתמקד בסוג מיוחד של פונקציות שורה – פונקציות המרה. אם נרצה להגדיר את מטרת פונקציות ההמרה במשפט, נאמר כי הן נועדו להמיר סוג מסויים של עמודה לסוג אחר.

כדי להבין את נושא זה טוב יותר, נתחיל בהסבר לגבי מנגנון ההמרה האוטומטי של אורקל.

מנגנון ההמרה האוטומטי של אורקל (Implicit Conversions)

כדי לבצע אופרציות שונות בשפת ה- SQL יהיה הגיוני לדרוש כי סוגי הערכים יהיו תואמים. למשל, לא הגיוני שנחבר 'טקסט' למספר (salary + 'some text'), או לא הגיוני שנשווה מספר למחרוזת (where salary = 'some_text').

בפועל אורקל מאפשר לנו לעשות דברים אלו, דוגמאות הקוד הבאות יעבדו :

חיבור טקסט למספר

SELECT salary + '45' 

FROM employees 

אמנם כתבנו מספר (45) אך ברגע שהקפנו אותו בגרשים אורקל מתייחס אליו כמחרוזת לכל דבר ולמרות זאת השאילתה פועלת בצורה תקינה.

השוואת  טקסט למספר:

WHERE salary = '5800'

שוב, אמנם כתבנו מספר (5800) אך ברגע שהקפנו אותו בגרשים אורקל מתייחס אליו כמחרוזת לכל דבר ולמרות זאת השאילתה פועלת בצורה תקינה.

אורקל מסוגל לבצע פעולות אלה ע"י מנגנון אשר נקרא Implicit Conversion, על אף שהוא מבין שעומדת לפניו מחרוזת, הוא בודק האם במקרה הספציפי הזה ניתן להמירה למספר (כי אנו מנסים לחבר אותה למספר להשוותה למספר) ובמידה והדבר אפשרי מתבצעת ההמרה.

!         הדוגמאות שהובאו הינן להמחשת הרעיון בלבד, אין כל סיבה לחבר מספר מוקף בגרשים למספר אחר, כדי לחשוב על דוגמא מעשית יותר דמיינו כי קיימת עמודה מחרוזתית אשר מכילה בתוכה מספרים ואותה אנו רוצים לחבר להשוות לעמודה מספרית אחרת.

המרות אוטומטיות שאורקל יודע לבצע

SELECT salary + '45' 

WHERE hire_date = '01-JAN-2000' 

SELECT 'some text' || salary

SELECT 'some text' || hire_date

מומלץ לא להשתמש בהמרות האוטומטיות מכמה סיבות :

v     המרות מפורשות (הנושא הבא) הרבה יותר קריאות ומובנת.

v     האלגוריתמים אשר מפעילים את ההמרות האוטומטיות עלולים להשתנות בין גרסאות או אפליקציות שונות של אורקל, ההמרות המפורשות הרבה יותר צפויות.

v     המרות אוטומטיות תלויות בערכי המקור אותן הן ממירות, קיימים מצבים בהם ערך המקור ישתנה (לדוגמא הפורמט הדיפולטיבי של התאריך ישתנה) וכתוצאה מכך ההמרות האוטומטיות יחזירו ערכים לא צפויים או שגיאה.

 פונקציות המרה מפורשות (Explicit Data Conversion)

על מנת להמיר מפורשות ערך מסויים לערך אחר אנו נשתמש בפונקציות ההמרה הבאות:

פונקציית TO_CHAR

פונקציית TO_CHAR מאפשרת לנו:

v     לבצע המרה מפורשת ממספר למחרוזת.

v     לבצע המרה מפורשת מתאריך למחרוזת.

v     לבצע המרה מפורשת (מתאריך או מספר) יחד עם שינוי הפורמט.

המרה מממספר תאריך למחרוזת :

TO_CHAR(4)  '4'

TO_CHAR('01-JAN-2000')  '01-JAN-2000'

 המרה ממספר למחרוזת יחד עם שינוי הפורמט

פונקציית TO_CHAR מאפשרת לנו יחד עם ההמרה למחרוזת לשנות גם את הפורמט המספרי ע"י הוספת ארגומנט נוסף:

TO_CHAR(4500 , '$9,999')  '$4,500'

TO_CHAR(4500 , '$9,999.00' )  '$4500.00'

v     9 – מייצג מספר בתבנית.

v     0 – מציג 0 בכל מקום שבו אין מספר.

v     $ – מציג את סימן הדולר.

v     , – מציג פסיק כהפרדה בין אלפים.

v     . – מציג נקודה עשרונית.

המרה מתאריך למחרוזת יחד עם שינוי הפורמט

פונקציית TO_CHAR מאפשרת לנו יחד עם ההמרה למחרוזת לשנות גם את הפורמט התאריכי ע"י הוספת ארגומנט נוסף:

בהנחה ש- SYSDATE = 01.01.2000

TO_CHAR(SYSDATE , 'Day / Month / Year' ) 

(output) Saturday/ January/ Two Thousand

v     YYYY שנה מלאה בספרות

v     Year שנה במילים

v     MM – חודש בספרות

v     Month – חודש במילים

v     DD – יום בספרות

v     Day – יום במילים

פונקציית TO_DATE

המרה ממחרוזת לתאריך

TO_DATE('date_value' , 'string_value')

 

TO_DATE('01-JAN-2010')   '01-JAN-2010'

המרה ממחרוזת לתאריך יחד עם שינוי הפורמט

למדנו כי אורקל יודע לזהות תאריכים במבנה דיפולטיבי ספציפי (DD-MON-YYYY) לעיתים מבנה הדיפולטיבי זה ישתנה.

פונקציית TO_DATE מאפשרת לנו יחד עם ההמרה לתאריך, "ללמד" את אורקל מהו התאריך החדש העומד לפניו :

TO_DATE(ערך_מחרוזתי , 'פורמט')  ערך תאריכי

TO_DATE('Saturday/ January/ Two Thousand' , 'Day / Month / Year')

 01.01.2000

פונקציית TO_NUMBER

המרה ממחרוזת למספר

TO_NUMBER('מחרוזת')

 
TO_NUMBER('5600')  5600

המרה ממחרוזת למספר יחד עם שינוי הפורמט

אורקל יודע לזהות מספרים במבנה נומרי רגיל, לעיתים המספרים אשר איתם נעבוד לא יתאימו למבנה זה (למשל עמודת מחיר מוצר שמכילה ערכים בסגנון : '5,603$' ).

פונקציית TO_NUMBER מאפשרת לנו יחד עם ההמרה למספר, "ללמד" את אורקל מהו המספר החדש העומד לפניו :

 TO_NUMBER(numeric_value , 'string_value')

TO_NUMBER('5,603$' , '$9,999')  5603