prosedur tersimpan oracle mengembalikan set hasil

Dalam Pangkalan Data Oracle, prosedur tersimpan ialah blok kod terkumpul dan boleh diguna semula yang menerima parameter input dan melaksanakan satu siri operasi, akhirnya mengembalikan hasil. Hasilnya mungkin nilai skalar, set hasil yang disimpan dalam jadual atau kursor sementara, atau nilai yang dihantar kepada pemanggil melalui parameter OUT.

Dalam kerja harian, kita selalunya perlu menulis prosedur tersimpan untuk menyelesaikan beberapa operasi kelompok, tugasan yang berjalan lama atau logik pemprosesan data yang kompleks. Walau bagaimanapun, apabila kita perlu mengembalikan set hasil dalam prosedur tersimpan, kita sering menghadapi beberapa masalah: Bagaimana untuk mengeluarkan set hasil? Apakah format set hasil output? Bagaimana untuk mengendalikan berbilang set hasil?

Untuk menangani isu ini, artikel ini akan memperkenalkan cara mengembalikan set hasil dalam prosedur tersimpan Oracle dan menyediakan beberapa kod sampel untuk membantu pembaca memahami dengan lebih baik.

1. Gunakan kursor untuk mengembalikan set hasil

Dalam prosedur tersimpan Oracle, kita boleh menggunakan kursor untuk mengembalikan set hasil. Khususnya, kita perlu mentakrifkan pembolehubah jenis REF CURSOR, kemudian isi data ke dalam kursor melalui operasi OPEN-FETCH-CLOSE, dan akhirnya kembalikan kursor kepada pemanggil sebagai parameter OUT.

Berikut ialah contoh kod ringkas yang menunjukkan cara menggunakan kursor untuk mengembalikan semua rekod dalam jadual pekerja:

CREATE OR REPLACE PROCEDURE get_all_employees(cur OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN cur FOR
    SELECT * FROM employees;
END;

Dalam kod di atas, kami telah menentukan storan bernama get_all_employees Prosedur, ia mempunyai parameter OUT cur jenis SYS_REFCURSOR, yang mewakili set hasil yang dikembalikan. Dalam prosedur tersimpan, kami menggunakan OPEN cur FOR untuk mengisi kursor dengan keputusan pelaksanaan pernyataan SELECT. Akhirnya, pada akhir prosedur yang disimpan, kursor ditutup secara automatik.

Apabila memanggil prosedur tersimpan, kita perlu terlebih dahulu mengisytiharkan pembolehubah jenis yang sama seperti kursor, hantarkannya sebagai parameter kepada prosedur tersimpan, dan kemudian gunakan pernyataan FETCH untuk membaca baris data daripada kursor :

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
BEGIN
  get_all_employees(emp_cur);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理数据行
  END LOOP;
  CLOSE emp_cur;
END;

Dalam kod di atas, kami mula-mula mengisytiharkan pembolehubah kursor bernama emp_cur, kemudian panggil get_all_employees prosedur tersimpan dan lulus emp_cur sebagai parameter. Seterusnya, kami menggunakan pernyataan LOOP dan FETCH untuk membaca data baris demi baris daripada kursor, menggunakan emp_rec pembolehubah untuk menyimpan data baris semasa pada setiap lelaran gelung. Selepas membaca semua data, kita perlu menutup kursor secara manual untuk mengeluarkan sumber.

Perlu diambil perhatian bahawa dalam kod di atas, kami menggunakan %ROWTYPE untuk menentukan pembolehubah jenis baris employees jenis emp_rec jadual. Dengan cara ini, dalam pernyataan FETCH, bukannya menentukan pembolehubah secara manual untuk setiap medan, keseluruhan baris data boleh dibaca ke dalam pembolehubah emp_rec. Pendekatan ini menjadikan kod lebih ringkas dan boleh dibaca.

Perlu dinyatakan bahawa dalam Oracle 12c, kita juga boleh menggunakan pernyataan FETCH BULK COLLECT INTO untuk membaca berbilang baris data ke dalam jadual PL/SQL atau pembolehubah tatasusunan pada satu masa untuk meningkatkan kecekapan kod. Memandangkan penggunaan BULK COLLECT adalah agak rumit, artikel ini tidak akan menerangkan secara terperinci Pembaca boleh mencari maklumat yang berkaitan dan mengetahui lebih lanjut.

2. Gunakan jadual sementara untuk mengembalikan set hasil

Selain kursor, kita juga boleh menggunakan jadual sementara untuk mengembalikan set hasil. Secara khusus, kita boleh mencipta jadual sementara dalam prosedur yang disimpan, mengisi jadual dengan data, dan akhirnya mengembalikan nama jadual kepada pemanggil sebagai parameter OUT.

Berikut ialah contoh kod ringkas yang menunjukkan cara menggunakan jadual sementara untuk mengembalikan semua rekod dalam jadual pekerja:

CREATE OR REPLACE PROCEDURE get_all_employees(tbl_name OUT VARCHAR2)
AS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_employees
  AS SELECT * FROM employees;
  
  tbl_name := 'temp_employees';
END;

Dalam kod di atas, kami mula-mula mencipta fail bernama temp_employees Jadual sementara global, semasa membuat jadual, isi semua rekod dalam jadual employees ke dalam jadual. Seterusnya, kami mengembalikan nama jadual "temp_employees" kepada pemanggil melalui parameter OUT tbl_name.

Apabila memanggil prosedur tersimpan, kami boleh mengakses data dalam jadual sementara melalui nama jadual:

DECLARE
  tbl_name VARCHAR2(30);
BEGIN
  get_all_employees(tbl_name);
  SELECT * FROM TABLE(tbl_name);
END;

Dalam kod di atas, kami mengisytiharkan pembolehubah bernama tbl_name, Digunakan untuk menyimpan nama jadual yang dikembalikan oleh prosedur tersimpan. Apabila prosedur tersimpan get_all_employees dipanggil, tbl_name akan dikemas kini kepada "temp_employees". Selepas itu, kita boleh mengakses data dalam jadual sementara melalui pernyataan SELECT * FROM TABLE(tbl_name) dan memaparkannya dalam klien.

Perlu diambil perhatian bahawa kitaran hayat jadual sementara global ialah tahap sesi, iaitu, data dalam jadual akan dipadamkan secara automatik apabila sesi pangkalan data tamat. Ini memastikan setiap sesi mempunyai jadual sementara sendiri dan mengelakkan konflik data antara sesi yang berbeza.

3. Kembalikan berbilang set hasil

Dalam sesetengah kes, kita perlu mengembalikan berbilang set hasil dalam satu prosedur tersimpan. Sebagai contoh, dalam pertanyaan kompleks, kita perlu mengembalikan kedua-dua hasil pertanyaan dan beberapa statistik ringkasan. Dalam prosedur tersimpan Oracle, kita boleh menggunakan parameter OUT dan kursor untuk mencapai output set hasil berbilang.

Berikut ialah contoh kod mudah yang menunjukkan cara mengembalikan dua set hasil dalam prosedur tersimpan:

CREATE OR REPLACE PROCEDURE get_employees_and_stats(cur OUT SYS_REFCURSOR, total_salary OUT NUMBER)
AS
BEGIN
  OPEN cur FOR SELECT * FROM employees;
  SELECT SUM(salary) INTO total_salary FROM employees;
END;

在上面的代码中，我们定义了一个名为 get_employees_and_stats 的存储过程，它有两个 OUT 参数，分别是一个游标变量 cur 和一个标量变量 total_salary。在存储过程中，我们先通过 OPEN cur FOR 来填充游标变量 cur，并将其返回给调用者。接着，我们通过 SELECT SUM(salary) INTO total_salary FROM employees; 语句计算出员工表中工资的总和，并将结果设置为标量变量 total_salary，同样也将其返回给调用者。

在调用存储过程时，我们需要将两个 OUT 参数作为参数传递给存储过程，并用游标变量来访问查询结果：

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
  total_salary NUMBER;
BEGIN
  get_employees_and_stats(emp_cur, total_salary);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理员工数据行
  END LOOP;
  -- 处理工资汇总数据（total_salary）
  CLOSE emp_cur;
END;

在上面的代码中，我们声明了一个游标变量 emp_cur 和一个标量变量 total_salary，用来接收存储过程的返回值。在调用存储过程时，我们将这两个变量作为参数传递给存储过程，并通过 FETCH emp_cur INTO emp_rec 逐行读取结果集中的数据。在读取所有数据之后，我们通过标量变量 total_salary 处理工资汇总数据。最后，我们需要手动关闭游标 emp_cur 以释放资源。

需要注意的是，在返回多个结果集时，我们需要保证每个结果集在调用存储过程之前都执行完毕，否则可能会导致输出数据不完整或者部分数据丢失。因此，我们需要仔细设计存储过程的逻辑，保证数据的完整性和准确性。

总结

在 Oracle 数据库中，存储过程是一个强大的编程工具，可以帮助我们完成一些复杂的数据处理任务。在存储过程中，返回结果集是一个常见的需求，我们可以使用游标或者临时表来实现结果集的输出，也可以通过 OUT 参数来返回多个结果集。在编写存储过程时，我们需要深入理解数据库的工作方式和 PL/SQL 语法，以保证代码的正确性和性能。

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