活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

本文在 https://www.gbase.cn/community/post/5594 基础上改进
南大通用 GBase 8s 数据库中，我们可以通过建立一个函数的方法用来调用获取建表语句。

本方法完善了生成索引创建语句

```
--在gbase模式（set environment sqlmode ‘gbase’）下建如下函数
set environment sqlmode 'gbase';

--删除get_ddl函数
Drop function if exists get_ddl2(varchar);

--创建函数语句
create function get_ddl(p_name varchar(128))

returns lvarchar(16384)

define v_ddl lvarchar(16384);

define v_colname varchar(128);

define v_coltype_name varchar(128);

define v_nullable varchar(128);

define v_defvalue varchar(254);

define v_deftype varchar(10);

define v_ddl_pk lvarchar(16384);

define v_pk_collist lvarchar(16384);

define v_pk_name varchar(128);

define v_index_ddl lvarchar(16384);

define v_idxname varchar(128);

define v_unique varchar(10);

define v_tabname varchar(128);

define v_col_list lvarchar(1024);

define v_owner varchar(128);

on exception

return null;

end exception;

let p_name = lower(p_name);

-- 生成表结构 DDL

let v_ddl = 'CREATE TABLE ' || p_name || '(' || chr(13) || chr(10);

let v_ddl_pk = null;

-- colname, coltype, nullable, default_value, default_type_flag

foreach SELECT tmp.colname,tmp.coltype_name,

CASE d.type

WHEN 'L' THEN gbasedbt.get_default_value(tmp.coltype, tmp.extended_id, tmp.collength, d.default::lvarchar(256))::VARCHAR(254)

WHEN 'C' THEN 'current year to second'::VARCHAR(254)

WHEN 'S' THEN 'dbservername'::VARCHAR(254)

WHEN 'U' THEN 'user'::VARCHAR(254)

WHEN 'T' THEN 'today'::VARCHAR(254)

WHEN 'E' THEN de.default::VARCHAR(254)

ELSE NULL::VARCHAR(254)

END AS def_value,

tmp.nullable,

d.type

INTO v_colname, v_coltype_name, v_defvalue, v_nullable, v_deftype

FROM (

SELECT ce.tabid, ce.colno, ce.colname,ce.coltype,ce.extended_id,ce.collength,ce.coltypename2::varchar(128) AS coltype_name,

CASE WHEN ce.coltype > 255 THEN 'NOT NULL' ELSE '' END AS nullable

FROM syscolumnsext ce, systables t

WHERE ce.tabid = t.tabid

AND t.tabname = p_name

ORDER BY ce.colno) tmp LEFT JOIN sysdefaults d ON (tmp.tabid = d.tabid AND tmp.colno = d.colno)

LEFT JOIN sysdefaultsexpr de ON (tmp.tabid = de.tabid AND tmp.colno = de.colno and de.type='T')

-- no column found, return null

if v_colname is null or v_colname = '' then

return null;

end if;

-- default values with function not do.

if v_deftype = 'E' then

let v_defvalue = 'DEFAULT (' || v_defvalue || ')';

elif v_deftype = 'L' and v_coltype_name[1,4] in ('CHAR','VARC','LVAR','NCHA','NVAR') then

let v_defvalue = 'DEFAULT ''' || v_defvalue || '''';

elif v_defvalue is not null then

let v_defvalue = 'DEFAULT ' || v_defvalue;

end if;

-- column: colname coltype default nullable

let v_ddl = v_ddl || ' ' || v_colname || ' ' || v_coltype_name || ' ' || v_defvalue || ' ' || v_nullable || ',' || chr(13) || chr(10);

end foreach;

-- primary key

let v_pk_collist = '';

let v_pk_name = '';

foreach SELECT

so.constrname,

rtrim((select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part1)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part2)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part3)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part4)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part5)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part6)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part7)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part8)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part9)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part10)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part11)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part12)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part13)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part14)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part15)) || ',' ||

(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part16)), ',') as keys

INTO v_pk_name, v_pk_collist

FROM systables st, sysconstraints so, sysindexes si

WHERE st.tabname = p_name

AND st.tabid = so.tabid

AND so.constrtype = 'P'

AND so.idxname = si.idxname

end foreach;

if v_pk_collist = '' or v_pk_collist is null then

let v_ddl = rtrim(v_ddl, ',' || chr(13) || chr(10));

elif v_pk_name[1,1] = 'u' then

let v_ddl = v_ddl || ' PRIMARY KEY(' || v_pk_collist || ')';

else

let v_ddl = v_ddl || ' CONSTRAINT ' || v_pk_name || ' PRIMARY KEY(' || v_pk_collist || ')';

end if;

let v_ddl = v_ddl || chr(13) || chr(10) || ');' || chr(13) || chr(10);

-- 生成索引 DDL

let v_index_ddl = '';

-- 获取索引基本信息及所属用户

foreach SELECT 

    i.idxname,

    CASE WHEN sc.constrtype IN ('U', 'P') THEN 'UNIQUE' ELSE '' END AS unique_flag,

    t.tabname,

    t.owner,

    rtrim(

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part1) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part2) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part3) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part4) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part5) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part6) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part7) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part8) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part9) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part10) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part11) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part12) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part13) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part14) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part15) || ',' ||

        (SELECT sc_col.colname FROM syscolumns sc_col WHERE sc_col.tabid = t.tabid AND sc_col.colno = i.part16),

        ','

    ) AS col_list

INTO v_idxname, v_unique, v_tabname, v_owner, v_col_list

FROM systables t, sysindexes i, sysconstraints sc

WHERE t.tabname = p_name

AND t.tabid = i.tabid

AND i.idxname = sc.idxname (+)

AND t.tabid = sc.tabid (+)

ORDER BY i.idxname

    -- 构建索引 DDL 语句

    IF v_unique = 'UNIQUE' THEN

        LET v_index_ddl = v_index_ddl || 'CREATE UNIQUE INDEX ' || v_idxname || ' ON ' || v_tabname || ' (' || v_col_list || ');' || CHR(13) || CHR(10);

    ELSE

        LET v_index_ddl = v_index_ddl || 'CREATE INDEX ' || v_idxname || ' ON ' || v_tabname || ' (' || v_col_list || ');' || CHR(13) || CHR(10);

    END IF;

END FOREACH;

-- 去除索引 DDL 末尾多余的换行符

IF LENGTH(v_index_ddl) > 0 THEN

    LET v_index_ddl = RTRIM(v_index_ddl, CHR(13) || CHR(10));

END IF;

-- 合并表 DDL 和索引 DDL

let v_ddl = v_ddl || v_index_ddl;

return v_ddl;

end function;
```



南大通用GBase 8s 获取建表语句的函数

南大通用GBase 8s 获取建表语句的函数

评论

热门帖子