五、Oracle的事务和内置函数

Oracle中的事务和内置函数

什么是事务？

事务用于保证数据的一致性，它有一组相关的DML语句组成，该组的语句要么全部成功，要么全部失败。

如，网上转账就是典型的事务处理，用以保证数据的一致性。

事务和锁：

当执行事务操作时(DML语句)，Oracle会在被作用的表上加锁，防止其他用户修改表的表结构，这点对用户来讲是非常重要的。

事务的几个重要操作：

1. 设置保存点

savepoint a1;

1. 取消部分事务，回滚操作

rollback to a1;

1. 取消全部事务

rollback

只读事务

只读事务是指只允许执行查询语句(select)的操作，而不允许其他任何DML语句操作的事务。使用只读事务可以确保用户只能取到某时间点的数据。

例如：机票代售点每天18点开始统计今天的销售情况，这时可以使用只读事务，在设置了只读事务后，尽管其他会话可能会提交新的事务，但是只读事务将不会取得最新事务的变化，从而可以保证取得特定时间点的数据信息。

 

sql函数使用

字符函数

介绍：字符函数是Oracle中最常用的函数

lower(char)：将字符串转换为小写格式；

upper(char)：将字符串转换为大写格式；

length(char)：返回字符串的长度；

substr(char,startIndex,contentLength)：截取字符串的字串；

如：显示首字母大写、其余字母小写的员工姓名；

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

注：oracle中用||连接2个字符串

如：以首字母小写的格式显示所有员工姓名；

select lower(ename)||upper(ename,2,length(ename)-1) from emp;

replace(char,search_string,replace_string)：字符串替换；

instr(char1,char2,[,n[,m]])：取字串在字符串中的位置；

 

数学函数

数学函数的输入参数和返回值的数据类型都是数字型的。数学函数包括cos，cosh，sin，sinh，exp，ln，sqrt，tan，tanh，acos，asin，atan，round。

常用的有：

round(n,[m])：执行四舍五入，如果省略掉m则四舍五入到整数；如果m是正数，则四舍五入到小数点的后m位；如果m是负数，则四舍五入到小数点的m为前；

trunc(n,[m])：该函数用于截取数字。如果省掉m，就截去小数部分；如果m是正数就截取到小数点后m位；如果m是负数就截取到小数点前m位。

mod(m,n)：取模

floor(n)：返回小于或是等于n的最大整数，向下取整；

ceil(n)：返回大于或是等于n的最小整数，想上取整；

对数字的处理，在财务系统或者银行系统里使用的最多，不同的处理方法，对财务报表有不同的影响。

如：显示在一个月为30天的情况下所有员工的日新金，忽略余数；

select trunc(sal/30),ename form emp;

select floor(sal/30),ename from emp;

 

其他数学函数：

abs(n)：返回数字n的绝对值

acos(n)：返回数字的反余弦值

asin(n)：返回数字的反正弦值

atan(n)：返回数字的反正切值

cos(n)：返回数字的余弦值

exp(n)：返回e的n次方幂

log(m,n)：返回对数值

power(m,n)：返回m的n次方幂

 

日期函数

日期函数用于处理date类型的数据。

默认情况下，Oracle日期格式是dd-mon-yy即12-8月-98

sysdate：该函数返回系统时间

add_months(d,n)：指定日期增加n个月份

last_day(d)：返回指定日期所在月份的最后一天

例如：查找入职8个月多的员工

select * from emp where add_months(hiredate,8)<sysdate;

例如：显示满10年服务年限的员工姓名与受雇日期

select ename,hiredate from emp where sysdate>=add_months(hiredate,10*12);

例如：对于每个员工，显示其加入公司的天数

select ename,floor(sysdate-hiredate) "入职天数" from emp;

select ename,trunc(sysdate-hiredate) "入职天数" from emp;

例如：找出各个月倒数第三天受雇的所有员工

select ename from emp where hiredate=last_day(hiredate)-2;

 

转换函数

转换函数用于将数据类型从一种转为另一种。在某些情况下，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(10); 这时Oracle会自动将10转换为'10'.

注意：尽管Oracle可以进行隐含的数据类型转换，但是并不适应于所有情况，为了提高程序的可靠性，应该使用转换函数进行转换。

 

to_char()

我们可以使用select ename，hiredate,sal from emp where deptno=10;显示信息，但是在某些情况下并不能满足所有需求；

例如：如何显示日期的格式为 时/分/秒

 

例如：如何显示薪水为指定的货币

select ename, to_char(sal,'L99999.99') from emp;

返回的是：RMB800.00

 

 

 

如：显示每个员工的入职日期，精确到时分秒

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

 

例子：显示1980年入职的所有员工

select ename form emp where to_char(hiredate,'yyyy')=1980;

例子：显示所有12月份入职的员工

select ename from emp where to_char(hiredate,'mm')=12;

 

to_date

函数to_date()将字符串转换为日期类型。

例子：按照中国人的习惯年-月-日添加日期

insrt into emp(hiredate) values(to_date('1990-12-09','yyyy-mm-dd')) where deptno=8888;

 

系统函数：sys_context

1. terminal：当期会话客户所对应的终端表示符
2. language：语言
3. db_name：当前数据库名称
4. nls_date_format：当期会话客户所对应的日期格式
5. session_user：当期会话客户所对应的 数据库用户名
6. current_schema：当期会话客户所对应的 默认方案
7. host：数据库所对应的主机名称

通过该函数，可以查看一些重要的系统信息，如查询正在使用的数据库：、

select sys_context('userenv','db_name') from dual;