FUNCATION_NAME --单行函数语法
rownum = 1 --所查数据任选一条
ALTER USER 登录账号 IDENTIFIED BY 密码; --修改plsql登录密码
into --赋予数据, select name into aaa from bbb --将bbb中的name赋予到aaa中
字符函数
UPPER --全部字符转换为大写,如select UPPER(‘nihao’) from dual 则结果为NIHAO
LOWER --全部字符转换为小写,如select LOWER(‘NIHAO’) from dual 则结果为nihao
INITCAP --将每个单词的第一个字母大写,其它字母变为小写,如select INITCAP(‘nihao’) from dual 则结果为Nihao
RTRIM --将指定字符去除,只能从最后字符开始去除,如select RTRIM(‘nihao’,‘ao’) from dual 则结果为nih
REPLACE --将替换指定字符,select REPLACE (‘nihaoa’,‘hao’,‘shi’) from dual 则结果为nishia
CONCAT --将连接指定字符,select CONCAT(‘ni’,‘hao’) from dual 则结果为ni hao
LENGTH --计算字符长度,select 列名 from 表名 where LENGTH(列名)=长度
SUBSTR --截取数据库某一列字段中的一部分,select substr(‘abcdefg’,0,3) from dual; 结果是abc,select substr(‘abcdefg’,1,3) from dual; 结果是abc
select substr(‘abcdefg’,-3,4) from dual; 结果efg即从右边第三位(e)开始然后从左往右(从e往g)取4位(efg)因为只有3位所以只取了三位efg
–如substr(string,start,length) --string - 指定的要截取的字符串。start - 必需,规定在字符串的何处开始。正数 - 在字符串的指定位置开始,
负数 - 在从字符串结尾的指定位置开始,0 - 在字符串中的第一个字符处开始。
length - 可选,指定要截取的字符串长度,缺省时返回字符表达式的值结束前的全部字符。
ASCII --将字符转换为数值,select ascii(‘A’) from dual,结果为65
CHR --将数值转换为字符,select chr(‘65’) from dual,结果为A
LTRIM --除字符串左边多余的空格,select ltrim (’ nihao’) from dual ,结果为nihao
RTRIM --除字符串右边多余的空格,select rtrim ('nihao ') from dual ,结果为nihao
TRIM --除字符串左右边多余的空格,select trim (’ nihao ') from dual ,结果为nihao
LPAD --在字符串左边填充,select lpad(‘nihao’,7,’#’) from dual,结果为##nihao,总共7位
RPAD --在字符串右边填充,select rpad(‘nihao’,7,’#’) from dual,结果为nihao##,总共7位
LPAD与RPAD组合使用:select lpad(rpad(‘nihao’,7,’#’),10,’#’) from dual,结果为##nihao###,总共10位,右3左2
INSTR --查找字符串所在位置,select instr(‘nihao’,‘n’),instr(‘nihao’,‘h’),instr(‘nihao’,‘N’) from dual,结果为1,3,0
select * from dual where instr(string1,string2)>0 --string2的数值在string1中有,>0既表示模糊查询,存在的意思
数字函数
ROUND(数字,保留位数) --对小数进行四舍五入,即进位,可以指定保留位数,若不指定则将小数点后的全部四舍五入,rount(jinge,2)表示进位到后两位
select round(789.652) 不保留小数,round(789.652,2) 保留两位小数,round(789.652,-1) 整数位进位 from dual,结果为:790 789.65 790
select round(789.652,-1),round(789.652,-2),round(789.652,-3),round(789.652,-4) from dual,结果为:790 800 1000 0,处理处理整数进位
TRUNC(数字,截取位数) --对小数不进行四舍五入,保留指定位数的小数,即不进位,如果不指定,则不保留
select trunc(789.652) 截取小数,trunc(789.652,2) 截取两位小数,trunc(789.652,-2) 取整 from dual,结果为:789 789.65 700
MOD(数字,数字) --取模,模即余数,select mod(10,3) from dual,结果为:1,select mod(11,3) from dual–结果为:2select mod(12,3) from dual–结果为:0,10除以3的余数
日期函数
SYSDATE --伪列,方便知道当前日期,select sysdate 当前日期,sysdate-3 三天前日期,sysdate+3 三天后日期,sysdate-时间列名 计算天数 from dual;
ADD_MONTHS(日期,数字) --在指定的日期上加指定的月数,求出新的日期,select add_months(sysdate,3) 三个月后的日期,add_months(sysdate,-3) 三个月前的日期 from dual
MONTHS_BETWEEN(日期1,日期2)–求出两个日期间的月数,select MONTHS_BETWEEN(add_months(sysdate,3),add_months(sysdate,-3))两个日期间的月数 from dual,结果为6
NEXT_DAY(日期,星期数) --求出下一个星期X的具体日期,select next_day(sysdate,‘星期一’) 下一个星期一的日期 from dual
LAST_DAY(日期) --求出指定日期所在月的最后一天日期,select LAST_DAY(sysdate)指定日期所在月的最后一天日期 from dual
EXTRACT(格式 FROM 数据) --日期时间分割,或计算给定两个日期的间隔(year年,month月,day日,hour时,minute分,second秒)
select extract(year from date’2018-02-17’)年,extract(month from date’2018-02-17’)月,extract(day from date ‘2018-02-17’)日 from dual,结果为:2018 2 17
转换函数(yyyy 年,mm 月,dd 日,hh 12小时制时,hh24 24小时制时,mi 分,ss 秒,am 上午,pm 下午,fm 去掉前导零)
TO_CHAR(日期|数字|列,转换格式) --将指定的数据按照指定的格式变为字符串型
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)格式化日期时间,to_char(sysdate,‘fmyyyy-mm-dd hh24:mi:ss’)去掉前导零格式化时间 from dual
select * from inp_ta_bill where to_char(djfsj,‘yyyy’)=2017 and to_char(djfsj,‘mm’)>=7 and to_char(djfsj,‘mm’)<=12 djfsj是2017年7月到12月之间的所有数据
9表示一位数字,0显示前导0,$将货币的符号显示为美元符号,L根据语音环境不同自动选择货币符号,‘.’显示小数点,‘,’显示千位符
select to_char(987654321,‘L999,999,999,999,99.999’),to_char(987654321,’$0,000,000,000’) from dual 结果是 ¥9,876,543,21.000 $0,987,654,321 --数字格式化
TO_DATE(字符串|列,转换格式) --将指定字符串按照指定的格式变为DATE型,select to_date (‘2018/1/10 00:00:01’,‘yyyy/mm/dd hh24:mi:ss’) from dual
通用函数
NVL(数字|列,默认值) --如果数字|列不为null,则显示该值本身,如果为空,则显示默认值;
select nvl(123,9) from dual;–结果为:123 select nvl(null,9) from dual;–结果为:9
NVL2(数字|列,默认值1,默认值2) --NVL()的扩展函数,如果数字|列不为空,则显示默认值1,如果为空,则显示默认值2
select nvl2(2,9,3) from dual;–结果为:9 select nvl2(null,9,3) from dual;–结果为:3
NULLIF(表达式一,表达式二) --比较表达式一和表达式的结果是否相等,如果相等返回NULL,不相等返回表达式一
select nullif(1,2) from dual;–结果为:1 select nullif(2,2) from dual;–结果为:NULL
DECODE(列|值,判断值1,显示结果1,判断值2,显示结果2,…,默认值) --多重判断,若列|值与判断值X相同则返回结果X,若不相同则返回默认值,是列|值与判断值相比较
select decode(2,1,‘内容1’,2,‘内容2’,3),decode(2,1,‘内容1’,4,‘内容2’,3) from dual;–结果为:内容2 3
decode( 字段 , 条件1 ,满足的结果,不满足的结果 )
CASE 列|数值 WHEN 表达式1 THEN 显示结果1…ELSE 表达式N…END
–用于实现多条件判断,在WHEN之后编写条件,而在THEN之后编写条件满足的显示操作,若无满足条件则使用ELSE中的表达处理
select case 列|数值 when 表达式1 then 显示结果1
when 表达式2 then 显示结果2
when 表达式3 then 显示结果3
…
else 表达式X
end
COALESCE(表达式1,表达式2,…,表达式N) --将表达式逐个判断,表达式1为NULL,则显示表达式2,若表达式2为NULL则显示表达式3,以此类推,若表达式N的结果还是NULL,则返回NULL
wm_concat 多行查询结果弄到一个里面
–转换成日期类型
SELECT TRUNC(SYSDATE) FROM DUAL ;
SELECT TRUNC(DCSRQ) FROM PAT_TA_BINFO
–查询一天前的数据 1
select t.* from My_Table t
where to_char ( to_date ( t.paytime , ‘yyyy-MM-dd HH24:mi:ss’ ) , ‘yyyy-MM-dd’) = to_char( sysdate-1 , ‘yyyy-MM-dd’)
注释:开始一直用 to_char ( t.paytime , ‘yyyy-MM-dd’),报错ORA-01722:无效数字。
因为 to_char(日期,“转换格式” ) :是将日期类型按照“转换格式”转换,而My_Table中的paytime是varchar类型,所以必须先通过
to_date(“要转换的字符串”,“转换的格式”) 将其转换成日期后再通过to_char转换成时间:年月日。
–查询一天前的数据 2
select * from My_Table
where trunc ( to_date ( paytime, ‘yyyy-MM-dd HH24:mi:ss’) ) = trunc ( sysdate - 1 )
注释:My_Table中的paytime是varchar类型,所以必须先通过to_date(“要转换的字符串”,“转换的格式”) 将其转换成日期后才能使用trunc
或者 select * from My_Table where paytime=trunc(sysdate) -1
注释:这个时候My_Table中的paytime是date型
–查询星期日期的语句
select a.* from out_Ta_tabs a where trunc(djzrq,‘IW’) = a.djzrq;–IW是ISO标准周
select a.* from out_Ta_tabs a where to_char(a.djzrq,‘day’)=‘星期一’ ;–to_char(a.djzrq,‘day’) 可以显示时间日期的星期数
- List item