首页 > 代码库 > 新建type

新建type

2024-07-20 19:03:06   221人阅读

类似数组的类型：TYPE TAB_TYPE_MCHNO IS TABLE OF t_r_terminal.rt_merchno%type;tMchNo  TAB_TYPE_MCHNO;1)集合赋值：select t.lmerno 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;

 

 

新建type


联系
我们