Oracle总结

1.distinct
select distinct t.* from ut_user t
使用distinct时，在查询的数据中不允许有重复数据出现(重复数据指查出数据的一条记录，包括查出的所有列)
2.通配符： "%"代表0个、1个或多个任意字符；"_"代表一个任意字符。
3.在Oracle中一般查询语句不允许更新数据，
Example：select t.* from ut_user t 只能查询，不可更新
但以下两种方式可以实现更新数据:
select distinct t.*,rowid from ut_user t
select t.* from ut_user t for update
4.order by子句对多列排序：select t.* from ut_user where age>23 order by username,age;
现根据username排序，当username相同时，在根据age排序，asc（升序，默认），desc（降序）。

5.oracle常用统计函数：count，min，max，sum，avg；
6.游标和表复制
/**********游标**********/
declare
user_id number;
user_username varchar2(20);
user_password varchar2(20);
user_age number;
cursor user_cur is --声明游标
select * from ut_user t where t.id<3;
begin
open user_cur;--打开游标
loop
exit when not user_cur%found;
fetch user_cur into user_id,user_username,user_password,user_age;--提取游标，将第一行数据放入变量中，游标后移
dbms_output.put_line(user_id || ' ' ||user_username);
end loop;
close user_cur;--关闭游标
end;

/**********复制表**********/
create table bbb as select t.* from ut_user t where 1<>1 --复制表结构
create table bbb as select t.* from ut_user t --复制表结构和表数据
create table ccc as select t.id,https://www.wendangku.net/doc/ab6361876.html,ername from ut_user t --复制部分表结构
create table ddd as select t.id,https://www.wendangku.net/doc/ab6361876.html,ername from ut_user t where 1<>1 --复制部分表结构及数据
7.每张表可以建立12个触发器：
BEFORE INSERT
BEFORE INSERT FOR EACH ROW

AFTER INSERT
AFTER INSERT FOR EACH ROW

BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW

AFTER UPDATE
AFTER UPDATE FOR EACH ROW

BEFORE DELETE
BEFORE DELETE FOR EACH ROW

AFTER DELETE
AFTER DELETE FOR EACH ROW

8.在创建视图时，只能使用as，而不能使用is；在声明游标时，只能使用is，而不能使用as。
同义词创建不支持replace命令。

9.rownum使用方法：
(1).使用rownum子查询：
rownum是一个总是从1开始的伪列，当查询条件rownum>n(n>1)时，不能从数据库查到记录，因此要
通过子查询解决：select t.* from (select rownum r,users.* from users ) t where r>2;
结果：
SQL> select t.* from (select rownum r,users.* from users ) t where r>2;
R ID USERNAME PASSWORD AGE
---------- --------- -------------------- -------------------- ----------
3 3 sd3 dd3 23
4 4 dsd4 ddd4 23
5 5 adsd5 ddd5

43
先通过子查询(select rownum r,users.* from users)获取全部数据或通过rownum前n条记录，然后从中查询满足条件的数据；
(2).使用rownum排序：
例如：
SQL> select rownum r,users.* from users order by username;
R ID USERNAME PASSWORD AGE
---------- --------- -------------------- -------------------- ----------
5 5 adsd5 ddd5 43
4 4 dsd4 ddd4 23
2 2 qsd2 dd2 23
1 1 sd1 dd1 23
3 3 sd3 dd3 23
从中可以看出rownum并不是按照name列来生成序号的，因为系统是按照记录插入式的顺序给记录排的号，
为了解决这个问题，必须使用子查询：
SQL> select rownum r,t.* from (select * from users order by username)t ;
R ID USERNAME PASSWORD AGE
---------- --------- -------------------- -------------------- ----------
1 5 adsd5 ddd5 43
2 4 dsd4 ddd4 23
3 2 qsd2 dd2 23
4 1 sd1 dd1 23
5 3 sd3 dd3 23

10.常见函数使用：
(1)
SQL> select upper('dsf'),lower('sdf'),initcap('hello word!'),concat('hello','word') from dual;
运行结果：
UPPER('DSF') LOWER('SDF') INITCAP('HELLOWORD!') CONCAT('HELLO','WORD')
------------ ------------ --------------------- ----------------------
DSF sdf Hello Word! helloword
(2)substr(string,a,b) a为起始位，默认为1，b为要截取字符串长度；其中a和b以字符为单位。
substrb()与substr相似，其中a和b以字节为单位。
测试结果：
SQL> select initcap('hello word!'),concat('hello','word'),substrb('世界你好',1,4) from dual;

INITCAP('HELLOWORD!') CONCAT('HELLO','WORD') SUBSTRB('世界你好',1,4)
--------------------- ---------------------- -----------------------
Hello Word! helloword （链接两个字符） 世界(注：4个字节)

SQL> select initcap('hello word!'),concat('hello','word'),substrb('世界你好',2,4) from dual;

INITCAP('HELLOWORD!') CONCAT('HELLO','WORD') SUBSTRB('世界你好',2,4)
--------------------- ---------------------- -----------------------
Hello Word! helloword 界(注：4个字节，其中‘世’和‘你’各占一个字节)

SQL> select initcap('hello word!'),concat('hello','word'),substrb('世界你好',3

,4) from dual;

INITCAP('HELLOWORD!') CONCAT('HELLO','WORD') SUBSTRB('世界你好',3,4)
--------------------- ---------------------- -----------------------
Hello Word! helloword 界你(注：4个字节)

(3)instr（源字符串，目标字符串，a，b匹配序号）
a代表查找的起始位置，此参数可选，默认为1，此参数为正，左起检索；为负右起检索；
b代表查找第几次出现目标字符串，此参数可选，若省略默认为1.为负，报错。
没有找到目标字符串，函数返回0。
SQL> select length('世界你好！'),lengthb('世界你好!')，instr('helloword','word',1,1) from dual;
INSTR('HELLOWORD','WORD',1,1)
-----------------------------
6

(4)length与lengthb

SQL> select length('世界你好！'),lengthb('世界你好！') from dual;
测试结果：
LENGTH('世界你好！') LENGTHB('世界你好！')
-------------------- ---------------------
5 10 (！汉语叹号)

SQL> select length('世界你好！'),lengthb('世界你好!') from dual;

LENGTH('世界你好！') LENGTHB('世界你好!')
-------------------- --------------------
5 9 (!英文叹号)


(5)lpad()与rpad()
SQL> select lpad('hello',10,'ab'),lpad('hello',4,'ab'),lpad('hello',10) from dual;
测试结果：
LPAD('HELLO',10,'AB') LPAD('HELLO',4,'AB') LPAD('HELLO',10)
--------------------- -------------------- ----------------
ababahello hell hello

注：lpad(str1,n,str2),其中str2是可选参数，若无str2，将在str1左侧添加空格，n是返回字符串的长度，若
n>str1+str2 长度和，则循环添加str2到str1的左侧。
（6）trim（‘str1’ from ‘str2’）
SQL> select trim('h' from 'hello'),trim('o' from 'hello'),trim('H' from 'hello') from dual;
测试结果：
TRIM('H'FROM'HELLO') TRIM('O'FROM'HELLO') TRIM('H'FROM'HELLO')
-------------------- -------------------- --------------------
ello hell hello
注：str1只能为一个字符，可以从左右两边截取，区分大小写。默认截取空格。ltrim左截取，rtrim右截取。
（7）ascii(),chr(),replace(str1，str2，str3)(str3将str1中的str2代替，若str3为空，则删除str1中str2）
SQL> select ascii('a'),chr('97'),chr(97),replace('hello','ll','mm'),replace('hello','ll') from dual;
测试结果：
ASCII('A') CHR('97') CHR(97) REPLACE('HELLO','LL','MM') REPLACE('HELLO','LL')
---------- --------- ------- -------------------------- ---------------------
97 a a hemmo heo


(8)translate(x,y,z)函数，返回将 X 串中每个字符按它在 Y 串中出现的位置翻译成 Z 串中相应位
置的字符后的

结果，相当与替换。
SQL> select translate('this is an example','my is','@#$%^&') t from dual;
测试结果：
T
------------------
th%^$%^$an$exa@ple

(9)add_months(date,n)返回加上n月后的date值。
SQL> select to_date('2012-11-1','yyyy-mm-dd'),add_months(to_date('2012-11-1','yyyy-mm-dd'),4) from dual;
测试结果：
TO_DATE('2012-11-1','YYYY-MM-D ADD_MONTHS(TO_DATE('2012-11-1'
------------------------------ ------------------------------
2012/11/1 2013/3/1

11.case语句：
（1）SQL> select id,age ,
2 case when id = 1 then 'book'
3 when id = 2 then 'music'
4 when id = 3 then 'video'
5 when id = 4 then 'dvd'
6 else null
7 end
8 from users;
测试结果：
ID AGE CASEWHENID=1THEN'BOOK'WHENID=2
--------- ---------- ------------------------------
1 23 book
2 23 music
3 23 video
4 23 dvd
5 43
6 12
7 72
12.执行带参数存储过程（参数前不要忘记带冒号）
SQL> var flg number;
SQL> var msg varchar2;
SQL> exec col_upload_rtp_pr(25,:flg,:msg)
13.事务：
事务是指由一系列数据库操作组成的一个完整的逻辑过程。具有ACID四个特性(atomicity,consistency,isolation,durability).
原子性：一个事物所有的操作，要么全部完成吗，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被
回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始和结束以后，数据的完整性没有被破坏。这表示写入的资料完全符合所有的默认规则，这包含资料的精确度、
串联性以及后续数据库可以自发性的完成预定的工作。
隔离性：当两个或者多个事务并发访问（此处访问指查询和修改的操作）数据库的同一数据时所表现出的相互关系。事务隔离分为不
同级别，包括读未提交(Read uncommitted)、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：在事务完成以后，该事务对数据库所作的更改便持久地保存在数据库之中，并且是完全的。

如果几个事务要访问相同的数据项，为了保证数据库的一致性，可以让这几个事务：
①串行执行：即一个接着一个地执行事务；
②并发执行：即同时执行多个事务，但用并发控制机制来解决不同事务间的相互影响。
事务的提交与回滚：
提交( Commit ）
通知事务管理器一个逻辑工作单元已完成，所做的更新操作可以被提交或永久保留表明事务成功地结束执行有效性检验
回滚( RollBack)
通知事务管理器事务未能正常完成，数据库可能处于不一

致状态，当前事务所做的所有更新操作必须撤消表明事务不成功地结束

目前主要有两种方式实现ACID：第一种是Write ahead logging，也就是日志式的方式。第二种是Shadow paging。
系统日志的恢复步骤是：
1、反向扫描文件日志（即从最后向前扫描日志文件），查找该事务的更新操作。
2、对该事务的更新操作执行逆操作。即将日志记录“更新前的值”写
入数据库。这样，如果记录中是插入操作，则相当于做删除操作；若记录中是删除操作，则做插入操作；若是修改操作，则相
当于用修改前的值代替修改后的值。
3、继续反向扫描日志文件，查找该事务的其他更新操作，并做和2一样的同样处理。
4、如此处理下去，直至读到此事务的开始标记，事务的故障恢复就完成了。
并发的问题：
现有两处火车票售票点，同时读取某一趟列车车票数据库中车票余额为 X。两处售票点同时卖出一张车票，同时修改余额为 X -1
写回数据库，这样就造成了实际卖出两张火车票而数据库中的却记录只少了一张。产生这种情况的原因是因为两个事物读入同一数
据并同时修改，其中一个事物提交的结果破坏了另一个事物提交的结果，导致其数据的修改被丢失，破坏了事物的隔离性。并发控
制要解决的就是这类问题。
先查一下锁的概念：
封锁是一项用于多用户同时访问数据库的技术，是实现并发控制的一项重要手段，能够防止当多用户改写数据库时造成数据丢失和
损坏。当有一个用户对数据库内的数据进行操作时，在读取数据前先锁住数据，这样其他用户就无法访问和修改该数据，直到这一数
据修改并写回数据库解除封锁为止。

解决方法：
悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影
响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁
冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。
观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额
外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有
会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。乐观

并发控制（又名“乐观锁”，
Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务
能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又
修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观并发控制多数用于数据争用不大、冲突较少的环境中，这种
环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。
乐观并发控制的事务包括以下阶段：
读取：事务将数据读入缓存，这时系统会给事务分派一个时间戳。
校验：事务执行完毕后，进行提交。这时同步校验所有事务，如果事务所读取的数据在读取之后又被其他事务修改，则产生冲突，事务被中断回滚。
写入：通过校验阶段后，将更新的数据写入数据库。
优点与不足：
乐观并发控制相信事物之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和
死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题


事务的传播特性：
a. PROPAGATION_REQUIRED: 如果存在一个事务，则支持当前事务。如果没有事务则 开启
b. PROPAGATION_SUPPORTS: 如果存在一个事务，支持当前事务。如果没有事务，则非事务的执行
c. PROPAGATION_MANDATORY: 如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常。
d. PROPAGATION_REQUIRES_NEW: 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起。
e. PROPAGATION_NOT_SUPPORTED: 总是非事务地执行，并挂起任何存在的事务。
f. PROPAGATION_NEVER: 总是非事务地执行，如果存在一个活动事务，则抛出异常
g. PROPAGATION_NESTED：如果一个活动的事务存在，则运行在一个嵌套的事务中. 如果没有活动事务,
则按TransactionDefinition.PROPAGATION_REQUIRED 属性执行

Spring事务的隔离级别

a. ISOLATION_DEFAULT：这是一个PlatfromTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别.
另外四个与JDBC的隔离级别相对应
2. ISOLATION_READ_UNCOMMITTED：这是事务最低的隔离级别，它充许令外一个事 务可以看到这个事务未提交的数据。
这种隔离级别会产生脏读，不可重复读和幻像读。
3. ISOLATION_READ_COMMITTED： 保证一个事务修改的数据提交后才能被另外一个事务读取

。另外一个事务不能读取该事务未提交的数据
4. ISOLATION_REPEATABLE_READ： 这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻像读。
5. ISOLATION_SERIALIZABLE 这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。
除了防止脏读，不可重复读外，还避免了幻像读。

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个
数据，然后使用了这个数据。

不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，
由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。

幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。
同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有
没有修改的数据行，就好象发生了幻觉一样。