接 oracle-学习笔记-2
-------------------------------------
6.1 事务
事务的隔离性
锁 的机制
例如：
第一个窗口：
SQL> select * from books for update;
BOOKS_ID BOOKS_NAME PRICE QTY PUB
---------- ---------- ---------- ---------- ----------
8
1 中国文学 39 12 人民大学
2 外国文学 12.5 22 人民大学
3 英语阅读 33 21 清华大学
4 建筑艺术 6 33 清华大学
5 计算机入门 44.8 24 电子工业
6 数据结构 54.7 65 电子工业
7 语文 43 5 电子工业
已选择8行。
第二个窗口：
SQL> update books set books_name='bbb' where books_id=8;
一直停着不动了。
第一个窗口：
SQL> commit；
提交完成。
第二个窗口：
SQL> select * from books;
BOOKS_ID BOOKS_NAME PRICE QTY PUB
---------- ---------- ---------- ---------- ----------
8 bbb
1 中国文学 39 12 人民大学
2 外国文学 12.5 22 人民大学
3 英语阅读 33 21 清华大学
4 建筑艺术 6 33 清华大学
5 计算机入门 44.8 24 电子工业
6 数据结构 54.7 65 电子工业
7 语文 43 5 电子工业
已选择8行。
SQL> update books set books_name='ccc' where books_id=8;
已更新 1 行。
SQL> select * from books;
BOOKS_ID BOOKS_NAME PRICE QTY PUB
---------- ---------- ---------- ---------- ----------
8 ccc
1 中国文学 39 12 人民大学
2 外国文学 12.5 22 人民大学
3 英语阅读 33 21 清华大学
4 建筑艺术 6 33 清华大学
5 计算机入门 44.8 24 电子工业
6 数据结构 54.7 65 电子工业
7 语文 43 5 电子工业
已选择8行。
第一个窗口：
SQL> select * from books for update;
停着不动了，锁了。
第二个窗口：
SQL> commit;
提交完成。
第一个窗口：
BOOKS_ID BOOKS_NAME PRICE QTY PUB
---------- ---------- ---------- ---------- ----------
8 ccc
1 中国文学 39 12 人民大学
2 外国文学 12.5 22 人民大学
3 英语阅读 33 21 清华大学
4 建筑艺术 6 33 清华大学
5 计算机入门 44.8 24 电子工业
6 数据结构 54.7 65 电子工业
7 语文 43 5 电子工业
已选择8行。
SQL> commit;
提交完成。
7.触发器
对每一行
SQL> l
1 create or replace trigger del_deptid
2 after delete on d
3 for each row
4 begin
5 delete from e where did=:old.id;
6* end del_deptid;
SQL> /
触发器已创建
SQL> select * from d;
ID NAME
---------- ----------
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择7行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择6行。
SQL> delete from d where id=1;
已删除 1 行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
SQL> select * from d;
ID NAME
---------- ----------
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择6行。
SQL> l
1 create or replace trigger insert_deptid
2 after insert on d
3 for each row
4 begin
5 insert into e values (10,'aaa','男',:new.id);
6* end insert_deptid;
SQL> /
触发器已创建
SQL> select * from d;
ID NAME
---------- ----------
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择7行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择6行。
SQL> insert into d values (50,'hh');
已创建 1 行。
SQL> select * from d;
ID NAME
---------- ----------
50 hh
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择8行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
10 aaa 男 50
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择7行。
其中的 new old 是在内存中的两张逻辑表
old new
insert - √
delete √ -
update √ √
使用触发器级联更新：
SQL> l
1 create or replace trigger update_deptid
2 after update on d
3 for each row
4 begin
5 update e set did=:new.id where did=:old.id;
6* end update_deptid;
SQL> /
触发器已创建
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
10 aaa 男 50
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择7行。
SQL> select * from d;
ID NAME
---------- ----------
50 hh
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择8行。
SQL> update d set id=55 where id=50;
已更新 1 行。
SQL> select * from d;
ID NAME
---------- ----------
55 hh
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择8行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
10 aaa 男 55
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择7行。
触发器的语句体里面不能写 rollback和commit语句。
SQL> l
1 create or replace trigger books_delete
2 after delete on books
3 for each row
4 begin
5 if :old.books_id=1 then
6 raise_application_error(-20000,'不允许删除！');
7 end if;
8* end books_delete;
SQL> /
触发器已创建
SQL> select * from books;
BOOKS_ID BOOKS_NAME PRICE QTY PUB
---------- ---------- ---------- ---------- ----------
8 ccc
1 中国文学 39 12 人民大学
2 外国文学 12.5 22 人民大学
3 英语阅读 33 21 清华大学
4 建筑艺术 6 33 清华大学
5 计算机入门 44.8 24 电子工业
6 数据结构 54.7 65 电子工业
7 语文 43 5 电子工业
已选择8行。
SQL> delete from books where books_id=1;
delete from books where books_id=1
*
第 1 行出现错误:
ORA-20000: 不允许删除！
ORA-06512: 在 "SCOTT.BOOKS_DELETE", line 3
ORA-04088: 触发器 'SCOTT.BOOKS_DELETE' 执行过程中出错
其中的错误号 只能写 -20999 -- -20000 之间的错误号。
创建语句级别的触发器
SQL> l
1 create or replace trigger dml_aa
2 after insert or delete or update on aa
3 begin
4 if inserting then
5 insert into mylog values (user,sysdate,'I');
6 elsif deleting then
7 insert into mylog values (user,sysdate,'D');
8 else
9 insert into mylog values (user,sysdate,'U');
10 end if;
11* end dml_aa;
SQL> /
触发器已创建
SQL> select * from aa;
A1 A2 A3
---------- ---------- ----------
avx ssw aa
avx swx aa
aeex ddswx aa
bbbb xxx xxxx
esd
ddd er
ee ssss ee
ddda dcc xxx
bbbb xxx xxx
da ccc xxx
已选择10行。
SQL> insert into aa values('01','掌声','很好');
已创建 1 行。
SQL> select * from aa;
A1 A2 A3
---------- ---------- ----------
avx ssw aa
avx swx aa
aeex ddswx aa
bbbb xxx xxxx
01 掌声 很好
esd
ddd er
ee ssss ee
ddda dcc xxx
bbbb xxx xxx
da ccc xxx
已选择11行。
SQL> update aa set a2='00000' where a1='01';
已更新 1 行。
SQL> select * from aa;
A1 A2 A3
---------- ---------- ----------
avx ssw aa
avx swx aa
aeex ddswx aa
bbbb xxx xxxx
01 00000 很好
esd
ddd er
ee ssss ee
ddda dcc xxx
bbbb xxx xxx
da ccc xxx
已选择11行。
SQL> delete from aa where a1='01';
已删除 1 行。
SQL> select * from aa;
A1 A2 A3
---------- ---------- ----------
avx ssw aa
avx swx aa
aeex ddswx aa
bbbb xxx xxxx
esd
ddd er
ee ssss ee
ddda dcc xxx
bbbb xxx xxx
da ccc xxx
已选择10行。
SQL> commit;
提交完成。
SQL> select * from mylog;
CURR_USER
CURR_DATE A
-----------------------------------------------------------------------------------
-------------- -
SCOTT
11-12月-10 I
SCOTT
11-12月-10 U
SCOTT
11-12月-10 D
SQL> col curr_user format a10
SQL> col curr_date format a10
SQL> col act format a3
SQL> select * from mylog;
CURR_USER CURR_DATE ACT
---------- ---------- ---
SCOTT 11-12月-10 I
SCOTT 11-12月-10 U
SCOTT 11-12月-10 D
SQL> select curr_user "用户",to_char(curr_date,'yyyy-mm-dd hh24:mi:ss') "时间",act "操作" from mylog;
用户 时间 操作
---------- -------------------- ----------
SCOTT 2010-12-11 21:31:40 I
SCOTT 2010-12-11 21:32:17 U
SCOTT 2010-12-11 21:32:48 D
利用触发器实现自增
SQL> l
1 create or replace trigger set_no
2 before insert on auto
3 for each row
4 declare
5 sn number(5);
6 begin
7 select myseq.nextval into sn from dual;
8 :new.a:=sn;
9* end set_no;
SQL> /
触发器已创建
SQL> select * from auto;
A B
---------- ----------
21 abc
22 abc
23 abc
24 abc
SQL> insert into auto values(1,'hh');
已创建 1 行。
SQL> select * from auto;
A B
---------- ----------
21 abc
22 abc
23 abc
24 abc
87 hh
SQL> insert into auto(b) values ('hhh');
已创建 1 行。
SQL> select * from auto;
A B
---------- ----------
21 abc
22 abc
23 abc
24 abc
87 hh
90 hhh
已选择6行。
SQL> alter sequence myseq increment by 1;
序列已更改。
SQL> insert into auto(b) values ('hhhh');
已创建 1 行。
SQL> select * from auto;
A B
---------- ----------
21 abc
22 abc
23 abc
24 abc
87 hh
90 hhh
91 hhhh
已选择7行。
替换触发器 解决视图 多表更新问题，只能建在视图上
SQL> @trigger_07
触发器已创建
SQL> l
1 create or replace trigger tr_v_e_d
2 instead of insert on v_e_d
3 for each row
4 begin
5 insert into d values(:new.id,:new.name);
6 insert into e(eid,ename,sex,did) values(:new.eid,:new.ename,:new.sex,:new.id);
7* end tr_v_e_d;
SQL> /
触发器已创建
SQL> select * from d;
ID NAME
---------- ----------
55 hh
20 hh
10 hh
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择10行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择6行。
SQL> select * from v_e_d;
EID ENAME SEX ID NAME
---------- ---------- ---- ---------- ----------
1 赵1 男 1 A部门_t
2 钱1 女 2 B部门_t
3 孙1 女 3 C部门_t
3 李1 男 4 D部门_t
3 周1 女 5 E部门_t
SQL> insert into v_e_d values(15,'aa','b',25,'gg');
已创建 1 行。
SQL> select * from v_e_d;
EID ENAME SEX ID NAME
---------- ---------- ---- ---------- ----------
15 aa b 25 gg
10 aaa 男 25 gg
1 赵1 男 1 A部门_t
2 钱1 女 2 B部门_t
3 孙1 女 3 C部门_t
3 李1 男 4 D部门_t
3 周1 女 5 E部门_t
已选择7行。
SQL> select * from e;
EID ENAME SEX DID
---------- ---------- ---- ----------
10 aaa 男 25
15 aa b 25
1 赵1 男 1
2 钱1 女 2
3 孙1 女 3
3 李1 男 4
3 周1 女 5
4 周2 女
已选择8行。
SQL> select * from d;
ID NAME
---------- ----------
55 hh
20 hh
10 hh
25 gg
1 A部门_t
2 B部门_t
3 C部门_t
4 D部门_t
5 E部门_t
6 F部门_t
7 G部门_t
已选择11行。
8.安全管理
使用oem管理工具(web管理)
创建用户
test/test1234
conn sys/ora as sysdba
grant select on scott.dept to test;
SQL> conn test/test1234
已连接。
SQL> conn sys/ora as sysdba
已连接。
SQL> grant select on scott.dept to test;
授权成功。
SQL> conn test/test1234
已连接。
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
锁定用户和解锁用户
alter user test account lock;
alter user test account unlock;
把权限下放给用户
SQL> grant select on scott.dept to test with grant option;
授权成功。
SQL> conn test/test1234
已连接。
SQL> grant select on scott.dept to decoration;
授权成功。
SQL> conn decoration/decoration;
已连接。
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
这也叫 级联授权 。
grant create user to test;
grant drop user to test;
grant create user to test with admin option;
创建概要文件mypro
设置登录3次不正确，锁定用户
设置test用户的概要文件为mypro
SQL> conn test/12
ERROR:
ORA-01017: invalid username/password; logon denied
警告: 您不再连接到 ORACLE。
SQL> conn test/124
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn test/1244
ERROR:
ORA-01017: invalid username/password; logon denied
SQL>
SQL> conn test/124444
ERROR:
ORA-28000: the account is locked
SQL> conn test/test1234
ERROR:
ORA-28000: the account is locked
解锁用户test
SQL> conn test/test1234
已连接。
一、ORACLE中的实体完整性
ORACLE在CREATE TABLE语句中提供了PRIMARY KEY子句，供用户在建表时指定关系的主码列。例如在学生选课数据库
中，要定义Student 表的Sno属性为主码，可使用下列语句：
CREATE TABLE Student
(Sno NUMBER(8),
Sname VARCHAR(20),
Sage NUMBER(20),
CONSTRAINT PK_SNO PRIMARY KEY (Sno));
其中，PRIMARY KEY(Sno)是Student表的主码。PK_SNO是主码的约束名。
若要在SC表中定义(Sno, Cno)为主码，则用下面语句建立SC表。
CREATE TABLE SC
(Sno NUMBER(8),
Cno NUMBER(2),
Grade NUMBER(2),
CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno));
当用户程序对主码列进行更新操作时，系统自动进行完整性检查，凡操作使主属性值为空值的操作或使主码值在表中
不唯一，系统拒绝此操作，从而保证了实体完整性。
二、ORACLE中的参照完整性
ORACLE在CREATE TABLE语句用FOREIGN KEY子句定义哪些为外码列，用REFERENCES子句指明外码相应于哪个表的主码，
用ON DELETE CASCADE子语指明在删除被参照关系的元组时，同时删除参照关系中外码值等于被参照关系的元组中主
码值的元组。
例1：建立表EMP表
CREATE TABLE EMP
(Empno NUMBER(4),
Ename VARCHAR(10),
Job VERCHAR2(9),
Mgr NUMBER(4),
Sal NUMBER(7,2),
Deptno NUMBER(2),
CONSTRAINT FK_DEPTNO
FOREIGN KEY (Deptno)
REFERENCES DEPT(Deptno));
这时EMP表中外码为Deptno，它相应于DEPT表中的主码Deptno。当要修改DEPT表中的DEPTNO值时，先要检查EMP表中
有无元组的Deptno值与之对应。若没有，系统接受这个修改操作。否则，系统拒绝此操作 。
例2：或用SQL语句建立EMP表：
CREATE TABLE EMP
(Empno NUMBER(4),
Ename VARCHAR(10),
Job VERCHAR2(9),
Mgr NUMBER(4),
Sal NUMBER(7,2),
Deptno NUMBER(2) CONSTRAINT FK_DEPTNO
FOREIGN KEY REFERENCES DEPT(Deptno));
当要删除DEPT表中某个元组时，系统要检查EMP表，若找到相应元组即将其随之删除。当要插入EMP表中某个元组时
系统要检查DEPT表 ，先要检查DEPT表中有无元组的Deptno值与之对应。若没有，系统拒绝此插入操作，否则，系
统接受此操作。
三、ORACLE中用户定义的完整性
ORACLE中定义用户完整性的两类方法
1. 用CREATE TABLE语句在建表时定义用户完整性约束，可定义三类完整性约束
列值非空（NOT NULL短语）
列值唯一（UNIQUE短语）
检查列值是否满足一个布尔表达式（CHECK短语）
例1：建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
CREATE TABLE DEPT
(Deptno NUMBER,
Dname VARCHAR(9) CONSTRAINT U1 UNIQUE,
Loc VARCHAR(10),
CONSTRAINT PK_DEPT PRIMARY KEY (Deptno));
其中 CONSTRAINT U1 UNIQUE 表示约束名为U1，该约束要求Dname列值唯一。
例2：建立学生登记表Student，要求学号在900000至999999之间，年龄<29，性别只能是‘男’或‘女’，姓名非空
CREATE TABLE Student
(Sno NUMBER(5)
CONSTRAINT C1 CHECK
(Sno BETWEEN 10000 AND 99999),
Sname VARCHAR(20) CONSTRAINT C2 NOT NULL,
Sage NUMBER(3) CONSTRAINT C3 CHECK (Sage < 29),
Ssex VARCHAR(2)
CONSTRAINT C4 CHECK (Ssex IN ('男', '女'));
例3 ：建立职工表EMP，要求每个职工的应发工资不得超过3000元。 应发工资实际上就是实发工资列Sal与扣除项
Deduct之和。
CREATE TABLE EMP
(Eno NUMBER(4)
Ename VARCHAR(10),
Job VARCHAR(8),
Sal NUMBER(7,2),
Deduct NUMBER(7,2)
Deptno NUMBER(2),
CONSTRAINTS C1 CHECK (Sal + Deduct <=3000));
2. 通过触发器来定义用户的完整性规则
定义其它的完整性约束时，需要用数据库触发器（Trigger）来实现。
数据库触发器：一类靠事务驱动的特殊过程
一旦由某个用户定义，任何用户对该数据的增、删、改操作均由服务器自动激活相应的触发子，在核心层进行集中
的完整性控制
定义数据库触发器的语句：CREATE [OR REPLACE] TRIGGER
例4： 为教师表Teacher定义完整性规则“教授的工资不得低于800元，如果低于800元，自动改为800元”
CREATE TRIGGER UPDATE_SAL
BEFORE INSERT OR UPDATE OF Sal, Pos ON Teacher
FOR EACH ROW
WHEN (:new.Pos='教授')
BEGIN
IF :new.sal<800
THEN :new.Sal:=800;
END IF;
END;
综上所述，ORACLE提供了CREATE TABLE语句和CREATE TRIGGER语句，其中CREATE TRIGGER语句可以定义很复杂的完
整性约束条件。完整性约束条件一旦定义好，ORACLE自动执行相应的完整性检查，对于违反完整性约束条件的操作
或者拒绝执行或者执行事先定义的操作。
