Prosedur Tersimpan SQL: Mencipta dan Menggunakan

Prosedur disimpan SQL adalah modul program yang boleh dilaksanakan yang boleh disimpan dalam pangkalan data sebagai pelbagai objek. Dalam erti kata lain, ia adalah objek yang mengandungi pernyataan SQL. Prosedur tersimpan ini boleh dilaksanakan dalam klien aplikasi untuk mendapatkan prestasi yang baik. Di samping itu, objek tersebut sering dipanggil dari senario lain atau bahkan dari beberapa bahagian lain.

Pengenalan

Ramai yang percaya bahawa mereka adalah serupa dengan prosedur pelbagai bahasa pengaturcaraan peringkat tinggi (masing-masing, kecuali untuk MS SQL). Mungkin, ia benar-benar begitu. Mereka mempunyai parameter yang sama, mereka boleh memberikan nilai yang sama. Lebih-lebih lagi, dalam beberapa kes mereka bersentuhan. Sebagai contoh, mereka digabungkan dengan pangkalan data DDL dan DML, serta dengan fungsi pengguna (nama kod - UDF).

Sebenarnya, prosedur yang disimpan SQL mempunyai pelbagai kelebihan yang membezakannya daripada proses yang serupa. Keselamatan, kebolehubahan pengaturcaraan, produktiviti - semua ini menarik pengguna yang bekerja dengan pangkalan data, semakin banyak. Puncak populariti prosedur berlaku pada tahun 2005-2010, ketika Microsoft mengeluarkan program yang disebut SQL Server Management Studio. Dengan bantuannya, bekerja dengan pangkalan data telah menjadi lebih mudah, lebih praktikal dan lebih mudah. Dari tahun ke tahun kaedah pemindahan maklumat ini menjadi popular di kalangan pengaturcara. Hari ini, MS SQL Server adalah program yang sangat biasa, yang bagi pengguna yang "berkomunikasi" dengan pangkalan data, telah meningkat bersama dengan "Excel".

Apabila prosedur dipanggil, ia diproses dengan segera oleh pelayan itu sendiri, tanpa proses yang tidak perlu dan intervensi pengguna. Selepas ini, anda boleh melakukan sebarang tindakan dengan maklumat: pemadaman, pelaksanaan, perubahan. Untuk semua ini adalah tanggungjawab pengendali DDL, yang semata-mata melakukan pemprosesan objek yang paling kompleks. Dan semua ini berlaku dengan sangat cepat, dan pelayan tidak dimuatkan. Kelajuan dan prestasi ini membolehkan anda dengan cepat memindahkan sejumlah besar maklumat dari pengguna ke pelayan dan sebaliknya.

Untuk melaksanakan kerja teknologi ini dengan maklumat, terdapat beberapa bahasa pengaturcaraan. Ini termasuk, sebagai contoh, PL / SQL dari sistem pengurusan pangkalan data Oracle, PSQL dalam sistem InterBase dan Firebird, serta "Microsoft Transact-SQL" klasik. Kesemua mereka direka untuk membuat dan melaksanakan prosedur tersimpan, yang membolehkan menggunakan algoritma besar dalam pengendali pangkalan data yang besar. Ia juga perlu bagi mereka yang menguruskan maklumat tersebut untuk melindungi semua objek daripada akses yang tidak dibenarkan oleh pihak ketiga dan, dengan sewajarnya, membuat, menukar atau memadam data tertentu.

Produktiviti

Objek pangkalan data ini boleh diprogramkan dalam pelbagai cara. Ini membolehkan pengguna memilih jenis kaedah yang digunakan, yang paling sesuai, yang menjimatkan masa dan usaha. Di samping itu, prosedur itu sendiri diproses, yang menghindari masa yang banyak dihabiskan untuk bertukar antara pelayan dan pengguna. Juga, modul ini boleh diprogram semula dan diubah mengikut arah yang dikehendaki pada bila-bila masa. Terutamanya adalah diperhatikan kelajuan yang mana prosedur tersimpan SQL bermula: proses ini lebih cepat daripada yang lain, sama seperti itu, yang menjadikannya mudah dan serba boleh.

Keselamatan

Pemprosesan maklumat jenis ini berbeza daripada proses yang sama kerana ia menjamin keselamatan yang semakin meningkat. Ini dipastikan oleh hakikat bahawa akses pengguna lain ke prosedur boleh dikecualikan sepenuhnya dan sepenuhnya. Ini akan membolehkan pentadbir menjalankan operasi dengan mereka secara bebas, tanpa takut memintas maklumat atau akses yang tidak dibenarkan ke pangkalan data.

Pemindahan data

Hubungan antara prosedur tersimpan SQL dan aplikasi klien ialah penggunaan parameter dan nilai pulangan. Yang terakhir tidak perlu memindahkan data ke prosedur tersimpan, namun maklumat ini (terutama pada permintaan pengguna) dan diproses untuk SQL. Selepas prosedur yang tersimpan telah menyelesaikan kerja, ia menghantar kembali paket data (tetapi, sekali lagi, jika dikehendaki) ke aplikasi yang memanggilnya, menggunakan pelbagai kaedah yang boleh digunakan sebagai panggilan ke prosedur tersimpan SQL, dan kembali, contohnya:

- pemindahan data menggunakan parameter Output;

- pemindahan data menggunakan operator balik;

- pemindahan data menggunakan pengendali pemilihan.

Sekarang mari kita lihat bagaimana proses ini kelihatan dari dalam.

1. Buat prosedur tersimpan EXEC di SQL

Anda boleh membuat prosedur di MS SQL (Managment Studio). Selepas prosedur dibuat, ia akan dipindahkan ke nod pangkalan data yang boleh diprogramkan di mana prosedur penciptaan dilakukan oleh pengendali. Untuk melaksanakan, prosedur tersimpan SQL menggunakan proses EXEC yang mengandungi nama objek itu sendiri.

Apabila membuat prosedur, namanya muncul dahulu, selepas mana satu atau lebih parameter yang diberikan kepadanya dihasilkan. Parameter boleh menjadi pilihan. Selepas parameter, iaitu, prosedur prosedur, ditulis, anda perlu melakukan beberapa operasi yang diperlukan.

Masalahnya adalah bahawa tubuh boleh mempunyai pembolehubah tempatan yang terletak di dalamnya, dan pembolehubah ini juga berkaitan dengan prosedur. Dalam erti kata lain, mereka hanya boleh dilihat di dalam tubuh prosedur Microsoft SQL Server. Prosedur yang disimpan kemudian dianggap sebagai tempatan.

Jadi, untuk membuat prosedur, kami memerlukan nama prosedur dan sekurang-kurangnya satu parameter sebagai badan tatacara. Perhatikan bahawa pilihan yang sangat baik dalam kes ini adalah untuk membuat dan melaksanakan prosedur dengan nama skema dalam pengelas.

Badan prosedur boleh mempunyai apa-apa jenis pernyataan SQL, contohnya, seperti membuat jadual, memasukkan satu atau lebih baris baris, menetapkan jenis dan sifat pangkalan data, dan sebagainya. Walau bagaimanapun, badan prosedur mengehadkan pelaksanaan operasi tertentu di dalamnya. Beberapa batasan penting disenaraikan di bawah:

- badan tidak boleh membuat sebarang prosedur lain yang disimpan;

- badan tidak boleh mencipta idea palsu objek;

- Tubuh tidak boleh mencetuskan sebarang pencetus.

2. Menetapkan pemboleh ubah dalam tubuh prosedur

Anda boleh membuat pembolehubah setempat kepada prosedur badan, dan kemudian mereka akan ditempatkan secara eksklusif di dalam tubuh prosedur. Amalan yang baik adalah untuk membuat pembolehubah pada permulaan badan prosedur yang disimpan. Tetapi anda juga boleh menetapkan pembolehubah di mana-mana di dalam badan objek ini.

Kadang-kadang anda akan melihat bahawa beberapa pembolehubah ditetapkan dalam satu baris, dan setiap parameter ubah dipisahkan oleh koma. Juga ambil perhatian bahawa pembolehubah mempunyai awalan @. Di dalam tubuh prosedur, anda boleh menetapkan pembolehubah di mana anda mahu. Sebagai contoh, pembolehubah @ NAME1 boleh diisytiharkan lebih dekat pada akhir badan tatacara. Untuk menetapkan nilai pemboleh ubah yang diisytiharkan, satu set data peribadi digunakan. Berbeza dengan keadaan ketika lebih dari satu pemboleh ubah dinyatakan dalam satu baris, hanya satu set data pribadi yang digunakan dalam situasi ini.

Selalunya, pengguna bertanya soalan: "Bagaimana untuk menetapkan beberapa nilai dalam satu operator dalam badan prosedur?" Nah. Persoalannya menarik, tetapi ia lebih mudah dilakukan daripada yang anda fikirkan. Jawab: menggunakan pasangan seperti "Pilih Var = nilai". Anda boleh menggunakan pasangan ini, memisahkannya dengan koma.

3. Membuat Prosedur Disimpan SQL

Dalam pelbagai contoh, orang menunjukkan cara membuat prosedur simpan ringkas dan melaksanakannya. Walau bagaimanapun, prosedur tersebut boleh mengambil parameter sedemikian bahawa proses panggilan akan mempunyai nilai-nilai yang dekat dengannya (tetapi tidak semestinya). Jika mereka bertepatan, maka proses yang sama bermula di dalam badan. Contohnya, jika anda membuat prosedur yang akan mengambil bandar dan wilayah dari pemanggil dan kembali data tentang berapa banyak pengarang yang berkaitan dengan bandar dan wilayah yang berkaitan. Prosedur ini akan menanyakan jadual pengarang pangkalan data, misalnya, Pub, untuk mengira pengarang ini. Untuk mendapatkan pangkalan data ini, sebagai contoh, Google memuat skrip SQL dari halaman SQL2005.

Dalam contoh terdahulu, prosedur ini mengambil dua parameter, yang dalam Bahasa Inggeris akan dipanggil secara konvensional @State dan @City. Jenis data sepadan dengan jenis yang ditentukan dalam aplikasi. Dokumen prosedur mempunyai pembolehubah dalaman @ TotalAuthors (semua penulis), dan pembolehubah ini digunakan untuk memaparkan nombor mereka. Kemudian terdapat bahagian untuk memilih pertanyaan, yang semuanya penting. Akhirnya, nilai yang dihitung dipaparkan dalam tetingkap output menggunakan operator cetak.

Bagaimana untuk melaksanakan prosedur tersimpan dalam SQL

Terdapat dua cara untuk melaksanakan prosedur ini. Laluan pertama menunjukkan parameter lulus, sebagai senarai dipisahkan koma dilaksanakan selepas nama prosedur. Katakan kita mempunyai dua nilai (seperti dalam contoh terdahulu). Nilai-nilai ini dikumpulkan menggunakan pembolehubah parameter prosedur @State dan @City. Perintah adalah penting dalam cara ini lulus parameter. Kaedah ini dipanggil pemindahan hujah ordinal. Dalam kaedah kedua, parameter telah diberikan secara langsung, dan dalam kes ini perintah itu tidak penting. Kaedah kedua ini dikenali sebagai pemindahan hujah-hujah yang dinamakan.

Prosedur ini boleh menyimpang dari tipikal. Semuanya adalah sama seperti dalam contoh terdahulu, tetapi hanya di sini parameter dipindahkan. Iaitu, parameter @City disimpan dahulu, dan @State disimpan bersebelahan dengan nilai lalai. Tetapan lalai biasanya berasingan. Prosedur SQL disimpan sebagai parameter mudah. Dalam kes ini, di bawah keadaan, parameter "UT" menggantikan nilai lalai "CA". Dalam pelaksanaan kedua, hanya satu nilai argumen yang diluluskan untuk parameter @City, dan parameter @State mengambil nilai lalai "CA". Pengaturcara yang berpengalaman menasihatkan bahawa semua pemboleh ubah secara lalai terletak lebih dekat pada akhir senarai parameter. Jika tidak, pelaksanaan tidak mungkin, dan kemudian anda mesti bekerja dengan pemindahan argumen yang dinamakan, yang lebih lama dan lebih sukar.

4. Prosedur Tersimpan SQL Server: Kaedah Pulangan

Terdapat tiga cara penting untuk menghantar data ke prosedur tersimpan yang dipanggil. Mereka disenaraikan di bawah:

- mengembalikan nilai prosedur tersimpan;

- output parameter prosedur tersimpan;

- Pilih salah satu daripada prosedur yang disimpan.

4.1 Mengembalikan nilai prosedur tersimpan SQL

Dalam teknik ini, prosedur memberi nilai kepada pembolehubah setempat dan mengembalikannya. Prosedur ini juga boleh terus mengembalikan nilai malar. Dalam contoh berikut, kami mencipta prosedur yang mengembalikan jumlah pengarang. Jika anda membandingkan prosedur ini dengan yang sebelumnya, anda dapat melihat bahawa nilai untuk percetakan digantikan oleh yang bertentangan.

Sekarang mari kita lihat cara untuk melaksanakan prosedur dan keluarkan nilai yang dikembalikan kepadanya. Pelaksanaan prosedur memerlukan menetapkan pemboleh ubah dan percetakan, yang dilakukan setelah semua proses ini. Ambil perhatian bahawa bukan pernyataan cetakan, anda boleh menggunakan operator Pilih, sebagai contoh, Pilih @RetValue, dan juga OutputValue.

4.2 Keluar parameter prosedur tersimpan SQL

Nilai tindak balas boleh digunakan untuk mengembalikan satu pembolehubah, yang kita lihat dalam contoh terdahulu. Menggunakan parameter Output membolehkan prosedur untuk menghantar satu atau lebih nilai ubah kepada pihak panggilan. Parameter output ditetapkan sebagai kali ini oleh kata kunci "Output" ketika membuat prosedur. Jika parameter ditentukan sebagai parameter keluaran, maka objek prosedur mesti memberi nilai kepadanya. Prosedur SQL yang disimpan, contohnya boleh dilihat di bawah, kemudian dikembalikan dengan maklumat ringkasan.

Dalam contoh kami, terdapat dua nama output: @TotalAuthors dan @TotalNoContract. Mereka dinyatakan dalam senarai parameter. Pembolehubah ini menetapkan nilai-nilai di dalam tubuh prosedur. Apabila kita menggunakan parameter output, pemanggil dapat melihat nilai yang ditetapkan di dalam badan prosedur.

Di samping itu, dalam senario terdahulu, dua pembolehubah diisytiharkan untuk melihat nilai-nilai yang menetapkan prosedur tersimpan MS SQL Server dalam parameter keluaran. Kemudian prosedur itu dilakukan dengan membekalkan nilai normal parameter "CA". Parameter berikut adalah output dan, oleh itu, pemboleh ubah yang diisytiharkan dihantar dalam perintah yang ditetapkan. Ambil perhatian bahawa apabila mengalihkan pemboleh ubah, kata kunci output juga dinyatakan di sini. Selepas prosedur selesai berjaya, nilai yang dikembalikan oleh parameter output dipaparkan pada tetingkap mesej.

4.3 Pilih satu daripada prosedur yang disimpan SQL

Teknik ini digunakan untuk mengembalikan satu set nilai sebagai satu jadual data (RecordSet) ke prosedur tersimpan panggilan. Dalam contoh ini, prosedur tersimpan SQL dengan parameter @AuthID memaparkan jadual Pengarang dengan menapis rekod yang dikembalikan menggunakan parameter @AuthId ini. Operator Select memutuskan apa yang perlu dikembalikan kepada prosedur yang disimpan panggilan. Apabila melaksanakan prosedur tersimpan, AuthId diluluskan. Prosedur sedemikian di sini selalu mengembalikan hanya satu rekod atau tidak sama sekali. Tetapi prosedur tersimpan tidak mempunyai sekatan ke atas penyertaan lebih daripada satu rekod. Selalunya mungkin untuk mencari contoh di mana pemulangan data menggunakan parameter yang dipilih dengan penyertaan pembolehubah yang dikira berlaku dengan menyediakan beberapa nilai ringkasan.

Kesimpulannya

Prosedur tersimpan adalah modul perisian yang agak serius yang mengembalikan atau menghantar, dan juga menetapkan pembolehubah yang diperlukan melalui aplikasi klien. Kerana prosedur yang disimpan dijalankan pada pelayan itu sendiri, pertukaran data dalam jumlah besar antara pelayan dan aplikasi klien (untuk beberapa pengiraan) dapat dielakkan. Ini membolehkan anda mengurangkan beban pada pelayan SQL, yang, tentu saja, menyatu dengan pemegangnya. Salah satu subspesies adalah prosedur tersimpan T SQL, namun, mereka perlu dipelajari oleh mereka yang membuat pangkalan data yang mengagumkan. Terdapat juga sejumlah besar nuansa yang boleh berguna dalam mengkaji prosedur tersimpan, tetapi ini lebih untuk mereka yang merancang untuk terlibat dalam pengaturcaraan, termasuk profesional.