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

v     פונקציות מחרוזתיות – פונקציות אשר עובדות על מחרוזות.

v     פונקציות מספריות – פונקציות אשר עובדות על מספרים.

v     פונקציות תאריכיות – פונקציות אשר עובדות על תאריכים.

(שימו לב, פוסט זה מתמקד בפונקציות שורה, מידע על פונקציות קבוצה ניתן למצוא בפוסט הבא)

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

פוסט זה מתמקד בפונקציות השורה בסביבת Oracle, לרשימה חלקית של פונקציות שורה בסביבת SQL Server ניתן להשתמש בפוסט הבא

 

כיצד פונקציות השורה עובדות

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

 

SELECT UPPER(first_name)

FROM employees

 

הפונקציה מקבלת את ערכי העמודה first_name ומשנה את אותיותיהם לאותיות גדולות

 

תצוגת ערכי העמודה לפני השינוי:              תצוגת ערכי העמודה אחרי השינוי:

First_name

 

First_name

Moshe

            ß

MOSHE

Yossi

            ß

YOSSI

David

            ß

DAVID

כפי שניתן לראות, פונקציות אלו עובדות על הערכים שבכל שורה ושורה, ולכן נקראות פונקציות שורה.

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

 

טבלת DUAL

טבלת DUAL היא טבלה אשר נוצרת בעת התקנת אורקל, היא מורכבת מעמודה אחת (אשר נקראת DUMMY) ומרשומה אחת אשר באופן דיפולטיבי מכילה את הערך X.

טבלת DUAL היא טבלת מערכת מיוחדת אשר מטרתה לאפשר לנו לבצע חישובים שונים מתוך ה- SQL בעת הצורך. ננסה לעשות חישוב פשוט 1+1 מתוך משפט SELECT :

SELECT 1+1  (שגיאה)

פקודה שכזו תחזיר שגיאה משום שחסרה פקודת FROM.                    

 

כדי שנוכל להציג את התוצאה של חישוב מסוג זה, אנו נשתמש בטבלת DUAL   :          

SELECT 1+1

FROM DUAL  

למידת פונקציות חדשות ע"י שימוש בטבלת DUAL

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

אולם, בעולם האמיתי לכל עמודה יש ערכים רבים, ולהריץ את הפונקציה על עמודות שכאלו לשם הלמידה הוא לא מצב אידיאלי.

לכן, לדוגמא, אם התוודענו לפונקצית UPPER לראשונה ועתה אנו רוצים לבדוק כיצד היא עובדת, יהיה פשוט יותר אם נריץ אותה על טבלת DUAL כאשר בתוך הסוגריים נכתוב ערך ולא עמודה :

 

SELECT UPPER(‘hello’)

FROM DUAL  

 

פונקציות מחרוזתיות

 

פונקציות מחרוזתיות – מניפולציה על גודל אות

 

שם הפונקציה

תפקיד הפונקציה

דוגמא

UPPER

ממירה טקסט לאותיות גדולות

UPPER(‘hello’) à HELLO

LOWER

ממירה טקסט לאותיות קטנות

LOWER(‘HELLO’) à hello

INITCAP

ממירה טקסט לאות ראשונה גדולה והשאר אותיות קטנות

INITCAP(‘hello’) à Hello

 

פונקציות מחרוזתיות – מניפולציות על טקסט

 

שם הפונקציה

תפקיד הפונקציה

דוגמא

SUBSTR

מחזירה רצף תווים מתוך מחרוזת

SUBSTR(‘Hello’ , 2 , 3) à ell

LENGTH

מחזירה את אורך הטקסט

LENGTH (‘Hello’) à 5

REPLACE

מחליפה ערכים בתוך המחרוזת

REPLACE (‘Hello Oracle’ , ‘Oracle’, ‘World’ ) à

Hello World

פונקציות מספריות

 

שם הפונקציה

תפקיד הפונקציה

דוגמא

ROUND

מעגלת מספר עשרוני (0-4 כלפי מטה, 5-9 כלפי מעלה)

ROUND(32.46 , 1) à 32.5

ROUND(32.42 , 1) à 32.4

TRUNC

מעגלת מספר עשרוני (תמיד כלפי מטה)

TRUNC (32.59 , 1) à 32.5

פונקציות תאריכיות

חישובים מותרים על תאריכים

הפעולה

תיאור

סוג התוצאה

תאריך + מספר

הוספת ימים לתאריך 

תאריך

תאריך – מספר

חיסור ימים מהתאריך

תאריך

תאריך – תאריך

חיסור תאריך מתאריך (תוצאה בימים)

מספר

תאריך + תאריך24

הוספת שעות לתאריך

תאריך

תאריך – תאריך24

חיסור שעות מתאריך

תאריך

 

חישובים אסורים על תאריכים

הפעולה

תיאור

סוג התוצאה

מספר – תאריך

חיסור תאריך ממספר  

שגיאה

מספר * תאריך

הכפלת תאריך במספר

שגיאה

תאריך מספר

מספר תאריך

חילוק תאריך במספר

חילוק מספר בתאריך

שגיאה

שגיאה

תאריך + תאריך

חיבור תאריך לתאריך אחר

שגיאה

 

פונקציות תאריכיות

 

שם הפונקציה

תפקיד הפונקציה

דוגמא

SYSDATE

הצגת התאריך העכשווי

SELECT SYSDATE

FROM DUAL

MONTHS_BETWEEN

הצגת הפרש בחודשים בין שני תאריכים

MONTHS_BETWEEN(’01-SEP-2000’ , ’01-JAN-2000’) à 8

ADD_MONTHS

הוספת חודשים לתאריך

ADD_MONTHS(’01-JAN-2000’ , 8) à 01-SEP-2000

NEXT_DAY

מתי היום הבא יחסית לתאריך מסויים

בהנחה ש- SYSDATE הוא יום שישי 25.03.2011

 

NEXT_DAY(SYSDATE , 'SUNDAY') à 27-MAR-2011

לפוסט הבא בנושא – פונקציות המרה