5 oracle的事务

始自第13讲 oracle的事务

在oracle中操作数据 -- 使用子查询插入数据
当使用values子句时，一次只能插入一行数据，当使用子查询插入
数据时，一条insert语句可以插入大量的数据。当处理行迁移或者
装载外部表的数据到数据库时，可以使用子查询来插入数据。
==================================================================
SQL> create table y3w (myId number(4), myName varchar2(50), myDept number(5));

Table created

SQL> insert into y3w (myId,myName,mydept) select empno,ename,deptno from emp;

17 rows inserted
==================================================================

使用update语句更新数据时，既可以使用表达式或者数值直接
修改数据，也可以使用子查询修改数据。

？希望员工scott的岗位、工资、补助与smith员工一样
第一感觉：
update emp set job=(select job from emp where ename='SMITH'),
sal=(select sal from emp where ename='SMITH'),...
其实可以这么来写
update emp set (job,sal,comm)=(select job,sal,comm from emp where
ename='SMITH') where ename='SCOTT';

===========================================================================

什么是事务？
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么
全部成功，要么全部失败。
如：网上转账就是典型的要哦用事务来处理，用以保证数据的一致性。

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

提交事务
当执行commit语句之后即可以提交事务。当执行了commit语句之后，会确认
事务的变化、结束事务、删除保存点、释放锁，当使用commit语句结束事务之后，
其他会话将可以查看到事务变化后的新数据

回退事务
先介绍一个下保存点的概念和作用：
保存点是事务中的一点。用于取消部分事务，当结束事务时，会自动的删除该事务所定义
的所有保存点。当执行rollback时，通过指定保存点可以回退到指定的点。

回退事务必须保证没有提交事务，如果提交了事务，无论做了多少个保存点，都没用了。
如果没提交单退出的话，会自动提交。

事务的几个重要操作：
①设置保存点savepoint a
②取消部分事务rollback to a
③取消全部事务rollback

java程序中如何使用事务
在java操作数据库时，为了保证数据的一致性，比如转账操作
①从一个账户减掉10$
②在另一个账户上加入10$，我们看看如何使用事务？
connection.setAutoCommit(false);
connection.commit();

===============================================================

只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其他dml操作的事务，
使用只读事务可以确保用户只能取得某时间点的数据。假定机票代售点每天18点
开始统计今天的销售情况，这时可以使用只读事务。在设置了只读事务后，尽管
其他会话可能会提交新的食物，但是只读事务将不会取得最新数据的变化，从而
可以保证取得特定时间点的数据信息。

设置只读事务
set transaction read only
【试验方法：开两个sql *plus，一个用SYSTEM登陆，一个用SCOTT登陆，一个
设置只读事务，另一个不设置只读事务，进行两相对比】

=================================================================

sql函数的使用 -- 字符函数
字符函数是oracle中最常用的函数：
①lower(char)：将字符串转化为小写的格式
②upper(char)：将字符串转化为大写的格式
③length(char)：返回字符串的长度
④substr(char, m, n)：取字符串的子串
？将所有员工的名字按小写的方式显示
select lower(ename),sal from emp;
？将所有员工的名字按大写的方式显示
select upper(ename) from emp;
？显示正好为5个字符的员工的姓名
select ename from emp where length(ename)=5;
？显示所有员工姓名的前三个字符【从第1个开始取，取的长度为3个】
select substr(ename,1,3) from emp;
？以首字母大写的方式显示所有员工的姓名
1.完成首字母大写
select upper(substr(ename,1,1)) from emp;
2.完成后面字母小写
select lower(substr(ename,2,length(ename)-1)) from emp;
3.合并
select
upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))
from emp;
？以首字母小写的方式显示所有员工的姓名
1.完成首字母大写
select lower(substr(ename,1,1)) from emp;
2.完成后面字母小写
select upper(substr(ename,2,length(ename)-1)) from emp;
3.合并
select
lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1))
from emp;

⑤replace(char1,search_string,replace_string)
⑥instr(char1,char2,[,n,[,m]])取子串在字符串中的位置
？显示所有员工的姓名，用a替换所有“A”
select replace(ename,'A','我是猪') from emp;
-------------------------------------------------------------
SQL> select replace(ename,'A','我是猪') from emp;

REPLACE(ENAME,'A','我是猪')
------------------------------------------------------------
SMITH
我是猪LLEN
W我是猪RD
JONES
M我是猪RTIN
BL我是猪KE
CL我是猪RK
SCOTT
KING
TURNER
我是猪D我是猪MS
J我是猪MES
FORD
MILLER
小红
小明
小杨

17 rows selected
-----------------------------------------------------------------

数学函数的输入参数和返回值的数据类型是数字类型的。数学函数包括cos，
cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,我们
讲最常用的：
①round(n,[m])
②trunc(n,[m])
③mod(m,n)
④floor(n)
⑤ceil(n)
对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，
对财务报表有不同的结果。

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的最小整数

select (round(sal)+round(comm))*13 from emp;
select round(sal) from emp where ename='scott';

？显示在一个月为30天的情况所有员工的日薪金，忽略余数。
select trunc(sal/30),ename from emp; 或
select floor(sal/30),ename from emp;

dual:亚元表，在做oracle函数测试无表可用的时候可以用这个。
select mod(10,2) from dual; 0
select mod(10,3) from dual; 1

其他的数学函数：
abs(n) 返回数字n的绝对值
select abs(-13) from dual;
acos(n): 返回数字的反余弦值
asin(n): 返回数字的反正弦值
atan(n)：返回数字的反正切值
cos(n):
exp(n): 返回e的n次幂
log(m,n): 返回对数值
power(m,n): 返回m的n次幂

=================================================================
sql函数的使用 -- 日期函数
日期函数用于处理date类型的数据。
默认情况下日期格式是dd-mon-yy即12-7月-78
(1)sysdate: 该函数返回系统时间
select sysdate from dual;
(2)add_months(d,n)
(3)last_day(d):返回指定日期所在月份的最后一天
？查找已经入职 300 个月多的员工
select * from emp where sysdate>add_months(hiredate,300);
？显示满10年服务年限的员工的姓名和雇用日期
select * from emp where sysdate>=add_months(hiredate,12*10);
？对于每个员工，显示其加入公司的天数【不能用单引号】
select trunc(sysdate-hiredate) "入职天数" from emp;
？找出各月倒数第3天受雇的所有员工
select hiredate,last_day(hiredate) from emp;
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

======================================================================

转换函数
转换函数用于将数据类型从一种转为另外一种。在某些情况下，
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';
我们要说的尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。

======================================================================

to_char函数
你可以使用select ename,hiredate,sal from emp where deptno=10;
显示信息，可是，在某些情况下，这个并不能满足你的需求。

？日期是否可以显示 时/分/秒【为什么查询出来的全部都是整数？】
【因为添加的时候没有按照这种格式添加日期~】
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
insert into emp values(8881,'test用户','MANAGER',7782,sysdate,23,23,10);
？薪水是否可以显示指定的货币符号【也是用to_date来进行处理的~】
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99,999.99') from emp;
=================================================================
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')
---------- ------------------------------ ------------------------
SMITH 1980-12-17 00:00:00 ￥800.00
ALLEN 1981-02-20 00:00:00 ￥1,600.00
WARD 1981-02-22 00:00:00 ￥1,250.00
JONES 1981-04-02 00:00:00 ￥2,975.00
MARTIN 1981-09-28 00:00:00 ￥1,250.00
BLAKE 1981-05-01 00:00:00 ￥2,850.00
CLARK 1981-06-09 00:00:00 ￥2,450.00
SCOTT 1987-04-19 00:00:00 ￥3,000.00
KING 1981-11-17 00:00:00 ￥5,000.00
TURNER 1981-09-08 00:00:00 ￥1,500.00
ADAMS 1987-05-23 00:00:00 ￥1,100.00
JAMES 1981-12-03 00:00:00 ￥950.00
FORD 1981-12-03 00:00:00 ￥3,000.00
MILLER 1982-01-23 00:00:00 ￥1,300.00
test用户 2011-03-13 20:43:17 ￥23.00
小红 1988-11-12 00:00:00 ￥88.90
小明 1988-11-12 00:00:00 ￥88.90
小杨 1988-11-12 00:00:00 ￥88.78

18 rows selected
=================================================================

yy：两位数字的年份 2004-->04
yyyy: 四位数字的年份 2004年
mm: 2位数字的月份 8月 --> 08
dd: 2位数字的天 30号 --> 30
hh24: 8点 --> 20
hh12: 8点 --> 08
mi、ss --> 显示分钟、秒
--------------------------------------------------
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前面加美元
L：在数字前面加本地货币符号
C：在数字前加国际货币符号
G：在指定位置显示组分隔符
D：在指定位置显示小数点符号(.)
--------------------------------------------------

to_char函数
？显示1980年入职的所有员工
select * from emp where to_char(hiredate,'yyyy')=1980;
？显示所有12月份入职的员工
select * from emp where to_char(hiredate,'mm')=12;

==========================================================

to_date函数：
用于将字符串转换成date类型的数据。
？能否按照中国人习惯的额方式年-月-日添加日期【略】

sys_context: 系统函数
①terminal: 当前会话客户所对应的终端的额标示符
②language: 语言
③db_name: 当前数据库名称
④nls_date_format: 当前会话客户所对应的日期格式
⑤session_user: 当前会话客户所对应的数据库用户名
⑥current_schema: 当前会话客户所对应的默认方案名【一个用户对应一个方案】?
⑦host: 返回数据库所在主机的名称
通过该函数，可以查询一些重要信息，比如你怎么在使用哪个数据库？
select sys_context('userenv','db_name') from dual;
select sys_context('userenv','language') from dual;
select sys_context('userenv','session_user') from dual;

用户和方案的关系？用户一旦创建之后，oracle就会自动为之创建一个方案
oracle是以方案的方式来管理数据对象的，方案的名字和用户的名字一摸一样~
方案里面究竟有什么东西呢？方案里面有各种各样的数据对象：
表、视图、触发器、存储过程、包、函数、序列 等等~

=============================================================