oracle常用函数（25）

函数

Oracle 提供一系列用于执行特定操作的函数

1.sysdate

系统当前时间函数

select sysdate from dual;

2.length(str)函数

          返回字符串的长度 str表示一个字符串

select length('aa') from dual;

select ename,length(ename) from emp;

--查询姓名有6个字符的员工信息

select * from emp where length(ename)=6;

3.concat(str1,str2)

    将字符串str1和字符串str2拼接在一起，与||一致；

select concat('aa','bb') from dual;

-- ||:也是字符串连接符

select 'aa'||'bb' from dual;

select concat(concat('aa','bb'),'cc') from dual;

select 'aa'||'bb'||'cc' from dual;

select table_name from user_tables;   --查询当前用户下数据库中所有的表名
---------在系统管理员用户下操作即可，非系统管理员账户如果无权限赋予权限即可

grant 权限名 to username；

----查询EMP表中每列名称

select column_name from user_tab_columns where table_name='EMP';

---将字符串与表名拼接在一起

select 'drop table '||table_name||';' from user_tables;

注意：字符串要用单引号括起来,在字符串(单引号中)中使用两个连着的单引号，这时第一个单引号是一个转义符号

select '''' from dual;

select ',''' from dual;

select 'insert into dept values('||deptno||','''||dname||''','''||loc||''');' from dept;

4.chr(ASCCII)

他将ASCCII码转换成字符

ASCCII对应键盘上的每个键位

select chr(39) from dual;

select chr(65) from dual;

select chr(97) from dual;

5.substr(str,index,len)

 字符串截取函数，str是一个字符串，index截取的开始位置，len截取的长度（可省略）

   表示从字符串str的index位置开始截取len个长度的字符

select substr('abcdefg',2,2) from dual;

select substr('abcdefg',2) from dual;

select substr('abcdefg',2,6) from dual;

select substr('abcdefg',2,8) from dual;

6.trim([s from ]str)

str表示字符串，s表示字符，代表去除字符串两端的空格，如果加s，代表去除字符串两端的字符s；

select '   abcd    ' from dual;

select trim('   abcd    ') from dual;

select trim('$' from '$abcd$$') from dual;

ltrim(str[,'s']):去除字符串左边的空格或者字符s

select ltrim('    abcd    ') from dual;

select ltrim('$abcd$','$') from dual;

rtrim(str[,'s'])：去除字符串右边的空格或者字符s

select rtrim('    abcd    ') from dual;

select rtrim('$abcd$','$') from dual;

7.replace(str,s,d)

替换函数str字符串，s要替换的内容，d替换的内容，代表将字符中str的s替换成d

select replace('abcd1qewr1qerazdf','qe','QE') from dual;

8.lpad/rpad(str,len,[s])

str表示字符串，len表示扩展后的字符串的长度，s表示要扩展的内容，可选参数

         表示在字符串的左边填充空格或者s让字符串的长度到len的长度

select lpad('aa',4) from dual;

select lpad('aa',4,'b') from dual;

select lpad('abcd',2,'6') from dual;

SELECT RPAD('abcd', 2, '6') FROM DUAL;

9.initcap(str)

str表示字符串，将字符串中的英文单词首字母大写

select initcap('hello world') from dual;

select initcap('helloworld') from dual;

10.lower/upper(str)

      将字符串全部转换成小写/大写

select lower(ename) from emp;

select upper(ename) from emp;

11.instr(str,s[,n1,n2])

    在字符串中找某个字符的位置，str字符串，s：要查询的字符，n1表示从位置开始查询，n2表示这个字符第几次出现，在字符串str中从n1的位置开始查询第n2次出现字符s的位置

select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j') from dual; --查字符串首次出现j的位置

select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',10) from dual;--从字符串的第10个字符开始查找第一次出现j的位置

select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',10,3) from dual;--从字符串的第10个字符开始查找第3次出现j的位置

select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',-3,3) from dual;--从字符串的倒数第3个字符开始反向查找第3次出现j的位置

12.floor(n)

      取整函数，只取整数部分，不进行四舍五入

select floor(3.41) from dual;

select floor(3.98) from dual;

13.mod(m,n)

        求m除以n的余数的函数

select mod(3,2) from dual;

14.round(n,s)

    四舍五入函数,n表示数字，s表示精度,默认是0

select round(3.134) from dual;

select round(5.76) from dual;

select round(5.68,1) from dual;

15.power(m,n)

   求m的n次方

select power(2,8) from dual;

16.to_number(str[,format])

       将字符串转换成数字,str字符串，format格式字符串

select to_number('78.1234') from dual;

17.to_date(str,format)

      将字符串转换成固定格式的日期，str是一个日期，format:日期格式

yyyy:表示4位的年

MM：表示两位月

dd:表示两位的天

hh24:表示24小时制的小时

mi:表示分钟

ss：表示秒

day:表示星期

select to_date('1999-09-09 14:24:34','yyyy-MM-dd hh24:mi:ss') from dual;

18.to_char(date,format)

       将日期转换成字符串，date是一个日期，format:日期格式

select to_char(sysdate,'yyyy') from dual;

select to_char(sysdate,'hh24:mi:ss') from dual;

select to_char(sysdate,'day') from dual;

19.add_months(date,n)

        给日期加减n个月

select add_months(sysdate,3) from dual;

select add_months(sysdate,-2) from dual;

日期直接加上一个整数，相当于加的天数

select sysdate-1 from dual;

20.months_between(date1,date2)

     求两个日期相差多少个月

select months_between(sysdate,to_date('20210120','yyyyMMdd')) from dual;

21.last_day(date)

      取日期当前月的最后一天

select last_day(sysdate) from dual;

select current_date from dual;

22.trunc()

       截断函数

select trunc(3.13453,3) from dual; --截断数字类型，第二个参数表示精度，不会四舍五入

select trunc(3234.1324,-2) from dual;

select trunc(sysdate,'yyyy') from dual;  --截取到年（本年的第一天）

select trunc(sysdate,'MM') from dual;  --截取到月（本月的第一天）

select trunc(sysdate,'month') from dual;  --截取到月（本月的第一天）

select trunc(sysdate,'day') from dual;  --截取到周（本周第一天，即上周日）

23.decode(exp,res1,value1,res2,value2,...,default)

exp 表达式的

res1，res2 ,..：表达式的结果

如果表达式结果和某一个res值相等，就会返回res后面对应的value

如果表达式的结果没有一个res值和它匹配，它会返回default默认值；

select decode(ssex,'男','男生','女','女生','未知') from student;

24.nvl(变量,默认值)

        如果变量的值是空时,函数返回默认值，如果变量的值不为空返回变量的值

select nvl(comm,200) from emp;

25.行列转换

1.行转列

     行转列的大致意思是，将表中多行数据转成一行的多个字段输出。

     需要行转列的业务逻辑通常是，将表中的一个字段分类统计后作为多个结 果字段输出。

如：

create table studentScores(

       username varchar2(20),

       subject  varchar2(30),

       score    number

);

insert into studentScores values('张三','语文',90);

insert into studentScores values('张三','英语',80);

insert into studentScores values('张三','数学',70);

insert into studentScores values('李四','语文',60);

insert into studentScores values('李四','英语',61);

insert into studentScores values('李四','数学',62);

select * from studentScores;

select username,

sum(case when subject='语文' then score else 0 end) 语文,

sum(case when subject='英语' then score else 0 end) 英语,

sum(case when subject='数学' then score else 0 end) 数学

from studentScores group by username;

--pivot函数:行转列的函数

select username,"'语文'","'英语'","'数学'" from studentScores --用pivot函数查出来的字段名称就是'语文',注意这两个单引号也是名称一部分，所以这行要用双引号括起来

pivot(sum(score) ----聚合列取值

   for subject in('语文','英语','数学'));

2.列转行

列转行更容易理解，将表中多个列,每个列拉成一行数据。

如：

create table studentScores2(

       username varchar2(20),

       yuwen  number,

       yingyu   number,

       shuxue   number

);

insert into studentScores2 values('张三',90,80,70);

insert into studentScores2 values('李四',60,61,62);

select * from studentScores2;

select username,'yuwen' as subject，yuwen as score from studentScores2

union all

select username,'shuxue'，shuxue from studentScores2

union all

select username,'yingyu'， yingyu from studentScores2;

/*

  unpivot函数是列转行函数

*/

select username,subject,score from studentScores2

unpivot(score --为列转行中多列变成一列的值取别名

              for subject  --在结果中成为一列的原多列取统一别名

  in(yuwen,yingyu,shuxue));