Oracle中的一些常用函数

最新推荐文章于 2024-08-08 11:12:42 发布

没甚麽大不了

最新推荐文章于 2024-08-08 11:12:42 发布

阅读量1.2k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/zhaoxiang10052111/article/details/79869820

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、日期函数
            ADD_MONTHS(d, n) 返回指定日期加上月数后的日期值
            MONTHS_BETWEEN(d1, d2) 返回两个日期间的月数
            LAST_DAY(d) 返回指定日期当前的最后一天的日期值
            RONUD(d,[fmt]) 返回指定日期四舍五入格式(YEAR、MONTH、DAY)后的日期值
            NEXT_DAY(d,day) 返回指定日期下一个星期几的日期值
            TRUNC(d,[fnt]) 返回指定日期截断为格式后的日期值

EXTRACT(fmt FROM d) 返回指定日期提取的格式的值

1.1 sysdate　　
　　　　用途：获取当前系统时间。
1.2 to_date('字符类型','日期类型')
　　　　用途：将字符类型转化成日期类型的函数，主要用于插入、查询和修改。

1.3 to_char('日期类型','字符类型')

              得到当前日期 select sysdate from dual;
                select to_char(sysdate,'YYYY/MM/DD') FROM DUAL; -- 2018/04/20
                select to_char(sysdate,'YYYY') FROM DUAL; -- 2018
                select to_char(sysdate,'YYY') FROM DUAL; -- 017
                select to_char(sysdate,'YY') FROM DUAL; -- 18
                select to_char(sysdate,'MM') FROM DUAL; -- 04
                select to_char(sysdate,'DD') FROM DUAL; -- 20
                select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') FROM DUAL; -- 2018/04/20 15:24:13
                select to_char(sysdate,'YYYY/MM/DD HH:MI:SS') FROM DUAL; -- 2018/04/20 03:25:23
                select to_char(sysdate,'RR/MM/DD') FROM DUAL; -- 18/04/20
                返回当月最后一天 select last_day(sysdate) from dual;

2、字符函数》字符函数接受字符输入，并返回字符或数值。
            INITCAP(char) 首字母大写
            LOWER(char) 转换为小写
            UPPER(char) 转换为大写
            LTRIM(char, set) 左裁切
            RTRIM(char, set) 右裁切
            TRANSLATE(char, from, to) 按字母翻译
            REPLACE(char, search_str, replace_str) 字符串替换
            INSTR(char, substr[,pos]) 查找子串位置
          SUBSTR(char, pos, len) 取子字符串
            CONCAT(char1, char2) 连接字符串

            CHR(ascii) 根据 ASCII 码返回对应字符串
            LPAD / RPAD 左 / 右填充
        LPAD ('function', 15 , '=') 返回 '=======function'
            TRAM 开头或结尾(或开头和结尾)裁剪特定的字符，默认裁剪空格。
        TRIM ([LEADING | TRAILING] trim_char)
            LENGTH(char) 返回字符串长度
            DECODE 逐个值进行字符串替换
        DECODE (expr, search1, result1, search2, result2, [ ,default])
          DECODE (ostalus, 'p', '准备处理', 'c', '已完成')

3、数字函数》数字函数接受数字输入并返回数值作为输出结果。
            ABS(n) 取绝对值
            CEIL(n) 向上取值
            FLOOR(n) 向下去整
            SIN(n) 正弦值
            COS(n) 余弦值
            POWER(m, n) 指数函数
            SQRT(n) 平方根
            MOD(m, n) 取余
            ROUND(m, n) 小数点后精度四舍五入
            TRUNC(m, n) 小数点后精度截断

4、转换函数》转换函数将一种数据类型转换为另一种数据类型。
            TO_CHAR (d|n, [,fmt]) 格式化日期 / 数值
            TO_DATE (char [,fmt]) 将 fmt模型格式的字符串转换为日期型
                TO_NUMBER (char) 将包含数字的的字符串转换为数值型

5、其他函数
            NVL (exp, exp2) 如果 exp 为空返回 exp2；如果非空返回 exp
            NVL2 (exp, exp2, exp3) 如果 exp 为空返回 exp3；如果非空返回 exp2
            NULLIF (exp1, exp2) 比较两表达式，相等返回空值，不等则返回 exp1

6、分组函数 / 聚合函数》分组函数基于一组行返回结果，即为每一组行返回单个值。
            AVG (columname) 返回指定列的平均值
            MAX (columname) 返回指定列的最大值
            MIN (columname) 返回指定列的最小值
            SUM (columname) 返回指定列的总值
            COUNT
                COUNT (*) 统计所有行个数，包括重复行和空值得行
                COUNT (columname) 统计指定列非空值的个行数
                  COUNT (DISTINCR columname) 统计指定列中非重复，非空值得行个数
            GROUP BY 子句
                用于将信息表划分为组，对查询结果按组进行聚合运算，为每组返回一个结果。
                    HAVING 子句用来指定 GROUP BY 子句的检索条件。

7、处理NCLOB类型的字段的函数

DBMS_LOB.SUBSTR（xxx）

8、INSTR（str，str2）函数

INSTR(str,str2) 如果str中包含了str2，则返回str2的位置；否则返回0

例如：INSTR('abc','b') 返回 2 ；；； INSTR('abc','d') 返回 0

9、查询某个字符串的某个字符最后出现的位置到结束的值

CKLZ='ABC/DEF/p/10_005_002';

SELECT substr(CKLZ,instr(CKLZ,'/',-1)+1) FROM TABLE 返回：10_005_002

10、统计问题，分组，聚合连用

SELECT Z.UNIT_NAME,
 (SUM(YBSL)||'/'||SUM(Z.ZSL)) AS XYGLYGZ,
(SUM(CASE WHEN Z.GWMC='本科教务口工作' THEN Z.YBSL ELSE 0 END ) ||'/'|| SUM(CASE WHEN Z.GWMC='本科教务口工作' THEN Z.ZSL ELSE 0 END )) AS BKJWGZ,
(SUM(CASE WHEN Z.GWMC='人事口工作' THEN Z.YBSL ELSE 0 END ) ||'/'|| SUM(CASE WHEN Z.GWMC='人事口工作' THEN Z.ZSL ELSE 0 END )) AS RSKGZ,
(SUM(CASE WHEN Z.GWMC='科研口工作' THEN Z.YBSL ELSE 0 END ) ||'/'|| SUM(CASE WHEN Z.GWMC='科研口工作' THEN Z.ZSL ELSE 0 END )) AS KYKGZ,
(SUM(CASE WHEN Z.GWMC='研究生口工作' THEN Z.YBSL ELSE 0 END ) ||'/'|| SUM(CASE WHEN Z.GWMC='研究生口工作' THEN Z.ZSL ELSE 0 END )) AS YJSKGZ,
Z.XYGLYNAME
 FROM (
SELECT  UT.UNIT_NAME,COUNT(DISTINCT R.RESOURCE_ID) ZSL,
COUNT(YB.RESOURCE_ID) AS YBSL,
(CASE WHEN RG.GWMC ='posmXYmanager2018' THEN '人事口工作'
      WHEN RG.GWMC ='posmKYmanager2018' THEN '科研口工作'
  WHEN RG.GWMC ='posmYJSmanager2018' THEN '研究生口工作'
  WHEN RG.GWMC ='posmJWmanager2018' THEN '本科教务口工作' END
) AS GWMC,
XY.USER_NAME AS XYGLYNAME

 FROM EP_BASE_RWQD R
INNER JOIN EP_BASE_RWQD_GWXX rg ON RG.RESOURCE_ID=R.GLGW
LEFT JOIN SYS_UICM_UNIT UT ON UT.UNIT_ID=RG.XYBH
LEFT JOIN (SELECT RESOURCE_ID FROM EP_BASE_RWQD WHERE RWZT='3') YB ON YB.RESOURCE_ID=R.RESOURCE_ID
LEFT JOIN (SELECT 
	PMS.UNIT_ID,
	REGEXP_REPLACE (
		MAX(PMS.USER_NAME),
		'([^,]+)(,\1)*(,|$)',
		'\1\3'
	) USER_NAME

FROM (SELECT
			DISTINCT UNIT_ID,
			TO_CHAR(WM_CONCAT (USER_NAME) OVER (
				PARTITION BY UNIT_ID
				ORDER BY
					USER_NAME
			) ) USER_NAME
		FROM
			SYS_POSM_MEMBERS
		WHERE
			PUBLIC_POST_ID = 'posmCOLmanager2018'
) PMS  GROUP BY PMS.UNIT_ID ) XY ON XY.UNIT_ID=RG.XYBH
WHERE 1=1
      <ISNULL>AND R.NF='@{NF}'</ISNULL>
GROUP BY RG.GWMC,UT.UNIT_NAME,XY.USER_NAME
ORDER BY UT.UNIT_NAME
) Z
GROUP BY Z.UNIT_NAME,Z.XYGLYNAME