迁移过程中经常遇到某些客户希望通过类似ORACLE中国DBA_XXX或ALL_XXX这类全局视图来获取所有表及字段信息的情况,在GBase中默认是按库进行划分,虽然有少量全局信息(systabnames等)但并不够灵活,所以可以尝试通过TABLE函数和UDR来进行替代,构造出需要的全局视图,由于仍然存在某些不完善的地方,在此仅作为一种思考来进行讨论
drop function if exists all_tables;
create function all_tables()
returns char(100) as dbname,char(100) as tabname,int as partnum;
define stmt char(200);
define tmp_sql char(20000);
define v_dbname char(100);
define v_tabname char(100);
define v_owner char(100);
define v_partnum int;
let stmt="select dbs_dbsname from sysmaster:sysdbslocale where dbs_dbsname=dbinfo('DBNAME')";
let tmp_sql="";
prepare p from stmt;
declare c cursor for p;
open c;
while(1=1)
fetch c into v_dbname;
if (sqlcode != 100) then
if(tmp_sql="") then
let tmp_sql="select '"||trim(v_dbname)||"',tabname,partnum from "||trim(v_dbname)||":systables where tabid>99";
else
let tmp_sql=trim(tmp_sql)||" union all select '"||trim(v_dbname)||"',tabname,partnum from "||trim(v_dbname)||":systables where tabid>99";
end if;
else
exit;
end if;
end while;
prepare p1 from tmp_sql;
declare c1 cursor for p1;
open c1;
while(1=1)
fetch c1 into v_owner,v_tabname,v_partnum;
if (sqlcode != 100) then
return v_owner,v_tabname,v_partnum with resume;
else
exit;
end if;
end while;
end function;
drop view if exists all_tables;
create view all_tables (owner,tabname,object_id) as
select unnamed_col_1 owner,unnamed_col_2 tabname,unnamed_col_3 object_id from table(all_tables());
上述代码可以在功能上实现全局视图的替代,但也存在着一定的问题
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞0
添加新评论0 条评论