ฟังก์ชั่นหรือขั้นตอนการจัดเก็บที่ การซ่อนโครงสร้างข้อมูล

ขั้นตอนการจัดเก็บ

หัวข้อของบทนี้เป็นหนึ่งในเครื่องมือที่ทรงพลังที่สุดที่เสนอให้กับนักพัฒนาแอปพลิเคชันฐานข้อมูล InterBase สำหรับการนำตรรกะทางธุรกิจไปใช้ กระบวนงานที่เก็บไว้ (ภาษาอังกฤษ กระบวนงานแบบ stoied) ช่วยให้คุณนำส่วนสำคัญของตรรกะของแอปพลิเคชันไปใช้ในระดับฐานข้อมูลได้ ประสิทธิภาพของแอปพลิเคชันทั้งหมด รวมศูนย์การประมวลผลข้อมูล และลดจำนวนโค้ดที่จำเป็นในการทำงานให้สำเร็จ เกือบทุกแอปพลิเคชันฐานข้อมูลที่ซับซ้อนเพียงพอต้องใช้ขั้นตอนการจัดเก็บ
นอกเหนือจากประโยชน์ที่รู้จักกันดีเหล่านี้ของการใช้กระบวนงานที่เก็บไว้ ซึ่งเป็นเรื่องปกติสำหรับระบบฐานข้อมูลเชิงสัมพันธ์ส่วนใหญ่ กระบวนงานที่เก็บไว้ InterBase สามารถเล่นบทบาทของชุดข้อมูลเกือบทั้งหมด ซึ่งช่วยให้คุณใช้ผลลัพธ์ที่ส่งคืนในการสืบค้น SQL ทั่วไปได้
นักพัฒนามือใหม่มักคิดว่ากระบวนงานที่เก็บไว้เป็นเพียงชุดของแบบสอบถาม SQL เฉพาะที่ทำบางสิ่งภายในฐานข้อมูล และมีความเห็นว่าการทำงานกับกระบวนงานที่เก็บไว้นั้นยากกว่าการใช้ฟังก์ชันเดียวกันในแอปพลิเคชันไคลเอนต์ในระดับสูง ภาษาระดับ
ดังนั้นขั้นตอนการจัดเก็บใน InterBase คืออะไร?
กระบวนงานที่เก็บไว้ (SP) เป็นส่วนหนึ่งของข้อมูลเมตาของฐานข้อมูล ซึ่งเป็นรูทีนย่อยที่คอมไพล์เป็นการแทนค่าภายในของ InterBase ซึ่งเขียนด้วยภาษาพิเศษ คอมไพเลอร์ซึ่งติดตั้งอยู่ในแกนหลักของเซิร์ฟเวอร์ InteiBase
กระบวนงานที่เก็บไว้สามารถเรียกได้จากแอปพลิเคชันไคลเอนต์ จากทริกเกอร์ และจากโพรซีเดอร์ที่เก็บไว้อื่นๆ กระบวนงานที่เก็บไว้จะดำเนินการภายในกระบวนการของเซิร์ฟเวอร์และสามารถจัดการข้อมูลในฐานข้อมูล รวมทั้งส่งคืนผลลัพธ์ของการดำเนินการไปยังไคลเอนต์ที่เรียกใช้ (เช่น ทริกเกอร์, HP, แอปพลิเคชัน)
พื้นฐานของคุณสมบัติอันทรงพลังที่มีอยู่ใน HP คือภาษาการเขียนโปรแกรมเชิงขั้นตอน ซึ่งรวมถึงคำสั่ง SQL ปกติที่แก้ไขแล้ว เช่น INSERT, UPDATE และ SELECT ตลอดจนเครื่องมือแยกย่อยและวนซ้ำ (IF, WHILE) รวมถึงเครื่องมือจัดการข้อผิดพลาด . และข้อยกเว้น ภาษาของกระบวนงานที่เก็บไว้ช่วยให้คุณสามารถใช้อัลกอริธึมที่ซับซ้อนสำหรับการทำงานกับข้อมูลและเนื่องจากการมุ่งเน้นที่การทำงานกับข้อมูลเชิงสัมพันธ์ SP จึงมีขนาดกะทัดรัดกว่าขั้นตอนที่คล้ายคลึงกันในภาษาดั้งเดิม
ควรสังเกตว่าใช้ภาษาการเขียนโปรแกรมเดียวกันสำหรับทริกเกอร์ ยกเว้นคุณลักษณะและข้อจำกัดจำนวนหนึ่ง ความแตกต่างระหว่างชุดย่อยของภาษาที่ใช้ในทริกเกอร์และภาษาของ HP มีการกล่าวถึงโดยละเอียดในบททริกเกอร์ (ตอนที่ 1)

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

ถึงเวลาสร้างขั้นตอนการจัดเก็บครั้งแรกและใช้เป็นตัวอย่างเพื่อศึกษากระบวนการสร้างกระบวนงานที่เก็บไว้ แต่ก่อนอื่น ควรพูดสองสามคำเกี่ยวกับวิธีการทำงานกับกระบวนงานที่เก็บไว้ ความจริงก็คือ HP มีชื่อเสียงในฐานะเครื่องมือที่คลุมเครือและไม่สะดวกสำหรับเครื่องมือมาตรฐานที่แย่มากสำหรับการพัฒนาและดีบักกระบวนงานที่เก็บไว้ ในเอกสารประกอบของ InterBase ขอแนะนำให้สร้างโพรซีเดอร์โดยใช้ไฟล์สคริปต์ SQL ที่มีข้อความของ CP ซึ่งป้อนไปยังตัวแปล isql และสร้างและแก้ไข CP หากเกิดข้อผิดพลาดขึ้น isql จะแสดงข้อความที่ บรรทัดของไฟล์สคริปต์ SQL เกิดข้อผิดพลาด แก้ไขข้อผิดพลาดและทำซ้ำทั้งหมดอีกครั้ง การดีบักในความหมายที่ทันสมัยของคำนั้นคือ การติดตามการดำเนินการ ที่มีความสามารถในการดูค่ากลางของตัวแปรนั้นเป็นไปไม่ได้เลย เห็นได้ชัดว่าวิธีการนี้ไม่ได้มีส่วนทำให้ความน่าดึงดูดใจของขั้นตอนการจัดเก็บในสายตาของนักพัฒนาเพิ่มขึ้น
อย่างไรก็ตาม นอกเหนือจากแนวทางที่เรียบง่ายมาตรฐานในการพัฒนา HP<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
ไวยากรณ์ของกระบวนงานที่เก็บไว้อธิบายไว้ดังนี้:

สร้างชื่อขั้นตอน
[ (ประเภทข้อมูลพารามิเตอร์ [, ประเภทข้อมูลพารามิเตอร์ ...]) ]
)]
เช่น
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var ประเภทข้อมูล;

=
เริ่ม
< compound_statement>
[< compound_statement> ...]
จบ
< compound_statement> = (คำแถลง;)

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

สร้างขั้นตอน SP_Add (first_arg DOUBLE PRECISION,
Second_arg ความแม่นยำสองเท่า)
ผลตอบแทน (ผลลัพธ์ความแม่นยำสองเท่า)
เช่น
เริ่ม
ผลลัพธ์=first_arg+second_arg;
ระงับ;
จบ

อย่างที่คุณเห็น ทุกอย่างเรียบง่าย: หลังจากคำสั่ง CREATE PROCEDURE ชื่อของโพรซีเดอร์ที่สร้างขึ้นใหม่จะถูกระบุ (ซึ่งต้องไม่ซ้ำกันในฐานข้อมูล) - ในกรณีนี้คือ SP_Add ตามด้วยพารามิเตอร์อินพุต XP - first_arg และ second_arg - อยู่ในวงเล็บคั่นด้วยเครื่องหมายจุลภาค - ระบุประเภท
รายการพารามิเตอร์อินพุตเป็นส่วนเสริมของคำสั่ง CREATE PROCEDURE - มีหลายกรณีที่โพรซีเดอร์ได้รับข้อมูลทั้งหมดสำหรับการทำงานผ่านการสืบค้นไปยังตารางภายในเนื้อหาของโพรซีเดอร์

กระบวนงานที่เก็บไว้ใช้ประเภทข้อมูลสเกลาร์ InteiBase ไม่ใช้อาร์เรย์และประเภทที่ผู้ใช้กำหนด - โดเมน

ถัดมาคือคีย์เวิร์ด RETURNS หลังจากนั้นพารามิเตอร์การส่งคืนจะแสดงอยู่ในวงเล็บ โดยระบุประเภท - ในกรณีนี้ มีเพียงรายการเดียวเท่านั้น - ผลลัพธ์
หากโพรซีเดอร์ไม่ควรส่งคืนพารามิเตอร์ แสดงว่าไม่มีคำว่า RETURNS และรายการพารามิเตอร์ที่ส่งคืน
RETURNSQ ตามด้วยคีย์เวิร์ด AS ก่อนที่คีย์เวิร์ด AS จะไป ชื่อ,และหลังจากนั้น - เทคโนโลยีขั้นตอน
เนื้อหาของกระบวนงานที่เก็บไว้คือรายการการประกาศตัวแปรภายใน (ท้องถิ่น) (ถ้ามี ให้อธิบายในรายละเอียดเพิ่มเติมด้านล่าง) คั่นด้วยเครื่องหมายอัฒภาค (;) และกลุ่มของคำสั่งที่อยู่ในวงเล็บคำสั่ง BEGIN END ในกรณีนี้ เนื้อหาของ CP นั้นง่ายมาก - เราขอให้เพิ่มอาร์กิวเมนต์อินพุตสองตัวและกำหนดผลลัพธ์ให้กับเอาต์พุต จากนั้นเรียกใช้คำสั่ง SUSPEND หลังจากนั้นเล็กน้อย เราจะอธิบายสาระสำคัญของการกระทำของคำสั่งนี้ แต่สำหรับตอนนี้ เราจะทราบเพียงว่าจำเป็นต้องส่งพารามิเตอร์ส่งคืนไปยังตำแหน่งที่เรียกกระบวนงานที่เก็บไว้

ตัวแยกในขั้นตอนการจัดเก็บ

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

กำหนดระยะเวลา

สำหรับกรณีทั่วไปของการสร้างโพรซีเดอร์ที่เก็บไว้ จะมีลักษณะดังนี้:

กำหนดระยะเวลา ^;
สร้างขั้นตอน some_procedure
... . .
จบ
^
กำหนดระยะเวลา ;^

เรียกกระบวนงานที่เก็บไว้

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

เลือก *
จาก Sp_add(181.35, 23.09)

ข้อความค้นหานี้จะส่งกลับหนึ่งแถวที่มีช่องผลลัพธ์เพียงช่องเดียว ซึ่งจะมีผลรวมของตัวเลข 181.35 และ 23.09 เช่น 204.44
ดังนั้น โพรซีเดอร์ของเราสามารถใช้ในการสืบค้น SQL ทั่วไปที่ดำเนินการทั้งในโปรแกรมไคลเอนต์และใน SP หรือทริกเกอร์อื่นๆ การใช้ขั้นตอนของเรานี้เกิดขึ้นได้โดยใช้คำสั่ง SUSPEND ที่ส่วนท้ายของกระบวนงานที่เก็บไว้
ความจริงก็คือใน InterBase (และในโคลนทั้งหมด) มีโพรซีเดอร์ที่เก็บไว้สองประเภท: โพรซีเดอร์ที่สามารถเลือกได้และโพรซีเดอร์ที่เรียกใช้งานได้ ความแตกต่างในการทำงานของ CP ทั้งสองประเภทนี้ก็คือ กระบวนการดึงข้อมูลมักจะส่งคืนพารามิเตอร์เอาต์พุตหลายชุด โดยจัดกลุ่มทีละแถว ซึ่งดูเหมือนชุดข้อมูล และโพรซีเดอร์ที่เรียกใช้งานได้ไม่สามารถส่งคืนพารามิเตอร์ได้เลย หรือส่งคืนชุดเดียว ของพารามิเตอร์เอาต์พุตที่ระบุไว้ใน Returns โดยที่พารามิเตอร์หนึ่งบรรทัด โพรซีเดอร์ Select ถูกเรียกในเคียวรี SELECT และโพรซีเดอร์ที่เรียกใช้งานได้จะถูกเรียกด้วยคำสั่ง EXECUTE PROCEDURE
โพรซีเดอร์ที่เก็บไว้ทั้งสองประเภทมีไวยากรณ์การสร้างเหมือนกันและเป็นทางการเหมือนกัน ดังนั้นโพรซีเดอร์ที่สามารถเรียกทำงานได้ใดๆ สามารถเรียกในเคียวรี SELECT และโพรซีเดอร์ Select ใดๆ สามารถเรียกได้โดยใช้ EXECUTE PROCEDURE คำถามคือว่า HP จะมีพฤติกรรมอย่างไรกับการโทรประเภทต่างๆ กล่าวอีกนัยหนึ่ง ความแตกต่างอยู่ในการออกแบบขั้นตอนสำหรับการโทรบางประเภท กล่าวคือ โพรซีเดอร์ select จะถูกสร้างขึ้นโดยเฉพาะเพื่อเรียกจากเคียวรี SELECT และโพรซีเดอร์ที่เรียกใช้งานได้ถูกสร้างขึ้นโดยเฉพาะเพื่อเรียกโดยใช้ EXECUTE PROCEDURE มาดูกันว่าการออกแบบ HP ทั้งสองประเภทนี้มีความแตกต่างกันอย่างไร
เพื่อให้เข้าใจว่าขั้นตอนการคัดเลือกทำงานอย่างไร คุณต้องเจาะลึกลงไปในทฤษฎีเล็กน้อย ลองนึกภาพการสืบค้น SQL ปกติเช่น SELECT ID, NAME FROM Table_example ผลลัพธ์ที่ได้จากการดำเนินการ เราได้ตารางที่ประกอบด้วยสองคอลัมน์ (ID และ NAME) และจำนวนแถวที่แน่นอน (เท่ากับจำนวนแถวในตาราง Table_example) ตารางที่ส่งกลับอันเป็นผลมาจากการสืบค้นนี้เรียกอีกอย่างว่าชุดข้อมูล SQL ลองคิดดูว่าชุดข้อมูลมีรูปแบบอย่างไรในระหว่างการดำเนินการของแบบสอบถามนี้ เซิร์ฟเวอร์เมื่อได้รับแบบสอบถามแล้วจะกำหนดตารางที่เป็นของ จากนั้นค้นหาว่าชุดย่อยใดของ ระเบียนจากตารางเหล่านี้ควรรวมอยู่ในผลลัพธ์ของแบบสอบถาม ถัดไป เซิร์ฟเวอร์จะอ่านแต่ละระเบียนที่ตรงกับผลลัพธ์ของการค้นหา เลือกฟิลด์ที่จำเป็นจากมัน (ในกรณีของเราคือ ID และ NAME) และส่งไปยังไคลเอนต์ จากนั้นกระบวนการจะทำซ้ำอีกครั้ง - และอื่นๆ สำหรับแต่ละระเบียนที่เลือก
การพูดนอกเรื่องทั้งหมดนี้เป็นสิ่งจำเป็นเพื่อให้ผู้อ่านที่รักเข้าใจว่าชุดข้อมูล SQL ทั้งหมดถูกสร้างขึ้นทีละบรรทัดรวมถึงในกระบวนงานที่เก็บไว้! และความแตกต่างที่สำคัญระหว่างขั้นตอนที่เลือกและขั้นตอนที่เรียกใช้งานได้คือขั้นตอนแรกได้รับการออกแบบให้ส่งคืนหลายแถว ในขณะที่ชุดหลังได้รับการออกแบบให้ส่งคืนเพียงแถวเดียว ดังนั้นจึงใช้ต่างกัน: เรียกขั้นตอนการเลือกโดยใช้คำสั่ง SELECT ซึ่ง "ต้องการ" กระบวนงานเพื่อส่งคืนระเบียนทั้งหมดที่สามารถส่งคืนได้ โพรซีเดอร์เรียกใช้งานได้โดยใช้ EXECUTE PROCEDURE ซึ่ง "ดึง" เพียงบรรทัดเดียวจาก CP และละเว้นส่วนที่เหลือ (แม้ว่าจะมีอยู่ก็ตาม!)
ลองดูตัวอย่างขั้นตอนการคัดเลือกเพื่อให้ชัดเจนยิ่งขึ้น เพื่อการให้อภัย ให้สร้างกระบวนงานที่เก็บไว้ซึ่งทำงานเหมือนกับ SELECT ID, NAME FROM Table_Example เคียวรี นั่นคือเพียงแค่เลือกฟิลด์ ID และ NAME จากทั้งตาราง นี่คือตัวอย่าง:

สร้างขั้นตอน Simple_Select_SP
ผลตอบแทน (
จำนวนเต็ม procID,
procNAME VARCHAR(80))
เช่น
เริ่ม
สำหรับ
SELECT ID, NAME จาก table_example
INTO:procID, :procNAME
ทำ
เริ่ม
ระงับ;
จบ
จบ

มาดูการดำเนินการของโพรซีเดอร์นี้กัน ซึ่งเรียกว่า Simple_Select_SP อย่างที่คุณเห็น มันไม่มีพารามิเตอร์อินพุตและมีพารามิเตอร์เอาท์พุตสองพารามิเตอร์ - ID และ NAME แน่นอนว่าสิ่งที่น่าสนใจที่สุดคือเนื้อหาของขั้นตอน โครงสร้าง FOR SELECT ถูกใช้ที่นี่:

สำหรับ
SELECT ID, NAME จาก table_example
INTO:procID, :procNAME
ทำ
เริ่ม

/*ทำอะไรบางอย่างกับตัวแปร procID และ procName*/

จบ

โค้ดชิ้นนี้หมายถึงสิ่งต่อไปนี้ สำหรับแต่ละแถวที่เลือกจากตาราง Table_example ให้ใส่ค่าที่เลือกไว้ในตัวแปร procID และ procName แล้วดำเนินการบางอย่างกับตัวแปรเหล่านี้
คุณสามารถทำหน้าแปลกใจและถามว่า "ตัวแปรมีตัวแปรอะไรอีก 9 ตัว" เป็นเรื่องที่น่าประหลาดใจในบทนี้ที่เราสามารถใช้ตัวแปรในกระบวนงานที่เก็บไว้ได้ ใน XP คุณสามารถประกาศทั้งตัวแปรโลคัลของคุณเองภายในโพรซีเดอร์ และใช้พารามิเตอร์อินพุตและเอาต์พุตเป็นตัวแปรได้
ในการประกาศตัวแปรโลคัลในโพรซีเดอร์ที่เก็บไว้ คุณต้องวางการประกาศหลังคีย์เวิร์ด AS และก่อนคำแรก BEGIN การประกาศตัวแปรโลคัลมีลักษณะดังนี้:

ประกาศตัวแปร ;

ตัวอย่างเช่น ในการประกาศตัวแปรท้องถิ่นจำนวนเต็ม Mylnt คุณจะต้องแทรกการประกาศต่อไปนี้ระหว่าง AS และ BEGIN

ประกาศตัวแปร MyInt INTEGER;

ตัวแปรในตัวอย่างของเราเริ่มต้นด้วยเครื่องหมายทวิภาค สิ่งนี้ทำได้เนื่องจากเข้าถึงได้ภายในคำสั่ง FOR SELECT SQL ดังนั้นในการแยกแยะระหว่างฟิลด์ในตารางที่ใช้ใน SELECT และตัวแปร คุณต้องนำหน้าหลังด้วยโคลอน ท้ายที่สุด ตัวแปรสามารถมีชื่อเหมือนกับฟิลด์ในตารางได้!
แต่ควรใช้โคลอนก่อนชื่อตัวแปรในการสืบค้น SQL เท่านั้น นอกข้อความ ตัวแปรสามารถเข้าถึงได้โดยไม่ต้องมีเครื่องหมายทวิภาค ตัวอย่างเช่น:

procName="ชื่อบางส่วน";

แต่กลับไปที่ร่างกายของขั้นตอนของเรา ส่วนคำสั่ง FOR SELECT ส่งคืนข้อมูลที่ไม่ได้อยู่ในรูปแบบของตาราง - ชุดข้อมูล แต่ทีละแถว แต่ละฟิลด์ที่ส่งคืนจะต้องอยู่ในตัวแปรของตัวเอง: ID => procID, NAME => procName ในส่วน DO ตัวแปรเหล่านี้จะถูกส่งไปยังไคลเอ็นต์ที่เรียกว่า p>procedure โดยใช้คำสั่ง SUSPEND
ดังนั้น คำสั่ง FOR SELECT... DO จะวนซ้ำระเบียนที่เลือกไว้ในส่วน SELECT ของคำสั่ง ในเนื้อความของลูปที่เกิดจากส่วน DO เรกคอร์ดที่สร้างถัดไปจะถูกโอนไปยังไคลเอ็นต์โดยใช้คำสั่ง SUSPEND
ดังนั้น ขั้นตอนการเลือกจึงออกแบบมาเพื่อส่งคืนแถวตั้งแต่หนึ่งแถวขึ้นไป ซึ่งจะมีการจัดระเบียบลูปภายในเนื้อหาของ CP โดยเติมพารามิเตอร์ตัวแปรที่ได้ และที่ส่วนท้ายของเนื้อความของลูปนี้มีคำสั่ง SUSPEND ที่จะส่งคืนข้อมูลแถวถัดไปไปยังไคลเอนต์

ลูปและตัวดำเนินการสาขา

นอกจากคำสั่ง FOR SELECT... DO ซึ่งจัดระเบียบไซเคิลผ่านเร็กคอร์ดของการเลือกบางรายการ ยังมีไซเคิลอีกประเภทหนึ่ง - WHILE...DO ซึ่งช่วยให้คุณจัดระเบียบไซเคิลตามการตรวจสอบเงื่อนไขใดๆ นี่คือตัวอย่างของ HP ที่ใช้ลูป WHILE..DO กระบวนงานนี้คืนค่ากำลังสองของจำนวนเต็มตั้งแต่ 0 ถึง 99:

สร้าง PROCEDJRE QUAD
ผลตอบแทน (QUADRAT INTEGER)
เช่น
ประกาศตัวแปร I จำนวนเต็ม;
เริ่ม
ผม = 1;
ขณะที่ฉัน<100) DO
เริ่ม
QUADRAT=ฉัน*ฉัน;
ผม=ผม+1;
ระงับ;
จบ
จบ

จากการดำเนินการแบบสอบถาม SELECT FROM QUAD เราจะได้รับตารางที่มีหนึ่งคอลัมน์ QUADRAT ซึ่งจะมีจำนวนเต็มกำลังสองตั้งแต่ 1 ถึง 99
นอกเหนือจากการวนซ้ำผลลัพธ์ของการสืบค้น SQL และการวนซ้ำแบบคลาสสิกแล้ว ภาษาของโพรซีเดอร์ที่เก็บไว้ใช้คำสั่ง IF...THEN..ELSE ซึ่งช่วยให้คุณจัดระเบียบการแตกแขนงตามการดำเนินการของ \conditions ได้ ไวยากรณ์ของมันคือ คล้ายกับข้อความสั่งการโยงหัวข้อส่วนใหญ่ในภาษาการเขียนโปรแกรมระดับสูง เช่น Pascal และ C
ลองดูตัวอย่างที่ซับซ้อนมากขึ้นของกระบวนงานที่เก็บไว้ซึ่งทำดังต่อไปนี้

คำนวณราคาเฉลี่ยในตาราง Table_example (ดูบท "Tables Primary Keys and Generators")
ถัดไป สำหรับแต่ละรายการในตาราง จะทำการตรวจสอบต่อไปนี้ หากราคาปัจจุบัน (PRICE) มากกว่าราคาเฉลี่ย แล้วกำหนดราคาให้เท่ากับราคาเฉลี่ยบวกเปอร์เซ็นต์คงที่ที่ระบุ
หากราคาปัจจุบันน้อยกว่าหรือเท่ากับราคาเฉลี่ย ให้กำหนดราคาเท่ากับราคาก่อนหน้าบวกส่วนต่างระหว่างราคาเก่ากับราคาเฉลี่ยครึ่งหนึ่ง
ส่งกลับแถวที่เปลี่ยนแปลงทั้งหมดในตาราง

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

สร้างขั้นตอน เพิ่มราคา (
เปอร์เซ็นต์2เพิ่มความแม่นยำสองเท่า)
ผลตอบแทน (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
ความแม่นยำ AS

โพรซีเดอร์จะเรียกว่า IncreasePrices มันมีพารามิเตอร์อินพุตหนึ่งตัว Peiceni21nciease ซึ่งเป็นประเภท DOUBLE PRECISION และพารามิเตอร์เอาต์พุต 3 ตัว - ID, NAME และ new_pnce โปรดทราบว่าพารามิเตอร์เอาต์พุตสองตัวแรกมีชื่อเหมือนกับฟิลด์ในตาราง Table_example ที่เราจะใช้งาน ซึ่งได้รับอนุญาตโดยกฎของภาษาของโพรซีเดอร์ที่เก็บไว้
ตอนนี้เราต้องประกาศตัวแปรท้องถิ่นที่จะใช้เก็บค่าเฉลี่ย การประกาศ Ego จะมีลักษณะดังนี้:

ประกาศตัวแปร avg_price DOUBLE PRECISION;

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

เลือก AVG(price_l)
จาก Table_Example
INTO:avg_price,-

แบบสอบถามนี้ใช้ฟังก์ชันการรวม AVG ที่ส่งกลับค่าเฉลี่ยของฟิลด์ PRICE_1 จากแถวการค้นหาที่เลือก - ในกรณีของเรา ค่าเฉลี่ยของ PRICE_1 ในตาราง Table_example ทั้งหมด ค่าที่ส่งคืนโดยเคียวรีจะอยู่ในตัวแปร avg_price โปรดทราบว่าตัวแปร avg_pnce นำหน้าด้วยโคลอน - เพื่อแยกความแตกต่างจากฟิลด์ที่ใช้ในคำขอ
คุณลักษณะของแบบสอบถามนี้คือส่งกลับระเบียนเดียวเท่านั้นเสมอ แบบสอบถามดังกล่าวเรียกว่า แบบสอบถาม singleton และเฉพาะการเลือกดังกล่าวเท่านั้นที่สามารถใช้ในกระบวนงานที่เก็บไว้ หากการสืบค้นส่งคืนมากกว่าหนึ่งแถว จะต้องจัดรูปแบบเป็นโครงสร้าง FOR SELECT...DO ซึ่งจัดระเบียบการวนซ้ำเพื่อประมวลผลแต่ละแถวที่ส่งคืน
ดังนั้นเราจึงได้มูลค่าเฉลี่ยของราคา ตอนนี้ คุณต้องดูทั้งตาราง เปรียบเทียบมูลค่าราคาในแต่ละระเบียนกับราคาเฉลี่ย และดำเนินการตามความเหมาะสม
จากจุดเริ่มต้น เราจัดระเบียบการวนซ้ำของแต่ละเรคคอร์ดจาก Table_example table

สำหรับ
SELECT ID, NAME, PRICE_1
จาก Table_Example
INTO:ID, :NAME, :new_price
ทำ
เริ่ม
/*_here สั่งซื้อแต่ละระเบียน*/
จบ

เมื่อดำเนินการสร้างนี้ ข้อมูลจะถูกนำมาจากแถวตาราง Table_example ทีละแถว และค่าฟิลด์ในแต่ละแถวจะถูกกำหนดให้กับตัวแปร ID, NAME และ new_pnce แน่นอน คุณจำได้ว่าตัวแปรเหล่านี้ถูกประกาศเป็นพารามิเตอร์เอาต์พุต แต่คุณไม่ควรกังวลว่าข้อมูลที่เลือกจะถูกส่งกลับเป็นผล: ความจริงที่ว่าพารามิเตอร์เอาต์พุตถูกกำหนดบางอย่างไม่ได้หมายความว่าไคลเอนต์ที่เรียก HP จะได้รับทันที ค่าเหล่านี้ ! พารามิเตอร์จะถูกส่งต่อเมื่อมีการดำเนินการคำสั่ง SUSPEND เท่านั้น และก่อนหน้านั้น เราสามารถใช้พารามิเตอร์เอาต์พุตเป็นตัวแปรปกติได้ ในตัวอย่างของเรา เราทำอย่างนั้นด้วยพารามิเตอร์ new_price
ดังนั้นภายในเนื้อความของลูป BEGIN.. .END เราสามารถประมวลผลค่าของแต่ละแถวได้ อย่างที่คุณจำได้ เราจำเป็นต้องค้นหาว่าราคาที่มีอยู่เปรียบเทียบกับค่าเฉลี่ยแล้วดำเนินการอย่างเหมาะสม เราใช้ขั้นตอนการเปรียบเทียบนี้โดยใช้คำสั่ง IF:

IF (new_price > avg_price) แล้ว /*ถ้าราคาปัจจุบันมากกว่าราคาเฉลี่ย*/
เริ่ม
/*แล้วกำหนดราคาใหม่เท่ากับราคาเฉลี่ยบวกเปอร์เซ็นต์คงที่*/
new_price = (avg_price + avg_price*(Percent2Increase/100));
อัปเดต Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
จบ
อื่น
เริ่ม
/* หากราคาปัจจุบันน้อยกว่าหรือเท่ากับราคาเฉลี่ย ให้กำหนดราคาเท่ากับราคาก่อนหน้าบวกส่วนต่างระหว่างราคาเก่ากับราคาเฉลี่ยครึ่งหนึ่ง */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
อัปเดต Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
จบ

อย่างที่คุณเห็น เรามีโครงสร้าง IF ที่ค่อนข้างใหญ่ ซึ่งจะเข้าใจยากหากไม่ใช่เพราะความคิดเห็นที่ใส่ใน /**/ อักขระ
ในการเปลี่ยนแปลงราคาตามผลต่างที่คำนวณได้ เราจะใช้คำสั่ง UPDATE ซึ่งอนุญาตให้คุณแก้ไขบันทึกที่มีอยู่ - หนึ่งรายการขึ้นไป เราใช้ฟิลด์คีย์หลักในส่วนคำสั่ง WHERE โดยเปรียบเทียบกับค่าของตัวแปรที่เก็บค่า ID สำหรับเรกคอร์ดปัจจุบัน: ID=:ID โปรดทราบว่าตัวแปร ID นำหน้าด้วยโคลอน
หลังจากดำเนินการสร้าง IF...THEN...ELSE แล้ว ตัวแปร ID, NAME และ new_price จะมีข้อมูลที่เราต้องส่งคืนไปยังลูกค้า\ ที่เรียกใช้โพรซีเดอร์ ในการทำเช่นนี้หลังจาก IF คุณต้องแทรกคำสั่ง SUSPEND ซึ่งจะส่งข้อมูลไปยังตำแหน่งที่ CP ถูกเรียก ในช่วงเวลาของการถ่ายโอน ขั้นตอนจะถูกระงับ และเมื่อต้องการบันทึกใหม่จาก CP จะดำเนินต่อไปอีกครั้ง - และจะดำเนินต่อไปจนกว่า FOR SELECT...DO จะไม่ทำซ้ำผ่านบันทึกทั้งหมดของข้อความค้นหา
ควรสังเกตว่านอกเหนือจากคำสั่ง SUSPEND ซึ่งหยุดเฉพาะโพรซีเดอร์ที่เก็บไว้เท่านั้น ยังมีคำสั่ง EXIT ซึ่งยุติกระบวนงานที่เก็บไว้หลังจากผ่านสตริงแล้ว อย่างไรก็ตาม คำสั่ง EXIT นั้นไม่ค่อยได้ใช้ เนื่องจากจำเป็นต้องใช้เป็นหลักในการหยุดการวนซ้ำเมื่อถึงเงื่อนไขที่กำหนด
ในกรณีนี้ เมื่อโพรซีเดอร์ถูกเรียกโดยคำสั่ง SELECT และสิ้นสุดโดย EXIT แถวที่ดึงล่าสุดจะไม่ถูกส่งกลับ นั่นคือถ้าคุณต้องการขัดจังหวะขั้นตอนและยังคง> รับบรรทัดนี้คุณต้องใช้ลำดับ

ระงับ;
ออก;

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

สร้างขั้นตอน เพิ่มราคา (
เปอร์เซ็นต์2เพิ่มความแม่นยำสองเท่า)
ผลตอบแทน (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
ประกาศตัวแปร avg_price DOUBLE PRECISION;
เริ่ม
เลือก AVG(price_l)
จาก Table_Example
INTO:avg_price;
สำหรับ
SELECT ID, NAME, PRICE_1
จาก Table_Example
INTO:ID, :NAME, :new_price
ทำ
เริ่ม
/*เราประมวลผลแต่ละระเบียนที่นี่*/
IF (new_pnce > avg_price) แล้ว /*ถ้าราคาปัจจุบันมากกว่าราคาเฉลี่ย*/
เริ่ม
/*กำหนดราคาใหม่เท่ากับราคาเฉลี่ยบวกเปอร์เซ็นต์คงที่ */
new_price = (avg_price + avg_price*(Percent2Increase/100));
อัปเดต Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
จบ
อื่น
เริ่ม
/* หากราคาปัจจุบันน้อยกว่าหรือเท่ากับราคาเฉลี่ย ให้กำหนดราคาเท่ากับราคาก่อนหน้าบวกส่วนต่างระหว่างราคาเก่ากับราคาเฉลี่ยครึ่งหนึ่ง */
new_price = (new_price + ((avg_price - new_price)/2));
อัปเดต Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
จบ
ระงับ;
จบ
จบ

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

ขั้นตอนการจัดเก็บแบบเรียกซ้ำ

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

สินค้า
- เครื่องใช้ไฟฟ้า
- ตู้เย็น
- สามห้อง
- สองห้อง
- ห้องเดี่ยว
- เครื่องซักผ้า
- แนวตั้ง
- หน้าผาก
- คลาสสิก
- แคบ
- เทคโนโลยีคอมพิวเตอร์
....

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

เครื่องซักผ้า - แนวตั้ง
เครื่องซักผ้า - Frontal Classic
เครื่องซักผ้า - Frontal Narrow

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

สร้างโต๊ะสินค้าTree
(ID_GOOD INTEGER ไม่ใช่ NULL
ID_PARENT_GOOD INTEGER,
GOOD_NAME วาร์ชาร์(80),
ข้อจำกัด pkGooci คีย์หลัก (ID_GOOD));

เราสร้างตาราง GoodsTree หนึ่งตาราง ซึ่งมีเพียง 3 ฟิลด์เท่านั้น: ID_GOOD เป็นตัวระบุหมวดหมู่อัจฉริยะ ID_PARENT_GOOD เป็นตัวระบุของแผนผังหลักของหมวดหมู่สำหรับหมวดหมู่นี้ และ GOOD_NAME คือชื่อของหมวดหมู่ เพื่อให้มั่นใจถึงความสมบูรณ์ของข้อมูลในตารางนี้ เราจะกำหนดข้อจำกัดของคีย์ภายนอกในตารางนี้:

ALTER TABLE สินค้าต้นไม้
เพิ่มข้อจำกัด FK_goodstree
กุญแจต่างประเทศ (ID_PARENT_GOOD)
ข้อมูลอ้างอิง GOODSTPE (ID_GOOD)

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

ID_GOOD

1
2
3
4
5
6
7
8
9
10
11
12

ID_PARENT_GOOD

0
1
1
2
2
4
4
4
5
5
10
10

GOOD_NAME

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

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

สร้างขั้นตอน GETFULLNAME (ID_GOOD2SHOW INTEGER)
ผลตอบแทน (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
เช่น
ประกาศตัวแปร CURR_CHILD_NAME VARCHAR(80);
เริ่ม
/*0จัดระเบียบวง FOR SELECT ภายนอกบนทายาทโดยทันทีของผลิตภัณฑ์ด้วย ID_GOOD=ID_GOOD2SHOW */
สำหรับ select gtl.id_good, gtl.good_name
จาก GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
ทำ
เริ่ม
/"ตรวจสอบด้วยฟังก์ชัน EXISTS ซึ่งจะคืนค่า TRUE หากการสืบค้นในวงเล็บส่งกลับอย่างน้อยหนึ่งแถว หากโหนดที่พบที่มี ID_PARENT_GOOD = ID_CHILD_GOOD ไม่มีลูก แสดงว่าเป็น "ใบไม้" ของต้นไม้และได้ผลลัพธ์ * /
ถ้า (ไม่มีอยู่ (
เลือก * จาก GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
แล้ว
เริ่ม
/* ส่ง "ใบไม้" ของต้นไม้ไปสู่ผลลัพธ์ */
ระงับ;
จบ
อื่น
/* สำหรับโหนดที่มีลูก */
เริ่ม
/*เก็บชื่อของโหนดหลักในตัวแปรชั่วคราว */
CURR_CHILD_NAME=full_goods_name;
/* เรียกใช้โพรซีเดอร์นี้ซ้ำ ๆ */
สำหรับ
เลือก ID_CHILD_GOOD,full_goods_name
จาก GETFULLNAME(:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
เริ่มต้น
/*เพิ่มชื่อของโหนดหลักในชื่อที่พบ ชื่อลูกโดยใช้การดำเนินการต่อสตริง || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
ระงับ; /* ส่งคืนชื่อเต็มของผลิตภัณฑ์ */
จบ
จบ
จบ
จบ

หากเราดำเนินการตามขั้นตอนนี้ด้วยพารามิเตอร์อินพุต ID_GOOD2SHOW= 1 เราได้รับสิ่งต่อไปนี้:

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

บทสรุป

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

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

แต่ก่อนอื่น ให้ทฤษฏีเล็กๆ น้อยๆ เพื่อให้คุณเข้าใจว่ากระบวนงานที่เก็บไว้คืออะไรและมีไว้เพื่ออะไรใน T-SQL

บันทึก! สำหรับโปรแกรมเมอร์มือใหม่ ผมขอแนะนำเนื้อหาที่เป็นประโยชน์ต่อไปนี้ในหัวข้อ T-SQL:

สำหรับการศึกษาภาษา T-SQL อย่างละเอียดยิ่งขึ้น ฉันยังแนะนำให้อ่านหนังสือ - The Way of the T-SQL Programmer บทช่วยสอน Transact-SQL

ขั้นตอนการจัดเก็บใน T-SQL คืออะไร?

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

ในการเรียกใช้กระบวนงานที่เก็บไว้ใน SQL Server จำเป็นต้องเขียนคำสั่ง EXECUTE ก่อนชื่อ นอกจากนี้ยังสามารถเขียนคำสั่ง EXEC นี้ในชวเลขได้ เรียกใช้กระบวนงานที่เก็บไว้ในคำสั่ง SELECT เช่น เนื่องจากฟังก์ชันจะไม่ทำงานอีกต่อไป เช่น ขั้นตอนการทำงานแยกกัน

ในโพรซีเดอร์ที่เก็บไว้ ซึ่งแตกต่างจากฟังก์ชัน มันเป็นไปได้ที่จะดำเนินการแก้ไขข้อมูลเช่น: UNSERT, UPDATE, DELETE นอกจากนี้ ในโพรซีเดอร์ คุณสามารถใช้คำสั่ง SQL ได้เกือบทุกประเภท เช่น CREATE TABLE เพื่อสร้างตารางหรือ EXECUTE เช่น เรียกขั้นตอนอื่นๆ ข้อยกเว้นคือคำสั่งหลายประเภท เช่น การสร้างหรือเปลี่ยนฟังก์ชัน มุมมอง ทริกเกอร์ การสร้างสคีมา และคำสั่งอื่นๆ ที่คล้ายคลึงกันสองสามคำสั่ง เช่น เป็นไปไม่ได้ที่จะสลับบริบทการเชื่อมต่อฐานข้อมูล (USE) ในกระบวนงานที่เก็บไว้

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

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

ตัวอย่างการทำงานกับกระบวนงานที่เก็บไว้ใน Microsoft SQL Server

ข้อมูลเบื้องต้นสำหรับตัวอย่าง

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

คำสั่งสร้างตาราง CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- เพิ่มคำสั่งข้อมูล INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "เมาส์", 100), (1, "คีย์บอร์ด", 200), (2, "โทรศัพท์", 400) GO -- SELECT * จากแบบสอบถาม TestTable

มีข้อมูลแล้ว เรามาเริ่มสร้างกระบวนงานที่เก็บไว้กันดีกว่า

การสร้างกระบวนงานที่เก็บไว้ใน T-SQL - CREATE PROCEDURE คำสั่ง

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

ตัวอย่างเช่น ลองเขียนขั้นตอนการจัดเก็บที่จะเพิ่มระเบียนใหม่เช่น รายการใหม่ในแผนภูมิทดสอบของเรา ในการทำเช่นนี้ เราจะกำหนดพารามิเตอร์อินพุตสามตัว: @CategoryId - ตัวระบุหมวดหมู่ผลิตภัณฑ์, @ProductName - ชื่อผลิตภัณฑ์ และ @ราคา - ราคาผลิตภัณฑ์ พารามิเตอร์นี้จะเป็นทางเลือกสำหรับเรา เช่น ไม่สามารถส่งผ่านไปยังขั้นตอนได้ ( เช่น เรายังไม่ทราบราคา) สำหรับสิ่งนี้ เราจะตั้งค่าเริ่มต้นในคำจำกัดความของมัน พารามิเตอร์เหล่านี้อยู่ในเนื้อหาของขั้นตอน กล่าวคือ ในบล็อก BEGIN…END สามารถใช้ในลักษณะเดียวกับตัวแปรทั่วไป ( ดังที่คุณทราบ ตัวแปรจะแสดงด้วยเครื่องหมาย @). หากคุณต้องการระบุพารามิเตอร์เอาต์พุต หลังจากชื่อพารามิเตอร์ ให้ระบุคีย์เวิร์ด OUTPUT ( หรือย่อ OUT).

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

นี่คือรหัสสำหรับขั้นตอนนี้ ฉันยังแสดงความคิดเห็นเกี่ยวกับมัน).

สร้างขั้นตอน CREATE PROCEDURE TestProcedure (--พารามิเตอร์ขาเข้า @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --คำแนะนำที่ใช้อัลกอริทึมของคุณ --การประมวลผลพารามิเตอร์ขาเข้า --การลบช่องว่างพิเศษที่จุดเริ่มต้นและ ต่อท้ายสตริงข้อความ SET @ProductName = LTRIM(RTRIM(@ProductName)); --เพิ่มรายการใหม่ INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

เรียกใช้กระบวนงานที่เก็บไว้ใน T-SQL - EXECUTE Command

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

พารามิเตอร์ที่มีค่าเริ่มต้นไม่สามารถระบุได้ สิ่งเหล่านี้เรียกว่าพารามิเตอร์ทางเลือก

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

1. เรียกขั้นตอนโดยไม่ระบุราคา EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" ---2 เราเรียกขั้นตอนด้วยราคาที่ระบุ EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 ---3 เราเรียกโพรซีเดอร์โดยไม่ระบุชื่อของพารามิเตอร์ EXEC TestProcedure 1, "Test item 3", 400

การเปลี่ยนขั้นตอนการจัดเก็บเป็น T-SQL - แก้ไขคำสั่งขั้นตอน

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

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

เปลี่ยนขั้นตอน ALTER PROCEDURE TestProcedure (--พารามิเตอร์ขาเข้า @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --คำแนะนำที่ใช้อัลกอริทึมของคุณ --การประมวลผลพารามิเตอร์ขาเข้า --ลบช่องว่างพิเศษที่จุดเริ่มต้นและจุดสิ้นสุด ของบรรทัดข้อความ SET @ProductName = LTRIM(RTRIM(@ProductName)); --เพิ่มระเบียนใหม่ INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

การลบขั้นตอนการจัดเก็บในคำสั่ง T-SQL - DROP PROCEDURE

หากจำเป็น คุณสามารถลบขั้นตอนการจัดเก็บ ซึ่งทำได้โดยใช้คำสั่ง DROP PROCEDURE.

ตัวอย่างเช่น ให้ลบขั้นตอนการทดสอบที่เราสร้างขึ้น

DROP PROCEDURE ขั้นตอนการทดสอบ

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

ฉันมีทุกอย่าง ฉันหวังว่าเนื้อหาจะน่าสนใจและมีประโยชน์สำหรับคุณ ลาก่อน!

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

สิทธิ์อนุญาตให้สร้างคำขอแบบไดนามิก เราจะส่งพารามิเตอร์ได้ที่ไหน

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

ดูหรือขั้นตอนการจัดเก็บบันทึกหน่วยความจำอย่างถาวร?

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

โปรดแจ้งให้เราทราบหากมีคะแนนเพิ่มเติม

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

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

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

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

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

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

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

ดังนั้นถ้าฉันอยู่ในแบบสอบถามจากตารางเหล่านั้นมาก... แทนที่จะเข้าร่วมในทุก ๆ peice 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 ด้วย ID ผู้ใช้... ทั้งหมดที่ฉันต้องทำคือ

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

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

สร้างโพรซีเดอร์ dbo.getDesc @ID int AS เริ่มต้น SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO

ทีหลังก็โทรได้

Dbo.getDesc 25

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ลองอ่านบทความนี้: การท่องเว็บเทียบกับกระบวนงานที่เก็บไว้ สิ่งที่คุณกำลังมองหา

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

กระบวนงานที่เก็บไว้ทำการประมวลผลทั้งหมดโดยใช้ 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 ลงในมุมมอง...

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

สร้างดูขยะเป็น SELECT * จาก

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

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

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

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

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

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

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

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

แนวคิดของขั้นตอนการจัดเก็บ

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

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

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

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

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

กระบวนงานที่เก็บไว้ในสภาพแวดล้อม MS SQL Server

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

ประเภทของขั้นตอนการจัดเก็บ

มีหลายประเภทใน SQL Server ขั้นตอนการจัดเก็บ.

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

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

การสร้าง ขั้นตอนการเก็บเกี่ยวข้องกับการแก้ไขงานต่อไปนี้:

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

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

<определение_процедуры>::= (CREATE | ALTER ) PROC protocol_name [;number] [(@parameter_name datatype ) [=default] ][,...n] AS sql_statement [...n]

พิจารณาพารามิเตอร์ของคำสั่งนี้

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

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

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

เพื่อกำหนดประเภทข้อมูลที่สอดคล้อง พารามิเตอร์ขั้นตอนการจัดเก็บ, ชนิดข้อมูล SQL ใดๆ ก็ใช้ได้ รวมถึงข้อมูลที่ผู้ใช้กำหนด อย่างไรก็ตาม ชนิดข้อมูล CURSOR ใช้ได้เฉพาะเป็น พารามิเตอร์เอาต์พุต ขั้นตอนการเก็บ, เช่น. ด้วยคำหลัก OUTPUT

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

คีย์เวิร์ด VARYING ใช้ร่วมกับพารามิเตอร์ OUTPUT ซึ่งเป็นประเภท CURSOR มันกำหนดว่า พารามิเตอร์เอาต์พุตจะเป็นชุดผลลัพธ์

คีย์เวิร์ด DEFAULT คือค่าที่ตรงกัน การตั้งค่าเริ่มต้น. ดังนั้น เมื่อเรียกใช้โพรซีเดอร์ คุณไม่สามารถระบุค่าของพารามิเตอร์ที่เกี่ยวข้องได้อย่างชัดเจน

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

จำเป็นต้องใช้พารามิเตอร์ FOR REPLICATION เมื่อจำลองข้อมูลและรวมไฟล์ created ขั้นตอนการเก็บเป็นบทความในสิ่งพิมพ์

คีย์เวิร์ด ENCRYPTION สั่งให้เซิร์ฟเวอร์เข้ารหัสรหัส ขั้นตอนการเก็บซึ่งสามารถให้การป้องกันการใช้อัลกอริธึมลิขสิทธิ์ที่ดำเนินงาน ขั้นตอนการเก็บ.

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

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

DROP PROCEDURE (procedure_name) [,...n]

ดำเนินการขั้นตอนการจัดเก็บ

สำหรับ กำลังดำเนินการขั้นตอนการจัดเก็บใช้คำสั่ง:

[[ EXEC [ UTE] protocol_name [;number] [[@parameter_name=](value | @variable_name) |][,...n]

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

อนุญาตให้ใช้คีย์เวิร์ด OUTPUT เมื่อเรียกใช้โพรซีเดอร์ได้เฉพาะพารามิเตอร์ที่ประกาศเมื่อ การสร้างขั้นตอนด้วยคีย์เวิร์ด OUTPUT

เมื่อมีการระบุคีย์เวิร์ด DEFAULT สำหรับการเรียกโพรซีเดอร์ คีย์เวิร์ด DEFAULT จะถูกใช้ ค่าเริ่มต้น. โดยปกติ คำที่ระบุ DEFAULT จะอนุญาตสำหรับพารามิเตอร์ที่กำหนดไว้เท่านั้น ค่าเริ่มต้น.

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

โปรดทราบว่าเมื่อมีการเรียกโพรซีเดอร์ จะมีการระบุชื่อพารามิเตอร์ที่มีค่าหรือเฉพาะค่าที่ไม่มีชื่อพารามิเตอร์ ไม่อนุญาตให้ใช้ร่วมกัน

ตัวอย่าง 12.1 ขั้นตอนโดยไม่มีพารามิเตอร์. พัฒนาขั้นตอนในการรับชื่อและราคาของสินค้าที่ซื้อโดย Ivanov

สร้าง PROC my_proc1 AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.LastName FROM Customer INNER JOIN (Item INNER JOIN Trade ON Item.ItemId=Trade.ItemId) ON Client.CustomerId=Trade.CustomerId WHERE Client .นามสกุล='อีวานอฟ' ตัวอย่าง 12.1 ขั้นตอนการรับชื่อและราคาของสินค้าที่ซื้อโดย Ivanov