达梦 8 函数 —— 从常用函数到分析函数_达梦数据库常用函数

本文主要描述了关于达梦 8 中的函数，其中主要包括常用的一些函数，字符处理，空值处理和日期处理。还简单介绍了几个分析函数。具体的主要内容如下：

1 相关数据字典

在达梦 8 数据库中提供了如何查询动态性能视图的数据字典，当我们记不住具体性能视图名字时，可以通过v$dynamic_tables数据字典模糊查询。比如，这里查询和函数相关的数据字典：

SQL> select name from v$dynamic_tables where name like '%FUN%';

行号     NAME      

---------- ----------

1          V$IFUN

2          V$IFUN_ARG

V$ifun主要记录的是达梦 8 数据库中的函数，可以查询到函数的名词及id等信息。当我们要查找具体的某个函数，同样没有记住具体的名字时，可以利用模糊查询查找。

如下为查找关于表定义信息的函数：

select * from v$ifun where name like '%TABLEDEF%';

如上只是查询出和表定义相关的函数，发现这里面有多个，根据id信息查询v$ifun_arg，可以查看到具体该函数的参数需求。也就是同样是查表定义，但是会有多样化的函数定义，对应的参数个数及in/out有区别。

如下查询id 449的函数参数查询：

select * from v$ifun_arg where id=449;

应用该函数查询dmhr.employees表的定义：

查看dmhr.employees的定义：

SQL> SP_TABLEDEF('DMHR','EMPLOYEE');

行号     COLUMN_VALUE                                                                   

---------- -------------------------------------------------------------------------------

1          CREATE TABLE "DMHR"."EMPLOYEE"  (  "EMPLOYEE_ID" INT NOT NULL,  "EMPLOYEE_NAME" VARCHAR(20),  "IDENTITY_CARD" VARCHAR(18),  "EMAIL" VARCHAR(50) NOT NULL,  "PHONE_NUM" VARCHAR(20),  "HIRE_DATE" DATE NOT NULL,  "JOB_ID" VARCHAR(10) NOT NULL,  "SALARY" INT,  "COMMISSION_PCT" INT,  "MANAGER_ID" INT,  "DEPARTMENT_ID" INT,  NOT CLUSTER PRIMARY KEY("EMPLOYEE_ID"),  CONSTRAINT "EMP_EMAIL_UK" UNIQUE("EMAIL"),  CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY("DEPARTMENT_ID") REFERENCES "DMHR"."DEPARTMENT"("DEPARTMEN

2          T_ID"),  CONSTRAINT "EMP_JOB_FK" FOREIGN KEY("JOB_ID") REFERENCES "DMHR"."JOB"("JOB_ID"),  CHECK("SALARY" > 0)) STORAGE(ON "MAIN", CLUSTERBTR) ;

总体来说，可以通过v$dynamic_tables数据字典查询和函数相关的数据字典，找到v$ifun和v$ifun_arg，再通过这两个函数相关的数据字典查询和具体某个函数相关的信息。

2 常用函数

这部分介绍一些比较基础，接触较多的常用函数，基本上是围绕着字符数据和日期数据的处理。

2.1 NVL函数

这个函数是用来处理空值的，和Oracle中的用法一样。

SELECT employee_name, employee_id, NVL (commission_pct, 0) AS commission_pct

  FROM dmhr.emp;

这里关于空值处理的不仅这一个函数，还有其它函数，可以在达梦的官方手册《DM8 SQL.pdf》中查找。

2.2 GREATEST函数

SQL> SELECT GREATEST(16,NULL) FROM dual;

行号     GREATEST(16,NULL)

---------- -----------------

1          NULL

SQL> SELECT GREATEST(16,158) FROM dual;

行号     GREATEST(16,158)    

---------- --------------------

1          158

2.3 REPLACE函数

SQL> SELECT REPLACE('123456',3,NULL) FROM dual;

行号     REPLACE('123456',3,NULL)

---------- ------------------------

1          12456

SQL> SELECT REPLACE('123456',3,30) from dual;

行号     REPLACE('123456',3,30)

---------- ----------------------

1          1230456

2.4 concat函数

SQL> SELECT CONCAT ('ABC','BCD','DDD','BBB') AS "OUTPUT" FROM DUAL;

行号     OUTPUT      

---------- ------------

1          ABCBCDDDDBBB

2.5 substr函数

通过函数 substr(v.汉字,level,?)进行拆分的结果：

 SELECT v.汉字,v.首拼,LEVEL,SUBSTR (v.汉字, LEVEL, 1) AS 汉字拆分,

           SUBSTR (v.首拼, LEVEL, 1) AS 首拼拆分,

           'substr(''' || v.汉字 || ''',' || LEVEL || ',1)' AS study

      FROM v

CONNECT BY LEVEL <= LENGTH (v.汉字);

2.6 add_months和add_days函数

date 类型的数据可以直接加减天数，加减月份需要使用 add_months 函数，同时也可以使用 add_days 加减天数。

SELECT hire_date AS 聘用日期,

       add_days (hire_date, -5) AS 减5天,

       add_days (hire_date, 5) AS 加5天,

       add_months (hire_date, -5) AS 减5月,

       add_months (hire_date, 5) AS 加5月,

       add_months (hire_date, -5 * 12) AS 减5年,

       add_months (hire_date, 5 * 12) AS 加5年

  FROM dmhr.employee

 WHERE ROWNUM <= 1;

2.7 months_between函数

SELECT max_hd - min_hd 间隔天,

       MONTHS_BETWEEN (max_hd, min_hd) 间隔月,

       MONTHS_BETWEEN (max_hd, min_hd) / 12 间隔年

  FROM (SELECT MAX (hire_date) max_hd, MIN (hire_date) min_hd

          FROM dmhr.employee);

3 分析函数

3.1 listagg函数

创建测试视图

CREATE OR REPLACE VIEW v

AS

   SELECT '10' deptno, 'CLARK' name, '800' sal FROM DUAL

   UNION ALL

   SELECT '10', 'KING', '900' FROM DUAL

   UNION ALL

   SELECT '20', 'JAMES', '1000' FROM DUAL

   UNION ALL

   SELECT '20', 'KATE', '2000' FROM DUAL

   UNION ALL

   SELECT '30', 'JONES', '1150' FROM DUAL;

SQL> select * from v;

行号     DEPTNO NAME  SAL

---------- ------ ----- ----

1          10     CLARK 800

2          10     KING  900

3          20     JAMES 1000

4          20     KATE  2000

5          30     JONES 1150

使用 listagg 分析函数实现合并显示

SQL>   SELECT deptno,SUM (sal) AS total_sal,

2            LISTAGG (name, ',') WITHIN GROUP (ORDER BY name) AS total_name

3       FROM v

4   GROUP BY deptno;

行号     DEPTNO TOTAL_SAL TOTAL_NAME

---------- ------ --------- ----------

1          10     1700      CLARK,KING

2          20     3000      JAMES,KATE

3          30     1150      JONES

3.2 sum ... over()

使用分析函数 sum (…) over (order by…) 可以生成累计和。

例如公司查看用人成本，需要对用员工的工资进行累加，了解员工人数与工资支出之间的对应关系。

SQL> //按员工编号排序对员工的工资进行累加

3   SELECT employee_id AS 编号,

4          employee_name AS 姓名,

5          salary AS 人工成本,

6          SUM (salary) OVER (ORDER BY employee_id) AS 成本累计

7     FROM dmhr.employee

8    WHERE job_id = 11;

行号     编号      姓名    人工成本 成本累计        

---------- ----------- --------- ------------ --------------------

1          1001        马学铭 30000        30000

2          2001        李慧军 10000        40000

3          3001        苏国华 30000        70000

4          4001        段建兵 10000        80000

5          5001        袁会方 18000        98000

6          6001        奚哲峰 8000         106000

7          7001        郑晓同 30000        136000

8          8001        吴迎    8000         144000

9          9001        陈强    8000         152000

10         10001       龚顺超 8000         160000

11         11001       高文栋 8000         168000

3.3 dense_rank () over ()

统计11号job_id的最高工资和最低工资：

select max(salary),min(salary) from dmhr.employee where job_id=11;

按照工资排序：

select dense_rank() over (order by salary desc) paixu,salary,EMPLOYEE_NAME

 from dmhr.employee where job_id=11;

3.4 max ..keep()

找出最低工资的人：

 select max(employee_name) keep (dense_rank first order by salary) as min_sal

 from dmhr.EMPLOYEE

 where job_id=11;

3.5 lead () over()

创建测试表：

CREATE OR REPLACE VIEW v(pro_id,pro_start,pro_end) as

SELECT 1,date '2020-10-01',date '2020-10-02' FROM dual UNION ALL

SELECT 2,date '2020-10-02',date '2020-10-03' FROM dual UNION ALL

SELECT 3,date '2020-10-03',date '2020-10-06' FROM dual UNION ALL

SELECT 4,date '2020-10-06',date '2020-10-07' FROM dual UNION ALL

SELECT 5,date '2020-10-09',date '2020-10-11' FROM dual UNION ALL

SELECT 6,date '2020-10-13',date '2020-10-15' FROM dual;

SELECT * FROM v;

查询连续值记录，即下一行记录的起始时间与上一行记录的结束时间一致

SELECT 工程号, 开始日期, 结束日期

  FROM (SELECT pro_id AS 工程号,

               pro_start AS 开始日期,

               pro_end AS 结束日期,

               LEAD (pro_start) OVER (ORDER BY pro_id) 下一工程开始日期

          FROM v)

 WHERE 下一工程开始日期 = 结束日期;

更多详情请参考 SQL 开发指南 | 达梦技术文档

到此结束！