ผู้เขียนบทความนี้จะแนะนำวิธีการเรียกใช้ Stored Procedure ของ SQL Server ผ่านทาง Java และอธิบายรายละเอียด Storage 5 ประเภทที่แตกต่างกัน โปรดดูรายละเอียดด้านล่าง
1. ใช้ขั้นตอนการจัดเก็บโดยไม่มีพารามิเตอร์
เมื่อใช้ไดร์เวอร์ JDBC เพื่อเรียกโพรซีเดอร์ที่เก็บไว้โดยไม่มีพารามิเตอร์ คุณต้องใช้ลำดับหลีกการเรียก SQL ไวยากรณ์สำหรับลำดับการหลีกเลี่ยงการโทรที่ไม่มีพารามิเตอร์จะเป็นดังนี้:
คัดลอกรหัสรหัสดังต่อไปนี้:
{ชื่อขั้นตอนการโทร}
เป็นตัวอย่าง สร้างขั้นตอนการจัดเก็บต่อไปนี้ในฐานข้อมูลตัวอย่าง SQL Server 2005 AdventureWorks:
คัดลอกรหัสรหัสดังต่อไปนี้:
สร้างขั้นตอน GetContactFormalNames
เช่น
เริ่ม
เลือกชื่อ 10 อันดับแรก + ' ' + ชื่อ + ' ' + นามสกุล AS ชื่อทางการ
จากบุคคลติดต่อ
จบ
กระบวนงานที่เก็บไว้นี้ส่งคืนชุดผลลัพธ์เดียวที่มีคอลัมน์ข้อมูลที่ประกอบด้วยชื่อเรื่อง ชื่อ และนามสกุลของผู้ติดต่อสิบคนแรกในตาราง Person.Contact
ในตัวอย่างต่อไปนี้ ฟังก์ชันนี้จะถูกส่งผ่านการเชื่อมต่อแบบเปิดไปยังฐานข้อมูลตัวอย่าง AdventureWorks จากนั้นกระบวนงานที่เก็บไว้ GetContactFormalNames จะถูกเรียกโดยใช้วิธีดำเนินการแบบสอบถาม
คัดลอกรหัสรหัสดังต่อไปนี้:
โมฆะคงที่สาธารณะดำเนินการ SprocNoParams (การเชื่อมต่อ) ... {
พยายาม...{
คำสั่ง stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
ในขณะที่ (rs.next()) ...{
System.out.println(rs.getString("ชื่อทางการ"));
-
rs.ปิด();
stmt.ปิด();
-
catch (ข้อยกเว้น e) ...{
e.printStackTrace();
-
-
2. ใช้ขั้นตอนการจัดเก็บพร้อมกับพารามิเตอร์อินพุต
เมื่อใช้ไดรเวอร์ JDBC เพื่อเรียกโพรซีเดอร์ที่เก็บไว้พร้อมพารามิเตอร์ คุณต้องใช้ลำดับการหลีกการเรียก SQL ร่วมกับเมธอด waitCall ของคลาส SQLServerConnection ไวยากรณ์สำหรับลำดับการหลีกเลี่ยงการโทรด้วยพารามิเตอร์ IN เป็นดังนี้:
คัดลอกรหัสรหัสดังต่อไปนี้:
{ชื่อขั้นตอนการโทร[([พารามิเตอร์][,[พารามิเตอร์]]...)]}
เมื่อสร้างลำดับการหลีกเลี่ยงการโทร ให้ใช้อักขระ ? (เครื่องหมายคำถาม) เพื่อระบุพารามิเตอร์ IN อักขระนี้ทำหน้าที่เป็นตัวแทนสำหรับค่าพารามิเตอร์ที่จะส่งผ่านไปยังกระบวนงานที่เก็บไว้ คุณสามารถใช้หนึ่งในวิธี setter ของคลาส SQLServerPreparedStatement เพื่อระบุค่าสำหรับพารามิเตอร์ วิธีการตั้งค่าที่ใช้ได้จะขึ้นอยู่กับประเภทข้อมูลของพารามิเตอร์ IN
เมื่อส่งค่าไปยังวิธี setter คุณต้องระบุไม่เพียงแต่ค่าจริงที่จะใช้ในพารามิเตอร์เท่านั้น แต่ยังระบุตำแหน่งลำดับของพารามิเตอร์ภายในขั้นตอนการจัดเก็บด้วย ตัวอย่างเช่น ถ้ากระบวนงานที่เก็บไว้มีพารามิเตอร์ IN เดียว ค่าลำดับของมันคือ 1 หากกระบวนงานที่เก็บไว้มีพารามิเตอร์สองตัว ค่าลำดับแรกคือ 1 และค่าลำดับที่สองคือ 2
เป็นตัวอย่างวิธีการเรียกกระบวนงานเก็บไว้ที่ประกอบด้วยพารามิเตอร์ IN ใช้กระบวนงานที่เก็บไว้ใน uspGetEmployeeManagers ในฐานข้อมูลตัวอย่าง SQL Server 2005 AdventureWorks กระบวนงานที่เก็บไว้นี้ยอมรับพารามิเตอร์อินพุตตัวเดียวชื่อ EmployeeID ซึ่งเป็นค่าจำนวนเต็ม และส่งกลับรายการพนักงานและผู้จัดการแบบเรียกซ้ำตาม EmployeeID ที่ระบุ นี่คือโค้ด Java ที่เรียกขั้นตอนการจัดเก็บนี้:
คัดลอกรหัสรหัสดังต่อไปนี้:
โมฆะคงที่สาธารณะดำเนินการ SprocInParams (การเชื่อมต่อ) ... {
พยายาม...{
PreparationStatement pstmt = con.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
ในขณะที่ (rs.next()) ...{
System.out.println("พนักงาน:");
System.out.println(rs.getString("นามสกุล") + ", " + rs.getString("FirstName"));
System.out.println("ผู้จัดการ:");
System.out.println(rs.getString("ManagerLastName") + ", " + rs.getString("ManagerFirstName"));
System.out.println();
-
rs.ปิด();
pstmt.ปิด();
-
catch (ข้อยกเว้น e) ...{
e.printStackTrace();
-
-
3. ใช้กระบวนงานที่เก็บไว้พร้อมกับพารามิเตอร์เอาต์พุต
เมื่อเรียกโพรซีเดอร์ที่เก็บไว้ดังกล่าวโดยใช้ไดรเวอร์ JDBC คุณต้องใช้ลำดับการหลีกการเรียก SQL ร่วมกับเมธอด waitCall ของคลาส SQLServerConnection ไวยากรณ์สำหรับลำดับหลีกการโทรที่มีพารามิเตอร์ OUT เป็นดังนี้:
คัดลอกรหัสรหัสดังต่อไปนี้:
{ชื่อขั้นตอนการโทร[([พารามิเตอร์][,[พารามิเตอร์]]...)]}
เมื่อสร้างลำดับการหลีกเลี่ยงการโทร ให้ใช้อักขระ ? (เครื่องหมายคำถาม) เพื่อระบุพารามิเตอร์ OUT อักขระนี้ทำหน้าที่เป็นตัวแทนสำหรับค่าพารามิเตอร์ที่จะถูกส่งกลับจากกระบวนงานที่เก็บไว้นี้ หากต้องการระบุค่าสำหรับพารามิเตอร์ OUT คุณต้องใช้เมธอด registerOutParameter ของคลาส SQLServerCallableStatement เพื่อระบุประเภทข้อมูลของแต่ละพารามิเตอร์ก่อนที่จะรันกระบวนงานที่เก็บไว้
ค่าที่ระบุสำหรับพารามิเตอร์ OUT โดยใช้เมธอด registerOutParameter จะต้องเป็นหนึ่งในประเภทข้อมูล JDBC ที่มีอยู่ใน java.sql.Types ซึ่งในทางกลับกันจะถูกแม็พกับประเภทข้อมูล SQL Server ดั้งเดิมประเภทใดประเภทหนึ่ง สำหรับข้อมูลเพิ่มเติมเกี่ยวกับชนิดข้อมูล JDBC และ SQL Server โปรดดูที่ การทำความเข้าใจประเภทข้อมูลไดรเวอร์ JDBC
เมื่อคุณส่งค่าไปยังเมธอด registerOutParameter สำหรับพารามิเตอร์ OUT ไม่เพียงแต่คุณต้องระบุชนิดข้อมูลที่จะใช้สำหรับพารามิเตอร์เท่านั้น แต่คุณยังต้องระบุตำแหน่งลำดับของพารามิเตอร์หรือชื่อของพารามิเตอร์ในกระบวนงานที่เก็บไว้ด้วย ตัวอย่างเช่น ถ้ากระบวนงานที่เก็บไว้มีพารามิเตอร์ OUT เดียว ค่าลำดับของมันคือ 1 ถ้ากระบวนงานที่เก็บไว้มีพารามิเตอร์สองตัว ค่าลำดับแรกคือ 1 และค่าลำดับที่สองคือ 2
ตามตัวอย่าง สร้างขั้นตอนการจัดเก็บต่อไปนี้ในฐานข้อมูลตัวอย่าง SQL Server 2005 AdventureWorks: ขึ้นอยู่กับพารามิเตอร์จำนวนเต็ม IN ที่ระบุ (employeeID) ขั้นตอนที่เก็บไว้นี้ยังส่งกลับพารามิเตอร์ OUT จำนวนเต็มเดียว (managerID) ขึ้นอยู่กับ EmployeeID ที่มีอยู่ในตาราง HumanResources.Employee ค่าที่ส่งคืนในพารามิเตอร์ OUT คือ ManagerID
ในตัวอย่างต่อไปนี้ ฟังก์ชันนี้จะถูกส่งผ่านการเชื่อมต่อแบบเปิดไปยังฐานข้อมูลตัวอย่าง AdventureWorks จากนั้นกระบวนงานที่เก็บไว้ GetImmediateManager จะถูกเรียกใช้โดยใช้วิธีการดำเนินการ:
คัดลอกรหัสรหัสดังต่อไปนี้:
โมฆะคงที่สาธารณะExecutStoredProcedure (การเชื่อมต่อ) ... {
พยายาม...{
CallableStatement cstmt = con.prepareCall("{โทร dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("รหัสผู้จัดการ: " + cstmt.getInt(2));
-
catch (ข้อยกเว้น e) ...{
e.printStackTrace();
-
-
ตัวอย่างนี้ใช้ตำแหน่งลำดับเพื่อระบุพารามิเตอร์ อีกทางหนึ่ง พารามิเตอร์สามารถระบุได้ด้วยชื่อ แทนที่จะระบุตำแหน่งตามลำดับ ตัวอย่างโค้ดต่อไปนี้แก้ไขตัวอย่างก่อนหน้านี้เพื่อแสดงวิธีใช้พารามิเตอร์ที่มีชื่อในแอปพลิเคชัน Java โปรดทราบว่าชื่อพารามิเตอร์เหล่านี้สอดคล้องกับชื่อพารามิเตอร์ในคำจำกัดความของกระบวนงานที่เก็บไว้: 11x16CREATE PROCEDURE GetImmediateManager
คัดลอกรหัสรหัสดังต่อไปนี้:
@employeeID INT,
@managerID INT เอาท์พุต
เช่น
เริ่ม
เลือก @managerID = ID ผู้จัดการ
จากฝ่ายทรัพยากรบุคคลพนักงาน
โดยที่ EmployeeID = @employeeID
จบ
ขั้นตอนที่เก็บไว้อาจส่งคืนจำนวนการอัปเดตและชุดผลลัพธ์หลายชุด ไดรเวอร์ JDBC ของ Microsoft SQL Server 2005 เป็นไปตามข้อกำหนด JDBC 3.0 ซึ่งระบุว่าควรดึงชุดผลลัพธ์และจำนวนการอัปเดตหลายชุดก่อนที่จะดึงพารามิเตอร์ OUT นั่นคือ แอปพลิเคชันควรดึงออบเจ็กต์ ResultSet ทั้งหมดก่อนและอัปเดตการนับ จากนั้นใช้เมธอด CallableStatement.getter เพื่อดึงพารามิเตอร์ OUT มิฉะนั้น เมื่อเรียกพารามิเตอร์ OUT วัตถุ ResultSet และจำนวนการอัปเดตที่ยังไม่ได้ถูกดึงข้อมูลจะหายไป
4. ใช้ขั้นตอนการจัดเก็บที่มีสถานะการคืนสินค้า
เมื่อเรียกกระบวนงานที่เก็บไว้ดังกล่าวโดยใช้ไดรเวอร์ JDBC คุณต้องใช้ลำดับการหลีกการเรียก SQL ร่วมกับเมธอด waitCall ของคลาส SQLServerConnection ไวยากรณ์สำหรับลำดับหลีกการโทรที่ส่งคืนพารามิเตอร์สถานะจะเป็นดังนี้:
คัดลอกรหัสรหัสดังต่อไปนี้:
{[?=]ชื่อขั้นตอนการโทร[([พารามิเตอร์][,[พารามิเตอร์]]...)]}
เมื่อสร้างลำดับหลีกการโทร ให้ใช้อักขระ ? (เครื่องหมายคำถาม) เพื่อระบุพารามิเตอร์สถานะการส่งคืน อักขระนี้ทำหน้าที่เป็นตัวแทนสำหรับค่าพารามิเตอร์ที่จะถูกส่งกลับจากกระบวนงานที่เก็บไว้นี้ หากต้องการระบุค่าสำหรับพารามิเตอร์สถานะการส่งคืน คุณต้องระบุชนิดข้อมูลของพารามิเตอร์โดยใช้เมธอด registerOutParameter ของคลาส SQLServerCallableStatement ก่อนที่จะดำเนินการขั้นตอนที่เก็บไว้
นอกจากนี้ เมื่อส่งค่าพารามิเตอร์สถานะการส่งคืนไปยังเมธอด registerOutParameter คุณต้องระบุไม่เพียงแต่ประเภทข้อมูลของพารามิเตอร์ที่จะใช้ แต่ยังรวมถึงตำแหน่งลำดับของพารามิเตอร์ในขั้นตอนการจัดเก็บด้วย ตำแหน่งลำดับของพารามิเตอร์สถานะการส่งคืนจะเป็น 1 เสมอ เนื่องจากจะเป็นพารามิเตอร์แรกเสมอเมื่อมีการเรียกใช้กระบวนงานที่เก็บไว้ แม้ว่าคลาส SQLServerCallableStatement จะสนับสนุนการใช้ชื่อของพารามิเตอร์เพื่อระบุพารามิเตอร์เฉพาะ แต่คุณสามารถใช้เฉพาะหมายเลขตำแหน่งลำดับของพารามิเตอร์สำหรับพารามิเตอร์สถานะการส่งคืนเท่านั้น
เป็นตัวอย่าง สร้างขั้นตอนการจัดเก็บต่อไปนี้ในฐานข้อมูลตัวอย่าง SQL Server 2005 AdventureWorks:
คัดลอกรหัสรหัสดังต่อไปนี้:
สร้างขั้นตอน CheckContactCity
(@cityName ชาร์(50))
เช่น
เริ่ม
ถ้า ((เลือกนับ(*)
จากบุคคล.ที่อยู่
โดยที่เมือง = @cityName) > 1)
กลับ 1
อื่น
กลับ 0
จบ
ขั้นตอนที่เก็บไว้ส่งกลับค่าสถานะเป็น 1 หรือ 0 ขึ้นอยู่กับว่าเมืองที่ระบุโดยพารามิเตอร์ cityName สามารถพบได้ในตาราง Person.Address หรือไม่
ในตัวอย่างต่อไปนี้ ฟังก์ชันนี้จะถูกส่งผ่านการเชื่อมต่อแบบเปิดไปยังฐานข้อมูลตัวอย่าง AdventureWorks จากนั้นกระบวนการที่จัดเก็บของ CheckContactCity จะถูกเรียกใช้โดยใช้วิธีการดำเนินการ:
คัดลอกรหัสรหัสดังต่อไปนี้:
โมฆะคงที่สาธารณะExecutStoredProcedure (การเชื่อมต่อ) ... {
พยายาม...{
CallableStatement cstmt = con.prepareCall("{? = โทร dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "แอตแลนตา");
cstmt.execute();
System.out.println("สถานะการส่งคืน: " + cstmt.getInt(1));
-
cstmt.ปิด();
catch (ข้อยกเว้น e) ...{
e.printStackTrace();
-
-
5. ใช้ขั้นตอนการจัดเก็บที่มีการนับการอัพเดต
หลังจากที่คุณใช้คลาสSQLServerCallableStatementเพื่อสร้างการเรียกไปยังกระบวนงานที่เก็บไว้ คุณสามารถใช้วิธีดำเนินการหรือExecuteUpdateเพื่อเรียกกระบวนงานเก็บไว้ วิธีการดำเนินการ Update จะส่งกลับค่า int ซึ่งมีจำนวนแถวที่ได้รับผลกระทบจากกระบวนงานที่เก็บไว้นี้ แต่วิธีการดำเนินการจะไม่ส่งกลับค่านี้ ถ้าคุณใช้วิธีการดำเนินการและต้องการนับจำนวนแถวที่ได้รับผลกระทบ คุณสามารถเรียกวิธีการgetUpdateCountได้หลังจากรันกระบวนงานเก็บไว้แล้ว
เป็นตัวอย่าง สร้างตารางต่อไปนี้และขั้นตอนการจัดเก็บในฐานข้อมูลตัวอย่าง SQL Server 2005 AdventureWorks:
คัดลอกรหัสรหัสดังต่อไปนี้:
สร้างตาราง TestTable
(Col1 int ตัวตน
โคล2 วาร์ชาร์(50),
Col3 อินท์);
สร้างขั้นตอน UpdateTestTable
@Col2 วาร์ชาร์(50)
@Col3 อินท์
เช่น
เริ่ม
อัปเดตตารางทดสอบ
SET Col2 = @Col2, Col3 = @Col3
จบ;
ในตัวอย่างต่อไปนี้ ฟังก์ชันนี้จะถูกส่งผ่านการเชื่อมต่อแบบเปิดไปยังฐานข้อมูลตัวอย่าง AdventureWorks ใช้วิธีการดำเนินการเพื่อเรียกกระบวนงานที่เก็บไว้ UpdateTestTable จากนั้นใช้วิธี getUpdateCount เพื่อส่งคืนจำนวนแถวที่ได้รับผลกระทบจากกระบวนงานที่เก็บไว้
คัดลอกรหัสรหัสดังต่อไปนี้:
โมฆะคงที่สาธารณะดำเนินการUpdateStoredProcedure (การเชื่อมต่อ) ... {
พยายาม...{
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "A");
cstmt.setInt(2, 100);
cstmt.execute();
จำนวน int = cstmt.getUpdateCount();
cstmt.ปิด();
System.out.println("แถวที่ได้รับผลกระทบ: " + นับ);
-
catch (ข้อยกเว้น e) ...{
e.printStackTrace();