oracle中常用函数大全

雨点打透心脏的1/2处 2022-06-01 12:42 228阅读 0赞

**1、数值型常用函数**

函数　　返回值　　　　　　　　　　　　样例　　　　　　　　　　　显示  
ceil(n) 大于或等于数值n的最小整数　　select ceil(10.6) from dual; 11  
  
floor(n) 小于等于数值n的最大整数　 select ceil(10.6) from dual; 10  
  
mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2  
  
power(m,n) m的n次方　　　　　　　　 select power(3,2) from dual; 9  
  
round(n,m) 将n四舍五入,保留小数点后m位　　select round(1234.5678,2) from dual; 1234.57  
  
sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1  
  
sqrt(n) n的平方根　　　　　　　　　select sqrt(25) from dual ; 5  
  
2、常用字符函数  
  
initcap(char) 把每个字符串的第一个字符换成大写　　select initicap('mr.ecop') from dual; Mr.Ecop  
  
lower(char) 整个字符串换成小写　　　　　　　　　select lower('MR.ecop') from dual; mr.ecop  
  
replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott  
  
substr(char,m,n) 取出从m字符开始的n个字符的子串　　select substr('ABCDEF',2,2) from dual; CD  
  
length(char) 求字符串的长度　　　　select length('ACD') from dual; 3  
  
|| 并置运算符　　　 select 'ABCD'||'EFGH' from dual; ABCDEFGH

**3、日期型函数**

sysdate 当前日期和时间 select sysdate from dual;  
  
last\_day 　本月最后一天 select last\_day(sysdate) from dual;  
  
add\_months(d,n)　当前日期d后推n个月 select add\_months(sysdate,2) from dual;  
  
months\_between(d,n) 日期d和n相差月数 select months\_between(sysdate,to\_date('20020812','YYYYMMDD')) from dual;  
  
next\_day(d,day) d后第一周指定day的日期 select next\_day(sysdate,'Monday') from dual;  
  
day 格式　　有　　'Monday' 星期一　　'Tuesday' 星期二  
  
'wednesday' 　星期三 'Thursday' 星期四 'Friday' 星期五  
  
'Saturday' 星期六 'Sunday' 星期日  
  
4、特殊格式的日期型函数  
  
Y或YY或YYY 年的最后一位，两位，三位 select to\_char(sysdate,'YYY') from dual;  
  
Q 季度,1-3月为第一季度　　　　select to\_char(sysdate,'Q') from dual;  
  
MM 　月份数　　　　　　　　　　　select to\_char(sysdate,'MM') from dual;  
  
RM 月份的罗马表示 select to\_char(sysdate,'RM') from dual; IV  
  
month 用9个字符表示的月份名 select to\_char(sysdate,'month') from dual;  
  
ww 当年第几周　　　　　　　　　select to\_char(sysdate,'ww') from dual;  
  
w 本月第几周　　　　　　　　　select to\_char(sysdate,'w') from dual;  
  
DDD 当年第几天,一月一日为001 ,二月一日032 select to\_char(sysdate,'DDD') from dual;  
  
DD 当月第几天 select to\_char(sysdate,'DD') from dual;  
  
D 周内第几天 select to\_char(sysdate,'D') from dual; 如　sunday  
  
DY 周内第几天缩写　　　　　　　select to\_char(sysdate,'DY') from dual; 如　sun  
  
hh12 12小时制小时数　　　　　　　select to\_char(sysdate,'hh12') from dual;  
  
hh24 24小时制小时数　　　　　　　select to\_char(sysdate,'hh24') from dual;  
  
Mi 分钟数　　　　　　　　　　　 select to\_char(sysdate,'Mi') from dual;  
  
ss 秒数　　　　　　　　　　　　 select to\_char(sysdate,'ss') from dual;  
  
select to\_char(sysdate,'YYYY-MM-DD HH:24:mi:ss') from dua;  
  
to\_number() 将合法的数字字符串 select to\_number('88877') from dual; 88877  
  
to\_char() 将数字转换为字符串　 select to\_char(88877) from dual; '88877'  
  
set serveroupt on;  
  
dbms\_output.put\_line('hello world')  
  
set heading off 由于正在创建数据文件，不需要表头  
  
set pagesize 0 不需要分页  
  
set linesize 80 设置行的最大尺寸  
  
set echo off 告诉sql plus 在执行语句时，不要回显语句  
  
set feedback off 禁止sql plus 显示有多少满足查询的行被检索到  
  
col sales format 999,999,999  
  
append 添加文本到当前行尾  
  
change/old/new/ 在当前行用新的文本代替旧的文本  
  
　change/text 从当前行删除ｗｅｎｂ  
  
　del 删除当前行  
  
　input text 　在当前行之后添加一行  
  
　list 显示缓冲区中的所有行  
  
　list n 显示缓冲区中的第n行  
  
list m n 显示m到n  
  
5、字符函数  
  
  
\--------------------------------------------------------------------------------  
  
字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。  
  
1. CONCAT (char1, char2)  
  
返回连接“char2”的“char1”。  
  
示例 SELECT CONCAT( CONCAT(ename, ' is a '), job) FROM emp;  
  
2. INITCAP(string)  
  
将“string”的字符转成大写。  
  
示例 Select INITCAP(ename) from emp;  
  
3. LOWER (string)  
  
将“string”转成小写。  
  
示例 Select LOWER(ENAME) from emp;  
  
4. LPAD(char1,n \[,char2\])  
  
返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。  
  
示例 SELECT LPAD(ename,15,'\*') FROM emp;  
  
5. LTRIM(string,trim\_set)  
  
从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim\_set” 是我们要去掉的字符的集合。  
  
示例 SELECT LTRIM('abcdab','a') FROM DUAL;  
  
6. REPLACE(string, if, then)  
  
用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。  
  
示例 SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;  
  
7. RPAD(char1, n \[,char2\])  
  
返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。  
  
示例 SELECT RPAD(ename,15,'\*') FROM emp;  
  
8. RTRIM(string,trim\_set)  
  
从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim\_set” 是我们要去掉的字符的集合。  
  
示例 SELECT RTRIM('abcdef', 'f') FROM DUAL;  
  
9. SOUNDEX(char)  
  
返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。  
  
示例 SELECT ename FROM emp  
  
WHERE SOUNDEX(ename) = SoUNDEX('SMYTHE');  
  
10. SUBSTR(string, start \[,count\])  
  
返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，则从“start”开始截取到“string”的尾部。  
  
示例 SELECT SUBSTR('ABCDEFGIJKLM',3,4) FROM DUAL;  
  
11. TRANSLATE(string, if, then)  
  
“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。  
  
示例 SELECT TRANSLATE(ename,'AEIOU', 'XXXXX') FROM emp;  
  
12. UPPER(string)  
  
返回大写的“string”。  
  
示例 SELECT UPPER('aptech computer education') FROM dual;  
  
13. ASCII(string)  
  
该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。该函数返回 “string”中第一个（最左边）字符的 ASCII 值。  
  
示例 SELECT ASCII('APTECH') from dual;  
  
14. INSTR (string, set\[, start\[, occurrence\] \] )  
  
该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。  
  
示例 SELECT INSTR('aptech is aptech','ap',1,2) FROM DUAL;  
  
15. LENGTH(string)  
  
返回“string”的长度值。  
  
示例 SELECT ename, LENGTH(ename) FROM emp   
  
WHERE empno = 7698;