ขั้นตอนการจัดเก็บคืออะไร? สร้างคำสั่งขั้นตอน การดีบัก SQL อย่างง่าย
รวมบรรทัดในขั้นตอนของคุณ - SET NOCOUNT ON:
ด้วยนิพจน์ DML แต่ละรายการ เซิร์ฟเวอร์ SQL จะส่งข้อความกลับถึงเราอย่างระมัดระวังซึ่งมีจำนวนบันทึกที่ประมวลผล ข้อมูลเหล่านี้มันอาจจะมีประโยชน์สำหรับเราในขณะที่ทำการดีบั๊กโค้ด แต่หลังจากนั้น มันก็จะไร้ประโยชน์โดยสิ้นเชิง โดยการเขียน SET NOCOUNT ON เราจะปิดการใช้งานฟังก์ชันนี้ สำหรับขั้นตอนการจัดเก็บที่มีนิพจน์หรือ/และลูปหลายรายการ การดำเนินการนี้สามารถเพิ่มประสิทธิภาพได้อย่างมาก เนื่องจากปริมาณการรับส่งข้อมูลจะลดลงอย่างมาก
ธุรกรรม-SQL
ใช้ชื่อสคีมากับชื่อวัตถุ:
ฉันคิดว่ามันชัดเจน การดำเนินการนี้จะบอกเซิร์ฟเวอร์ว่าจะค้นหาวัตถุได้ที่ไหน และแทนที่จะสุ่มค้นหาในถังขยะ มันจะรู้ทันทีว่าต้องไปที่ไหนและต้องทำอะไร ด้วยฐานข้อมูล ตาราง และขั้นตอนการจัดเก็บจำนวนมาก จึงสามารถประหยัดเวลาและความกังวลใจของเราได้อย่างมาก
ธุรกรรม-SQL
SELECT * FROM dbo.MyTable -- การทำเช่นนี้เป็นสิ่งที่ดี -- แทนที่จะเป็น SELECT * FROM MyTable -- และการทำเช่นนี้ไม่ดี -- การเรียกขั้นตอน EXEC dbo.MyProc -- ดีอีกครั้ง -- แทนที่จะเป็น EXEC MyProc --แย่!
อย่าใช้คำนำหน้า "sp_" ในชื่อของขั้นตอนการจัดเก็บของคุณ:
หากชื่อขั้นตอนของเราขึ้นต้นด้วย "sp_" เซิร์ฟเวอร์ SQLจะค้นหาฐานข้อมูลหลักก่อน ความจริงก็คือคำนำหน้านี้ใช้สำหรับขั้นตอนการจัดเก็บภายในส่วนบุคคลของเซิร์ฟเวอร์ ดังนั้นการใช้งานอาจนำไปสู่ค่าใช้จ่ายเพิ่มเติมและแม้กระทั่งผลลัพธ์ที่ไม่ถูกต้องหากพบขั้นตอนที่มีชื่อเดียวกันกับของคุณในฐานข้อมูล
ใช้ IF EXISTS (SELECT 1) แทน IF EXISTS (SELECT *):
ในการตรวจสอบการมีอยู่ของบันทึกในตารางอื่น เราใช้คำสั่ง IF EXISTS สำนวนนี้คืนค่าเป็นจริงหากส่งคืนค่าอย่างน้อยหนึ่งค่าจากนิพจน์ภายใน ซึ่งไม่สำคัญว่า "1" จะเป็นคอลัมน์ทั้งหมดหรือตาราง โดยพื้นฐานแล้วข้อมูลที่ส่งคืนจะไม่ถูกใช้ในทางใดทางหนึ่ง ดังนั้น ในการบีบอัดการรับส่งข้อมูลระหว่างการส่งข้อมูล จึงควรใช้ "1" ดังที่แสดงด้านล่าง
ขั้นตอนการเก็บ Stored Procedure) เป็นวัตถุโปรแกรมฐานข้อมูลที่มีชื่อ SQL Server มีขั้นตอนการจัดเก็บหลายประเภท
ขั้นตอนการจัดเก็บของระบบขั้นตอนการจัดเก็บของระบบ) จัดทำโดยนักพัฒนา DBMS และใช้เพื่อดำเนินการกับไดเร็กทอรีระบบหรือรับข้อมูลระบบ ชื่อของพวกเขามักจะขึ้นต้นด้วยคำนำหน้า "sp_" คุณรันกระบวนงานที่เก็บไว้ทุกประเภทโดยใช้คำสั่ง EXECUTE ซึ่งสามารถย่อให้เหลือ EXEC ได้ ตัวอย่างเช่น sp_helplogins กระบวนงานที่เก็บไว้ รันโดยไม่มีพารามิเตอร์ สร้างรายงานสองฉบับเกี่ยวกับชื่อบัญชี (ภาษาอังกฤษ)เข้าสู่ระบบ) และผู้ใช้ที่เกี่ยวข้องในแต่ละฐานข้อมูล (ภาษาอังกฤษ)ผู้ใช้)
EXEC sp_helplogins;
เพื่อให้ทราบถึงการดำเนินการที่ดำเนินการโดยใช้ขั้นตอนการจัดเก็บของระบบ ตารางที่ 1 10.6 แสดงตัวอย่างบางส่วน โดยรวมแล้วมีขั้นตอนการจัดเก็บระบบมากกว่าหนึ่งพันขั้นตอนใน SQL Server
ตารางที่ 10.6
ตัวอย่างระบบที่จัดเก็บ ขั้นตอน SQLเซิร์ฟเวอร์
ผู้ใช้สามารถสร้างขั้นตอนการจัดเก็บในฐานข้อมูลผู้ใช้และในฐานข้อมูลสำหรับออบเจ็กต์ชั่วคราว ในกรณีหลังนี้ขั้นตอนการจัดเก็บจะเป็นดังนี้ ชั่วคราวเช่นเดียวกับตารางชั่วคราว ชื่อของขั้นตอนการจัดเก็บชั่วคราวจะต้องขึ้นต้นด้วยคำนำหน้า "#" หากเป็นขั้นตอนการจัดเก็บชั่วคราวในเครื่อง หรือด้วย "##" หากเป็นขั้นตอนส่วนกลาง ขั้นตอนชั่วคราวในเครื่องสามารถใช้ได้เฉพาะภายในการเชื่อมต่อที่ถูกสร้างขึ้นเท่านั้น ส่วนขั้นตอนส่วนกลางยังสามารถใช้ภายในการเชื่อมต่ออื่น ๆ ได้อีกด้วย
วัตถุที่ตั้งโปรแกรมได้ของ SQL Server สามารถสร้างขึ้นได้โดยใช้เครื่องมือ Transact-SQL หรือแอสเซมบลี (ภาษาอังกฤษ)แอสเซมบลี) ในสภาพแวดล้อม CRL (Common Language Runtime) ของ Microsoft.Net Framework บทช่วยสอนนี้จะครอบคลุมเฉพาะวิธีแรกเท่านั้น
หากต้องการสร้างขั้นตอนการจัดเก็บ ให้ใช้คำสั่ง CREATE PROCEDURE (สามารถย่อเป็น PROC) ซึ่งมีรูปแบบดังต่อไปนี้:
สร้าง (ขั้นตอนที่ฉันดำเนินการ) proc_name [ ; ตัวเลข ]
[(ประเภทข้อมูลพารามิเตอร์ g)
[“ค่าเริ่มต้น] |
[กับ
[สำหรับการจำลอง]
AS ([ เริ่มต้น ] sql_statement [;] [ ...n ] [ END ] )
หากขั้นตอนการจัดเก็บ (หรือทริกเกอร์ ฟังก์ชัน มุมมอง) ถูกสร้างขึ้นด้วยตัวเลือกการเข้ารหัส โค้ดจะถูกแปลงในลักษณะที่ทำให้ข้อความไม่สามารถอ่านได้ ในเวลาเดียวกันตามที่ระบุไว้ในอัลกอริทึมที่ใช้ถูกถ่ายโอนจาก SQL Server เวอร์ชันก่อนหน้าและไม่ถือเป็นอัลกอริธึมการป้องกันที่เชื่อถือได้ - มีโปรแกรมอรรถประโยชน์ที่ช่วยให้คุณทำการแปลงย้อนกลับได้อย่างรวดเร็ว
ตัวเลือก RECOMPILE ระบุว่าระบบจะคอมไพล์ข้อความใหม่ทุกครั้งที่เรียกใช้ขั้นตอน ในกรณีปกติ ขั้นตอนที่คอมไพล์ในการรันครั้งแรกจะถูกจัดเก็บไว้ในแคช ซึ่งช่วยให้สามารถเพิ่มประสิทธิภาพได้
EXECUTE AS ระบุบริบทด้านความปลอดภัยที่ขั้นตอนจะดำเนินการ ถัดไปหนึ่งในค่า f CALLER | ตนเอง | เจ้าของ | "ชื่อผู้ใช้"). CALLER เป็นค่าเริ่มต้นและหมายความว่าโค้ดจะถูกดำเนินการในบริบทด้านความปลอดภัยของผู้ใช้ที่เรียกใช้โมดูลนี้ ดังนั้น ผู้ใช้จะต้องมีสิทธิ์ไม่เพียงแต่กับอ็อบเจ็กต์ที่ตั้งโปรแกรมได้เท่านั้น แต่ยังรวมถึงอ็อบเจ็กต์ฐานข้อมูลอื่นที่ได้รับผลกระทบจากอ็อบเจ็กต์นั้นด้วย EXECUTE AS SELF หมายถึงการใช้บริบทของผู้ใช้ในการสร้างหรือแก้ไขวัตถุที่ตั้งโปรแกรมได้ OWNER ระบุว่าโค้ดจะถูกดำเนินการในบริบทของเจ้าของปัจจุบันของโพรซีเดอร์ หากไม่มีการระบุเจ้าของ ระบบจะถือว่าเจ้าของสคีมาเป็นเจ้าของ EXECUTE AS "user_name" ช่วยให้คุณสามารถระบุชื่อผู้ใช้ได้อย่างชัดเจน (ในเครื่องหมายคำพูดเดี่ยว)
สามารถระบุพารามิเตอร์สำหรับขั้นตอนได้ เหล่านี้เป็นตัวแปรท้องถิ่นที่ใช้ในการส่งผ่านค่าไปยังขั้นตอน หากพารามิเตอร์ถูกประกาศด้วยคีย์เวิร์ด OUTPUT (หรือเรียกสั้นๆ ว่า OUT) พารามิเตอร์นั้นจะเป็นค่าเอาต์พุต: ค่าที่กำหนดให้กับพารามิเตอร์นั้นในโพรซีเดอร์หลังจากเสร็จสิ้นแล้วสามารถใช้งานได้โดยโปรแกรมที่เรียกโพรซีเดอร์นั้น คำสำคัญ READONLY หมายความว่าค่าของพารามิเตอร์ไม่สามารถเปลี่ยนแปลงภายในกระบวนงานที่เก็บไว้
สามารถกำหนดค่าพารามิเตอร์เป็นค่าเริ่มต้นได้ ซึ่งจะถูกนำมาใช้หากไม่ได้ระบุค่าพารามิเตอร์ไว้อย่างชัดเจนเมื่อเรียกใช้ขั้นตอน ลองดูตัวอย่าง:
สร้าง PROC surma (@a int, @b int=0,
©ผลลัพธ์ int OUTPUT) AS
SET @result=0a+0b
เราได้สร้างโพรซีเดอร์ที่มีพารามิเตอร์สามตัว และพารามิเตอร์ @b มีค่าเริ่มต้นเป็น =0 และพารามิเตอร์ @result เป็นพารามิเตอร์เอาต์พุต: ส่งคืนค่าไปยังโปรแกรมที่เรียกใช้ การดำเนินการที่ทำนั้นค่อนข้างง่าย - พารามิเตอร์เอาต์พุตจะได้รับค่าผลรวมของอินพุตสองตัว
เมื่อทำงานใน SQL Server Management Studio คุณสามารถดูขั้นตอนการจัดเก็บที่สร้างขึ้นได้ในส่วนออบเจ็กต์ฐานข้อมูลที่ตั้งโปรแกรมได้ (ภาษาอังกฤษ)ความสามารถในการตั้งโปรแกรม) ในส่วนย่อยสำหรับขั้นตอนการจัดเก็บ (รูปที่ 10.2)
เมื่อเรียกโพรซีเดอร์ คุณสามารถใช้ทั้งตัวแปรและค่าคงที่เป็นพารามิเตอร์อินพุตได้ ลองดูสองตัวอย่าง ในตอนแรก พารามิเตอร์อินพุตของขั้นตอนจะถูกระบุอย่างชัดเจนเป็นค่าคงที่ และคีย์เวิร์ด OUTPUT จะถูกระบุสำหรับพารามิเตอร์เอาต์พุตในการเรียก ในตัวเลือกที่สอง ค่าของตัวแปรจะถูกใช้เป็นพารามิเตอร์อินพุตตัวแรก และสำหรับพารามิเตอร์ตัวที่สองที่ใช้ คำสำคัญ DEFAULT บ่งชี้ว่าควรใช้ค่าเริ่มต้น:
ข้าว. 10.2.
ประกาศ @сint;
EXEC สรุป 10.5,@c เอาต์พุต;
พิมพ์ 0c; – 15 จะปรากฏขึ้น
ประกาศ Gi int = 5;
– เมื่อโทรใช้ค่าเริ่มต้น
EXEC สรุป Gi,ค่าเริ่มต้น, 0c เอาต์พุต;
พิมพ์ 0c; – 5 จะปรากฏขึ้น
ตอนนี้ให้เราพิจารณาตัวอย่างด้วยการวิเคราะห์โค้ดส่งคืนที่ขั้นตอนสิ้นสุดลง สมมติว่าเราต้องคำนวณจำนวนหนังสือในตาราง Bookl ที่ตีพิมพ์ในช่วงปีที่กำหนด นอกจากนี้ หากปีเริ่มต้นมากกว่าปีสุดท้าย ขั้นตอนจะส่งกลับ "1" และไม่นับ มิฉะนั้น เราจะนับจำนวนหนังสือและส่งกลับ 0:
สร้าง PROC dbo.rownum (0FirsYear int, GLastYear int, 0 ผลลัพธ์ int OUTPUT) AS
ถ้า 0FirsYear>0LastYear ผลตอบแทน 1
SET @result= (SELECT COUNT(*) จาก dbo.Bookl
โดยที่ระหว่าง 0FirsYear และ 0LastYear);
ลองพิจารณาตัวแปรในการเรียกโพรซีเดอร์นี้ ซึ่งโค้ดส่งคืนจะถูกเก็บไว้ในตัวแปรจำนวนเต็ม 0ret หลังจากนั้นจึงวิเคราะห์ค่าของมัน (ในกรณีนี้จะเป็น 1) ใช้ในแถลงการณ์ ฟังก์ชันการพิมพ์ CAST ใช้ในการแปลงค่าของตัวแปรจำนวนเต็ม Gres เป็นประเภทสตริง:
ประกาศ 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
ถ้า 0ret=l พิมพ์ "ปีที่เริ่มต้นมากกว่าปีที่สิ้นสุด"
พิมพ์ "จำนวนหนังสือ" + CAST(Gres as varchar(20))
ขั้นตอนที่เก็บไว้ไม่เพียงแต่สามารถอ่านข้อมูลจากตารางเท่านั้น แต่ยังแก้ไขข้อมูลและแม้แต่สร้างตารางและออบเจ็กต์ฐานข้อมูลอื่นๆ จำนวนหนึ่งอีกด้วย
อย่างไรก็ตาม คุณไม่สามารถสร้างสคีมา ฟังก์ชัน ทริกเกอร์ ขั้นตอน และมุมมองจากขั้นตอนที่เก็บไว้ได้
ตัวอย่างต่อไปนี้แสดงให้เห็นทั้งความสามารถและปัญหาที่เกี่ยวข้องกับขอบเขตของออบเจ็กต์ชั่วคราว ขั้นตอนการจัดเก็บต่อไปนี้จะตรวจสอบการมีอยู่ของตารางชั่วคราว #TaL2; หากไม่มีตารางนี้ ก็จะสร้างขึ้นมา หลังจากนั้น ค่าของสองคอลัมน์จะถูกป้อนลงในตาราง #TaL2 และเนื้อหาของตารางจะแสดงโดยใช้คำสั่ง SELECT:
สร้าง PROC My_Procl (@id int, @name varchar(30))
ถ้า OBJECT_ID("tempdb.dbo.#Tab21) เป็นโมฆะ
แทรกลงใน dbo.#Tab2 (id, ชื่อ) ค่า (0id, 0name)
SELECT * จาก dbo #Tab2 –หมายเลข1
ก่อนที่จะเรียกใช้ Stored Procedure เป็นครั้งแรก เราจะสร้างตารางชั่วคราว #TaL2 ที่ใช้ในนั้น ให้ความสนใจกับตัวดำเนินการ EXEC ในตัวอย่างก่อนหน้านี้ พารามิเตอร์ถูกส่งไปยังขั้นตอน "ตามตำแหน่ง" แต่ในกรณีนี้ จะใช้รูปแบบที่แตกต่างกันสำหรับการส่งพารามิเตอร์ - "ตามชื่อ" ชื่อของพารามิเตอร์และค่าจะถูกระบุอย่างชัดเจน:
สร้างตาราง dbo.#Tab2 (id int ชื่อ varchar(30));
EXEC My_Procl 0name = "lvan", 0id = 2;
SELECT * จาก dbo.#Tab2; –หมายเลข 2
ในตัวอย่างข้างต้น คำสั่ง SELECT จะถูกประมวลผลสองครั้ง: ครั้งแรก – ภายในโพรซีเดอร์, ครั้งที่สอง – จากส่วนของรหัสการโทร (ทำเครื่องหมายด้วยความคิดเห็น “หมายเลข 2”)
ก่อนที่จะเรียกขั้นตอนที่สอง เราจะลบตารางชั่วคราว #TaL2 จากนั้นตารางชั่วคราวที่มีชื่อเดียวกันจะถูกสร้างขึ้นจากขั้นตอนการจัดเก็บ:
วางตาราง dbo.#Tab2;
EXEC My_Procl 0name = "อีวาน", 0id = 2;
SELECT * จาก dbo.#Tab2; –หมายเลข 2
ในกรณีนี้ เฉพาะคำสั่ง SELECT ที่อยู่ภายในโพรซีเดอร์ (ที่มีเครื่องหมาย "Xa 1") เท่านั้นที่จะแสดงข้อมูล การดำเนินการ SELECT "หมายเลข 2" จะส่งผลให้เกิดข้อผิดพลาด เนื่องจากตารางชั่วคราวที่สร้างขึ้นในกระบวนงานที่เก็บไว้จะถูกลบออกจากฐานข้อมูล tempdb ในขณะที่กระบวนงานส่งคืน
คุณสามารถละทิ้งขั้นตอนการจัดเก็บโดยใช้คำสั่ง DROP PROCEDURE รูปแบบของมันถูกนำเสนอด้านล่าง คุณสามารถลบโพรซีเดอร์ที่เก็บไว้หลายๆ โพรซีเดอร์ได้ด้วยคำสั่งเดียว โดยแสดงรายการโดยคั่นด้วยเครื่องหมายจุลภาค:
DROP (PROC I ขั้นตอน) ( ขั้นตอน ) [
ตัวอย่างเช่น ลองลบขั้นตอน summa ที่สร้างขึ้นก่อนหน้านี้:
สรุป DROP PROC;
คุณสามารถเปลี่ยนแปลงขั้นตอนที่มีอยู่ (และในความเป็นจริง กำหนดใหม่) โดยใช้คำสั่ง ALTER PROCEDURE (อนุญาต
อักษรย่อ ป.ร.ก.) ยกเว้นคีย์เวิร์ด ALTER รูปแบบของคำสั่งจะเหมือนกับของ CREATE PROCEDURE ตัวอย่างเช่น ลองเปลี่ยนขั้นตอน dbo rownum ตั้งค่าให้ดำเนินการในบริบทความปลอดภัยของเจ้าของ:
แก้ไข PROC dbo.rownum (SFirsYear int,
SLastYear int, ผลลัพธ์ int OUTPUT)
ด้วย EXECUTE AS Owner - ตัวเลือกที่สามารถติดตั้งได้
ถ้า 0FirsYear>0LastYear ส่งคืน 1 มิฉะนั้นจะเริ่มต้น
SET 0result= (SELECT COUNT(*) จาก dbo.Bookl
ที่ไหนระหว่าง SFirsYear และ SLastYear);
ในบางกรณี อาจจำเป็นต้องสร้างคำสั่งแบบไดนามิกและดำเนินการบนเซิร์ฟเวอร์ฐานข้อมูล ปัญหานี้สามารถแก้ไขได้โดยใช้ตัวดำเนินการ EXEC ตัวอย่างด้านล่างดึงข้อมูลบันทึกจากตาราง Bookl หากแอตทริบิวต์ Year เท่ากับค่าที่ระบุโดยตัวแปร:
ประกาศ 0у int = 2000;
EXEC ("SELECT * จาก dbo.Bookl WHERE = "+@y) ;
การดำเนินการตามคำสั่งที่สร้างขึ้นแบบไดนามิกจะสร้างเงื่อนไขเบื้องต้นสำหรับการดำเนินการโจมตีคอมพิวเตอร์ เช่น “SQL injector” (ภาษาอังกฤษ)การฉีด SQL) สาระสำคัญของการโจมตีคือผู้โจมตีแทรกโค้ด SQL ของตัวเองลงในแบบสอบถามที่สร้างขึ้นแบบไดนามิก ซึ่งมักเกิดขึ้นเมื่อพารามิเตอร์ที่ถูกแทนที่ถูกนำมาจากผลลัพธ์ของการป้อนข้อมูลของผู้ใช้
ลองเปลี่ยนตัวอย่างก่อนหน้านี้เล็กน้อย:
ประกาศ 0у varchar (100);
SET 0у="2ООО"; – เราได้รับสิ่งนี้จากผู้ใช้
หากเราถือว่าเราได้รับค่าสตริงที่กำหนดในคำสั่ง SET จากผู้ใช้ (ไม่ว่าจะอย่างไร เช่น ผ่านเว็บแอปพลิเคชัน) ตัวอย่างนี้จะแสดงพฤติกรรม "ปกติ" ของโค้ดของเรา
ประกาศ 0у varchar (100);
SET 0у="2000; ลบจาก dbo.Book2"; – การฉีด
EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);
หากเป็นไปได้ ขอแนะนำให้ใช้ขั้นตอนการจัดเก็บของระบบ sp_executcsql ในกรณีดังกล่าว ซึ่งช่วยให้คุณควบคุมประเภทของพารามิเตอร์ ซึ่งเป็นหนึ่งในอุปสรรคในการ การฉีด SQL. มาดูตัวอย่างที่คล้ายกับตัวอย่างที่นำเสนอก่อนหน้านี้โดยไม่ต้องคำนึงถึงรูปแบบโดยละเอียด:
ดำเนินการ sp_executesql
N"เลือก * จาก dbo.Bookl WHERE =0y",
ซึ่งจะระบุประเภทของพารามิเตอร์ที่ใช้ในการสืบค้นอย่างชัดเจน และ SQL Server จะควบคุมพารามิเตอร์ดังกล่าวระหว่างการดำเนินการ ตัวอักษร "N" หน้าเครื่องหมายคำพูดระบุว่านี่คือค่าคงที่ตามตัวอักษร Unicode ตามที่กำหนดในขั้นตอน พารามิเตอร์สามารถกำหนดได้ไม่เพียงแต่ค่าคงที่ แต่ยังรวมถึงค่าของตัวแปรอื่นด้วย
1. ใส่บรรทัดในขั้นตอนของคุณ - ตั้งค่า NOCOUNT ON:ด้วยนิพจน์ DML แต่ละรายการ เซิร์ฟเวอร์ SQL จะส่งข้อความกลับถึงเราอย่างระมัดระวังซึ่งมีจำนวนบันทึกที่ประมวลผล ข้อมูลนี้อาจเป็นประโยชน์สำหรับเราในขณะที่แก้ไขโค้ด แต่หลังจากนั้นจะไม่มีประโยชน์โดยสิ้นเชิง โดยการเขียน SET NOCOUNT ON เราจะปิดการใช้งานฟังก์ชันนี้ สำหรับขั้นตอนการจัดเก็บที่มีนิพจน์หรือ/และลูปหลายรายการ การดำเนินการนี้สามารถเพิ่มประสิทธิภาพได้อย่างมาก เนื่องจากปริมาณการรับส่งข้อมูลจะลดลงอย่างมาก
สร้าง PROC dbo.ProcName
เช่น
ตั้งหมายเลขบน;
--นี่คือรหัสขั้นตอน
เลือก column1 จาก dbo.TblTable1
--Toggle SET NOCOUNT เป็นสถานะเริ่มต้น
ตั้งค่าจำนวนปิด;
ไป
2. ใช้ชื่อสคีมากับชื่อวัตถุ:ฉันคิดว่ามันชัดเจน การดำเนินการนี้จะบอกเซิร์ฟเวอร์ว่าจะค้นหาวัตถุได้ที่ไหน และแทนที่จะสุ่มค้นหาในถังขยะ มันจะรู้ทันทีว่าต้องไปที่ไหนและต้องทำอะไร ด้วยฐานข้อมูล ตาราง และขั้นตอนการจัดเก็บจำนวนมาก จึงสามารถประหยัดเวลาและความกังวลใจของเราได้อย่างมาก
SELECT * FROM dbo.MyTable --นี่เป็นสิ่งที่ดีที่จะทำ
-- แทน
SELECT * FROM MyTable -- และการทำเช่นนี้ถือเป็นเรื่องไม่ดี
--การเรียกขั้นตอน
EXEC dbo.MyProc --ดีอีกครั้ง
--แทน
EXEC MyProc --แย่!
3. อย่าใช้คำนำหน้า “sp_” ในชื่อของขั้นตอนการจัดเก็บของคุณ:หากชื่อขั้นตอนของเราขึ้นต้นด้วย "sp_" SQL Server จะค้นหาในฐานข้อมูลหลักก่อน ความจริงก็คือคำนำหน้านี้ใช้สำหรับขั้นตอนการจัดเก็บภายในส่วนบุคคลของเซิร์ฟเวอร์ ดังนั้นการใช้งานอาจนำไปสู่ค่าใช้จ่ายเพิ่มเติมและแม้กระทั่งผลลัพธ์ที่ไม่ถูกต้องหากพบขั้นตอนที่มีชื่อเดียวกันกับของคุณในฐานข้อมูล
4. ใช้ IF EXISTS (SELECT 1) แทน IF EXISTS (SELECT *):ในการตรวจสอบการมีอยู่ของบันทึกในตารางอื่น เราใช้คำสั่ง IF EXISTS นิพจน์นี้จะคืนค่าเป็นจริงหากมีการส่งคืนค่าอย่างน้อยหนึ่งค่าจากนิพจน์ภายใน ซึ่งไม่สำคัญว่า "1" จะเป็นทุกคอลัมน์หรือตาราง โดยพื้นฐานแล้วข้อมูลที่ส่งคืนจะไม่ถูกใช้ในทางใดทางหนึ่ง ดังนั้น ในการบีบอัดการรับส่งข้อมูลระหว่างการส่งข้อมูล การใช้ "1" จึงมีเหตุผลมากกว่า ดังที่แสดงด้านล่าง:
หากมีอยู่ (เลือก 1 จาก sysobjects
โดยที่ชื่อ = "MyTable" และพิมพ์ = "U")
5. ใช้ TRY-Catch เพื่อตรวจจับข้อผิดพลาด:ก่อนเซิร์ฟเวอร์ปี 2005 หลังจากการร้องขอแต่ละครั้ง มีการเขียนการตรวจสอบข้อผิดพลาดจำนวนมากในขั้นตอนนี้ โค้ดที่มากขึ้นจะใช้ทรัพยากรและเวลามากขึ้นเสมอ ด้วย SQL Server ปี 2005 ความถูกต้องและมากขึ้น วิธีที่สะดวกวิธีแก้ไขปัญหานี้:
เริ่มลอง
--รหัส
สิ้นสุดการลอง
เริ่มจับ
--ข้อผิดพลาดในการจับรหัส
จบการจับ
บทสรุป
โดยพื้นฐานแล้วนั่นคือทั้งหมดที่ฉันมีสำหรับวันนี้ ฉันขอย้ำอีกครั้งว่านี่เป็นเพียงเทคนิคที่ฉันใช้เป็นการส่วนตัวในการฝึกฝนและฉันสามารถรับรองประสิทธิภาพได้
ป.ล.
กระทู้แรกของผม อย่าตัดสินรุนแรงจนเกินไป
ขั้นตอนการเก็บเป็นแพ็คเกจคำสั่ง 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 ช่วยให้คุณสามารถเปลี่ยนชื่อและชนิดข้อมูลของคอลัมน์ในชุดผลลัพธ์ที่สร้างโดยโพรซีเดอร์ ดังนั้นฟังก์ชันใหม่นี้จึงให้ความยืดหยุ่นมากขึ้นในการดำเนินการตามขั้นตอนการจัดเก็บและวางผลลัพธ์ไว้ในตารางใหม่
การเปลี่ยนโครงสร้างของขั้นตอนการจัดเก็บ
โปรแกรมฐานข้อมูลยังรองรับคำสั่งอีกด้วย เปลี่ยนแปลงขั้นตอนเพื่อปรับเปลี่ยนโครงสร้างของขั้นตอนการจัดเก็บ คำสั่งเปลี่ยนแปลงโดยทั่วไป PROCEDURE จะใช้เพื่อแก้ไขคำสั่ง Transact-SQL ภายในขั้นตอน พารามิเตอร์ทั้งหมดของคำสั่ง ALTER PROCEDURE มีความหมายเหมือนกับพารามิเตอร์เดียวกันของคำสั่ง CREATE PROCEDURE วัตถุประสงค์หลักของการใช้คำสั่งนี้คือเพื่อหลีกเลี่ยงการแทนที่สิทธิ์ในกระบวนงานที่เก็บไว้ที่มีอยู่
โปรแกรมฐานข้อมูลรองรับ ชนิดข้อมูลเคอร์เซอร์. ชนิดข้อมูลนี้ใช้ในการประกาศเคอร์เซอร์ในกระบวนงานที่เก็บไว้ เคอร์เซอร์เป็นโครงสร้างการเขียนโปรแกรมที่ใช้ในการจัดเก็บผลลัพธ์ของการสืบค้น (โดยปกติจะเป็นชุดของแถว) และอนุญาตให้ผู้ใช้แสดงผลลัพธ์นั้นทีละแถว
หากต้องการลบขั้นตอนที่เก็บไว้หนึ่งหรือกลุ่ม ให้ใช้ คำแนะนำขั้นตอน DROP. เฉพาะเจ้าของหรือสมาชิกของบทบาทคงที่ db_owner และผู้ดูแลระบบเท่านั้นที่สามารถลบขั้นตอนที่เก็บไว้ได้
ขั้นตอนการจัดเก็บและรันไทม์ภาษาทั่วไป
SQL Server รองรับ Common Language Runtime (CLR) ซึ่งช่วยให้คุณพัฒนาอ็อบเจ็กต์ฐานข้อมูลต่างๆ (ขั้นตอนการจัดเก็บ ฟังก์ชันที่ผู้ใช้กำหนด ทริกเกอร์ การรวมที่ผู้ใช้กำหนด และประเภทข้อมูลที่กำหนดเอง) โดยใช้ C# และ วิชวลเบสิก. CLR ยังช่วยให้คุณเรียกใช้งานออบเจ็กต์เหล่านี้ได้โดยใช้ระบบรันไทม์ทั่วไป
รันไทม์ภาษาทั่วไปถูกเปิดใช้งานและปิดใช้งานโดยใช้ตัวเลือก clr_เปิดใช้งานขั้นตอนของระบบ sp_configureซึ่งเปิดตัวเพื่อดำเนินการตามคำสั่ง กำหนดค่าใหม่. ตัวอย่างต่อไปนี้แสดงวิธีที่คุณสามารถใช้ขั้นตอนของระบบ sp_configure เพื่อเปิดใช้งาน CLR:
ใช้ SampleDb; EXEC sp_configure "clr_enabled",1 กำหนดค่าใหม่
หากต้องการสร้าง คอมไพล์ และบันทึกขั้นตอนโดยใช้ CLR คุณต้องดำเนินการตามลำดับขั้นตอนต่อไปนี้ตามลำดับที่แสดง:
สร้างกระบวนงานที่เก็บไว้ใน C# หรือ Visual Basic แล้วคอมไพล์โดยใช้คอมไพลเลอร์ที่เหมาะสม
การใช้คำแนะนำ สร้างแอสเซมบลีให้สร้างไฟล์ปฏิบัติการที่เกี่ยวข้อง
ดำเนินการตามขั้นตอนโดยใช้คำสั่ง EXECUTE
ภาพด้านล่างแสดงให้เห็น แผนภาพกราฟิกขั้นตอนที่อธิบายไว้ก่อนหน้านี้ ด้านล่างมีเพิ่มเติม คำอธิบายโดยละเอียดกระบวนการนี้
ขั้นแรกให้สร้างโปรแกรมที่จำเป็นในสภาพแวดล้อมการพัฒนาบางอย่างเช่น วิชวลสตูดิโอ. คอมไพล์โปรแกรมที่เสร็จแล้วเป็นออบเจ็กต์โค้ดโดยใช้คอมไพเลอร์ 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การประกอบลบแอสเซมบลีที่ระบุและไฟล์ที่เกี่ยวข้องทั้งหมดออกจากฐานข้อมูลปัจจุบัน
ตัวอย่างด้านล่างแสดงวิธีการสร้างขั้นตอนการจัดเก็บตามโค้ดที่ได้รับการจัดการที่คุณนำไปใช้ก่อนหน้านี้:
ใช้ 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 ส่งคืนจำนวนแถวปัจจุบันในตารางพนักงาน
ขั้นตอนการเก็บเป็นไปได้เฉพาะในกรณีที่ดำเนินการในบริบทของฐานข้อมูลที่มีขั้นตอนอยู่ประเภทของขั้นตอนการจัดเก็บ
SQL Server มีหลายประเภท ขั้นตอนการจัดเก็บ.
- ระบบ ขั้นตอนการจัดเก็บออกแบบมาเพื่อดำเนินการด้านการบริหารต่างๆ กิจกรรมการดูแลเซิร์ฟเวอร์เกือบทั้งหมดดำเนินการได้ด้วยความช่วยเหลือ เราสามารถพูดได้ว่าเป็นระบบ ขั้นตอนการจัดเก็บเป็นอินเทอร์เฟซที่ให้การทำงานร่วมกับตารางระบบ ซึ่งท้ายที่สุดแล้วอยู่ที่การเปลี่ยนแปลง เพิ่ม ลบ และดึงข้อมูลจากตารางระบบของทั้งฐานข้อมูลผู้ใช้และฐานข้อมูลระบบ ระบบ ขั้นตอนการจัดเก็บมีคำนำหน้า sp_ เก็บไว้ในฐานข้อมูลระบบและสามารถเรียกได้ในบริบทของฐานข้อมูลอื่น
- กำหนดเอง ขั้นตอนการจัดเก็บดำเนินการบางอย่าง ขั้นตอนการจัดเก็บ– วัตถุฐานข้อมูลเต็มรูปแบบ ส่งผลให้แต่ละคน ขั้นตอนการเก็บตั้งอยู่ในฐานข้อมูลเฉพาะที่ดำเนินการ
- ชั่วคราว ขั้นตอนการจัดเก็บมีอยู่เพียงชั่วระยะเวลาหนึ่งเท่านั้น หลังจากนั้นเซิร์ฟเวอร์จะถูกทำลายโดยอัตโนมัติ แบ่งออกเป็นระดับท้องถิ่นและระดับโลก ท้องถิ่นชั่วคราว ขั้นตอนการจัดเก็บสามารถเรียกได้จากการเชื่อมต่อที่สร้างขึ้นเท่านั้น เมื่อสร้างขั้นตอนดังกล่าว คุณต้องตั้งชื่อให้ขึ้นต้นด้วยอักขระ # ตัวเดียว เช่นเดียวกับวัตถุชั่วคราวทั้งหมด ขั้นตอนการจัดเก็บประเภทนี้จะถูกลบโดยอัตโนมัติเมื่อผู้ใช้ยกเลิกการเชื่อมต่อหรือเซิร์ฟเวอร์รีสตาร์ทหรือหยุดทำงาน ทั่วโลกชั่วคราว ขั้นตอนการจัดเก็บพร้อมใช้งานสำหรับการเชื่อมต่อใด ๆ จากเซิร์ฟเวอร์ที่มีขั้นตอนเดียวกัน หากต้องการกำหนด เพียงตั้งชื่อโดยขึ้นต้นด้วยอักขระ ## ขั้นตอนเหล่านี้จะถูกลบออกเมื่อเซิร์ฟเวอร์รีสตาร์ทหรือหยุดทำงาน หรือเมื่อปิดการเชื่อมต่อในบริบทที่สร้างขึ้น
สร้าง แก้ไข และลบขั้นตอนการจัดเก็บ
การสร้าง ขั้นตอนการเก็บเกี่ยวข้องกับการแก้ปัญหาต่อไปนี้:
- กำหนดประเภทของการสร้าง ขั้นตอนการเก็บ: ชั่วคราวหรือกำหนดเอง นอกจากนี้คุณสามารถสร้างระบบของคุณเองได้ ขั้นตอนการเก็บโดยตั้งชื่อนำหน้าด้วย sp_ และวางไว้ในฐานข้อมูลระบบ ขั้นตอนนี้จะพร้อมใช้งานในบริบทของฐานข้อมูลเซิร์ฟเวอร์ท้องถิ่น
- การวางแผนสิทธิ์การเข้าถึง ในขณะที่กำลังสร้าง ขั้นตอนการเก็บควรคำนึงว่าจะมีสิทธิ์การเข้าถึงวัตถุฐานข้อมูลเช่นเดียวกับผู้ใช้ที่สร้างขึ้น
- คำนิยาม พารามิเตอร์ขั้นตอนการจัดเก็บ. คล้ายกับขั้นตอนที่รวมอยู่ในภาษาการเขียนโปรแกรมส่วนใหญ่ ขั้นตอนการจัดเก็บอาจมีพารามิเตอร์อินพุตและเอาต์พุต
- การพัฒนารหัส ขั้นตอนการเก็บ. รหัสขั้นตอนสามารถมีลำดับของคำสั่ง SQL ใดๆ รวมถึงการเรียกคำสั่งอื่นๆ ขั้นตอนการจัดเก็บ.
การสร้างสิ่งใหม่และการเปลี่ยนแปลงสิ่งที่มีอยู่ ขั้นตอนการเก็บเสร็จสิ้นโดยใช้คำสั่งต่อไปนี้:
<определение_процедуры>::= (สร้าง | แก้ไข ) Procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]
มาดูพารามิเตอร์ของคำสั่งนี้กัน
การใช้คำนำหน้า sp_ , # , ## ขั้นตอนที่สร้างขึ้นสามารถกำหนดเป็นระบบหรือชั่วคราวได้ ดังที่คุณเห็นจากไวยากรณ์คำสั่ง ไม่อนุญาตให้ระบุชื่อของเจ้าของที่จะเป็นเจ้าของขั้นตอนที่สร้างขึ้น รวมถึงชื่อของฐานข้อมูลที่ควรตั้งอยู่ ดังนั้นเพื่อที่จะวางสิ่งที่สร้างขึ้น ขั้นตอนการเก็บในฐานข้อมูลเฉพาะ คุณต้องออกคำสั่ง CREATE PROCEDURE ในบริบทของฐานข้อมูลนั้น เมื่อหันออกจากร่างกาย ขั้นตอนการเก็บชื่อที่สั้นลงสามารถใช้กับออบเจ็กต์ของฐานข้อมูลเดียวกันได้ กล่าวคือ โดยไม่ต้องระบุชื่อฐานข้อมูล เมื่อคุณต้องการเข้าถึงออบเจ็กต์ที่อยู่ในฐานข้อมูลอื่น จำเป็นต้องระบุชื่อฐานข้อมูล
หมายเลขในชื่อคือหมายเลขประจำตัว ขั้นตอนการเก็บซึ่งระบุได้โดยไม่ซ้ำกันในกลุ่มของขั้นตอน เพื่อความสะดวกในการจัดการ ขั้นตอนต่างๆ จะเป็นประเภทเดียวกันตามตรรกะ ขั้นตอนการจัดเก็บสามารถจัดกลุ่มได้โดยการตั้งชื่อให้เหมือนกันแต่มีหมายเลขประจำตัวต่างกัน
เพื่อถ่ายโอนข้อมูลอินพุตและเอาต์พุตในการสร้าง ขั้นตอนการเก็บสามารถใช้พารามิเตอร์ได้ ชื่อที่ต้องขึ้นต้นด้วยสัญลักษณ์ @ เช่นเดียวกับชื่อของตัวแปรท้องถิ่น หนึ่ง ขั้นตอนการเก็บคุณสามารถระบุหลายพารามิเตอร์โดยคั่นด้วยเครื่องหมายจุลภาค เนื้อความของขั้นตอนไม่ควรใช้ตัวแปรท้องถิ่นที่มีชื่อตรงกับชื่อของพารามิเตอร์ของขั้นตอนนี้
เพื่อกำหนดชนิดข้อมูลให้สอดคล้องกัน พารามิเตอร์ขั้นตอนการจัดเก็บแบบไหนก็เหมาะ ข้อมูล SQLรวมถึงสิ่งที่ผู้ใช้กำหนดด้วย อย่างไรก็ตาม ชนิดข้อมูล CURSOR สามารถใช้เป็นข้อมูลได้เท่านั้น พารามิเตอร์เอาท์พุท ขั้นตอนการเก็บ, เช่น. การระบุคีย์เวิร์ด OUTPUT
การมีอยู่ของคีย์เวิร์ด OUTPUT หมายความว่าพารามิเตอร์ที่เกี่ยวข้องมีจุดประสงค์เพื่อส่งคืนข้อมูล ขั้นตอนการเก็บ. อย่างไรก็ตามนี่ไม่ได้หมายความว่าพารามิเตอร์ไม่เหมาะสำหรับการส่งค่าไปให้ ขั้นตอนการเก็บ. การระบุคีย์เวิร์ด OUTPUT จะสั่งให้เซิร์ฟเวอร์ออก ขั้นตอนการเก็บกำหนดค่าปัจจุบันของพารามิเตอร์ให้กับตัวแปรท้องถิ่นที่ระบุไว้เมื่อเรียกใช้ขั้นตอนเป็นค่าของพารามิเตอร์ โปรดทราบว่าเมื่อระบุคีย์เวิร์ด OUTPUT ค่าของพารามิเตอร์ที่เกี่ยวข้องเมื่อเรียกใช้ขั้นตอนสามารถตั้งค่าได้โดยใช้ตัวแปรท้องถิ่นเท่านั้น ไม่อนุญาตนิพจน์หรือค่าคงที่ใดๆ ที่ได้รับอนุญาตสำหรับพารามิเตอร์ปกติ
คำหลัก VARYING ใช้ร่วมกับ