تطبيق وصل wassll لمتابعة مخصصات المحروقات

الدوال Functions في قواعد البيانات

الدوال Functions قواعد البيانات, الدالة Function هي عبارة عن أداة قوية و مفيدة عند إستخدام جملة SELECT فإن الدوال لجملة SELECT مرونة و منطق.

دوال الصف الواحد Single Row Functions

دوال الصف الواحد في Oracle هي عبارة عن دوال Functions تتعامل مع بيانات صف واحد فقط و تكون نتيجتها قيمة واحدة وتستخدم في أي مكان من جملة SELECT.

أنواع دوال الصف الواحد

1. دوال حرفية Character Functions.
2. دوال رقمية Number Functions.
3. دوال تاريخ Date Functions.
4. دوال التحويل Conversion Functions.

الدوال الحرفية Character Functions

دوال الحرفية هي الدوال التي تتعامل مع بيانات حرفية Character و تكون بياناتها إما رقماً أو حرفاً و توجد عدة قيم لهذا الأمر سوف نذكرها وهي :

دالة LOWER: تستخدم لتحويل جميع حروف عمود أو سلسلة إلى حروف صغيرة.

Select LOWER('GOOD by') from dual;

دالة UPPER: تستخدم لتحويل جميع حروف عمود أو سلسلة إلى حروف كبيرة.

Select UPPER('GOOD by') from dual;

دالة INITCAP: تستخدم لتحويل الحرف الأول فقط من عمود أو سلسلة إلى حرف كبير و باقي الأحرف تحولها إلى حروف صغيرة.

Select INITCAP('GOOD') from dual;

دالة CONCAT: تستخدم لربط عمودين أو سلسلتين معاً و هي مثل أداء اللابط (||).

Select CONCAT('GOOD', 'BY') from dual;

دالة SUBSTR: تستخدم لقطع جزء من عمود أو سلسلة تبدأ من الحرف رقم M و عدد الحروف المقطوعة N.

Select SUBSTR('GOOD BY', 2,3) from dual;

دالة LENGTH: تستخدم لإيجاد عدد حروف السلسلة أو العمود.

Select LENGTH('GOOD') from dual;

دالة INSTR: تستخدم للبحث وتحديد مكان الحرف المطلوب داخل العمود او السلسة.

Select INSTR('GOOD', 'D') from dual;

دالة LPAD: تستخدم لضبط أو إضافة بيانات لعمود أو سلسلة من ناحية اليمين و ذلك بملئ حرف معين من اليسار.

Select LPAD('AHMED',10, '*') from dual;

دالة RPAD: تستخدم لضبط أو إضافة بيانات لعمود أو سلسلة من ناحية اليسار و ذلك بملئ حرف معين من اليمين.

Select RPAD('AHMED',10 '*') from dual;

دالة TRIM: تستخدم لقطع حرف معين من بداية أو نهاية الكلمة فقط.

Select TRIM('S' FROM 'SAMI') from dual;

دالة DUAL: هي عبارة عن جدول وهمي موجود داخل لغة SQL يستخدم لإجرء العمليات التي لا يدخل فيها أي جدول من داخل قاعدة البيانات.

SELECT LOWER(ename) , UPPER(job) , INITCAP(job) , CONCAT(ename, job)
FROM emp
WHERE sal=3000 ;

هذا المثال يقوم بعرض أسماء الموظفين بأحرف صغيرة بإستخدام الدالة LOWER و عرض الوظيفة بحروف كبيرة بإستخدام الدالة UPPER.

و عرض الحرف الأول كبير و باقي الأحرف صغيرة في حقل الوظيفة بإستخدام الدالة INITCAP.

ثم تقوم الدالة CONCAT بدمج سلسلتين نصيتين إسم الموظف مع الوظيفة ليبدو و كأنة حقل واحد من جدول الموظفين FROM emp.

الدوال الرقمية Number Functions

هي عبارة عن دوال تعمل مع البيانات الرقمية و تكون نتيجتها رقماً فقط, و لها بعض القيم و هي:

دالة ROUND: تستخدم لقص عدد معين من الجزء العشري مع تقريب الأعداد إلى أقرب عدد عشري. أو إلى عدد صحيح و n يبين عدد الأرقام بعد العلامة العشرية و توجد حالات للحرف n.

• إذا كان (n=0) فإن التقريب يكون إلى اقرب عدد صحيح.
• إذا كان (n<0) يكون عدد سالب فإن التقريب يكون قبل العلامة العشرية (الجزء الصحيح).
• إذا كان (n>0) يكون عدد موجب فإن التقريب يكون بعد العلامة العشرية (الجزء العشري).

دالة TRUNC: تستخدم لقص جزء معين من العدد العشري بدون تقريب ولها حالات.

1. إذا كان (n=0) فإنة يتم قص الجزء العشري كله ويكون الناتج عدد صحيح.
2. إذا كان(n>0) يكون عدد موجب فإن القص يكون في الجزء بعد العلامة العشرية (الجزء العشري).
3. إذا كان (n<0) يكون عدد سالب فإن القص يكون قبل العلامة العشرية (الجزء الصحيح).

دالة MOD: تستخدم لإيجاد باقي قسمة العدد m على العدد n. 

SQL> SELECT TRUNC(45.923,0),TRUNC(45.923,2),TRUNC(45.923, 1),TRUNC(45.923,-2)
  2  FROM dual ;

المثال يوضح تأثير الدالة الرقمية ROUND على الأرقام و نجد أنه عندما كان n تساوي صفر تم قص الجزء العشري.

• عندما كانت n تساوي 2 فإنه تم قص العدد العشري ليصبح رقمين فقط بعد العلامة العشرية.
• عندما كان n تساوي 1- تم قص العدد 5 من الجزء قبل العلامة العشرية فكانت النتيجة 40.
• عندما كانت n تساوي 2- تم قص العددين الصحيحين فأصبح صفر.

دوال التاريخ Date Functions

هي عبارة عن دوال تتعامل مع البيانات من نوع تواريخ date, علماً بأن اوراكل قامت بتخزين التاريخ. بالشكل التالي "DD-MON-YY".

و يتوفر عدة دوال فرعية للتعامل مع التاريخ و هي :

دالة SYSDATE: تستخدم هذه الدالة لعرض تاريخ النظام في الجهاز الحالي.

SELECT SYSDATE FROM DUAL;

دالة MONTHS_BETWEEN: تستخدم لإيجاد عدد الأشهر بين تاريخين.

MONTHS_BETWEEN('01-SEP-95' , '11-JAN-94')

دالة ADD_MONTHS: تستخدم لإضافة عدد معين من الأشهر على تاريخ موجود.

ADD_MONTHS('11-JAN-94' , 6)

دالة NEXT_DAY: تستخدم لإيجاد تاريخ يوم معين بعد اليوم الحالي.

NEXT_DAY('01-SEP-95' , 'FRIDAY')

دالة LAST_DAY: تستخدم هذه الدالة لإيجاد آخر يوم في الشهر الحالي.

LAST_DAY('01-SEP-95')

دالة ROUND: تستخدم لتقريب التاريخ لأقرب شهر أو سنه.

ROUND('25-JUL-95' , 'MONTH')

دالة TRUNC: تستخدم لقص التاريخ لأقرب شهر أو سنه.

TRUNC('25-JUL-95' , 'MONTH')

دوال التحويل Conversion Functions

هي عبارة عن دوال تقوم بتحويل البيانات من نوع إلى نوع آخر و هي:

دالة TO_CHAR: تستخدم هذه الدالة لتحويل البيانات من نوع رقمي أو تاريخ إلى بيانات حرفية بشكل معين "FORMAT" حسب الطلب fmt.

SQL> SELECT sysdate,TO_CHAR(sysdate,'DD/MM/YYYY') 
 2 FROM dual ;

دالة TO_DATE: تستخدم لتحويل البيانات الحرفية إلى بيانات من نوع تاريخ بشكل معين "FORMAT" حسب الطلب fmt.

SQL> SELECT TO_DATE( 'FEBRUARY 22, 1981' , 'MONTH DD, YYYY' )
 2 FROM dual

دالة TO_NUMBER: تستخدم لتحويل البيانات الحرفية إلى بيانات رقمية بشكل معين "FORMAT" حسب الطلب fmt.

SQL> SELECT TO_NUMBER( HIREDATE ,'DD ,MONTH,YY' )
 2 FROM EMP
3 where sal>2000;

 

تمرين 1

عرض أسماء الموظفين ثم قطع جزء من الأسماء و عرضها في حقل آخر بإستخدام دالة (SUBSTR(ENAME,2,3F). إبتداءً من الحرف رقم 2 بمسافة "3" أحرف, ثم عرض عدد أحرف أسماء الموظفين. بإستخدام الدالة (LENGTH), ثم عرض ترتيب الحرف 'K' إبتداءً من اليسار مع إستخدام دالة WHERE لتفادي الخطأ.

SQL> SELECT ename,
 2 SUBSTR(ename,2,3),LENGTH(ename),INSTR(ename,'K') 
 3 FROM emp 
 4 WHERE sal>1000 ;

تمرين 2

عرض إسم الموظف و راتبه و عمولته و باقي قسمة راتبه على ما يأخذ من عمولة.

SQL> SELECT ename , sal , comm , MOD(sal,comm) 
2 FROM emp 
3 WHERE sal=1600 ;

تمرين 3

عرض أسماء الموظفين ثم عرض الأسماء بعد قص حرف 'S' بإستخدام الدالة (TRIM) من بداية الأسم أو نهايتة. ثم عرض أسماء الموظفين و إضافة بعض الرموز (*) من بداية الأسم, بحيث يكون طول الأسم عشرة. أحرف بإستخدام الدالة (LPAD), ثم عرض أسماء الموظفين مع إضافة بعض الرموز (#) من نهاية الأسم, ليصبح طول الإسم عشرة أحرف بإستخدام الدالة (RPAD).

SQL>SELECT ename,TRIM('S' FROM ename), LPAD(ename,10,'*') , RPAD(ename,10,'#')
 2 FROM emp 
 3 WHERE sal>2500 ;

تمرين 4

عرض إسم الموظف و رقم الموظف و الراتب و تحويل الراتب إلى قيمة حرفية بحيث تكون صيغة الراتب على الشكل التالي ('sal , '$99,999).

SQL> SELECT empno, ename,sal, TO_CHAR(sal , '$99,999') salary
2 FROM emp 
3 WHERE sal > 2500 ;

ملاحظات عن '99,999$'

• الرقم 9: عند تكرار هذا الرقم فإنه يمثل عدد الخانات التي تظهر عندما نكتب (99) يعني ظهور رقمين.
• الرقم 099: يعني ظهور رقم و قبله صفر.
• الرقم 990: يعني ظهور صفر إذا كانت القيمة معدومة.
• الرقم $99: ظهور علامة الدولار قبل الرقم و تكون العلامة العشرية بعد رقمين.
• الفاصلة .: إظهار العلامة العشرية.
• الفاصلة الألفية ,: إظهار الفاصلة بين كل ثلاث ارقام.
• الـ M1: إظهار علامة السالب إذا كانت القيمة سالبة.
  

الدوال التجميعية Aggregate Functions

هي عبارة عن دوال تستخدم لأكثر من صف في جملة الإستعلام SELECT لإخراج قيمة واحدة. بعكس دوال الصف الواحد التي تتعامل مع صف واحد لإخراج قيمة واحدة.

الدوال التجمعية لأكثر من صف

هي الدوال التي تتعامل مع بيانات مجموعة من الصفوف لإخراج قيمة واحدة فقط مثل عمل جمع. لرواتب الموظفين او معرفة اكثر راتب يتم صرفة للموظف و كذلك أقل راتب يتم صرفه.

 

أنواع الدوال التجمعية

1. SUM: تُستخدم لإيجاد مجموع عدد من القيم.
2. MAX: تُستخدم لإيجاد اكبر قيمة من بين مجموع قيم.
3. MIN: تُستخدم لإيجاد اقل قيمة من بين مجموع قيم.
4. AVG: تُستخدم لإيجاد المتوسط الحسابي لمجموعة من القيم.
5. COUNT: تُستخدم لإيجاد عدد القيم او عدد الصفوف و كذلك تتجاهل القيم الفارغة NULL.
6. STDDEV DEVIATION: تُستخدم لإيجاد الإنحراف المعياري لمجموع من القيم.
7. VARIANCE: تستخدم لإيجاد مقدار التباين لمجموعة من القيم.
8. NVL: تستخدم لحل القيم الفارغة الموجودة في أي عمود لأن الدوال التجميعية تتجاهل القيم الفارغة.

 

مثال 1

عرض مجموع رواتب الموظفين, اكبر راتب و اقل راتب و المتوسط الحسابي للرواتب.

SQL> SELECT SUM(sal) , MAX(sal) , MIN(sal) , AVG(sal)
 2 FROM emp ;

كل من الدالتين MAX and MIN تتعامل مع جميع البيانات عند إستخدامها مع البيانات الحرفية و تكون النتيجة على حسب الترتيب الأبجدي

 

مثال 2

عرض اول الأسماء الأبجدية و آخر الأسماء.

SQL> SELECT MAX(ename) , MIN(ename) 
 2 FROM emp ;

مثال 3

عرض المتوسط الحسابي لمكافئات الموظفين.

SQL> SELECT AVG( NVL(comm , 0) )
 2 FROM emp ;

لقد تم إستخدام دالة NVL لكي يتم قسمة مجموع المكافئات على جميع الموظفين. بما في ذلك القيم الفارغة يعني يتم القسمة على 14 موظف, أما إذا تم إستخدام.

SQL> SELECT AVG( comm )
 2 FROM emp ;

فإنه سوف يتم القسمة على حقول الموظفين الذين يستلمون المكافئات فقط. و سوف يتم تجاهل القيم الفارغة و سوف يكون الناتج يساوي 550.

التعامل مع دالة COUNT

الدالة ((*)COUNT): تُستخدم لعد جميع الصفوف داخل الجدول بما في ذلك الصفوف المكررة. و الصفوف التي تحتوي على قيم فارغة NULL و إذا. كانت جملة الشرط where تحتوي على شرط فإنها تقوم بِعَد الصفوف على حسب جملة الشرط.

الدالة (COUNT(column)): تُستخدم لعد قيم أو بيانات عمود معين بما في ذلك البيانات المكررة مع تجاهل القيم الفارغة فقط NULL.

مثال

عرض إجمالي عدد الصفوف داخل جدول الموظفين و عدد الموظفين الذين يستلمون المكافئات و عدد الإدارات داخل عمود( deptno).

SQL> SELECT COUNT(*),COUNT(comm), COUNT(deptno) 
 2 FROM emp ; 

دالة Group By في أوراكل

هي عبارة عن دالة شرط تقوم بتقسيم البيانات إلى مجموعات على حسب عمود معين أو اكثر و قد يكن ذلك العمود مفتاح ثانوي في عمود آخر.

SQL >SELECT deptno , MAX(sal)
 2 FROM emp
 3 GROUP BY deptno ;

لقد تم تقسيم الموظفين إلى مجموعات على حسب رقم الإدارة و كان اكبر راتب في الإدارة رقم عشرة و هو 5000.

و اكبر راتب في الإدارة رقم عشرين و هو 3000 و اكبر راتب في الإدارة رقم ثلاثين. و هو 2850 و تم إستخدام دالة الشرط Group By لوجود دالة التجميع Max.

SQL> SELECT deptno , sal FROM emp;

إستخدام الدوال التجميعية

1. عند كتابة اي عمود داخل قائمة Select لا بد من كتابته مع جزء Group By و ذلك لأن الدوال التجميعية تتعامل مع عدة صفوف.
2. يمكن إستخدام جزء ORDER BY لترتيب الصفوف مع التجميعية كما هو مبين في المثال السابق.
3. لا يمكن إستخدام الدوال التجميعية في الجزء Where و لكن نستخدم الجزء Having بدل منها.

دالة الشرط ORDER BY للترتيب Oracle

تستخدم ORDER BY لترتيب الصفوف مع الدوال التجميعية على حسب كل صف مثل إيجاد المتوسط الحسابي لكل إدارة على حدة.

دالة HAVING لعمل شروط على الدوال

هي عبارة عن دالة تستخدم لعمل شرط معين على الدوال التجميعية بدل من Where.

 

مثال 1

عرض المتوسط الحسابي لمرتبات الموظفين في كل إدارة كما تم ترتيب المخرجات تصاعدياً حسب المتوسط.

SQL> SELECT deptno , AVG(sal) 
 2 FROM emp
 3 GROUP BY deptno 
 4 ORDER BY AVG(sal) ;

 

عند كتابة العمود (deptno) ضمن قائمة Select و لم نكتبه ضمن جزء Group By سوف تظهر رسالة خطأ إذا لا بد عند كتابة العمود (deptno) أن نكتب العمود (deptno) في جزء Group By. 

عند إستخدام الدوال التجميعية داخل دالة الشرط Where تظهر رسالة خطأ و بدل من ذلك نستخدم جملة Having.

SQL> SELECT deptno , AVG(sal)
2 FROM emp
3 WHERE AVG(sal) > 2000
4 GROUP BY deptno ;
ERROR at line 3 :
ORA-00934: group function is not allowed herev  رسالة الخطأ

 

مثال 2

عرض المتوسط الحسابي لمرتبات الموظفين في كل إدارة بشرط أن تكون المتوسطات الحسابية. للمرتبات اكبر من 2000 سوف نستخدم دالة الشرط Having.

SQL> SELECT deptno , AVG(sal)
2 FROM emp 
3 GROUP BY deptno
4 HAVING AVG(sal) > 2000 ;