新建体（1）：新建type

类似数组的类型：
TYPE TAB_TYPE_MCHNO IS TABLE OF t_r_terminal.rt_merchno%type;
tMchNo  TAB_TYPE_MCHNO;
1)集合赋值：select t.lmerno bulk collect into tMchNo from t_r_terminal.rt_merchno t;
2)单一赋值:(首先需要全局定义类型)
inPara TAB_TYPE_MCHNO := TAB_TYPE_MCHNO();      //初始化
inPara.extend(1);                               //扩展一条记录空间
inPara(iIdx) := substr(tmpPara, 1, iPos- 1);    //进行赋值

 

新建一个类型：（类似C中新建一个结构体）
create or replace type nac_user.type_fix_bind_route as object
(
  LT_TERMNO          VARCHAR2(16),
  LT_MERCHNO         VARCHAR2(16),
  RI_INTERFACE       NUMBER(4),  

  FBR_MERCHNO        VARCHAR2(16),
  FBR_TERMNO         VARCHAR2(16),
  FBR_START          DATE,
  FBR_END            DATE,
  FBR_STATUS         CHAR(1),
  SSID               VARCHAR2(16),
  OPRUSER            VARCHAR2(20),
  COVER_AMOUNT       NUMBER,
  AIM_AMOUNT         NUMBER,
  CURR_AMOUNT        NUMBER
);

 

 

新建一个类型集合：（类似C中新建一个结构体数组）

create or replace type nac_user.tab_fix_bind_route is table of nac_user.type_fix_bind_route;

 

构造函数：

tTotal       tab_fix_bind_route := tab_fix_bind_route();

 

初始化：

tTotal.extend;  //如果是一对一"="赋值需要先扩展空间

tTotal(1):=tab_fix_bind_route (NULL,NULL,NULL,NULL,NULL,NULL,NULL...);

 

进行赋值：（批量赋值不需要扩展）

SELECT type_fix_bind_route(lt_termno,

                                 lt_merchno,

                                 ri_interface,

                                 fbr_merchno,

                                 fbr_termno,

                                 fbr_start,

                                 fbr_end,

                                 fbr_status,

                                 ssid,

                                 opruser,

                                 cover_amount,

                                 aim_amount,

                                 curr_amount) BULK COLLECT

        INTO tTotal

        FROM (select *

                from........

---

判断集合是否空：

if tTotal.Count > 0 then

......

错误案例：create or replace type nac_user.tab_fix_bind_route is table of nac_user."type_fix_bind_route;"
删除一个类型：（界面无法删除的）这个地方的引号不能有
drop type nac_user."type_fix_bind_route";
drop type nac_user."tab_fix_bind_route";

 

数据集合强制转换为TABLE——集合排序
select type_fix_bind_route(lt_termno,
                           lt_merchno,
                           ri_interface,
                           fbr_merchno,
                           fbr_termno,
                           fbr_start,
                           fbr_end,
                           fbr_status,
                           ssid,
                           opruser,
                           cover_amount,
                           aim_amount,
                           curr_amount) BULK COLLECT
  into tPartAg
  from TABLE(tPart) tp
 order by (tp.CURR_AMOUNT + lAmt) desc;