prosedur tersimpan oracle mengembalikan set hasil

Dalam pangkalan data Oracle, prosedur tersimpan ialah kaedah pemprosesan data yang digunakan secara meluas. Walaupun prosedur tersimpan boleh melakukan pelbagai operasi data yang kompleks, penggunaan yang paling biasa adalah untuk mengembalikan set hasil pertanyaan. Dalam artikel ini, kami akan meneroka kaedah dan pelaksanaan prosedur tersimpan Oracle untuk mengembalikan set hasil.

1. Pengenalan kepada prosedur tersimpan Oracle

Prosedur tersimpan dalam pangkalan data Oracle ialah objek pangkalan data, iaitu satu set penyata SQL tersusun yang ditulis dalam bahasa PL/SQL. Prosedur tersimpan boleh dianggap sebagai pernyataan SQL yang diprogramkan, yang biasanya digunakan untuk melaksanakan operasi data yang kompleks, seperti pengiraan data, penapisan data dan pemprosesan logik.

Prosedur tersimpan boleh digunakan untuk memproses pelbagai data, termasuk set hasil pertanyaan, mengubah suai data, mencipta dan memadam objek dan melaksanakan operasi data lain. Prosedur tersimpan yang paling biasa digunakan digunakan untuk menanyakan data, yang boleh mengembalikan set hasil tertentu berdasarkan syarat pertanyaan tersuai.

2. Kaedah untuk prosedur tersimpan Oracle untuk mengembalikan set hasil

Pangkalan data Oracle menyediakan pelbagai kaedah untuk mengembalikan set prosedur tersimpan Berikut adalah beberapa yang lebih biasa digunakan.

1. Gunakan kursor untuk mengembalikan set hasil

Kursor ialah struktur data yang digunakan untuk memproses set hasil pertanyaan Ia boleh menyimpan set hasil pertanyaan dalam memori supaya program boleh Anda boleh memanipulasi dan memproses data dalam set hasil. Dalam prosedur tersimpan, anda menggunakan kursor untuk menyimpan set hasil pertanyaan ke dalam kursor dan kemudian mengembalikannya ke kursor.

Dalam prosedur tersimpan Oracle, proses asas menggunakan kursor untuk mengembalikan set hasil ialah: mula-mula tentukan pembolehubah kursor, kemudian simpan set hasil pertanyaan ke kursor dan kembalikan pembolehubah kursor sebagai prosedur tersimpan nilai pulangan.

Berikut ialah contoh prosedur tersimpan yang menggunakan kursor untuk mengembalikan set hasil:

CREATE OR REPLACE PROCEDURE my_proc(p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_recordset FOR
    SELECT * FROM my_table;
END;

Dalam contoh di atas, kami mentakrifkan prosedur tersimpan bernama my_proc, dan nilai pulangannya ialah kursor p_recordset . Apabila prosedur tersimpan dilaksanakan, data dalam jadual my_table disoal dan set hasil disimpan dalam pembolehubah kursor p_recordset.

2. Menggunakan jenis jadual untuk mengembalikan set hasil

Pangkalan data Oracle juga menyediakan cara lain untuk mengembalikan set hasil, iaitu menggunakan jenis jadual. Jenis jadual ialah jenis objek Oracle yang digunakan untuk mentakrifkan struktur data jadual. Ia boleh menentukan pelbagai jenis lajur dan jenis data, termasuk rentetan, integer, tarikh, dsb.

Dalam prosedur tersimpan, anda boleh menggunakan jenis jadual untuk merangkum set hasil pertanyaan ke dalam jenis jadual, dan kemudian gunakan jenis jadual sebagai nilai pulangan prosedur tersimpan.

Berikut ialah contoh prosedur tersimpan yang mengembalikan set hasil menggunakan jenis jadual:

CREATE OR REPLACE TYPE my_type AS OBJECT (
  c1 INT,
  c2 VARCHAR2(10)
);

CREATE OR REPLACE TYPE my_table_type AS TABLE OF my_type;

CREATE OR REPLACE PROCEDURE my_proc(p_recordset OUT my_table_type)
AS
BEGIN
  SELECT my_type(my_table.col1, my_table.col2)
    BULK COLLECT INTO p_recordset
    FROM my_table;
END;

Dalam contoh di atas, kami mentakrifkan dua jenis objek my_type dan my_table_type, di mana my_type mewakili satu baris data dan my_table_type Mewakili set hasil pertanyaan lengkap. Dalam prosedur tersimpan my_proc, kami merangkum set hasil pertanyaan ke dalam objek jenis jadual my_table_type dan menggunakannya sebagai nilai pulangan prosedur tersimpan.

3. Pelaksanaan prosedur tersimpan Oracle mengembalikan set hasil

Seterusnya kita akan melaksanakan prosedur tersimpan secara beransur-ansur menggunakan kursor dan jenis jadual untuk mengembalikan data dalam jadual my_table. Prosedur tersimpan ini mentakrifkan dua parameter, iaitu parameter input date_field dan parameter output p_recordset.

Pertama, kita perlu mencipta jadual my_table dan memasukkan beberapa data ujian untuk digunakan sebagai contoh kita. Pernyataan SQL untuk mencipta dan memasukkan data adalah seperti berikut:

CREATE TABLE my_table (
  col1 NUMBER,
  col2 VARCHAR2(10),
  date_field DATE
);

INSERT INTO my_table VALUES (1, 'A', TO_DATE('2022-01-01', 'YYYY-MM-DD'));
INSERT INTO my_table VALUES (2, 'B', TO_DATE('2022-02-01', 'YYYY-MM-DD'));
INSERT INTO my_table VALUES (3, 'C', TO_DATE('2022-03-01', 'YYYY-MM-DD'));

Seterusnya, kami mencipta prosedur tersimpan bernama my_proc untuk menanyakan data dalam jadual my_table dan menyimpan hasil pertanyaan ke pembolehubah kursor. Dalam prosedur tersimpan, kami mentakrifkan parameter date_field untuk mengehadkan julat tarikh set hasil pertanyaan.

Kod sampel prosedur tersimpan my_proc yang menggunakan kursor untuk mengembalikan set hasil adalah seperti berikut:

CREATE OR REPLACE PROCEDURE my_proc(
  date_field IN DATE DEFAULT NULL,
  p_recordset OUT SYS_REFCURSOR
)
AS
  v_query_str VARCHAR2(1000);
BEGIN
  v_query_str := 'SELECT * FROM my_table WHERE 1=1';

  IF date_field IS NOT NULL THEN
    v_query_str := v_query_str || ' AND date_field >= :date_field ';
  END IF;

  OPEN p_recordset FOR v_query_str USING date_field;
END;

Dalam kod di atas, kami menggunakan pernyataan SQL dinamik v_query_str untuk membina SQL secara dinamik pernyataan pertanyaan. Pernyataan SQL ini mengandungi sekatan julat tarikh, jadi kita perlu menggunakan pernyataan SQL dinamik untuk menambah keadaan tarikh secara dinamik dalam klausa WHERE.

Baris pertama mentakrifkan rangka kerja asas pernyataan pertanyaan, termasuk nama jadual pertanyaan dan klausa WHERE lalai. Baris 4 menentukan sama ada parameter input date_field adalah NULL Jika ia bukan NULL, tambahkan sekatan tarikh pada pernyataan pertanyaan.

Barisan terakhir melaksanakan pernyataan pertanyaan dan menggunakan pernyataan OPEN untuk menyimpan set keputusan ke dalam pembolehubah kursor dan menggunakan pembolehubah kursor sebagai nilai pulangan prosedur yang disimpan.

Akhir sekali, kami mencipta prosedur tersimpan yang menggunakan jenis jadual untuk mengembalikan set hasil, yang juga digunakan untuk menanyakan data dalam jadual my_table dan menyimpan hasil pertanyaan ke jadual.

Kod sampel prosedur tersimpan my_proc2 yang menggunakan jenis jadual untuk mengembalikan set hasil adalah seperti berikut:

CREATE OR REPLACE TYPE my_type AS OBJECT (
  col1 NUMBER,
  col2 VARCHAR2(10),
  date_field DATE
);

CREATE OR REPLACE TYPE my_table_type AS TABLE OF my_type;

CREATE OR REPLACE PROCEDURE my_proc2(
  date_field IN DATE DEFAULT NULL,
  p_recordset OUT my_table_type
)
AS
BEGIN
  SELECT my_type(col1, col2, date_field)
    BULK COLLECT INTO p_recordset
    FROM my_table
    WHERE date_field >= NVL(date_field, date_field);
END;

Dalam kod di atas, kami mentakrifkan dua jenis objek: objek my_type mewakili data baris, dan jenis my_table_type mewakili set hasil Pertanyaan. Dalam prosedur tersimpan my_proc2, kami merangkum data pertanyaan ke dalam objek jenis jadual my_table_type, dan menggunakan pernyataan PUKAL COLLECT INTO untuk menyimpan set hasil pertanyaan ke dalam parameter p_recordset.

4. Ringkasan

Artikel ini memperkenalkan kaedah menggunakan prosedur tersimpan untuk mengembalikan set hasil dalam pangkalan data Oracle, terutamanya termasuk kaedah menggunakan kursor dan jenis jadual. Melalui contoh praktikal, kami belajar cara menulis prosedur tersimpan yang boleh mengembalikan set hasil pertanyaan dan mempelajari tentang penggunaan pernyataan SQL dinamik.

Dalam pembangunan sebenar, prosedur tersimpan adalah alat pemprosesan data yang sangat berguna, dengan banyak kelebihan seperti kecekapan tinggi, fleksibiliti dan keselamatan. Mempelajari cara menulis dan menggunakan prosedur tersimpan adalah penting untuk meningkatkan keupayaan pemprosesan data pangkalan data Oracle.

Atas ialah kandungan terperinci prosedur tersimpan oracle mengembalikan set hasil. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!