oracle 表值函数,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;

插入数据

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

select * from table(ns_cms_getcmstemp('')) ;

select * from table(NS_CMS_GETCMSTEM('')) ;