Oracle培训笔记三
2014-07-25
要点五：事务
ddl语句
- create
- drop
- modify
dml语句
- select
- update
- insert
- delete
事务相关
-
定义：一个事务包含一条或者多条sql语句，是逻辑管理单元（原子单元）；事务一般开始于第一条sql语句，结束于commit或者rollback或者ddl语句。
-
commit,rollback属于显示提交事务，而ddl语句属于隐式提交事务。
-
rollback：回滚到上一个事务的提交点。
事务中的保存点（savepoint）
-
在事务的上下文中声明称为savepoint符号，将一个较长的事务分隔成较小的局部。
-
用法：rollback to savepoint b；
commit、rollback例子
- 创建t1表，表结构如下：
SQL> desc t1;
名称 类型
--------------------- ---------------------
ID NUMBER(38)
AGE NUMBER(38)
NAME VARCHAR2(20)
- 往t1表中插入两条数据
SQL> insert into t1(id,age,name) values (1,18,'java1');
已创建 1 行。
SQL> insert into t1(id,age,name) values (2,33,'java2');
已创建 1 行。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ------------------------------
1 18 java1
2 33 java2
- 新开一个dos窗口，用当前账户连接到数据库，查询该表数据，如下所示：
C:\Documents and Settings\Administrator>sqlplus test/123
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 7月 25 16:36:37 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t1;
未选定行 // 查询不到数据
- 在原dos窗口中，执行commit操作
SQL> commit;
提交完成。
- 在新dos窗口再次查询，结果正常显示。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ---------------
1 18 java1
2 33 java2
- 接着在原dos窗口往t1表中插入一条数据
SQL> insert into t1(id,age,name) values (100,143,'java103'); //插入一条新数据
已创建 1 行。
SQL> select * from t1; //查询如下，显示插入数据成功。
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
100 143 java103
- 执行rollback操作
SQL> rollback;
回退已完成。
SQL> select * from t1; //回退后，再次查询，表数据恢复到第一次commit时。
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
savepoint、rollback例子
- t1表中现有数据
SQL> rollback;
回退已完成。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
- 插入3条数据，每插入一条数据，做一个保存点。
SQL> insert into t1(id,age,name) values (200,43,'java200');
已创建 1 行。
SQL> savepoint a; // 保存点a
保存点已创建。
SQL> insert into t1(id,age,name) values (201,53,'java201');
已创建 1 行。
SQL> savepoint b; //保存点b
保存点已创建。
SQL> insert into t1(id,age,name) values (202,45,'java202');
已创建 1 行。
SQL> savepoint c; // 保存点c
保存点已创建。
SQL> select * from t1; // 查询数据
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
200 43 java200
201 53 java201
202 45 java202
- 执行rollback to savepoint xx 操作
SQL> rollback to savepoint c;//回滚到保存点c时段。
回退已完成。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
200 43 java200
201 53 java201
202 45 java202
SQL> rollback to savepoint b;//回滚到保存点b时段。
回退已完成。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
200 43 java200
201 53 java201
SQL> rollback to savepoint a; //回滚到保存点a时段。
回退已完成。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
200 43 java200
SQL> rollback; //回滚到上一次提交时段，恢复到之前原始状态。
回退已完成。
SQL> select * from t1;
ID AGE NAME
---------- ---------- ----------------------------------------
1 18 java1
2 33 java2
要点六：dual表
dual表是一张实际存在的表，任何用户都可读取，常常用在没有目标表的select语句块中。
用例：
SQL> select user from dual; //用途1，查询用户名
USER
------------------------------------------------------------
LHB
SQL> select 56*34 from dual; //用途2，简单的计算器
56*34
----------
1904
SQL> select sysdate from dual; //用途3，查询系统日期
SYSDATE
--------------
25-7月 -14
SQL> select to_char(sysdate, 'yyyy-mm-dd') from dual; //格式化日期
TO_CHAR(SYSDATE,'YYYY-MM-DD')
---------------------------
2014-07-25
字符串和日期之间的相互转换
-
日期转换成字符串（to_char） select to_char(sysdate, ‘yyyy-mm-dd’) from dual;
-
字符串转换成日期（to_date） select to_date(‘2014-07-24’,’yyyy-mm-dd’) from dual;
-
例子：
SQL> create table stu(id int, hiredate date);//创建一个表，有个date类型的字段
Table created
SQL> insert into stu2 values(1,'2014-07-12');
insert into stu2 values(1,'2014-07-12')
ORA-01861: 文字与格式字符串不匹配 //直接插入不成功，因'2014-07-12'是字符串类型，不是date类型
SQL> insert into stu2 values(1,to_date('2014-07-12', 'yyyy-mm-dd')); //进行转换后插入，成功。
1 row inserted
要点七：序列（sequence）
用来实现某个字段自增的功能。
创建序列
SQL> create sequence s1; //方法一
Sequence created
SQL> create sequence s2 increment by 1 start with 2; //方法二，指定步进值和初始值
Sequence created
查找序列
SQL> select s1.nextval from dual;
NEXTVAL
----------
1
SQL> select s2.nextval from dual;
NEXTVAL
----------
2
SQL> alter sequence s2 increment by 3; //修改序列的步进值
Sequence altered
SQL> select s2.nextval from dual;
NEXTVAL
----------
5
要点八：同义词（synonym）
-
从字面上理解就是别名，共呢个和视图功能类似，是一种映射关系。
-
同义词可分为public，private。
-
public：创建的同义词，所有用户可用
-
private：创建的同义词，只有那个创建的用户可用，其他用户不能用。
-
-
作用：
-
在多用户协同开发时，可以屏蔽对象名称或者拥有者；
-
为用户简化sql语句编写；
-
为分布式数据库提供位置透明化。
-
-
权限
与同义词相关的权限有create synonym、create any synonym、create public synonym权限。
- 用户在自己的模式下创建私有同义词，这个用户必须拥有create synonym权限，否则不能创建私有同义词。
如下所示，用户DM缺少CREATE SYNONYM权限，创建同义词时会报ORA-01031错误
SQL> create synonym test for dm.tm_wgg_atm_gtw_mon;
create synonym test for dm.tm_wgg_atm_gtw_mon
ORA-01031: insufficient privileges
用sys账号给DM账号赋予CREATE SYNONYM的权限
SQL> grant create synonym to dm;
Grant succeeded.
然后创建私有同义词
SQL> create synonym test for dm.tm_wgg_atm_gtw_mon;
Synonym created
- 如果需要在其它模式下创建同义词，则必须具有CREATE ANY SYNONYM的权限。