Oracle表值函数的两种写法

-- 创建表
DECLARE
  CNT INTEGER;
BEGIN
  SELECT COUNT(0)
    INTO CNT
    FROM USER_ALL_TABLES
   WHERE TABLE_NAME = 'CMSTEMP';
  IF CNT = 0 THEN
    EXECUTE IMMEDIATE '  CREATE TABLE CMSTEMP(
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR2(32),
  AGE INTEGER,
  ADDRESS VARCHAR2(64)
  )';
  END IF;
END;
-- 创建 type
DECLARE
  CNT INTEGER;
BEGIN
  SELECT COUNT(0) INTO CNT FROM USER_TYPES WHERE TYPE_NAME = 'CMSTEMPTYPE';
  IF CNT = 0 THEN
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE CMSTEMPTYPE AS OBJECT (
 ID INTEGER,
  NAME VARCHAR2(32),
  AGE INTEGER,
  ADDRESS VARCHAR2(64)
)';
  END IF;
END;
-- 创建type 表
DECLARE
CNT INTEGER;
BEGIN
  SELECT COUNT(0) INTO CNT FROM USER_TYPES WHERE TYPE_NAME = 'CMSTEMPTYPETABLE';
  IF CNT = 0 THEN
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE CMSTEMPTYPETABLE AS TABLE OF CMSTEMPTYPE ' ;
    END IF;
  END;
-- 创建临时表
DECLARE
  CNT INTEGER;
BEGIN
  SELECT COUNT(0)
    INTO CNT
    FROM USER_ALL_TABLES
   WHERE TABLE_NAME = 'CMSTEMPGLO';
  IF CNT = 0 THEN
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE CMSTEMPGLO(
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR2(32),
  AGE INTEGER,
  ADDRESS VARCHAR2(64)
  )
  ON COMMIT PRESERVE ROWS';
  END IF;
END;
--创建包
CREATE OR REPLACE PACKAGE GLOBALPACKGE
AS
TYPE CUR1 IS REF CURSOR;
END;

第一种方式使用临时表

CREATE OR REPLACE FUNCTION NS_CMS_GETCMSTEM(NAME VARCHAR2)
  RETURN CMSTEMPTYPETABLE
  PIPELINED IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_NAME    VARCHAR2(32);
  V_AGE     INTEGER;
  V_ADDRESS VARCHAR2(64);
  V_ID      INTEGER;
  RWS       CMSTEMPTYPE := CMSTEMPTYPE(NULL, NULL, NULL, NULL);
  V_CUR     GLOBALPACKGE.CUR1;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE CMSTEMPGLO';
  FOR REC IN (SELECT * FROM CMSTEMP) LOOP
    V_NAME    := REC.NAME;
    V_AGE     := REC.AGE;
    V_ID      := REC.ID;
    V_ADDRESS := REC.ADDRESS;
    INSERT INTO CMSTEMPGLO
      (ID, ADDRESS, AGE, NAME)
    VALUES
      (V_ID, V_ADDRESS, V_AGE, V_NAME);
      DBMS_OUTPUT.PUT_LINE('11111111111');
  END LOOP;
  COMMIT;
  OPEN V_CUR FOR
    SELECT * FROM CMSTEMPGLO;
  LOOP
    FETCH V_CUR
      INTO RWS.ID,RWS.NAME,RWS.AGE,RWS.ADDRESS;
    EXIT WHEN V_CUR%NOTFOUND;
    PIPE ROW(RWS);
  END LOOP;
  CLOSE V_CUR;
END;

第二种方式不适用临时表

CREATE OR REPLACE FUNCTION NS_CMS_GETCMSTEMP(NAME VARCHAR2)
  RETURN CMSTEMPTYPETABLE
  PIPELINED AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_CUR     GLOBALPACKGE.CUR1;
  V_NAME    VARCHAR2(32);
  V_ADDRESS VARCHAR2(64);
  V_AGE     INTEGER;
  V_ID      INTEGER;
  RET_ROW   CMSTEMPTYPE := CMSTEMPTYPE(NULL, NULL, NULL, NULL);
  RET_TABLE CMSTEMPTYPETABLE := CMSTEMPTYPETABLE();  -- 这里必须这样写
BEGIN
  FOR REC IN (SELECT * FROM CMSTEMP) LOOP
    RET_TABLE.EXTEND;
    RET_ROW.ID      := REC.ID;
    RET_ROW.AGE     := REC.AGE;
    RET_ROW.NAME    := REC.NAME;
    RET_ROW.ADDRESS := REC.ADDRESS;
    RET_TABLE(RET_TABLE.COUNT) := RET_ROW;
  END LOOP;
  COMMIT;
  open V_CUR for
  select * from table(RET_TABLE);
  loop
    fetch V_CUR into RET_ROW.ID,RET_ROW.NAME,RET_ROW.AGE,RET_ROW.ADDRESS; --顺序要和type对象里定义的顺序保持一致
    exit when V_CUR%notfound;
    pipe row(RET_ROW);
    end loop;
    close v_cur;
END;

插入数据

<pre class="sql" name="code">declare
i integer;
begin
  for i in 1 ..100 loop
    insert into cmstemp values (i,'zhangsan'||i,1+i,'王府大街'||i);
    commit;
    end loop;
  end;

查询sql

<pre class="sql" name="code">select * from table(ns_cms_getcmstemp('')) ;
select * from table(NS_CMS_GETCMSTEM('')) ;