每当我调用它时，如何返回我的过程的 table ？
-- this code will find all the empty categories.
create or replace procedure empty_cats(emptyCat OUT DEPT%rowtype) as
begin
select * into emptyCat from DEPT where CatNO not in (select CatNO from posts);
end;我正在使用 oracle 11 XE & Application Express 4.0.2.00.09
回答1
选项 1：光标
CREATE PROCEDURE empty_cats(
o_cursor OUT SYS_REFCURSOR
) AS
BEGIN
OPEN o_cursor FOR
SELECT *
FROM dept
WHERE CatNO NOT IN (SELECT CatNO FROM posts);
END;
/选项 2：DBMS_SQL.RETURN_RESULT（来自 Oracle 12.1）
CREATE PROCEDURE empty_cats
AS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT *
FROM dept
WHERE CatNO NOT IN (SELECT CatNO FROM posts);
DBMS_SQL.RETURN_RESULT(v_cursor);
END;
/选项 3：集合
创建一个对象和一个集合类型：
CREATE TYPE dept_obj AS OBJECT(
col1 NUMBER,
col2 VARCHAR2(20),
col3 DATE
);
CREATE TYPE dept_obj_table AS TABLE OF dept_obj;然后：
CREATE PROCEDURE empty_cats(
o_depts OUT dept_obj_table
) AS
BEGIN
SELECT dept_obj(col1, col2, col3)
BULK COLLECT INTO o_depts
FROM dept
WHERE CatNO NOT IN (SELECT CatNO FROM posts);
END;
/或者
CREATE FUNCTION empty_cats
RETURN dept_obj_table AS
v_depts dept_obj_table;
BEGIN
SELECT dept_obj(col1, col2, col3)
BULK COLLECT INTO v_depts
FROM dept
WHERE CatNO NOT IN (SELECT CatNO FROM posts);
RETURN v_depts;
END;
/选项 4：SQL_MACRO(TABLE)（来自 Oracle 19.7）
CREATE FUNCTION empty_cats
RETURN VARCHAR2 SQL_MACRO(TABLE) IS
BEGIN
RETURN q'{SELECT * FROM dept WHERE CatNO NOT IN (SELECT CatNO FROM posts)}';
END;
/db<>小提琴https://dbfiddle.uk/?rdbms=oracle_21&fiddle=1998b1df2e189c2cd14da95e4b338c86