กระบวนงานที่เก็บไว้ใน SQL | ภาพรวม

ในบล็อกนี้ เราจะพูดถึงขั้นตอนการจัดเก็บใน SQL ที่คุณสามารถใช้เพื่อบันทึกชุดรหัสและใช้ซ้ำได้ทุกเมื่อที่คุณต้องการ กระบวนงานที่เก็บไว้คล้ายกับมุมมอง อย่างไรก็ตาม คุณสามารถดำเนินการกับตารางทั่วไปได้เช่น DROP, TRUNCATE, DELETE เป็นต้น ด้วยกระบวนงานที่เก็บไว้ซึ่งคุณไม่สามารถดำเนินการกับมุมมองได้

กระบวนงานที่เก็บไว้ยังได้รับการคอมไพล์ล่วงหน้าเพื่อให้ทำงานได้เร็วกว่ามุมมอง นอกจากนี้ยังลดปริมาณข้อมูลที่ส่งไปยังเซิร์ฟเวอร์ฐานข้อมูล

สารบัญ

• ไวยากรณ์สำหรับขั้นตอนการจัดเก็บใน SQL: ไม่มีพารามิเตอร์
• ไวยากรณ์สำหรับขั้นตอนการจัดเก็บใน SQL: ด้วยพารามิเตอร์
• สถานการณ์ตัวอย่างขั้นตอนการจัดเก็บใน SSMS
• สถานการณ์ตัวอย่างที่สอง
• สถานการณ์ตัวอย่างที่สาม
• ตำแหน่งของขั้นตอนการจัดเก็บที่บันทึกไว้ใน SQL
• การสร้างขั้นตอนการจัดเก็บใน SQL ด้วยพารามิเตอร์
• การสร้างขั้นตอนการจัดเก็บด้วยค่าเริ่มต้น
• บทสรุป

ไวยากรณ์สำหรับขั้นตอนการจัดเก็บใน SQL: ไม่มีพารามิเตอร์

ในการสร้างกระบวนงานที่เก็บไว้โดยไม่มีพารามิเตอร์ คุณต้องเริ่มด้วยฟังก์ชันCREATE จากนั้นเพิ่ม ฟังก์ชัน PROCพิมพ์ชื่อขั้นตอนตามหลัง และเพิ่มฟังก์ชันAS

การสร้างกระบวนงานที่เก็บไว้ในนั้นคล้ายกับการสร้างตารางและมุมมอง อย่างไรก็ตาม ความแตกต่างอยู่ในกระบวนการรับข้อมูล

ตัวอย่างเช่น หากเราต้องการรับข้อมูลจากมุมมอง เราใช้ “ SELECT * FROM view_name ” ในทางกลับกัน สำหรับโพรซีเดอร์ที่เก็บไว้ เราใช้ EXEC ซึ่งแปลว่า "ดำเนินการ" จากนั้นพิมพ์ชื่อโพรซีเดอร์ที่เก็บไว้ตามหลัง

เมื่อเราดำเนินการตามขั้นตอนที่เก็บไว้ก็จะแสดงข้อมูลตามคำแนะนำหรือคำสั่งที่คุณเพิ่มเข้าไปด้วย

ไวยากรณ์สำหรับขั้นตอนการจัดเก็บใน SQL: ด้วยพารามิเตอร์

ย้ายไปที่ การสร้างขั้น ตอนกระบวนการนี้คล้ายกับตัวอย่างก่อนหน้านี้ที่ฉันแสดงให้เห็น ข้อแตกต่างเพียงอย่างเดียวคือคุณต้องรวมพารามิเตอร์ก่อน ฟังก์ชัน ASและประเภทข้อมูลหลังจากนั้นด้วย

คุณยังสามารถใช้พารามิเตอร์ที่คุณประกาศไว้ในการสร้างโพรซีเดอร์ที่เก็บไว้ในคำสั่งของคุณ วิธีนี้ช่วยให้คุณประหยัดเวลาในการป้อนค่าหลายค่า ตัวอย่างเช่น แทนที่จะพิมพ์customerID1 , customerID2และอื่นๆ ด้วยตนเอง เราใช้@custidหรือพารามิเตอร์เพื่อหลีกเลี่ยงชุดโค้ดที่ยาว

โปรดทราบว่าเมื่อใช้หรือรวมพารามิเตอร์ สิ่งสำคัญคือต้องใช้สัญลักษณ์@ คุณยังสามารถใส่พารามิเตอร์ได้มากเท่าที่คุณต้องการ ไม่ใช่แค่ตัวเดียว นี่คือวิธีที่เราดำเนินการขั้นตอนการจัดเก็บด้วยพารามิเตอร์

อย่างที่คุณสังเกตเห็น เรายังใช้พารามิเตอร์ที่มีค่าเท่ากับ1ในการดำเนินการตามขั้นตอนที่เก็บไว้ โดยจะนำ เฉพาะข้อมูลของcusid1 เท่านั้น

สถานการณ์ตัวอย่างขั้นตอนการจัดเก็บใน SSMS

มาดูตัวอย่างเพิ่มเติมของขั้นตอนการจัดเก็บใน SQL ในตัวอย่างแรก ฉันจะสร้างตัวอย่างกระบวนงานที่เก็บไว้โดยใช้คำสั่งที่เน้นด้านล่าง

สำหรับการตั้งชื่อแบบแผนในการสร้างกระบวนงานที่เก็บไว้ เรามักจะใช้ “usp” หรือ “sp” เพื่อระบุว่าเป็นกระบวนงานที่เก็บไว้ หากคุณสงสัยว่า “usp” หมายถึงอะไร มันหมายถึงขั้นตอนการจัดเก็บที่ผู้ใช้กำหนด 

ต่อไป เราจะเขียนแบบสอบถามสำหรับกระบวนงานที่เก็บไว้เพื่อดำเนินการเมื่อดำเนินการ

ข้อความค้นหาในตัวอย่างก่อน หน้านี้จะลบตารางชื่อdbo.stageOrders จากนั้นจะสร้างdbo.stageOrders ใหม่ ด้วยข้อมูลจากตาราง  Sales.SalesOrderHeader

มาสร้างกระบวนงานที่เก็บไว้นี้ โดยเน้นโค้ดต่อไปนี้แล้วคลิกที่ปุ่มExecute

หลังจากนั้นคุณควรเห็นข้อความเช่นนี้

จากนั้นเราจะดำเนินการตามขั้นตอนการจัดเก็บโดยเรียกใช้EXEC usp_TEST

เมื่อการดำเนินการ ของกระบวนงาน ที่เก็บไว้usp_TEST ตอนนี้ dbo.stageOrdersควรมีข้อมูลจากSales.SalesOrderHeader ตรวจสอบเนื้อหาของdbo.stageOrdersโดยใช้คำสั่งด้านล่าง

ด้วยเหตุนี้ ข้อมูลเหล่านี้จึงเป็นข้อมูลที่เราสร้างขึ้นในdbo.stageOrdersตามคำสั่งที่เราเพิ่มลงในกระบวนงานที่เก็บไว้

สถานการณ์ตัวอย่างที่สอง

มาดูตัวอย่างอื่นกัน ครั้งนี้ฉันไม่ต้องการทิ้ง ตาราง dbo.stageOrdersและสร้างตารางเดิมใหม่ด้วยข้อมูลที่มาจากตารางSales.SalesOrderHeader

แต่ฉันต้องการให้ข้อมูลจากตาราง Sales.SalesOrderHeader แสดงเมื่อใดก็ตามที่ฉันดำเนินการตามขั้นตอนการจัดเก็บ “ usp_TEST ”

ในการดำเนินการนี้ ฉันต้องทำการเปลี่ยนแปลงกระบวนงานที่เก็บไว้ที่เพิ่งสร้างขึ้นโดยใช้คำสั่งALTER

ในตัวอย่างนี้ เราใช้ คำสั่ง ALTERซึ่งใช้ในการแก้ไขขั้นตอนการจัดเก็บของเรา คุณยังสามารถใช้สิ่งนี้กับมุมมองและตารางได้อีกด้วย เมื่อเราเรียกใช้โค้ดในภาพหน้าจอด้านบนแล้ว โค้ดควรเปลี่ยนการสืบค้นที่ต้องดำเนินการตามขั้นตอนการจัดเก็บของเรา

ครั้งนี้จะไม่สร้างตารางใหม่เหมือนที่เราทำก่อนหน้านี้ หากเราดำเนินการ “ usp_TEST ” จะเป็นการนำข้อมูลจากตารางSales.SalesOrderHeader

สถานการณ์ตัวอย่างที่สาม

ลองทำอีกตัวอย่างหนึ่ง ในครั้งนี้ เราจะนำตาราง dbo.stageOrders มาใช้ซ้ำ ซึ่งเรามีในตัวอย่างแรกของการสร้างกระบวนงานที่จัดเก็บไว้ในSSMS เราจะเริ่มต้นด้วยตัวอย่างนี้โดยเลือก ตาราง dbo.stageOrders 

ผลลัพธ์เมื่อเลือกข้อมูลทั้งหมดจาก dbo.stageOrders ควรเป็นเช่นนี้

ตอน นี้เรามาล้างตาราง dbo.stageOrders โดยดำเนินการusp_TEST เราสามารถทำได้โดยเพิ่มคำสั่งต่อไปนี้

หลังจากเพิ่ม คำ สั่งTRUNCATE TABLEแล้ว เราต้องอัปเดตusp_TEST

หลังจากนั้น ข้อความแจ้งว่าCommands are complete Completelyควรแสดงขึ้น จากนั้นเราจะดำเนินการusp_TESTอีกครั้ง

เมื่อดำเนินการusp_TESTจะแสดงบันทึกทั้งหมดจากSales.SalesOrderHeaderและล้างตาราง  dbo.stageOrders ด้วย

ในการตรวจสอบว่าตาราง dbo.stageOrders ว่างเปล่าหรือไม่ เราจำเป็นต้องเลือกโดยใช้คำสั่งต่อไปนี้และดำเนินการ

เมื่อรันโค้ดด้านบน เราจะเห็นว่าตาราง dbo.stageOrders ว่างเปล่า นี่เป็นเพราะ คำ สั่งTRUNCATE TABLEที่เราใช้ในการอัปเดตusp_TEST

ตำแหน่งของขั้นตอนการจัดเก็บที่บันทึกไว้ใน SQL

หากคุณต้องการดูว่าขั้นตอนการจัดเก็บในSQL ถูกบันทึกไว้ที่ใด ให้ไปที่ แผง Object Explorerทางด้านซ้ายแล้วคลิกไอคอน “ + ” ก่อนฐานข้อมูลที่คุณกำลังทำงานอยู่

จากนั้นคลิกขวาที่Programmabilityแล้วเลือกRefresh

ขยาย โฟลเดอร์หรือกลุ่ม ความสามารถในการตั้งโปรแกรมโดยคลิกที่ไอคอน “ + ” จากนั้นขยายกลุ่มขั้นตอนการจัดเก็บโดยทำขั้นตอนเดียวกัน ภายในกลุ่ม Stored Procedure คุณควรเห็นdbo.usp_TEST

หากคุณต้องการตรวจสอบว่าคำสั่งหรือแบบสอบถามใดที่โพรซีเดอร์ที่เก็บไว้เฉพาะใน SQL กำลังทำงาน คุณสามารถคลิกขวาที่โพรซีเดอร์ที่เก็บไว้ และทำตามขั้นตอนในภาพหน้าจอด้านล่าง

หลังจากนั้นก็จะเปิดกระบวนงานที่จัดเก็บไว้ในแท็บอื่นซึ่งคุณสามารถดูคำสั่งที่ให้ไว้ได้ นี่คือ ลักษณะของ dbo.usp_TESTเมื่อเปิดขึ้นมา

อย่างที่คุณเห็น มีคำสั่งเริ่มต้นก่อนหน้าคำสั่งCREATE คุณสามารถลบออกได้หากต้องการ 

ตอนนี้คุณรู้วิธีตรวจสอบว่าคำสั่งใดที่โพรซีเดอร์ที่เก็บไว้ใน SQLกำลังทำงาน

การสร้างขั้นตอนการจัดเก็บใน SQL ด้วยพารามิเตอร์

ต่อไป เราจะสร้างขั้นตอนการจัดเก็บด้วยพารามิเตอร์ ตัวอย่างเช่น เราจะใช้โค้ดต่อไปนี้เพื่อสร้างกระบวนงานที่เก็บไว้ใหม่

ในโค้ดตัวอย่าง ฉันใช้กระบวนการเดียวกันใน การสร้างโพรซีเดอร์ที่เก็บไว้ชื่อusp_GetCustomer จากนั้น ฉันเพิ่มพารามิเตอร์ซึ่งเป็น@CustomerIDด้วยประเภทอินพุตของINT

โปรดทราบว่าเมื่อคุณเพิ่มพารามิเตอร์ในการสร้างโพรซีเดอร์ที่จัดเก็บแล้ว คุณควรระบุพารามิเตอร์ทุกครั้งเมื่อคุณจะดำเนินการคำสั่ง

มาดูกันว่าจะเกิดอะไรขึ้นหากเราดำเนินการusp_GetCustomerโดยไม่ระบุพารามิเตอร์

เมื่อดำเนินการusp_GetCustomerโดยไม่มีพารามิเตอร์ ข้อความแสดงข้อผิดพลาดปรากฏขึ้น นี่คือลักษณะถ้าเราดำเนินการusp_GetCustomerด้วยพารามิเตอร์

ด้วยพารามิเตอร์ที่ให้มา เราสามารถได้รับผลลัพธ์ที่เหมาะสมในการดำเนินการตามขั้นตอนที่เก็บไว้ ของเรา

การสร้างขั้นตอนการจัดเก็บด้วยค่าเริ่มต้น

หากคุณต้องการหลีกเลี่ยงการรับข้อความแสดงข้อผิดพลาดในการดำเนินการขั้นตอนที่จัดเก็บด้วยพารามิเตอร์ คุณสามารถตั้งค่าเริ่มต้นที่จะทำหน้าที่เป็นพารามิเตอร์เริ่มต้น

ตัวอย่างเช่น เราจะสร้างกระบวนงานที่เก็บไว้ชื่อusp_GetOrdersByYear

จากนั้นฉันจะเพิ่มพารามิเตอร์@OrderYearด้วยประเภทอินพุตของ “ INT ”และค่าเริ่มต้นที่เท่ากับ2011

หากเราดำเนินการusp_GetOrdersByYear โดยไม่ ได้ระบุพารามิเตอร์ไว้ ระบบจะแสดงบันทึกที่มีปี2011

ในทางกลับกัน หากเราดำเนินการusp_GetOrdersByYearโดยมี2014 เป็นพารามิเตอร์ที่ กำหนดก็ควรแสดงบันทึกด้วยปี2014

นี่คือวิธีที่คุณใช้ขั้นตอนการจัดเก็บในงานการจัดการข้อมูลรายวันของคุณ

มีประโยคในฟังก์ชันรวม SQL
ISNULL ฟังก์ชัน SQL ในงบกรณี
การดึงข้อมูล SQL โดยใช้ OFFSET และ FETCH

บทสรุป

เมื่อคำนึงถึงทุกสิ่ง คุณได้เรียนรู้ว่ากระบวนงานที่จัดเก็บใน SQL คืออะไรและวัตถุประสงค์ของมัน เรายังได้กล่าวถึง คำสั่ง ALTERที่ใช้เพื่อทำการเปลี่ยนแปลงหรืออัปเดตในกระบวนงานที่เก็บไว้ในปัจจุบัน

นอกจากนี้ คุณได้เรียนรู้ว่ามีวิธีการต่างๆ ในการสร้างกระบวนงานที่จัดเก็บใน SQL และเรียนรู้วิธีหลีกเลี่ยงการรับข้อผิดพลาดเมื่อดำเนินการกระบวนงานที่จัดเก็บโดยระบุค่าเริ่มต้น

สิ่งสำคัญที่สุดคือ คุณได้เรียนรู้การใช้โพรซีเดอร์ที่เก็บไว้สำหรับจัดเก็บชุดคำสั่งเพื่อหลีกเลี่ยงการรันชุดโค้ดที่ยาวซ้ำๆ อย่าลืมใช้สัญลักษณ์ “ @ ” เมื่อระบุพารามิเตอร์

สิ่งที่ดีที่สุด

ฮาฟิซ