orcale sql 实践使用记录

1. oracle的 LPAD() 函数 和 RPAD() 函数

解决的问题是，给字段值的左边或者右边填充指定的字符串，或者 是从左边/右边 截取指定长度的 字段值。

使用参考地址
oracle常用关键字和函数参考地址

1, 定义
LPAD函数是Oracle数据库函数，lpad函数从左边对字符串使用指定的字符进行填充。从其字面意思也可以理解，l是left的简写，pad是填充的意思，所以lpad就是从左边填充的意思。

2，语法格式:
lpad( string, padded_length, [ pad_string ] )

参数说明：
string
准备被填充的字符串；
padded_length
填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string
填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

3，举例子说明

• 示例一：

SQL> select lpad('abcde',10,'x') from dual;

LPAD('ABCDE',10,'X')
结果为：xxxxxabcde

• 示例二，生产示例

select b.*,a.*
       from theme.DAT_BUL a  inner  JOIN 
       bas_stati  b ON   ( LPAD (A .roid, 2, 0) || LPAD (A .staid, 2, 0) = b.param_value )  where ID = '${ID}'

4, 对应的右填充函数
rpad函数从右边对字符串使用指定的字符进行填充，语法格式与lpad格式相同：

rpad(string,padded_length,[pad_string])

string
被填充的字符串
padded_length
字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
pad_string
是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，rpad函数将会在string的右边粘贴空格。

5 示例展示对比：

LPAD('tech', 7); -- 将返回'tech'
RPAD('tech', 7); -- 将返回'tech'

RPAD('tech1237', 7); -- 将返回'tech123'
LPAD('tech1237', 7); -- 将返回'tech123'

LPAD('tech', 2); -- 将返回'te'
LPAD('tech', 2); -- 将返回'te'

LPAD('tech', 8, '0'); -- 将返回'0000tech'
RPAD('tech', 8, '0'); -- 将返回'tech0000'

LPAD('tech on the net', 15, 'z'); -- 将返回 'tech on the net'

LPAD('tech on the net', 16, 'z'); -- 将返回 'ztech on the net'
RPAD('tech on the net', 16, 'z'); -- 将返回 'tech on the netz'

2，cast()函数

1，作用，在sql语句时解决 部分数据类型转换的问题

2，数据库CAST()函数和CONVERT()函数比较

数据库CAST()函数和CONVERT()函数比较参考地址

3, CAST函数 的定义
CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

4, CAST函数 语法

CAST (expression AS data_type)

参数说明
expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

但是可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

• 二进制，同带binary前缀的效果 : BINARY
• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED
• 无符号整数 : UNSIGNED

5, 示例 将字段UNIT_ID类型NUMBER(18,0) 转换成 VARCHAR2(32)类型

SELECT CAST(brs.UNIT_ID AS VARCHAR2(32)) AS mid FROM B_SEGMENT brs ;

3 coalesce()函数

1，作用
对于Oracle数据库，一般经常对空值处理的函数为NVL，而mysql中常用到的是ifnull，这两个函数相似，其实都是由一个函数衍生而来，那就是COALESCE()函数。

2，COALESCE()函数 定义：
返回列表中第一个非null表达式的值。如果所有表达式求值为null，则返回null

COALESCE()函数有两种用法：
1.COALESCE ( expression, expression2 );
2.COALESCE ( expression1, expression2, … expression-n );

第一种就相当于Oracle中的nvl或者mysql中的ifnull，写成表达式的形式为：
CASE WHEN expression IS NOT NULL THEN expression ELSE
expression2 END;

第二种可以包含n个表达式，表示如果第一个不为空取第一个，否则判断下一个，以此类推，如果全部为空，则返回null值。

需注意：vertica中空字符串跟null值不一样

3 示例：
sql select zhye,coalesce(mx.zhye,0) ,nvl(mx.zhye,0) from mx ；

结果展示

部分结论：

可以看到，coalesce(mx.zhye,0)和nvl用法一致，若不为null，则显示原值，否则显示第二个值 0。

4，round() 函数

1， 定义：
ROUND() 函数用于把数值字段舍入为指定的小数位数。

2, 语法
ROUND(column_name,decimals)

参数说明：

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

5，case when …then … end

1,Case函数 的作用 根据不同的值转换出不同的内容

2, 语法格式
Case 具有两种格式。简单 Case 函数和 Case 搜索函数。

• 简单Case函数
  CASE sex
  WHEN ‘1’ THEN ‘男’
  WHEN ‘2’ THEN ‘女’
  ELSE ‘其他’ END

（注释：列名，这里是sex，如果放在case后，则显示在屏幕上的列名即为列名sex）

• Case搜索函数
  CASE WHEN sex = ‘1’ THEN ‘男’
  WHEN sex = ‘2’ THEN ‘女’
  ELSE ‘其他’ END [as] re_sex

（注释：列名，这里是sex，如果放在when后，若是不为整个CASE WHEN语句写个别名的话，则显示在屏幕上的列名即为整个CASE WHEN语句， 列名 re_sex）

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。

还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

3，示例
case when t.RD_TYPE1_ID ='1' then '计划类阻塞' when t.RD_TYPE1_ID ='2' then '计划类中断' end eventType

6，instr()函数

1, instr函数 的作用
Oracle中可以使用instr函数对某个字符串进行判断，判断其是否含有指定的字符。在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。

主要用于代替模糊查询like 的使用

2，语法格式

instr(sourceString,destString,start,appearPosition)
instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

参数说明：

• sourceString代表源字符串；
• destString代表要从源字符串中查找的子串；
• start代表查找的开始位置，这个参数可选的，默认为1；
• appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1

如果start的值为负数，则代表从右往左进行查找，但是位置值仍然从左向右计算。
返回值为：查找到的字符串的位置值。且只检索一次

3,示例

• instr() 在orcale 中的模糊查询的使用

select * from tableName where name like '%helloworld%';
select * from tableName where instr(name,'helloworld')>0;  

这两条语句的效果是一样的

• 对于instr函数，我们经常这样使用：从一个字符串中查找指定子串的位置。例如：

select instr('abcdefgh','de') position from dual;

结果：默认从1开始算 第一次出现de 的位置为4，所以返回4

• 从第1个字符开始，查找第2次出现子串的位置的写法：

select instr('qinyinglianqin','qin', 1, 2) position from dual;

结果：从1开始算 第2次出现de 的位置为12，所以返回12