إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

Select And case statement


2 رد (ردود) على هذا الموضوع

#1 ammartalal2005

ammartalal2005

    مشترك

  • الأعضــاء
  • 63 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 27 March 2006 - 01:58 AM

أصدقائي الأعزاء
أرجو الأجابة عن هذا السؤال أريد مثالا أستخدام Select بداخلها Case Statement ?

شاكرا لكم جهودكم .........

#2 أسامة موسى

أسامة موسى

    مشرف سابق وعضو مميز

  • المجموعة الماسية
  • 2,381 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 27 March 2006 - 09:53 AM

هذا مثال مفصل

In Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:

CASE expression
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result END

expression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.



Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.

You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.



For Example:

You could use the case statement in an SQL statement as follows:

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value' END
from all_tables;



The above case statement is equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
     result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN
    result := 'The owner is SYSTEM'';

ELSE
    result := 'The owner is another value';

END IF;



The case statement will compare each owner value, one by one.



One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END
from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

۩ اللهـــــــــــــــــــــــم صلي على سيدنا محمد وعلـى آله وصحبه أجمـعين ۩
۩ سبحـــــــــــــــــان الله وبحمده ....... سبحــــــــــــــــــــــــان الله العظيم ۩



يمكنك زيارتي على صفحتي الخاصة بالفيس بوك على الرابط AskOsama

يمكنك زيارتي على مدونتي الجديدة على الرابط http://askossama.blogspot.com

#3 ammartalal2005

ammartalal2005

    مشترك

  • الأعضــاء
  • 63 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 27 March 2006 - 11:36 PM

مشكور اخي على هذا الرد الوافي
:) :) :) :) :) :)