ORACLE 通过TYPE声明TABLE的具体用法（另类的临时表用法）

1.情景展示

对于临时存在的数据（读完就没有存在的意义了），我们通常想到的是：使用临时表。

除了临时表，还有没有其它的实现方式呢？

2.具体分析

面向ORACLE编程，我们可以通过TYPE来实现。

3.解决方案

创建一个Object对象，用于存储表结构。

CREATE OR REPLACE TYPE T_体检人员信息 AS OBJECT
(
  体检号   VARCHAR2(50),
  姓名     VARCHAR2(100),
  性别     VARCHAR2(4),
  年龄     VARCHAR2(10),
  出生日期 VARCHAR2(10),
  身份证号 VARCHAR2(18),
  婚姻状况 VARCHAR2(20),
  职业     VARCHAR2(50),
  民族     VARCHAR2(50),
  联系电话 VARCHAR2(20) NOT NULL,
  家庭地址 VARCHAR2(100) NOT NULL,
  工作单位 VARCHAR2(100)
)

如何调用？

这是一个空对象，必须通过赋值的方式完成调用。

我们可以看到：

type为Object的T_体检人员信息可以临时存储数据。

但是，有且只能通过插入数据的方式，进行读取。

并且，原有表的字段名称会发生改变。

创建一个Table对象，用于存储表数据。

CREATE OR REPLACE TYPE T_体检人员信息1
     AS TABLE OF T_体检人员信息;

将T_体检检查人员信息1作为T_体检检查人员信息的表。

我们来验证一下：

调用此type。

我们可以看到，执行结果返回的是一个集合。

点击右侧的三个点…，可以查看具体的集合内容。

到这里，我们可以证明：

type T_体检检查人员信息1，其本质是一个空表，具体的表结构取决于：type T_体检检查人员信息。

另外，通过type声明的table，可以充当临时表，以我的理解，这是它的核心优势与特征。

（单独调用返回的是空表，通过函数或者存储过程对其赋值，就能返回数据。另外，不像普通表那样一直叠加数据，每次运行都是全新的数据，其作用可与临时表媲美）

最终实现

CREATE OR REPLACE FUNCTION FUN_GET体检检查人员信息 RETURN T_体检人员信息1 IS
  -- 也可以不对其进行初始化
  V_RETURN_TABLE T_体检人员信息1;
  -- 声明对象并初始化
  --V_RETURN_TABLE T_体检人员信息1 := T_体检人员信息1();
  
  ---函数必须返回字段 体检号,姓名,性别,年龄,出生日期,身份证号,婚姻状况,职业,民族,联系电话,家庭地址,工作单位
  ---函数检索有未读取的检验体检人员的基本信息
BEGIN

  --返回示例写法
  SELECT T_体检人员信息(A.体检号,
                  A.姓名,
                  A.性别,
                  A.年龄,
                  A.出生日期,
                  A.身份证号,
                  A.婚姻状况,
                  A.职业,
                  A.民族,
                  A.联系电话,
                  A.家庭地址,
                  A.工作单位)
    BULK COLLECT
    INTO V_RETURN_TABLE
    FROM (SELECT T.UNIQUE_NO AS 体检号,
                 T.PATIENT_NAME AS 姓名,
                 DECODE(T.SEX, 1, '男', 2, '女', '未知') AS 性别,
                 TRUNC(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DAY) / 12) AS 年龄,
                 T.BIRTH_DAY AS 出生日期,
                 T.CARD_ID AS 身份证号,
                 T.MARITAL AS 婚姻状况,
                 '' AS 职业,
                 T.NATION AS 民族,
                 T.LINKMAN_PHONE AS 联系电话,
                 T.ADDRESS AS 家庭地址,
                 '' AS 工作单位
            FROM WJ_PATIENT_INFO T, WJ_CONSULT_INFO W
           WHERE T.SQDTYPE = '1'
             AND T.SQDID = V_SQD_ID
             AND T.SQDID = W.APPLY_NO
             AND W.CONSULT_STATUS = '2') A; --SQDTYPE = '1'代表：检查
  RETURN V_RETURN_TABLE;
-- 出现异常，返回null
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END FUN_GET体检检查人员信息;

具体原理：

FROM 字段后面跟的是：数据来源（虚拟A表）。

将A表数据按列塞进Object T_体检人员信息对象当中。

然后将T_体检人员信息对象里面的数据，通过BULK COLLECT INTO批量插入到type T_体检人员信息对象1表中。

调用函数

SELECT FUN_GET体检人员信息() FROM DUAL;

 

对于type声明table的调用方式，还有另外一种方式。

可以直接像查询普通表那样进行展示。

SELECT * FROM TABLE (SELECT FUN_GET体检人员信息() FROM DUAL);

4.扩展

关于变量赋值的三种方式

第一种：单行单列

SELECT COLUMN1 INTO V_1 FROM TABLE;

只能完成单列单行数据的赋值。

第二种：单行整列

--将变成声明成行类型
V_ROW_VIRTUAL_CARD VIRTUAL_CARD%ROWTYPE;

--将整行数据塞到变量当中
SELECT T.* INTO  V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T;

--调用变量行的具体字段
V_ROW_VIRTUAL_CARD.ID

 这种整行整列的变量，必须依赖于表结构存在。

第三种：多行多列

第一步：声明将多列的具体结构。

CREATE OR REPLACE TYPE T_体检人员信息 AS OBJECT
(
  体检号   VARCHAR2(50),
  姓名     VARCHAR2(100),
  性别     VARCHAR2(4),
  年龄     VARCHAR2(10),
  出生日期 VARCHAR2(10),
  身份证号 VARCHAR2(18),
  婚姻状况 VARCHAR2(20),
  职业     VARCHAR2(50),
  民族     VARCHAR2(50),
  联系电话 VARCHAR2(20) NOT NULL,
  家庭地址 VARCHAR2(100) NOT NULL,
  工作单位 VARCHAR2(100)
)

 第二步：声明存放多列多行数据的表。

CREATE OR REPLACE TYPE T_体检人员信息1
     AS TABLE OF T_体检人员信息;

说明：

将第一步声明的Object作为第二步的表。

第三步：声明表变量并使用BULK COLLECT INTO进行批量插入。

T_RETURN T_体检人员信息1;

SELECT T_体检人员信息(A.体检号,
              A.姓名,
              A.性别,
              A.年龄,
              A.出生日期,
              A.身份证号,
              A.婚姻状况,
              A.职业,
              A.民族,
              A.联系电话,
              A.家庭地址,
              A.工作单位)
BULK COLLECT
INTO T_RETURN
FROM (SELECT T.UNIQUE_NO AS 体检号,
             T.PATIENT_NAME AS 姓名,
             DECODE(T.SEX, 1, '男', 2, '女', '未知') AS 性别,
             TRUNC(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DAY) / 12) AS 年龄,
             T.BIRTH_DAY AS 出生日期,
             T.CARD_ID AS 身份证号,
             T.MARITAL AS 婚姻状况,
             '' AS 职业,
             T.NATION AS 民族,
             T.LINKMAN_PHONE AS 联系电话,
             T.ADDRESS AS 家庭地址,
             '' AS 工作单位
        FROM WJ_PATIENT_INFO T, WJ_CONSULT_INFO W
       WHERE T.SQDTYPE = '1'
         AND T.SQDID = V_SQD_ID
         AND T.SQDID = W.APPLY_NO
         AND W.CONSULT_STATUS = '2') A;

变形（简化版）：

如果表结构刚好就是某张表的结构的话，我们可以把第一步省略掉。

直接来到第二步。

CREATE OR REPLACE TYPE T_DEMO
     AS TABLE OF TABLE_DEMO%ROWTYPE;

直接使用TABLE_DEMO表的表结构。

第三步

声明变量

V_RETURN_TABLE T_DEMO;

批量配值

SELECT * BULK COLLECT INTO V_RETURN_TABLE FROM TABLE_DEMO2;

 

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• 个人主页
• ORACLE-临时表
• oracle 遍历双重游标（动态游标）&三级关联查询&存储过程使用临时表返回游标
• oracle 存储过程 变量的声明和赋值的3种方式