--===============
--author:yeeXun
--date:发表于 @2010年12月21日 17:03:00
--address:Jau 17-304
--===============
一.字符函数
♢lower(char):将字符串转化为小写格式
♢upper(char):将字符串转化为大写格式
♢length(char):返回字符串的长度
♢substr(char,m,n):在char字符串中从m开始,截取n个字符
❤如果省略了n,那么默认长度是m位置以后剩下的char的长度
实例:
?将所有员工的名字按小写方式显示:
select lower(ename) name,job from emp;
NAME JOB
---------- ---------
smith CLERK
allen SALESMAN
ward SALESMAN
...
14 rows selected
?将所有员工的名字按大写的方式显示:
select upper(ename) from emp order by ename;
UPPER(ENAME)
------------
ADAMS
ALLEN
BLAKE
...
14 rows selected
?显示正好为5个字符的员工的姓名:
select ename,job,sal from emp where length(ename)=5;
ENAME JOB SAL
---------- --------- ---------
SMITH CLERK 800.00
ALLEN SALESMAN 1600.00
JONES MANAGER 2975.00
...
8 rows selected
?显示所有员工姓名的前三个字符:
select substr(ename,1,3) 姓名 from emp;
姓名
------
SMI
ALL
WAR
...
14 rows selected
?以首字母大写其他字母小写的方式显示所有员工的姓名:
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1) ) 姓名 from emp;
姓名
--------------------
Smith
Allen
Ward
...
14 rows selected
?将首字母小写其他字符大写的方式显示员工姓名:
select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1) 姓名 from emp;
姓名
--------------------
sMITH
aLLEN
wARD
...
14 rows selected
/*郁闷啊,突然的断电,我写的东西都没了,登录PL/SQL Developer说没有监听 程序,难道是监听程序自动关闭了?但是由oracle附带的oracle SQL*Plus登录, 能成功登录。
我的电脑->管理->服务和应用程序->服务
查看,原来真的这突然的断电导致了oracle监听进程OracleOraDb10g_home1TNSListener关闭,手动启动;再次登录PL/SQL Developer,还说没有监听进程,再次郁闷;重新启动,登录PL/SQL Developer,成功!原来啊,刚刚才的监听进程启动没生效!
*/
♢replace(char1,search_string,replace_string):在char1里面用 replace_string来替换search_string。
?显示所有员工的姓名,用"我是A"替换所有"A":
select replace(ename,'A','我是大啊') 替换后的姓名 from emp;
替换后的姓名
-------------------------------------------------------------- ------------------
SMITH
我是大啊LLEN
W我是大啊RD
M我是大啊RTIN
BL我是大啊KE
SCOTT
我是大啊D我是大啊MS
J我是大啊MES
...
14 rows selected
♢instr(char1,char2,[,n[,m]]):取子串在字符串的位置
二.数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的,数学函数有:cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,...常用的有:
♢round(n,[m]):用于执行四舍五入,如果省略了m,则四舍五入到整数,如果m 为正数,则四舍五入到小数的m位后,如果m为负数,则四舍五入到小数点的m位前。
?计算奖金不为空的员工的年薪
select ename ,(round(sal)+round(comm))*13 年薪,sal from emp where comm is not null;
ENAME 年薪 SAL
---------- ---------- ---------
ALLEN 24700 1600.00
WARD 22750 1250.00
MARTIN 34450 1250.00
TURNER 19500 1500.00
♢trunc(n,[m]):用于截取数字,如果省略掉m,就截取小数部分,如果m是正数, 就截取到小数点的m位后,如果m为负数,则截取到小数点的前m位
♢mod(m,n):求m和n的余数
♢floor(n):返回小于或等于n的最大整数
♢ceil(n):返回大于会等于n的最小整数
dual表:在做oracle测试时,可以使用此表,主要用于做测试,虚拟。
?显示在一个月为30天的情况下所有员工的日薪金,忽略余数:
select trunc(sal/30),ename from emp;
TRUNC(SAL/30) ENAME
------------- ----------
26 SMITH
53 ALLEN
41 WARD
...
14 rows selected
select floor(sal/30),ename from emp;
结果与上面的相同
其他数学函数:
Abs(n):返回n的绝对值
Acos(n):返回n的反余弦值
Asin(n):返回n的反正弦值
Atan(n):返回n的反正切值
Cos(n):
Exp(n):返回e的n次幂
Log(m,n):取对数
Power(m,n):返回m的n次幂
三.日期函数
/*
当打开PL/SQL Developer进行登录时,弹出一个警告窗口:(ORA-12560:TNS: 协议适配器错误),之后无法登录。我的电脑->属性->管理->服务,发现我的oracle 监听进程自动关闭了,当我启动时,弹出这样的警告信息:(在本地计算机上的 OracleOraDb10g_home1TNSListener服务启动后又停止。一些服务自动停止,如 果它们没有什么可做的,例如"性能日志和警报"服务)。也就是启动失败,但是用 oracle自动附带的Oracle SQL*Plus登录,成功了,这是为什么呢?*/
介绍
日期函数用于处理date类型的数据,默认情况下日期格式为dd-mm-yy,例如 25-7-1987。
1.sysdate:返回系统时间
2.add_months(d,n):
?查找已经入职8各月多的员工
select ename,job,sal from emp where sysdate>add_months(hiredate,300)
?显示满10年服务年限的员工的姓名和雇佣日期
select ename,job,sal from emp where sysdate>add_months(hiredate,240)
?对于每个员工,显示加入公司的天数
select trunc(sysdate-hiredate) 入职天数,ename 姓名 from emp;
3. last_day(d):返回指定日期所在月份的最后一天
?找出各月倒数第三天受雇的所有员工
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
-----------------------------------------
以上内容是在两周之前写下的
-----------------------------------------
/*课程设计过去了一个周,紧接着是一课程期末考试,和6级考试,哎!紧张的日子 过去了,现在有时间来继续我的oracle学习了。
两周之前,我的PL/SQL Devloper出了点问题,一直未去解决。今天打开它时,居 然已经过了30天的试用期,郁闷,卸载了重新安装吧!可是找不到源文件了,于是上 找了一个PL/SQL Devloper8.0版本的,试用期也是30天。刚刚安装好,用户 scott登录,居然连接超时,没有出现前两周出现的情况。但是提示未登录,于是建 一个command window,在里面输入:connect scott/tiger
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
居然连接成功了!
SQL> show user
User is "scott"
再次验证:
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
.......
14 rows selected
*/
四.转换函数
转换函数用于将数据类型从一种转换为另一种。在某些情况下,oracle server允许值的数据类型和实际的不一样,这时oracle server会隐式的转化数据类型。
例如:
create table t1(id int);
insert into t1 values('10')--这样oracle会自动的将'10'转换为10;
create table t2(id varchar2(10));
insert into t2 values(1)--这样oracle就会自动的将1转换为'1';
1.to_char函数
to_char
插入一条数据:
SQL> insert into emp values(8881,'test用户','MANAGER',7782,sysdate,23,23,10);
将emp表中的雇佣日期转换为字符串的形式显示出来:
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ------------------------------
test用户 2010-12-21 16:03:33
SMITH 1980-12-17 00:00:00
ALLEN 1981-02-20 00:00:00
......
15 rows selected
这里说一下日期格式:
yy:两位数字的年份 2010-->10
yyyy:四位数字的年份 2010
mm:两位数字的月份 2月-->02
dd:两位数字的天 5号-->05
hh24:时间的24小时制
hh12:时间的12小时制度
mi、ss: 显示分钟/秒
9:显示数字,忽略前面的0
0:显示数字,如位数不足,则用0补齐
·:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前加美元符号
L:在数字前加本地货币符号
C:在数字前加国际货币符号
G:在指定位置显示组分隔符
D:在指定位置显示小数点符号(.)
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99,999.99') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99,999.99')
---------- ------------------------------ -------------------------
test用户 2010-12-21 16:03:33 ¥23.00
SMITH 1980-12-17 00:00:00 ¥800.00
ALLEN 1981-02-20 00:00:00 ¥1,600.00
......
15 rows selected
?显示1980年入职的员工
SQL> select * from emp where to_char(hiredate,'yyyy')=1980;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
2.to_date函数
to_date函数用于将字符串转换为date类型的数据。
3.sys_context
每个用户对应一个方案,方案名和用户名相同。用户一旦被创建后,就为其创建了一个方案。
1).terminal:当前会话客户所对应的终端的标识符
2).language:语言
3).db_name:当前数据库名称
4).mls_date_format:当前会话客户所对应的日期格式
5).session_user:当前会话客户所对应的数据库用户名
6).current_schema:当前会话客户所对应的默认方案名
7).host:返回数据库所在主机名称
SQL> select sys_context('userenv','language') from dual;
SYS_CONTEXT('USERENV','LANGUAG
----------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
实例这里有