Contoh cara mencipta dan melaksanakan prosedur tersimpan dalam Oracle

Oracle ialah sistem pengurusan pangkalan data yang sangat berkuasa dengan banyak fungsi dan ciri lanjutan, yang mana prosedur tersimpan adalah salah satu daripadanya. Prosedur tersimpan ialah satu set pernyataan SQL yang dipratentukan untuk operasi pangkalan data yang boleh disimpan dalam pangkalan data untuk kegunaan panggilan kemudian.

Dalam Oracle, prosedur tersimpan ditulis dalam PL/SQL, bahasa yang menggabungkan SQL dan pengaturcaraan. PL/SQL mempunyai keupayaan manipulasi data yang kuat dan keupayaan kawalan proses, dan boleh menulis prosedur tersimpan yang cekap dengan mudah.

Faedah prosedur tersimpan

Faedah utama prosedur tersimpan ialah ia boleh meningkatkan kecekapan pelaksanaan pangkalan data dan mengurangkan overhed komunikasi rangkaian. Oleh kerana prosedur tersimpan telah dikompilasi dan dioptimumkan terlebih dahulu, tidak perlu menghuraikan dan mengoptimumkannya berulang kali semasa pelaksanaan, dan boleh dipanggil terus untuk pelaksanaan. Selain itu, prosedur tersimpan juga boleh melaksanakan operasi dinamik melalui parameter, yang bukan sahaja memudahkan kod tetapi juga mengelakkan risiko seperti suntikan SQL.

Penciptaan dan pelaksanaan prosedur tersimpan

Berikut menerangkan cara mencipta dan melaksanakan prosedur tersimpan dalam Oracle.

Buat prosedur tersimpan

Dalam Oracle, anda perlu menggunakan pernyataan CREATE PROCEDURE untuk mencipta prosedur tersimpan adalah seperti berikut:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
[IS | AS]
BEGIN
      pl/sql_code_block;
END [procedure_name];

Di mana:

• BUAT PROSEDUR: Pernyataan untuk mencipta prosedur tersimpan.
• ATAU GANTI: Parameter pilihan Jika parameter ini ditentukan, ini bermakna prosedur tersimpan yang dibuat akan diganti jika ia sudah wujud.
• procedure_name: Nama prosedur yang disimpan.
• parameter_name: Parameter input dan/atau output pilihan yang digunakan untuk menentukan input dan output prosedur yang disimpan.
• parameter_type: Jenis parameter, yang boleh menjadi jenis data seperti VARCHAR2, NUMBER atau jenis kursor, seperti SYS_REFCURSOR.
• IS |. AS: Parameter pilihan, digunakan untuk menentukan jenis bahasa prosedur yang disimpan, IS mewakili permulaan (blok PL/SQL), AS mewakili penghujung (blok PL/SQL).
• pl/sql_code_block: Blok kod PL/SQL, yang mengandungi pelaksanaan logik khusus prosedur tersimpan.

Kod contoh berikut menunjukkan cara mencipta prosedur tersimpan mudah yang menerima dua parameter dan menghasilkan jumlahnya:

CREATE OR REPLACE PROCEDURE add_nums(
    num1 IN NUMBER,
    num2 IN NUMBER,
    sum OUT NUMBER
)
IS
BEGIN
    sum := num1 + num2;
END add_nums;

Laksanakan prosedur tersimpan

Dalam Oracle, pernyataan EXECUTE atau EXECUTE IMMEDIATE diperlukan untuk melaksanakan prosedur tersimpan. Sebagai contoh, untuk melaksanakan program contoh di atas, anda boleh menggunakan pernyataan berikut:

DECLARE
    result NUMBER;
BEGIN
    add_nums(10, 20, result);
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || result);
END;

Di sini kami menggunakan pernyataan DECLARE untuk mengisytiharkan hasil pembolehubah yang perlu digunakan, dan memanggil prosedur tersimpan add_nums dan keluarkan hasilnya ke skrin.

Jenis parameter

Dalam prosedur tersimpan, parameter boleh menjadi parameter input, parameter output atau parameter dwiarah.

• Parameter input: Tentukan input prosedur yang disimpan.
• Parameter output: Tentukan output prosedur yang disimpan.
• Parameter dua arah: boleh menjadi input atau output.

Kaedah mengisytiharkan jenis parameter adalah seperti berikut:

(param_name [IN | OUT | IN OUT] param_type [, ...])

Dalam pengisytiharan ini, [MASUK | KELUAR] ialah parameter pilihan, digunakan untuk menentukan jenis parameter. Jika jenis parameter tidak ditentukan, ia lalai kepada jenis IN, iaitu parameter input.

Kod sampel:

CREATE OR REPLACE PROCEDURE my_proc (
    num IN NUMBER,
    str IN OUT VARCHAR2,
    cur OUT SYS_REFCURSOR
)
IS
BEGIN
    -- 逻辑实现
END my_proc;

Dalam kod di atas, kami mengisytiharkan prosedur tersimpan my_proc yang mengandungi tiga parameter Nombor parameter pertama ialah parameter input, dan parameter str kedua ialah dua parameter -way, dan parameter ketiga cur ialah parameter output.

Pemprosesan set rekod

Apabila menggunakan prosedur tersimpan untuk mengendalikan data, selalunya perlu mengembalikan senarai hasil pertanyaan. Oracle menyediakan dua jenis set rekod: kursor dan jadual PL/SQL.

Kursor

Kursor ialah struktur data yang mengembalikan set hasil, yang boleh merentasi hasil pertanyaan. Kursor boleh menjadi eksplisit atau tersirat Kursor eksplisit memerlukan pengisytiharan pembolehubah kursor dan membuka dan menutupnya dalam kod secara automatik dibuat dan diuruskan oleh Oracle.

Berikut ialah prosedur tersimpan yang menunjukkan cara menggunakan kursor:

CREATE OR REPLACE PROCEDURE get_employee(
    id_list IN VARCHAR2,
    emp_cur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN emp_cur FOR 'SELECT * FROM employees WHERE id IN (' || id_list || ')';
END get_employee;

Dalam contoh ini, kami mengisytiharkan prosedur tersimpan get_employee dengan dua parameter, yang menerima senarai A yang dipisahkan koma ID pekerja digunakan sebagai parameter input dan kursor emp_cur yang mengandungi maklumat pekerja yang dipilih dikembalikan.

Jadual PL/SQL

Jadual PL/SQL ialah struktur data seperti tatasusunan yang boleh menyimpan set nilai. Jadual PL/SQL mempunyai banyak aplikasi praktikal dalam prosedur tersimpan, seperti menghantar satu set data kepada prosedur tersimpan, dsb.

Dalam Oracle, jadual PL/SQL boleh diisytiharkan dan digunakan dalam prosedur tersimpan, seperti kod berikut:

CREATE OR REPLACE PACKAGE my_package
IS
    TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER);
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package
IS
    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER)
    IS
        total NUMBER := 0;
    BEGIN
        FOR indx IN 1 .. nums.COUNT LOOP
            total := total + nums(indx);
        END LOOP;
        sum := total;
    END sum_nums;
END my_package;

Di sini, kami telah mencipta pakej bernama my_package, di mana A PL /SQL jenis jadual bernama num_list dan prosedur tersimpan sum_nums yang menggunakan jenis ini diisytiharkan. sum_nums menerima hujah jenis num_list dan mengira jumlahnya.

Kesimpulan

Dalam Oracle, prosedur tersimpan adalah salah satu alat penting untuk mengekalkan pangkalan data Mereka mempunyai keupayaan dan dinamik pelaksanaan yang cekap. Kami juga boleh menggunakan prosedur tersimpan untuk membiarkannya melaksanakan beberapa logik perniagaan dan bukannya hanya melaksanakan satu pernyataan SQL, yang boleh meningkatkan kebolehgunaan semula dan kebolehselenggaraan. Kerana ia boleh disimpan dalam pangkalan data dan dikongsi serta diakses oleh pelbagai aplikasi atau proses. Terdapat banyak faedah menggunakan prosedur tersimpan, dan sukar untuk mengupas semuanya dalam artikel pendek Namun, kami percaya bahawa selagi kami mempunyai pemahaman dan aplikasi yang mendalam, kami akan mendapat banyak manfaat dalam kerja sebenar.

Atas ialah kandungan terperinci Contoh cara mencipta dan melaksanakan prosedur tersimpan dalam Oracle. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!