การประกาศขั้นตอน

สร้างขั้นตอน [({ใน|ออก|เข้า} [,…])]
[ชุดผลลัพธ์แบบไดนามิก ]
เริ่ม [อะตอมมิก]

จบ

คำหลัก
- ใน (อินพุต) – พารามิเตอร์อินพุต
- OUT (เอาต์พุต) – พารามิเตอร์เอาต์พุต
- INOUT – อินพุตและเอาต์พุต รวมถึงฟิลด์ (ไม่มีพารามิเตอร์)
- ชุดผลลัพธ์แบบไดนามิกบ่งชี้ว่าขั้นตอนสามารถเปิดเคอร์เซอร์ตามจำนวนที่ระบุซึ่งจะยังคงเปิดอยู่หลังจากที่ขั้นตอนกลับมา

หมายเหตุ
ไม่แนะนำให้ใช้พารามิเตอร์จำนวนมากในขั้นตอนการจัดเก็บ (โดยหลักแล้วจะมีตัวเลขและสตริงอักขระจำนวนมาก) เนื่องจากเครือข่ายและสแต็กโอเวอร์โหลด ในทางปฏิบัติ ภาษาถิ่นที่มีอยู่ของ Transact-SQL, PL/SQL และ Informix มีความแตกต่างอย่างมีนัยสำคัญจากมาตรฐาน ทั้งในการประกาศและการใช้พารามิเตอร์ การประกาศตัวแปร และการเรียกรูทีนย่อย Microsoft ขอแนะนำให้ใช้การประมาณต่อไปนี้เพื่อประเมินขนาดแคชของกระบวนงานที่เก็บไว้:
=(จำนวนผู้ใช้พร้อมกันสูงสุด)*(ขนาดของแผนการดำเนินการที่ใหญ่ที่สุด)*1.25 การกำหนดขนาดของแผนปฏิบัติการในหน้าต่างๆ สามารถทำได้โดยใช้คำสั่ง: DBCC MEMUSAGE

การเรียกขั้นตอน

ใน DBMS ที่มีอยู่จำนวนมาก กระบวนการจัดเก็บจะถูกเรียกโดยใช้ตัวดำเนินการ:

ขั้นตอนดำเนินการ [(][)]

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

ตัวอย่างการประกาศขั้นตอน

สร้างขั้นตอน Proc1 AS // ประกาศขั้นตอน
ประกาศเคอร์เซอร์ Cur1 สำหรับชื่อที่เลือก เมืองจากพนักงานขาย โดยที่เรตติ้ง>200 // ประกาศเคอร์เซอร์
OPEN Cur1 //เปิดเคอร์เซอร์
FETCH NEXT FROM Cur1 // อ่านข้อมูลจากเคอร์เซอร์
ในขณะที่ @@Fetch_Status=0
เริ่ม
ดึงข้อมูลถัดไปจาก Cur1
จบ
ปิด Cur1 // ปิดเคอร์เซอร์
จัดสรร Cur1
ดำเนินการ Proc1 // เรียกใช้โพรซีเดอร์

ความแตกต่าง
สามารถสร้างรูทีนย่อยสองรูทีนที่มีชื่อเดียวกันได้ในสคีมาเดียวกัน หากพารามิเตอร์ของรูทีนย่อยทั้งสองมีความแตกต่างกันเพียงพอที่จะแยกความแตกต่างได้ หากต้องการแยกความแตกต่างระหว่างสองรูทีนที่มีชื่อเดียวกันในสคีมาเดียวกัน แต่ละรูทีนจะได้รับชื่อทางเลือกและไม่ซ้ำกัน (ชื่อเฉพาะ) ชื่อดังกล่าวอาจถูกระบุอย่างชัดเจนเมื่อมีการกำหนดรูทีนย่อย เมื่อเรียกรูทีนย่อยที่มีชื่อเหมือนกันหลายชื่อ การกำหนดรูทีนย่อยที่ต้องการจะดำเนินการในหลายขั้นตอน:
- เริ่มแรก ขั้นตอนทั้งหมดที่มีชื่อที่ระบุจะถูกกำหนดไว้ และหากไม่มี ฟังก์ชันทั้งหมดที่มีชื่อที่กำหนดก็จะถูกกำหนดไว้
- เฉพาะรูทีนย่อยที่ผู้ใช้มีสิทธิ์ดำเนินการ (EXECUTE) เท่านั้นที่จะถูกเก็บไว้เพื่อการวิเคราะห์เพิ่มเติม
- สำหรับพวกเขาจะมีการเลือกจำนวนพารามิเตอร์ที่สอดคล้องกับจำนวนอาร์กิวเมนต์การโทร มีการตรวจสอบประเภทข้อมูลที่ระบุของพารามิเตอร์และตำแหน่ง
- หากมีรูทีนย่อยเหลืออยู่มากกว่าหนึ่งรูทีน จะมีการเลือกรูทีนย่อยที่มีชื่อที่สั้นกว่า
ในทางปฏิบัติ ใน Oracle polymorphism รองรับฟังก์ชันที่ประกาศในแพ็คเกจเท่านั้น DB@ - ในสคีมาที่แตกต่างกัน และใน Sybase และ MS SQL Server ห้ามโอเวอร์โหลด

การลบและการเปลี่ยนแปลงขั้นตอน
หากต้องการลบขั้นตอน ให้ใช้ตัวดำเนินการ:

หากต้องการเปลี่ยนขั้นตอน ให้ใช้ตัวดำเนินการ:

เปลี่ยนแปลงขั้นตอน [([{ใน|ออก|เข้า}])]
เริ่ม [อะตอมมิก]

จบ

สิทธิพิเศษในการดำเนินการตามขั้นตอน

ให้สิทธิ์ดำเนินการ ถึง |สาธารณะ [ด้วยตัวเลือกการให้ทุน]

ขั้นตอนของระบบ
DBMS จำนวนมาก (รวมถึง SQL Server) มีชุดขั้นตอนการจัดเก็บระบบในตัวเฉพาะที่คุณสามารถใช้เพื่อวัตถุประสงค์ของคุณเอง

เมื่อใดที่ฉันควรใช้กระบวนงานที่เก็บไว้ และเมื่อใดที่ฉันควรใช้มุมมองใน SQL Server

สิทธิ์ช่วยให้เราสามารถสร้างแบบสอบถามแบบไดนามิกที่เราสามารถส่งพารามิเตอร์?

อันไหนเร็วที่สุดและเร็วกว่าอันอื่นบนพื้นฐานอะไร?

มุมมองหรือขั้นตอนการจัดเก็บจะเก็บหน่วยความจำไว้อย่างถาวรหรือไม่

หมายความว่าอย่างไรถ้ามีคนบอกว่ามุมมองสร้างตารางเสมือนและขั้นตอนสร้างสารบัญ?

โปรดแจ้งให้เราทราบเกี่ยวกับจุดเพิ่มเติมหากมี

โซลูชันที่รวบรวมจากเว็บของ "อะไรคือความแตกต่างระหว่างขั้นตอนการจัดเก็บและมุมมอง"

วิวคือ เสมือนโต๊ะ. คุณสามารถรวมหลายตารางในมุมมองเดียว และใช้มุมมองนั้นเพื่อนำเสนอข้อมูลเสมือนว่าข้อมูลมาจากตารางเดียว

Stored Procedure ใช้พารามิเตอร์เพื่อทำหน้าที่...ไม่ว่าจะอัปเดตและแทรกข้อมูลหรือส่งคืนค่าแต่ละค่าหรือชุดข้อมูล

การสร้างมุมมองและขั้นตอนการจัดเก็บ - มีข้อมูลบางอย่างจาก Microsoft ว่าควรใช้เมื่อใดและเพราะเหตุใด

สมมติว่าฉันมีสองตาราง:

คอลัมน์ tbl_user: .user_id, .user_name, .user_pw

คอลัมน์ tbl_profile: .profile_id, .user_id .profile_description

ดังนั้นหากฉันกำลังสืบค้นจากตาราง ALOT เหล่านี้... แทนที่จะทำการเข้าร่วมในทุก ๆ sql ฉันจะกำหนดมุมมองดังนี้:

สร้าง ดู vw_user_profile AS เลือก A.user_id, B.profile_description จาก tbl_user A เหลือการรวม tbl_profile B บน A.user_id = b.user_id GO

ดังนั้นในอนาคตหากฉันต้องการสืบค้น profile_description โดย userid... สิ่งที่ฉันต้องทำคือ

เลือกprofile_descriptionจากvw_user_profileโดยที่ user_id = @ID

รหัสนี้สามารถใช้ในขั้นตอนการจัดเก็บ เช่น:

สร้างขั้นตอน dbo.getDesc @ID int AS เริ่มต้น SELECT profile_description จาก vw_user_profile WHERE user_id = @ID END GO

หลังจากนั้นฉันสามารถโทรได้

Dbo.getDesc25

และฉันจะได้รับคำอธิบายสำหรับผู้ใช้ id 25 โดยที่ 25 คือพารามิเตอร์ของคุณ

แน่นอนว่ายังมีอะไรอีกมากมาย แต่นั่นเป็นเพียงแนวคิดพื้นฐานเท่านั้น

ก่อนอื่นคุณต้องเข้าใจว่าทั้งสองสิ่งต่างกัน ขั้นตอนที่เก็บไว้เหมาะที่สุดสำหรับคำสั่ง INSERT-UPDATE-DELETE และ Views ใช้สำหรับคำสั่ง SELECT และคุณควรใช้ทั้งสองอย่าง

ในมุมมอง คุณไม่สามารถเปลี่ยนแปลงข้อมูลได้

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

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

สามารถใช้มุมมองใน Stored Procedure ได้ แต่ไม่สามารถใช้ Stored Procedure ใน Views...!

ขั้นตอนการจัดเก็บจะใช้เมื่อ SQL ธรรมดาไม่เพียงพอ ขั้นตอนการจัดเก็บประกอบด้วยตัวแปร ลูป และการเรียกขั้นตอนการจัดเก็บอื่นๆ มันเป็นภาษาโปรแกรม ไม่ใช่ภาษาคิวรี

การดูเป็นแบบคงที่ คิดว่าตารางเหล่านี้เป็นตารางใหม่ที่มีเค้าโครงเฉพาะ และข้อมูลในตารางจะถูกสร้างขึ้นทันทีโดยใช้แบบสอบถามที่คุณสร้างขึ้น เช่นเดียวกับตาราง SQL อื่นๆ คุณสามารถจัดเรียงและกรองโดยใช้ WHERE , GROUP BY และ ORDER BY

มันขึ้นอยู่กับสิ่งที่คุณกำลังทำ

มันขึ้นอยู่กับฐานข้อมูล มุมมองธรรมดาเพียงแค่เรียกใช้แบบสอบถามและกรองผลลัพธ์ แต่ฐานข้อมูลเช่น Oracle อนุญาตให้คุณสร้างมุมมอง "ที่เป็นรูปธรรม" ซึ่งโดยพื้นฐานแล้วคือตารางที่อัปเดตโดยอัตโนมัติเมื่อข้อมูลพื้นฐานของมุมมองเปลี่ยนแปลง

มุมมองที่เป็นรูปธรรมช่วยให้คุณสร้างดัชนีในคอลัมน์มุมมอง (โดยเฉพาะในคอลัมน์จากการคำนวณที่ไม่มีอยู่ในฐานข้อมูล)

ฉันไม่เข้าใจสิ่งที่คุณกำลังพูดถึง

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

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

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

มุมมองจะเร็วขึ้นเนื่องจากจะแสดงข้อมูลจากตารางที่อ้างอิง ในขณะที่ขั้นตอนการจัดเก็บดำเนินการคำสั่ง SQL

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

@Patrick ถูกต้องกับสิ่งที่เขาพูด แต่เพื่อตอบคำถามอื่น ๆ ของคุณ View จะสร้างตัวเองในหน่วยความจำและขึ้นอยู่กับประเภทของการรวมข้อมูลและหากการรวมกลุ่มใด ๆ เสร็จสิ้น ก็อาจเป็นมุมมองที่ค่อนข้างหิว

ขั้นตอนที่เก็บไว้ทำการประมวลผลทั้งหมดโดยใช้ Temp Hash Table เช่น #tmpTable1 หรือในหน่วยความจำโดยใช้ @tmpTable1 ขึ้นอยู่กับสิ่งที่คุณต้องการจะพูด

กระบวนการจัดเก็บจะคล้ายกับฟังก์ชัน แต่ถูกเรียกตามชื่อโดยตรง แทนฟังก์ชันที่ใช้จริงภายในคำขอนั้นเอง

แน่นอนว่าตารางหน่วยความจำเวลาส่วนใหญ่จะเร็วกว่า เว้นแต่ว่าคุณจะดึงข้อมูลจำนวนมาก

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

สร้าง ดู vw_user_profile AS เลือก A.user_id, B.profile_description จาก tbl_user A เหลือการรวม tbl_profile B บน A.user_id = b.user_id

ฉันสามารถอัปเดตข้อมูลได้... ดังตัวอย่าง ฉันสามารถทำสิ่งเหล่านี้ได้...

อัพเดต vw_user_profile ตั้งprofile_description="Manager" โดยที่ user_id=4

อัปเดต tbl_profile ตั้งprofile_description="Manager" โดยที่ user_id=4

คุณไม่สามารถ INSERT ลงในมุมมองนี้ได้ เนื่องจากไม่มีฟิลด์ทั้งหมดในตารางทั้งหมด และฉันถือว่า PROFILE_ID เป็นคีย์หลักและไม่สามารถเป็น NULL ได้ อย่างไรก็ตาม บางครั้งคุณอาจต้องการ INSERT ลงในมุมมอง...

ฉันได้สร้างมุมมองสำหรับตารางที่มีอยู่โดยใช้...

สร้าง View Junk เป็น SELECT * from

แทรกลงในค่าขยะ (รหัสชื่อ) ("glyn", "Glyn Roberts"), ("Mary", "Maryann Roberts")

ลบออกจากขยะ โดยที่ ID>4

ทั้ง INSERT และ DELETE ทำงานในกรณีนี้

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

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

นอกเหนือจากความคิดเห็นข้างต้น ฉันอยากจะเพิ่มหมายเหตุบางประการเกี่ยวกับ Views

1. การแสดงแทนสามารถใช้เพื่อซ่อนความซับซ้อนได้ ลองนึกภาพสถานการณ์ที่มีคน 5 คนกำลังทำงานในโครงการหนึ่ง แต่มีเพียงหนึ่งคนเท่านั้นที่ดีเกินไปกับฐานข้อมูล เช่น การรวมที่ซับซ้อน ในสถานการณ์เช่นนี้ เขาสามารถสร้างมุมมองที่สมาชิกในทีมคนอื่นๆ สามารถสืบค้นได้อย่างง่ายดายในขณะที่พวกเขาสืบค้นตารางใดตารางหนึ่ง
2. การรักษาความปลอดภัยสามารถนำไปใช้ได้อย่างง่ายดายโดย Views สมมุติว่าเรา พนักงานตารางที่มีคอลัมน์ที่ละเอียดอ่อนเช่น เงินเดือน , หมายเลขเอสเอสเอ็น- คอลัมน์เหล่านี้ไม่ควรปรากฏแก่ผู้ใช้ที่ไม่ได้รับอนุญาตให้ดู ในกรณีนี้เราสามารถสร้างมุมมองที่จะเลือกคอลัมน์ในตารางที่ไม่จำเป็นต้องมีการอนุญาต เช่น ชื่อ , อายุและฯลฯ โดยไม่เปิดเผยคอลัมน์ที่มีช่องโหว่ (เช่น เงินเดือน ฯลฯ ที่เรากล่าวถึงก่อนหน้านี้) ตอนนี้เราสามารถลบสิทธิ์ในการสืบค้นตารางได้โดยตรง พนักงานและเพียงเก็บสิทธิ์การอ่านไว้ในมุมมอง ดังนั้นเราจึงสามารถใช้การรักษาความปลอดภัยโดยใช้ Views

การใช้ฟังก์ชัน DBMS ที่จัดเก็บไว้เพื่อใช้ตรรกะทางธุรกิจหรือบางส่วนถือเป็นอุปสรรค์มาโดยตลอด ด้านหนึ่งของสิ่งกีดขวางคือ DBA และโปรแกรมเมอร์ฐานข้อมูล ส่วนอีกด้านหนึ่งคือนักพัฒนาแบ็กเอนด์
ฉันเสี่ยงที่จะเกิดความโกรธเคืองจากทั้งสองค่าย แต่ฉันยังคงสรุปข้อดีและข้อเสีย และสรุปความคิดของฉันว่าเมื่อใดจึงคุ้มค่าที่จะเขียนโค้ดในฟังก์ชันที่เก็บไว้ และเมื่อใดควรนำออกไปข้างนอก

เริ่มจากข้อโต้แย้งต่อ:

ตรรกะทางธุรกิจที่เปื้อน

จริงๆ แล้วนี่ไม่ใช่ปัญหากับ DBMS และ HF ในฐานะเครื่องมือ แต่เป็นปัญหาจากการใช้งานที่ไม่ถูกต้อง โปรแกรมเมอร์ฐานข้อมูลอาจต้องการอธิบายตรรกะทั้งหมดของการดำเนินการที่ถูกนำไปใช้ในฟังก์ชันที่เก็บไว้ - แน่นอนว่าข้อมูลทั้งหมดอยู่ที่นั่นแล้ว หากโปรแกรมเมอร์ยอมจำนนต่อสิ่งล่อใจและผู้จัดการของเขาไม่คัดค้านปัญหาในอนาคตอาจเกิดขึ้นกับความแคบของอินเทอร์เฟซกับระบบภายนอก (เช่นกับแอปพลิเคชันเซิร์ฟเวอร์) - จะต้องเพิ่มพารามิเตอร์ใหม่ ตรรกะซับซ้อน ฯลฯ สิ่งนี้อาจนำไปสู่การปรากฏของ HF ที่ "ซ้ำกัน" โดยมีฟังก์ชันการทำงานที่แตกต่างกันเล็กน้อย

ความขาดแคลนของภาษา DBMS

มีสิ่งนั้นอยู่ ภาษาดั้งเดิมสำหรับการเขียน HF pl/sql, t-sql, pl/pgsql นั้นค่อนข้างดั้งเดิมเมื่อเทียบกับภาษาวัตถุประสงค์ทั่วไปสมัยใหม่ เป็นที่น่าสังเกตว่ามีความเป็นไปได้ที่จะเขียน HF ในภาษาขั้นสูง เช่น Java ใน Oracle หรือ Python ใน postgresql

การไม่ทนต่อฟังก์ชั่นที่เก็บไว้

นี่หมายถึงความไม่ลงรอยกันของภาษาถิ่นของภาษาขั้นตอนของ DBMS ที่แตกต่างกัน มัลติแพลตฟอร์มนั้นถูกต้อง - ด้วยการสนับสนุนระบบปฏิบัติการและสถาปัตยกรรมที่แตกต่างกันใน DBMS เองและความเป็นอิสระของภาษาในตัวจากแพลตฟอร์มภายนอก การตัดสินใจขึ้นอยู่กับลักษณะเฉพาะของโครงการอีกครั้ง หากโปรเจ็กต์สามารถทำซ้ำได้ และคุณไม่ได้ควบคุมแพลตฟอร์ม (ตัวอย่างคลาสสิกคือ CMS) คุณจะต้องมีความสะดวกในการพกพา และการใช้ HF มีแต่จะเพิ่มความปวดหัวเท่านั้น หากโครงการไม่ซ้ำกัน หรือการนำไปใช้งานจะเกิดขึ้นในลักษณะที่เป็นเอกภาพ (เช่น ในสาขาต่างๆ ของบริษัทเดียวกัน) คุณก็สามารถลืมเรื่องการไม่ยอมรับระหว่าง DBMS ต่างๆ ได้

ขาดทักษะที่จำเป็นในทีมและ "ต้นทุน" ที่สูงของผู้เชี่ยวชาญที่เกี่ยวข้อง

ในความคิดของฉัน นี่เป็นข้อโต้แย้งที่ร้ายแรงที่สุดต่อการใช้ HF ทุกอย่างขึ้นอยู่กับขนาดของโครงการ โดยทั่วไปแล้ว การใช้โค้ดที่จัดเก็บไว้บนฝั่ง DBMS นั้นสมเหตุสมผลสำหรับโปรเจ็กต์ระดับองค์กรขนาดกลางและขนาดใหญ่ หากโปรเจ็กต์เล็กลง เกมก็ไม่คุ้มกับเทียน หากโปรเจ็กต์มีขนาดใหญ่และโอเวอร์โหลด สถาปัตยกรรมที่มี HF และ RDBMS จะประสบปัญหาในการปรับขนาด - ที่นี่จำเป็นต้องใช้ที่เก็บข้อมูลเฉพาะและวิธีการประมวลผลข้อมูล

ตอนนี้ข้อดี:

ความเร็ว

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

การซ่อนโครงสร้างข้อมูล

เมื่อระบบซอฟต์แวร์เติบโตและพัฒนา สคีมาข้อมูลสามารถและควรเปลี่ยนแปลงได้ อินเทอร์เฟซซอฟต์แวร์ HF ที่ออกแบบมาอย่างดีจะช่วยให้คุณเปลี่ยนสคีมาข้อมูลโดยไม่ต้องเปลี่ยนรหัสของแอปพลิเคชันภายนอก (ซึ่งอาจมีหลายอย่าง) สิ่งนี้ส่งผลให้เกิดการแยกบทบาทของนักพัฒนาที่ทำงานกับฐานข้อมูลและรู้โครงสร้างของฐานข้อมูล และผู้พัฒนาแอปพลิเคชันภายนอกที่ควรรู้เฉพาะ API ที่ให้มาเท่านั้น เมื่อใช้ Dynamic SQL บนฝั่งแอปพลิเคชัน สำหรับการแยกดังกล่าว จะมีการแนะนำเลเยอร์เพิ่มเติมของนามธรรมซอฟต์แวร์ฐานข้อมูลและ ORM ต่างๆ

การจัดการสิทธิ์การเข้าถึงที่ยืดหยุ่น

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

โอกาสในการฉีด SQL น้อยลง

เมื่อใช้ไดนามิก SQL ในส่วนของโปรแกรมไคลเอนต์ โปรแกรมไคลเอนต์จะส่งคำสั่ง SQL ไปยัง DBMS ในรูปแบบของสตริงที่ถูกสร้างขึ้นล่วงหน้าในโค้ด เมื่อสร้างสตริงเหล่านี้ โปรแกรมเมอร์จะต้องใช้ความระมัดระวังเป็นอย่างยิ่งเพื่อป้องกันความเป็นไปได้ในการแก้ไขคำสั่ง SQL โดยไม่ได้ตั้งใจ เมื่อใช้ HF SQL โค้ดบนฝั่งแอปพลิเคชันมักจะเป็นแบบคงที่ และดูเหมือนเป็นการเรียก HF แบบธรรมดา พารามิเตอร์ที่ถูกส่งผ่านไม่อยู่ในสตริง แต่ส่งผ่านตัวยึดตำแหน่ง (:ตัวแปร) ผ่านกลไกการเชื่อมโยง แน่นอนว่าสิ่งนี้ไม่ได้ยกเว้นความเป็นไปได้ของการฉีด SQL อย่างสมบูรณ์ (ท้ายที่สุดคุณสามารถจัดการเพื่อเชื่อมต่อสตริงที่ส่งเป็นพารามิเตอร์กับข้อความของแบบสอบถาม SQL ที่ดำเนินการแบบไดนามิกใน HF) แต่จะลดโอกาสลงอย่างมาก

การใช้ SQL ซ้ำ

เมื่อใช้ตรรกะในการทำงานกับข้อมูลในเลเยอร์ที่เก็บไว้ เราจะได้โมเดลลำดับชั้นที่คุ้นเคยของการนำโค้ด SQL มาใช้ซ้ำ
เมื่อใช้ไดนามิก SQL การใช้คิวรีซ้ำเป็นเรื่องยาก
ตัวอย่างเช่น ให้มีระบบ A ที่ใช้ HF และระบบ B ที่ใช้ Dynamic SQL ทั้งสองระบบมีฟังก์ชันสำหรับรับราคาของสินค้า get_price ในกรณี A นี่คือฟังก์ชันหรือมุมมองที่เก็บไว้ ในกรณี B สมมติว่าเป็นขั้นตอนจาวาที่ดำเนินการแบบสอบถาม SQL ผ่าน JDBC มีภารกิจ - รับต้นทุนรวมของสินค้าในคลังสินค้า ในกรณี A เรารวม get_price โดยตรงกับแบบสอบถามที่ได้รับรายการสินค้าในคลังสินค้า (หาก get_price เป็นมุมมองหรือ HF ใน SQL เช่นใน PostgreSQL เครื่องมือเพิ่มประสิทธิภาพจะขยายแบบสอบถามแบบอินไลน์ - ดังนั้นจึงได้รับหนึ่งแบบสอบถามที่รวดเร็ว หาจำนวนเงิน)
ในกรณี B มีสองตัวเลือก - วิ่งผ่านเคอร์เซอร์พร้อมกับเลือกสินค้าในคลังสินค้าและเรียก get_price n ครั้ง (ซึ่งหมายความว่าการเลือกทั้งหมดจะต้องส่งผ่านเครือข่ายไปยังไคลเอนต์) หรือลืมการใช้ซ้ำและเขียน แบบสอบถามย่อยที่ซ้ำกับแบบสอบถามที่เขียนไว้แล้วใน get_price ตัวเลือกทั้งสองไม่ดี

การดีบัก SQL อย่างง่าย

การดีบักนั้นง่ายขึ้น (เมื่อเทียบกับโค้ดภายนอก + sql ของโพรซีเดอร์ที่ต่างกัน)
ในระบบที่มี SQL แบบไดนามิก (ORM ใดๆ) แม้แต่งานง่ายๆ ในการค้นหาชิ้นส่วนที่มีปัญหาของ SQL ก็อาจเป็นเรื่องยาก
การตรวจสอบความหมายและวากยสัมพันธ์ของ SQL ในขั้นตอนการคอมไพล์
ความสามารถในการกำหนดโปรไฟล์ฟังก์ชันและค้นหาจุดคอขวด
ความสามารถในการติดตามระบบที่กำลังทำงานอยู่และกำลังทำงานอยู่
การควบคุมการขึ้นต่อกันอัตโนมัติ - เมื่อคำจำกัดความของออบเจ็กต์มีการเปลี่ยนแปลง เอนทิตีที่ขึ้นต่อกันจะใช้งานไม่ได้

เมื่อใดจึงควรเขียนตรรกะทางธุรกิจลงในฐานข้อมูล?

หากความเร็วในการประมวลผลข้อมูลเป็นสิ่งสำคัญ

การประมวลผลข้อมูลโดยตรงที่จัดเก็บมักจะช่วยเพิ่มความเร็วในการประมวลผลได้อย่างมาก การเพิ่มประสิทธิภาพ เช่น การรวมกลุ่มในระดับการจัดเก็บข้อมูลสามารถทำได้ - ข้อมูลจากอาร์เรย์จะไม่ถูกถ่ายโอนไปยังเซิร์ฟเวอร์ DBMS ด้วยซ้ำ ไม่ต้องพูดถึงไคลเอนต์

เมื่อความสมบูรณ์และความสม่ำเสมอของข้อมูลเป็นสิ่งสำคัญ

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

ข้อมูลมีโครงสร้างที่ซับซ้อนแต่มีโครงสร้างที่ดี

โครงสร้างที่แบนและประกบกันอย่างหลวมๆ มักไม่ต้องการเครื่องมือประมวลผลมากมายที่ DBMS นำเสนอ สำหรับสิ่งเหล่านี้ คุณสามารถใช้พื้นที่จัดเก็บคีย์-ค่าที่รวดเร็วเป็นพิเศษและการแคชในหน่วยความจำได้
โครงสร้างลำดับชั้นและเครือข่ายที่ซับซ้อนและแน่นแฟ้นเป็นตัวบ่งชี้ที่ชัดเจนว่าความรู้ RDBMS ของคุณจะมีประโยชน์!

เมื่อไหร่จะปล่อยโค้ด?

การทำงานกับข้อมูลภายนอก

หากลักษณะเฉพาะของระบบมีข้อมูลที่เข้ามาเพื่อประมวลผลจากภายนอก (จากเซ็นเซอร์ จากระบบอื่น) มากกว่าข้อมูลที่จัดเก็บไว้ในฐานข้อมูล ข้อดีหลายประการของฐานข้อมูลในฐานะแพลตฟอร์มการเขียนโปรแกรมก็จะสูญหายไป ปรากฎว่าการประมวลผลข้อมูลขาเข้าจากภายนอกและบันทึกผลลัพธ์ลงในฐานข้อมูลทำได้ง่ายกว่าการยัดทุกอย่างลงในฐานข้อมูลก่อนแล้วจึงประมวลผล มีการปฏิบัติตามหลักการเดียวกันที่นี่ - เพื่อประมวลผลข้อมูลที่ใกล้เคียงกับแหล่งที่มามากที่สุดซึ่งเราได้พูดถึงข้างต้นเกี่ยวกับการประมวลผลข้อมูลที่เก็บไว้ในฐานข้อมูลแล้ว

อัลกอริทึมที่ซับซ้อน

เป็นการดีกว่าที่จะเขียนอัลกอริธึมตัวเลขที่ซับซ้อนหรือปรับให้เหมาะสมที่สุดในภาษาที่เหมาะสมกว่าสำหรับจุดประสงค์นี้ ภาษา RDBMS ในตัวนั้นทรงพลังมาก (ในแง่ที่ว่าเป็นภาษาระดับสูงไม่ยืดหยุ่น) แต่ด้วยเหตุนี้จึงมีค่าใช้จ่ายสูง

โหลดสูง

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

สรุปได้ว่าไม่มีอัลกอริธึมที่ชัดเจน ในแต่ละครั้ง การตัดสินใจยังคงอยู่กับสถาปนิกและผู้จัดการ และจะกำหนดว่าโครงการจะประสบปัญหาเกี่ยวกับสภาพการแข่งขันและข้อมูล NoSQL ที่ไม่สอดคล้องกัน ปัญหาเกี่ยวกับประสิทธิภาพและการดีบักคำสั่ง ORM หรือจะประสบปัญหาในการปรับขนาด DBMS เมื่อใช้ฟังก์ชันที่เก็บไว้ ดังนั้นควรตัดสินใจให้ถูกต้อง :)

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

การแนะนำ

หลายๆ คนเชื่อว่ากระบวนการเหล่านี้คล้ายคลึงกับขั้นตอนต่างๆ (ตามลำดับ ยกเว้น MS SQL) บางทีนี่อาจเป็นเรื่องจริง พวกเขามีพารามิเตอร์ที่คล้ายกันและสามารถสร้างค่าที่คล้ายกันได้ ยิ่งไปกว่านั้นในบางกรณีก็สัมผัสกัน ตัวอย่างเช่น จะรวมกับฐานข้อมูล DDL และ DML รวมถึงฟังก์ชันผู้ใช้ (ชื่อรหัส UDF)

ในความเป็นจริง ขั้นตอนการจัดเก็บ SQL มีข้อดีมากมายที่ทำให้กระบวนการเหล่านี้แตกต่างจากกระบวนการที่คล้ายคลึงกัน ความปลอดภัย ความยืดหยุ่นในการเขียนโปรแกรม ประสิทธิภาพการทำงาน - ทั้งหมดนี้ดึงดูดผู้ใช้ที่ทำงานกับฐานข้อมูลเพิ่มมากขึ้นเรื่อยๆ ความนิยมสูงสุดของขั้นตอนเกิดขึ้นในปี 2548-2553 เมื่อมีการเปิดตัวโปรแกรมจาก Microsoft ชื่อ "SQL Server Management Studio" ด้วยความช่วยเหลือนี้ ทำให้การทำงานกับฐานข้อมูลกลายเป็นเรื่องง่าย ใช้งานได้จริง และสะดวกยิ่งขึ้น ในแต่ละปีสิ่งนี้ได้รับความนิยมในหมู่โปรแกรมเมอร์ วันนี้มันเป็นโปรแกรมที่คุ้นเคยอย่างยิ่งซึ่งสำหรับผู้ใช้ที่ "สื่อสาร" กับฐานข้อมูลนั้นเทียบเท่ากับ Excel

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

ในการใช้เทคโนโลยีนี้ในการทำงานกับข้อมูล มีภาษาการเขียนโปรแกรมหลายภาษา ซึ่งรวมถึง ตัวอย่างเช่น PL/SQL จาก Oracle, PSQL ในระบบ InterBase และ Firebird รวมถึง Microsoft Transact-SQL แบบคลาสสิก ทั้งหมดนี้ได้รับการออกแบบสำหรับการสร้างและดำเนินการขั้นตอนการจัดเก็บซึ่งช่วยให้โปรเซสเซอร์ฐานข้อมูลขนาดใหญ่สามารถใช้อัลกอริธึมของตนเองได้ นี่เป็นสิ่งจำเป็นเช่นกันเพื่อให้ผู้ที่จัดการข้อมูลดังกล่าวสามารถปกป้องวัตถุทั้งหมดจากการเข้าถึงโดยไม่ได้รับอนุญาตโดยบุคคลที่สาม และด้วยเหตุนี้ การสร้าง การแก้ไข หรือการลบข้อมูลบางอย่าง

ผลผลิต

ออบเจ็กต์ฐานข้อมูลเหล่านี้สามารถตั้งโปรแกรมได้หลายวิธี ทำให้ผู้ใช้สามารถเลือกประเภทของวิธีการใช้ที่เหมาะสมที่สุด ประหยัดแรงและเวลา นอกจากนี้ กระบวนการยังได้รับการประมวลผลด้วยตัวมันเอง ซึ่งช่วยหลีกเลี่ยงค่าใช้จ่ายจำนวนมากในการสื่อสารระหว่างเซิร์ฟเวอร์และผู้ใช้ นอกจากนี้ โมดูลยังสามารถตั้งโปรแกรมใหม่และเปลี่ยนแปลงไปในทิศทางที่ต้องการได้ตลอดเวลา เป็นเรื่องที่น่าสังเกตว่าความเร็วที่เปิดตัวโพรซีเดอร์ SQL ที่เก็บไว้: กระบวนการนี้เกิดขึ้นเร็วกว่ากระบวนการอื่นที่คล้ายคลึงกันซึ่งทำให้สะดวกและเป็นสากล

ความปลอดภัย

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

การถ่ายโอนข้อมูล

ความสัมพันธ์ระหว่างขั้นตอนการจัดเก็บ SQL และแอปพลิเคชันไคลเอ็นต์คือการใช้พารามิเตอร์และค่าที่ส่งคืน หลังไม่จำเป็นต้องส่งข้อมูลไปยังขั้นตอนการจัดเก็บ แต่ข้อมูลนี้ (ส่วนใหญ่ตามคำขอของผู้ใช้) ได้รับการประมวลผลสำหรับ SQL หลังจากที่กระบวนงานที่เก็บไว้เสร็จสิ้นการทำงาน มันจะส่งแพ็กเก็ตข้อมูลกลับ (แต่เป็นทางเลือกอีกครั้ง) ไปยังแอปพลิเคชันที่เรียกมัน โดยใช้วิธีต่างๆ ที่สามารถใช้เพื่อเรียกขั้นตอนที่เก็บไว้ของ SQL หรือส่งคืน เช่น:

การถ่ายโอนข้อมูลโดยใช้พารามิเตอร์ประเภทเอาต์พุต

การส่งผ่านข้อมูลโดยใช้ตัวดำเนินการส่งคืน

การส่งผ่านข้อมูลโดยใช้โอเปอเรเตอร์ที่เลือก

ตอนนี้เรามาดูกันว่ากระบวนการนี้มีลักษณะอย่างไรจากภายใน

1. สร้างขั้นตอนการจัดเก็บ EXEC ใน SQL

คุณสามารถสร้างขั้นตอนใน MS SQL (Managment Studio) หลังจากสร้างขั้นตอนแล้ว ขั้นตอนดังกล่าวจะแสดงอยู่ในโหนดที่ตั้งโปรแกรมได้ของฐานข้อมูล ซึ่งขั้นตอนการสร้างจะดำเนินการโดยผู้ปฏิบัติงาน ในการดำเนินการ ขั้นตอนการจัดเก็บ SQL จะใช้กระบวนการ EXEC ที่มีชื่อของออบเจ็กต์นั้นเอง

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

ประเด็นก็คือเนื้อความสามารถมีตัวแปรภายในเครื่องได้ และตัวแปรเหล่านี้ก็อยู่ในตัวที่เกี่ยวข้องกับกระบวนการด้วย กล่าวอีกนัยหนึ่งสามารถดูได้ภายในเนื้อความของขั้นตอน Microsoft SQL Server เท่านั้น ขั้นตอนการจัดเก็บในกรณีนี้จะถือว่าอยู่ในเครื่อง

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

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

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

ร่างกายไม่ควรสร้างความประทับใจที่ผิดพลาดต่อวัตถุ

ร่างกายไม่ควรสร้างสิ่งกระตุ้นใดๆ

2. การตั้งค่าตัวแปรในเนื้อหาของโพรซีเดอร์

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

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

ผู้ใช้มักถามคำถาม: “จะกำหนดค่าหลายค่าในคำสั่งเดียวในเนื้อหาของโพรซีเดอร์ได้อย่างไร” ดี. เป็นคำถามที่น่าสนใจ แต่ทำได้ง่ายกว่าที่คุณคิดมาก คำตอบ: การใช้คู่เช่น "Select Var = value" คุณสามารถใช้คู่เหล่านี้ได้โดยคั่นด้วยเครื่องหมายจุลภาค

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

ในตัวอย่างก่อนหน้านี้ กระบวนการใช้พารามิเตอร์สองตัว ซึ่งในภาษาอังกฤษจะเรียกว่า @State และ @City ตามอัตภาพ ประเภทข้อมูลตรงกับประเภทที่กำหนดไว้ในแอปพลิเคชัน เนื้อความของโพรซีเดอร์มีตัวแปรภายใน @TotalAuthors และตัวแปรนี้ใช้เพื่อแสดงจำนวนผู้เขียน ถัดไปคือส่วนการเลือกข้อความค้นหาซึ่งจะคำนวณทุกอย่าง สุดท้าย ค่าที่คำนวณได้จะถูกพิมพ์ในหน้าต่างเอาท์พุตโดยใช้คำสั่งการพิมพ์

วิธีดำเนินการขั้นตอนการจัดเก็บใน SQL

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

ขั้นตอนอาจเบี่ยงเบนไปจากปกติเล็กน้อย ทุกอย่างเหมือนกับในตัวอย่างก่อนหน้า แต่เฉพาะพารามิเตอร์เท่านั้นที่จะถูกเลื่อน นั่นคือ @City จะถูกเก็บไว้ก่อน และ @State จะถูกเก็บไว้ถัดจากค่าเริ่มต้น โดยปกติพารามิเตอร์เริ่มต้นจะถูกเน้นแยกกัน ขั้นตอนการจัดเก็บ SQL จะถูกส่งผ่านเป็นเพียงพารามิเตอร์ ในกรณีนี้ พารามิเตอร์ "UT" จะแทนที่ค่าเริ่มต้น "CA" ในการดำเนินการครั้งที่สอง จะมีการส่งผ่านค่าอาร์กิวเมนต์เพียงค่าเดียวสำหรับพารามิเตอร์ @City และพารามิเตอร์ @State จะใช้ค่าเริ่มต้นเป็น "CA" โปรแกรมเมอร์ที่มีประสบการณ์แนะนำว่าตัวแปรทั้งหมดควรอยู่ที่ส่วนท้ายของรายการพารามิเตอร์ตามค่าเริ่มต้น มิฉะนั้น การดำเนินการจะไม่สามารถทำได้ จากนั้นคุณจะต้องดำเนินการโดยส่งผ่านอาร์กิวเมนต์ที่มีชื่อ ซึ่งมีความยาวและซับซ้อนมากขึ้น

4. ขั้นตอนการจัดเก็บเซิร์ฟเวอร์ SQL: วิธีการส่งคืน

มีสามวิธีที่สำคัญในการส่งข้อมูลในขั้นตอนการจัดเก็บที่เรียกว่า พวกเขาอยู่ด้านล่าง:

ส่งกลับค่าของกระบวนงานที่เก็บไว้

เอาต์พุตพารามิเตอร์ขั้นตอนการจัดเก็บ;

การเลือกหนึ่งในขั้นตอนการจัดเก็บ

4.1 การส่งคืนค่าจาก SQL Stored Procedure

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

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

4.2 เอาต์พุตพารามิเตอร์ SQL Stored Procedure

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

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

นอกจากนี้ ในสถานการณ์ก่อนหน้านี้ มีการประกาศตัวแปรสองตัวเพื่อดูค่าที่ MS SQL Server จัดเก็บโพรซีเดอร์ที่ตั้งค่าไว้ในพารามิเตอร์เอาต์พุต จากนั้นดำเนินการตามขั้นตอนโดยระบุค่าปกติของพารามิเตอร์ “CA” พารามิเตอร์ต่อไปนี้เป็นพารามิเตอร์เอาต์พุต ดังนั้นตัวแปรที่ประกาศจะถูกส่งไปตามลำดับที่ระบุ โปรดทราบว่าเมื่อส่งผ่านตัวแปร คีย์เวิร์ดเอาต์พุตจะถูกตั้งค่าไว้ที่นี่ด้วย หลังจากขั้นตอนเสร็จสมบูรณ์ ค่าที่ส่งคืนโดยพารามิเตอร์เอาต์พุตจะแสดงในกล่องข้อความ

4.3 การเลือกหนึ่งในขั้นตอนการจัดเก็บ SQL

เทคนิคนี้ใช้เพื่อส่งคืนชุดค่าเป็นตารางข้อมูล (RecordSet) ไปยังขั้นตอนการเรียกที่เก็บไว้ ในตัวอย่างนี้ ขั้นตอนการจัดเก็บ SQL ที่มีพารามิเตอร์ @AuthID จะสอบถามตาราง Authors โดยการกรองระเบียนที่ส่งคืนโดยใช้พารามิเตอร์ @AuthId นั้น คำสั่ง Select ตัดสินใจว่าควรส่งคืนอะไรให้กับผู้เรียกกระบวนงานที่เก็บไว้ เมื่อดำเนินการขั้นตอนที่เก็บไว้ AuthId จะถูกส่งกลับ ขั้นตอนนี้จะส่งกลับเพียงระเบียนเดียวหรือไม่มีเลยเสมอ แต่ขั้นตอนการจัดเก็บไม่มีข้อจำกัดใดๆ ในการส่งคืนระเบียนมากกว่าหนึ่งรายการ ไม่ใช่เรื่องแปลกที่จะเห็นตัวอย่างที่มีการส่งคืนข้อมูลโดยใช้พารามิเตอร์ที่เลือกซึ่งเกี่ยวข้องกับตัวแปรที่คำนวณโดยการให้ผลรวมหลายรายการ

สรุปแล้ว

โพรซีเดอร์ที่เก็บไว้เป็นโมดูลโปรแกรมที่ค่อนข้างจริงจังซึ่งส่งคืนหรือผ่าน และยังตั้งค่าตัวแปรที่จำเป็นด้วยแอปพลิเคชันไคลเอนต์ เนื่องจากขั้นตอนการจัดเก็บรันตัวเองบนเซิร์ฟเวอร์ จึงสามารถหลีกเลี่ยงการแลกเปลี่ยนข้อมูลจำนวนมากระหว่างเซิร์ฟเวอร์และแอปพลิเคชันไคลเอ็นต์ (สำหรับการคำนวณบางอย่าง) ได้ สิ่งนี้ช่วยให้คุณลดภาระบนเซิร์ฟเวอร์ SQL ซึ่งแน่นอนว่าจะเป็นประโยชน์ต่อเจ้าของเซิร์ฟเวอร์ หนึ่งในประเภทย่อยคือขั้นตอนการจัดเก็บ T SQL แต่การศึกษามีความจำเป็นสำหรับผู้ที่สร้างฐานข้อมูลที่น่าประทับใจ นอกจากนี้ยังมีความแตกต่างจำนวนมากที่อาจมีประโยชน์เมื่อศึกษาขั้นตอนการจัดเก็บ แต่นี่เป็นสิ่งจำเป็นสำหรับผู้ที่วางแผนจะมีส่วนร่วมในการเขียนโปรแกรมรวมถึงมืออาชีพด้วย

ขั้นตอนการจัดเก็บเป็นแพ็คเกจคำสั่ง Transact-SQL ชนิดพิเศษที่สร้างขึ้นโดยใช้ภาษา SQL และส่วนขยายขั้นตอน ข้อแตกต่างที่สำคัญระหว่างแพ็คเกจและขั้นตอนการจัดเก็บก็คือขั้นตอนหลังถูกจัดเก็บเป็นวัตถุฐานข้อมูล กล่าวอีกนัยหนึ่ง กระบวนการจัดเก็บจะถูกจัดเก็บไว้ในฝั่งเซิร์ฟเวอร์เพื่อปรับปรุงประสิทธิภาพและความสม่ำเสมอของงานที่ทำซ้ำได้

โปรแกรมฐานข้อมูลรองรับขั้นตอนการจัดเก็บและขั้นตอนของระบบ Stored Procedures ถูกสร้างขึ้นในลักษณะเดียวกับออบเจ็กต์ฐานข้อมูลอื่นๆ ทั้งหมด เช่น โดยใช้ภาษา DDL ขั้นตอนของระบบจัดทำโดย Database Engine และสามารถใช้เพื่อเข้าถึงและแก้ไขข้อมูลในแค็ตตาล็อกระบบ

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

ขั้นตอนการจัดเก็บจะถูกคอมไพล์ล่วงหน้าก่อนที่จะจัดเก็บเป็นอ็อบเจ็กต์ในฐานข้อมูล รูปแบบพรีคอมไพล์ของโพรซีเดอร์จะถูกเก็บไว้ในฐานข้อมูลและใช้ทุกครั้งที่มีการเรียก คุณสมบัติของกระบวนงานที่เก็บไว้นี้ให้ประโยชน์ที่สำคัญในการกำจัด (ในเกือบทุกกรณี) การคอมไพล์ขั้นตอนที่ทำซ้ำ และบรรลุการปรับปรุงประสิทธิภาพที่สอดคล้องกัน คุณสมบัติของขั้นตอนการจัดเก็บนี้ยังมีผลดีต่อปริมาณข้อมูลที่แลกเปลี่ยนระหว่างระบบฐานข้อมูลและแอปพลิเคชันอีกด้วย โดยเฉพาะอย่างยิ่ง การเรียกขั้นตอนการจัดเก็บที่มีขนาดหลายพันไบต์อาจต้องใช้น้อยกว่า 50 ไบต์ เมื่อผู้ใช้หลายคนทำงานซ้ำๆ โดยใช้ขั้นตอนการจัดเก็บ ผลสะสมของการประหยัดเหล่านี้อาจมีนัยสำคัญมาก

ขั้นตอนการจัดเก็บสามารถใช้เพื่อวัตถุประสงค์ดังต่อไปนี้:

เพื่อสร้างบันทึกการดำเนินการกับตารางฐานข้อมูล

การใช้ขั้นตอนการจัดเก็บจะให้ระดับการควบคุมความปลอดภัยที่เหนือกว่าการรักษาความปลอดภัยที่ได้รับจากการใช้คำสั่ง GRANT และ REVOKE ซึ่งให้สิทธิ์การเข้าถึงที่แตกต่างกันแก่ผู้ใช้ สิ่งนี้เป็นไปได้เนื่องจากการอนุญาตในการดำเนินการขั้นตอนการจัดเก็บนั้นไม่ขึ้นอยู่กับการอนุญาตในการแก้ไขออบเจ็กต์ที่มีอยู่ในขั้นตอนการจัดเก็บ ดังที่อธิบายไว้ในส่วนถัดไป

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

การสร้างและดำเนินการขั้นตอนการจัดเก็บ

ขั้นตอนที่เก็บไว้ถูกสร้างขึ้นโดยใช้คำสั่ง สร้างขั้นตอนซึ่งมีรูปแบบดังนี้:

สร้าง PROC proc_name [((@param1) type1 [ แปรผัน] [= default1] )] (, ...) AS แบทช์ | ชื่อภายนอก method_name แบบแผนไวยากรณ์

พารามิเตอร์ schema_name ระบุชื่อของสคีมาที่กำหนดโดยเจ้าของกระบวนงานที่เก็บไว้ที่สร้างขึ้น พารามิเตอร์ proc_name ระบุชื่อของกระบวนงานที่เก็บไว้ พารามิเตอร์ @param1 เป็นพารามิเตอร์ของขั้นตอน (อาร์กิวเมนต์ที่เป็นทางการ) ซึ่งประเภทข้อมูลถูกกำหนดโดยพารามิเตอร์ type1 พารามิเตอร์ของโพรซีเดอร์เป็นแบบโลคัลภายในโพรซีเดอร์ เช่นเดียวกับตัวแปรโลคัลที่อยู่ในแพ็กเกจ พารามิเตอร์ขั้นตอนคือค่าที่ผู้เรียกส่งผ่านไปยังขั้นตอนการใช้งาน พารามิเตอร์ default1 ระบุค่าเริ่มต้นสำหรับพารามิเตอร์ขั้นตอนที่เกี่ยวข้อง (ค่าเริ่มต้นสามารถเป็น NULL ได้)

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

ดังที่ได้กล่าวไว้ก่อนหน้านี้ รูปแบบพรีคอมไพล์ของโพรซีเดอร์จะถูกเก็บไว้ในฐานข้อมูลและใช้งานทุกครั้งที่มีการเรียก หากจำเป็นต้องรวบรวมโพรซีเดอร์ที่เก็บไว้ทุกครั้งที่มีการเรียกด้วยเหตุผลบางประการ ให้ใช้เมื่อประกาศโพรซีเดอร์ พร้อมตัวเลือกการคอมไพล์- การใช้ตัวเลือก WITH RECOMPILE จะลบล้างข้อดีที่สำคัญที่สุดประการหนึ่งของกระบวนงานที่เก็บไว้ นั่นคือ การปรับปรุงประสิทธิภาพเนื่องจากการคอมไพล์ครั้งเดียว ดังนั้น ตัวเลือก WITH RECOMPILE ควรใช้เฉพาะเมื่อมีการปรับเปลี่ยนวัตถุฐานข้อมูลที่ใช้โดยกระบวนงานที่เก็บไว้บ่อยครั้งเท่านั้น

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

ตามค่าเริ่มต้น เฉพาะสมาชิกของบทบาทเซิร์ฟเวอร์คงที่ของ sysadmin และบทบาทฐานข้อมูลคงที่ db_owner หรือ db_ddladmin เท่านั้นที่สามารถใช้คำสั่ง CREATE PROCEDURE ได้ แต่สมาชิกของบทบาทเหล่านี้สามารถมอบหมายสิทธิ์นี้ให้กับผู้ใช้รายอื่นได้โดยใช้คำสั่ง ขั้นตอนการสร้างทุน.

ตัวอย่างด้านล่างแสดงวิธีการสร้างขั้นตอนการจัดเก็บอย่างง่ายเพื่อทำงานกับตารางโครงการ:

ใช้ SampleDb; ไปสร้างขั้นตอน เพิ่มงบประมาณ (@percent INT=5) AS อัปเดต งบประมาณชุดโครงการ = งบประมาณ + งบประมาณ * @percent/100;

ตามที่ระบุไว้ก่อนหน้านี้ หากต้องการแยกสองแพ็กเก็ต ให้ใช้ ไปคำแนะนำ- คำสั่ง CREATE PROCEDURE ไม่สามารถรวมกับคำสั่ง Transact-SQL อื่น ๆ ในชุดเดียวกันได้ กระบวนการจัดเก็บ เพิ่มงบประมาณ จะเพิ่มงบประมาณสำหรับโครงการทั้งหมดตามเปอร์เซ็นต์ที่กำหนด ซึ่งกำหนดโดยพารามิเตอร์ @percent ขั้นตอนนี้ยังกำหนดค่าเปอร์เซ็นต์เริ่มต้น (5) ที่ใช้ถ้าไม่มีอาร์กิวเมนต์นี้เมื่อกระบวนการทำงาน

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

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

กระบวนการจัดเก็บชั่วคราวในเครื่องสามารถดำเนินการได้โดยผู้ใช้ที่สร้างมันขึ้นมา และเฉพาะในขณะที่เชื่อมต่อกับฐานข้อมูลที่มันถูกสร้างขึ้นเท่านั้น ผู้ใช้ทุกคนสามารถดำเนินการขั้นตอนชั่วคราวส่วนกลางได้ แต่จนกว่าการเชื่อมต่อสุดท้ายที่จะดำเนินการ (โดยปกติคือการเชื่อมต่อของผู้สร้างขั้นตอน) จะสิ้นสุดลง

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

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. แบบแผนไวยากรณ์

ด้วยข้อยกเว้นของพารามิเตอร์ return_status พารามิเตอร์ทั้งหมดของคำสั่ง EXECUTE มีความหมายเชิงตรรกะเดียวกันกับพารามิเตอร์เดียวกันของคำสั่ง CREATE PROCEDURE พารามิเตอร์ return_status ระบุตัวแปรจำนวนเต็มที่เก็บสถานะการส่งคืนของขั้นตอน สามารถกำหนดค่าให้กับพารามิเตอร์โดยใช้ค่าคงที่ (ค่า) หรือตัวแปรเฉพาะที่ (@variable) ลำดับของค่าของพารามิเตอร์ที่มีชื่อนั้นไม่สำคัญ แต่ต้องระบุค่าของพารามิเตอร์ที่ไม่มีชื่อตามลำดับที่กำหนดไว้ในคำสั่ง CREATE PROCEDURE

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

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

ใช้ SampleDb; ดำเนินการ เพิ่มงบประมาณ 10;

คำสั่ง EXECUTE ในตัวอย่างนี้ดำเนินการขั้นตอนการจัดเก็บ เพิ่มงบประมาณ ซึ่งจะเพิ่มงบประมาณของโครงการทั้งหมด 10%

ตัวอย่างด้านล่างแสดงวิธีการสร้างขั้นตอนการจัดเก็บเพื่อประมวลผลข้อมูลในตาราง Employee และ Works_on:

ขั้นตอนตัวอย่าง ModifyEmpId แสดงให้เห็นถึงการใช้ขั้นตอนการจัดเก็บเป็นส่วนหนึ่งของกระบวนการรักษาความสมบูรณ์ของการอ้างอิง (ในกรณีนี้คือระหว่างตาราง Employee และ Works_on) ขั้นตอนการจัดเก็บที่คล้ายกันสามารถนำมาใช้ภายในคำจำกัดความของทริกเกอร์ ซึ่งจริงๆ แล้วให้ความสมบูรณ์ในการอ้างอิง

ตัวอย่างต่อไปนี้แสดงการใช้ส่วนคำสั่ง OUTPUT ในกระบวนงานที่เก็บไว้:

ขั้นตอนที่เก็บไว้นี้สามารถดำเนินการได้โดยใช้คำแนะนำต่อไปนี้:

ประกาศ @quantityDeleteEmployee INT; ดำเนินการ DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee เอาท์พุต; PRINT N"พนักงานที่ถูกลบ: " + แปลง (nvarchar (30), @quantityDeleteEmployee);

ขั้นตอนนี้จะนับจำนวนโครงการที่พนักงานที่มีหมายเลขบุคลากร @empId กำลังทำงานอยู่ และกำหนดค่าผลลัพธ์ให้กับพารามิเตอร์ ©counter หลังจากที่แถวทั้งหมดสำหรับหมายเลขบุคลากรที่กำหนดถูกลบออกจากตาราง Employee และ Works_on แล้ว ค่าที่คำนวณได้จะถูกกำหนดให้กับตัวแปร @quantityDeleteEmployee

ค่าพารามิเตอร์จะถูกส่งกลับไปยังขั้นตอนการเรียกเฉพาะเมื่อมีการระบุตัวเลือก OUTPUT ในตัวอย่างข้างต้น ขั้นตอน DeleteEmployee จะส่งผ่านพารามิเตอร์ @counter ไปยังขั้นตอนการเรียก ดังนั้นกระบวนงานที่เก็บไว้จึงส่งคืนค่าให้กับระบบ ดังนั้นจึงต้องระบุพารามิเตอร์ @counter ทั้งในตัวเลือก OUTPUT เมื่อประกาศขั้นตอนและในคำสั่ง EXECUTE เมื่อเรียกใช้

WITH RESULTS SETS clause ของคำสั่ง EXECUTE

ใน SQL Server 2012 คุณต้องป้อนคำสั่ง EXECUTE ด้วยคำสั่งชุดผลลัพธ์ซึ่งเมื่อตรงตามเงื่อนไขบางประการ คุณสามารถเปลี่ยนรูปแบบของชุดผลลัพธ์ของขั้นตอนการจัดเก็บได้

สองตัวอย่างต่อไปนี้จะช่วยอธิบายประโยคนี้ ตัวอย่างแรกคือตัวอย่างเบื้องต้นที่แสดงให้เห็นว่าผลลัพธ์อาจมีลักษณะอย่างไรเมื่อละเว้นคำสั่งย่อย WITH RESULTS SETS:

ขั้นตอน EmployeesInDept เป็นขั้นตอนง่ายๆ ที่แสดงหมายเลขบุคลากรและนามสกุลของพนักงานทุกคนที่ทำงานในแผนกเฉพาะ หมายเลขแผนกเป็นพารามิเตอร์ขั้นตอนและต้องระบุเมื่อโทร การดำเนินการตามขั้นตอนนี้จะสร้างตารางที่มีสองคอลัมน์ซึ่งมีส่วนหัวตรงกับชื่อของคอลัมน์ที่เกี่ยวข้องในตารางฐานข้อมูล เช่น ไอดีและนามสกุล. หากต้องการเปลี่ยนส่วนหัวของคอลัมน์ผลลัพธ์ (รวมถึงประเภทข้อมูล) SQL Server 2012 จะใช้ส่วนคำสั่ง WITH RESULTS SETS ใหม่ การใช้ประโยคนี้แสดงไว้ในตัวอย่างด้านล่าง:

ใช้ SampleDb; EXEC EmployeesInDept "d1" พร้อมชุดผลลัพธ์ (( INT ไม่ใช่ NULL, [นามสกุล] CHAR (20) ไม่ใช่ NULL));

ผลลัพธ์ของการดำเนินการขั้นตอนการจัดเก็บที่เรียกว่าในลักษณะนี้จะเป็นดังนี้:

อย่างที่คุณเห็น การรันโพรซีเดอร์ที่เก็บไว้โดยใช้คำสั่งย่อย WITH RESULT SETS ในคำสั่ง EXECUTE ช่วยให้คุณสามารถเปลี่ยนชื่อและชนิดข้อมูลของคอลัมน์ในชุดผลลัพธ์ที่สร้างโดยโพรซีเดอร์ ดังนั้นฟังก์ชันใหม่นี้จึงให้ความยืดหยุ่นมากขึ้นในการดำเนินการตามขั้นตอนการจัดเก็บและวางผลลัพธ์ไว้ในตารางใหม่

การเปลี่ยนโครงสร้างของขั้นตอนการจัดเก็บ

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

โปรแกรมฐานข้อมูลรองรับ ชนิดข้อมูลเคอร์เซอร์- ชนิดข้อมูลนี้ใช้ในการประกาศเคอร์เซอร์ในกระบวนงานที่เก็บไว้ เคอร์เซอร์เป็นโครงสร้างการเขียนโปรแกรมที่ใช้ในการจัดเก็บผลลัพธ์ของการสืบค้น (โดยปกติจะเป็นชุดของแถว) และอนุญาตให้ผู้ใช้แสดงผลลัพธ์นั้นทีละแถว

หากต้องการลบขั้นตอนที่เก็บไว้หนึ่งหรือกลุ่ม ให้ใช้ คำสั่ง DROP PROCEDURE- เฉพาะเจ้าของหรือสมาชิกของบทบาทคงที่ db_owner และผู้ดูแลระบบเท่านั้นที่สามารถลบขั้นตอนที่เก็บไว้ได้

ขั้นตอนการจัดเก็บและ CLR

SQL Server รองรับ Common Language Runtime (CLR) ซึ่งช่วยให้คุณพัฒนาอ็อบเจ็กต์ฐานข้อมูลต่างๆ (ขั้นตอนการจัดเก็บ ฟังก์ชันที่ผู้ใช้กำหนด ทริกเกอร์ การรวมที่ผู้ใช้กำหนด และประเภทข้อมูลแบบกำหนดเอง) โดยใช้ C# และ Visual Basic CLR ยังช่วยให้คุณเรียกใช้งานออบเจ็กต์เหล่านี้ได้โดยใช้ระบบรันไทม์ทั่วไป

รันไทม์ภาษาทั่วไปถูกเปิดใช้งานและปิดใช้งานโดยใช้ตัวเลือก clr_เปิดใช้งานขั้นตอนของระบบ sp_configureซึ่งเปิดตัวเพื่อดำเนินการตามคำสั่ง กำหนดค่าใหม่- ตัวอย่างต่อไปนี้แสดงวิธีที่คุณสามารถใช้ขั้นตอนระบบ sp_configure เพื่อเปิดใช้งาน CLR:

ใช้ SampleDb; EXEC sp_configure "clr_enabled",1 กำหนดค่าใหม่

หากต้องการสร้าง คอมไพล์ และบันทึกขั้นตอนโดยใช้ CLR คุณต้องดำเนินการตามลำดับขั้นตอนต่อไปนี้ตามลำดับที่แสดง:

สร้างกระบวนงานที่เก็บไว้ใน C# หรือ Visual Basic แล้วคอมไพล์โดยใช้คอมไพเลอร์ที่เหมาะสม

การใช้คำแนะนำ สร้างแอสเซมบลีให้สร้างไฟล์ปฏิบัติการที่เกี่ยวข้อง

ดำเนินการตามขั้นตอนโดยใช้คำสั่ง EXECUTE

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

ขั้นแรก สร้างโปรแกรมที่จำเป็นในสภาพแวดล้อมการพัฒนา เช่น Visual Studio คอมไพล์โปรแกรมที่เสร็จแล้วเป็นออบเจ็กต์โค้ดโดยใช้คอมไพเลอร์ C# หรือ Visual Basic รหัสนี้ถูกเก็บไว้ในไฟล์ไลบรารีลิงก์แบบไดนามิก (.dll) ซึ่งทำหน้าที่เป็นแหล่งที่มาสำหรับคำสั่ง CREATE ASSEMBLY ซึ่งสร้างรหัสปฏิบัติการระดับกลาง ถัดไป ออกคำสั่ง CREATE PROCEDURE เพื่อบันทึกโค้ดที่รันเป็นอ็อบเจ็กต์ฐานข้อมูล สุดท้าย ให้รันโพรซีเดอร์โดยใช้คำสั่ง EXECUTE ที่คุ้นเคย

ตัวอย่างด้านล่างแสดงซอร์สโค้ดสำหรับกระบวนงานที่เก็บไว้ใน C#:

การใช้ System.Data.SqlClient; โดยใช้ Microsoft.SqlServer.Server; StoredProcedures คลาสสาธารณะบางส่วน ( public static int CountEmployees() ( int rows; SqlConnection Connection = new SqlConnection("Context Connection=true"); Connection.Open(); SqlCommand cmd = Connection.CreateCommand(); cmd.CommandText = "select count(*) เป็น "จำนวนพนักงาน" " + "จากพนักงาน"; row = (int)cmd.ExecuteScalar(); Connection.Close(); กลับแถว; ) )

ขั้นตอนนี้ใช้แบบสอบถามเพื่อนับจำนวนแถวในตารางพนักงาน การใช้คำสั่งที่จุดเริ่มต้นของโปรแกรมระบุเนมสเปซที่จำเป็นในการรันโปรแกรม การใช้คำสั่งเหล่านี้ช่วยให้คุณสามารถระบุชื่อคลาสในซอร์สโค้ดโดยไม่ต้องระบุเนมสเปซที่เกี่ยวข้องอย่างชัดเจน ถัดไป มีการกำหนดคลาส StoredProcedures ซึ่ง แอตทริบิวต์ SqlProcedureซึ่งแจ้งคอมไพเลอร์ว่าคลาสนี้เป็นกระบวนงานที่เก็บไว้ วิธีการ CountEmployees() ถูกกำหนดไว้ภายในรหัสชั้นเรียน การเชื่อมต่อกับระบบฐานข้อมูลถูกสร้างขึ้นผ่านอินสแตนซ์ของคลาส การเชื่อมต่อ SQL- หากต้องการเปิดการเชื่อมต่อ จะใช้เมธอด Open() ของอินสแตนซ์นี้ ก CreateCommand() วิธีการช่วยให้คุณเข้าถึงอินสแตนซ์ของคลาสได้ SQLCommndซึ่งส่งคำสั่ง SQL ที่จำเป็นไปให้

ในข้อมูลโค้ดต่อไปนี้:

Cmd.CommandText = "select count(*) เป็น "จำนวนพนักงาน" " + "จากพนักงาน";

ใช้คำสั่ง SELECT เพื่อนับจำนวนแถวในตารางพนักงานและแสดงผลลัพธ์ ข้อความคำสั่งถูกระบุโดยการตั้งค่าคุณสมบัติ CommandText ของตัวแปร cmd ให้กับอินสแตนซ์ที่ส่งคืนโดยเมธอด CreateCommand() ต่อไปก็เรียกว่า วิธีการ ExecuteScalar()อินสแตนซ์ SqlCommand เมธอดนี้ส่งคืนค่าสเกลาร์ที่ถูกแปลงเป็นชนิดข้อมูลจำนวนเต็มและกำหนดให้กับตัวแปรแถว

ตอนนี้คุณสามารถคอมไพล์โค้ดนี้โดยใช้ Visual Studio ฉันเพิ่มคลาสนี้ในโครงการชื่อ CLRStoredProcedures ดังนั้น Visual Studio จะรวบรวมแอสเซมบลีที่มีชื่อเดียวกันพร้อมกับนามสกุล *.dll ตัวอย่างด้านล่างแสดงขั้นตอนถัดไปในการสร้างขั้นตอนการจัดเก็บ: การสร้างโค้ดที่ปฏิบัติการได้ ก่อนที่คุณจะเรียกใช้โค้ดในตัวอย่างนี้ คุณจำเป็นต้องทราบตำแหน่งของไฟล์ dll ที่คอมไพล์แล้ว (โดยปกติจะอยู่ในโฟลเดอร์ Debug ของโครงการ)

ใช้ SampleDb; ไปสร้าง ASSEMBLY CLRStoredProcedures จาก "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" ด้วย PERMISSION_SET = SAFE

คำสั่ง CREATE ASSEMBLY รับโค้ดที่ได้รับการจัดการเป็นอินพุต และสร้างออบเจ็กต์ที่สอดคล้องกันซึ่งคุณสามารถสร้างขั้นตอนการจัดเก็บ CLR ฟังก์ชันที่ผู้ใช้กำหนด และทริกเกอร์ได้ คำสั่งนี้มีไวยากรณ์ดังต่อไปนี้:

สร้าง ASSEMBLY assembly_name [ การอนุญาต Owner_name ] จาก (dll_file) แบบแผนไวยากรณ์

พารามิเตอร์ assembly_name ระบุชื่อของแอสเซมบลี ส่วนคำสั่ง AUTHORIZATION ที่เป็นทางเลือกระบุชื่อบทบาทในฐานะเจ้าของแอสเซมบลีนี้ ส่วนคำสั่ง FROM ระบุเส้นทางที่มีชุดประกอบที่จะโหลดอยู่

ด้วยส่วนคำสั่ง PERMISSION_SETเป็น clause ที่สำคัญมากของคำสั่ง CREATE ASSEMBLY และต้องระบุเสมอ มันกำหนดชุดของการอนุญาตที่มอบให้กับรหัสแอสเซมบลี ชุดสิทธิ์อนุญาต SAFE นั้นมีข้อจำกัดมากที่สุด รหัสแอสเซมบลีที่มีสิทธิ์เหล่านี้ไม่สามารถเข้าถึงทรัพยากรระบบภายนอกเช่นไฟล์ได้ ชุดสิทธิ์ EXTERNAL_ACCESS อนุญาตให้โค้ดแอสเซมบลีเข้าถึงทรัพยากรระบบภายนอกบางอย่าง ในขณะที่ชุดสิทธิ์ UNSAFE อนุญาตให้เข้าถึงทรัพยากรอย่างไม่จำกัดทั้งภายในและภายนอกระบบฐานข้อมูล

หากต้องการบันทึกข้อมูลรหัสแอสเซมบลี ผู้ใช้จะต้องสามารถออกคำสั่ง CREATE ASSEMBLY ได้ เจ้าของแอสเซมบลีคือผู้ใช้ (หรือบทบาท) ที่ดำเนินการคำสั่ง คุณสามารถกำหนดให้ผู้ใช้รายอื่นเป็นเจ้าของแอสเซมบลีได้โดยใช้ส่วนคำสั่ง AUTHORIZATION ของคำสั่ง CREATE SCHEMA

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

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

ใช้ SampleDb; ไปสร้างขั้นตอน CountEmployees เป็นชื่อภายนอก CLRStoredProcedures.StoredProcedures.CountEmployees

คำสั่ง CREATE PROCEDURE ในตัวอย่างแตกต่างจากคำสั่งเดียวกันในตัวอย่างก่อนหน้านี้ที่มี พารามิเตอร์ชื่อภายนอก- ตัวเลือกนี้ระบุว่าโค้ดถูกสร้างขึ้นโดยรันไทม์ภาษาทั่วไป ชื่อในประโยคนี้ประกอบด้วยสามส่วน:

Assembly_name.class_name.method_name

assembly_name - ระบุชื่อของชุดประกอบ

class_name - ระบุชื่อของคลาสทั่วไป

method_name - ส่วนเสริม ระบุชื่อของวิธีการที่กำหนดไว้ภายในคลาส

การดำเนินการตามขั้นตอนของ CountEmployees จะแสดงอยู่ในตัวอย่างด้านล่าง:

ใช้ SampleDb; ประกาศ @count INT EXECUTE @count = CountEmployees PRINT @count -- ส่งคืน 7

คำสั่ง PRINT ส่งคืนจำนวนแถวปัจจุบันในตารางพนักงาน