informix LIST



/***********************************************************************/
 informix LIST 练习
/***********************************************************************/
1 list 包含一列的数据

create function get_list()
returning  list(int not null )
 define v_list list(int not null );

 define v_id int;
 define v_count int;
 define i int;
 /*
 SET DEBUG FILE TO '/home/informix/info_debug.txt';
 TRACE 'BEGIN TRACE' ;
 trace on;
 */

 let v_list=list{};

 FOR i = 1 TO 10 
  insert into table(v_list) values(i);
 END FOR;

 return v_list;
end function;
 

--测试
> select * from table(get_list());
>  select * from table(get_list());

unnamed_col_1

            1
            2
            3
            4
            5
            6
            7
            8
            9
           10

10 row(s) retrieved.

Elapsed time: 0.004 sec

2 LIST 包含多列数据，需要设置ROW TYPE

create row type rt_list(id int, ccount int);

drop function if exists   get_list2();
create function get_list2()
returning  list(rt_list not null   )
 define v_list list(rt_list not null );
 define v_rt_list rt_list;

  define i int;
 /*
 SET DEBUG FILE TO '/home/informix/info_debug.txt';
 TRACE 'BEGIN TRACE' ;
 trace on;
 */

 let v_list=list{};

 FOR i = 1 TO 10 
  insert into table(v_list) values(row(i,i*2)::rt_list);
 END FOR;

 return v_list;
end function;

>  select * from table(get_list2());
        id      ccount

          1           2
          2           4
          3           6
          4           8
          5          10
          6          12
          7          14
          8          16
          9          18
         10          20

10 row(s) retrieved.

Elapsed time: 0.005 sec

/************************************************************************************************/
 将ORACLE 中含有 对象类型的数据表导入到8T 中LIST 字段表中（　LOSS_DEF_TABLE_CELL_CAL）
/************************************************************************************************/

一 ORACLE 端
1 oracle 端 表结构
CREATE OR REPLACE TYPE "CALSRCCELL"  
AS OBJECT(x NUMBER(5), y NUMBER(5));
/
CREATE OR REPLACE TYPE CALSRCLIST  AS VARRAY (256) OF CALSRCCELL;
/

create table LOSS_DEF_TABLE_CELL_CAL
(
  tab_ccal    VARCHAR2(128) not null,
  row_ccal    INTEGER default 0 not null,
  col_ccal    INTEGER default 0 not null,
  srclst_ccal CALSRCLIST,
  parse_ccal  VARCHAR2(1024) default 'null' not null,
  info_ccal   VARCHAR2(1024) default 'null' not null
)
;

alter table LOSS_DEF_TABLE_CELL_CAL
  add primary key (TAB_CCAL, ROW_CCAL, COL_CCAL);

2 模拟数据
truncate table LOSS_DEF_TABLE_CELL_CAL;
insert into LOSS_DEF_TABLE_CELL_CAL values('111',1,1,CALSRCLIST(CALSRCCELL(1,1),CALSRCCELL(11,11),CALSRCCELL(111,111)),'111','1111');
insert into LOSS_DEF_TABLE_CELL_CAL values('112',2,2,CALSRCLIST(CALSRCCELL(2,2),CALSRCCELL(22,22),CALSRCCELL(222,222)),'222','2222');
insert into LOSS_DEF_TABLE_CELL_CAL values('113',3,3,CALSRCLIST(CALSRCCELL(3,3),CALSRCCELL(13,11),CALSRCCELL(111,111),CALSRCCELL(111,111)),'111','1111');
insert into LOSS_DEF_TABLE_CELL_CAL values('114',4,4,CALSRCLIST(CALSRCCELL(4,44),CALSRCCELL(14,11),CALSRCCELL(111,111),CALSRCCELL(111,111),CALSRCCELL(111,111)),'111','1111');
insert into LOSS_DEF_TABLE_CELL_CAL values('115',5,5,null,'111','1111');
insert into LOSS_DEF_TABLE_CELL_CAL values('116',6,6,null,'111','1111');
insert into LOSS_DEF_TABLE_CELL_CAL values('117',7,7,CALSRCLIST(CALSRCCELL(7,77),CALSRCCELL(17,11),CALSRCCELL(181,111)),'111','1111');

insert into LOSS_DEF_TABLE_CELL_CAL values('118',8,8,CALSRCLIST(CALSRCCELL(8,88),CALSRCCELL(888,null),CALSRCCELL(null,8888)),'111','1111');

insert into LOSS_DEF_TABLE_CELL_CAL values('119',9,9,CALSRCLIST(CALSRCCELL(9,99),CALSRCCELL(99,null)),'111','1111');
insert into LOSS_DEF_TABLE_CELL_CAL values('1191',91,91,CALSRCLIST(CALSRCCELL(91,991),CALSRCCELL(null,99991)),'111','1111');

> select * from LOSS_DEF_TABLE_CELL_CAL;
TAB_CCAL
--------------------------------------------------------------------------------
  ROW_CCAL   COL_CCAL
---------- ----------
SRCLST_CCAL(X, Y)
--------------------------------------------------------------------------------
PARSE_CCAL
--------------------------------------------------------------------------------
INFO_CCAL
--------------------------------------------------------------------------------
111
  1     1
CALSRCLIST(CALSRCCELL(1, 1), CALSRCCELL(11, 11), CALSRCCELL(111, 111))
111
1111

112
  2     2
CALSRCLIST(CALSRCCELL(2, 2), CALSRCCELL(22, 22), CALSRCCELL(222, 222))
222
2222

113
  3     3
CALSRCLIST(CALSRCCELL(3, 3), CALSRCCELL(13, 11), CALSRCCELL(111, 111), CALSRCCEL
L(111, 111))
111
1111

114
  4     4
CALSRCLIST(CALSRCCELL(4, 44), CALSRCCELL(14, 11), CALSRCCELL(111, 111), CALSRCCE
LL(111, 111), CALSRCCELL(111, 111))
111
1111

115
  5     5

111
1111

116
  6     6

111
1111

117
  7     7
CALSRCLIST(CALSRCCELL(7, 77), CALSRCCELL(17, 11), CALSRCCELL(181, 111))
111
1111

118
  8     8
CALSRCLIST(CALSRCCELL(8, 88), CALSRCCELL(888, NULL), CALSRCCELL(NULL, 8888))
111
1111

119
  9     9
CALSRCLIST(CALSRCCELL(9, 99), CALSRCCELL(99, NULL))
111
1111

1191
 91    91
CALSRCLIST(CALSRCCELL(91, 991), CALSRCCELL(NULL, 99991))
111
1111

10 rows selected.

3 oracle 端导出数据
1) 创建中间表
create table SRCLST_CCAL
(
  tab_ccal    VARCHAR2(128) not null,
  row_ccal    INTEGER default 0 not null,
  col_ccal    INTEGER default 0 not null,
  x NUMBER(5),
  y NUMBER(5)
)
;

create table LOSS_DEF_TABLE_CELL_CAL_TEMP
(
  tab_ccal    VARCHAR2(128) not null,
  row_ccal    INTEGER default 0 not null,
  col_ccal    INTEGER default 0 not null,
  parse_ccal  VARCHAR2(1024) default 'null' not null,
  info_ccal   VARCHAR2(1024) default 'null' not null
)
;

2） 创建存储过程 将数据 导入到 中间表
create or replace procedure get_srclst_ccal
      is
 v_tab_ccal varchar2(128);
 v_row_ccal int;
 v_col_ccal int;
        v_srclst_ccal CALSRCLIST;
 v_x int ;
 v_y int;
 v_CALSRCCELL CALSRCCELL;

 begin

 delete from   SRCLST_CCAL;
 delete from LOSS_DEF_TABLE_CELL_CAL_TEMP;

 for v_tab in(select tab_ccal,row_ccal ,col_ccal,srclst_ccal,parse_ccal,info_ccal from LOSS_DEF_TABLE_CELL_CAL) loop
  v_tab_ccal:=v_tab.tab_ccal;
  v_row_ccal:=v_tab.row_ccal;
  v_col_ccal:=v_tab.col_ccal;
  v_srclst_ccal:=v_tab.srclst_ccal;

  dbms_output.put_line('v_tab_ccal :'||v_tab_ccal);
  dbms_output.put_line('v_row_ccal :'||v_row_ccal);
  dbms_output.put_line('v_col_ccal :'||v_col_ccal);

        
  if v_srclst_ccal is not null then
        FOR v_count IN v_srclst_ccal.FIRST .. v_srclst_ccal.LAST
         LOOP
    v_x:=v_srclst_ccal(v_count).x;
    v_y:=v_srclst_ccal(v_count).y;
    insert into SRCLST_CCAL values(v_tab_ccal,v_row_ccal,v_col_ccal,v_x,v_y);
    dbms_output.put_line('                              v_x :'||v_x);
    dbms_output.put_line('                              v_y :'||v_y);
         END LOOP;
  else
   insert into SRCLST_CCAL(tab_ccal,row_ccal,col_ccal) values(v_tab_ccal,v_row_ccal,v_col_ccal);
  end if;

 end loop;

 insert into LOSS_DEF_TABLE_CELL_CAL_TEMP select tab_ccal,row_ccal,col_ccal,parse_ccal,info_ccal from LOSS_DEF_TABLE_CELL_CAL;
 commit;
end;
/

sql> set serveroutput on

--运行存储过程
sql> exec  get_srclst_ccal();

SQL> select count(1) from SRCLST_CCAL;

  COUNT(1)
----------
 27
SQL> select count(1) from LOSS_DEF_TABLE_CELL_CAL_TEMP;

  COUNT(1)
----------
  10

二 8T 端
1 8T 端表结构

create row type CALSRCCELL(
    x INTEGER,
    y INTEGER
);

CREATE TABLE LOSS_DEF_TABLE_CELL_CAL(
    tab_ccal VARCHAR(128) NOT NULL,
    row_ccal DECIMAL(32,0) DEFAULT 0 NOT NULL,
    col_ccal DECIMAL(32,0) DEFAULT 0 NOT NULL,
    srclst_ccal list(CALSRCCELL not null),
    parse_ccal LVARCHAR(1024) DEFAULT 'null' NOT NULL,
    info_ccal LVARCHAR(1024) DEFAULT 'null' NOT NULL
);

ALTER TABLE LOSS_DEF_TABLE_CELL_CAL
 ADD CONSTRAINT  PRIMARY KEY(TAB_CCAL,ROW_CCAL,COL_CCAL) CONSTRAINT SYS_C00106311;

2 8T 端导入数据
1) 创建中间表

CREATE TABLE srclst_ccal(
    tab_ccal VARCHAR(128) NOT NULL,
    row_ccal DECIMAL(32,0) DEFAULT 0 NOT NULL,
    col_ccal DECIMAL(32,0) DEFAULT 0 NOT NULL,
    x INTEGER,
    y INTEGER
);

CREATE TABLE LOSS_DEF_TABLE_CELL_CAL_TEMP(
    tab_ccal VARCHAR(128) NOT NULL,
    row_ccal DECIMAL(32,0) DEFAULT 0 NOT NULL,
    col_ccal DECIMAL(32,0) DEFAULT 0 NOT NULL,
    parse_ccal LVARCHAR(1024) DEFAULT 'null' NOT NULL,
    info_ccal LVARCHAR(1024) DEFAULT 'null' NOT NULL
);

2)  通过KETTLE 将数据导入到INFORMIX 端
select TABLE_NAME from user_tables  where table_name  in('LOSS_DEF_TABLE_CELL_CAL_TEMP','SRCLST_CCAL')

3） 创建存储过程 将数据从中间表导入到 正式表
drop procedure if exists  set_srclst_ccal;
create  procedure set_srclst_ccal()

     define v_tab_ccal varchar(128);
     define v_row_ccal   int;
     define v_col_ccal   int;
     define v_srclst_ccal list(CALSRCCELL not null);
     define v_CALSRCCELL CALSRCCELL;
     define v_x int;
     define v_y int;
     define v_parse_ccal lvarchar(1024);
     define v_info_ccal  lvarchar(1024);
     define v_count int;

     --1
     delete from LOSS_DEF_TABLE_CELL_CAL;
     FOREACH  select tab_ccal,row_ccal,col_ccal,parse_ccal,info_ccal  into v_tab_ccal, v_row_ccal,v_col_ccal, v_parse_ccal,v_info_ccal from LOSS_DEF_TABLE_CELL_CAL_TEMP
     
      let v_srclst_ccal=list{};
      select count(1) into v_count from srclst_ccal where tab_ccal=v_tab_ccal and row_ccal=v_row_ccal and col_ccal=v_col_ccal and (x is not null or y is not null);
     
      if v_count >0 then
       FOREACH select x,y into v_x,v_y from srclst_ccal where tab_ccal=v_tab_ccal and row_ccal=v_row_ccal and col_ccal=v_col_ccal and (x is not null or y is not null)
    let v_CALSRCCELL=row(v_x,v_y)::CALSRCCELL;
    insert into table(v_srclst_ccal) values(v_CALSRCCELL);
       END FOREACH
       insert into LOSS_DEF_TABLE_CELL_CAL(tab_ccal,row_ccal,col_ccal,srclst_ccal,parse_ccal,info_ccal) values (v_tab_ccal,v_row_ccal,v_col_ccal,v_srclst_ccal,v_parse_ccal,v_info_ccal);
      else
       insert into LOSS_DEF_TABLE_CELL_CAL(tab_ccal,row_ccal,col_ccal,parse_ccal,info_ccal) values (v_tab_ccal,v_row_ccal,v_col_ccal,v_parse_ccal,v_info_ccal);
          end if;
     END FOREACH

end procedure;

--测试
> execute procedure set_srclst_ccal() ;

> select count(1) from LOSS_DEF_TABLE_CELL_CAL;
         (count)

               10

1 row(s) retrieved.

> select * from LOSS_DEF_TABLE_CELL_CAL;
tab_ccal     111
row_ccal     1
col_ccal     1
srclst_ccal  LIST{ROW(1          ,1          ),ROW(11         ,11         ),ROW
             (111        ,111        )}
parse_ccal   111
info_ccal    1111

tab_ccal     112
row_ccal     2
col_ccal     2
srclst_ccal  LIST{ROW(2          ,2          ),ROW(22         ,22         ),ROW
             (222        ,222        )}
parse_ccal   222
info_ccal    2222

tab_ccal     113
row_ccal     3
col_ccal     3
srclst_ccal  LIST{ROW(3          ,3          ),ROW(13         ,11         ),ROW
             (111        ,111        ),ROW(111        ,111        )}
parse_ccal   111
info_ccal    1111

tab_ccal     114
row_ccal     4
col_ccal     4
srclst_ccal  LIST{ROW(4          ,44         ),ROW(14         ,11         ),ROW
             (111        ,111        ),ROW(111        ,111        ),ROW(111   
                 ,111        )}
parse_ccal   111
info_ccal    1111

tab_ccal     115
row_ccal     5
col_ccal     5
srclst_ccal  
parse_ccal   111
info_ccal    1111

tab_ccal     116
row_ccal     6
col_ccal     6
srclst_ccal  
parse_ccal   111
info_ccal    1111

tab_ccal     117
row_ccal     7
col_ccal     7
srclst_ccal  LIST{ROW(7          ,77         ),ROW(17         ,11         ),ROW
             (181        ,111        )}
parse_ccal   111
info_ccal    1111

tab_ccal     118
row_ccal     8
col_ccal     8
srclst_ccal  LIST{ROW(8          ,88         ),ROW(888        ,NULL),ROW(NULL,8
             888       )}
parse_ccal   111
info_ccal    1111

tab_ccal     119
row_ccal     9
col_ccal     9
srclst_ccal  LIST{ROW(9          ,99         ),ROW(99         ,NULL)}
parse_ccal   111
info_ccal    1111

tab_ccal     1191
row_ccal     91
col_ccal     91
srclst_ccal  LIST{ROW(91         ,991        ),ROW(NULL,99991      )}
parse_ccal   111
info_ccal    1111

10 row(s) retrieved.