1. No category

‫‪SQL‬‬
‫‪Q Server 2000 Admin‬‬
‫‪Creating and Managing‬‬
‫‪Views‬‬
‫‪Stored Procedures‬‬
‫‪Triggers‬‬
‫‪User Defined Functions‬‬
‫‪z‬‬
‫‪z‬‬
‫‪z‬‬
‫‪z‬‬
‫ﻣﻘﺪﻣﺔ‬
‫ﺡ‬
‫ﻭﺍﻟﻘﻮﺍﺩﺡ‬
‫{ ﺍﳌﻨﺎﻇﲑ ﻭﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰﻧﺔ‬
‫ﻭﺗﻮﺍﺑﻊ ﺍﳌﺴﺘﺨﺪﻡ ﻫﻲ ﺃﺃﻏﺮﺍﺽ ‪Objects‬‬
‫ﺗﻘﺪﻣﻬﺎ ﻟﻐﺔ ‪ Transact-SQL‬ﻟﺮﻓﻊ ﻣﺴﺘﻮﻯ‬
‫ﺃﺩﺍﺀ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ ﻭﺗﺒﺴﻴﻂ ﻣﻬﺎﻡ‬
‫ﺍﻟﺘﻄﻮﻳﺮ‬
‫ﺽ ﰲ ﻗﺎﻋﺪﺓ‬
‫ﺍﻷﻏﺮﺍﺽ‬
‫ﻴﻊ ﻫﺬﻩ ﺮ‬
‫ُﺨﺰﻥ ﲨﻴﻊ‬
‫{ﺗ ﺰ‬
‫ﺍﳌﻌﻄﻴﺎﺕ‬
‫{ ﳝﻜﻦ ﺇﻧﺸﺎﺀﻫﺎ ﺑﺎﺳﺘﺨﺪﺍﻡ ‪SQL Query‬‬
‫‪ Analyzer‬ﺃﻭ ‪Enterprise Manager‬‬
‫‪1‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﳌﻨﺎﻇﲑ ‪Views‬‬
‫{ ﻳُﻌﺮﻑ ﺍﳌﻨﻈﺎﺭ ﻋﻠﻰ ﺃﻧﻪ ﺍﺳﺘﻌﻼﻡ ﻣُﺨﺰّﻥ ﻛﻐﺮﺽ ﰲ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ‬
‫{ ﳝﻜﻦ ﻭﺻﻒ ﺍﳌﻨﻈﺎﺭ ﺑﺄﻧﻪ ﺟﺪﻭﻝ ﺍﻓﺘﺮﺍﺿﻲ‪ ،‬ﻭﺫﻟﻚ ﻟﻠﺘﺸﺎﺑﻪ ﺍﻟﻜﺒﲑ ﺑﻴﻨﻪ‬
‫ﻭﺑﲔ ﺍﳉﺪﺍﻭﻝ ﻣﻦ ﺣﻴﺚ ﺍﻷﺩﺍﺀ‪ ،‬ﻟﺬﻟﻚ‪ ،‬ﻏﺎﻟﺒﹰﺎ ﻣﺎ ﻳُﺸﺎﺭ ﻟﻠﻤﻨﺎﻇﲑ ﰲ‬
‫ﻋﺒﺎﺭﺍﺕ ‪ T-SQL‬ﻋﻠﻰ ﺃﻬﻧﺎ ﺟﺪﺍﻭﻝ ﻣﻊ ﺑﻌﺾ ﺍﻻﺳﺘﺜﻨﺎﺀﺍﺕ‬
‫{ ﺗُﺴﺘﺨﺪﻡ ﺍﳌﻨﺎﻇﲑ ﻟﻌﺮﺽ ﳎﻤﻮﻋﺔ ﺟﺰﺋﻴﺔ ﻣﻦ ﺍﳌﻌﻄﻴﺎﺕ‪ ،‬ﺃﻭ ﺑﺸﻜﻞ‬
‫ﺃﺩﻕّ‪ ،‬ﻟﻌﺮﺽ ﳎﻤﻮﻋﺔ ﻣﻦ ﺍﻷﺳﻄﺮ ﺃﻭ ﺍﻷﻋﻤﺪﺓ ﺳﻮﺍﺀ ﻛﺎﻧﺖ ﻣﻦ‬
‫ﺍﻟﺒﻌﺾ ﻣﻦ‬
‫ﺑﻌﻀﻬﺎﺎ ﺍﻟ ﺾ‬
‫ﻂ ﻣﻊ ﻀ‬
‫ﺗﺮﺗﺒﻂ‬
‫ﺟﺪﺍﻭﻝﻝ ﺗ ﺗ‬
‫ﻋﺪﺓّﺓ ﺪﺍ‬
‫ﺟﺪﻭﻝﻝ ﻣﻌﲔ ﺃﺃﻭ ﻣﻦ ﺪ‬
‫ﺪ‬
‫ﺧﻼﻝ ﻋﺒﺎﺭﺍﺕ ‪ JOIN‬ﺃﻭ ‪UNION‬‬
‫{ ﺑﺎﺳﺘﺜﻨﺎﺀ ﺍﳌﻨﺎﻇﲑ ﺍﳌﻔﻬﺮﺳﺔ‪ ،‬ﻻ ﺗﺸﻜﻞ ﺍﳌﻨﺎﻇﲑ ﺍﻓﺘﺮﺍﺿﻴﹰﺎ ﻋﺒﺌﹰﺎ ﺇﺿﺎﻓﻴﹰﺎ‬
‫ﻋﻠﻰ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ‪ ،‬ﻭﺫﻟﻚ ﻷﻬﻧﺎ ﻻ ﲣﺰّﻥ ﳏﺘﻮﻳﺎﻬﺗﺎ ﻣﻦ ﺍﳌﻌﻄﻴﺎﺕ‬
‫ﺑﺸﻜﻞ ﻓﻌﻠﻲ‬
‫ﺍﳌﻨﺎﻇﲑ‬
‫ﻣﻨﺎﻇﻴﺮ ﻣﻦ ﺝﺪول واﺣﺪ‬
‫ﻣﻨﻈﺎر ﻣﺘﻌﺪد اﻟﺠﺪاول‬
‫‪2‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﺳﺘﺨﺪﺍﻣﺎﺕ ﺍﳌﻨﺎﻇﲑ‬
‫ﺗﺒﺴﻴﻂ ﻋﻤﻠﻴﺎﺕ ﺍﺳﺘﺮﺟﺎﻉ ﺍﳌﻌﻄﻴﺎﺕ ﰲ ﺍﻻﺳﺘﻌﻼﻣﺎﺕ ﺍﳌﻌﻘﹼﺪﺓ‬
‫{ ﻛﺜﲑﹰﺍ ﻣﺎ ﺗُﺴﺘﺨﺪﻡ ﻋﻤﻠﻴﺎﺕ ﺍﻟﺪﻣﺞ ﺍﳌﻌﻘﺪﺓ ﺃﻭ ﺍﻟﺘﻮﺍﺑﻊ ﺍﻟﺘﺠﻤﻴﻌﻴﺔ ﺃﻭ‬
‫ﺗﻮﺍﺑﻊ ‪ SQL‬ﺍﳌﹸﻀﻤّﻨﺔ ﺃﻭ ﻏﲑﻫﺎ‪ ،‬ﰲ ﻋﻤﻠﻴﺎﺕ ﺍﺳﺘﻌﻼﻡ ﺍﳌﻌﻄﻴﺎﺕ‪،‬‬
‫ﻭﺇﻥ ﺗﻜﺮﺍﺭ ﻭﺗﻮﺍﺗﺮ ﻫﺬﻩ ﺍﻟﻌﻤﻠﻴﺎﺕ ﻋﻠﻰ ﺍﳌﻌﻄﻴﺎﺕ ﻗﺪ ﻳﺆﺩﻱ ﺇﱃ‬
‫ﺯﻳﺎﺩﺓ ﺗﻌﻘﻴﺪ ﺍﻻﺳﺘﻌﻼﻣﺎﺕ ﻣﻊ ﻣﺮﻭﺭ ﺍﻟﻮﻗﺖ‬
‫{ ﳝﻜﻨﻨﺎ ﻣﻦ ﺧﻼﻝ ﺍﳌﻨﺎﻇﲑ ﺃﻥ ﻧﻘﻮﻡ ﺑﺈﺧﻔﺎﺀ ﺍﻟﺘﻌﻘﻴﺪ ﺍﻟﻨﺎﺗﺞ ﻋﻦ ﺗﻠﻚ‬
‫ﺍﻻﺳﺘﻌﻼﻣﺎﺕ ﻣﻦ ﺧﻼﻝ ﲣﺰﻳﻨﻬﺎ ﰲ ﻣﻨﻈﺎﺭ ﻣﻨﺎﺳﺐ ﰒ ﺍﺳﺘﻌﻼﻡ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻣﻦ ﺫﻟﻚ ﺍﳌﻨﻈﺎﺭ ﻣﺒﺎﺷﺮﺓ‬
‫ﺍﺳﺘﺨﺪﺍﻣﺎﺕ ﺍﳌﻨﺎﻇﲑ‬
‫ﲣﺒﺌﺔ ﺑﻨﻴﺔ ﺍﳉﺪﺍﻭﻝ‬
‫ﺍﻟﻔﻌﻠﻴﺔ‪،‬‬
‫ﻭﺍﳉﺪﺍﻭﻝ ﺍﻟﻔ ﻠ ﺔ‬
‫ﺍﻟﺘﻄﺒﻴﻘﺎﺕ ﺍﳉ ﺍ ﻝ‬
‫ﻣﺎ ﺑﲔ ﺍﻟ ﻄ ﻘﺎ‬
‫ﻭﺳﻴﻄﺔ ﺎ‬
‫ﻃﺒﻘﺔ ﻄﺔ‬
‫ﻋﺒﺎﺭﺓ ﻋﻦ ﻃ ﻘﺔ‬
‫ﻟﺘﻜﻮﻥ ﺎ ﺓ‬
‫ﺍﳌﻨﺎﻇﲑ ﻟ ﻜ ﻥ‬
‫ﺍﺳﺘﺨﺪﺍﻡ ﺍﳌ ﺎﻇ‬
‫ﺍ‬
‫ﳝﻜﻦ ﺍ‬
‫{ ﻜ‬
‫ﻣﺎ ﻳﺴﻤﺢ ﺑﺎﻟﺘﺤﻜﻢ ﺑﺎﻟﻮﻟﻮﺝ ﺇﱃ ﺍﳌﻌﻄﻴﺎﺕ ﻛﻤﺎ ﻳﺴﻤﺢ ﺑﻌﺰﻝ ﺍﳌﺴﺘﺨﺪﻣﲔ ﻋﻦ ﺍﻟﺘﻐﲑﺍﺕ ﺍﻟﱵ‬
‫ﺗﻄﺮﺃ ﻋﻠﻰ ﺍﳉﺪﺍﻭﻝ‬
‫{ ﻓﻌﻠﻰ ﺳﺒﻴﻞ ﺍﳌﺜﺎﻝ‪ ،‬ﻟﻨﻔﺘﺮﺽ ﺃﻥ ﺃﺣﺪ ﺍﻟﺘﻄﺒﻴﻘﺎﺕ ﻳﻘﻮﻡ ﺑﺎﺳﺘﺨﺪﺍﻡ ﻣﻌﻄﻴﺎﺕ ﺟﺪﻭﻝ ﻣﻌﲔ‪ .‬ﺇﻥ‬
‫ﺃﻱ ﺗﻐﻴﲑ ﻣﺴﺘﻘﺒﻠﻲ ﻋﻠﻰ ﺍﳉﺪﻭﻝ ﺳﻴﺆﺩﻱ ﺑﺎﻟﻀﺮﻭﺭﺓ ﺇﱃ ﺗﻌﺪﻳﻞ ﺍﻟﺘﻄﺒﻴﻖ ﺍﻟﺬﻱ ﻳﺴﺘﻌﻠﻢ ﺫﻟﻚ‬
‫ﺍﳉﺪﻭﻝ ﻟﻜﻲ ﻳﻮﺍﻓﻖ ﺍﻟﺘﻐﲑﺍﺕ ﺍﳉﺪﻳﺪﺓ‪ .‬ﺃﻣﺎ ﰲ ﺣﺎﻝ ﺍﺳﺘﺨﺪﺍﻡ ﻣﻨﻈﺎﺭ ﻛﻄﺒﻘﺔ ﻭﺳﻴﻄﺔ ﺑﲔ‬
‫ﺍﻟﺘﻄﺒﻴﻖ ﻭﺍﳉﺪﻭﻝ‪ ،‬ﻓﺈﻧﻪ ﻟﻴﺲ ﻣﻦ ﺍﻟﻀﺮﻭﺭﻱ ﺇﺟﺮﺍﺀ ﺃﻱ ﺗﻌﺪﻳﻞ ﻋﻠﻰ ﺍﻟﺘﻄﺒﻴﻖ‪ ،‬ﺇﺫ ﺃﻧﻪ ﻳﻜﻔﻲ‬
‫ﺍﳌﻌﻄﻴﺎﺕ‬
‫ُﻌﻴﺪ ﻴ‬
‫ﺒﻴﻖ‪ .‬ﻓﻤﺎﺩﺍﻡﻡ ﺍﳌﻨﻈﺎﺭﺭ ﻳ ﻴ‬
‫ﳛﺘﺎﺟﻬﺎ ﺫﻟﻚ ﺍﻟﺘﻄﺒﻴﻖ‬
‫ﻬ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﺍﻟﱵ‬
‫ﱵ‬
‫ﺐ ﻴ‬
‫ﻳﻞ ﺍﳌﻨﻈﺎﺭﺭ ﻴﻟﻴﺠﻠﺐ‬
‫ﺗﻌﺪﻳﻞ‬
‫ﺍﳌﻨﺎﺳﺒﺔ‪ ،‬ﺑﺎﻟﺘﺎﱄ ﻻ ﻳﻮﺟﺪ ﺃﻱ ﺍﺭﺗﺒﺎﻁ ﻣﺒﺎﺷﺮ ﺑﲔ ﺍﻟﺘﻄﺒﻴﻖ ﻭﺟﺪﺍﻭﻝ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ‬
‫{ ﺗﱪﺯ ﻛﺬﻟﻚ ﺍﻟﻔﺎﺋﺪﺓ ﻣﻦ ﺍﺳﺘﺨﺪﺍﻡ ﺍﳌﻨﺎﻇﲑ ﰲ ﺍﳉﺪﺍﻭﻝ ﺍﺠﻤﻟﺰﺃﺓ‪ ،‬ﻓﻔﻲ ﺑﻌﺾ ﺃﻧﻮﺍﻉ ﻗﻮﺍﻋﺪ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻳﺘﻢ ﲡﺰﺋﺔ ﺍﳉﺪﺍﻭﻝ ﺍﻟﱵ ﻳﺰﺩﺍﺩ ﺣﺠﻤﻬﺎ ﺑﺸﻜﻞ ﻛﺒﲑ ﺟﺪﹰﺍ ﺇﱃ ﻋﺪّﺓ ﺃﻗﺴﺎﻡ ﻣﻦ ﺃﺟﻞ‬
‫ﲢﺴﲔ ﺍﻷﺩﺍﺀ‪ ،‬ﲝﻴﺚ ﻳﺘﻢ ‪-‬ﺃﺛﻨﺎﺀ ﺑﻨﺎﺀ ﺍﻻﺳﺘﻌﻼﻡ‪ -‬ﲢﺪﻳﺪ ﺃﻱ ﺟﺰﺀ ﻧﺮﻳﺪ ﺍﺧﺘﻴﺎﺭ ﺍﳌﻌﻠﻮﻣﺎﺕ ﻣﻨﻪ‪،‬‬
‫ﺇﻻ ﺃﻧﻪ ﻭﺑﺎﺳﺘﺨﺪﺍﻡ ﺍﳌﻨﺎﻇﲑ ﳝﻜﻨﻨﺎ ﺇﺟﺮﺍﺀ ﻋﻤﻠﻴﺔ ﺍﻻﺳﺘﻌﻼﻡ ﺗﻠﻚ ﻭﻛﺄﻬﻧﺎ ﻣﻦ ﺟﺪﻭﻝ ﻭﺣﻴﺪ‪،‬‬
‫ﻭﺫﻟﻚ ﻣﻦ ﺧﻼﻝ ﻣﺎ ﻳُﻌﺮﻑ ﺑﺎﺳﻢ ﺍﳌﻨﻈﺎﺭ ﺍﳌﹸﺠﺰّﺉ‬
‫‪3‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﺳﺘﺨﺪﺍﻣﺎﺕ ﺍﳌﻨﺎﻇﲑ‬
‫ﺍﻟﺘﺤﻜﻢ ﺑﻌﻤﻠﻴﺎﺕ ﺍﻟﻮﻟﻮﺝ ﺇﱃ ﺍﳌﻌﻄﻴﺎﺕ ﻋﻠﻰ ﻣﺴﺘﻮﻯ ﺍﻷﺳﻄﺮ ﺃﻭ ﺍﻷﻋﻤﺪﺓ‬
‫ﻋﻤﻠﻴﺔ ﺇ ﺍ ﺓ‬
‫ﺇﺩﺍﺭﺓ‬
‫ﻂ ﻠ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻭﺗﺒﺴﻴﻂ‬
‫ﺍﻟﻮﻟﻮﺝ ﺇﱃ ﺍﳌ ﻄ ﺎ‬
‫ﻋﻤﻠﻴﺎﺕ ﺍﻟ ﻟ‬
‫ﳊﺼﺮ ﻠ ﺎ‬
‫ﺃﻳﻀﺎ ﳊ‬
‫ﺍﳌﻨﺎﻇﲑ ﺃ ﺎﹰ‬
‫{ ﺗُُﺴﺘﺨﺪﻡ ﺍﳌ ﺎﻇ‬
‫ﺍﻟﺴﻤﺎﺣﻴﺎﺕ‪ ،‬ﺇﺫ ﺃﻧﻪ ﻟﻴﺲ ﻣﻦ ﺍﻟﻀﺮﻭﺭﻱ ﺗﻮﺻﻴﻒ ﲰﺎﺣﻴﺎﺕ ﺍﻟﻮﻟﻮﺝ ﺇﱃ ﺍﳉﺪﺍﻭﻝ ﺇﺫﺍ ﻣﺎ‬
‫ﲤﺖ ﺗﻠﻚ ﺍﻟﻌﻤﻠﻴﺔ ﻋﻠﻰ ﺍﳌﻨﻈﺎﺭ ﺍﻟﺬﻱ ﻳﺴﺘﺨﺪﻡ ﺗﻠﻚ ﺍﳉﺪﺍﻭﻝ‪ ،‬ﲝﻴﺚ ﳝﻜﻦ ﺇﺳﻨﺎﺩ ﻛﺎﻓﺔ‬
‫ﺃﻧﻮﺍﻉ ﺍﻟﺴﻤﺎﺣﻴﺎﺕ ﻣﻦ ﻣﺴﺘﻮﻯ ﺍﳌﻨﻈﺎﺭ‪ .‬ﳝﻜﻦ ﺗﺼﻨﻴﻒ ﲰﺎﺣﻴﺎﺕ ﺍﻟﻮﻟﻮﺝ ﺑﺎﺳﺘﺨﺪﺍﻡ‬
‫ﺍﳌﻨﺎﻇﲑ ﺇﱃ ﻧﻮﻋﲔ ﺃﺳﺎﺳﻴﲔ‪ ،‬ﳘﺎ‪:‬‬
‫{ ﺍﳊﻤﺎﻳﺔ ﻋﻠﻰ ﻣﺴﺘﻮﻯ ﺍﻷﻋﻤﺪﺓ‪ :‬ﲝﻴﺚ ﳝﻜﻦ ﺍﻟﺴﻤﺎﺡ ﺃﻭ ﻋﺪﻡ ﺍﻟﺴﻤﺎﺡ ﳌﺴﺘﺨﺪﻣﻲ ﺍﳌﻨﻈﺎﺭ‬
‫ﺑﺎﻟﻮﻟﻮﺝ ﺇﱃ ﻣﻌﻄﻴﺎﺕ ﻋﻤﻮﺩ ﺑﺎﻟﻜﺎﻣﻞ‬
‫ﺍﳌﻨﻈﺎﺭ‬
‫ﺍﻟﺴﻤﺎﺡ ﳌﳌﺴﺘﺨﺪﻣﻲ ﺍﳌ ﻈﺎ‬
‫ﺍﻟﺴﻤﺎﺡ ﺃﺃﻭ ﻋﺪﻡ ﺍﻟ ﺎ‬
‫ﳝﻜﻦ ﺍﻟ ﺎ‬
‫ﺍﻷﺳﻄﺮ‪ :‬ﲝﻴﺚ ﻜ‬
‫ﻋﻠﻰ ﻣﺴﺘﻮﻯ ﺍﻷ ﻄ‬
‫ﺍﳊﻤﺎﻳﺔ ﻠ‬
‫{ ﺍﳊ ﺎ‬
‫ﺑﺎﻟﻮﻟﻮﺝ ﺇﱃ ﺃﺳﻄﺮ ﳏﺪﺩﺓ ﻣﻦ ﻋﻤﻮﺩ ﻣﻌﲔ‪ ،‬ﻭﺫﻟﻚ ﺑﺘﻄﺒﻴﻖ ﺷﺮﻁ ﻓﻠﺘﺮﺓ ﻣﻨﺎﺳﺐ ﺑﺎﺳﺘﺨﺪﺍﻡ‬
‫ﺍﻟﻌﺒﺎﺭﺓ ‪ WHERE‬ﻋﻠﻰ ﺗﻠﻚ ﺍﻷﺳﻄﺮ ﺃﺛﻨﺎﺀ ﺗﻌﺮﻳﻒ ﺍﳌﻨﻈﺎﺭ‬
‫ﺍﺳﺘﺨﺪﺍﻣﺎﺕ ﺍﳌﻨﺎﻇﲑ‬
‫ﺗﻌﺪﻳﻞ ﺍﳌﻌﻄﻴﺎﺕ ﻋﱪ ﺍﳌﻨﺎﻇﲑ‬
‫ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﺍﳌﻨﺎﻇﲑ ﰲ ﺗﻌﻠﻴﻤﺎﺕ ‪ Update, Insert, Delete‬ﻛﻤﺎ‬
‫{ ﻜ‬
‫ﻧﺴﺘﺨﺪﻡ ﺍﳉﺪﺍﻭﻝ‪.‬‬
‫{ ﺑﺎﻹﺿﺎﻓﺔ ﻟﻘﻴﻮﺩ ﺍﻟﺘﻌﺪﻳﻞ ﻋﻠﻰ ﺍﳉﺪﺍﻭﻝ ﺗﻮﺟﺪ ﺑﻌﺾ ﺍﻟﻘﻴﻮﺩ ﻋﻠﻰ ﺍﻟﺘﻌﺪﻳﻞ‬
‫ﺑﺎﺳﺘﺨﺪﺍﻡ ﺍﳌﻨﺎﻇﲑ‪:‬‬
‫‪z‬‬
‫‪z‬‬
‫‪z‬‬
‫‪z‬‬
‫‪z‬‬
‫‪4‬‬
‫ﻻ ﳝﻜﻦ ﺗﻌﺪﻳﻞ ﺃﻛﺜﺮ ﻣﻦ ﺟﺪﻭﻝ ﰲ ﺍﳌﻨﻈﺎﺭ‪.‬‬
‫ﻻ ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﺗﻌﻠﻴﻤﺔ ﺍﳊﺬﻑ ﰲ ﻣﻨﻈﺎﺭ ﻣﺘﻌﺪﺩ ﺍﳉﺪﺍﻭﻝ‬
‫ﻻ ﳝﻜﻦ ﺗﻌﺪﻳﻞ ﺍﻷﻋﻤﺪﺓ ﺍﶈﺴﻮﺑﺔ‪.‬‬
‫ﻻ ﳝﻜﻦ ﺗﻌﺪﻳﻞ ﺍﳌﻌﻄﻴﺎﺕ ﰲ ﻣﻨﻈﺎﺭ ﳛﺘﻮﻱ ﺇﺣﺪﻯ ﺍﻟﻌﺒﺎﺭﺍﺕ ‪GROUP BY,‬‬
‫‪DISTINCT, TOP, UNION‬‬
‫ﻗﺪ ﻳﻨﺘﺞ ﻋﻦ ﻋﻤﻠﻴﺎﺕ ﺍﻟﺘﻌﺪﻳﻞ ﻣﺸﺎﻛﻞ ﺗﺘﻌﻠﻖ ﺑﺘﻌﺮﻳﻒ ﺍﳉﺪﺍﻭﻝ ﺍﳌﺸﻜﻠﺔ ﻟﻠﻤﻨﻈﺎﺭ‪.‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺇﻧﺸﺎﺀ ﺍﳌﻨﺎﻇﲑ‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫ﻳﻨﺒﻐﻲ ﺍﺳﺘﺨﺪﺍﻡ ﺍﺳﻢ ﻣﻌﺒّﺮ ﻟﻠﻤﻨﻈﺎﺭ‪ ،‬ﻭﻫﻮ ﳜﻀﻊ ﻟﻘﻮﺍﻋﺪ ﺍﻟﺘﺴﻤﻴﺔ ﺍﳌﻌﺮﻭﻓﺔ‬
‫ﻋﺒﺎﺭﺓﺓ ﻋﻦ ﺎﺗﺎﺑﻊ ﲡﲡﻤﻴﻌﻲ‪ ،‬ﺃﺃﻭ‬
‫ﺗﻜﻮﻥ ﺎ‬
‫ﻠﻚ ﺍﻟﺍﻟﱵ ﻜ‬
‫ﻭﺧﺎﺻﺔ ﺗﻠﻚ‬
‫ﺍﳌﻨﻈﺎﺭ‪ ،‬ﺎ‬
‫ﺃﻋﻤﺪﺓ ﺍﳌ ﻈﺎ‬
‫ﻳﻨﺒﻐﻲ ﺗﺴﻤﻴﺔ ﺃ ﺓ‬
‫ﺍﻷﻋﻤﺪﺓ ﺫﺍﺕ ﺍﻷﲰﺎﺀ ﺍﳌﺘﺸﺎﻬﺑﺔ ﺑﲔ ﺍﳉﺪﺍﻭﻝ ﺍﻟﱵ ﻳﺴﺘﺨﺪﻣﻬﺎ ﺍﳌﻨﻈﺎﺭ‬
‫ﻳُﻔﻀﻞ ﺩﺍﺋﻤﹰﺎ ﻗﺒﻞ ﺇﻧﺸﺎﺀ ﺍﳌﻨﻈﺎﺭ ﺃﻥ ﺗﺘﻢ ﻋﻤﻠﻴﺔ ﺍﺧﺘﺒﺎﺭ ﻟﻼﺳﺘﻌﻼﻡ ﺍﻟﺬﻱ ﻳُﻜﻮّﻥ ﺫﻟﻚ‬
‫ﺍﳌﻨﻈﺎﺭ‬
‫ﻻ ﳝﻜﻦ ﺃﻥ ﲢﺘﻮﻱ ﺗﻌﻠﻴﻤﺔ ‪ CREATE VIEW‬ﻋﻠﻰ ﺃﻱ ﻣﻦ ﺍﻟﻜﻠﻤﺎﺕ ﺍﳌﻔﺘﺎﺣﻴﺔ ﺍﻟﺘﺎﻟﻴﺔ‪:‬‬
‫‪ INTO‬ﻭ‪ COMPUTE‬ﻭ ‪COMPUTE BY.‬‬
‫ﻳﺘﻢ ﺍﺳﺘﺨﺪﺍﻡﻡ ‪ TOP‬ﺃﻳﻀﹰﺎ‬
‫ﻻ ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡﻡ ﺍﻟﻌﺒﺎﺭﺓ ‪ ORDER BY‬ﰲ ﺍﳌﻨﻈﺎﺭ ﻣﺎ ﱂ ﻢ‬
‫ﻻ ﳝﻜﻦ ﺃﻥ ﻳُﺸﲑ ﺍﳌﻨﻈﺎﺭ ﻷﻛﺜﺮ ﻣﻦ ‪ ١٠٢٤‬ﻋﻤﻮﺩﺍﹰ‪ ،‬ﻛﻤﺎ ﻻ ﳝﻜﻦ ﺃﻥ ﻳﺸﲑ ﺇﱃ‬
‫ﺟﺪﺍﻭﻝ ﻣﺆﻗﺘﺔ ﺃﻭ ﻣﺘﺤﻮﻻﺕ ﻣﻦ ﳕﻂ "ﺟﺪﻭﻝ"‬
‫ﻻ ﺑﺪ ﺃﻥ ﻳﺘﻢ ﺇﻧﺸﺎﺀ ﺍﳌﻨﻈﺎﺭ ﺿﻤﻦ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ ﺍﳊﺎﻟﻴّﺔ‪ ،‬ﻣﻊ ﺍﻟﻌﻠﻢ ﺃﻧﻪ ﳝﻜﻦ ﻟﻠﻤﻨﻈﺎﺭ‬
‫ﺃﻥ ﻳُﺸﲑ ﺇﱃ ﻋﺪّﺓ ﺟﺪﺍﻭﻝ ﺃﻭ ﻣﻨﺎﻇﲑ ﰲ ﻗﺎﻋﺪﺓ ﻣﻌﻄﻴﺎﺕ ﺃﺧﺮﻯ ﺃﻭ ﺣﱴ ﻋﻠﻰ ﳐﺪﱢﻡ‬
‫ﺁﺧﺮ )ﰲ ﺣﺎﻝ ﺍﺳﺘﺨﺪﺍﻡ ﺍﺳﺘﻌﻼﻣﺎﺕ ﻣﻮﺯّﻋﺔ(‬
‫ﺇﻧﺸﺎﺀ ﺍﳌﻨﺎﻇﲑ ﺑﺎﺳﺘﺨﺪﺍﻡ ‪T-SQL‬‬
‫] ‪CREATE VIEW [ < database_name > . ] [ < owner > .‬‬
‫] ) ] ‪view_name [ ( column [ ,...n‬‬
‫] ] ‪[ WITH < view_attribute > [ ,...n‬‬
‫‪AS‬‬
‫‪select_statement‬‬
‫] ‪[ WITH CHECK OPTION‬‬
‫=‪< view_attribute > ::‬‬
‫| ‪{ ENCRYPTION‬‬
‫| ‪SCHEMABINDING‬‬
‫} ‪VIEW_METADATA‬‬
‫‪5‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺧﻮﺍﺹ ﺍﳌﻨﻈﺎﺭ ‪View Attributes‬‬
‫‪WITH ENCRYPTION‬‬
‫ﲝﻴﺚ ﻻ‬
‫‪ T-SQL‬ﻴ‬
‫ﺗﻌﻠﻴﻤﺎﺕ ‪Q‬‬
‫ﻣﻦ ﻴ‬
‫ﻮﻯ ﺍﳌﻨﻈﺎﺭﺭ ﻦ‬
‫ﺗﺸﻔﲑ ﳏﺘﻮﻯ‬
‫ﺑﻐﺮﺽ ﲑ‬
‫ﺍﳌﻨﻈﺎﺭ ﻭﻭﺫﻟﻚ ﺑ ﺮﺽ‬
‫ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﻩ ﺍﳋﺎﺻﺔ ﺃﺛﻨﺎﺀ ﺇﺇﻧﺸﺎﺀ ﺭ‬
‫ﻡ‬
‫{ ﳝﳝﻜﻨﻨﺎ‬
‫ﳝﻜﻦ ﺍﺳﺘﻌﺮﺍﺽ ﺃﻭ ﺗﻌﺪﻳﻞ ﺫﻟﻚ ﺍﳌﻨﻈﺎﺭ ﺑﻌﺪ ﺗﺸﻔﲑ ﳏﺘﻮﻳﺎﺗﻪ‬
‫{ ﺗُﺴﺘﺨﺪﻡ ﻫﺬﻩ ﺍﳋﺎﺻﺔ ﻷﻏﺮﺍﺽ ﺍﻹﺩﺍﺭﺓ ﺃﻭ ﻟﻀﻤﺎﻥ ﺍﻷﻣﻦ ﲝﻴﺚ ﻻ ﳝﻜﻦ ﻧﺸﺮ ﳏﺘﻮﻯ ﺍﳌﻨﻈﺎﺭ‪ ،‬ﻣﻊ ﺍﻟﻌﻠﻢ ﺃﻧﻪ ﻣﻦ‬
‫ﺍﻟﻀﺮﻭﺭﻱ ﺍﳊﻔﺎﻅ ﻋﻠﻰ ﻧﺴﺨﺔ ﻣﻦ ﺍﳌﺨﻄﻮﻁ ﺍﻟﺬﻱ ﻳﻌﱪ ﻋﻦ ﺍﳌﻨﻈﺎﺭ ﻗﺒﻞ ﺗﺸﻔﲑﻩ ﻭﺫﻟﻚ ﻣﻦ ﺃﺟﻞ ﺇﻋﺎﺩﺓ ﺇﻧﺸﺎﺋﻪ ﺃﻭ ﺗﻌﺪﻳﻠﻪ‬
‫ﻼ‬
‫ﻣﺴﺘﻘﺒ ﹰ‬
‫‪WITH SCHEMABINDING‬‬
‫{ ﺗﺘﻄﻠﺐ ﻫﺬﻩ ﺍﳋﺎﺻﺔ ﺍﻟﺘﻌﺒﲑ ﻋﻦ ﻛﻞ ﻣﻦ ﺍﺳﻢ ﺍﻟﻐﺮﺽ ﻭﺍﺳﻢ ﻣﺎﻟﻜﻪ ﻟﻜﻞ ﻣﻦ ﺍﻷﻏﺮﺍﺽ ﺍﳌﻜﻮّﻧﺔ ﻟﻠﻤﻨﻈﺎﺭ‬
‫{ ﳝﻨﻊ ﻫﺬﺍ ﺍﳋﹶﻴﺎﺭ ﻛﺎﻓﺔ ﺍﻷﻏﺮﺍﺽ ﺫﺍﺕ ﺍﻻﺭﺗﺒﺎﻁ ﺑﺎﳌﻨﻈﺎﺭ ﻣﻦ ﺃﻥ ﻳﺘﻢ ﺣﺬﻓﻬﺎ ﺃﻭ ﺗﻌﺪﻳﻠﻬﺎ ﺑﻄﺮﻳﻘﺔ ﺗﺆﺛﺮ ﻋﻠﻰ ﺍﳌﻨﻈﺎﺭ‪ ،‬ﻣﺎ ﱂ‬
‫ﻳﺘﻢ ﺣﺬﻑ ﺫﻟﻚ ﺍﳌﻨﻈﺎﺭ ﺃﻭ ﺇﺯﺍﻟﺔ ﺗﻔﻌﻴﻞ ﻫﺬﻩ ﺍﳋﺎﺻّﺔ ﻓﻴﻪ‬
‫‪VIEW_METADATA‬‬
‫{ ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﻩ ﺍﳋﺎﺻﺔ ﳚﻌﻞ ‪ SQL Server‬ﻳﻌﻴﺪ ﻣﻌﻠﻮﻣﺎﺕ ﻟﻠﱪﻧﺎﻣﺞ ﺗﺴﻤﺢ ﳍﺎ ﺑﺘﺤﻘﻴﻖ ﻣﱰﻟﻘﺔ ﻗﺎﺑﻠﺔ ﻟﻠﺘﻌﺪﻳﻞ ﻣﻦ ﺟﻬﺔ‬
‫ﺍﻟﺰﺑﻮﻥ‪.‬‬
‫‪WITH CHECK OPTION‬‬
‫{ ﻋﻠﻰ ﺍﻟﺮﻏﻢ ﻣﻦ ﺃﻧﻪ ﳝﻜﻨﻨﺎ ﺣﺼﺮ ﺍﻷﺳﻄﺮ ﺍﻟﱵ ﻳﻌﻴﺪﻫﺎ ﺍﳌﻨﻈﺎﺭ ﻣﻦ ﺧﻼﻝ ﺍﻟﻌﺒﺎﺭﺓ ‪ WHERE‬ﰲ ﻋﺒﺎﺭﺓ ﺍﻻﺧﺘﻴﺎﺭ ﺍﳌﻜﻮّﻧﺔ‬
‫ﻟﻠﻤﻨﻈﺎﺭ‪ ،‬ﺇﻻ ﺃﻥ ﺫﻟﻚ ﻻ ﳝﻨﻊ ﺍﳌﺴﺘﺨﺪﻡ ﻣﻦ ﺗﻌﺪﻳﻞ ﺃﻭ ﺇﺿﺎﻓﺔ ﺃﺳﻄﺮ ﺟﺪﻳﺪﺓ ﺇﱃ ﺫﻟﻚ ﺍﳌﻨﻈﺎﺭ‪ ،‬ﻭﻗﺪ ﻳﻌﺪﻝ ﺍﳌﺴﺘﺨﺪﻡ‬
‫ﺃﺳﻄﺮﹰﺍ ﻻ ﺗﻈﻬﺮ ﰲ ﺍﳌﻨﻈﺎﺭ‪ ،‬ﻟﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﻩ ﺍﳋﺎﺻﻴﺔ ﳝﻨﻊ ﺍﳌﺴﺘﺨﺪﻡ ﻣﻦ ﺗﻌﺪﻳﻞ ﺃﻭ ﺇﺿﺎﻓﺔ ﺃﺳﻄﺮ ﻻ ﺗﻈﻬﺮ ﰲ‬
‫ﺍﳌﻨﻈﺎﺭ‪.‬‬
‫ﻣﺜﺎﻝ‬
‫‪6‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﳌﻨﺎﻇﲑ ﺍﺠﻤﻟﺰﺃﺓ ‪Partitioned Views‬‬
‫{ ﺗﺴﺘﺨﺪﻡ ﺍﳌﻨﺎﻇﲑ ﺍﺠﻤﻟﺰﺃﺓ ﻟﻠﻮﻟﻮﺝ ﺇﱃ ﺍﳌﻌﻄﻴﺎﺕ ﺍﳌﻘﺴﻤﺔ ﺃﻓﻘﻴﹰﺎ ﺃﻭ‬
‫ﻝ‬
‫ﺟﺪﺍﻭﻝ‬
‫ﻋﻠﻰ ﻋﺪﺓ ﺃﺃﻗﺴﺎﻡ ﰲ ﻋﺪﺓ‬
‫ﺍﺠﻤﻟﺰﺃﺓ ﻠ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﺠﻤﻟ ﺃ‬
‫ﺇﱃ ﳌ ﻄ‬
‫ﱃ‬
‫{ ﳝﻜﻦ ﺃﻥ ﺗﻜﻮﻥ ﺍﳉﺪﺍﻭﻝ ﺍﻟﱵ ﲢﺘﻮﻱ ﻋﻠﻰ ﺍﳌﻌﻄﻴﺎﺕ ﺍﺠﻤﻟﺰﺃﺓ‪ ،‬ﻋﻠﻰ‬
‫ﻧﻔﺲ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ ﺃﻭ ﰲ ﻗﺎﻋﺪﺓ ﻣﻌﻄﻴﺎﺕ ﺃﺧﺮﻯ ﺃﻭ ﺣﱴ ﻋﻠﻰ‬
‫ﳐﺪﱢﻡ ﺁﺧﺮ ﺃﻭ ﻋﺪّﺓ ﳐﺪﻣﺎﺕ‬
‫{ ﻳﺘﻢ ﺇﻧﺸﺎﺀ ﺍﳌﻨﺎﻇﲑ ﺍﺠﻤﻟﺰﺃﺓ ﺍﻟﱵ ﺗﻘﻮﻡ ﺑﺘﺠﻤﻴﻊ ﺗﻠﻚ ﺍﳌﻌﻄﻴﺎﺕ‬
‫ﺍﻟﺘﻌﻠﻴﻤﺔ ‪UNION ALL‬‬
‫ﺾ ﻣﻦ ﺧﻼﻝ ﺍﻟ ﻠ ﺔ‬
‫ﺑﻌﻀﻬﺎﺎ ﺍﻟﺍﻟﺒﻌﺾ‬
‫ﻭﻋﺮﺿﻬﺎﺎ ﻣﻊ ﻀ‬
‫ﺿ‬
‫ﻭﺫﻟﻚ ﺑﻌﺪ ﺍﻹﺷﺎﺭﺓ ﺇﱃ ﺍﳌﺨﺪﻡ ﻭﺍﳉﺪﻭﻝ ﻭﺍﳉﺰﺀ ﺍﳌﻨﺎﺳﺐ ﰲ ﻛﻞ‬
‫ﺍﺳﺘﻌﻼﻡ ﻣﻦ ﺍﻻﺳﺘﻌﻼﻣﺎﺕ ﺍﳌﻜﻮّﻧﺔ ﻟﻠﻤﻨﻈﺎﺭ‬
‫ﺍﳌﻨﺎﻇﲑ ﺍﳌﻔﻬﺮﺳﺔ ‪Indexed Views‬‬
‫{ ﻫﻲﻲ ﻣﻨﺎﻇﲑ ﻳﺘﻢﻢ ﺑﻨﺎﺅﻫﺎ ﻭﲣﺰﻳﻨﻬﺎ ﳊﲔ ﺍﳊﺎﺟﺔ ﺇﻟﻴﻬﺎ ﻻﺣﻘﹰﺎ‪.‬‬
‫{ ﲢﺴﻦ ﺍﳌﻨﺎﻇﲑ ﺍﳌﻔﻬﺮﺳﺔ ﻣﻦ ﺃﺩﺍﺀ ﺑﻌﺾ ﺃﻧﻮﺍﻉ ﺍﳌﻨﺎﻇﲑ ﻣﺜﻞ‬
‫ﺍﳌﻨﺎﻇﲑ ﺍﻟﱵ ﺗﺴﺘﺨﺪﻡ ﺍﻟﺘﻮﺍﺑﻊ ﺍﻟﺘﺠﻤﻴﻌﻴﺔ‪.‬‬
‫{ ﻟﻜﻨﻬﺎ ﻏﲑ ﻣﻨﺎﺳﺒﺔ ﰲ ﺣﺎﻝ ﺍﻟﺘﻜﺮﺍﺭ ﺍﻟﻜﺒﲑ ﻟﺘﻌﺪﻳﻞ‬
‫ﺍﳌﻌﻄﻴﺎﺕ‪.‬‬
‫ﻋﻠﻴﻪ‬
‫ﻓﺮﻳﺪ ﻠ‬
‫ﻋﻨﻘﻮﺩﻱ ﻓ ﺪ‬
‫ﺑﺒﻨﺎﺀ ﻓﻓﻬﺮﺱ ﻘ‬
‫ﺍﳌﻨﺎﻇﲑ ﺎ‬
‫ﻓﻬﺮﺳﺔ ﺍﳌ ﺎﻇ‬
‫{ ﺗﺗﺘﻢ ﻓ ﺔ‬
‫ﻭﻳﺘﻄﻠﺐ ﻫﺬﺍ ﺗﻮﺍﻓﺮ ﻋﺪﺓ ﺷﺮﻭﻁ ﰲ ﺍﻟﻔﻬﺮﺱ ﻭﰲ ﻋﻤﻠﻴﺔ‬
‫ﺇﻧﺸﺎﺀ ﺍﻟﻔﻬﺮﺱ‪.‬‬
‫‪7‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﳌﻨﺎﻇﲑ ﻭﺃﺩﺍﺀ ﺍﻻﺳﺘﻌﻼﻣﺎﺕ‬
‫ﺍﻻﺳﺘﻌﻼﻣﺎﺕ‬
‫ﻳﺆﺛﺮﺮ ﺍﺳﺘﺨﺪﺍﻡﻡ ﺍﳌﻨﺎﻇﲑﲑ ﻋﻠﻰﻰ ﺃﺩﺍﺀﺀ ﻻ ﻼ‬
‫{ ﻻ ﻳﺆ‬
‫ﺣﻴﺚ ﻳﻘﻮﻡ ‪ Query Optimizer‬ﺑﺎﻟﻌﻤﻞ ﻋﻠﻰ ﲢﺴﲔ‬
‫ﻼ‬
‫ﺃﺩﺍﺀ ﺍﻻﺳﺘﻌﻼﻡ ﺑﻌﺪ ﺭﺑﻄﻪ ﺑﺎﳌﻨﻈﺎﺭ ﻣﺎ ﻳﻌﲏ ﺃﺩﺍ ًﺀ ﳑﺎﺛ ﹰ‬
‫ﻟﻠﺤﺎﻟﺔ ﺍﻟﱵ ﻻ ﻳﺴﺘﻌﻤﻞ ﻓﻴﻬﺎ ﺍﳌﻨﻈﺎﺭ‪.‬‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰﻧﺔ ‪Stored Procedures‬‬
‫{ ﺗﻌﱪ ﺍﻹﺟﺮﺍﺋﻴﺔ ﺍﳌﺨﺰّﻧﺔ ﻋﻦ ﺗﻌﻠﻴﻤﺔ ﺃﻭ ﺃﻛﺜﺮ ﻣﻦ ﺗﻌﻠﻴﻤﺎﺕ ‪T-SQL‬‬
‫ﺍﳌﻌﻄﻴﺎﺕ‬
‫ﻣُُﺨ ّﺰّﻧﺔ ﻛﻛﻐﺮﺽ ﺗﻨﻔﻴﺬﻱ ﰲ ﻗﺎﻋﺪﺓ ﳌ ﻄ‬
‫{ ﳝﻜﻦ ﺍﺳﺘﺪﻋﺎﺀﻫﺎ ﻣﻦ ﺑﺮﻧﺎﻣﺞ ﺃﻭ ﺇﺟﺮﺍﺀ ﺁﺧﺮ ﺃﻭ ﻣﻦ ﻗﺎﺩﺡ‬
‫{ ﳝﻜﻦ ﲤﺮﻳﺮ ﺍﳌﻌﺎﻣﻼﺕ ﻣﻦ ﻭﺇﱃ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ‪ ،‬ﳑﺎ ﻳﺰﻳﺪ ﻣﻦ‬
‫ﻓﺎﺋﺪﺓ ﻭﻣﺮﻭﻧﺔ ﺗﻠﻚ ﺍﻷﻏﺮﺍﺽ‬
‫{ ﺗﻌﻴﺪ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﻗﻴﻤﺔ ﺭﻗﻤﻴﺔ ﳏﺪﺩﺓ ﺃﻭ ﳎﻤﻮﻋﺔ ﻣﻦ‬
‫‪Resultsets‬‬
‫‪8‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﺳﺘﺨﺪﺍﻣﺎﺕ ﻭﻓﻮﺍﺋﺪ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫ﳝﻜﻨﻨﺎ ﻣﻦ ﺧﻼﻝ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﺃﻥ ﳓﻘﻖ ﻣﻔﻬﻮﻡ ﺍﻟﱪﳎﺔ ﺍﳌﹸﺠﺘﺰﺃﺓ‬
‫‪M d l PProgramming‬‬
‫‪Modular‬‬
‫‪i‬‬
‫ﺣﺼﺮ ﻋﻤﻠﻴﺎﺕ ﺍﻟﻮﻟﻮﺝ ﺇﱃ ﺍﳉﺪﺍﻭﻝ ﻭﺍﻻﻋﺘﻤﺎﺩ ﻋﻠﻰ ﻣﻔﻬﻮﻡ ﺍﻟﻮﻟﻮﺝ ﺇﱃ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﺍﻋﺘﻤﺎﺩﹰﺍ ﻋﻠﻰ ﺍﻟﺘﻮﺍﺑﻊ‬
‫ﲣﻔﻴﺾ ﺍﻟﻀﻐﻂ ﻋﻠﻰ ﺍﻟﺸﺒﻜﺔ‬
‫ﺗﺘﻤﺘﻊ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﺑﺴﺮﻋﺔ ﺗﻨﻔﻴﺬ ﺃﻛﱪ ﻣﻦ ﺳﺮﻋﺔ ﺗﻨﻔﻴﺬ ﳐﻄﻮﻃﺎﺕ ‪T-‬‬
‫‪SQL‬‬
‫ﲣﻔﻴﺾ ﺍﻷﺧﻄﺎﺀ ﺍﻟﱪﳎﻴﺔ ﻧﺴﺒﻴﹰﺎ‬
‫ﺇﻣﻜﺎﻧﻴﺎﺕ ﺿﻤﺎﻥ ﺗﻜﺎﻣﻞ ﺍﳉﺪﺍﻭﻝ‬
‫ﻣﺴﺎﻭﺉ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ‬
‫ﻣﻘﺎﺭﻧ ﹰﺔ ﻣﻊﻊ‬
‫ﺟﻴﺪﺓ ﺭ‬
‫‪ T-SQL‬ﻟﻐﺔ ﺑﺮﺑﺮﳎﺔ ﻴ‬
‫{ ﻻ ﺗﻌﺘﱪﱪ ﻟﻐﺔ ‪Q‬‬
‫ﻟﻐﺎﺕ ﺍﻟﱪﳎﺔ ﺍﻷﺧﺮﻯ‬
‫{ ﺿﻌﻒ ﺍﻟﺒﻴﺌﺔ ﺍﻟﱪﳎﻴﺔ ﺍﻟﱵ ﺗﻌﻤﻞ ﻋﻠﻴﻬﺎ‪ ،‬ﻣﺜﻞ ﺍﻟﺘﻨﻘﻴﺢ‬
‫ﻭﺗﺘﺒﻊ ﺍﻷﺧﻄﺎﺀ ‪ debugging‬ﻭ ﺇﺩﺍﺭﺓ ﺍﻟﻨﺴﺦ‬
‫‪ Versioning‬ﻭﻏﲑﻫﺎ‬
‫{ ﺯﻳﺎﺩﺓ ﺻﻌﻮﺑﺔ ﺍﻟﻨﻘﻞ ﻟﻘﻮﺍﻋﺪ ﻣﻌﻄﻴﺎﺕ ﺃﺧﺮﻯ‬
‫‪Portablility‬‬
‫‪9‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺇﻧﺸﺎﺀ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﺑﺎﺳﺘﺨﺪﺍﻡ ‪T-SQL‬‬
‫ﻋﺒﺎﺭﺓ ﺇﻨﺸﺎﺀ ﺍﻹﺠﺭﺍﺌﻴﺔ ﺍﻟﻤﺨﺯّﻨﺔ ﻤﻊ ﺘﺤﺩﻴﺩ ﺍﺴﻡ ﺍﻟﻤﺎﻟﻙ ﻭﺍﺴﻡ ﺍﻹﺠﺭﺍﺌﻴﺔ ﺍﻟﺫﻱ‬
‫ﻴﺨﻀﻊ ﻟﺸﺭﻭﻁ ﺍﻟﺘﺴﻤﻴﺔ ﺍﻟﻤﻌﺭﻭﻓﺔ‬
‫ﺍﻟﺘﻲ ﺘﺘﺸﺎﺒﻪ ﻓﻓﻲ ﺍﻷﺴﻤﺎﺀ‪ ،‬ﺒﺤﻴﺙ ﻴﻤﻜﻥ ﻤﻥ‬
‫ﻴﺴﺘﺨﺩﻡ ﻟﺘﺠﻤﻴﻊ ﺍﻹﺠﺭﺍﺀﺍﺕ ﺍﻟﺘ‬
‫ﺭﻗﻡ ﻴُﺴﺘﺨﺩﻡ‬
‫ﺨﻼل ﺍﺴﺘﺨﺩﺍﻡ ﻫﺫﻩ ﺍﻟﺨﺎﺼﺔ ﺃﻥ ﻨﻘﻭﻡ ﺒﺤﺫﻑ ﻋﺩّﺓ ﺇﺠﺭﺍﺌﻴﺎﺕ ﻤﺨﺯّﻨﺔ ﺩﻓﻌﺔ‬
‫ﻭﺍﺤﺩﺓ‬
‫]‪CREATE PROCEDURE [ owner.‬‬
‫‪procedure_name‬‬
‫] ‪[ ; number‬‬
‫ﻗﺎﺌﻤﺔ ﺍﻟﻤﻌﺎﻤﻼﺕ‪ ،‬ﺒﺤﻴﺙ ﻴﺘﻡ ﺘﺤﺩﻴﺩ ﺍﺴﻡ ﺍﻟﻤﻌﺎﻤل ﻭﻨﻤﻁﻪ‪ ،‬ﺒﺎﻹﻀﺎﻓﺔ ﺇﻟﻰ ﻗﻴﻤﺘﻪ‬
‫ﺍﻻﺒﺘﺩﺍﺌﻴﺔ‬
‫=[ ‪[ [(] @parameter data_type‬‬
‫]‪default_value‬‬
‫ﺍﻟﻜﻠﻤﺔ ﺍﻟﻤﻔﺘﺎﺤﻴﺔ ﺍﻟﺘﻲ ﺘﺩل ﻋﻠﻰ ﺃﻥ ﺍﻟﻤﻌﺎﻤل ﺍﻟﻤﺤﺩﺩ ﻫﻭ ﻤﻌﺎﻤل ﺨﺭﺝ؛ ﻤﺘﺘﺎﻟﻴﺔ‬
‫ﺒﺒﻘﻴﺔ ﻤﻌﺎﻤﻼﺕ ﺍﻹﺠﺭﺍﺌﻴﺔ ﺍﻟﻤﺨﺯﻨﺔ‬
‫] ‪[ OUTPUT‬‬
‫] ])[ ] ‪[ ,...n‬‬
‫ﻭﻫﻲ ﺍﻟﺨﺎﺼﺔ ﺍﻟﺘﻲ ﺘﺤﺩﺩ ﺃﻨﻪ ﻻ ﻴﻨﺒﻐﻲ ﺘﺨﺯﻴﻥ ﺨﻁﺔ ﺍﻻﺨﺘﺯﺍل ﺍﻟﻤﻁﺒﻘﺔ ﻤﻥ ﺃﺠل‬
‫ﻋﻤﻠﻴﺎﺕ ﺍﻟﺘﻨﻔﻴﺫ ﺍﻟﻤﺴﺘﻘﺒﻠﻴﺔ ﻟﻺﺠﺭﺍﺌﻴﺔ ﺍﻟﻤﺨﺯّﻨﺔ‪ ،‬ﺒﺤﻴﺙ ﻴﺘﻡ ﺇﻋﺎﺩﺓ ﺒﻨﺎﺀ ﺘﻠﻙ‬
‫ﺍﻟﺨﻁﺔ ﻓﻲ ﻜل ﻤﺭﺓ ﻭﻗﺕ ﺍﻟﺘﻨﻔﻴﺫ‬
‫ﺨﺎﺼﺔ ﺘﺴﺘﺨﺩﻡ ﻟﺘﺸﻔﻴﺭ ﻤﺤﺘﻭﻯ ﺍﻹﺠﺭﺍﺀﺍﺕ ﺍﻟﻤﺨﺯّﻨﺔ‪) ،‬ﺭﺍﺠﻊ ﺨﺎﺼﺔ ﺍﻟﺘﺸﻔﻴﺭ‬
‫ﻓﻲ ﺍﻟﻤﻨﺎﻅﻴﺭ(‬
‫ﻟﻠﺩﻻﻟﺔ ﻋﻥ ﺇﻤﻜﺎﻨﻴﺔ ﺍﺴﺘﺨﺩﺍﻡ ﺍﻟﺨﺎﺼﺘﻴﻥ ﺍﻟﺴﺎﺒﻘﺘﻴﻥ ﻤﻌﹰﺎ‬
‫ﺘﻌﻠﻴﻤﺎﺕ ‪ T-SQL‬ﺍﻟﻤﻜﻭّﻨﺔ ﻟﻺﺠﺭﺍﺌﻴﺔ‬
‫ﻗﻴﻤﺔ ﺍﻟﺨﺭﺝ ‪-‬ﻓﻲ ﺤﺎل ﻭﺠﻭﺩﻫﺎ‪ -‬ﺍﻟﺘﻲ ﺘﻌﻴﺩﻫﺎ ﺍﻹﺠﺭﺍﺌﻴﺔ ﺍﻟﻤﺨﺯّﻨﺔ‪ ،‬ﻤﻊ ﺍﻟﻌﻠﻡ‬
‫ﺃﻨﻪ ﻴﻔﻀل ﺍﺴﺘﺨﺩﺍﻡ ﺍﻟﻌﺒﺎﺭﺓ ‪ RETURN 0‬ﻓﻲ ﺍﻟﺤﺎﻻﺕ ﺍﻟﺘﻲ ﻴﺘﻡ ﻓﻴﻬﺎ ﺘﻨﻔﻴﺫ‬
‫ﺍﻹﺠﺭﺍﺌﻴﺔ ﺍﻟﻤﺨﺯّﻨﺔ ﺒﺸﻜل ﺴﻠﻴﻡ‪ ،‬ﻭﺇﻋﺎﺩﺓ ﺭﻗﻡ ﺁﺨﺭ ﺨﻼﻑ ﺫﻟﻙ‬
‫| ‪{ RECOMPILE‬‬
‫‪[ WITH‬‬
‫| ‪ENCRYPTION‬‬
‫] } ‪RECOMPILE , ENCRYPTION‬‬
‫] ‪AS sql_statement [ ...n‬‬
‫]]‪[return [integer_status_value‬‬
‫ﺗﻨﻔﻴﺬ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰﻧﺔ‬
‫]]‪[[exec[ute‬‬
‫‪[ ]] [@status‬‬
‫@[‬
‫]]=‬
‫]‪[owner.]procedure_name[; number‬‬
‫]] ‪[[@parm_name =] expression [output][, ...‬‬
‫]‪[with recompile‬‬
‫‪10‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﻣﻄﺎﺑﻘﺔ ﺍﻷﲰﺎﺀ ﺍﳌﺆﺟﻠﺔ ‪Deferred Name Resolution‬‬
‫‪SQL‬‬
‫ﻮﻡ ‪Q Server‬‬
‫ﳐﺰﻥ ﻻ ﻳﻳﻘﻮﻡ‬
‫ﺇﺟﺮﺍﺀ ﺰ‬
‫{ ﻋﻨﺪ ﺇﺇﻧﺸﺎﺀ ﺇ ﺮ‬
‫ﺑﺎﻟﺘﺄﻛﺪ ﻣﻦ ﺗﻮﺍﺟﺪ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﻷﺧﺮﻯ ﺍﻟﱵ ﻳﻄﻠﺒﻬﺎ‬
‫ﻫﺬﺍ ﺍﻟﻺﺟﺮﺍﺀ‪.‬‬
‫{ ﻳﻔﻴﺪ ﻫﺬﺍ ﰲ ﲡﺎﻭﺯ ﺿﺮﻭﺭﺓ ﺗﺮﺗﻴﺐ ﺇﻧﺸﺎﺀ‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ‪.‬‬
‫{ ﻻ ﻳﻨﻄﺒﻖ ﻫﺬﺍ ﻋﻠﻰ ﺃﲰﺎﺀ ﺍﳉﺪﺍﻭﻝ ﻭﺍﻷﻋﻤﺪﺓ‪.‬‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰﻧﺔ ﺍﳌﺘﺪﺍﺧﻠﺔ ‪Nested‬‬
‫ﳐﺰﻧﺔ‬
‫ﺇﺟﺮﺍﺀﺍﺕ ﺰ‬
‫ﺐ ﺇ ﺮﺀ‬
‫ﺗﻄﻠﺐ‬
‫ﺍﳌﺨﺰﻧﺔ ﺃﻥ ﻄ‬
‫ﻟﻺﺟﺮﺍﺀﺍﺕ ﺰ‬
‫{ ﳝﳝﻜﻦﻦ ﻺ ﺮ ﺀ‬
‫ﺃﺧﺮﻯ ﻭﳝﻜﻦ ﺃﻥ ﻳﺼﻞ ﻣﺴﺘﻮﻯ ﺍﻟﺘﺪﺍﺧﻞ ﺇﱃ ‪٣٢‬‬
‫{ ﳛﺪﺩ ﺍﻟﺘﺎﺑﻊ ‪ @@NESTLEVEL‬ﻣﺴﺘﻮﻯ ﺍﻟﺘﺪﺍﺧﻞ‬
‫{ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﻟﻌﻮﺩﻳﺔ ‪ Recursive‬ﻫﻲ ﺣﺎﻟﺔ ﺧﺎﺻﺔ ﻣﻦ‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺘﺪﺍﺧﻠﺔ ﻭﲣﻀﻊ ﻟﻨﻔﺲ ﺍﻟﺸﺮﻭﻁ‬
‫‪11‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﺳﺘﺨﺪﺍﻡ ﺍﳉﺪﺍﻭﻝ ﺍﳌﺆﻗﺘﺔ ﰲ ﺍﻹﺟﺮﺍﺀﺍﺕ‬
‫‪OK‬‬
‫’‪Error Invalid object name ‘#temp‬‬
‫ﺍﺳﺘﺨﺪﺍﻡ ﳕﻂ ﺍﳌﻌﻄﻴﺎﺕ ‪table‬‬
‫{ ﻳﻔﻀﻞ ﺍﺳﺘﺨﺪﺍﻡ ﳕﻂ ﺍﳌﻌﻄﻴﺎﺕ ‪ table‬ﰲ ﻣﻌﻈﻢ ﺍﳊﺎﻻﺕ‬
‫ﻟﻸﺳﺒﺎﺏ ﺍﻟﺘﺎﻟﻴﺔ‪:‬‬
‫‪ z‬ﲣﺰﻥ ﳏﺘﻮﻳﺎﺕ ﻫﺬﺍ ﺍﻟﻨﻤﻂ ﰲ ﺍﻟﺬﺍﻛﺮﺓ ﻭﻟﻴﺲ ﰲ ﺍﻟﻘﺎﻋﺪﺓ‬
‫‪ tempdb‬ﻭﺑﺎﻟﺘﺎﱄ ﺍﻟﺘﻌﺎﻣﻞ ﻣﻌﻪ ﺃﺳﺮﻉ‬
‫ﺍﻟﺘﺮﲨﺎﺕ‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﻣﻦﻦ ﻋﺪﺩ ﺮ‬
‫ﻳﻘﻠﻞ ﺍﺳﺘﺨﺪﺍﻣﻪ ﰲ ﻹ ﺮ‬
‫‪z‬ﻳ ﻞ‬
‫‪ Recompilations‬ﺍﳌﻄﻠﻮﺑﺔ‬
‫‪ z‬ﺗﻘﻠﻴﻞ ﻋﺪﺩ ﺍﻷﻗﻔﺎﻝ ﺍﳌﻄﻠﻮﺑﺔ ﻋﻠﻰ ﺍﳉﺪﺍﻭﻝ ﻟﺘﻮﺍﺟﺪ ﺍﳌﻌﻄﻴﺎﺕ‬
‫ﻓﻘﻂ ﻋﻨﺪ ﺗﻨﻔﻴﺬ ﺍﻹﺟﺮﺍﺀ ﻛﻮﻥ ﺍﳉﺪﻭﻝ ﳛﺬﻑ ﺁﻟﻴﹰﺎ ﻋﻨﺪ ﺍﻧﺘﻬﺎﺀ‬
‫ﺗﻨﻔﻴﺬ ﺍﻹﺟﺮﺍﺀ‬
‫‪12‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﻣﺜﺎﻝ‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﻵﻟﻴﺔ ‪Autostart‬‬
‫{ ﻳﺘﻢ ﺗﺸﻐﻴﻞ ﻫﺬﺍ ﺍﻟﻨﻮﻉ ﻣﻦ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺁﻟﻴﹰﺎ ﻋﻨﺪ ﺗﺸﻐﻴﻞ ﺍﻟﻨﻈﺎﻡ‬
‫{ ﺗﺴﺘﺨﺪﻡ ﻫﺬﻩ ﺍﻹﺟﺮﺍﺀﺍﺕ ﰲ‪:‬‬
‫‪ z‬ﻭﺿﻊ ﺍﻟﺴﻤﺎﺣﻴﺎﺕ ﰲ ﺍﻟﻘﺎﻋﺪﺓ ‪tempdb‬‬
‫‪ z‬ﺇﻧﺸﺎﺀ ﺟﺪﻭﻝ ﻣﺆﻗﺖ ﻋﺎﻡ‬
‫‪ z‬ﺗﺸﻐﻴﻞ ﺑﺮﻧﺎﻣﺞ ﺁﺧﺮ ﻋﻨﺪ ﺗﺸﻐﻴﻞ ‪ SQL Server‬ﺑﺎﺳﺘﺨﺪﺍﻡ‬
‫‪xp_cmdshell.‬‬
‫ﺗﺸﻐﻴﻞ ﺍﳌﺍﳌﺨﺪﻡ‬
‫ﻗﺒﻞ ﻞ‬
‫‪ cache‬ﻗ ﻞ‬
‫ﻭﻣﻠﺊ ﺍﻟﺍﻟـ ‪h‬‬
‫‪ z‬ﲢﲢﻀﲑ ﻠ‬
‫{ ﻳﺘﻢ ﺗﺸﻐﻴﻞ ﺇﺟﺮﺍﺀ ﺁﻟﻴﹰﺎ ﻋﻨﺪ ﺇﻗﻼﻉ ﺍﳌﺨﺪﻡ ﰲ ﺣﺎﻝ ﻛﺎﻥ ﻟﺪﻳﻪ‬
‫ﺍﳋﺎﺻﺔ ‪ startup‬ﻣﻔﻌّﻠﺔ‪ ،‬ﻭﻳﺘﻢ ﺗﻔﻌﻴﻠﻬﺎ ﻛﻤﺎ ﻳﻠﻲ‪:‬‬
‫‪sp_procoption procedure_name, startup, true‬‬
‫‪13‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﻹﺿﺎﻓﻴﺔ ‪Extended‬‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫{‬
‫ﻫﻲ ﻋﺒﺎﺭﺓ ﻋﻦ ﺗﻮﺍﺑﻊ ﻣﻮﺟﻮﺩﺓ ﰲ ﻣﻜﺘﺒﺎﺕ ‪ DLL‬ﻭﻣﻜﺘﻮﺑﺔ ﺑﻠﻐﺔ ‪ C‬ﺃﻭ ‪... C++‬‬
‫ﺍﲰﻬﺎ ﺑـ _‪xp‬‬
‫ﻳﺒﺪﺃ ﺍ ﺎ‬
‫ﺃ‬
‫ﺗﺘﻮﺍﺟﺪ ﰲ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ ‪ master‬ﻓﻘﻂ‬
‫ﻼ ﻋﻨﺪ ﻃﻠﺒﻬﺎ‬
‫ﺗﻌﺎﻣﻞ ﻣﺜﻞ ﻣﻌﺎﻣﻠﺔ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﻻﻋﺘﻴﺎﺩﻳﺔ ﻟﻜﻦ ﳚﺐ ﺫﻛﺮ ﺍﲰﻬﺎ ﻛﺎﻣ ﹰ‬
‫‪exec master..xp_fixeddrives‬‬
‫ﺗﺴﺘﺨﺪﻡ ﻫﺬﻩ ﺍﻹﺟﺮﺍﺀﺍﺕ ﻧﻔﺲ ﻓﻀﺎﺀ ﺍﻟﺬﺍﻛﺮﺓ ﺍﳋﺎﺹ ﺑـ ‪ SQL Server‬ﻟﺬﻟﻚ ﻗﺪ ﺗﺴﺒﺐ‬
‫ﺍﻷﺧﻄﺎﺀ ﰲ ﻫﺬﻩ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺿﺮﺭﹰﺍ ﻛﺒﲑﹰﺍ ﻟـ ‪SQL Server‬‬
‫‪ sp_adde‬ﻭﻭﲢﺬﻑ ﺑﺑﺎﺳﺘﺨﺪﺍﻡﻡ‬
‫‪sp addextendedproc‬‬
‫ﺍﻹﺟﺮﺍﺀﺀ ‪te dedp oc‬‬
‫‪ SQ‬ﺑﺑﺎﺳﺘﺨﺪﺍﻡﻡ ﻹ ﺮ‬
‫‪SQL Se‬‬
‫‪Server‬‬
‫ﺗﻀﺎﻑ ﺇﱃ ‪ve‬‬
‫ﻀ‬
‫ﺍﻹﺟﺮﺍﺀ ‪ sp_dropextendedproc‬ﻭﻳﺴﺘﻌﻠﻢ ﻋﻨﻬﺎ ﺑـ ‪sp_helpextendedproc‬‬
‫ﺃﺷﻬﺮ ﻫﺬﻩ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﻌﺮﻓﺔ ﰲ ‪ SQL Server‬ﻫﻮ ‪ xp_cmdshell‬ﺍﻟﺬﻱ ﻳﺴﻤﺢ ﺑﺘﺸﻐﻴﻞ‬
‫ﺑﺮﻧﺎﻣﺞ ﺧﺎﺭﺟﻲ ﻣﺜﻞ‬
‫’*‪EXEC xp_cmdshell ‘DIR c:\*.‬‬
‫ﺍﻟﻘﻮﺍﺩﺡ ‪Triggers‬‬
‫{ ﺗﻌﱪ ﺍﻟﻘﻮﺍﺩﺡ ﺣﺎﻻﺕ ﺧﺎﺻﺔ ﻣﻦ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ‪ ،‬ﲝﻴﺚ ﻳﺘﻢ‬
‫ﺍﳌﻌﻄﻴﺎﺕ‬
‫ﳊﺪﻭﺙ ﺗﻌﺪﻳﻼﺕ ﰲ ﳌ ﻄ‬
‫ﻧﺘﻴﺠﺔ ﳊ‬
‫ﺗﻠﻘﺎﺋﻴﺎ ﹰ‬
‫ﹰ‬
‫ﺗﻨﻔﻴﺬﻫﺎ‬
‫{ ﻳﺘﻢ ﺇﻧﺸﺎﺀ ﺍﻟﻘﻮﺍﺩﺡ ﻋﻠﻰ ﺍﳉﺪﺍﻭﻝ‪ ،‬ﻛﻤﺎ ﻳﺘﻢ ﺭﺑﻂ ﺗﻨﻔﻴﺬﻫﺎ ﺑﻮﻗﻮﻉ‬
‫ﺣﺪﺙ ﺃﻭ ﺃﻛﺜﺮ ﲝﻴﺚ ﻳﺮﺗﺒﻂ ﺗﻨﻔﻴﺬﻫﺎ ﲝﺪﻭﺙ ﺗﻐﲑﺍﺕ ﻋﻠﻰ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻣﻦ ﺇﺿﺎﻓﺔ ﺃﻭ ﺣﺬﻑ ﺃﻭ ﺗﻌﺪﻳﻞ‬
‫‪14‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﻟﻘﻮﺍﺩﺡ‬
‫{ ﺗﻌﺘﱪ ﺍﻟﻘﻮﺍﺩﺡ ﻣﻦ ﺍﻷﺩﻭﺍﺕ ﺍﳍﺎﻣﺔ ﺍﻟﱵ ﻳﺘﻢ ﺍﺳﺘﺨﺪﺍﻣﻬﺎ ﻣﻦ ﺃﺟﻞ‬
‫ﺗﻜﺎﻣﻠﻬﺎ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻭﺿﻤﺎﻥ ﻜ‬
‫ﺻﻴﺎﻧﺔ ﻗﺎﻋﺪﺓ ﳌ ﻄ‬
‫{ ﺗﺴﺘﻄﻴﻊ ﺍﻟﻘﻮﺍﺩﺡ ‪-‬ﺃﺛﻨﺎﺀ ﺗﻨﻔﻴﺬﻫﺎ‪ -‬ﺃﻥ ﺗﻘﻮﻡ ﺑﻌﺪﺩ ﻛﺒﲑ ﻣﻦ ﺍﻷﻋﻤﺎﻝ‪،‬‬
‫ﻣﻨﻬﺎ‪:‬‬
‫‪ z‬ﺍﺧﺘﺒﺎﺭ ﺻﺤﺔ ﺗﻌﺪﻳﻞ ﺍﳌﻌﻄﻴﺎﺕ ﻭﺇﻟﻐﺎﺀ ﺍﻟﺘﻌﺪﻳﻞ ﺍﳋﺎﻃﺊ‬
‫‪ z‬ﻗﺮﺍﺀﺓ ﺍﳌﻌﻄﻴﺎﺕ ﻣﻦ ﻋﺪّﺓ ﺟﺪﺍﻭﻝ ﻣﻦ ﻧﻔﺲ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ ﺃﻭ ﻣﻦ ﻗﻮﺍﻋﺪ‬
‫ﺎﺕ ﳐﺘﻠﻔﺔ‬
‫ﻣﻌﻄﻴﺎﺕ‬
‫ﻄ‬
‫‪ z‬ﺗﻌﺪﻳﻞ ﺍﳉﺪﺍﻭﻝ ﻣﻦ ﻧﻔﺲ ﻗﺎﻋﺪﺓ ﺍﳌﻌﻄﻴﺎﺕ ﺃﻭ ﻣﻦ ﻗﻮﺍﻋﺪ ﻣﻌﻄﻴﺎﺕ ﳐﺘﻠﻔﺔ‬
‫‪ z‬ﺗﻨﻔﻴﺬ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﺍﶈﻠﻴﺔ ﻭﺍﻟﺒﻌﻴﺪﺓ‬
‫ﺍﺳﺘﺨﺪﺍﻣﺎﺕ ﺍﻟﻘﻮﺍﺩﺡ‬
‫‪ z‬ﺍﺧﺘﺒﺎﺭ ﺍﻟﻘﻴﻮﺩ ﺍﳌﻌﻘﺪﺓ ﺍﳌﻔﺮﻭﺿﺔ ﻋﻠﻰ ﺍﻷﻋﻤﺪﺓ‪ ،‬ﻭﺧﺎﺻﺔ‬
‫ﺍﻷﺳﻄﺮ ﺍﳌﻮﺟﻮﺩﺓ ﺿﻤﻦ ﻧﻔﺲ‬
‫ﺗﻠﻚ ﺍﻟﱵ ﺗﻌﺘﻤﺪ ﻋﻠﻰ ﺑﻌﺾ ﻷ‬
‫ﺍﳉﺪﻭﻝ ﺃﻭ ﺟﺪﺍﻭﻝ ﳐﺘﻠﻔﺔ‬
‫‪ z‬ﺿﻤﺎﻥ ﺍﻟﺘﻜﺎﻣﻞ ﺍﻟﺘﺎﺑﻌﻲ ﺍﻟﺸﻼﹼﱄ ‪Cascading Referential‬‬
‫‪Integrity‬‬
‫‪ z‬ﺗﻮﻟﻴﺪ ﺍﻟﻘﻴﻢ ﺍﻟﺘﻠﻘﺎﺋﻴﺔ ﺍﳌﻌﻘﺪﺓ ﺍﻟﱵ ﺗﻌﺘﻤﺪ ﻋﻠﻰ ﻣﻌﻄﻴﺎﺕ‬
‫ﻝ‬
‫ﺟﺪﺍﻭﻝ‬
‫ﻣﻮﺟﻮﺩﺓ ﰲ ﻋﺪﺓ ﺃﺃﺳﻄﺮ ﺃﺃﻭ ﺃﺃﻋﻤﺪﺓ ﺃﺃﻭ‬
‫‪ z‬ﺍﻟﺘﻜﺎﻣﻞ ﺑﲔ ﻋﺪﺓ ﻗﻮﺍﻋﺪ ﻣﻌﻄﻴﺎﺕ‬
‫‪ z‬ﺇﳚﺎﺩ ﻧﺴﺦ ﻣﻦ ﺍﳌﻌﻄﻴﺎﺕ ﺃﻭ ﻣﻌﻄﻴﺎﺕ ﻣﺸﺘﻘﺔ‬
‫‪15‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺃﻧﻮﺍﻉ ﻭﺧﺼﺎﺋﺺ ﺍﻟﻘﻮﺍﺩﺡ‬
‫{ ﻗﺒﻞﻞ ﻇﻬﻮﺭ ﺍﻹﺻﺪﺍﺭ ‪ ،SQL Server 2000‬ﻛﺎﻧﺖ ﺍﻟﻘﻮﺍﺩﺡ‬
‫ﺡ‬
‫ﺍﻟﻼﺣﻘﺔ ‪ AFTER Triggers‬ﻫﻲ ﺍﻟﻨﻮﻉ ﺍﻟﻮﺣﻴﺪ ﻣﻦ‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﺍﳌﺘﺎﺣﺔ‪ ،‬ﲝﻴﺚ ﱂ ﻳﻜﻦ ﻣﻦ ﺍﻟﻀﺮﻭﺭﻱ ﺍﺳﺘﺨﺪﺍﻡ‬
‫ﺍﻟﻌﺒﺎﺭﺓ "‪ "AFTER‬ﻟﻠﺪﻻﻟﺔ ﻋﻠﻰ ﻫﺬﺍ ﺍﻟﻨﻮﻉ ﻣﻦ ﺍﻟﻘﻮﺍﺩﺡ‬
‫{ ﻭﻗﺪّﻣﺖ ﺍﻷﺩﺍﺓ ‪ SQL Server 2000‬ﻧﻮﻋﹰﺎ ﺟﺪﻳﺪﹰﺍ ﻣﻦ‬
‫ﺍﻟﺒﺪﻳﻠﺔ ‪INSTEAD‬‬
‫ﻋﻠﻴﻬﺎ ﺍﺳﻢ ﻟﺍﻟﻘﻮﺍﺩﺡ ﻟ ﻠ‬
‫ﻳﻄﻠﻖ ﻠ‬
‫ﻟﺍﻟﻘﻮﺍﺩﺡ ﻄﻠ‬
‫‪OF TRIGGERS‬‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﻼﺣﻘﺔ ‪AFTER Triggers‬‬
‫{ ﺗﻌﺘﱪ ﻃﺮﻳﻘﺔ ﻋﻤﻞ ﻫﺬﺍ ﺍﻟﻨﻮﻉ ﻣﻦ ﺍﻟﻘﻮﺍﺩﺡ ﺑﺴﻴﻄﺔ ﻣﻦ ﺣﻴﺚ‬
‫ﺍﻟﻨﻘﺎﻁ ﺍﳍﺎﻣﺔ ﻟﺍﻟﱵ ﻳﻨﺒﻐﻲ ﺃﺃﺧﺬﻫﺎ ﺑﻌﲔ‬
‫ﻫﻨﺎﻙ ﺑﻌﺾ ﻟ ﻁ‬
‫ﳌﺍﳌﻔﻬﻮﻡ‪ ،‬ﺇﻻ ﺃﺃﻥ ﻙ‬
‫ﺍﻻﻋﺘﺒﺎﺭ ﻋﻨﺪ ﺍﺳﺘﺨﺪﺍﻡ ‪AFTER Triggers‬‬
‫{ ﻳﺘﻢ ﺇﻃﻼﻕ ‪ AFTER Triggers‬ﺑﻌﺪ ﺗﻨﻔﻴﺬ ﺍﻟﺘﻌﺪﻳﻼﺕ ﻋﻠﻰ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻭﻟﻜﻦ ﻗﺒﻞ ﺗﺄﻛﻴﺪ ﺗﻠﻚ ﺍﻟﺘﻌﺪﻳﻼﺕ‪ ،‬ﲝﻴﺚ ﻳﺘﻢ ﺗﺴﺠﻴﻞ‬
‫ﻛﺎﻓﺔ ﺍﳌﻨﺎﻗﻼﺕ ‪-‬ﺍﳌﺴﺆﻭﻟﺔ ﻋﻦ ﺍﻟﺘﻌﺪﻳﻼﺕ ﺍﻟﱵ ﻳﺘﻢ ﺗﻄﺒﻴﻘﻬﺎ‪ -‬ﰲ‬
‫ﻳﻨﺘﻬﻲ ﺗﻨﻔﻴﺬ‬
‫ﺳﺠﻞ ﺍﳌﻨﺎﻗﻼﺕ‪ ،‬ﻭﻟﻜﻦ ﻻ ﻳﺘﻢ ﺗﺄﻛﻴﺪﻫﺎ ﺣﱴ ﻳﻨﺘﻬ‬
‫ﻣﻠﻒ ﺳﺠﻞﹼ‬
‫ﺍﻟﻘﺎﺩﺡ ﺑﺎﻟﻜﺎﻣﻞ‬
‫{ ﳝﻜﻦ ﺍﻟﺘﺮﺍﺟﻊ ﻋﻦ ﻛﺎﻓﺔ ﺍﻟﺘﻌﺪﻳﻼﺕ ﺍﻟﱵ ﰎ ﺗﻄﺒﻴﻘﻬﺎ ﻣﻦ ﺧﻼﻝ‬
‫ﺍﻟﻘﺎﺩﺡ ﺃﻭ ﻣﻦ ﺧﻼﻝ ﺍﻟﻌﺒﺎﺭﺍﺕ ﺍﻟﱵ ﻗﺎﻣﺖ ﺑﺈﻃﻼﻗﻪ‬
‫‪16‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺇﻧﺸﺎﺀ ﻗﺎﺩﺡ ﻻﺣﻖ‬
‫ﺘﺤﺩﻴﺩﺩ ﺍﺍﺴﻡ ﺫﻟﻙ ﺍﻟﻘﺎﺩﺡ‬
‫ﻤﻊ ﺘ ﺩ‬
‫ﺠﺩﻴﺩ ﻊ‬
‫ﺇﻨﺸﺎﺀ ﻗﺎﺩﺡ ﺩ ﺩ‬
‫ﺘﻌﻠﻴﻤﺔﺔ ﺇﻨﺸﺎ‬
‫ﺘﻠ‬
‫ﺘﺤﺩﻴﺩ ﺍﻟﺠﺩﻭل ﺍﻟﺫﻱ ﺴﻴﺘﻡ ﺇﻨﺸﺎﺀ ﺍﻟﻘﺎﺩﺡ ﻋﻠﻴﻪ‬
‫ﻋﺒﺎﺭﺓ ‪ AFTER‬ﺘﺩل ﻋﻠﻰ ﻨﻭﻉ ﺍﻟﻘﺎﺩﺡ ﺍﻟﻤﻨﺸﺄ‪ ،‬ﻤﻊ‬
‫ﺍﻟﻌﻠﻡ ﺃﻨﻬﺎ ﺍﻟﻌﺒﺎﺭﺓ ﺍﻟﺘﻠﻘﺎﺌﻴﺔ؛ ﺘﻠﻲ ﺍﻟﻌﺒﺎﺭﺓ ‪AFTER‬‬
‫ﻋﺒﺎﺭﺓ ﺘﺩل ﻋﻠﻰ ﺍﻟﻌﻤﻠﻴﺔ ﺍﻟﺘﻲ ﺴﺘﺅﺩﻱ ﺇﻟﻰ ﺇﻁﻼﻕ‬
‫ﺍﻟﻘﺎﺩﺡ‪ ،‬ﺃﻱ ﺇﻤﺎ ﺒﻌﺩ ﺇﻀﺎﻓﺔ ﺴﻁﺭ ﺠﺩﻴﺩ‪ ،‬ﺃﻭ ﺒﻌﺩ ﺘﻌﺩﻴل‬
‫ﺴﻁﺭ ﻤﻌﻴﻥ‪ ،‬ﺃﻭ ﺒﻌﺩ ﺤﺫﻑ ﺴﻁﺭ‬
‫ﻋﺒﺎﺭﺓ ﻋﻥ ﺍﻟﺘﻌﻠﻴﻤﺎﺕ ﺍﻟﺘﻲ ﺴﻴﺘﻡ ﺘﻨﻔﻴﺫﻫﺎ ﻤﻥ ﻗﺒل‬
‫ﺍﻟﻘﺎﺩﺡ ﺒﻌﺩ ﺇﻁﻼﻗﺔ‬
‫‪CREATE TRIGGER‬‬
‫‪trigger_name‬‬
‫‪ON table_name‬‬
‫| ‪AFTER {INSERT‬‬
‫}‪UPDATE | DELETE‬‬
‫‪AS‬‬
‫‪Sql_statements‬‬
‫ﻋﺪﺩ ﻭﺗﺮﺗﻴﺐ ﺍﻟﻘﻮﺍﺩﺡ‬
‫{ ﻳﺴﻤﺢ ﻛﻞ ﻣﻦ ‪ SQL Server 7.0‬ﻭ‪ SQL Server 2000‬ﺑﺒﻨﺎﺀ ﺃﻛﺜﺮ ﻣﻦ ﻗﺎﺩﺡ‬
‫ﺗﻌﺪﻳﻞﻞ ﺃﺃﻭ ﺬﻑ‬
‫ﺣﺬﻑ‬
‫ﺍﳌﻌﻄﻴﺎﺕ ﻣﻦ ﺇﺿﺎﻓﺔ ﺃﺃﻭ ﺗ ﺪ‬
‫ﻋﻤﻠﻴﺔ ﺗﻐﺗﻐﻴﲑ ﰲ ﺍﳌ ﻄ ﺎ‬
‫ﺃﺟﻞ ﻛﻞ ﻠ ﺔ‬
‫ﻣﻦ ﺃ ﻞ‬
‫{ ﺗﻌﺘﱪ ﻫﺬﻩ ﺍﳋﺎﺻﺔ ﻣﻔﻴﺪﺓ ﰲ ﺑﻌﺾ ﺍﳊﺎﻻﺕ ﺇﻻ ﺃﻬﻧﺎ ﳝﻜﻦ ﺃﻥ ﺗﺆﺩﻱ ﺇﱃ‬
‫ﺣﺪﻭﺙ ﺑﻌﺾ ﺍﻻﺭﺗﺒﺎﻛﺎﺕ ﺧﺎﺻ ﹰﺔ ﻓﻴﻤﺎ ﻳﺘﻌﻠﻖ ﺑﺘﺮﺗﻴﺐ ﻋﻤﻠﻴﺔ ﺍﻟﺘﻨﻔﻴﺬ‬
‫{ ﺍﺳﺘﻄﺎﻉ ﺍﻹﺻﺪﺍﺭ ‪ SQL Server 2000‬ﺃﻥ ﻳﺰﻳﻞ ﺑﻌﻀﹰﺎ ﻣﻦ ﺣﺎﻻﺕ ﺍﻻﺭﺗﺒﺎﻙ‬
‫ﺗﻠﻚ ﻣﻦ ﺧﻼﻝ ﺍﻟﺴﻤﺎﺡ ﺑﺘﻮﺻﻴﻒ ﺃﻭﻝ ﻭﺁﺧﺮ ﻗﺎﺩﺡ ﻳﺘﻢ ﺇﻃﻼﻗﻬﻤﺎ ﻧﺘﻴﺠ ﹰﺔ‬
‫ﻟﻌﻤﻞ ﻣﻌﲔ‪ ،‬ﺇﻻ ﺃﻧﻪ ﱂ ﳛﻞ ﺗﻠﻚ ﺍﳌﺸﻜﻠﺔ ﺑﺸﻜﻞ ﺟﺬﺭﻱ‪ ،‬ﻓﻔﻲ ﺣﺎﻝ ﻭﺟﻮﺩ‬
‫ﺍﻟﺜﺎﱐ‬
‫ﺍﻟﻘﺎﺩﺣﲔ ﺍﻟ ﺎ‬
‫ﺗﻨﻔﻴﺬ ﺍﻟﻘﺎ‬
‫ﺃﺳﻠﻮﺏ ﻔ ﺬ‬
‫ﻓﺴﻴﺒﻘﻰ ﺃ ﻠ‬
‫ﻣﻌﺎ‪ ،‬ﻓ ﻘ‬
‫ﺇﻃﻼﻗﻬﺎ ﺎﹰ‬
‫ﻗﻮﺍﺩﺡ ﻳﺘﻢ ﺇﻃﻼﻗ ﺎ‬
‫ﺃﺃﺭﺑﻌﺔ ﻗ ﺍ‬
‫ﻭﺍﻟﺜﺎﻟﺚ ﻏﲑ ﻣﻌﺮﻭﻑ‬
‫{ ﺗُﺴﺘﺨﺪﻡ ﺍﻹﺟﺮﺍﺋﻴﺔ ‪ sp_settriggerorder‬ﻹﻋﺪﺍﺩ ﺗﺮﺗﻴﺐ ﺗﻨﻔﻴﺬ ﺍﻟﻘﻮﺍﺩﺡ‪،‬‬
‫ﲝﻴﺚ ﺗﺄﺧﺬ ﺛﻼﺛﺔ ﻣﻌﺎﻣﻼﺕ‪ ،‬ﻭﻫﻲ‪:‬‬
‫)‪sp_settriggerorder (action,order_value,trigger_name‬‬
‫‪17‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺧﻄﻮﺍﺕ ﺗﻨﻔﻴﺬ ﺍﻟﻌﻤﻠﻴﺎﺕ ﻣﻊ ﺍﻟﻘﺎﺩﺡ‬
‫ﺡ ﺍﻟﺮﺋﻴﺴﻲﻲ ﺍﳌﻌﺮﻓﺔ‬
‫‪ (١‬ﻣﻌﺎﳉﺔ ﻗﻴﻮﺩ ﺍﻻﺧﺘﺒﺎﺭ ﻭﺍﻟﺘﻔﺮﺩ ﻭﺍﳌﻔﺘﺎﺡ‬
‫ﻋﻠﻰ ﺍﳉﺪﻭﻝ‬
‫‪ (٢‬ﻣﻌﺎﳉﺔ ﻗﻴﻮﺩ ﺍﻟﺘﻜﺎﻣﻞ ﺍﳌﺮﺟﻌﻲ )ﺍﳌﻔﺎﺗﻴﺢ ﺍﳋﺎﺭﺟﻴﺔ(‬
‫‪ (٣‬ﺗﻨﻔﻴﺬ ﺃﻋﻤﺎﻝ ﺍﻟﻘﺎﺩﺡ‬
‫‪z‬‬
‫‪z‬‬
‫‪z‬‬
‫ﻛﺘﺎﺑﺔ ﺍﻟﺘﻌﺪﻳﻼﺕ ﺇﱃ ﺳﺠﻞ ﺍﳌﻨﺎﻗﻼﺕ‬
‫ﺍﻟﻘﺎﺩﺡ‬
‫ﺗﻌﻠﻴﻤﺎﺕ ﺍﻟﻘﺎ‬
‫ﺗﻨﻔﻴﺬﺬ ﻠ ﺎ‬
‫ﻔ‬
‫ﺍﻟﺘﺮﺍﺟﻊ ﻋﻦ ﺍﻟﺘﻌﺪﻳﻞ ﺃﻭ ﺗﺄﻛﻴﺪ ﺍﻟﺘﻌﺪﻳﻞ ﻭﺗﺴﺠﻴﻠﻪ ﰲ ﻗﺎﻋﺪﺓ‬
‫ﺍﳌﻌﻄﻴﺎﺕ‬
‫ﻗﻴﻮﺩ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﻼﺣﻘﺔ‬
‫ﻭﻟﻴﺲ ﻋﻠﻰﻰ ﲑ‬
‫ﺍﳌﻨﺎﻇﲑ‬
‫ﺍﳉﺪﺍﻭﻝ ﻭ ﻴﺲ‬
‫ﻋﻠﻰ ﻭ‬
‫ﺗﻌﺮﻳﻔﻬﺎ ﻰ‬
‫ﳝﻜﻦ ﺮ ﻬ‬
‫{ ﻦ‬
‫{ ﻻ ﳝﻜﻦ ﻟﻘﺎﺩﺡ ﺃﻥ ﻳﻮﺿﻊ ﻋﻠﻰ ﺃﻛﺜﺮ ﻣﻦ ﺟﺪﻭﻝ‬
‫{ ﻻ ﳝﻜﻦ ﺃﻥ ﺗﺴﺘﺨﺪﻡ ﺃﻋﻤﺪﺓ ﻣﻦ ﳕﻂ ‪text, ntext,‬‬
‫‪ image‬ﰲ ﺍﻟﻘﻮﺍﺩﺡ‬
‫ﻳﻜﻮﻥ ﺍﺳﻢ ﻟﺍﻟﻘﺎﺩﺡ ﻓﺮﻳﺪ ﰲ ﻗﺎﻋﺪﺓ‬
‫{ ﳚﺐ ﺃﺃﻥ ﻜ‬
‫ﺍﳌﻌﻄﻴﺎﺕ‬
‫‪18‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
deleted ‫ ﻭ‬inserted ‫ﺍﳉﺪﺍﻭﻝ‬
‫ ﻭ‬inserted ‫ﲔ‬
‫ﺍﳉﺪﻭﻟﲔ‬
‫ﻳﻜﻮﻥ ﻭ‬
‫ﺡﻳ ﻮ‬
‫ﻛﺘﺎﺑﺔ ﺍﻟﻘﺎﺩﺡ‬
‫{ ﻋﻨﺪ ﺑ‬
:‫ ﻣﻌﺮﻓﲔ ﻭﳛﺘﻮﻳﺎﻥ ﺍﳌﻌﻄﻴﺎﺕ ﺍﻟﺘﺎﻟﻴﺔ‬deleted
Statement
INSERT
UPDATE
DELETE
Contents of inserted
Rows added
New rows
Empty
Contents of deleted
Empty
Old rows
Rows deleted
‫ﻣﺜﺎﻝ‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
19
‫ﺍﻟﺘﺎﺑﻊ ‪UPDATE‬‬
‫ﻋﻤﻮﺩ‬
‫ﺗﺄﺛﺮ ﻮ‬
‫ﲟﻌﺮﻓﺔ ﺮ‬
‫ﻟﻠﻘﺎﺩﺡ ﺮ‬
‫ﺑﻊ ‪ UPDATE‬ﺡ‬
‫{ ﻳﻳﺴﻤﺢﺢ ﺍﻟﺘﺎﺑﻊ‬
‫ﻣﺎ ﺑﻌﻤﻠﻴﺔ ﺍﻟﺘﻌﺪﻳﻞ ﺍﳉﺎﺭﻳﺔ ﻭﻳﺴﺘﺨﺪﻡ ﻛﻤﺎ ﻳﻠﻲ‪:‬‬
‫{ )‪UPDATE (column_name‬‬
‫{ ﻭﻳﻌﻴﺪ ﺍﻟﻘﻴﻤﺔ ‪ TRUE‬ﰲ ﺣﺎﻝ ﺗﺄﺛﺮ ﺍﻟﻌﻤﻮﺩ ﺑﻌﻤﻠﻴﺔ‬
‫ﺍﻟﺘﻌﺪﻳﻞ‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﺍﳌﺘﺪﺍﺧﻠﺔ ‪Nested‬‬
‫ﺁﺧﺮ ﻳ ﻢ‬
‫ﻳﺘﻢ‬
‫ﺡ ﺮ‬
‫ﻚ ﻗﺎﺩﺡ‬
‫ﻭﻻ ﳝﳝﺘﻠﻚ‬
‫ﺡ ﺟﺪﻭﻻﹰ‬
‫{ ﻋﻨﺪﻣﺎ ﻳﻳﻌﺪّﻝﻝ ﻗﺎﺩﺡ‬
‫ﻼ‬
‫ﺗﺸﻐﻴﻞ ﺍﻟﻘﺎﺩﺡ ﺍﻟﺜﺎﱐ ﻭﻳﻌﺘﱪ ﺍﻟﻘﺎﺩﺡ ﺍﻟﺜﺎﱐ ﻣﺘﺪﺍﺧ ﹰ‬
‫ﻣﻊ ﺍﻷﻭﻝ‬
‫{ ﳝﻜﻦ ﻣﻨﻊ ﺗﺪﺍﺧﻞ ﺍﻟﻘﻮﺍﺩﺡ ﺑﻮﺿﻊ ﻗﻴﻤﺔ ﺍﳋﻴﺎﺭ‬
‫‪ nested triggers‬ﻋﻠﻰ ﺍﻟﻘﻴﻤﺔ ‪0‬‬
‫{ ﻳﺴﻤﺢ ﲟﺴﺘﻮﻯ ﺗﺪﺍﺧﻞ ﺣﱴ ‪٣٢‬‬
‫{ ﺗﻌﺘﱪ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﻌﻮﺩﻳﺔ ﺣﺎﻟﺔ ﺧﺎﺻﺔ ﻣﻦ ﺍﳌﺘﺪﺍﺧﻠﺔ‬
‫‪20‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﺒﺪﻳﻠﺔ ‪INSTEAD OF Triggers‬‬
‫{ ﻳﻘﺪﻡ ﺍﻹﺻﺪﺍﺭ ‪ SQL Server 2000‬ﻧﻮﻋﹰﺎ ﺟﺪﻳﺪﹰﺍ ﻣﻦ‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﻳﻄﻠﻖ ﻋﻠﻴﻬﺎ ﺍﺳﻢ ‪،INSTEAD OF Triggers‬‬
‫ﺗﻮﺳﱢﻊ ﻫﺬﻩ ﺍﻟﻘﻮﺍﺩﺡ ﻣﻦ ﺍﻹﻣﻜﺎﻧﻴﺎﺕ ﺍﳌﺘﺎﺣﺔ ﻟﻠﻘﻮﺍﺩﺡ‬
‫ﻼ ﻟﻘﻮﺍﺩﺡ ﺍﻟـ ‪ AFTER‬ﺍﻟﱵ ﰎ‬
‫ﺑﺸﻜﻞ ﻋﺎﻡ ﻭﺗﻘﺪﻡ ﺑﺪﻳ ﹰ‬
‫ﺍﺳﺘﺨﺪﺍﻣﻬﺎ ﰲ ﺍﻹﺻﺪﺍﺭﺍﺕ ﺍﻟﺴﺎﺑﻘﺔ‬
‫ﺒﲑ ﰲ ﺃﺳﻠﻮﺏ‬
‫ﻮﺏ‬
‫ﺡ ﺑﺸﻜﻞﻞ ﻛﺒﲑ‬
‫ﺍﻟﻘﻮﺍﺩﺡ‬
‫ﻮﻉ ﻣﻦﻦ ﻮ‬
‫{ ﳜﺘﻠﻒ ﻫﺬﺍ ﺍﻟﻨﻮﻉ‬
‫ﻋﻤﻠﻪ ﻋﻦ ﺍﻟﻨﻮﻉ ﺍﻟﺴﺎﺑﻖ‪ ،‬ﲝﻴﺚ ﻳﺘﻢ ﰲ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﺒﺪﻳﻠﺔ ‪-‬‬
‫ﻭﻛﻤﺎ ﻳﺒﺪﻭ ﻣﻦ ﺍﲰﻬﺎ‪ -‬ﺍﺳﺘﺒﺪﺍﻝ ﺍﻟﻌﺒﺎﺭﺓ ﺍﻟﱵ ﺃﺩّﺕ ﺇﱃ‬
‫ﺇﻃﻼﻕ ﺍﻟﻘﺎﺩﺡ ﺑﺎﻟﻌﺒﺎﺭﺓ ﺍﳌﻮﺻﻔﺔ ﰲ ﺍﻟﻘﺎﺩﺡ ﻧﻔﺴﻪ‬
‫ﺇﻧﺸﺎﺀ ‪INSTEAD OF Triggers‬‬
‫ﺘﺤﺩﻴﺩﺩ ﺍﺍﺴﻡ ﺫﻟﻙ ﺍﻟﻘﺎﺩﺡ‬
‫ﻤﻊ ﺘ ﺩ‬
‫ﺠﺩﻴﺩ ﻊ‬
‫ﺇﻨﺸﺎﺀ ﻗﺎﺩﺡ ﺩ ﺩ‬
‫ﺘﻌﻠﻴﻤﺔﺔ ﺇﻨﺸﺎ‬
‫ﺘﻠ‬
‫ﺘﺤﺩﻴﺩ ﺍﻟﺠﺩﻭل ﺍﻟﺫﻱ ﺴﻴﺘﻡ ﺇﻨﺸﺎﺀ ﺍﻟﻘﺎﺩﺡ ﻋﻠﻴﻪ‬
‫‪21‬‬
‫‪CREATE TRIGGER‬‬
‫‪trigger_name‬‬
‫‪ON table_name‬‬
‫ﻋﺒﺎﺭﺓ ‪ INSTEAD OF‬ﺘﺩل ﻋﻠﻰ ﻨﻭﻉ ﺍﻟﻘﺎﺩﺡ ﺍﻟﺫﻱ ﻨﻘﻭﻡ‬
‫ﺒﺒﻨﺎﺌﻪ‬
‫ﺘﻠﻲ ﺍﻟﻌﺒﺎﺭﺓ‪ INSTEAD OF‬ﻋﺒﺎﺭﺓ ﺘﺩل ﻋﻠﻰ ﺍﻟﻌﻤﻠﻴﺔ ﺍﻟﺘﻲ‬
‫ﺴﺘﺅﺩﻱ ﺇﻟﻰ ﺇﻁﻼﻕ ﺍﻟﻘﺎﺩﺡ‪ ،‬ﺃﻱ ﺇﻤﺎ ﺒﻌﺩ ﺇﻀﺎﻓﺔ ﺴﻁﺭ ﺠﺩﻴﺩ‪،‬‬
‫ﺃﻭ ﺒﻌﺩ ﺘﻌﺩﻴل ﺴﻁﺭ ﻤﻌﻴﻥ‪ ،‬ﺃﻭ ﺒﻌﺩ ﺤﺫﻑ ﺴﻁﺭ‪ ،‬ﺒﺤﻴﺙ ﻴﺘﻡ‬
‫ﺍﺴﺘﺒﺩﺍل ﺘﻠﻙ ﺍﻟﻌﻤﻠﻴﺔ ﺒﻤﺤﺘﻭﻯ ﺍﻟﻘﺎﺩﺡ ﻤﻥ ﻋﺒﺎﺭﺍﺕ‬
‫‪INSTEAD OF‬‬
‫| ‪{INSERT | UPDATE‬‬
‫}‪DELETE‬‬
‫ﻋﺒﺎﺭﺓ ﻋﻥ ﺍﻟﺘﻌﻠﻴﻤﺎﺕ ﺍﻟﺘﻲ ﺴﻴﺘﻡ ﺘﻨﻔﻴﺫﻫﺎ ﻤﻥ ﻗﺒل ﺍﻟﻘﺎﺩﺡ ﺒﻌﺩ‬
‫ﺇﻁﻼﻗﺔ‬
‫‪AS‬‬
‫‪Sql_statements‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺧﻄﻮﺍﺕ ﺗﻨﻔﻴﺬ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﺒﺪﻳﻠﺔ‬
‫‪(١‬‬
‫‪(٢‬‬
‫{‬
‫{‬
‫ﺍﺳﺘﺒﺪﺍﻝ ﺍﻟﻌﺒﺎﺭﺓ ﺍﻟﱵ ﺃﺩﺕ ﺇﱃ ﺇﻃﻼﻕ ﺍﻟﻘﺎﺩﺡ ﺑﺎﻟﻌﺒﺎﺭﺍﺕ ﺍﻟﱵ‬
‫ﻫﺬﻩ ﻟﺍﻟﻌﺒﺎﺭﺍﺕ‬
‫ﺫﻟﻚ ﻟﺍﻟﻘﺎﺩﺡ ﻭﺗﻨﻔﻴﺬﺬ ﺬ‬
‫ﻳﺘﻜﻮﻥ ﻣﻨﻬﺎ ﻟﻚ‬
‫ﻜ‬
‫ﻣﻌﺎﳉﺔ ﺍﻟﻘﻴﻮﺩ‪ :‬ﺗﺘﻢ ﺑﻌﺪ ﺫﻟﻚ ﻣﻌﺎﳉﺔ ﺍﻟﻘﻴﻮﺩ ﺍﳌﻔﺮﻭﺿﺔ ﻣﻦ‬
‫ﺍﳌﻔﺎﺗﻴﺢ ﺍﻻﻭﻟﻴﺔ ﺃﻭ ﻗﻴﻮﺩ ﺗﻔﺮﱡﺩ ﺃﻭ ﻗﻴﻮﺩ ﺍﻻﺧﺘﺒﺎﺭ‬
‫ﰲ ﺣﺎﻝ ﱂ ﻳﻘﻮﻡ ﺍﻟﻘﺎﺩﺡ ﺑﺘﻨﻔﻴﺬ ﻋﻤﻠﻴﺔ ﺍﻟﺘﻌﺪﻳﻞ ) ‪Update,‬‬
‫‪ (Delete, Insert‬ﻓﻠﻦ ﺗﺘﻢ ﺍﻟﻌﻤﻠﻴﺔ ﻣﻄﻠﻘﹰﺎ‬
‫ﺍﻟﺘﻜﺎﻣﻞ‬
‫ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﺍ ﺍﻟﻨﻮﻉ ﻣﻦ ﺍﻟﻘﻮﺍﺩﺡ ﻹﳚﺎﺩ ﺷﺮﻭﻁ ﻜ‬
‫ﻜ‬
‫ﺍﳌﺮﺟﻌﻲ – ﺣﱴ ﰲ ﺣﺎﻝ ﻋﺪﻡ ﻭﺟﻮﺩﻫﺎ‪ -‬ﻗﺒﻞ ﺗﻨﻔﻴﺬ ﻋﻤﻠﻴﺔ‬
‫ﺍﻟﺘﻌﺪﻳﻞ‬
‫ﻣﺜﺎﻝ‬
‫‪22‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺍﺳﺘﺨﺪﺍﻡ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﻼﺣﻘﺔ ﺃﻡ ﺍﻟﺒﺪﻳﻠﺔ‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﻼﺣﻘﺔ ﺃﻡﻡ‬
‫ﺡ‬
‫ﻟﻺﺟﺎﺑﺔ ﻋﻠﻰﻰ ﺍﻟﺘﺴﺎﺅﻝ ﻫﻞﻞ ﺃﺳﺘﺨﺪﻡﻡ‬
‫ﻺ‬
‫ﺍﻟﺒﺪﻳﻠﺔ ﻧﻼﺣﻆ ﻣﺎ ﻳﻠﻲ‪:‬‬
‫{ ﰲ ﺣﺎﻝ ﻛﱪ ﺍﺣﺘﻤﺎﻝ ﺭﻓﺾ ﺍﻟﺘﻌﺪﻳﻼﺕ ﻳﻔﻀﻞ ﺍﺳﺘﺨﺪﺍﻡ‬
‫ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﺒﺪﻳﻠﺔ ﻷﻬﻧﺎ ﲣﺘﱪ ﺍﻟﺸﺮﻭﻁ ﻗﺒﻞ ﺗﻨﻔﻴﺬ ﺍﻟﺘﻌﺪﻳﻞ‬
‫ﺡ ﺍﻟﻼﺣﻘﺔ ﺗﻜﻮﻥ‬
‫{ ﰲ ﺣﺎﻝ ﺻﻐﺮ ﻫﺬﺍ ﺍﻻﺣﺘﻤﺎﻝ ﻓﺎﻟﻘﻮﺍﺩﺡ‬
‫ﺃﺳﺮﻉ ﻭﺃﺳﻬﻞ‬
‫{ ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﺒﺪﻳﻠﺔ ﻣﻊ ﺍﳌﻨﺎﻇﲑ‬
‫ﺍﺳﺘﺨﺪﺍﻡ ﺍﻟﻘﻮﺍﺩﺡ ﺍﻟﻼﺣﻘﺔ ﻭﺍﻟﺒﺪﻳﻠﺔ ﺳﻮﻳﹰﺎ‬
‫ﳝﻜﻦ ﺗﻮﺍﺟﺪ ﺍﻟﻨﻮﻋﲔ ﻣﻦ ﺍﻟﻘﻮﺍﺩﺡ ﻋﻠﻰ ﺟﺪﻭﻝ ﻣﺎ‪ ،‬ﰲ ﻫﺬﻩ ﺍﳊﺎﻟﺔ‬
‫ﻳﻜﻮﻥ ﺗﺮﺗﻴﺐ ﻟﺍﻟﺘﻨﻔﻴﺬ ﻛﻛﻤﺎ ﻳﻠﻲ‬
‫ﻜ‬
‫‪ (١‬ﻃﻠﺐ ﺗﻌﻠﻴﻤﺔ ﺗﻌﺪﻳﻞ )‪(Insert, Update, Delete‬‬
‫‪ (٢‬ﺗﺸﻐﻴﻞ ﺍﻟﻘﺎﺩﺡ ﺍﻟﺒﺪﻳﻞ ﻋﻮﺿﹰﺎ ﻋﻦ ﺍﻟﺘﻌﻠﻴﻤﺔ ﻭﻛﺘﺎﺑﺔ ﺍﻟﺘﻌﺪﻳﻼﺕ‬
‫ﰲ ﺳﺠﻞ ﺍﳌﻨﺎﻗﻼﺕ‬
‫‪ (٣‬ﻣﻌﺎﳉﺔ ﺍﻟﻘﻴﻮﺩ‬
‫‪ (٤‬ﺗﺸﻐﻴﻞ ﺍﻟﻘﺎﺩﺡ ﺍﻟﻼﺣﻖ‬
‫‪ (٥‬ﰲ ﺣﺎﻝ ﱂ ﻳﻠﻐﻲ ﺍﻟﻘﺎﺩﺡ ﺍﻟﻼﺣﻖ ﺍﻟﺘﻌﺪﻳﻞ ﻳﺘﻢ ﻛﺘﺎﺑﺔ‬
‫ﺍﻟﺘﻌﺪﻳﻼﺕ ﰲ ﺍﻟﻘﺎﻋﺪﺓ‬
‫‪23‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺗﻮﺍﺑﻊ ﺍﳌﺴﺘﺨﺪﻡ ‪User Defined Functions‬‬
‫{‬
‫{‬
‫{‬
‫{‬
‫ﻳﺰﻭﺩﻧﺎ ‪ SQL Server‬ﺑﻌﺪﺩ ﻣﻦ ﺍﻟﺘﻮﺍﺑﻊ ﻣﺴﺒﻘﺔ ﺍﻟﺘﻌﺮﻳﻒ ﻣﻀﻤّﻨﺔ ﰲ ﻟﻐﺔ ‪Transact-‬‬
‫ﻹﺟﺮﺍﺀ‬
‫ﺗﻮﺍﺑﻊ ﻹ ﺍ‬
‫ﻑ ﺇﱃ ﺗ ﺍ‬
‫ﺍﶈﺎﺭﻑ‬
‫ﺳﻼﺳﻞﻞ ﺍﶈﺎ‬
‫ﺎﳉﺔ ﻼ‬
‫ﺗﻮﺍﺑﻊ ﳌﳌﻌﺎﳉﺔ‬
‫ﺎﻬﺗﺎ ﻣﻦ ﺗ ﺍ‬
‫ﻭﺍﻟﱵ ﲣﺘﻠﻒ ﻣﻬﻤﺎﻬﺗﺎ‬
‫‪ ،SQL‬ﺍﻟﱵ‬
‫ﺣﺴﺎﺑﺎﺕ ﺭﻳﺎﺿﻴﺔ‪ ،‬ﺃﻭ ﺗﻮﺍﺑﻊ ﻹﺟﺮﺍﺀ ﲢﻮﻳﻼﺕ ﰲ ﺃﳕﺎﻁ ﺍﳌﻌﻄﻴﺎﺕ ﻭﻏﲑﻫﺎ‬
‫ﻋﻠﻰ ﺍﻟﺮﻏﻢ ﻣﻦ ﺃﻥ ‪ SQL Server‬ﻳﺰﻭﺩﻧﺎ ﲟﺠﻤﻮﻋﺔ ﻛﺒﲑﺓ ﻭﻭﺍﺳﻌﺔ ﻣﻦ ﺍﻟﺘﻮﺍﺑﻊ‪ ،‬ﺇﻻ ﺃﻧﻨﺎ‬
‫ﳝﻜﻦ ﺃﻥ ﻧﺘﻌﺮّﺽ ﰲ ﻛﺜﲑ ﻣﻦ ﺍﻷﺣﻴﺎﻥ ﺇﱃ ﺣﺎﻻﺕ ﳓﺘﺎﺝ ﻓﻴﻬﺎ ﻟﺘﺎﺑﻊ ﻣﺎ ﻏﲑ ﻣﻌﺮّﻑ ﰲ‬
‫ﺗﻠﻚ ﺍﻟﻘﺎﺋﻤﺔ‬
‫ﻧﺴﺘﻄﻴﻊ ﻣﻦ ﺧﻼﻝ ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﺃﻥ ﻧﻘﻮﻡ ﺑﺈﻧﺸﺎﺀ ﻣﻌﺎﳉﺔ ﳏﺪﺩﺓ ﺗﻌﻴﺪ ﻧﺘﻴﺠﺔ ﻣﺎ‬
‫ﻮﺏ ﺍﻟﺘﻌﺎﻣﻞﻞ‬
‫ﺍﻹﺟﺮﺍﺀﺍﺕ ﺍﳌﺨﺰّﻧﺔ ﳜﺘﻠﻒ ﻋﻦﻦ ﺃﺳﻠﻮﺏ‬
‫ﺍﺣﺘﻴﺎﺟﺎﺗﻨﺎ‪ ،‬ﺇﺇﻻ ﺃﻥ ﺍﺳﺘﺨﺪﺍﻡﻡ ﻹ ﺮ‬
‫ﺐ ﻴ‬
‫ﺗﻨﺎﺳﺐ‬
‫ﻣﻊ ﺍﻟﺘﻮﺍﺑﻊ‪ ،‬ﻓﻌﻠﻰ ﺳﺒﻴﻞ ﺍﳌﺜﺎﻝ‪ ،‬ﻻ ﳝﻜﻨﻨﺎ ﺃﻥ ﻧﺴﺘﺨﺪﻡ ﺍﻹﺟﺮﺍﺀﺍﺕ ﻹﻋﺎﺩﺓ ﻗﻴﻤﺔ ﺛﺎﺑﺘﺔ‬
‫ﻧﺴﺘﻄﻴﻊ ﻣﻦ ﺧﻼﻝ ‪ SQL Server 2000‬ﺃﻥ ﻧﻘﻮﻡ ﺑﺒﻨﺎﺀ ﺗﻮﺍﺑﻊ ﻣﺴﺘﺨﺪﻡ ﻣﻌﺮّﻓﺔ ﳝﻜﻨﻬﺎ ﺃﻥ‬
‫ﺗﻌﻴﺪ ﻗﻴﻤﺔ ﺛﺎﺑﺘﺔ‪ ،‬ﲤﺎﻣﺎ ﻛﻤﺎ ﺗﻌﻤﻞ ﺗﻮﺍﺑﻊ ‪ SQL Server‬ﺍﳌﻀﻤّﻨﺔ‪ .‬ﻫﺬﺍ ﺑﺎﻹﺿﺎﻓﺔ ﺇﱃ ﺃﻥ‬
‫ﺗﻮﺍﺑﻊ ﺍﳌﺴﺘﺨﺪﻡ ﺍﳌﻌﺮّﻓﺔ ﺗﻠﻚ ﳝﻜﻨﻬﺎ ﺇﻋﺎﺩﺓ ﳎﻤﻮﻋﺔ ﺃﺳﻄﺮ ﺿﻤﻦ ﻣﺘﺤﻮﻝ ﻣﻦ ﳕﻂ ‪table‬‬
‫ﺃﻧﻮﺍﻉ ﺗﻮﺍﺑﻊ ﺍﳌﺴﺘﺨﺪﻡ‬
‫ﺗﻮﺍﺑﻊ ﺍﳌﺴﺘﺨﺪﻡﻡ ‪:‬‬
‫ﻣﻦ ﻮ ﻊ‬
‫ﺃﺳﺎﺳﻴﲔ ﻦ‬
‫ﻴﲔ‬
‫ﻧﻮﻋﲔ‬
‫{ ﻫﻨﺎﻟﻚ ﻮ ﲔ‬
‫‪ z‬ﺗﻮﺍﺑﻊ ﺍﻟﻘﻴﻤﺔ ﺍﻟﻮﺍﺣﺪﺓ ‪ Scalar Functions‬ﺍﻟﱵ ﺗﻌﻴﺪ‬
‫ﻗﻴﻤﺔ ﻭﺍﺣﺪﺓ ﺑﺴﻴﻄﺔ‬
‫‪ z‬ﺍﻟﺘﻮﺍﺑﻊ ﺍﳉﺪﻭﻟﻴﺔ ‪ Table-valued functions‬ﺍﻟﱵ ﺗﻌﻴﺪ‬
‫ﻧﻮﻋﲔ ‪:‬‬
‫ﻭﻓﻴﻬﺎ ﻮ‬
‫ﺟﺪﻭ ﹰﻻ ﻭ ﻬ‬
‫ﻭ‬
‫{‪Inline‬‬
‫{‪Multistatement‬‬
‫‪24‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﺇﻧﺸﺎﺀ ﺗﻮﺍﺑﻊ ﺍﳌﺴﺘﺨﺪﻡ ﺍﳌﻌﺮّﻓﺔ ﺑﺎﺳﺘﺨﺪﺍﻡ ‪T-SQL‬‬
‫ﺘﺤﺩﻴﺩ ﺍﺍﺴﻡﻡ ﺫﻟﻙ ﺍﺍﻟﺘﺎﺒﻊ‬
‫ﺒﻊ‬
‫ﺠﺩﻴﺩ ﻤﻊﻊ ﻴ‬
‫ﺘﺎﺒﻊ ﺠ ﻴ‬
‫ﺘﻌﻠﻴﻤﺔ ﺇﺇﻨﺸﺎﺀﺀ ﺒﻊ‬
‫ﻴ‬
‫ﻭﺍﺴﻡ ﻤﺎﻟﻜﻪ‬
‫ﺘﺤﺩﻴﺩ ﺍﻟﻤﻌﺎﻤﻼﺕ ﺍﻟﺘﻲ ﻴﻤﻜﻥ ﺘﻤﺭﻴﺭﻫﺎ ﻟﻠﺘﺎﺒﻊ‪ ،‬ﻤﻊ ﻨﻤﻁ‬
‫ﻜل ﻤﻨﻬﺎ‬
‫]‪CREATE FUNCTION [owner.‬‬
‫] ‪[owner‬‬
‫‪function_name‬‬
‫= [ ‪([{ @parameter_name scalar_datatype‬‬
‫) ] ] ‪default] } [ , … n‬‬
‫ﺍﻟﺘﺼﺭﻴﺢ ﻋﻥ ﻨﻤﻁ ﺍﻟﻘﻴﻤﺔ ﺍﻟﺘﻲ ﻴُﻌﻴﺩﻫﺎ ﺍﻟﺘﺎﺒﻊ‬
‫‪RETURNS value_datatype‬‬
‫ﻟﺘﺸﻔﻴﺭ ﻤﺤﺘﻭﻯ ﺍﻟﺘﺎﺒﻊ ﺃﻭ ﻟﺭﺒﻁﻪ ﻤﻊ ﺃﻏﺭﺍﺽ ﺃﺨﺭﻯ‬
‫ﻀﻤﻥ ﺍﻟـ‪Schema‬‬
‫‪[ WITH {ENCRYPTION,‬‬
‫}‪SCHEMABINDING‬‬
‫ﺍﻟﺘﺼﺭﻴﺢ ﻋﻥ ﺍﻟﺘﺎﺒﻊﻊ ﻭﻋﻥ ﻤﺤﺘﻭﻴﺎﺘﻪ‪،‬‬
‫ﺢ‬
‫ﻟﻠﻔﺼل ﺒﻴﻥ‬
‫ﻭﻫﺫﻩ ﺍﻟﻌﺒﺎﺭﺓ ﻟﻴﺴﺕ ﺇﺠﺒﺎﺭﻴﺔ‬
‫ﻟﻠﺘﻌﺒﻴﺭ ﻋﻥ ﺒﺩﺍﻴﺔ ﺠﺯﺀ ﺍﻟﻌﺒﺎﺭﺍﺕ ﺍﻟﻤﻜﻭّﻨﺔ ﻟﻠﺘﺎﺒﻊ‬
‫ﺘﻌﻠﻴﻤﺎﺕ ‪ T-SQL‬ﺍﻟﺘﻲ ﻴﺘﻜﻭﻥ ﻤﻨﻬﺎ ﺍﻟﺘﺎﺒﻊ‬
‫ﻗﻴﻤﺔ ﺍﻟﺨﺭﺝ ﺍﻟﺘﻲ ﻴﻌﻴﺩﻫﺎ ﺍﻟﺘﺎﺒﻊ‬
‫]‪[AS‬‬
‫‪BEGIN‬‬
‫‪Sql_statements‬‬
‫‪RETURN value‬‬
‫ﻟﻠﺘﻌﺒﻴﺭ ﻋﻥ ﺍﻨﺘﻬﺎﺀ ﺍﻟﺘﺎﺒﻊ‬
‫‪END‬‬
‫ﺗﻮﺍﺑﻊ ﺍﻟﻘﻴﻤﺔ ﺍﻟﻮﺍﺣﺪﺓ‬
‫‪SQL‬‬
‫ﺍﻟﺘﻮﺍﺑﻊ ﺍﳌﻀﻤﻨﺔ ﻣﻊﻊ ‪Q Server‬‬
‫ﺗﺸﺒﻪ ﻮ ﺑﻊ‬
‫{ ﺒ‬
‫{ ﺗﻌﻴﺪ ﻗﻴﻤﺔ ﻭﺣﻴﺪﺓ ‪Scalar‬‬
‫{ ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻣﻬﺎ ﺣﻴﺚ ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﺗﻌﺒﲑ ﺛﺎﺑﺖ‬
‫ﻃﻠﺐ ﺍﻟﺘﺎﺑﻊﻊ‬
‫ﺍﺳﻢ ﺍﳌﺎﻟﻚ ﰲ ﺐ‬
‫ﺐ ﺫﻛﺮﺮ ﻢ‬
‫{ ﳚﺐ‬
‫‪25‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫ﻣﺜﺎﻝ‬
‫ﺍﻟﺘﻮﺍﺑﻊ ﺍﳉﺪﻭﻟﻴﺔ‬
‫ﳎﻤﻮﻋﺔ ﺃﺳﻄﺮﺮ ‪rowset‬‬
‫ﺍﻟﺘﻮﺍﺑﻊ ﻮ‬
‫ﺗﻌﻴﺪ ﻫﺬﻩ ﻮ ﺑﻊ‬
‫{ ﻴ‬
‫{ ﳝﻜﻦ ﺍﺳﺘﺨﺪﺍﻡ ﻫﺬﻩ ﺍﻟﺘﻮﺍﺑﻊ ﰲ ﻋﺒﺎﺭﺓ ‪FROM‬‬
‫{ ﺗﺴﺘﺨﺪﻡ ﻫﺬﻩ ﺍﻟﺘﻮﺍﺑﻊ ﺍﻟﻜﻠﻤﺔ ‪ Table‬ﰲ ﻋﺒﺎﺭﺓ‬
‫‪Return‬‬
‫{ ﻫﻨﺎﻟﻚ ﻧﻮﻋﲔ ﻣﻨﻬﺎ ‪ Inline‬ﻭ ‪Multistatement‬‬
‫‪26‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫‪Inline Table-valued functions‬‬
‫ﺑﻊ ﺍﻟﻜﻠﻤﺔ ‪ table‬ﻓﻘﻂ‬
‫ﺍﻟﺘﻮﺍﺑﻊ‬
‫ﺍﻟﻨﻮﻉ ﻣﻦﻦ ﻮ‬
‫{ ﻳﻳﺴﺘﺨﺪﻡﻡ ﻫﺬﺍ ﻮﻉ‬
‫ﰲ ﻋﺒﺎﺭﺓ ‪ return‬ﺩﻭﻥ ﲢﺪﻳﺪ ﺗﻌﺮﻳﻒ ﺍﳉﺪﻭﻝ‬
‫{ ﺟﺴﻢ ﻫﺬﺍ ﺍﻟﺘﺎﺑﻊ ﳛﺘﻮﻱ ﻋﺒﺎﺭﺓ ‪ return‬ﻭﺣﻴﺪﺓ‬
‫ﺗﻄﻠﺐ ﺗﻌﻠﻴﻤﺔ ‪select‬‬
‫{ ﻟﻴﺲ ﻣﻦ ﺍﻟﻀﺮﻭﺭﻱ ﺫﻛﺮ ﻣﺎﻟﻚ ﺍﻟﺘﺎﺑﻊ ﻋﻨﺪ ﻃﻠﺒﻪ‬
‫ﻣﺜﺎﻝ‬
‫‪27‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬
‫‪Multistatement Table-valued functions‬‬
‫ﺑﻊ ﺍﳌﻌﺎﺩ‬
‫ﺗﻌﺮﻳﻒ ﺍﻟﺘﺎﺑﻊ‬
‫ﺗﻌﻠﻴﻤﺔ ‪ Return‬ﺮﻳ‬
‫{ ﻫﻨﺎ ﲢﺪﺩ ﻴ‬
‫{ ﺟﺴﻢ ﺍﻟﺘﺎﺑﻊ ﳛﺘﻮﻱ ﻋﺪﺓ ﺗﻌﻠﻴﻤﺎﺕ‬
‫{ ﻳﺴﺘﺨﺪﻡ ﻋﻨﺪ ﺍﳊﺎﺟﺔ ﻹﺟﺮﺍﺀ ﻣﻌﺎﳉﺎﺕ ﻣﺎ ﻗﺒﻞ ﺇﻋﺎﺩﺓ‬
‫ﺍﻟﻘﻴﻤﺔ‬
‫ﻣﺜﺎﻝ‬
‫‪28‬‬
‫إﻋﺪاد أﺳﺎﻣﺔ أﺣﻤﺪ‬