Apakah Prosedur Tersimpan?

SQL (Bahasa Pertanyaan Berstruktur) ialah bahasa standard untuk mengurus dan mengendalikan pangkalan data hubungan. Salah satu ciri yang berkuasa dan biasa digunakan ialah prosedur tersimpan. Prosedur tersimpan ialah satu set penyata SQL yang disusun dan disimpan dalam pangkalan data dan boleh menerima parameter input, melaksanakan operasi dan mengembalikan hasil. Mari terokai apakah prosedur tersimpan dan cara menciptanya.

Pengenalan kepada prosedur tersimpan

Prosedur tersimpan mungkin terdengar seperti istilah yang kompleks, tetapi ia adalah asas pengurusan pangkalan data yang cekap. Mari kita mulakan dengan definisinya.

Apakah prosedur tersimpan?

Prosedur tersimpan ialah satu siri pernyataan SQL yang dipratakrif dan disimpan pada pelayan pangkalan data. Apabila anda perlu melakukan operasi ini, anda boleh melaksanakannya dengan memanggil nama prosedur yang disimpan dan bukannya menghantar berbilang arahan pertanyaan berasingan.

Berikut ialah contoh ringkas yang menunjukkan cara membuat prosedur tersimpan mudah dalam SQL Server:

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Berikut ialah beberapa komponen utama prosedur tersimpan:

• Parameter input: Ini ialah nilai yang dihantar kepada prosedur tersimpan dari luar untuk menyesuaikan gelagat prosedur tersimpan. Parameter input membenarkan prosedur tersimpan melakukan tindakan berbeza berdasarkan keadaan berbeza.
• Parameter output: Sama seperti parameter input, parameter output juga merupakan sebahagian daripada prosedur yang disimpan, tetapi peranannya adalah untuk mengembalikan nilai kepada pemanggil dan bukannya menerima nilai.
• Pembolehubah setempat: Ini ialah pembolehubah yang diisytiharkan dalam prosedur tersimpan dan digunakan untuk menyimpan hasil perantaraan atau nilai yang dikira semasa pelaksanaan. Pembolehubah setempat hanya boleh dilihat dalam konteks prosedur tersimpan dan boleh ditetapkan beberapa kali sepanjang hayatnya.
• Pernyataan SQL: Ini membentuk logik teras prosedur tersimpan, termasuk tetapi tidak terhad kepada pertanyaan, memasukkan, mengemas kini dan memadam data.

Komponen ini bekerjasama untuk menjadikan prosedur tersimpan sebagai cara yang boleh digunakan semula dan cekap untuk melaksanakan operasi pangkalan data. Dengan merangkum tugas pangkalan data biasa dalam prosedur tersimpan, anda boleh memudahkan pembangunan aplikasi sambil meningkatkan prestasi dan keselamatan.

Cara prosedur tersimpan berfungsi

Prosedur tersimpan dilaksanakan di dalam pelayan pangkalan data, yang bermaksud ia boleh menyelesaikan operasi dengan lebih cekap dan melaksanakan lebih cepat berbanding jika berbilang pertanyaan dihantar satu demi satu daripada pelanggan. Selain itu, menggunakan prosedur tersimpan boleh mengurangkan trafik rangkaian dengan ketara kerana hanya set hasil akhir perlu dipindahkan daripada pelayan kepada klien, dan bukannya hasil setiap pertanyaan individu berulang-alik. Dengan cara ini, ia bukan sahaja meningkatkan kelajuan pemprosesan data, tetapi juga mengurangkan penggunaan lebar jalur rangkaian.

Peranan dalam Pengurusan Pangkalan Data

Prosedur tersimpan memainkan peranan penting dalam pengurusan pangkalan data kerana ia menyimpan logik perniagaan secara berpusat pada pelayan pangkalan data. Melakukannya memastikan bahawa operasi kritikal sentiasa dilakukan dengan cara yang konsisten, selamat dan cekap. Khususnya, prosedur tersimpan membantu:

• Mengekalkan integriti data: Dengan memastikan semua operasi data mengikut peraturan dan kekangan yang telah ditetapkan, prosedur tersimpan membantu mengekalkan integriti dan konsistensi data.
• Menguatkuasakan logik perniagaan: Merangkumkan peraturan perniagaan yang kompleks dalam prosedur tersimpan memastikan peraturan ini dikuatkuasakan dengan ketat dan tidak akan terjejas oleh perubahan dalam kod pelanggan.
• Memudahkan interaksi pangkalan data: Dengan menyediakan antara muka yang merangkumi operasi yang kompleks, prosedur tersimpan mengurangkan kerumitan interaksi pangkalan data aplikasi, menjadikan pembangunan dan penyelenggaraan lebih mudah.

Faedah menggunakan prosedur tersimpan

Terdapat beberapa kelebihan utama untuk menggunakan prosedur tersimpan:

1. Prestasi yang dipertingkatkan:

• Prosedur tersimpan yang telah dikompilasi dilaksanakan dengan lebih pantas.
• Kelajuan tindak balas yang lebih baik dan penggunaan sumber pelayan yang lebih cekap.

1. Kebolehgunaan semula dan kebolehselenggaraan:

• Prosedur tersimpan boleh dipanggil beberapa kali untuk mengurangkan pertindihan kod.
• Kemas kini pada prosedur tersimpan akan berkuat kuasa di semua tempat di mana ia digunakan, memastikan ketekalan dan mengurangkan ralat.

1. Keselamatan data:

• Kawal akses pangkalan data dan hadkan keupayaan untuk mengendalikan jadual secara terus.
• Sediakan lapisan keselamatan melalui prosedur tersimpan untuk menghalang akses tanpa kebenaran dan serangan berniat jahat.

Perintah biasa digunakan dengan prosedur tersimpan

Sekarang mari kita lihat arahan berguna yang berpasangan dengan prosedur tersimpan.

BUAT PROSEDUR

Seperti yang dinyatakan sebelum ini, arahan ini digunakan untuk menentukan prosedur tersimpan baharu dalam pangkalan data. Berikut ialah contoh prosedur tersimpan menggunakan fungsi ini:

Andaikan kita mempunyai jadual yang dipanggil "Pekerja" dengan lajur berikut:

• ID Pekerja
• Nama Pertama
• Nama Akhir
• ID Jabatan
• Gaji

Kami ingin membuat prosedur tersimpan untuk mendapatkan semula semua pekerja yang dimiliki oleh jabatan tertentu.

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

EXEC

Arahan ini digunakan untuk melaksanakan prosedur tersimpan. Ia juga boleh digunakan untuk menghantar parameter input dan output. Untuk contoh kami yang terdahulu, arahan "EXEC" akan kelihatan seperti ini:

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

UBAH PROSEDUR

Arahan ini membolehkan anda mengubah suai prosedur tersimpan sedia ada tanpa menjatuhkan dan menciptanya semula. Meneruskan contoh sebelumnya, jika kami ingin mengubah suai prosedur tersimpan bernama "GetEmployeesByDepartment" untuk menambah penapis gaji tambahan, iaitu, kami ingin mendapatkan maklumat tentang pekerja di jabatan tertentu yang gajinya lebih besar daripada jumlah tertentu.

Berikut ialah contoh:

EXEC GetEmployeesByDepartment @DepartmentID = 1;

PROSEDUR DROP

Jika prosedur tersimpan tidak lagi diperlukan, anda boleh mengalih keluarnya daripada pangkalan data menggunakan arahan DROP PROCEDURE.

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

Cara Membuat dan Menggunakan Prosedur Tersimpan

Kami akan melihat untuk mencipta dan menggunakan prosedur tersimpan dalam tiga bidang:

• MySQL
• SQL Server
• Oracle

MySQL

Membuat prosedur tersimpan dalam MySQL agak mudah. Anda mentakrifkan prosedur, menentukan parameter dan menulis kod SQL menggunakan pernyataan "CREATE PROCEDURE".

Anda boleh melakukan ini:

Langkah 1: Buat Jadual Pekerja

Mula-mula, mari buat contoh jadual pekerja untuk diisi dengan data yang akan kami gunakan.

DROP PROCEDURE GetEmployeesByDepartment

Langkah 2: Masukkan data sampel

Masukkan beberapa data sampel ke dalam jadual Pekerja.

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

Langkah 3: Buat prosedur tersimpan

Mari kita buat prosedur tersimpan untuk mendapatkan pekerja berdasarkan jabatan mereka.

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

Langkah 4: Panggil prosedur tersimpan

Untuk memanggil prosedur tersimpan dan mendapatkan semula pekerja jabatan tertentu, gunakan pernyataan CALL.

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

Pelayan SQL

Dalam SQL Server, penciptaan dan pelaksanaan prosedur tersimpan sedikit berbeza, tetapi tidak berubah secara drastik. Berikut ialah contoh:

Langkah 1: Buat Jadual Pekerja

Mula-mula, mari kita buat contoh jadual Pekerja.

CALL GetEmployeesByDepartment(1);

Langkah 2: Masukkan Data Sampel

Seterusnya, kami akan memasukkan beberapa data sampel ke dalam jadual Pekerja.

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Langkah 3: Buat prosedur tersimpan

Mari kita buat prosedur tersimpan untuk mendapatkan pekerja berdasarkan jabatan mereka.

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

Langkah 4: Laksanakan prosedur tersimpan

Untuk melaksanakan prosedur tersimpan dan mendapatkan semula pekerja jabatan tertentu, gunakan penyata EXEC.

EXEC GetEmployeesByDepartment @DepartmentID = 1;

Oracle

Oracle juga menyokong prosedur tersimpan. Berikut ialah panduan langkah demi langkah tentang cara melaksanakannya dalam Oracle menggunakan SQL.

Langkah 1: Buat Jadual Pekerja

Mula-mula, mari kita buat contoh jadual Pekerja.

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

Langkah 2: Masukkan Data Sampel

Seterusnya, kami memasukkan beberapa sampel data ke dalam jadual pekerja untuk membuat set data.

DROP PROCEDURE GetEmployeesByDepartment

Langkah 3: Buat prosedur tersimpan

Mari kita buat prosedur tersimpan untuk mendapatkan pekerja berdasarkan jabatan mereka.

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

Mereka bentuk prosedur tersimpan: amalan terbaik

Selepas melengkapkan pengenalan secara langsung ini, mari lihat beberapa amalan terbaik untuk mereka bentuk prosedur tersimpan.

Menggunakan pertanyaan berparameter

Pertanyaan berparameter dalam prosedur tersimpan membantu mencegah serangan suntikan SQL. Sentiasa gunakan parameter dan bukannya menggabungkan input pengguna terus ke dalam pernyataan SQL.

Sebagai contoh, jangan gunakan ini:

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

Gunakan ini：

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

Hadkan akses kepada jadual asas

Seperti yang dinyatakan sebelum ini, prosedur tersimpan boleh bertindak sebagai lapisan keselamatan dengan mengehadkan akses terus kepada jadual asas. Ini mengurangkan risiko data sensitif terdedah.

Optimumkan kod SQL

Untuk memastikan prosedur yang disimpan berjalan dengan cekap, prosedur tersebut harus dioptimumkan untuk prestasi. Ini bermakna mengurangkan pengiraan yang tidak perlu dan menggunakan indeks dengan baik. Anda boleh meningkatkan kecekapan pertanyaan dengan menganalisis pelan pelaksanaan pertanyaan untuk mengenal pasti dan menyelesaikan kesesakan prestasi.

Sebagai contoh, anda harus mengelak daripada menggunakan "PILIH *" untuk mendapatkan semula semua medan dalam jadual kerana ini meningkatkan jumlah data yang dipindahkan dan mengurangkan kecekapan. Sebaliknya, anda harus memilih hanya medan yang anda perlukan untuk mengecilkan skop pengambilan data untuk meningkatkan prestasi.

Dokumentasikan prosedur tersimpan anda

Kod pendokumentasian juga digunakan pada penulisan prosedur tersimpan. Ini penting untuk pembangun lain memahami peranan dan fungsi setiap prosedur. Ia juga menggalakkan konvensyen penamaan dan gaya pengekodan yang konsisten.

Proses ini boleh dicapai dengan menambahkan ulasan pada prosedur tersimpan atau mengekalkan dokumentasi yang berasingan. Contohnya:

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Mengekalkan kawalan versi

Kawalan versi adalah penting untuk mengurus dan menjejaki perubahan pada prosedur yang disimpan. Adalah berguna untuk mengekalkan repositori yang mengandungi sejarah lengkap perubahan pada skrip prosedur tersimpan dan dokumentasinya. Ini bukan sahaja memudahkan untuk menjejaki semua pengubahsuaian, tetapi juga memastikan konsistensi merentas persekitaran penggunaan yang berbeza.

Fikiran akhir

Prosedur tersimpan ialah cara pengurusan pangkalan data yang cekap dan selamat. Mereka menawarkan beberapa faedah yang, apabila digunakan dengan amalan terbaik yang betul, boleh meningkatkan kecekapan dan keberkesanan analisis data dengan ketara dalam organisasi.

---

Komuniti

Pergi ke tapak web Chat2DB
? Sertai Komuniti Chat2DB
? Ikuti kami di X
? Cari kami di Discord

Atas ialah kandungan terperinci Apakah Prosedur Tersimpan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!