Prosedur Tersimpan MySQL: Tulis, Uji dan Panggil dari CFML

Prosedur tersimpan MySQL ditulis dalam pangkalan data dengan CREATE PROCEDURE, kemudian dipanggil dari CFML dengan cfstoredproc. Bahagian yang selalu menyusahkan bukan panggilan itu, tetapi parameter out yang pulang kosong, dua set hasil yang tertukar, dan prosedur yang melakukan COMMIT sehingga cftransaction anda tidak lagi mengawal apa-apa.

Artikel ini memberi tumpuan kepada bahagian MySQL, iaitu cara prosedur itu ditulis dan tingkah laku yang benar-benar berlaku apabila ia dipanggil. Rujukan penuh untuk atribut tag ada dalam cfstoredproc dan cfprocparam. Setiap blok CFML di bawah dijalankan melalui HTTP terhadap Lucee 7.1.1.17 dengan MySQL 8.4, dan keputusan di sini datang daripada kod HTTP serta output sebenar. Satu perbezaan dengan dokumen lama: atribut dbtype pada cfstoredproc ditolak oleh Lucee.

Menulis prosedur pertama

MySQL memisahkan arahan dengan koma bertitik, dan badan prosedur juga mengandungi koma bertitik. Sebab itu klien mysql memerlukan DELIMITER sebelum CREATE PROCEDURE; jika tidak, ia berhenti membaca pada koma bertitik pertama di dalam badan prosedur.

DELIMITER //

CREATE PROCEDURE senarai_produk(IN p_min_stok INT)
BEGIN
    SELECT id, nama, harga, stok
      FROM produk
     WHERE stok >= p_min_stok
     ORDER BY nama;
END //

DELIMITER ;

Prosedur itu menerima satu parameter masuk dan memulangkan satu set hasil. Panggilan dari CFML hanya memerlukan nama prosedur, datasource, satu cfprocparam dan satu cfprocresult.

<cfstoredproc procedure="senarai_produk" datasource="kedai">
    <cfprocparam type="in" cfsqltype="cf_sql_integer" value="5">
    <cfprocresult name="qProduk">
</cfstoredproc>
<cfoutput>#qProduk.recordCount# produk dengan stok 5 atau lebih</cfoutput>

Blok itu membalas 200 dan mencetak 3 baris, kerana hanya tiga baris dalam jadual ujian mempunyai stok 5 atau lebih. Nama lajur yang diterima ialah ID, NAMA, HARGA, STOK dalam huruf besar, kerana nama itu datang daripada pemacu JDBC. CFML tidak mengira huruf besar dalam nama kunci, jadi qProduk.nama tetap berfungsi.

Tiga jenis parameter dan cara mengisytiharkannya

MySQL mengisytiharkan arah parameter dalam tandatangan prosedur, dan CFML mengulangi arah yang sama pada cfprocparam. Kedua-dua pihak mesti sepadan; kalau tidak, pemanggilan gagal atau nilai pulang hilang.

Contoh di bawah menambah stok dan memulangkan nilai baharu melalui parameter keluar. Perhatikan bahawa cfprocparam jenis out tidak mempunyai atribut value langsung.

DELIMITER //

CREATE PROCEDURE tambah_stok(IN p_id INT, IN p_tambah INT, OUT p_stok_baru INT)
BEGIN
    UPDATE produk SET stok = stok + p_tambah WHERE id = p_id;
    SELECT stok INTO p_stok_baru FROM produk WHERE id = p_id;
END //

DELIMITER ;

<cfstoredproc procedure="tambah_stok" datasource="kedai">
    <cfprocparam type="in" cfsqltype="cf_sql_integer" value="1">
    <cfprocparam type="in" cfsqltype="cf_sql_integer" value="2">
    <cfprocparam type="out" cfsqltype="cf_sql_integer" variable="stokBaru">
</cfstoredproc>
<cfoutput>Stok baharu: #stokBaru#</cfoutput>

Dalam rig, stokBaru keluar sebagai integer sebenar, bukan rentetan, dan nilainya ialah stok selepas ditambah. Ujian yang sama dengan type="inout" juga berjaya: nilai awal dihantar masuk, prosedur menulis semula, dan CFML menerima nilai baharu pada pemboleh ubah yang sama.

Ada satu kekeliruan yang mudah berlaku. Apabila SELECT ... INTO tidak menjumpai baris, prosedur tidak membaling ralat; pemboleh ubah keluar kekal NULL, CFML memaparkan nilai kosong, dan itu kelihatan seperti parameter keluar tidak disokong. Semak IS NULL dalam prosedur sebelum menyalahkan enjin.

Berbilang set hasil dalam satu panggilan

MySQL membenarkan satu prosedur memulangkan lebih daripada satu set hasil. Lucee menangkap setiap satu dengan resultset pada cfprocresult, bernombor mengikut urutan SELECT di dalam prosedur, bermula dengan 1.

DELIMITER //

CREATE PROCEDURE dua_set(IN p_min_stok INT)
BEGIN
    SELECT COUNT(*) AS jumlah FROM produk WHERE stok >= p_min_stok;
    SELECT id, nama FROM produk WHERE stok >= p_min_stok ORDER BY id;
END //

DELIMITER ;

<cfstoredproc procedure="dua_set" datasource="kedai">
    <cfprocparam type="in" cfsqltype="cf_sql_integer" value="0">
    <cfprocresult name="qKira" resultset="1">
    <cfprocresult name="qSenarai" resultset="2">
</cfstoredproc>
<cfoutput>#qKira.jumlah# baris dikira, #qSenarai.recordCount# baris disenaraikan</cfoutput>

Kedua-dua set datang dengan betul dalam rig: set pertama mengandungi satu baris dengan lajur JUMLAH, dan set kedua mengandungi enam baris dengan lajur ID, NAMA. Susunan cfprocresult mesti mengikut susunan SELECT dalam prosedur; kalau tidak, anda menangkap hasil yang salah dan menganggapnya pepijat enjin.

Ralat dari dalam prosedur: SIGNAL dan handler

Prosedur yang hanya memulangkan hasil tidak mencukupi apabila input tidak sah. MySQL menyediakan SIGNAL untuk membaling ralat dengan kod SQLSTATE sendiri, dan ralat itu sampai ke CFML sebagai pengecualian pangkalan data biasa.

DELIMITER //

CREATE PROCEDURE produk_harga_min(IN p_harga DECIMAL(10,2))
BEGIN
    IF p_harga < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Harga minimum tidak boleh negatif';
    END IF;
    SELECT id, nama, harga FROM produk WHERE harga >= p_harga ORDER BY harga;
END //

DELIMITER ;

<cftry>
    <cfstoredproc procedure="produk_harga_min" datasource="kedai">
        <cfprocparam type="in" cfsqltype="cf_sql_decimal" value="-1">
        <cfprocresult name="qMurah">
    </cfstoredproc>
    <cfcatch type="any">
        <cfoutput>#cfcatch.type#: #cfcatch.message# (SQLSTATE #cfcatch.sqlState#)</cfoutput>
    </cfcatch>
</cftry>

Hasil sebenar dalam rig: cfcatch.type ialah database, cfcatch.message ialah teks MESSAGE_TEXT, cfcatch.sqlState ialah 45000, dan cfcatch.nativeErrorCode ialah 1644. Aplikasi boleh membezakan ralat perniagaan daripada ralat sambungan tanpa membaca teks mesej, asalkan prosedur menetapkan SQLSTATE yang tersendiri.

Handler dalam prosedur, contohnya DECLARE EXIT HANDLER FOR SQLEXCEPTION, berfungsi pada lapisan MySQL dan bukan lapisan CFML. Apabila handler menelan ralat dan hanya menetapkan pemboleh ubah status, CFML tidak melihat pengecualian sama sekali, jadi aplikasi mesti memeriksa nilai status itu sendiri.

Transaksi: siapa yang mengawal COMMIT

Inilah bahagian yang paling mudah merosakkan data, dan ia tidak kelihatan sehingga diuji. cftransaction hanya boleh menggulung balik kerja yang masih berada dalam transaksi yang sama. Kalau prosedur memulakan dan menamatkan transaksinya sendiri, keputusan itu sudah muktamad sebelum CFML sampai ke penghujung blok.

Dua ukuran yang saya jalankan dengan prosedur pemindahan stok:

Kes kedua itu bukan pepijat Lucee. MySQL menganggap COMMIT di dalam prosedur sebagai penamat transaksi semasa, jadi ROLLBACK kemudian tidak mempunyai apa-apa untuk digulung. Corak yang selamat ialah membiarkan pemanggil mengawal transaksi: prosedur hanya melakukan UPDATE dan SELECT, tanpa START TRANSACTION dan tanpa COMMIT, manakala cftransaction di sekelilingnya memegang sempadan transaksi. Corak penuh ada dalam cftransaction, commit dan rollback.

<cftransaction>
    <cfstoredproc procedure="pindah_stok" datasource="kedai">
        <cfprocparam type="in" cfsqltype="cf_sql_integer" value="1">
        <cfprocparam type="in" cfsqltype="cf_sql_integer" value="2">
        <cfprocparam type="in" cfsqltype="cf_sql_integer" value="2">
        <cfprocparam type="out" cfsqltype="cf_sql_varchar" variable="statusPindah">
    </cfstoredproc>
</cftransaction>
<cfoutput>#statusPindah#</cfoutput>

Blok itu membalas 200 dan mencetak berjaya dalam rig. Tetapi kerana prosedur contoh memanggil COMMIT di dalam badannya, blok itu hanya selamat kerana tiada ralat selepasnya. Kalau anda menambah satu langkah lagi dan langkah itu gagal, pemindahan stok tadi tetap kekal.

Tiga had yang saya ukur pada Lucee dan MySQL

Pertama, dbtype ditolak. Menulis <cfstoredproc ... dbtype="MySQL"> menghasilkan ralat kompil Attribute [dbtype] is not allowed for tag [cfstoredproc], dan enjin menyenaraikan atribut sah: blockfactor, cachedafter, cachedwithin, cachename, datasource, debug, password, result, returncode, timeout, username, procedure.

Kedua, returncode="true" tidak berfungsi pada MySQL. Lucee membentuk panggilan seperti fungsi, dan MySQL membalas FUNCTION kedai.senarai_produk does not exist. MySQL tidak mempunyai nilai pulangan prosedur seperti SQL Server, jadi gunakan parameter out untuk membawa status.

Ketiga, executionTime dalam struktur result bukan milisaat dan bukan mikrosaat. Prosedur yang memanggil SLEEP(0.2) melaporkan 207047870, manakala prosedur ringkas melaporkan 7952514. Nisbah itu sepadan dengan nanosaat, jadi bahagikan dengan satu bilion untuk mendapatkan saat. Struktur result mengandungi dua kunci sahaja, iaitu executionTime dan cached.

Kebenaran pangkalan data: EXECUTE sudah cukup

Prosedur tersimpan boleh menjadi lapisan keselamatan, bukan hanya lapisan prestasi. Dalam ujian, saya mencipta pengguna MySQL yang hanya mempunyai GRANT EXECUTE pada pangkalan data itu. Pengguna tersebut berjaya memanggil prosedur dan membaca set hasilnya, tetapi SELECT terus pada jadual ditolak dengan SELECT command denied.

Sebabnya ialah prosedur MySQL berjalan dengan hak DEFINER secara lalai, iaitu hak pengguna yang mencipta prosedur. Aplikasi boleh membaca data melalui prosedur tanpa kebenaran membaca jadual, tetapi prosedur itu sendiri mesti menyemak siapa yang memanggil jika data berbeza mengikut pengguna.

Dua arahan yang membantu semasa menyelenggara prosedur ialah SHOW CREATE PROCEDURE untuk melihat definisi penuh, dan information_schema.routines untuk menyenaraikan prosedur dalam sesuatu skema. Kalau sambungan gagal sebelum sampai ke prosedur, ralatnya bukan pada prosedur; lihat ralat sambungan MySQL dalam Lucee dan konfigurasi datasource.

Senarai semak sebelum prosedur naik ke produksi

• Uji prosedur pada MySQL sebenar, bukan pada H2. H2 tidak menyokong OUT, INOUT dan berbilang set hasil cara MySQL.
• Pastikan susunan cfprocparam sama dengan susunan parameter dalam CREATE PROCEDURE, dan beri atribut variable pada setiap parameter out dan inout.
• Senaraikan cfprocresult mengikut susunan SELECT, dan uji setiap resultset.
• Jangan letak COMMIT di dalam prosedur kalau pemanggil yang mengawal transaksi.
• Jangan bina nama prosedur daripada input pengguna. Pilih prosedur melalui senarai putih dalam kod, seperti yang dibincangkan dalam cfquery dan cfqueryparam.
• Beri pengguna aplikasi EXECUTE sahaja, bukan ALL PRIVILEGES, jika prosedur itu satu-satunya jalan masuk yang diperlukan.

Bila prosedur menyusahkan

Prosedur tersimpan menyusahkan apabila logiknya hanya digunakan oleh satu aplikasi dan berubah setiap minggu. Setiap perubahan memerlukan migrasi pangkalan data, penyahpepijatan berlaku di luar jejak ralat CFML, dan anda terikat kepada dialek MySQL.

Soalan Lazim

Perlukah saya pasang sambungan tambahan untuk menggunakan cfstoredproc dalam Lucee?

Tidak. cfstoredproc ialah tag teras Lucee. Yang menentukan kejayaan ialah pangkalan data menyokong prosedur tersimpan dan datasource didaftarkan dengan betul.

Kenapa parameter out saya kosong walaupun prosedur berjalan?

Biasanya prosedur tidak menetapkan nilai itu. SELECT ... INTO yang tidak menjumpai baris meninggalkan pemboleh ubah sebagai NULL. Semak juga bahawa cfprocparam itu mempunyai atribut variable dan bukan value.

Bolehkah saya memanggil prosedur dengan cfquery dan CALL?

Boleh untuk set hasil, tetapi cfquery tidak boleh mengikat parameter out. Helah pemboleh ubah sesi seperti @stok_baru berfungsi dalam ujian saya kerana sambungan yang sama digunakan semula.

Kenapa returncode gagal apabila prosedur dipanggil pada MySQL?

Lucee membentuk panggilan sebagai fungsi apabila returncode dihidupkan, dan MySQL membalas bahawa fungsi itu tidak wujud. Bawa status melalui parameter out.

Adakah executionTime dalam milisaat?

Tidak. Dalam Lucee 7.1.1.17 nilainya dalam nanosaat. Prosedur yang tidur 0.2 saat melaporkan sekitar 207 juta, jadi bahagikan dengan satu bilion.

Sumber

• CREATE PROCEDURE, Manual MySQL 8.4
• SIGNAL, Manual MySQL 8.4
• DECLARE HANDLER, Manual MySQL 8.4
• GRANT, Manual MySQL 8.4
• Dokumentasi cfstoredproc, docs.lucee.org
• Dokumentasi cfprocparam, docs.lucee.org
• Dokumentasi cfprocresult, docs.lucee.org