DBA大师实战国产数据库系列——达梦6.0之SQL篇

文章来自达梦技术社区

　　作者简介：杨廷琨（网名Yangtingkun），现任海虹医药电子商务有限公司首席DBA， ITPUB论坛Oracle数据库管理版版主。2004年曾参与编写《Oracle数据库性能优化》一书，2007年被Oracle公司授予Oracle ACE称号，喜欢研究Oracle相关的技术问题，他的技术博客上积累了1500多篇Oracle相关的原创技术文章。

　　前几天ITPUB的熊建国主编和我联系，希望我能参加国产数据库达梦的适用活动，并写几篇使用感受。本来最近手工的事情比较多，本打算推辞的，不过熊主编再三邀请，而且强调并非是枪手文，只要写出真实使用感受即可。既然如此，我就本着支持国产数据库的原则，写几篇试用感受。

　　由于本人唯一熟悉的数据库就是Oracle，因此所有的对比都是与Oracle数据库进行对比，在这个过程中，将尽可能避免将对Oracle数据库的喜爱之情带进来，争取站在一个比较公正的位置上来进行评价。

　　这一篇简单介绍一下达梦数据库SQL相关的内容。

　　利用上一篇文章搭建的测试环境，首先登陆数据库：

C:\dmdbms\bin>isql

isql V6.0.2.51-Build(2009.12.23)

SQL>login

server name:localhost

user name:test

password:

port:12345

dm_login time used:176.475(ms)

SQL>

　　首先测试DML语句，达梦数据库支持SQL92标准，且兼容Oracle8i的语法，下面测试一下：

SQL>INSERT INTO T

2   VALUES (1, TEST, SYSDATE);

INSERT INTO T

VALUES (1, TEST, SYSDATE)

1 rows affected

time used: 0.343(ms) clock tick:551330.

SQL>INSERT INTO T

2   VALUES (2, TEST AGAIN, TO_DATE(2010-3-31, YYYY-MM-DD));

INSERT INTO T

VALUES (2, TEST AGAIN, TO_DATE(2010-3-31, YYYY-MM-DD))

1 rows affected

time used: 0.516(ms) clock tick:848580.

SQL>UPDATE T

2   SET NAME = NEWNAME

3   WHERE ID = 2;

UPDATE T

SET NAME = NEWNAME

WHERE ID = 2;

1 rows affected

time used: 0.498(ms) clock tick:818000.

SQL>INSERT INTO T

2   SELECT ROWNUM + ID, NAME, CREATE_DATE

3   FROM T;

INSERT INTO T

SELECT ROWNUM + ID, NAME, CREATE_DATE

FROM T;

2 rows affected

time used: 67.481(ms) clock tick:112655890.

SQL>COMMIT;

COMMIT;

time used: 11.671(ms) clock tick:19334860.

SQL>SELECT * FROM T;

SELECT * FROM T;

id              name            create_date

1       1       TEST    2010-03-31

2       2       NEWNAME 2010-03-31

3       2       TEST    2010-03-31

4       4       NEWNAME 2010-03-31

4 rows got

time used: 0.385(ms) clock tick:631120.

SQL>DELETE T WHERE ID = 3;

DELETE T WHERE ID = 3;

0 rows affected

time used: 0.442(ms) clock tick:721900.

SQL>DELETE T WHERE ID = 4;

DELETE T WHERE ID = 4;

1 rows affected

time used: 0.475(ms) clock tick:780110.

SQL>SELECT * FROM T;

SELECT * FROM T;

id              name            create_date

1       1       TEST    2010-03-31

2       2       NEWNAME 2010-03-31

3       2       TEST    2010-03-31

3 rows got

time used: 0.321(ms) clock tick:520970.

　　最基本的SELECT、INSERT、UPDATE、DELETE操作没有问题，甚至连ORACLE语法中的ROWNUM都支持。在ISQL显示结果中，有一个自动的行号显示，但是列名显示的时候没有将这一位空出来，使得ID列跑到了行号的位置上，比较容易引起误解。

SQL>MERGE INTO T

2   USING (SELECT ID + 1 ID, NAME, CREATE_DATE FROM T) T1

3   ON (T.ID = T1.ID)

4   WHEN MATCHED THEN UPDATE

5   SET T.NAME = T1.NAME, T.CREATE_DATE = T1.CREATE_DATE

6   WHEN NOT MATCHED THEN INSERT

7   VALUES (T1.ID, T1.NAME, T1.CREATE_DATE);

　　第1行: INTO附近有语法错误

SQL>SELECT * FROM T

2   START WITH ID = 1

3   CONNECT BY PRIOR ID = ID + 1;

SELECT * FROM T

START WITH ID = 1

CONNECT BY PRIOR ID = ID + 1;

id              name            create_date

1       1       TEST    2010-03-31

1 rows got

time used: 54.497(ms) clock tick:90706490.

　　Oracle9i新增的MERGE语句果然是不支持的，而Oracle特有的树形查询居然是可以的，甚至连LEVEL伪列都是支持的：

SQL>SELECT T.*, LEVEL

2   FROM T

3   START WITH ID = 1

4   CONNECT BY ID = PRIOR ID + 1;

SELECT T.*, LEVEL

FROM T

START WITH ID = 1

CONNECT BY ID = PRIOR ID + 1;

id              name            create_date             LEVEL

1       1       TEST    2010-03-31      1

2       2       NEWNAME 2010-03-31      2

3       2       TEST    2010-03-31      2

3 rows got

time used: 35.683(ms) clock tick:59512460.

　　下面看看对其他Oracle特有的语法支持的如何：

SQL>SELECT *

2   FROM T, T1

3   WHERE T.ID = T1.ID;

SELECT *

FROM T, T1

WHERE T.ID = T1.ID;

id              name            create_date             ID

1       1       TEST    2010-03-31      1

1 rows got

time used: 0.521(ms) clock tick:855860.

SQL>SELECT *

2   FROM T, T1

3   WHERE T.ID = T1.ID (+);

SELECT *

FROM T, T1

WHERE T.ID = T1.ID (+);

id              name            create_date             ID

1       1       TEST    2010-03-31      1

2       2       NEWNAME 2010-03-31      NULL

3       2       TEST    2010-03-31      NULL

3 rows got

time used: 36.644(ms) clock tick:61195700.

　　不但普通的连接支持，连Oracle的外连接’+’都是支持的。

SQL>SELECT *

2   FROM

3   (

4       SELECT ROWNUM RN, A.*

5       FROM

6       (

7               SELECT T.*

8               FROM T, T1

9               WHERE T.ID = T1.ID(+)

10              ORDER BY 2, 1

11      ) A

12      WHERE ROWNUM <= 5

13  )

14  WHERE RN > 1;

SELECT *

FROM

(

        SELECT ROWNUM RN, A.*

        FROM

        (

                SELECT T.*

                FROM T, T1

                WHERE T.ID = T1.ID(+)

                ORDER BY 2, 1

        ) A

        WHERE ROWNUM <= 5

)

WHERE RN > 1;

RN              id              name            create_date

1       2       1       TEST    2010-03-31

2       3       2       TEST    2010-03-31

2 rows got

time used: 30.910(ms) clock tick:51615050.

　　Oracle的标准分页语句是支持的，查询子查询语句是支持的，ORDER BY常量代替列名也是支持的。

SQL>SELECT ROW_NUMBER() OVER(ORDER BY NAME, ID DESC) RN,

2   ID,

3   NAME

4   FROM T;

　　第1行: (附近有语法错误。显然分析函数是不支持的，虽然分析函数在8i就出现了，不过确实属于Oracle比较特有的技术，达梦不支持分析函数并不意外。

SQL>SELECT NAME, MAX(ID)

2   FROM T

3   GROUP BY NAME

4   HAVING COUNT(*) > 1;

SELECT NAME, MAX(ID)

FROM T

GROUP BY NAME

HAVING COUNT(*) > 1;

NAME

1       TEST    2

1 rows got

time used: 0.583(ms) clock tick:963240.

SQL>SELECT NAME, AVG(ID)

2   FROM T

3   GROUP BY ROLLUP(NAME);

SELECT NAME, AVG(ID)

FROM T

GROUP BY ROLLUP(NAME);

　　无效的存储过程名 ROLLUP .error code = -1024。显然聚集函数、GROUP BY语句和HAVING语句都是支持的。但是8i开始Oracle对GROUP BY提供了ROLLUP和CUBE功能，而这显然是达梦所不支持的，既然ROLLUP和CUBE不支持，那么对应的GROUPING等CUBE和ROLLUP专用的函数肯定也是不支持的。

SQL>SELECT T.ID, T1.ID ID1

2   FROM T, T1

3   WHERE T.ID = T1.ID (+)

4   ORDER BY 2 NULLS FIRST;

　　第4行: NULLS附近有语法错误。支持ORDER BY语句，但是Oracle的NULLS LAST和NULLS FIRST语法并不支持。

SQL>SELECT ID, (SELECT ID FROM T1 WHERE T1.ID = T.ID)

2   FROM T;

SELECT ID, (SELECT ID FROM T1 WHERE T1.ID = T.ID)

FROM T;

ID

1       1       1

2       2       NULL

3       2       NULL

3 rows got

time used: 0.556(ms) clock tick:916730.

　　将子查询作为查询列的方式居然也是支持的，这是没有想到的。

SQL>SELECT DECODE(ID, 1, A, 2, B, C)

2   FROM T;

SELECT DECODE(ID, 1, A, 2, B, C)

FROM T;

1       A

2       B

3       B

4       C

4 rows got

time used: 32.734(ms) clock tick:54721350.

SQL>SELECT CASE ID WHEN 1 THEN A WHEN 2 THEN B ELSE C END

2   FROM T;

SELECT CASE ID WHEN 1 THEN A WHEN 2 THEN B ELSE C END

FROM T;

1       A

2       B

3       B

4       C

4 rows got

time used: 0.439(ms) clock tick:724740.

　　达梦还支持DECODE函数和CASE语法。

SQL>SELECT * FROM T;

SELECT * FROM T;

id              name            create_date

1       1       TEST    2010-03-31

2       2       NEWNAME 2010-03-31

3       2       TEST    2010-03-31

3 rows got

time used: 0.385(ms) clock tick:630330.

SQL>DELETE T WHERE ID = 2 AND ROWNUM = 1;

DELETE T WHERE ID = 2 AND ROWNUM = 1;

1 rows affected

time used: 0.611(ms) clock tick:1004030.

SQL>ALTER TABLE T ADD PRIMARY KEY (ID);

ALTER TABLE T ADD PRIMARY KEY (ID);

time used: 14.329(ms) clock tick:23647990.

SQL>ALTER TABLE T1 ADD PRIMARY KEY (ID);

ALTER TABLE T1 ADD PRIMARY KEY (ID);

time used: 12.599(ms) clock tick:20886900.

SQL>UPDATE

2   (SELECT T.ID, T.NAME

3   FROM T, T1

4   WHERE T.ID = T1.ID)

5   SET NAME = A;

　　第2行: (附近有语法错误。

SQL>UPDATE T A

2   SET (NAME, CREATE_DATE) =

3       (SELECT NAME, CREATE_DATE FROM T WHERE ID = A.ID);

　　第3行: SELECT附近有语法错误。最后测试了一下UPDATE子查询方式，显然这也是达梦数据库所不支持的，不过这种语法在Oracle8i中已经出现了。另一种通过子查询同时更新多个列的写法也是不支持的。

SQL>login

server name:localhost

user name:sysdba

password:

port:12345

dm_login time used:43.856(ms)

SQL>select * from dual;

select * from dual;

SYSDUAL_COL

1       1

1 rows got

time used: 40.933(ms) clock tick:68373830.

　　达梦为了兼容Oracle还特意构造了一个DUAL表。虽然字段名称以及记录的值和Oracle并不相同，但是无论是字段名还是记录的值都是不重要的，重要的是表中只有一个字段且只有一条记录。

简单总结一下，达梦数据库在文档中说已经支持Oracle8i的语法，当时看到这种说明心里不以为然，要知道Oracle有很多语法是SQL92标准所不支持的，比如查询子查询、外连接、树形查询等等。但是测试的结果确实出乎意料，进行了众多的测试，既然只有寥寥几种写法是达梦不支持的，其中还要去掉MERGE语句，因为这是9i才引入的新特性。

　　除了支持SQL92语法，以及一些常见的语法外，达梦还支持下列Oracle特有的语法：包括树形查询、外连接、ROWNUM伪列、LEVEL伪列、DUAL表、标准分页查询、甚至包括子查询作为查询列。其中达梦对于树形查询的支持比较出乎意料，不仅支持LEVEL伪列，还支持CONNECT_IS_LEAF和CONNECT_IS_CYCLE伪列，还支持CONNECT BY NOCYCLE语句，以及CONNECT_BY_ROOT操作、SYS_CONNECT_BY_PATH函数。虽然达梦中所有树形查询的语法并没有超过Oracle中的语法，但是这些伪列、函数和操作已经远远超过8i中Oracle的语法了，已经完全可以支持Oracle10g中树形查询的语法和功能了。

　　而在不支持的特性包括分析函数、ROLLUP和CUBE相关的GROUP BY语句、ORDER BY语句中的NULLS FIRST或NULLS LAST语句、UPDATE或DELETE子查询语句。其中分析函数不支持再正常不过，这是Oracle提供的一个独特的强大工具，能在记录行级进行排序、分区等复杂的操作。而GROUP BY的ROLLUP和CUBE的实现应该不算太复杂，不过毕竟这部分更多的是用在数据仓库中，在OLTP环境下很少可以用到，不支持也不会有多大的影响。而ORDER BY中的NULLS FIRST和NULLS LAST语句不支持就不太应该了，这应该是经常会使用的功能，而且实现起来应该也很容易，怀疑是被遗漏的功能。至于UPDATE或DELETE子查询，这也是Oracle非常独特的写法，其中还涉及键值保留表的概念，因此不支持也是在情理之中。但是对于根据查询结果同时更新多个列的情况还是比较常见的，这种写法不但简单，而且可以避免子查询执行多次，这种UPDATE方式还是应该支持的。

　　从目前看，将8i的SQL的语句迁移到达梦数据库上而不做修改还是可行的，九成以上的功能都已经实现了。不过在Oracle的9/10/11版本中，又增加了大量的新的语法，比如MERGE、INSERT ALL、WITH、AS OF、MODEL等等，如果达梦的新版本想要兼容Oracle新版本中的语法，还是有不少难度的。

　　最后看看达梦中特有的写法，说是特有只是针对Oracle而言，这种语法早在SQLSERVER中就存在了：

SQL>SELECT TOP 2 *

2   FROM T

3   ORDER BY ID;

SELECT TOP 2 *

FROM T

ORDER BY ID;

id              name            create_date

1       1       TEST    2010-03-31

2       2       TEST    2010-03-31

2 rows got

time used: 31.185(ms) clock tick:52131540.

SQL>SELECT * FROM T

2   LIMIT 2 OFFSET 1;

SELECT * FROM T

LIMIT 2 OFFSET 1;

id              name            create_date

1       2       NEWNAME 2010-03-31

2       2       TEST    2010-03-31

2 rows got

time used: 0.469(ms) clock tick:772680.

　　前一个是获取前N条记录的方法，相当于Oracle标准分页方式，不过语法要比标准分页简单得多。后面一个语句是在结果集中获取指定位置开始的N条记录。在Oracle中可以通过ROWNUM或分析函数实现同样的功能，不过语句要复杂一些。