kursor prosedur tersimpan mysql

Penjelasan terperinci tentang prosedur dan kursor tersimpan MySQL

Dalam pangkalan data MySQL, prosedur tersimpan ialah satu set pernyataan SQL yang telah disusun sebelumnya, yang boleh dianggap sebagai subrutin dalam pangkalan data. Prosedur tersimpan biasanya digunakan untuk mengendalikan logik perniagaan yang kompleks, memudahkan interaksi antara aplikasi dan pangkalan data, dan meningkatkan kecekapan dan keselamatan pemprosesan data. Kursor ialah mekanisme yang digunakan untuk memproses set hasil pertanyaan dalam prosedur tersimpan.

Artikel ini akan memperkenalkan secara terperinci penggunaan dan ciri-ciri prosedur dan kursor disimpan MySQL.

1. Prosedur tersimpan

1.1 Struktur sintaks prosedur tersimpan

Berikut ialah struktur sintaks asas prosedur tersimpan MySQL:

CREATE PROCEDURE procedure_name [ (parameter_list) ] BEGIN
    -- 存储过程的逻辑实现
END;

di mana ,

• procedure_name: nama prosedur yang disimpan;
• parameter_list: senarai parameter prosedur yang disimpan, yang boleh mengandungi 0 atau lebih parameter Setiap parameter terdiri daripada a nama parameter dan jenis parameter;
• Antara BEGIN dan END ialah bahagian pelaksanaan logik bagi prosedur tersimpan.

Sebagai contoh, tentukan prosedur tersimpan mudah untuk menanyakan semua rekod dalam jadual pelajar dan kembalikan:

CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM student;
END;

1.2 Lulus parameter prosedur tersimpan

Prosedur tersimpan boleh pratakrif parameter, yang boleh diluluskan apabila memanggil prosedur tersimpan. Prosedur tersimpan MySQL menyokong tiga kaedah lulus parameter: IN, OUT dan INOUT.

• IN: Sebagai parameter input, nilai parameter diluluskan apabila memanggil prosedur tersimpan.
• KELUAR: Sebagai parameter output, hasilnya dikira dan dikembalikan dalam prosedur yang disimpan.
• INOUT: Ini adalah kedua-dua parameter input dan parameter output Apabila memanggil prosedur tersimpan, nilai parameter diluluskan dan hasil yang dikira oleh prosedur tersimpan diterima.

Berikut ialah contoh prosedur tersimpan menggunakan lulus parameter:

CREATE PROCEDURE add_two_numbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

Dalam contoh di atas, prosedur tersimpan add_two_numbers menerima dua parameter input num1 dan num2, dan hasil daripada menambahkannya Berikan nilai kepada hasil parameter output.

Anda boleh menggunakan pernyataan berikut untuk memanggil prosedur tersimpan ini:

CALL add_two_numbers(2, 3, @result);
SELECT @result; -- 输出 5

1.3 Kawalan proses prosedur tersimpan

Prosedur tersimpan MySQL menyokong kenyataan kawalan proses biasa, seperti JIKA, KES, SEMASA, LOOP dan LEAVE dll. Gunakan pernyataan ini untuk melaksanakan kawalan logik yang kompleks dan pemprosesan perniagaan.

Berikut ialah contoh prosedur tersimpan menggunakan pernyataan IF dan WHILE:

CREATE PROCEDURE calc_factorial(IN num INT, OUT result BIGINT)
BEGIN
    SET result = 1;
    WHILE num > 1 DO
        SET result = result * num;
        SET num = num - 1;
    END WHILE;
END;

Dalam contoh di atas, jika nombor parameter input lebih besar daripada 1, gelung WHILE digunakan untuk mengira nilai faktor num, dan Simpan hasilnya dalam hasil parameter output.

Anda boleh menggunakan pernyataan berikut untuk memanggil prosedur tersimpan ini:

CALL calc_factorial(6, @result);
SELECT @result; -- 输出 720

2. Kursor

2.1 Konsep asas kursor

Dalam MySQL stored procedure , kursor ialah mekanisme yang digunakan untuk melintasi keputusan pertanyaan yang ditetapkan dalam prosedur tersimpan. Kursor menjejaki baris semasa dalam set hasil pertanyaan dan menggerakkan baris demi baris mengikut keperluan untuk membaca data dalam baris tersebut.

Menggunakan kursor untuk mengisytiharkan, membuka, menutup dan mengendalikan kursor memerlukan empat arahan berikut:

• ISYTIHKAN: mengisytiharkan kursor, mentakrifkan nama kursor, pernyataan pertanyaan dan jenis kursor, dsb.
• BUKA: Buka kursor dan simpan set hasil pertanyaan pada penimbal yang ditunjuk oleh kursor.
• FETCH: Dapatkan baris semasa dihalakan oleh kursor dan simpan nilai baris semasa ke dalam pembolehubah yang sepadan.
• TUTUP: Tutup kursor dan lepaskan memori yang diduduki oleh kursor.

Berikut ialah contoh prosedur tersimpan menggunakan kursor:

CREATE PROCEDURE get_all_students()
BEGIN
    DECLARE done INT DEFAULT FALSE; -- 定义游标是否结束的标志
    DECLARE s_id INT; -- 存储查询结果中的学生编号
    DECLARE s_name VARCHAR(255); -- 存储查询结果中的学生姓名
    DECLARE cursor_students CURSOR FOR SELECT id, name FROM student; -- 声明游标，查询表 student 中的所有数据
    -- 打开游标
    OPEN cursor_students;
    -- 遍历游标指向的结果集
    read_loop: LOOP
        -- 获取游标指向的当前行
        FETCH cursor_students INTO s_id, s_name;
        -- 如果游标到达结果集的末尾，则跳出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 输出当前行的值
        SELECT s_id, s_name;
    END LOOP;
    -- 关闭游标
    CLOSE cursor_students;
END;

Dalam contoh di atas, kursor bernama cursor_students diisytiharkan untuk menanyakan semua data dalam pelajar jadual. Selepas membuka kursor, gunakan badan gelung read_loop untuk melintasi set hasil pertanyaan, dan gunakan arahan FETCH untuk mendapatkan nilai baris semasa, simpannya dalam pembolehubah s_id dan s_name, dan keluarkannya ke konsol. Jika kursor mencapai penghujung set hasil, keluar dari gelung. Apabila kursor selesai digunakan, anda perlu menggunakan arahan CLOSE untuk menutup kursor.

2.2 Ciri dan senario aplikasi kursor

Walaupun kursor memberikan pembangun cara yang mudah untuk membuat pertanyaan set hasil, ia memerlukan memori dan sumber tambahan Anda perlu memberi perhatian khusus kepada isu berikut:

• Kursor akan menjejaskan prestasi, terutamanya apabila memproses set data yang besar. Ia tidak disyorkan untuk menggunakan kursor dalam persekitaran berskala besar atau konkurensi tinggi, dan kaedah lain, seperti subkueri dan operasi JOIN, harus diberi keutamaan.
• Kursor hanya boleh digunakan dalam prosedur tersimpan dan tidak boleh digunakan secara langsung dalam pernyataan SQL.
• Kursor perlu digunakan dengan berhati-hati, kerana jika kursor tidak ditutup dengan betul, ia akan menyebabkan pangkalan data MySQL menduduki banyak sumber memori atau malah ranap.

Biasanya, kursor sesuai untuk senario berikut:

• Situasi di mana logik pertanyaan kompleks perlu dilaksanakan dalam prosedur tersimpan.
• Situasi di mana set data yang besar perlu diproses secara berkelompok.
• Situasi di mana set hasil pertanyaan perlu diproses baris demi baris.

3. Ringkasan

Artikel ini terutamanya memperkenalkan penggunaan dan ciri-ciri prosedur tersimpan dan kursor dalam pangkalan data MySQL. Prosedur tersimpan boleh meningkatkan kecekapan dan keselamatan interaksi antara aplikasi dan pangkalan data, dan kursor boleh melintasi set hasil pertanyaan dengan mudah. Walau bagaimanapun, perlu diingatkan bahawa anda perlu berhati-hati apabila menggunakan kursor untuk mengelakkan kebocoran memori dan masalah prestasi.

Rujukan:

• MySQL :: Manual Rujukan MySQL 8.0 :: 13.6.4.1 ISYTIHKAN Penyata kursor
• MySQL :: Manual Rujukan MySQL 8.0 :: 13.6.4.2 BUKA Penyata kursor
MySQL :: MySQL 8.0 Rujukan Manual :: 13.6.4.4 CLOSE cursor Statement

Atas ialah kandungan terperinci kursor prosedur tersimpan mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!