التحكم و اتخاذ القرارات في اللغة T-SQL

السلام عليكم ورحمة الله وبركاته :

كلمات مفتاحية قبل البدء ..

تعريف كتلة من التعابير A Statement Block BEGIN … END

الخروج من حلقة التكرار While الداخلية BREAK

إعادة تنفيذ حلقة التكرار While CONTINUE

متابعة التنفيذ عند التعليمة التالية للعلامة التي اسمها label GOTO label

لتعريف التنفيذ الشرطي (أي تنفيذ كتلة تعليمات عند تحقق شرط منطقي ما) والتنفيذ الاختياري لكتلة أخرى

عند عدم تحقق الشرط IF … ELSE

الخروج غير المشروط RETURN

انتظار مؤقت للتنفيذ WAITFOR

تنفيذ مجموعة من التعليمات طالما أن شرطاً محدداً ما زال محققاً WHILE تعيد تعبيراً واحداً

من مجموعة من التعابير بناء على شرط مقارنة CASE

مقدمة:

في الكثير من الأحيان نجد أنه من الضروري ألا يتم تنفيذ استعلام ما ضمن إجرائية مخزنة إلا عند تحقق

شرط معين كإدخال معامل بقيمة صحيحة مثلاً. وقد يكون أحد السيناريوهات المحتملة أيضاً الرغبة في عدم تنفيذ استعلام ما لم

يعِد استعلام سابق نتيجة متوقعة. أو أن نحتاج إلى تنفيذ استعلامات مختلفة بحسب قيمة معامل محدد مثلاً .

تحتوي أغلب أنظمة قواعد البيانات العلائقية على نوعين أساسين من التعبيرات الشرطية:

• التعبير IF………..ELSE

• التعبير CASE………….WHEN

كما نحتاج في الكثير من الأحيان إلى تكرار مجموعة من التعليمات بحسب شرط أو شروط معينة.

ويتم ذلك باستخدام التعبير WHILE.

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

1. الحلقة الشرطية IF…ELSE

وتستخدم لتنفيذ كتلة تعليمات بناءً على تحقق شرط منطقي ما.

IF Boolean_expression 
	{ sql_statement | statement_block } 
[ ELSE 
	{ sql_statement | statement_block } ]

ينفذ الجزء ELSE في حال كان الشرط المنطقي Boolean_expression غير محقق (أي يعطي قيمة منطقية FALSE).

وهذا الجزء اختياري وليس بالضرورة موجود.

2. التعليمة CASE

تعيد إحدى القيم الممكنة من مجموعة قيم وذلك بناء على شرط ما.

لهذه التعليمة نمطين:

• النمط البسيط Simple CASE: وفيه تتم مقارنة تعبير ما مع مجموعة من التعابير المحتملة لتحديد النتيجة.

• نمط الاكتشاف Searched CASE: وفيه يتم اختبار مجموعة من الشروط المنطقية لتحديد النتيجة.

يمكن تضمين جزء ELSE في كلا النمطين.

النمط البسيط:

CASE input_expression 
	WHEN when_expression THEN result_expression 
		[ ...n ] 
	[ 
		ELSE else_result_expression 
	] 
END 
نمط الاكتشاف:
CASE
	WHEN Boolean_expression THEN result_expression 
		[ ...n ] 
	[ 
		ELSE else_result_expression 
	] 
END

input_expression :

وهو التعبير المراد مقارنته مع مجموعة القيم الموجودة في النمط البسيط (تعبير المقارنة)

WHEN when_expression :

وهو تعبير بسيط لمقارنة قيمته مع قيمة تعبير المقارنة. نمط هذا التعبير ونمط تعبير المقارنة يجب أن يتطابقا أو أن يكون

هناك تحويل ممكن مضمن بينهما.

THEN result_expression :

وهو ناتج التعليمة في حال طابق تعبير المقارنة التعبير البسيط المحدد في فقرة WHEN الموافقة.

n : للدلالة على أن الجزء WHEN…THEN يمكن أن تتكرر أكثر من مرة.

ELSE else_result_expression :

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

إذا لم يتم تحديد هذا الجزء وكانت قيمة المقارنة مغايرة لجميع الأنماط البسيطة المحددة فإن ناتج التعليمة هو NULL.

يتم تطبيق التراكيب IF…ELSE و CASE…WHEN في Oracle و DB2 و MySQL بصورة مشابهة لتلك المستخدمة في

SQL Server مع الانتباه إلى الاختلافات التالية:

• في Oracle وDB2 و MySQL يمكنك استخدام ELSE…IF في التركيب IF…ELSE لتقييم شروط إضافية.

• في Oracle و DB2 و MySQL يمكن لـ CASE…WHEN أن تستخدم بصورة منفصلة

وليست بحاجة لأن تكون جزءاً من عبارة تعيين قيمة لمتغير أو جزءاً من عبارة

SELECT كما هي الحال في SQL Server.

• يتم إغلاق التركيب CASE…WHEN بالتعبير END CASE وليس END كما هي الحال في SQL Server.

• بالطبع لن نغفل الاختلافات المذكورة مسبقاً كضرورة تحويل إشارات المساواة إلى إشارات := في Oracle

عوضاً عن استخدام SET.

3. حلقة التكرار WHILE

وتستخدم لتنفيذ تعليمة SQL أو كتلة تعليمات طالما أن شرطاً منطقياً مازال محققاً.

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

• ففي SQL Server يستخدم التعبير WHILE مع BEGIN وEND.

• أما في Oracle فيُستخدم لتحديد بداية جسم الحلقة التعبير LOOP ولنهايتها END LOOP.

• وفي DB2 نستخدم التعبير DO لتحديد جسم الحلقة، والتعبير END WHILE لتحديد نهايتها.

• وفي MySQL يُحدد جسم الحلقة بالتعبير WHILE ونهايتها بالتعبير END WHILE.

WHILE Boolean_expression :

{ sql_statement | statement_block }

[ BREAK ]

{ sql_statement | statement_block }

[ CONTINUE ]

Boolean_expression:

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

وضع تعليمة الانتقاء بين أقواس.

{ sql_statement | statement_block }

ويمكن أن تتضمن أي تعليمة SQL أو كتلة تعليمات SQL.

كتلة تعليمات هي مجموعة من التعليمات الموضوعة بين BEGIN و END.

BREAK:

وتؤدي إلى الخروج من تعليمة WHILE الداخلية (في حالة وجود أكثر من حلقة WHILE منسوجة ضمن بعضها البعض).

الكلمة المفتاحية END تحدد نهاية الحلقة.

CONTINUE:

وتؤدي إلى العودة إلى بداية الحلقة مجدداً وإهمال كافة التعليمات اللاحقة لها.

4. التعليمة WAITFOR

وتستخدم لإيقاف تنفيذ التعليمة التالية له مدة محددة من الزمن أو بدء التنفيذ في زمن محدد.

WAITFOR { DELAY 'time' | TIME 'time' }

DELAY : تنفيذ التعليمة التالية بعد مرور الزمن المحدد بالقيمة time (هذا المتحول يجب أن يكون من النمط DateTime).

TIME : تنفيذ التعليمة التالية بحلول التوقيت المحدد بالقيمة time ( وهو متحول يجب أن يكون من النمط DateTime).

5. أمثلة

مثال 1:

يقوم المثال التالي بتحديد كون وسطي أسعار الكتب أكبر أو أصغر من 15$

USE pubs
IF 
	(SELECT AVG(price) FROM titles ) < $15
BEGIN
	PRINT 'Average title price is less than $15.'
	PRINT 'Another Statement in the Block '
END
ELSE
	PRINT 'Average title price is more than $15.'

مثال 2:

نعيد نفس المثال السابق لكن مع الحالات أكبر من، أصغر من، ويساوي 15$.

IF (SELECT AVG(price) FROM titles ) < $15
	PRINT 'Average title price is less than $15.'
ELSE
	IF (SELECT AVG(price) FROM titles ) > $15
   		PRINT 'Average title price is more than $15.'
	ELSE
		PRINT 'Average title price is equal to $15.'

مثال 3:

نريد طباعة بيانات عن الكتب بحسب مجالات السعر: أصغر من 10، بين 10 و 20، وأكبر من $20 مع رسالة

توضيحية تبين مجال السعر.

Use Pubs
Go
Declare @msg VarChar(50)
If (Select Count(*) From Titles Where Price<10) >0
Begin
	Set @msg = 'There are several books with prices less than $10. These Books are:'
	Print @msg
	Select title From titles where price<10
End

If (Select Count(*) From Titles Where Price Between 10 and 20) >0
Begin
	Set @msg = 'There are several books with bearable prices. These Books are:'
	Print @msg
	Select title From titles Where price between 10 and 20
End
Else
Begin
	Set @msg = 'Many books are very expensive. These Books are:'
	Print @msg
	Select title From titles Where price > 20
End

مثال 4:

نريد أن نضاعف أسعار جميع الكتب طالما أن وسطي السعر أقل من $30 وأعلى سعر لا يتجاوز$50.

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
	UPDATE titles
	SET price = price * 2
	SELECT MAX(price) FROM titles
	IF (SELECT MAX(price) FROM titles) > $50
		BREAK
	ELSE
		CONTINUE
END

مثال 5

يظهر المثال التالي كيفية استخدام تعليمة case من أجل تعديل القيمة الراجعة من حقل. تظهر التعليمة صنف،

عنوان، سعر الكتب، مع وسطي السعر للكتب حسب النوع ومجموع سعر كل نوع من أنواع الكتب.

USE pubs
GO
SELECT   Category = 
	CASE type
		WHEN 'popular_comp' THEN 'Popular Computing'
		WHEN 'mod_cook' THEN 'Modern Cooking'
		WHEN 'business' THEN 'Business'
		WHEN 'psychology' THEN 'Psychology'
		WHEN 'trad_cook' THEN 'Traditional Cooking'
		ELSE 'Not yet categorized'
	END,
	CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY type
COMPUTE SUM(price) BY type
Category			Shortened Title		   Price				 
------------------- ------------------------- --------------------- 
Business			The Busy Executive's Data 19.9900
Business			Cooking with Computers: S 11.9500
Business			You Can Combat Computer S 2.9900
Business			Straight Talk About Compu 19.9900

											  avg
											  =====================
											  13.7300


											  sum
											  =====================
											  54.9200


Category			Shortened Title		   Price				 
------------------- ------------------------- --------------------- 
Modern Cooking	  Silicon Valley Gastronomi 19.9900
Modern Cooking	  The Gourmet Microwave	 2.9900

											  avg
											  =====================
											  11.4900


											  sum
											  =====================
											  22.9800


Category			Shortened Title		   Price				 
------------------- ------------------------- --------------------- 
Popular Computing   But Is It User Friendly?  22.9500
Popular Computing   Secrets of Silicon Valley 20.0000

											  avg
											  =====================
											  21.4750


											  sum
											  =====================
											  42.9500


Category			Shortened Title		   Price				 
------------------- ------------------------- --------------------- 
Psychology		  Computer Phobic AND Non-P 21.5900
Psychology		  Is Anger the Enemy?	   10.9500
Psychology		  Life Without Fear		 7.0000
Psychology		  Prolonged Data Deprivatio 19.9900
Psychology		  Emotional Security: A New 7.9900

											  avg
											  =====================
											  13.5040


											  sum
											  =====================
											  67.5200


Category			Shortened Title		   Price				 
------------------- ------------------------- --------------------- 
Traditional Cooking Onions, Leeks, and Garlic 20.9500
Traditional Cooking Fifty Years in Buckingham 11.9500
Traditional Cooking Sushi, Anyone?			14.9900

											  avg
											  =====================
											  15.9633


											  sum
											  =====================
											  47.8900


(26 row(s) affected)

مثال 6

يمكننا نمط الاستكشاف لتعليمة case من استبدال قيم حقل في النتيجة النهائية.

اكتب تعليمة استعلام عنوان الكتاب، تعيد من أجل كل كتاب "very Reasonable" إذا كان سعر الكتاب أقل من عشرة، "Coffee

Table Title" إذا كان سعر الكتاب بين 10 و 20 ( أصغر تماماً من 20) ، "not yet priced" إذا كان السعر null.

و إلا تعيد "Expensive Book".

USE pubs
GO
SELECT	'Price Category' = 
	  CASE 
		 WHEN price IS NULL THEN 'Not yet priced'
		 WHEN price < 10 THEN 'Very Reasonable Title'
		 WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
		 ELSE 'Expensive book!'
	  END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price

مثال 7

تقوم التعليمة التالية بإيقاف التنفيذ لمدة ثانيتين ثم تقوم بعرض قائمة بأرقام الموظفين في قاعدة البيانات Northwind.

WAITFOR DELAY '00:00:02'

SELECT EmployeeID FROM Northwind.dbo.Employees

مثال 8

عند الساعة العاشرة والنصف و59 ثانية ليلاً أنشئ تقريراً بأرقام وأسماء الموظفين من قاعدة البيانات Northwind.

USE northwind
go
BEGIN
	WAITFOR TIME '22:30:59'
	SELECT 
		employeeId,
		firstName +  ' '  + lastname as ' full name'
	FROM 
		employees
	ORDER BY employeeid
END

المراجع : منهاج الجامعة الافتراضية السورية ..