ORACLE 内置函数

た入场券 2022-09-26 14:52 262阅读 0赞

\--以下内容是从网上和实践中总结出来的。由于是刚刚入手 ORACLE 故记录下来以便日后备用

\--ORACLE内置函数  
\--1、  
ASCII  --返回与字符对应的十进制数字  
    select ascii('a') from dual  
\--2、  
CHR    -- 给出整数，返回对应的字符  
    select chr(10) from dual  
\--3、  
concat  --链接两个字符串  
   select concat('链接','两个字符串')||'||是连接符'from dual  
\--4、  
initcap  --返回字符串并将字符串的第一个字符大写  
  select initcap('abcdef') from dual  
\--5、  
instr     --在字符串中搜索指定的字符，返回发现指定字符的位置  
 select instr('abcdefg','a',1,1)  
 --6、  
 length     ---返回字符串的长度  
 select length('ddd')  
 --7、  
 lower  ----将所有字符小写  
 upper  ----将所有字符大写  
 --8、  
 rpad   --在列的右侧粘贴字符  
 lpad   --在列的左侧粘贴字符  
 --9、  
 ltrim  --删除左侧出现的字符  
 rtrim  --删除右边出现的字符  
select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;  
select ltrim('abcde','a') from dual;  
 --10、  
 Substr ----获取子字符串  
 select substr('123456789',1,5) from dual;  
\--11、  
replace ---替换指定的字符串  
select replace ('123abc456','456','def') from dual;  
\--12、  
soundex --返回一个与给定的字符串相同读音的字符串  
create table table1(xm varchar(8));  
 insert into table1 values('weather');  
 insert into table1 values('wether');  
 insert into table1 values('gao');

select xm from table1 where soundex(xm)=soundex('weather');  
\--13、  
trim    --剪切掉前后的字符，如果不指定默认为空格  
leading --j剪切掉前面的字符  
select trim(leading | trailing | both '  ' from '   abc      d      ') from dual;  
\--14、  
ceil    --返回大于或等于给出数字的最小整数  
select ceil(12.1) from dual;  
\--15、  
floor   --对给定的数字取整  
select floor(533.22) from dual;  
\--16、  
mod --返回余数  
select mod(5,3)from dual;  
\--17、  
round   --按照指定的精度进行舍入  
trunc   --按照指定的精度进行舍入  
select round(55.5),round(-55.4),trunc(55.4),trunc(-55.4) from dual;  
\--18、  
Sign    --取数字的符号.大于0返回1，小于0返回-1，等于0返回0  
select sign(123),sign(-100),sign(0) from dual;  
\--19、  
trunc   --按照指定的精度截取一个数  
select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;  
\--20、  
add\_months  --增加或减去月份  
select to\_char(add\_months(to\_date('199912','yyyymm'),2),'yyyymm') from dual;  
\--21、  
last\_day    --返回日期的最后一天  
select to\_char(sysdate,'yyyy.mm.dd'),to\_char((sysdate)+1,'yyyy.mm.dd') from dual;  
select last\_day(sysdate) from dual;  
\--22、  
months\_between(date1,date2)  --给出两个日期间的差  
select months\_between('19-12月-1999','19-3月-1999') mon\_between from dual;  
select months\_between(to\_date('2000.05.20','yyyy.mm.dd'),to\_date('2005.05.20','yyyy.dd')) mon\_betw from dual;  
\--23、  
next\_day (date,'day')   --给出日期date和星期x之后计算下一个星期的日期  
select next\_day('18-5月-2001','星期五') next\_day from dual;  
\--24、  
sysdate --获取当前日期  
select to\_char(sysdate,'dd-mm-yyyy day') from dual;  
trunc(date,fmt)--按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒  
select to\_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, to\_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;  
\--25、  
chartorowid --将字符数据类型转化为ROWID类型  
select rowid,rowidtochar(rowid),ename from scott.emp;  
\--26、  
convert(c,dset,sset)    --将源字符串 sset从一个语言字符集转换到另一个目的dset字符集  
select convert('strutz','we8hp','f7dec') "conversion" from dual;  
\--27、  
to\_char(date,'format')  --将日期转化为指定格式的字符串  
select to\_char(Sysdate,'yyyy/mm/dd mm:ss') from dual;  
\--28、  
to\_date(String,'format')    --将字符串转化为ORACLE中的一个日期  
select to\_date('2000.05.20','yyyy-mm-dd') from dual;  
\--29、  
to\_number   --将字符转化为数字  
select to\_number('1922') from dual;  
\--30、  
bfilename(dir,filename)  --指定一个外部二进制文件  
insert  a select bfilename('E:/ProgramFiles/Detong','picture.gif');  
\--31、  
greatest    --返回一组表达式中的最大值，即比较字符编码的大小  
select greatest('a','b','c','d','5','1') from dual;  
\--32、  
least       --返回一组表达式中的最小值，即比较字符编码的大小  
select least('a','b','c','d','5','1') from dual;  
\--33、  
uid --返回标识当前用户的唯一整数  
user    --返回当前名  
select username,user\_id from dba\_users where user\_id=uid;  
select user from dual;  
\--34、  
userenv(opt) --返回当前用户环境的信息,opt可以是:  
                                                              --  entryid,instance,sessionid,terminal,isdba,lable,language,client\_info,lang,vsize  
                                                             -- ISDBA  查看当前用户是否是DBA如果是则返回true  
enteryid    --返回会话人口标志  
instance    --返回当前INSTANCE的标志  
sessionid   --返回会话标志  
terminal    -- 返回用户的终端或机器的标志    
lang           --返回当前环境的语言的缩写  
language    --返回当前环境变量                                    
vsize   --返回X的大小(字节)数  
select vsize(user),user from dual;  
\--35、  
avg(distinct|all)   --求平均值  
create table table3(xm varchar(8),sal number(7,2));  
insert into table3 values('gao',1111.11);  
insert into table3 values('gao',1111.11);  
 insert into table3 values('zhu',5555.55);  
commit;  
select avg(distinct sal) from table3;  
select avg(all sal) from table3;  
\--36、  
max(distinct|all)       --求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次  
min(distinct|all)       --求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次  
stddev(distinct|all)   --求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差  
\--37、  
nullif (expr1, expr2) ->相等返回NULL，不等返回expr1

\-----分组函数  
 AVG(\[\{DISYINCT|ALL\}\]) distinct去掉重复的数据 all所有数据  
　 Max  
　SUM  
　Count　 计数函数  
　　 count（\*）　 统计所有的行，包括重复的值和空值  
　　 count(col\_name) 统计列中非空值的个数  
　　 count(distinct col\_name) 统计列中除去空值和重复值的行数

\-----分析函数:

\--作用：用于完成对聚集的累计排名，移动平均数和报表计算。

--分析函数是查询中执行的最后一组操作,只能出现在select列表或order by 字句

--row\_number() over(\[Partition by column\] order by column)　 返回唯一排序值select

--按工资排名：

select ename,job,deptno,sal,row\_number() over(order by sal desc) as A from empa;

--按部门分组再按工资排名

select ename,job,deptno,sal,row\_number() over(Partition by deptno order by sal desc) as A from empa  
\--38、  
dense\_rank() --计算一个行在一组有序行中的排位，排位从1开头的连续整数，具有相等值，排位相同。排位是连续的。  
select d.dname,e.ename,e.sal,dense\_rank() over(partition by e.deptno order by e.sal desc) denserank  
from empa a,dept d where e.deptno=d.deptno  
\--39、  
rank() --计算一个值在一组值中的排位，排位从1开始,相等值的行排位相同，序数跳跃相应值.  
　　 select ename,sal,comm,deptno,rank() over(partition by deptno order by sal desc) from empa;  
\--40、  
nvl2(a,b,c)　 --表达式1不为NULL，返回表达式2；为NULL .返回表达式3。表达式2和表达式3类型不同的话，表达式3会转换为表达式2的类型  
\--41、  
nvl--（表达式1,表达式2) 表达式1为NULL，返回表达式2；不为NULL，返回表达式1。注意两者的类型要一致