SQL 语法

between and 怎么使用

请参考《DM_SQL 语言手册》,手册位于数据库安装路径 /dmdbms/doc 文件夹,示例如下:

示例执行结果

BETWEEN ... AND 属于前包含后不包含,包含 2019-01-01 不包含 2020-01-01。

如何设置成主键后,生成默认自增

DM 数据库也可以实现这种方式,只是和 MySQL 的操作方式不一样而已。举例如下:

CREATE TABLE "TAB_12"
(
"ID" INT IDENTITY (1, 1) NOT NULL,
"NAME" VARCHAR(10),
NOT CLUSTER PRIMARY KEY("ID")) ;

查询结果

查询结果

小写调用存储过程报错

这个是数据库大小写敏感的问题。数据库在初始化的时候选择是否大小写敏感,默认是大小写敏感。这时执行的 SQL 在到解释器那层解析时,会自动转换为大写名称。举例如下:

create or REPLACE PROCEDURE p_test as
V_BACKUP_DATE varchar;
begin
V_BACKUP_DATE := TO_CHAR(SYSDATE,'YYYY_MM_DD_HH24_MI_SS');
print V_BACKUP_DATE;
end;

执行结果日志

而以下语句将报错:

create or REPLACE PROCEDURE "p_test1" as
V_BACKUP_DATE varchar;
begin
V_BACKUP_DATE := TO_CHAR(SYSDATE,'YYYY_MM_DD_HH24_MI_SS');
print V_BACKUP_DATE;
end;

执行出错日志

因为执行 call p_test1() 时,数据库解释器自动换行成 CALL P_TEST1(),而对象 P_TEST1 在数据库中是不存在的,数据库中存的是 p_test1 这个。可以加上 “” 再执行,执行结果如下图所示:

执行成功日志

DM 中按姓氏笔画排序用什么函数

可以使用 NLSSORT 函数 实现此功能,NLSSORT 支持以拼音、笔画、部首排序,示例如下:

拼音:SELECT * FROM PEOPLE ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_PINYIN_M');
笔划:SELECT * FROM PEOPLE ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_STROKE_M');
部首:SELECT * FROM PEOPLE ORDER BY NLSSORT(name,'NLS_SORT = SCHINESE_RADICAL_M');

有类似 Oracle 的 REDO 日志的文件吗,如何语句查询

DM 有 REDO 日志,默认位置在数据库文件夹内,名称为:数据库名 0X.log。

如 (DAMENG01.log DAMENG02.log) 可以通过如下语句查看 REDO 日志信息。

select * from v$rlogfile;

关于 REDO 日志的介绍请参考《DM 系统管理手册》第二章-2.4 节内容(手册位于数据库安装路径 /dmdbms/doc 文件夹下),节选如下:

  • 重做日志,又叫 REDO 日志,指在 DM 数据库中添加、删除、修改对象,或者改变数据,DM 数据库都会按照特定的格式,将这些操作执行的结果写入到当前的重做日志文件中。重做日志文件以 log 为扩展名。每个 DM 数据库实例必须至少有 2 个重做日志文件,默认两个日志文件为 DAMENG01.log、DAMENG02.log,这两个文件循环使用。
  • 重做日志文件主要用于数据库的备份与恢复。理想情况下,数据库系统不会用到重做日志文件中的信息。但是如果出现意外情况,例如电源故障、系统故障、介质故障,或者数据库实例进程被强制终止等,数据库缓冲区中的数据页来不及写入数据文件。这样,在重启 DM 实例时,通过重做日志文件中的信息,就可以将数据库的状态恢复到发生意外时的状态。
  • 重做日志文件对于数据库是至关重要的。它们用于存储数据库的事务日志,以便系统在出现系统故障和介质故障时能够进行故障恢复。在 DM 数据库运行过程中,任何修改数据库的操作都会产生重做日志,例如,当一条元组插入到一个表中的时候,插入的结果写入了重做日志,当删除一条元组时,删除该元组的操作也记录在日志内,这样,当系统出现故障时,通过分析日志可以知道在故障发生前系统做了哪些动作,并可以重做这些动作使系统恢复到故障之前的状态。

字符串截断、超长文本截取

  • 执行 INSERT 时报错

一般此类问题是由于目标表存在一个(或多个)字段长度不够,导致插入失败。重点关注 CHAR、VARCHAR 字段的长度,可以创建一个具备足够长度的测试表,将报错数据插入测试表中,通过 select max(length(“目标字段”)) from “测试表”,获得插入数据实际的长度信息,从而修改目标表数据或者处理包含超长字段的记录。

另外,注意中文字符的长度问题:数据库初始化参数 LENGTH_IN_CHAR=0 时,unicode 编码下一个中文占据 3 个字节 (char(3));GBK 编码下一个中文占据 2 个字节 (char(2));当数据库初始化参数 LENGTH_IN_CHAR=1 时,char(1) 即可存储一个字符。

Varchar 类型描述

  • 执行查询时报此错误

此类问题常见于分组查询中使用了 wm_concat() 之类函数拼接字符串,由于分组数据记录过多导致拼接函数返回值超长,解决方法一般是改写 SQL 查询条件降低分组记录数或者对 SQL 语句进行修改(如改为超长切分)。

此外,clob 字段进行模糊查询时的字符串截断:Case (clob_column_name as varchar) like

  • 数据迁移时报错

一般从 Oracle 迁移到 DM 的时候,出现字符串截断的一般都是字段中含有中文,出现这种问题是因为 DM 初始化的时候选择的字符集是 Unicode(即 utf-8),该字符集的国际标准是一个汉字占 3 个字节,而 Oracle 中默认情况下一个汉字占 2 个字节,此时迁移的时候就会报。

遇到该错误有 3 种解决方法。

  1. 在初始化数据库的时候,字符集选择【gb18030】。
  2. 在初始化数据库的时候,选择【VARCHAR 类型以字符为单位】。
  3. 因为前面 2 种都需要重新初始化数据库,第三种不需要重新初始化数据库即可解决,即在选择迁移方式时,选择字符长度映射关系为 2,如下图所示:

选择迁移方式-设置字符长度

  1. 也可使用数据库自带的包解决 clob 字段模糊查询的问题。

例如:

---执行以下语句报错,其中a1字段为clob类型
select * from test where a1 like '%会议%';

---尝试使用to_char进行转换后模糊查询,报错test类型数据过长
select * from test where to_char(a1) like '%会议%';

---通过数据库自带的包 dbms_lob.instr 处理,解决该问题
select * from test where dbms_lob.instr(a1,'会议',1,1)>0;

to_date(2019/12/31,'YYYY-MM-DD')报:无效的日期格式

【场景复现】

执行结果

【解决方式】

结果集

【报错原因】

合法的 DATE 格式为年月日、月日年和日月年三种,各部分之间可以有间隔 (".","-","/") 或者没有间隔符;合法的 TIME 格式为:时分时分秒,间隔符为":"。

详见《DM_SQL 语言手册》第八章-函数 8.3 日期时间函数中第 37 条。(手册位于数据库安装路径 /dmdbms/doc 文件夹下)

举例如下:

SELECT TO_DATE('2003-06-19 08:40:36','YYYY-MM-DD HH:MI:SS');

查询结果如下:

结果集

数据链路在哪里创建

数据链路也就是外部链接,外部链接对象 (LINK) 是 DM 中的一种特殊的数据库实体对象,它记录了远程数据库的连接和路径信息,用于建立与远程数据的联系。用户可以通过外部链接对远程数据库的表进行查询和增删改操作,以及本地调用远程的存储过程。

例如:DM-DM 外部链接的创建

  • 两台服务器,其中一个为目的主机 A,另一个为源端机器 B;分别在这两台服务器上修改 dm.ini 中参数 MAL_UTHR_FLAG = 0MAL_INI=1(开启 MAL 系统),且配置 dmmal.ini (保证源端目的端的 dmmal.ini 文件相同)如下:
[MAL_INST1]
MAL_INST_NAME = DMSERVER  #实例名,和 dm.ini 中的 INSTANCE_NAME 一致
MAL_HOST   = 192.168.10.2  #实例所在 IP 地址
MAL_PORT   = 5536  #MAL系统监听 TCP 连接的端口,不是数据库端口号
[MAL_INST2]
MAL_INST_NAME = DMSERVER1
MAL_HOST   = 192.168.10.3
MAL_PORT   = 5537
  • 分别重启 A,B 两台机器上的 DM 服务。
  • 在主机 A 上建表 test:
CREATE TABLE TEST(C1 INT,C2 VARCHAR(20));

在 B 上建立到 A 的外部链接 LINK01:

CREATE PUBLIC LINK LINK01 CONNECT WITH SYSDBA IDENTIFIED BY SYSDBA USING '192.168.10.2/5536';
  • 在 B 上使用链接进行插入,更新,删除等操作,说明外部链接创建成功。如下图所示:

执行结果

注意

异构外部链接创建步骤请参考《DM_SQL 语言使用手册》第 16 章-外部链接。(手册位于**数据库安装路径** `/dmdbms/doc` 文件夹下)

DM 有没有类似 pl\sql 的对比表结构,比对表数据差异的工具,生成更新脚本,更新到正式环境

DM 数据库目前没有比对工具,可以自己写一个 SQL 进行比对或手动比对。示例如下:

  • 执行如下语句查看表结构信息。
SELECT * FROM DBA_TAB_COLUMNS;

结果集

  • 执行如下语句将不同数据库中表结构信息导入至新创建表中,方便进行对比。
CREATE TABLE TABLE_2 AS SELECT * FROM DBA_TAB_COLUMNS;
CREATE TABLE TXT_1 AS SELECT * FROM DBA_TAB_COLUMNS;

表 TABLE_2 是 DM 数据库实例(端口 5236)中的表结构信息,表 TXT_1 是 DM 数据库实例(端口 5237)中的表结构信息,根据不同实例中的表结构进行比对是否存在不一致的表。

执行结果

执行结果

  • 将 TXT_1(端口 5237)表导入到 DM 数据库(端口 5236)中,进行左连接查询,比对是否存在表结构差异。
SELECT DISTINCT A.OWNER,A.TABLE_NAME
FROM SYSDBA.TABLE_2 A LEFT JOIN SYSDBA.TXT_1 B
ON A.OWNER|| A.TABLE_NAME = B.OWNER|| B.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME AND
B.COLUMN_ID=A.COLUMN_ID
WHERE A.OWNER='SYSDBA' AND B.OWNER IS NULL
ORDER BY A.TABLE_NAME;

结果集

DM 数据库如何获取表的列注释?在代码中如何修改列属性的注释

DM 数据库查询表的列注释语句如下:

select * from SYSCOLUMNCOMMENTS;

执行结果

如题所示:用户可以自行条件筛选得到想要的结果集。

列属性的注释:

select COMMENTS from user_col_comments where TABLE_NAME='TEST' AND COLUMN_NAME='ID';

修改列属性的注释:

comment on column "SYSDBA"."TEST"."ID" is 'ttttt';

DM 数据库 VARCHAR2 的长度最大是多少

  • DM 数据库的 VARCHAR2 长度如果指定了 USINGLONG ROW 存储选项,则插入 VARCHAR 数据类型的长度不受数据库页面大小限制。VARCHAR 类型在表达式计算中的长度上限不受页面大小限制,为 32767;

关于 VARCHAR2 长度详细简介详见《DM_SQL 语言使用手册》-1.41 章节。(手册位于数据库安装路径 /dmdbms/doc 文件夹下)

  • VARCHAR2 数据类型指定变长字符串,用法类似 CHAR 数据类型,可以指定一个不超过 8188 的正整数作为字符长度,在 DM 数据库中,VARCHAR2 数据类型的实际最大长度由数据库页面大小决定,在建库时指定,之后无法修改。具体最大长度算法如下表所示:
数据页面大小 实际最大长度
4K 1900
8K 3900
16K 8000
32K 8188

DM 支持的字段名最大长度

正规标识符的最大长度是 128 个英文字符或 64 个汉字。

  • 测试:64 个汉字长度字段。
CREATE TABLE "SYSDBA"."TABLE_1" --64个汉字字段
(
"一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四" CHAR(10)) STORAGE(ON "MAIN", CLUSTERBTR);

测试结果:创建成功

CREATE TABLE "SYSDBA"."TABLE_1" --65个汉字字段
(
"一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五六七八九十一二三四五" CHAR(10))STORAGE(ON "MAIN", CLUSTERBTR
);  

测试结果:失败

测试结果:最大长度是 64 个汉字字符。

执行结果

  • 测试:128 个英文字符长度字段。

执行结果

测试成功。

  • 测试:129 个英文字符长度字段。

执行结果

测试结果:最大长度是 128 个英文字符。

DM 数据库有类似于 Oracle 的 v$sql 的视图吗

DM 数据库没有提供完全一样的系统视图,提供了 V$SQL_HISTORY 视图来达到类似的功能。目前 DM 数据库可以兼容 100 多个 Oracle 的系统视图。在《DM 系统管理员手册》中有更多介绍(手册位于数据库安装路径 /dmdbms/doc 文件夹下)。

[不能修改或删除聚集索引的列] 或 [试图删除聚集主键]、[表 xx 中不能同时包含聚集 KEY 和大字段]

  • 情况一

表上某一列上创建有聚集索引,但是该列不是主键列。举例如下:

CREATE TABLE TEST_C (A INT, B VARCHAR,
CONSTRAINT C1 CLUSTER KEY (A));

或:

CREATE TABLE TEST_C1(A INT,B VARCHAR);
CREATE CLUSTER INDEX IDX1 ON TEST_C1(A);

这种情况下,在列 A 上有一个聚集索引,如下图所示:

TEST_C1

此时若想直接修改列 A 的字段类型或删除列 A,会报错:不能修改或删除聚集索引的列

若想对列 A 进行修改,需要先删除掉 A 上的索引,再做修改。

执行结果

  • 情况二

表上某一列为聚集主键,举例如下:

CREATE TABLE TEST_PK(A INT PRIMARY KEY,B VARCHAR);

主键

此时修改或删除列 A,会报错:不能修改或删除聚集索引的列;删除表上的主键约束会报错:试图删除聚集主键

在这种情况下,若想对列 A 进行修改,可以重建表,或者重新在别的列上建立一个聚集索引,然后再删掉,此时主键上的索引变成非聚集了,就可以修改了。

要想在一开始建表的时候就指定非聚集主键,有两种方法:

  1. 用语句显式指定;
  2. 修改 dm.ini 参数 PK_WITH_CLUSTER 为 0,默认创建主键时为非聚集型;

语句显式指定如下:

CREATE TABLE TEST_PK2(A INT,B VARCHAR,
CONSTRAINT C1 NOT CLUSTER PRIMARY KEY (A));

索引

此时可以对列 A 进行修改与删除,如下图所示:

执行结果

执行结果

  • 修改 dm.ini 参数的方式

由于 PK_WITH_CLUSTER 默认取值为 1,即仅指定 PRIMARY KEY 关键字时默认创建为聚集主键,修改为 0 后默认创建非聚集主键。如下所示:

SELECT * FROM V$DM_INI WHERE PARA_NAME = 'PK_WITH_CLUSTER';

查询该参数的值可以使用 SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',0) 语句来将该参数值修改为 0;使用 SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',1) 语句将该参数值改回 1。

另外,报错 表 xx 中不能同时包含聚集 KEY 和大字段 也可以通过将表重建为非聚集型主键的方式来解决。

DM 数据库如何获取系统时间

DM 数据库提供对应的系统函数获取系统时间,如下所示:

--返回值为:DATE
select current_date();
--返回值为:TIMESTAMP(0)
select sysdate();
--返回值为:DATETIME(6) WITH TIME ZONE
select current_timestamp();

DM 是否可以并发创建索引

DM 无法并发创建索引,如下所示:

drop table table1;  
create  table table1 (c1 int,c2 VARCHAR2(10),c3 number);
insert  into table1 select level,level,level from dual CONNECT by level <10000;

按 Oracle 语法并行创建索引,如下所示:

create  index ind_c2 on SYSDBA.TABLE1(c2) parallel 2;

报错,如下所示:

create  index ind_c2 on SYSDBA.TABLE1(c2) parallel 2;
执行失败(语句1)  [P]  第 1 行, 第 41 列 [parallel] 附近出现错误 [-2007]:

语法分析出错
  • DM 并行查询使用方法

修改并行查询相关的 dm.ini 参数,如下所示:

select  * from v$dm_ini where para_name
in('MAX_PARALLEL_DEGREE','PARALLEL_POLICY','PARALLEL_THRD_NUM')

只有 PARALLEL_POLICY0 时并行才可以使用(取值:0,1,2),MAX_PARALLEL_DEGREE 指定并行度(取值:1-128);PARALLEL_THRD_NUM 指定并行线程数(取值:1-1024);

PARALLEL_POLICY=1 时(自动并行模式),修改 PARALLEL_POLICY 的值为 1,需要重启数据库生效:

sp_set_para_value(2,'PARALLEL_POLICY',1);

修改 MAX_PARALLEL_DEGREE 为 10,即时生效:

sp_set_para_value(1,'MAX_PARALLEL_DEGREE',10);

此时查询默认开启并行,且并行度为 10,当 MAX_PARALLEL_DEGREE 值为 1 时,无并行:

sp_set_para_value(1,'MAX_PARALLEL_DEGREE',1);

但是此时指定 hint 的 parallel 仍可以开启并行,且指定 hint 优先级高于 MAX_PARALLEL_DEGREE 参数。

PARALLEL_POLICY=2 时(手动并行模式),修改 PARALLEL_POLICY 的值为 2,需要重启数据库生效:

sp_set_para_value(2,'PARALLEL_POLICY',2);

此时 MAX_PARALLEL_DEGREE 参数无效,必须指定 hint 的 parallel 才开启并行。如下所示:

create  table t_objects as select * from dba_objects;

select  * from t_objects;

执行计划:

1    #NSET2: [0, 1768, 566]
2      #PRJT2: [0, 1768, 566]; exp_num(16), is_atom(FALSE)
3        #CSCN2: [0, 1768, 566]; INDEX33555710(T_OBJECTS)

select  /*+ PARALLEL(2)*/* from t_objects;

执行计划:

1    #NSET2: [0, 1768, 566]
2      #LOCAL COLLECT: [0, 1768, 566]; op_id(1) n_grp_by (0) n_cols(0)  n_keys(0) for_sync(FALSE)
3        #PRJT2: [0, 1768, 566]; exp_num(16), is_atom(FALSE)
4          #CSCN2: [0, 1768, 566]; INDEX33555710(T_OBJECTS)

SQL 执行计划绑定变量和非绑定变量的不一致

  • 检查是否赋值有数据类型转换,导致不走索引。举例说明如下:
create  table table1 (c1 int,c2  VARCHAR2(10),c3 number);
insert into table1 select  level,level,level from dual CONNECT by level <10000;
create index ind_c2 on  SYSDBA.TABLE1(c2);
select * from SYSDBA.TABLE1 where  TABLE1.C2=?

执行计划:(走 IND_C2 索引)

1  #NSET2: [0, 249,  90]
2   #PRJT2: [0, 249,  90]; exp_num(4), is_atom(FALSE)
3    #BLKUP2:  [0, 249, 90]; IND_C2(TABLE1)
4       #SSEK2: [0, 249, 90]; scan_type(ASC), IND_C2(TABLE1),scan_range[exp_param(no:0),exp_param(no:0)]
select * from SYSDBA.TABLE1 where  TABLE1.C2=5

执行计划:(有数据类型转换,不走 IND_C2 索引)

1  #NSET2: [1, 499,  90]
2   #PRJT2: [1, 499,  90]; exp_num(4), is_atom(FALSE)
3    #SLCT2: [1,  499, 90]; exp_cast(TABLE1.C2) = 5
4     #CSCN2:  [1, 9999, 90]; INDEX33555695(TABLE1)
select * from SYSDBA.TABLE1 where TABLE1.C2='5'

执行计划:(无数据类型转换,走 IND_C2 索引)

1  #NSET2: [0, 249,  90]
2   #PRJT2: [0, 249,  90]; exp_num(4), is_atom(FALSE)
3    #BLKUP2:  [0, 249, 90]; IND_C2(TABLE1)  
4     #SSEK2:  [0, 249, 90]; scan_type(ASC), IND_C2(TABLE1), scan_range['5','5']
  • 估算值不同,使用的连接方式、连接顺序会有差异,影响最终的执行计划。

有过滤条件,查询时不走索引

需注意数据分布统计信息,很多时候,统计信息不全会导致优化器计算执行代价的时候算错,误认为走索引会比全表慢,造成执行计划不对。

  • 统计信息的收集方法

某用户下的所有索引:

DBMS_STATS.GATHER_SCHEMA_STATS('SSCKF',100,TRUE,'FOR ALL INDEXED SIZE AUTO');

某用户下所有字段(包括索引):

DBMS_STATS.GATHER_SCHEMA_STATS('SSCKF',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

某表下的所有字段:

DBMS_STATS.GATHER_TABLE_STATS('SSCKF','FO_ACCOUNTBILL',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

收集某一列的统计信息:

sp_col_stat_init('JXWOA','OA2_RECEIVEDOC','RD_STATE');

sp_col_stat_init('JXWOA','OA2_SENDDOC','SD_STATE');

例如有 10 万条测试数据,然后执行查询语句 SELECT * FROM T WHERE C='A';但这 10 万条数据全是一样的,是不可能走索引的。

修改测试数据的脚本,让 C 这个字段的值平均分布在 1~1000 上,做完后收集统计信息,就走索引了。

如何修改用户默认表空间

创建用户 TEST 并指定默认表空间为 TBS_TEST,如下所示:

create tablespace TBS_TEST datafile  'TBS_TEST.dbf' size 50;

create user TEST identified by “123456789” default tablespace  TBS_TEST;

修改用户 TEST 的默认表空间为 TBS_TEST1:

create tablespace TBS_TEST1 datafile  'TBS_TEST1.dbf' size 50;

alter user TEST default tablespace  TBS_TEST1;

DM 支持 Oracle 的闪回查询么

闪回开启参数,在 dm.ini 文件中。(生产环境最好不要开闪回功能,慎用

参数介绍

管理工具开启闪回功能如下:

执行结果

管理工具界面闪回按钮如下:

闪回按钮

闪回条件

时刻:指定闪回查询时刻。

事务 ID 号:指定事务 ID 号。

要使用闪回功能实例要开启归档:

  • 在线开启

修改数据库为 Mount 状态,如下所示:

SQL ALTER DATABASE MOUNT;

配置本地归档,如下所示:

SQL ALTER DATABASE ADD ARCHIVELOG 'DEST = /archive, TYPE = local, FILE_SIZE = 1024, SPACE_LIMIT = 0';
//DEST=归档路径  FILE_SIZE:单个归档文件大小。SPACE_LIMIT:预留归档总大小,0 表示没有限制。

开启归档模式,如下所示:

SQL ALTER DATABASE ARCHIVELOG;

修改数据库为 OPEN 状态,如下所示:

SQL ALTER DATABASE OPEN;

null 值结果排序前后问题

对可为空的 timestamp 列排序,都是 null 在最前面

创建测试表并初始化数据,如下所示:

CREATE TABLE "SYSDBA"."a"
("COLUMN_1" TIMESTAMP(6)) STORAGE(ON "MAIN", CLUSTERBTR);

insert "SYSDBA"."a" values (''),(SYSTIMESTAMP),(SYSTIMESTAMP);
commit;
insert "SYSDBA"."a" values (''),(SYSTIMESTAMP),(SYSTIMESTAMP);
commit;
insert "SYSDBA"."a" values (''),(SYSTIMESTAMP),(SYSTIMESTAMP);
commit;

执行结果

执行结果

测试结果:可为空的 timestamp 列排序,无论 asc,desc,都是 null 在最前面,解决方法如下:

  • 修改 dm.ini 中 ORDER_BY_NULLS_FLAG 的值为 1;
    这个值默认是 0,意义是
    Whether to place NULL values to the end of the result set when in ascending order
    修改以后,重启数据库。查询时 order by asc 就发现 null 值在最后了,但是 DESC 降序时该参数无效。
  • 修改 SQL 写法
 order by NVL(column , '0') 当column列为null时则指定为0。
 order by column asc nulls first : null值始终放在最前面。
 order bu column desc nulls last : null值始终放在最后面。

Insert values 超过最大参数个数 (2048)

insert into xx(id, name ....) values (?,?,?), (?,?,?), (?,?,?)
报错:超过最大参数个数(2048)

insert into FZQ(COLUMN_1) values (1),(2)....(2048);

执行结果

尝试插入 2049 个报错复现:

insert into FZQ(COLUMN_1) values (1),(2)....(4100);

执行结果

结论: 最大参数不止 2048。

  • insert all INTO 语法改写

批量插入数据 insert all。

INTO  A(field_1,field_2) VALUES (value_1,value_2)
INTO  A(field_1,field_2) VALUES (value_3,value_4)
INTO  A(field_1,field_2) VALUES (value_5,value_6)  select  1 from dual;

Demo 如下所示:

INSERT ALL
INTO FZQ(COLUMN_1) VALUES (1)
INTO FZQ(COLUMN_1) VALUES (2)  .......
INTO FZQ(COLUMN_1) VALUES (4100)
select 1 from dual

执行结果

类似 Oracle 正则表达式 regexp_substr 函数

  • regexp_like 样例测试

REGEXP_LIKE
(
source_string,
pattern
[, match_parameter]
)

--创建表并初始化数据
--创建表
create table fzq (  
   id varchar(4),  
   value varchar(10)
);
--插入数据
insert into fzq values ('1','1234560');
insert into fzq values ('2','1234560');
insert into fzq values ('3','1b3b560');
insert into fzq values ('4','abc');
insert into fzq values ('5','abcde');
insert into fzq values ('6','ADREasx');
insert into fzq values ('7','123 45');
insert into fzq values ('8','adc de');
insert into fzq values ('9','adc,.de');
insert into fzq values ('10','1B');
insert into fzq values ('10','abcbvbnb');
insert into fzq values ('11','11114560');
insert into fzq values ('11','11124560');
--提交
commit;
select * from fzq where regexp_like(value,'1[0-9]{4}60');

查询结果

  • REGEXP_SUBSTR 测试样例

REGEXP_SUBSTR
(
source_string,
pattern
[,position [,occurrence [,match_parameter]]]
)

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL;

查询结果

  • REGEXP_INSTR 样例测试

REGEXP_INSTR
(
source_string,
pattern
[,start_position
[,occurrence
[,return_option
[,match_parameter]]]]
)

SELECT REGEXP_INSTR ('hello itmyhome', 'e')FROM dual;

查询结果

  • REGEXP_REPLACE 样例测试

REGEXP_REPLACE
(
source_string,
pattern
[,replace_string]
[,position]
[,occurtence]
[,match_parameter]
)

select regexp_replace('asdlfj','(.)','\1 ') from dual;

查询结果

对象权限的授权

--对象权限的授予

grant select on  ""SYS"".""V$SQL_NODE_NAME"" to ""用户"";
grant select on  ""SYS"".""V$CIPHERS"" to ""用户"";
grant select on  ""SYS"".""V$DATAFILE"" to ""用户"";

USING LONG ROW(启用超长记录)选项的作用

在建表时,字符类型的字段和整条数据的存储长度是受初始化参数 PAGE_SIZE(页大小)限制的,具体关系如下图所示:

参数信息

实际插入表中的列长度受到记录长度的约束,每条记录总长度不能大于页面大小的一半。例如我们页大小初始化是 32 KB,某表中有 3 个 VARCHAR 类型的字段,那么每个字段最大长度不能操作 8188 字节,且每条记录不能超过页大小(32 KB)的一半,也就是不能超过 16 KB,如果记录超过 16 KB 时,会报“错误号:-2665 错误消息:[P] 记录超长”。

报错信息

当指定了 USINGLONG ROW 存储选项,则每条记录的长度不受数据库页面大小限制。USINGLONG ROW 选项用法如下:

create table "text_to_varchar1"
(
"id" CHAR(8188),
"textcol1" VARCHAR(8188),
"textcol3" VARCHAR(8188),
"textcol4" VARCHAR(8188),
"textcol5" VARCHAR(8188),
"textcol2" VARCHAR(8188)
) STORAGE (USING LONG ROW);

关于 DM 数据库的数据类型

DM 数据的数据类型请参考如下两个手册的内容(手册位于数据库安装路径 /dmdbms/doc 文件夹下。):

  • 《DM_SQL 语言使用手册》-DM_SQL 所支持的数据类型章节;其中手册中“长度最大为 2G-1 字节”的“-”表示减号。
  • 《DM_SQL 程序设计》-DM_SQL 程序数据类型与操作符章节。

insert 后如何获取自增 ID

IDENT_CURRENT
定义:
int
IDENT_CURRENT (
 fulltablename varchar(8187)
)
功能说明:
获取自增列当前值
参数说明:
fulltablename:表全名;格式为“模式名.表名”
返回值:
自增列当前值

举例说明:

SELECT IDENT_CURRENT('PRODUCTION.PRODUCT');

若为序列作为自增列,则使用 select 模式名.序列名.CURRVAL 获取当前值;select 模式名.序列名.NEXTVAL 获取下一值。

DM 和其他数据库的兼容性配置

DM 数据库初始化 dm.ini 文档,是否兼容其他数据库模式,如下所示:

0:不兼容
1:兼容 SQL92 标准
2:部分兼容 Oracle
3:部分兼容 MS SQL SERVER
4:部分兼容 MySQL
5:兼容 DM6
6:部分兼容 TERADATA

Oracle 和 DM 数据类型对比表

Oracle DM
类型名 说明 类型名 说明 备注
CHAR (n) 定长字符串,最大长度 2000,长度不足 n 时,自动填充空格 CHAR (n)、CHARACTER (n) 定长字符串,最大长度 8000(页大小为 16 KB 或以上时),长度不足 n 时,自动填充空格 DM 允许的串最大长度可通过设置页大小参数分别调整为 1900,3900,8000,16200 之一。
VARCHAR (n) VARCHAR2 (n) 可变长字符串,最大长度 4000 VARCHAR (n)、 VARCHAR2 (n) 可变长字符串,最大长度 8000(页大小为 16 KB 或以上时)
LONG 可变长文本,最大长度 134217727,表中只能有一个 LONG 列 TEXT、LONGVARCHAR 长文本,最大长度 2G-1,表中可有多个 TEXT/LONGVARCHAR 列 可用于存储较长的非格式化文本,文本较小时可考虑用 Oracle 的 VARCHAR2 或 DM 的 VARCHAR
NUMBER 可变长数值列,最大数值长度为 38 个数字,NUMBER 是 Oracle 特有的非标准数据类型 BIT、BOOLEAN BIT 类型用于存储整数数据 1、0 或 NULL。BOOLEAN 类型的字值可以取常量 TRUE 和 FALSE,或者数值 1 和 0 这些数据类型都不带精度描述。DM 的 BIT 类型与 SQL Server 2000 的 BIT 数据类型相似。
BYTE、 TINYINT 1 字节存储的整数,精度 3,标度 0 BIT、BOOLEAN 都可以用来支持 ODBC JDBC 的布尔数据类型,MONEY 类型便于存储货币数据。
SMALLINT 2 字节存储的整数,精度 5,标度 0
INT、 INTEGER 4 字节存储的整数,精度 10,标度 0
BIGINT 8 字节存储的长整数,精度 19,标度 0
DEC、 DECIMAL、 NUMERIC、 NUMBER 定点数,精度 16,标度 0
MONEY 定点数,精度 19,标度 4
NUMBER (m,n) 可变长数值列,最大数值长度为 38 个数字 DECIMAL (m,n)、 NUMBER (m,n)、NUMERIC (m,n) 这些数据类型都带精度描述。
FLOAT 浮点数,精度 126 FLOAT、DOUBLE、DOUBLE PRECISION 浮点数,最大二进制精度 53,十进制精度 15
REAL 实数类型,NUMBER (63),精度更高 REAL 浮点数,二进制精度 24,十进制精度 7
(LONG) RAW 可变长二进制数据 最大长度 134217727,表中只能有一个 LONG RAW 列 BINARY (n) 定长二进制数据,缺省长度 1,最大长度同 CHAR,长度不足 n 时,自动填充 0
VARBINARY (n) 可变长二进制数据,缺省长度 1,最大长度同 CHAR
IMAGE、LONGVARBINARY 可变长的长二进制数据,最大长度 2G-1,表中可有多个 IMAGE/ LONGVARBINARY 列 可用于存储多媒体图像、WORD 文档等,长度较小时可考虑用 DM 的 VARBINARY
BLOB (n) 可变长二进制大对象,最大长度 4G-1 BLOB (n) 可变长二进制大对象,最大长度 2G-1
CLOB (n) 可变长二进制大对象,最大长度 4G-1 CLOB (n) 可变长字符串大对象,最大长度 2G-1
DATE 固定长度(7 字节)的日期型,时间也作为一部分存储其中。Oracle 的 DATE 是其特有的非标准数据类型。 DATETIME 日期型,包括年、月、日、时分秒信息。数据格式:datetime'2013-11-06 12:23:22' 时间间隔类型 Oracle、DM 均支持(略)
TIME 时间型,包括时、分、秒信息。格式:time ’09:10:21’
TIMESTAMP 时间戳型,包括年月日时分秒信息。例如:timestamp ‘1999-07-13 10:11:22’
BFILE 存放在数据库外的二进制数据,最大长度 4 GB BFILE 存储在操作系统中的二进制文件,文件存储在操作系统而非数据库中,仅能进行只读访问,最大长度 2G-1。
NCHAR (n) 根据字符集而定的固定长度字符串,最大长度 2000 bytes NCHAR (n) 根据字符集确定需要扩展的长度,最大长度 8000(页大小为 16 KB 或以上时)
NVARCHAR2 根据字符集而定的可变长度字符串,最大长度 4000 bytes NVARCHAR2 (n) 根据字符集确定需要扩展的长度,最大长度 8000(页大小为 16 KB 或以上时)
NCLOB 根据字符集而定的字符数据,最大长度 4 GB NCLOB 可变长字符串大对象,最大长度 2G-1
ROWID 数据表中记录的唯一行号,10 bytes VARCHAR (n)、VARCHAR2 (n) 可变长字符串,最大长度 8000(页大小为 16 KB 或以上时)
NROWID 二进制数据表中记录的唯一行号,最大长度 4000 bytes VARCHAR (n)、VARCHAR2 (n) 可变长字符串,最大长度 8000(页大小为 16 KB 或以上时)

如何加 comment 备注

举例如下:

CREATE TABLE "AEFD01"
("BANKID" VARCHAR2(3)) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE "AEFD01" IS '会计事件定义';--备注名称

字段名和关键字冲突

  • 建议尽可能避免这些关键词,关键字在《DM_SQL 语言使用手册》附录 1 中有说明。(手册位于数据库安装路径 /dmdbms/doc 文件夹下)
  • 双引号可以屏蔽关键字。(同样注意,双引号也能匹配大小写)
--[执行语句1]:
create table testdomain(domain int);
执行失败(语句1)
第 1 行, 第 24 列[domain]附近出现错误:
语法分析出错
--[执行语句1]:
create table testdomain("DOMAIN" int);
执行成功, 执行耗时362毫秒. 执行号:1626676
影响了0条记录
1条语句执行成功
  • 通过配置 dm_svc.conf 文件 KEYWORDS 参数解决保留关键字的问题。
    KEYWORDS
    标识用户关键字,所有在列表中的字符串, 如果以单词的形出现在 SQL 语句中,则这个单词会被加上双引号。该参数主要用来解决用户需要使用 DM 中的保留字作为对象名使用的状况。
    修改服务名配置文件如下:
TIME_ZONE=(480)
LANGUAGE=(cn)
dm=(10.*.*.9:5236)
[dm]
KEYWORDS=(需要排除的关键字 以逗号做分割)
  • 通过 jdbc 的 url 中添加 keyWords 参数,例如 aa abc
jdbc:dm://dm?keyWords=aa,abc
建议

不建议屏蔽,更换列名是最合适。

时间类型的默认值可以是当前时间

  • 问题截图

问题截图

  • 处理办法

使用查询函数 CURRENT_TIMESTAMP,如下所示:

SELECT CURRENT_TIMESTAMP();

执行界面

创建测试表,如下所示:

创建测试表

使用函数作为值不需要加引号。

含中文字符的 SQL 语句执行报错

实例如下所示:

执行语句

复制 SQL 语句到 DM 客户端执行要注意是英文输入模式下的标点符号。同时可以打开选项设置,勾选显示空白字符,可以很方便的发现是否有中文标点符号。

选项设置

Manager 中 SQL 自动补全

SQL 自动补全功能又称为:SQL 提示补充、SQL 查询补全、SQL 联想功能。
通过启动 Manager,打开窗口,选择【启用 SQL 输入助手】开启该功能,如下图所示:

输入助手

如何使用打印信息

可使用如下方法打印信息:

DBMS_OUTPUT.**ENABLE**();--使 DBMS_OUTPUT 启用

DBMS_OUTPUT.PUT_LINE('11111');

执行结果

PB 调用存储过程

PB 调用 he_test 报错 Space must be allocated for bind parameter,翻译过来是 约束变量必须分配空间

【报错原因】

PB 中使用 Declare 方式不能调用具有 in out 类型参数的存储过程,故采用外部函数的方式。

【问题描述】

create or replace procedure he_test(tin in varchar2,
out1 out varchar2,
out2 out varchar2)
as
begin
out1 := tin;
out2 := tin;
end he_test;

PowerBuilder:

string tpstring,ta,tb,tc
tpstring = 'ssss'
DECLARE get_trriff PROCEDURE FOR
he_test(:tpstring,:ta,:tb) using ORA;
EXECUTE get_trriff ;
if ORA.SqlCode < 0 then
      MessaGeBox("1","1" + ORA.sqlerrtext)
      CLOSE get_trriff;
      RETURN -1
    end if
FETCH get_trriff INTO :ta,:tb;
if ORA.SqlCode < 0 then
   MessaGeBox("2","2" + ORA.sqlerrtext)
   CLOSE get_trriff;
   RETURN -1
end if
sle_1.text = ta
sle_2.text = tb

如果要获取 OUTPUT 可把 procedure 作为 transaction 的外部函数引用 (local external function)。新建 -standare class- transation - local external function 右键 - paste special-sql - remote sp

shell 执行 DM 语句

  • 编写 SQL 脚本,写入需要执行的 SQL 语句。

编写脚本

  • shell 脚本中执行 DM 语句的格式如下:

shell 脚本

  • 执行脚本

执行结果

详细用法可参考《DM_Disql 使用手册》,手册位于数据库安装路径 /dmdbms/doc/special 文件夹下。

update 和 order by 如何一起用

错误示例:

update scm set row_id = rownum where bill_id =12345 order by id;

正确示例:

update scm t set row_id =  (select rn from (select bill_id, id, row_number() over(order by id) rn from scm tt where bill_id = 12345) tt where t.id = tt.id) where bill_id = 12345;

SQL 语句中不带上模式名

访问 TESTDB1 用户(模式)下的表,就使用 TESTDB1 用户登录来访问,就不需要加模式名 TESTDB1。假设一个用户拥有多个模式,该用户访问自己所属的其他非同名模式下的对象。

`SET SCHEMA TEST;` --切换当前模式为 TEST

参考链接:达梦数据库写 SQL 如何才能不带上模式名

分段连接、拼接字符串函数

【问题描述】

  • Oracle 的分段连接函数:WMSYS.WM_CONTACT,在 DM 库中有对应的函数吗?
  • DM 中有办法实现 MySQL 的 group_concat 函数吗?
  • DM 有没聚合,拼接字符串的函数?

【解决方法】

DM 数据库可以用 wm_concat。推荐使用 listagg。

可参《DM_SQL 语言使用手册》-LISTAGG/LISTAGG2 集函数,手册位于数据库安装路径 /dmdbms/doc 文件夹下。

DISQL 语句不结束怎么处理

对于普通 SQL,输入 ; 后回车结束。对于执行语句块(例如,创建触发器、存储过程、函数、包和模式等时),输入 / 结束。

获取 GUID 的函数

【问题描述】

SQL Server 中有 uniquidentifer 类型的 GUID;DM 中的 GUID 是什么?

【解决方法】

DM 中没有 uniquidentifer,获取 GUID 的函数 sys_guid()

sys_guid() 用的是 binary(16),16 个字节的二进制,和 SQL Server 的 uniqueidentifier 类型一样的。

需要 transform 定义 DDL 语句

【问题描述】

在 dbms_metadata 下,获取一个 table 的 DDL 语句,现在表相关的序列,外键都出来了,用户只想定制只返回 create table 的语句。

  • ORALCE 的 DBMS_METADATA.GET_DDL 定义语句如下:
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;

其中可以关闭索引、外键等关联如下:

结果

  • DM 的 DBMS_METADATA.GET_DDL 定义如下:
FUNCTION GET_DDL(
OBJECT_TYPE IN VARCHAR(30),
NAME IN VARCHAR(128),
SCHNAME IN VARCHAR(128) DEFAULT NULL )
RETURN CLOB

【解决方法】

这属于需求开发,建议联系相应项目的技术服务人员/销售提交功能需求。

如何查询 SQL 计划中各操作符的执行时间

SELECT N.NAME, TIME_USED, N_ENTER FROM V$SQL_NODE_NAME N,
V$SQL_NODE_HISTORY H WHERE N.TYPE$ = H.TYPE$ AND EXEC_ID = 538;

--538 为该 SQL 的执行号,dm.ini 中 ENABLE_MONITOR 参数需要改为 3。

--可以创建一个存储过程,来封装这个查询的 sql,输入参数为 sql 语句的执行号:
create or replace procedure sql_et(eid int) is
    begin
    select
            name as "OP",
            time_used/1000 || 'ms' as "TIME",
            cast(time_used * 100.0/sum(time_used) over() as dec(10, 2)) || '%' as "PERCENT" ,
            rank() over (order by time_used desc) as "RANK"    ,
            seq_no as "SEQ"
        from
           v$sql_node_history a,
           v$sql_node_name b
       where
           a.type$ = b.type$
           and exec_id = eid
       order by time_used desc;
   End;

执行 SQL 报错:试图在 blob 或者 clob 列上排序或比较

  • 不能 order by blob 或者 clob;
  • 有大字段的查询,不允许 distinct;

具体的语法规则,请参考《DM SQL 语言使用手册》,手册位于数据库安装路径 /dmdbms/doc/special 文件夹下。

如何查询所有自增列

可执行下列语句进行查询:

select b.table_name,a.name COL_NAME from  SYS.SYSCOLUMNS a,all_tables b,sys.sysobjects c where a.INFO2 & 0x01 = 0x01
and a.id=c.id and c.name= b.table_name

如何输出格式为 yyyy 年 mm 月 dd 日 HH 时 MM 分 SS 秒 的日期数据

SELECT to_char(sysdate,'yyyy"年"mm"月"dd"日" HH24"时"mi"分"ss"秒"') from dual;

达梦嵌套模糊查询如何编写

【问题描述】

SQL1: select unit_code from s_org s
SQL2: select * from t_org where path like concat('%', SQL1.unit_code, '%')
sql1的查询结果,作为sql2的模糊查询条件,该怎么实现
select * from t_org where path like concat('%', '1111', '%')
select * from t_org where path like concat('%', '1122', '%')
select * from t_org where path like concat('%', '1123', '%')
.....

就像这个,如何写成一条 SQL。

【解决方法】

可以使用 REGEXP_LIKE 函数

CREATE TABLE "SYSDBA"."ORG"
("ORGID" INT) STORAGE(ON "MAIN", CLUSTERBTR) ;

CREATE TABLE "SYSDBA"."TESTUSER"
("ID" INT,"A1" CHAR(10)) STORAGE(ON "MAIN", CLUSTERBTR) ;

select
        ID,
        A1
from
        TESTUSER
where
        REGEXP_LIKE (A1,
        (
                select
                        replace(wm_concat(orgid), ',', '|')
                from
                        (
                                select orgid from org
                        )
        )
        );

达梦数据库如何兼容 MySQL 的语法

【问题描述】

如何在达梦数据库中支持以下语法:

  • 判断表存在就删除 drop table if exists table_name
  • 不存在就创建 CREATE TABLE IF NOT EXISTS

【解决方法】

  • 需要在 dm.ini 文件中修改参数 compatible_mode=4,并重启数据库服务,就可以兼容 MySQL 数据库才支持的语法。
  • 也可以参考下 Oracle 的写法,Oracle 怎么判断表是否存在,就可以用在达梦上。

一般可以查询 DBA_TABLES 表,通过一个 IF 条件进行判断。

是否有类似 MySQL 的 ShA()等加密函数

可通过 dbms_obfuscation_toolkit 包实现。

有类似 mysql 的 LAST_INSERT_ID() 的函数吗

可以使用下面语句:

select @@IDENTITY;

举例说明:

CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
);
insert into new_employees(fname,minit,lname) values('test','d','test1');
select @@IDENTITY;

如何写复制表的 SQL

语句如下:

create table table1 as select * from table2;

可以参考安装目录 \doc 目录下的《DM_SQL 语言使用手册》—表管理章节。

如何用命令清理归档日志

删除 n 天前的归档日志,可执行下列语句:

SELECT SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - n);

获取表的主键的视图

使用下列语句:

select * from ALL_CONSTRAINTS where CONSTRAINT_TYPE='P'; 

在 Linux 环境下,AWR 报告如何取出来

--启用系统包和 AWR 包
CALL SP_INIT_AWR_SYS(1);
CALL SP_CREATE_SYSTEM_PACKAGES(1);

--查询 AWR 快照
SELECT * FROM SYS.WRM$_SNAPSHOT;

--设置快照间隔
--如果不设置快照间隔,手动执行快照后 SYS.WRM$_SNAPSHOT 视图中将没有记录。
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(50);

--在两个时间点分别手动创建快照,或者等待系统自动生成
--10:00 时创建第一快照:
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
--30 分钟后再创建一个,10:30:
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

--查询快照
SELECT * FROM SYS.WRM$_SNAPSHOT;

--创建AWR报告
---SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTML');:
SYS.AWR_REPORT_HTML(1,2,'/opt','AWR1.HTML');

达梦数据库的“函数”、“存储过程”的使用参考文档

可以参考安装目录 doc 目录下的《DM_SQL 语言使用手册》、《DM_SQL 程序设计》。

表的 id 字段没有重复值,但是添加主键就提示违反唯一性约束

  • 首先检查该列有没有唯一索引,如果有的话就不能添加主键。
    例如:
create table "SYSDBA"."TABLE_1"("COLUMN_1" CHAR(10) unique ,"COLUMN_2" CHAR(10));
--[执行语句1]:
alter table "SYSDBA"."TABLE_1" add primary key("COLUMN_1");
--执行失败(语句1)
--第1 行附近出现错误[-2864]:
--表中已存在这样的唯一关键字或主键
  • 使用 trim 函数找到存在重复的数据,去除重复值后就可以正常使用唯一性约束
CREATE TABLE "SYSDBA"."TEST"("T1" INT,"T2" VARCHAR(50));

insert into "SYSDBA"."TEST"("T1", "T2") VALUES(1,'TEST1');
insert into "SYSDBA"."TEST"("T1", "T2") VALUES(1,'TEST2');
insert into "SYSDBA"."TEST"("T1", "T2") VALUES(2,'TEST3');
insert into "SYSDBA"."TEST"("T1", "T2") VALUES(2,'TEST4');
insert into "SYSDBA"."TEST"("T1", "T2") VALUES(3,'TEST5');

--给有重复数据的列添加主键,就会报错:-6612  错误消息: 违反唯一性约束
alter table "SYSDBA"."TEST" add primary key("T1");

--你用 trim 函数查询重复值
select trim(T1) as ,count(*) from "SYSDBA"."TEST" group by trim(T1) having count(*) >1

结果

表创建完成后怎么修改字段顺序?

表创建之后不能修改顺序。可以把当前的表删掉重新建一下(删除之前做好备份),然后将数据重新导入。

是否支持 IF 函数

【问题描述】:

否支持 IF 函数,如 IF(TRUE,'A','B')/ 自定义函数实现 IF 功能

【问题解答】:

支持/使用 IF 函数,或者使用 DECODE 函数来实现。具体可以参考安装目录 doc 目录下的《DM_SQL 语言使用手册》。

NVARCHAR2 和 VARCHAR2、VARCHAR 的区别

Oracle 的这三个类型有区别,DM 的这三个类型是没有区别的。

达梦不支持布尔类型,请问可以使用什么替代?

可以通过 BIT 类型替代,具体语法请参考 BIT 类型语法

达梦可以给自增列赋值么?

可以,可以通过设置 SET IDENTITY_INSERT [< 模式名 >.]< 表名 > ON ;的形式进行赋值插入指定的数据,设置 on 之后会自动还原成 off;不会影响到后续自增列的使用。

示例如下:

create table "SYSDBA"."TABLE_5"
(
"ID" INTEGER identity(1, 1) not null ,
"NAME" CHAR(50),
primary key("ID")
);
--插入数据
insert into "SYSDBA"."TABLE_5"("NAME") VALUES('测试数据001');
insert into "SYSDBA"."TABLE_5"("NAME") VALUES('测试数据002');
insert into "SYSDBA"."TABLE_5"("NAME") VALUES('测试数据003');
SET IDENTITY_INSERT SYSDBA.TABLE_5 ON;--设置自增列插入
insert into "SYSDBA"."TABLE_5"("ID","NAME") VALUES(5,'测试数据004');--插入指定数据给自增列
insert into "SYSDBA"."TABLE_5"("NAME") VALUES('测试数据005');
select * from "SYSDBA"."TABLE_5";

结果

达梦如何判断表中的字段是否为自增列?如何用脚本查询?

可以通过 SYSCOLUMNS 的 INFO2 来确认,查询自增列表和列可以使用以下语句查询;

select
        b.TABLE_NAME,
        a.NAME as COL_NAME
from
        SYS.SYSCOLUMNS a,
        all_tables b    ,
        SYS.SYSOBJECTS c
where
        a.INFO2&0x01=0x01
    and a.ID        =c.ID
    and c.NAME      =b.TABLE_NAME;

达梦数据库中整数相除如何让结果为小数?如 select TRUNCATE((1/5),4) from dual

方法 1:将分子或者分母设置为小数。

select TRUNCATE((1/5.0),4) from dual

方法 2:执行以下语句修改 dm.ini 参数并重启数据库服务。

SP_SET_PARA_VALUE(2,'CALC_AS_DECIMAL',1);

达梦有类似 MySQL 的 conv() 进制转换的的函数么?

可以使用语法:RAWTOHEX (binary)

功能:将 RAW 类数值 binary 转换为一个相应的十六进制表示的字符串。binary 中的 每个字节都被转换为一个双字节的字符串。 RAWTOHEX 和 HEXTORAW 是两个相反的函数。

例:

SELECT RAWTOHEX('达梦数据库有限公司');

查询结果为:B4EFC3CECAFDBEDDBFE2D3D0CFDEB9ABCBBE

SELECT RAWTOHEX('13');

查询结果为:3133

达梦如何用脚本导出某个模式下全部表定义

  • 方法一,将模式下的表定义生成到表中
--创建表存放表定义数据
drop table sch_table_ddl;
create table sch_table_ddl(tab_name VARCHAR, tab_ddl clob);

--创建存储过程获取表定义,并存放到指定表中
create or replace procedure get_schema_table_ddl(sch_name VARCHAR(30))
as
begin
for rec in (select table_name from dba_tables where owner = sch_name and table_name != 'SCH_TABLE_DDL')
loop
insert into sch_table_ddl values (rec.table_name,dbms_metadata.get_ddl('TABLE',rec.table_name,sch_name));
commit;
end loop;
EXCEPTION when others
then 
dbms_output.Put_line('导出异常');
end;

--测试获取SYSDBA模式下的表定义
call get_schema_table_ddl('SYSDBA');

--查询表定义
SELECT TAB_NAME,TO_CHAR(TAB_DDL) FROM sch_table_ddl;

  • 方法二,将模式下的表定义生成到脚本文件
--创建存放ctrl文件的路径
CREATE OR REPLACE DIRECTORY TMP AS 'D:\dmdbms\data\DAMENG';

CREATE OR REPLACE
PROCEDURE SP_sch_table_ddl
IS
        FILE_HANDLE UTL_FILE.FILE_TYPE;
        WRITE_CONTENT   clob;
        TAB_NAME varchar(30);
        CURSOR CUR_SP_OUT
        IS
        select table_name from dba_tables where owner = 'SYSDBA' and table_name != 'SCH_TABLE_DDL';
BEGIN
                FILE_HANDLE     := UTL_FILE.FOPEN('TMP', 'SYSDBA_DDL.sql', 'W');
        OPEN CUR_SP_OUT;
        LOOP
        --此处写入内容为表的列名
                FETCH CUR_SP_OUT INTO TAB_NAME;
                EXIT  WHEN CUR_SP_OUT%NOTFOUND;
                WRITE_CONTENT   := dbms_metadata.get_ddl('TABLE',TAB_NAME,'SYSDBA');
                --写入文件
                IF UTL_FILE.IS_OPEN(FILE_HANDLE) THEN
                        UTL_FILE.PUT_LINE(FILE_HANDLE, WRITE_CONTENT);
                END IF;
                --关闭文件
        END LOOP;
          UTL_FILE.FCLOSE(FILE_HANDLE);
END;


CALL SP_sch_table_ddl;

子查询中存在相同列名,进行查询时,报有歧义的列名

【问题描述】:

 --创建表test,
CREATE TABLE "SYSDBA"."TEST"
(
"ID" INT,
"NAME" VARCHAR(100));
--使用如下语句进行查询
select * from (select ID,ID,NAME from test) T1;

报错,有歧义的列名:

有歧义的列名

【问题解答】:

修改 ERROR_COMPATIBLE_FLAG 参数可绕过;

CALL SP_SET_PARA_VALUE(1,'ERROR_COMPATIBLE_FLAG',0);

收集列的统计信息时是否会收集索引统计信息

【问题说明】:

--收集指定用户下所有表所有列的统计信息:
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

这个语句是否会收集索引的统计信息?

【问题解答】:

会收集,测试如下:

select * from "TEST"."TEST_WL";
--新建索引
create index idx_test_wl_id on test.test_wl(id);
--查看索引统计信息情况
dbms_stats.index_stats_show('TEST','IDX_TEST_WL_ID');
--收集模式下的列统计信息
dbms_stats.gather_schema_stats('TEST',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--再次查看索引统计信息即可查看到统计信息情况

在客户端做 TEXT 数据类型查询时,无法直接看到文本内容

可以查出来,也可以用 “=” 作匹配,只是在客户端查出来的结果也一般是显示为:< 长文本 >

如果想要在客户端工具直接显示成文本,一般对大字段进行查询都要进行特殊处理,有两种查询大字段方式:

  • cast/to_char

如:

select id ,cast(t_text as varchar) from test;
  • dbms_lob.substr

如:

select id ,dbms_lob.substr(t_text,8000) from test;

Mysql 的 replace into 语法,用达梦怎么实现

达梦可以用 merge into 实现 replace into 操作。详细操作可以参考安装目录 DOC 目录下的 -- DM SQL 语言使用手册。

举例:

例如 MySQL 中,用 replace into 指令替换属性 id,numbers,age 的值分别为 2,100,15:

replace into test(code,number,age) values(2,100,15)

在达梦数据库中,使用 merge into 替换为:

merge into table1
using (select 2 code,100 number,15 age from dual) t
on(table1.code = t.code)
when matched then
update set table1.number=t.number,table1.age=t.age
when not matched then
insert (code,number,age) values(t.code,t.number,t.age)

xml 解析库未加载

使用包内的过程和函数之前,如果还未创建过系统包。请先调用系统过程创建系统包。

SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_LOB');
SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_XMLGEN');
SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_OUTPUT');
SET SERVEROUTPUT ON;

SQL 语法错误:无效的表或视图名[dual]

【问题解决】

dual 前面不要写模式名,用法如下:

select 1 from dual;

此外,在 DM6 数据库中,dual 伪表在 SYSTEM 库的 SYSDBA 模式下,非 SYSDBA 用户访问时需要带上库名和模式名来访问,如果需要直接访问可以用 SYSDBA 创建一个 PUBLIC 同义词:

--创建 public 同义词
CREATE PUBLIC SYNONYM dual for SYSTEM.SYSDBA.SYSDUAL;

--创建完成后,用户库中的非 SYSDBA 用户就可以直接访问
select to_date(sysdate,'yyyy-MM-dd') from dual;

在 disql 中,语句用 ";" 无法结束

【问题描述】

disql 中,用";" 无法结束,或报错:The script file is not complete,last sql has not been executed.

【问题解决】

创建触发器,存储过程,函数,包,模式等时需要用“/”结束。

IP 函数 inet_aton()和 inet_ntoa()怎么转换成达梦语法?

目前达梦数据库没有这两个函数,但是可以用基础函数实现,如下:

样本数据
IP格式: 192.168.117.181   对应的数字格式:3232265653

inet_ntoa()     --整数转IP

select
   trunc(ip/16777216)||'.'|| trunc( mod(ip, 16777216)/65536) ||'.'|| trunc(mod(ip,65536)/256)||'.'|| trunc(mod(ip,256)) as ip_address
from
   (select 3232265653 as ip from dual);

输出:192.168.117.181

inet_aton()

select
   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\1')) * 16777216 +
   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\2')) * 65536 +
   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\3')) * 256 +
   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\4'))  as ip_number
from
   (select '192.168.117.181' as ip from dual);

输出:3232265653

如何利用数据库取得一个唯一值编号?

  • 可以利用自增列取得唯一值。
CREATE TABLE T
(
ID INT IDENTITY(1,1),
MEMO VARCHAR(256)
);
INSERT INTO T VALUES('A');
COMMIT;
SELECT * FROM T;
  • 可以使用序列取得唯一值。
CREATE SEQUENCE SEQ_1 INCREMENT BY 1;
CREATE TABLE T_SEQ(ID INT,C1 DATE);
INSERT INTO T_SEQ VALUES(SEQ_1.NEXTVAL,SYSDATE());
COMMIT;
SELECT * FROM T_SEQ;
  • 如果是字符串,可以使用 GUID 函数返回唯一的值。
SELECT GUID FROM DUAL;

达梦整数或者整除的显示,如何保持与 Oracle 相同?

达梦数据库整数除法等运算默认舍弃小数,不会进行四舍五入,与 Oracle 不同。
达梦数据库中:

SQL> SELECT 123456/10000 FROM DUAL;
行号       123456/10000
---------- ------------
1          12

Oracle 中:

SQL> SELECT 123456/10000 FROM DUAL;
123456/10000
------------
    12.3456

达梦数据库使用 to_char(CEIL(表达式))结果返回为科学记数法

SQL> select to_char(ceil((SYSDATE - to_date('2020-10-26 10:39:05','YYYY-MM-DD hh24:mi:ss')) * 24 * 60)) from dual;
行号       TO_CHAR(CEIL((SYSDATE-TO_DATE('2020-10-2610:39:05','YYYY-MM-DDhh24:mi:ss'))*24*60))
---------- -----------------------------------------------------------------------------------
1          6.31E2

此时 Oracle 返回整数

可通过修改参数设置,使达梦输出与 Oracle 一致:

修改 CALC_AS_DECIMAL 参数值为 2,并重启数据库生效并验证。

sp_set_para_value(2,'CALC_AS_DECIMAL',2);

需重启数据库生效;

blob 字段用 length 取长度报错:参数不兼容

对 blob 字段取长度,无法使用 length, 可以使用系统包 DBMS_LOB.GETLENGTH(列名)或者 lengthb(列名);

Select  dbms_log.getlength(file_content) from  AR_FILE;

PIVOT 行列转换函数报错

行列转换 PIVOT 子句,必须要包含一个聚合函数,聚合函数分别是:COUNT、SUM、MAX、MIN、AVG,针对不同的使用场景,选择聚合函数。

语法格式:

pivot (聚合函数(列名) for 被转换的列名 in(要显示出来的列名))

select
        *
from
        table_name pivot(max(column_name)  --行转列后的列的值value,聚合函数是必须要有
        for column_name in(value_1, value_2, value_3) --需要行转列的列及其对应列的属性1/2/3
        );

示例说明:

create table tmp as select * from (
select '张三' student,'语文' course ,78 score from dual union all
select '张三','数学',87 from dual union all
select '张三','英语',82 from dual union all
select '张三','物理',90 from dual union all
select '张三','物理',92 from dual union all
select '李四','语文',65 from dual union all
select '李四','数学',77 from dual union all
select '李四','英语',65 from dual union all
select '李四','物理',85 from dual);

--PIVOT行转列
select
        t.*,
        (t.语+t.数+t.外+t.物) as total
from
        (
                select
                        *
                from
                        tmp pivot ( max(score) for course in ('语文' as 语, '数学' as 数, '英语' as 外, '物理' as 物) )
        )
        t;

--返回结果集

张三   78   87   82   182   429

李四   65   77   65   85   292

字符串太长,导致 LISTAGG 函数报“字符串截断”如何处理?

处理方法:将 LISTAGG 改成 LISTAGG2。示例如下:
1、创建测试表和造测试数据:

--创建测试表test
create table test
as select level id ,rpad('A',1000,'B') c1 FROM DUAL CONNECT BY LEVEL<=10;
--插入测试数据
BEGIN
for I in 1..10
LOOP
INSERT INTO TEST
SELECT * FROM TEST;
END LOOP;
commit;
END;
--查询测试表的总数据量
select count(*) from test;--10240

2、用 listagg 函数去执行 sql 语句

SELECT ID ,LISTAGG(C1,'->') WITHIN GROUP (ORDER BY ID)
FROM TEST
GROUP BY ID;
--报错,字符串截断

3、将 listagg 修改成 listagg2,再次执行该 sql 语句

SELECT ID ,LISTAGG2(C1,'->') WITHIN GROUP (ORDER BY ID)
FROM TEST
GROUP BY ID;   --成功

查询语句中如何按照自定义的顺序进行排序

【问题描述】

现有需求要根据自定义的顺序进行排序,例如:现有 PERSON 表,需要将第 7 条数据作为第一行,第 3 条数据作为第二行,第 2 条数据作为第三行进行输出,表中数据如图:

PERSON 表数据

【问题解决】

  • 利用 CASE 语句:
SELECT * FROM PERSON.PERSON WHERE PERSONID IN (2,3,7)
ORDER BY CASE PERSONID
             WHEN 7 THEN 1       ---将 PERSONID=7 的数据作为第一行输出
             WHEN 3 THEN 2       ---将 PERSONID=3 的数据作为第二行输出
             WHEN 2 THEN 3       ---将 PERSONID=2 的数据作为第三行输出
        END;
  • 利用 DECODE 函数:
SELECT * FROM PERSON.PERSON WHERE PERSONID IN (2,7,3)
ORDER BY DECODE(PERSONID,7,1,3,2,2,3); 

以上两种方法均可得到以下输出结果:

EMPLOYEES 表数据

sql 快捷命令:授予用户对某一模式的只读权限,并查询

select ‘grant select  on 模式名.’||table_name||’  to 只读用户;’ from dba_tables where owner=‘模式名’;

---例如授予 READER 用户对 SYSDBA 模式下所有表的只读权限,并查询:
select 'grant select on SYSDBA.'||table_name||' to READER' from dba_tables where owner='SYSDBA';  

或者

select 'grant select on '||owner||'.'||object_name||' to 用户名;' from dba_objects where owner in ('当前用户')  and object_type='TABLE';

---例如授予 READER 用户对 DMHR 模式下所有表的只读权限,并查询:
select 'grant select on '||owner||'.'||object_name||' to READER;' from dba_objects where owner in ('DMHR') and object_type='TABLE';  

使用 UTL_FILE 包报错:utf.file.open 无访问权限

【问题解决】

  • 读功能,当读取的文件 dmdba 用户无法访问时会报无权限访问问题,修改其用户属组或者权限即可。
  • 写功能,达梦为安全考虑,默认调用系统包只能写入特定目录,即库目录,可以通过以下语句查询到目录路径:
SELECT * FROM V$DM_INI WHERE PARA_NAME LIKE '%SYSTEM_PATH%';

如何查询一个表涉及到的所有触发器

【问题解决】

通过以下语句进行查询:

select * from dba_triggers where DBA_TRIGGERS.OWNER = '模式名' AND DBA_TRIGGERS.TABLE_NAME = '表名'

dm 到 oracle 的 dblink 环境中,在 dm 上调用 oracle 包中的存储过程提示:获取对象失败

【问题描述】

在 DM 上调用 oracle 数据库中的 ceshi_pkg 包的 showMessage 存储过程,出现如下报错:

报错信息

【问题解决】
远程调用 oracle 包里的存储过程需要加上用户名,将 SQL 语句改为如下即可:

call scott.ceshi_pkg.showMessage@link1();

当虚拟列的基列中有空数据时,查询虚拟列或者把虚拟列作为查询条件会报错:非法的参数数据

【问题描述】

表 xnl 中,pwd 字段为虚拟列,其基列是 passwd,passwd 中有空值。

利用如下语句进行查询,均出现报错:非法的参数数据

select * from "SYSDBA"."xnl"
select "id","passwd","sex","pwd" from "SYSDBA"."xnl" ;

【问题解决】

把虚拟列用 ifnull 参数处理一下即可,如下图所示:

修改虚拟列

如何利用 SQL 语句查看数据库用户限制登录和允许登录的 IP 地址

【问题解决】

---查询所有用户的 IP 黑白名单:
select a.id,b.username,a.allow_addr,a.not_allow_addr from sysuser$ a, dba_users b where a.id=b.user_id;

如何批量禁用/启用用户下的所有约束信息

【问题解决】

  • 禁用:运行以下 SQL 语句,将得到的结果复制出来在 disql 中批量执行。
select 'alter table '|| t.OWNER||'.' ||t.TABLE_NAME||' disable constraint '|| t.CONSTRAINT_NAME|| ';'
from dba_constraints t
where t.OWNER not in( 'SYSDBA','SYS');
  • 启用:运行以下 SQL 语句,将得到的结果复制出来在 disql 中批量执行。
select 'alter table '|| t.OWNER||'.' ||t.TABLE_NAME||' enable constraint  '|| t.CONSTRAINT_NAME|| ';'
from dba_constraints t
where t.OWNER not in( 'SYSDBA','SYS');

插入的数据带 & 字符,显示请输入 XXX 的值,该如何解决

【问题描述】

如下图所示,name 列插入数据带 & 字符,显示要输入 cc 的值。回车后,认定 cc 为空,因此只插入了值 aa。

插入带 & 的字段

【问题解决】

这里插入的语句中 name 字段的值‘aa & cc’中的 & 被当成了变量,因此显示需要输入 cc 的值。此时有两种解决办法:

方法一
sql 命令行中执行 set define off; 关闭替代标记功能后,重新插入数据成功。

关闭替代标记功能

方法二
通过使用转义符取消掉 & 符号的特殊含义,具体如下:全文替换:& 替换为'||'&'||',然后再导入就可以了。

转义符转义 &

TRANSLATE 剔除字符串中的字符后结果为空

【问题分析】

现需要使用 TRANSLATE 剔除字符串 a 中,包含字符集 b 的任一字符,例如在'1@111*/'中,要剔除 @、*和/中的任一字符,使其结果为'1111'。
而使用 TRANSLATE( char,from,to),只要第三个参数 to 为空,则整个结果为空。如下图所示:

剔除后为空

可以使用 regexp_replace 函数代替,如下图所示:

运行截图

执行查询语句时报错:字符串转换出错

【问题描述】

问题重现如下:

---建表,设置 ID 列的数据类型为 varchar
create table test1008(id varchar(20));
insert into test1008 values(12345678);
insert into test1008 values('12345678_2');
insert into test1008 values('12345678_3');
commit;

---按照以下语句进行查询报错:字符转换出错
select * from test1008 where id>=1;

【问题分析】

需要检查查询语句的条件列是否正确,根据表结构可以看出 ID 列数据类型为 varchar,不是数值型数据,所以需要加上单引号,修改查询语句如下即可。

select * from test1008 where id>='1';

达梦的聚集索引不支持大字段操作,将聚集索引变成非聚集索引应如何操作

【问题解决】

可通过在表上除聚集主键外的任意一列创建一个聚集索引,原聚集主键列被取代,再删除新创建的聚集索引即可。具体实例如下:

---创建测试表 TEST111,设置 ID 列为聚集主键
drop table  TEST111;
CREATE TABLE TEST111(ID int CLUSTER PRIMARY KEY,name varchar);
insert  into TEST111 select id ,name from sysobjects ;
commit;

---先用该表上除聚集主键以外的任意一列创建一个聚集索引
 create cluster index idx1_1 on TEST111(name);

---查询现在的表定义,原先在 id 上的聚集主键列,已经变成非聚集主键列
 select tabledef('SYSDBA','TEST111');

---最后在删除先前创建的 idx1_1 聚集索引
 drop   index idx1_1;

DM 中默认除数和被除数都是整数,最后的结果也被当作整数处理

【问题描述】

当被除数和除数都是整数,结果也直接默认取整数,此结果和 oracle 不一致。例如:

---dm 数据库运行结果为 0,Oracle 数据库运行结果为 0.4
select 8/20;

【问题解决】

  • 达梦数据库种默认整数相除,最后的结果集是按照整数处理。
  • 如果除数或者被除数有一个是浮点型,最后的结果都是按照浮点型处理。
---以下运行结果均为 0.4
select 24/60.0;
或者
select 24.0/60;
  • 达梦数据库还在 dm.ini 配置文件中提供了 CALC_AS_DECIMAL 参数,该参数是静态参数,需要重启数据库才生效。该参数值的含义如下:
    0:默认值,表示整数类型的除法、整数与字符或 BINARY 串的所有四则运算,结果都处理成整数;
    1:表示整数类型的除法全部转换为 DEC(0,0)处理;
    2:表示将整数与字符或 BINARY 串的所有四则运算都转换为 DEC(0,0)处理。
注意

CALC_AS_DECIMAL 参数只有在 USE_PLN_POOL 为 0 或 1 时有效。当 USE_PLN_POOL 为 2 或 3 时,按照 CALC_AS_DECIMAL=2 处理

例如:

---打开 CALC_AS_DECIMAL=1,整数类型的除法全部转换为 DEC(0,0)浮点型处理,运行结果为 0.4
select 24/60=0.4

LIKE 语句 CLOB 字段模糊查询报错:字符串截断

【问题解决】

DM7 LIKE 语句中 CLOB 类型的最大长度默认值为 31KB,DM8 LIKE 语句中 CLOB 类型的最大长度默认值为 10240KB,当查询的 CLOB 字段长度超过这个值后就会报错。
处理方法:使用命令 sp_set_para_value(1,'CLOB_LIKE_MAX_LEN',10240); 修改 CLOB_LIKE_MAX_LEN 的值,之后重启数据库生效。
CLOB_LIKE_MAX_LEN:LIKE 语句中 CLOB 类型的最大长度,单位 KB,有效值范围(8~102400),静态参数,修改后需要重启。

在一条 select 查询中无法同时执行两个 distinct()函数

【问题分析】

distinct() 函数的执行原理为从被选择出的具有重复行的每一组中仅返回一个符合筛选条件的这些行的拷贝,而同时执行两个 distinct() 函数意味着需要从被选择出的具有重复行的每一组中返回两个符合不同筛选条件的这些行的拷贝,从执行原理上产生了冲突,所以无法同时执行两个 distinct() 函数。
解决办法:将两个 distinct() 函数分别用两条 select 查询语句分次执行。

---执行报错:
select distinct(JOB_ID),distinct(MIN_SALARY) from DMHR.JOB;  

---分开执行 distinct,执行成功
select distinct(JOB_ID) from DMHR.JOB;
select distinct(MIN_SALARY) from DMHR.JOB;

插入数据时报错:违反引用约束[CONSxxxxx]

【问题解决】

若在表 A 的 fid 字段上设置了外键,引用了表 B 的 pid 字段,则当在表 A 的 fid 字段插入数据前,需确保表 B 的 pid 字段已存在相同数据,否则会因为违反引用的约束,无法正常完成数据插入。

达梦有很多内置函数,比如 sp_set_para_value, sf_set,有没有一张表或者视图可以查询出来

可通过查询 v$ifun 视图查看:

select * from v$ifun;

对于 resource 角色的用户跨用户访问其他模式对象,向外键所在表插入修改数据报错:没有引用表[T1]上的权限

【问题描述】

对于 resource 角色的用户跨用户访问其他模式对象,赋予外键和外键引用主键表的全部 DML 权限,向外键所在表插入修改数据报错:没有引用表[T1]上的权限。具体实例如下:

---建立用户 test1 和 test2,并授予默认权限
create user test1 identified by ****;
create user test2 identified by ****;

---在 test1 上创建表 t1 和 t2,并设置主键和外键
create table test1.t1(c1 int,c2 int,primary key(c1));
create table test1.t2(c1 int,c2 int,primary key(c1));
alter table test1.t2 add constraint foreign key(c2) references test1.t1(c1);

---将 test1 上表 t1 和 t2 的所有 DML 权限授予给 test2 用户
grant select,insert,delete,update on test1.t1 to test2;
grant select,insert,delete,update on test1.t2 to test2;

---使用 TEST2 用户登录数据库,执行如下语句:
INSERT INTO TEST1.T1 VALUES(1,1); --执行成功,不报错
COMMIT;
INSERT INTO TEST1.T2 VALUES(1,1);--报错:没有引用表[T1]上的权限

【问题解决】

对于存在外键的表,需要增加 references 权限的授予,赋予如下权限即可。

grant references on test1.t1 to test2;

Oracle XML 函数在 DM 中相同功能函数替代案例说明

【问题分析】

Oracle XML 函数在 DM 中有相应函数替代,ORACLE SYS_XMLAGG 在达梦数据库中可以用 XMLAGG 进行替代,XMLTABLE 可以视实际的情况使用 XMLTABLE 或者 XMLQUERY 来进行替代,同时 XMLTABLE 在写法上和 ORACLE 有一定的差异。以下简要介绍 XMLAGG 和 XMLTABLE 函数:

1.XMLAGG 函数替代案例

---Oracle端 SYS_XMLAGG 函数使用方式
select SYS_XMLAGG(xmltype(reg_data)) data from bdc_regdata where sd_id='430811005' and reg_type='14';

---DM 端 XMLAGG 函数使用方式
 select XMLAGG(xmltype(reg_data)) data from bdc_regdata where sd_id='430811005' and reg_type='14'

2.XMLTABLE 函数替代案例一

---Oracle端 SYS_XMLTABLE 函数使用方式
select * from xmltable('//抵押权登记' passing (select SYS_XMLAGG(xmltype(reg_data)) data 
  from bdc_regdata where sd_id='430811005' and reg_type='14') );

---DM 端 SYS_XMLTABLE 函数使用方式
select XMLQUERY(t.reg_data,'/不动产登记簿信息/抵押权登记信息/抵押权登记') 
   from bdc_regdata t where t.sd_id='430811005' and t.reg_type='14' ;

3.XMLTABLE 函数替代案例二

---Oracle端 SYS_XMLTABLE 函数使用方式
select szbs
  from xmltable('//目录' passing
                (select SYS_XMLAGG(xmltype(data)) data from clob_test)
                columns data_id VARCHAR2(28) PATH '不动产单元号',
                szbs number PATH '所在本数')
  where data_id = '430811005';

---DM 端 SYS_XMLTABLE 函数使用方式
select szbs
  from CLOB_TEST t,xmltable('//目录' passing (xmltype(t.data)) columns bdcid VARCHAR2(28) PATH '不动产单元号', szbs number PATH '所在本数' )
  where bdcid='430811005';

关于 XMLTABLE、XMLAGG、XMLQUERY 以及其他 XML 相关的函数使用方法以及详细介绍请参考《SQL 语言使用手册》(手册位于数据库安装路径 /dmdbms/doc 文件夹下)。

子查询语句执行报错,提示单行子查询返回多行

报错截图如下:

报错截图

【问题解决】

问题原因:子查询语句输出结果并非单值,主查询语句的 where 筛选条件中 FK_ID 的值与子查询语句的输出结果不能相互匹配,where 条件出现一对多情况。

执行截图

解决办法:根据具体业务需求在子查询语句中添加 where 筛选条件,或者在主查询语句中添加 all、any 等关键字,使子查询语句输出结果为单值,确保主查询语句的 where 筛选条件中 FK_ID 的值与子查询语句的输出结果相互匹配。查询语句修改后的执行结果如下所示:
1.在子查询语句中添加 where 筛选条件:

执行截图

2.在主查询语句中使用 all 关键字:

执行截图

3.在主查询语句中使用 any 关键字:

执行截图

插入数据到视图,报错:提示违反视图[t1_v]CHECK 约束

【问题描述】

插入数据到视图,报错,提示违反视图[t1_v]CHECK 约束,报错截图和视图数据来源表的查询结果截图如下:

报错截图

视图数据来源表的查询结果:

来源表的查询结果

【问题分析】

出现以上报错是由于在创建视图 t1_v 时指定了 witch check option 关键字,这也就是说,新增或更新后的每一条数据仍然要满足创建视图时指定的 where 条件。因为新增的记录的 id 值为 7,超过了 where 条件中限制的 t.id<6 的范围,所以报错,提示违反视图[t1_v]CHECK 约束。
解决方法:调整新增的记录的 id 值,使 id 值保持在 t.id<6 的范围内,语句即可正常执行。

BLOB 如何转换成可视化字符串

通过以下 SQL 语法进行转换:

SELECT
utl_raw.cast_to_varchar2(dbms_lob.substr(表.列))
from 表

多字段组合唯一性约束限制

【问题描述】

唯一性约束可以实现对多个字段的组合唯一性约束限制,现有如下类型的唯一性要求,如表 TEST(C1 INT,C2 INT),要求在 C1 <0 的情况下,C1 和 C2 的组合不参与唯一性判断;在 C1>=0 时,要求 C1 和 C2 的组合必须唯一。

【问题解决】

该需求可以通过条件表达式来实现:

create unique index index_ttt on test(case when  c1 <0 then null else cast(c1 as varchar)||','||cast(c2 as varchar) end);

如何查询出 varchar 字段中包含的非数字数据

【问题描述】

字段中存在 varchar 类型,正常情况存储的为数字,如何查询出字段中包含的非数字数据。如:

create table reg_test(c1 int,c2 varchar(100));
insert into reg_test values(1,'1');
insert into reg_test values(2,'11');
insert into reg_test values(3,'12');
insert into reg_test values(4,'1a3');
insert into reg_test values(4,'13a');
insert into reg_test values(4,'13a$');
insert into reg_test values(4,'13¥');
insert into reg_test values(4,'13.');

【问题解决】

可通过以下 SQL 语法解决:

select * from reg_test where not regexp_like(c2, '^[[:digit:]]*$');

如何设置部分用户禁止删除某张数据表

【问题描述】

针对数据表无法设定 TRUNCATE 操作权限,为了提高安全性,要求对部分用户禁止针对某张数据表做 truncate 和 drop 操作。应该如何设置。

【问题解决】

可以通过触发器实现权限设定:

CREATE OR REPLACE TRIGGER  tri_prevent_drop_truncate
  BEFORE TRUNCATE OR DROP ON DATABASE
BEGIN
  IF :eventinfo.objectname='表名' and :eventinfo.schemaname='用户名'
  THEN
     raise_application_error (-20000, '禁止DROP或TRUNCATE表TEST!');
  END IF;
END;

使用 to_date 函数进行数据类型转换过程中出现报错:非法的时间日期类型数据

【问题描述】

使用 to_date 函数进行数据类型转换过程中出现报错:非法的时间日期类型数据。如下图所示:

报错截图

【问题解决】

可以通过修改 DATETIME_FAST_RESTRICT 参数为 0 解决。如下图所示:

报错截图

注意

DATETIME_FAST_RESTRICT为动态系统级参数(默认为1),TO_DATE(字符串,FAST格式)或 ALTERSESSION 设置了 DATE 的格式为 FAST 格式后,CAST 字符串 AS DATE 时: 0:字符串可以带时间; 1:字符串不允许带时间,带时间会报错。 其中FAST格式(标准格式)为:YYYY-MM-DD、YYYY/MM/DD、YYYY:MM:DD、YYYY.MM.DD。

MySQL 迁移到 DM 的存储过程运算结果与原库不一致

【问题描述】

MySQL 中执行 SELECT SIGN(65^4)^1*100/10; 运算结果为 0;而 DM 数据库中执行结果为 11。

【问题分析】

此问题是由于 MySQL 和 DM 数据库运算优先级不一致导致。

DM 数据库运算优先级:

DM 数据库运算优先级

MySQL 数据库运算优先级:

MySQL 数据库运算优先级

因此 MySQL 中该运算 SELECT SIGN(65^4)^1*100/10; 实际执行顺序为:select (sign(65^4)^1)*100/10;
DM 中该运算 SELECT SIGN(65^4)^1*100/10; 实际执行顺序为:select sign(65^4)^(1*100/10);

解决方法:在达梦的过程中实现与 MySQL 一样的顺序,对异运算加括号干预:select (sign(65^4)^1)*100/10;

创建索引后,在管理工具导航栏索引下看不到索引信息

【问题描述】

执行以下语句创建索引,命令执行成功,但在管理工具中无法查看索引信息。

create table test(id int,name varchar(20));
create index idx_test_id on test('id');

【问题解决】

该问题的原因是引号使用错误:单引号在数据库中表示字符串。
用单引号括起来表示索引目标是一个常量字符串,而不是某个字段。但针对某个 id 对象创建索引时,应该直接使用字段名称,而不应该用单引号括起来。
以上创建过程使用以下命令查看索引类型为函数索引,而非普通索引,所以不会在管理工具中显示。

---查看索引类型为函数索引
select index_type from "SYS"."DBA_INDEXES" where index_name='IDX_TEST_ID';

正确创建索引方法:

---方法一:不加双引号
create index idx_test_id on test(id);

---方法二:用双引号将字段名称括起来
create index idx_test_id on test("ID");

count 查询某张表显示为空,但是 select * from 查询某张表是有数据的

【问题描述】:

count 查询某张表显示为空,但是 select * from 某张表是有数据的。

【问题解决】:

通过看两条语句的执行计划可以发现,两个计划走的索引不一致,select *语句计划走的索引为表自带索引,count 语句计划走的索引为 BM$_33557020,此索引为位图索引,删除此位图索引解决。

SQL> explain select count(*) from ZWFW_XWSJZX.XWXT_HISTORYDATA_OPT_LOGS;
      #NSET2:[1,1,4]
           #PRJT2:[1,1,4];exp_num(1),is_atom(FALSE)
               #AAGR2:[1,1,4];grp_num(0),sfun_num(1),distinct_f1ag[0]; slave_empty(0)
                    #CSCN2:[1,1,4];INDEX33557021(BM$_33557020) 
已用时间:0.522(毫秒).执行号:0.

SQL> explain select top 2 * from ZWFW_XWSJZX. XWXT_HISTORYDATA_OPT_LOGS;
      #NSET2:[2621,2,482]
           #PRJT2:[2621,2,482];exp_num(12),is_atom(FALSE)
               #TOPN2:[2621,2,482];top_num(2)
                   #CSCN2:[2621,12871527,482];INDEX33555462(XWXT_HISTORYDATA_OPT LOGS) 
已用时间:0.557(毫秒).执行号:0

查询数据时怎么样可以不带模式名

【问题解决】

方式一:通过使用模式所属的用户登录,查询时可以只用写表名。

方式二:通过设置 set schema 模式名 ,使得其他用户也可以不用加模式名操作该模式的对象。此种方法的前提是需要登录的用户具有该模式对象的权限。

未设置模式时:

image.png

设置模式后查询成功:

image.png

数据库报错 select 包含过多表

【问题描述】

当 select 后查询不同表的列时,其中表的数量超过了时,报错:“select 包含过多表(不能超过 100 个)”

image.png【解决方法】

可以在 v$dm_ini 视图中直接找到 MAX_TABLES_IN_SELECT 参数,对其进行修改,但是需要注意的是,该参数值上限为 150。

查看默认参数值:

select * from  v$dm_ini WHERE para_name = 'MAX_TABLES_IN_SELECT';

image.png

修改参数值为 110:

SP_SET_PARA_VALUE(1,'MAX_TABLES_IN_SELECT',110);

image.png

再次执行查询语句,查询成功。

image.png

注意

MAX_TABLES_IN_SELECT 该参数属于系统会话级参数,修改参数值后只对该会话生效,参数值上限为150。

微信扫码
分享文档
扫一扫
联系客服