Oracle-Func-Table-1

Oracle 函数形式的表

前言

你是否有看过下面这种形式的查询 SQL 呢？

SELECT * FROM TABLE (FUNC_AAA());

这种方式的查询语句速度比你单独写 SQL 或者视图还要快，因为函数会预先编译完成，函数的执行效率原本也是比视图快很多。

案例

定义 TYPE 类型 以及 TYPE 表

-- 创建 TYPE 类型
CREATE OR REPLACE TYPE SEVATTAL_DEMO_TYPE AS OBJECT (
ID VARCHAR2(50) NULL,
NAME VARCHAR2(50) NULL
);
/

CREATE OR REPLACE TYPE SEVATTAL_DEMO_TABLE AS  TABLE OF SEVATTAL_DEMO_TYPE;
/

函数编辑

CREATE OR REPLACE FUNCTION SEVATTAL_DEMO_FUNC
RETURN 
    SEVATTAL_DEMO_TABLE PIPELINED
IS
    PRAGMA AUTONOMOUS_TRANSACTION; 
    -- 返回值游标
    TYPE REF0 IS REF CURSOR; 
    MYCURSOR REF0;
    OUT_REC  SEVATTAL_DEMO_TYPE := SEVATTAL_DEMO_TYPE(
     NULL,NULL
    );
BEGIN
    -- 游标方式第一次插入
    OPEN MYCURSOR FOR
    SELECT  '1' ,'张三' FROM DUAL
    UNION
    SELECT  '2' ,'李四' FROM DUAL;

    LOOP FETCH MYCURSOR INTO 
	    out_rec.ID,
	    out_rec.NAME;

	    EXIT WHEN MYCURSOR%NOTFOUND;
	    PIPE ROW(out_rec);
    END LOOP;
    CLOSE MYCURSOR;

    -- 游标方式第二次插入
    OPEN MYCURSOR FOR 
    SELECT  '3' ,'王五' FROM DUAL
    UNION
    SELECT  '4' ,'赵四' FROM DUAL;

    LOOP FETCH MYCURSOR INTO 
	    out_rec.ID,
	    out_rec.NAME;

	    EXIT WHEN MYCURSOR%NOTFOUND;
	    PIPE ROW(out_rec);
    END LOOP;
    CLOSE MYCURSOR;

    -- 不适用游标,单独插入
    SELECT 
    	'5','SEVATTAL' INTO 
    	out_rec.ID,out_rec.NAME 
	FROM DUAL;
	PIPE ROW(out_rec);

    RETURN;

END;

查询语句

SELECT * FROM TABLE (SEVATTAL_DEMO_FUNC())