informix-通过系统视图查询

/**************************************************************************************/
informix-通过系统视图查询
/**************************************************************************************/
--1 GLOBAL_MULTYEAR_CZ.sp_get_ind_con2  
--功能:取得索引列的 组合 串  
/*------------------------------------------------------------------------------------*/
drop function if exists GLOBAL_MULTYEAR_CZ.sp_get_ind_con2;
create function GLOBAL_MULTYEAR_CZ.sp_get_ind_con2(v_tabname varchar(30),v_IndName varchar(30))
returning lvarchar(32739);
--索引中引用的 列
define idx_var    smallint;
define idx_v1    smallint;
define idx_v2       smallint;
define idx_v3       smallint;
define idx_v4       smallint;
define idx_v5       smallint;
define idx_v6       smallint;
define idx_v7       smallint;
define idx_v8       smallint;
define idx_v9       smallint;
define idx_v10      smallint;
define idx_v11      smallint;
define idx_v12      smallint;
define idx_v13      smallint;
define idx_v14      smallint;
define idx_v15      smallint;
define idx_v16      smallint;
define v_ind_cont   lvarchar(32739);
  define idx_cur_col varchar(64);
define esql int;
define eisam int;


let v_ind_cont='';
--取得列的 编号 
FOREACH select part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,
part11,part12,part13,part14,part15,part16
into idx_v1,idx_v2,idx_v3,idx_v4,idx_v5,idx_v6,idx_v7,idx_v8,
idx_v9,idx_v10,idx_v11,idx_v12,idx_v13,idx_v14,idx_v15,idx_v16
from SYSINDEXES s1,systables s2
where s1.tabid=s2.tabid and s2.tabname=v_tabname and trim(s1.idxname)=trim(v_IndName)
END FOREACH
 
--组合 列 
FOR idx_var in (idx_v1,idx_v2,idx_v3,idx_v4,idx_v5,idx_v6,idx_v7,idx_v8,idx_v9,idx_v10,idx_v11,idx_v12,idx_v13,idx_v14,idx_v15,idx_v16)
SELECT c.colname INTO idx_cur_col FROM syscolumns c,systables t 
where c.tabid = t.tabid and  t.tabname = v_tabname and  c.colno = idx_var  ;
IF idx_cur_col IS NOT NULL THEN
LET v_ind_cont=v_ind_cont||','||idx_cur_col;
END IF;
END FOR;
let v_ind_cont=substr(v_ind_cont,2);
return v_ind_cont;
end function;


--测试
drop  table t100;
create table t100 ( a int ,b int ,c int ,d int,e int,YEAR char(4),PROVINCE varchar(30));


create index idx_t100_1 on t100(a,b);
create index idx_t100_2 on t100(a,b,c);
create index idx_t100_3 on t100(a,b,c,d);
create index idx_t100_4 on t100(a,b,YEAR,c);
create index idx_t100_5 on t100(a,b,YEAR,PROVINCE,d);
create index idx_t100_6 on t100(a,b,e,c,PROVINCE);
create index idx_t100_7 on t100(a,b,e,c,d,PROVINCE);
create index idx_t100_8 on t100(YEAR,PROVINCE);




select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_1') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_2') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_3') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_4') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_5') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_6') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_7') from sysmaster:sysdual;
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2('t100','idx_t100_8') from sysmaster:sysdual;


/**************************************************************************************/
--2 GLOBAL_MULTYEAR_CZ.sp_get_primary_key  
--功能: 取得主键 列的 组合 串
/*------------------------------------------------------------------------------------*/
drop function if exists GLOBAL_MULTYEAR_CZ.sp_get_primary_key;
create function GLOBAL_MULTYEAR_CZ.sp_get_primary_key(v_tabname varchar(30) )
returning lvarchar(32739);
define v_idxname varchar(30);
define v_idx_con lvarchar(32739);
select idxname into v_idxname from SYSCONSTRAINTS s1,systables s2 where s1.tabid=s2.tabid and s2.tabname=v_tabname and constrtype='P';
select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2(v_tabname,v_idxname) into v_idx_con from sysmaster:sysdual;
return v_idx_con;
end function;


--测试
drop table a107;
CREATE TABLE a107(a107 INT not null , b107 INT, c INT ,d int ,e int,f int ,YEAR char(4),PROVINCE varchar(30));
alter table a107 add CONSTRAINT primary key(a107,b107) ;


> select GLOBAL_MULTYEAR_CZ.sp_get_primary_key('a107') from sysmaster:sysdual;
(expression)  a107,b107 


1 row(s) retrieved.


/**************************************************************************************/
--3 GLOBAL_MULTYEAR_CZ.sp_get_unique  
--功能: 取得 唯一键 的 组合 串
/*------------------------------------------------------------------------------------*/
drop function if exists GLOBAL_MULTYEAR_CZ.sp_get_unique;
create function GLOBAL_MULTYEAR_CZ.sp_get_unique(v_tabname varchar(30))
returning lvarchar(32739);
define v_constraint_name varchar(30);
define v_idxname varchar(30);
define v_tmp lvarchar(32739);
define v_sql lvarchar(32739);
define v_idx_con lvarchar(32739);
define v_ret  lvarchar(32739);


let v_ret ='';
FOREACH  select constrname,idxname into v_constraint_name,v_idxname 
from SYSCONSTRAINTS s1,systables s2 where s1.tabid=s2.tabid and s2.tabname=v_tabname and constrtype='U'


select   GLOBAL_MULTYEAR_CZ.sp_get_ind_con2(v_tabname,v_idxname) into v_idx_con from sysmaster:sysdual;


let v_ret =v_ret||'v_constraint_name is '||v_constraint_name||' v_idx_con is :'||v_idx_con||';'||chr(10);
END FOREACH
let v_ret=substr(v_ret,1,length(v_ret)-1);
return v_ret;
end function;


--测试
drop table a107;
CREATE TABLE a107(a107 INT not null , b107 INT, c INT ,d int ,e int,f int ,YEAR char(4),PROVINCE varchar(30));
alter table a107 add  CONSTRAINT unique(d,e,f) ;
alter table a107 add  CONSTRAINT unique(a107,b107) ;


> select GLOBAL_MULTYEAR_CZ.sp_get_unique('a107') from sysmaster:sysdual;


(expression)  v_constraint_name is u4034_15589 v_idx_con is :d,e,f;
v_constrain
              t_name is u4034_15590 v_idx_con is :a107,b107 


/***************************************************************************/
--4 GLOBAL_MULTYEAR_CZ.sp_get_check   
--功能:取得一个表的 check约束　 
/*--------------------------------------------------------------------------*/
drop function if exists GLOBAL_MULTYEAR_CZ.sp_get_check;
create function GLOBAL_MULTYEAR_CZ.sp_get_check(v_tabname varchar(30))
returning lvarchar(32739);
define v_constraint_name varchar(30);
define v_check_condition lvarchar(32739);
define v_tmp lvarchar(32739);
define v_sql lvarchar(32739);
define v_check varchar(30);
define v_ret lvarchar(32739);
let v_ret='';
FOREACH  select constrname into v_constraint_name from SYSCONSTRAINTS s1,systables s2 where s1.tabid=s2.tabid and s2.tabname=v_tabname and constrtype='C'
let v_check_condition='';
FOREACH select s3.checktext into  v_check from SYSCONSTRAINTS s1, SYSCHECKS s3 where s1.constrid=s3.constrid  and s1.constrname=v_constraint_name   and s3.type='T' order by s3.seqno
let v_check_condition='v_constraint_name is: '||v_constraint_name||' v_check_condition is :'||v_check_condition||v_check;
END FOREACH
let v_ret =v_ret||v_check_condition||';'||chr(10);
END FOREACH
let v_ret=substr(v_ret,1,length(v_ret)-1);
return v_ret;


end function;
 
--测试
drop  table a9;
create table a9 ( a int ,b int ,c int ,d int,e int,YEAR char(4),PROVINCE varchar(30));
ALTER TABLE a9 ADD CONSTRAINT CHECK  ((d > 20 ) AND (e = 10 ) ) CONSTRAINT chk_a9_11;
ALTER TABLE a9 ADD CONSTRAINT CHECK ((d > 20 ) AND (e = 10 ) ) CONSTRAINT chk_a9_12;


ALTER TABLE a9 ADD CONSTRAINT CHECK ((d > 200000 ) AND (e = 100000 ) and PROVINCE='123123123123123123123123') CONSTRAINT chk_a9_3;


CREATE TABLE   a9 (a INTEGER,b INTEGER,c INTEGER, d INTEGER,e INTEGER,YEAR CHAR(4),province VARCHAR(30),
 CHECK ((d > 20 )AND (e = 10 ) ) CONSTRAINT chk_a9_11,
 CHECK ((d > 20 )AND (e = 10 ) ) CONSTRAINT chk_a9_12,
 CHECK (((d > 200000 )AND (e = 100000 ) )AND (province = '123123123123123123123123' ) ) CONSTRAINT chk_a9_3 );


 > select GLOBAL_MULTYEAR_CZ.sp_get_check('a9') from sysmaster:sysdual;
(expression)  v_constraint_name is: chk_a9_11 v_check_condition is :((d > 20 ) 
              AND (e = 10 ) )    ;
v_constraint_name is: chk_a9_12 v_check_cond
              ition is :((d > 20 ) AND (e = 10 ) )    ;
v_constraint_name is: c
              hk_a9_3 v_check_condition is :v_constraint_name is: chk_a9_3 v_ch
              eck_condition is :v_constraint_name is: chk_a9_3 v_check_conditio
              n is :(((d > 200000 ) AND (e = 10000) ) AND (province = '12312312
              33123123123123' ) )             


1 row(s) retrieved.


 /***************************************************************************/
--5 GLOBAL_MULTYEAR_CZ.sp_get_foreign   
--功能:取得一个表的 外键 约束　 
/*--------------------------------------------------------------------------*/
drop function if exists GLOBAL_MULTYEAR_CZ.sp_get_foreign;
create function GLOBAL_MULTYEAR_CZ.sp_get_foreign(v_oldname varchar(30))
returning lvarchar(32739);
define  v_constrid int;
define  v_constrname varchar(30);
define  v_tabname varchar(30);
define  v_delrule char(1);---- C = Cascading delete  R = Restrict (default)
define  v_primary int;  --引用表 主键 对应的 索引 编号 
define  v_idxname_ref varchar(30);
define  v_idxname varchar(30);
define  v_rule varchar(30); 
define  v_sql lvarchar(32739);
define  v_tmp lvarchar(32739);
define  v_idx_con_ref lvarchar(32739);
define  v_idx_con lvarchar(32739);
define v_ret  lvarchar(32739);
let v_ret='';


--1 查询指定表的外键的ID ,外键名称  ,索引名, 引用表名  ,外键使用规则  ,引用键ID 
FOREACH select s1.constrid,s1.constrname,s1.idxname,s3.tabname ,s2.delrule,s2.primary  into v_constrid,v_constrname,v_idxname,v_tabname,v_delrule,v_primary
from SYSCONSTRAINTS s1,SYSREFERENCES s2,systables s3
where s1.constrid= s2.constrid and s3.tabid=s2.ptabid
and s1.constrname in (select s1.constrname from SYSCONSTRAINTS s1,systables s2 where s1.tabid=s2.tabid and s2.tabname=v_oldname and s1.constrtype='R')
  
--2 取得 本 表外键的 列组合 
select  GLOBAL_MULTYEAR_CZ.sp_get_ind_con2(v_oldname,v_idxname) into v_idx_con from sysmaster:sysdual;


--3 取得引用表 索引名 constrid 取自上面的 primary
 
select idxname into v_idxname_ref from  SYSCONSTRAINTS where constrid =v_primary; 

--4 根据表名，索引名取 引用 表 列组合 
select  GLOBAL_MULTYEAR_CZ.sp_get_ind_con2(v_tabname,v_idxname_ref) into v_idx_con_ref from sysmaster:sysdual;
let v_rule='';


--5 组合外键 SQL  
if v_delrule ='C' then  
let v_rule=' on delete cascade';
end if;
 
/*
let v_sql='alter table '||v_newName||' add constraint (FOREIGN KEY ('||v_idx_con||') references '||v_tabname||'('||v_idx_con_ref||') CONSTRAINT '|| v_constrname||v_rule||' )';
let v_tmp='alter table '||v_tabname||' drop constraint '|| v_constrname;
*/
      
let v_ret=v_ret||'v_constrname is:'||v_constrname||' v_idx_con is :'||v_idx_con||'  v_idx_con_ref is :'||v_idx_con_ref||'   v_rule is: '||v_rule||';'||chr(10);
END FOREACH
let v_ret=substr(v_ret,1,length(v_ret)-1);
return  v_ret;
end function;


--测试 


1 创建 时没有 指定 CONSTRAINT NAME, 系统自动指定 的 ，指定 on delete cascade
drop table a400;
drop table a401;
drop table a402;
drop table a401bak;


create table a402(a int primary key ,b int,c int,d int);
create table a400(a int primary key ,b int,c int,d int);


create table a401(a int ,
b int  REFERENCES a400(a),
c int REFERENCES a402(a),
d int
);


> select GLOBAL_MULTYEAR_CZ.sp_get_foreign('a401' )  from sysmaster:sysdual;


(expression)  v_constrname is:r4039_15599 v_idx_con is :b  v_idx_con_ref is :a 
                v_rule is: ;
v_constrname is:r4039_15600 v_idx_con is :c  v_idx
              _con_ref is :a   v_rule is:  


1 row(s) retrieved.