本文主要描述了关于达梦 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 开发指南 | 达梦技术文档
到此结束！
1392
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
