Oracle+11g+笔记(5)-Oracle数据库管理操作
Oracle+11g+笔记(5)-Oracle数据库管理操作
5、Oracle数据库管理操作
5.1 学会使用视图
5.1.1 增加安全性
# step1
conn sys/sysroot as sysdba;# step2
alter user hr identified by hr account unlock;
conn hr/hr;# step3
grant connect to salesmanager identified by sales;# step4
create view emp_sale as select employee_id,first_name,last_name from hr.employees;# step5
grant select on emp_sale to salesmanager;# step6
conn salesmanager/sales;
select * from sys.emp_sale;
select salary from hr.employees;
5.1.2 隐藏数据的复杂性
--创建 PilotSkills表
CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY(pilot,plane));--创建 Hangar 表
CREATE TABLE Hangar
(plane CHAR(15) PRIMARY KEY);
CREATE VIEW QualifiedPilots(pilot)
AS
SELECTDISTINCT pilot
FROMPilotSkills PS1
WHERENOT EXISTS
(SELECT*FROMHangarWHERENOT EXISTS
(SELECT*FROMPilotSkills PS2WHERE(PS1.pilot = PS2.pilot)AND(PS2.plane = Hangar.plane)));
CREATE VIEW QualifiedPilots1(pilot)
AS
SELECTPS1.pilot
FROMPilotSkills PS1,Hangar H1
WHEREPS1.plane = H1.plane
GROUP BYPS1.pilot
HAVINGCOUNT(PS1.plane)=(SELECTCOUNT(plane)FROMHangar);
select pilot from QualifiedPilots;
5.1.3 实现命名简洁和易读性
create view 雇员基本信息(雇员编号,雇员姓名,电子邮件,电话,雇用日期)
as
select employee_id,first_name+last_name,email,phone_number,hire_date from employees;
Select 雇员编号,雇员姓名,电子邮件 From 雇员基本信息;
5.1.4 实现更改灵活性
由于视图相对于基表是独立的，当对基表进行操作的时候只会影响到视图中存在的列，而视图中不存在的列将不受
到影响。而当对视图进行操作的时候，操作的数据会反映到基表当中，但是只会影响在视图中出现过的基表属性。
从这一角度讲，就实现了安全性的控制，从另一角度讲，也实现了更改的灵活性。尤其是基表经常变化时，用视图
可以封闭这种变化。
5.2 实现记录的唯一性
5.2.1 用键实现
使用primary key约束。
# 新增主键约束
alter table stu add constraint cl primary key(sno);
# 删除主键约束
alter table stu drop constraint cl;
5.2.2 创建唯一索引
CREATE UNIQUE INDEX name ON table(column[,...]);
create unique index ind1 on stu(sno);
5.2.3 使用序列实现
序列是一个可以为表中的行自动生成序列号的数据库对象，利用它可生成唯一的整数，产生一组等间隔的数值(类
型为数字)，主要用于生成唯一、连续的序号。一个序列的值是由特殊的Oracle程序自动生成，因此序列避免了在
应用层实现序列而引起的性能瓶颈。
序列的主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个
值，因此可以使用序列实现记录的唯一性。
创建序列需要CREATE SEQUENCE系统权限，其语法格式如下：
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
其中，各选项的意义为：
-
INCREMENT BY用于定义序列的步长，若省略，则默认为1；若出现负值，则代表序列的值是按照此步长递减的。
-
START WITH：用于定义序列的初始值，默认为1。 -
MAXVALUE：用于定义序列生成器能产生的最大值。NOMAXVALUE为默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是
10^27；对于递减序列，最大值是-1。 -
MINVALUE：用于定义序列生成器能产生的最小值。NOMINVALUE为默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是
-10^26；对于递增序列，最小值是1。 -
CYCLE和NOCYCLE：表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。若循环，则当递增序列达到最大值时，循环到最小值；当递减序列达到最小值时，循环到最大值。如果不
循环，达到限制值后，继续产生新值就会发生错误。
-
CACHE：用于定义存放序列的内存块的大小，默认值为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
删除序列的语法格式为：
DROP SEQUENCE 序列名;
提示：删除序列的人应该是序列的创建者或拥有DROP ANY SEQUENCE系统权限的用户。序列一旦删除就不能被引
用了。
注意：序列的某些部分可以在使用中进行修改，但不能修改START WITH选项。对序列的修改只影响随后产生的序
号，已经产生的序号不变。
# 1.创建序列
CREATE SEQUENCE ABC
INCREMENT BY 1
START WITH 10
MAXVALUE 9999999
NOCYCLE NOCACHE;
# 2.删除序列
DROP SEQUENCE ABC;
使用序列
如果已经创建了序列，怎样才能引用序列呢？方法是使用CURRVAL和NEXTVAL来引用序列的值。调用
NEXTVAL将生成序列中的下一个序列号，调用时要指出序列名，其格式如下：
序列名.NEXTVAL
CURRVAL用于产生序列的当前值，无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用
NEXTVAL产生过序列的下一个值，先引用CURRVAL没有意义。调用CURRVAL的方法同上，要指出序列名，即用以
下方式调用产生序列的值：
序列名.CURRVAL
# Step 1 产生序列的第一个值:
SELECT ABC.NEXTVAL FROM DUAL;
# Step2产生序列的下一个值:
SELECT ABC.NEXTVAL FROM DUAL;
# Step3产生序列的当前值:
SELECT ABC.CURRVAL FROM DUAL;
10
11
11
查看序列
通过数据字典USER_OBJECTS 可以查看用户拥有的序列。通过数据字典USER_SEQUENCES 可以查看序列的设置。
SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM USER_SEQUENCES;
5.3 实现数据的完整性
5.3.1 域完整性
1、NOT NULL(非空)约束
创建NOT NULL 约束有两种方法：
(1)、在创建表时定义约束
create table student
(sno char(7),
sname char(10) not null,
ssex char(2),
sage int,
sdept char(20));
(2)、在修改表时添加约束
alter table student modify sname not null;
alter table student modify sname null;
2、UNIQUE(唯一)约束
创建UNIQUE约束也有两种方法：
(1)、在创建表时定义约束
create table student
(sno char(7),
sname char(10) unique,
ssex char(2),
sage int,
sdept char(20));
(2)、在修改表时添加约束
alter table student add constraint stu_uk unique(sname);
alter table student drop constraint stu_uk;
3、CHECK(检查)约束
创建CHECK 约束也有两种方法：
(1)、在创建表时定义约束
create table student
(sno char(7),
sname char(10),
ssex char(2),
sage int check(sage>0 and sage<=100),
sdept char(20));
(2)、在修改表时添加约束
alter table student add constraint stu_ck_check(sage>0 and sage<=100);
alter table student drop constraint stu_ck_check;
5.3.2 实体完整性
实体完整性约束是通过定义PRIMARY KEY约束来实现的。
提示：定义为PRIMARY KEY约束的列被称为主键列。
创建PRIMARY KEY约束也有两种方法：
(1)、在创建表时定义约束
create table student
(sno char(7) primary key,
sname char(10),
ssex char(2),
sage int,
sdept char(20));
(2)、在修改表时添加约束
alter table student add constraint stu_pk primary key(sno);
alter table student drop constraint stu_pk;
5.3.3 引用完整性
引用完整性是通过外键FOREIGN KEY约束来实现的。定义为FOREIGN KEY约束的列称为外键列，被FOREIGN
KEY 约束引用的列称为引用列”。包含外键的表称为子表，也称为引用表，包含引用列的表称为父表，也称为被引
用表，通过使用公共列在表之间建立一种父子关系。在表上定义的外键可以指向主键或者其他表的唯一键。
create table sc
(sno char(7),
cno char(10),
grade int,
foreign key(sno) references student(sno));
注意：在一个表上创建外键之前，被引用表必须已经存在，并且必须为该表的引用列定义UNIQUE约束或者
PRIMARY KEY约束。
在定义外键PRIMARY KEY约束时，还可以通过关键字on指定引用行为的类型。当尝试删除被引用表中的一条记录
时，通过引用行为可以确定如何处理外键表中的外键列。引用类型包含如下几种：
-
如果在定义外键
PRIMARY KEY约束时使用了CASCADE关键字，那么当被引用表中的被引用列的数据被删除时，引用表中对应的外键数据也将被删除。
-
如果在定义外键
PRIMARY KEY约束时使用了SET NULL关键字，那么当被引用表中的被引用列的数据被删除时，引用表中对应的外键数据将被设置为NULL。要使这个关键字起作用,外键列必须支持 NULL值。
-
如果在定义外键
PRIMARY KEY约束时使用了NO ACTION关键字，那么删除被引用表中的被引用列的数据将违反外键约束，该操作也会被禁止执行，这也是外键的默认引用类型。
【以下的示例将演示外键的级联删除】
# step1首先创建一个新的被引用表dep,并为其添加主键约束
create table dep as select * from hr.departments where department_id=50;alter table dep add primary key(department_id);select * from dep;
DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
-------------+---------------+----------+-----------+50|Shipping | 121| 1500|
# step2创建一个新的引用表null_emp
create table null_emp as select * from hr.EMPLOYEES where employee_id is null;SELECT * FROM null_emp;
EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL|PHONE_NUMBER|HIRE_DATE|JOB_ID|SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|
-----------+----------+---------+-----+------------+---------+------+------+--------------+----------+-------------+
# step3使用ON DELETE CASCADE关键字添加null_emp表的外键约束
alter table null_emp add constraint nullemp_fk foreign key(department_id) references dep on delete cascade;
# step4向null_emp表添加数据
insert into null_emp select * from employees where department_id=50;SELECT * FROM null_emp;
EMPLOYEE_ID|FIRST_NAME|LAST_NAME |EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID |SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|
-----------+----------+-----------+--------+------------+-----------------------+--------+------+--------------+----------+-------------+198|Donald |OConnell |DOCONNEL|650.507.9833|2007-06-21 00:00:00.000|SH_CLERK| 2600| | 124| 50|199|Douglas |Grant |DGRANT |650.507.9844|2008-01-13 00:00:00.000|SH_CLERK| 2600| | 124| 50|120|Matthew |Weiss |MWEISS |650.123.1234|2004-07-18 00:00:00.000|ST_MAN | 8000| | 100| 50|121|Adam |Fripp |AFRIPP |650.123.2234|2005-04-10 00:00:00.000|ST_MAN | 8200| | 100| 50|122|Payam |Kaufling |PKAUFLIN|650.123.3234|2003-05-01 00:00:00.000|ST_MAN | 7900| | 100| 50|123|Shanta |Vollman |SVOLLMAN|650.123.4234|2005-10-10 00:00:00.000|ST_MAN | 6500| | 100| 50|124|Kevin |Mourgos |KMOURGOS|650.123.5234|2007-11-16 00:00:00.000|ST_MAN | 5800| | 100| 50|125|Julia |Nayer |JNAYER |650.124.1214|2005-07-16 00:00:00.000|ST_CLERK| 3200| | 120| 50|126|Irene |Mikkilineni|IMIKKILI|650.124.1224|2006-09-28 00:00:00.000|ST_CLERK| 2700| | 120| 50|127|James |Landry |JLANDRY |650.124.1334|2007-01-14 00:00:00.000|ST_CLERK| 2400| | 120| 50|128|Steven |Markle |SMARKLE |650.124.1434|2008-03-08 00:00:00.000|ST_CLERK| 2200| | 120| 50|129|Laura |Bissot |LBISSOT |650.124.5234|2005-08-20 00:00:00.000|ST_CLERK| 3300| | 121| 50|130|Mozhe |Atkinson |MATKINSO|650.124.6234|2005-10-30 00:00:00.000|ST_CLERK| 2800| | 121| 50|131|James |Marlow |JAMRLOW |650.124.7234|2005-02-16 00:00:00.000|ST_CLERK| 2500| | 121| 50|132|TJ |Olson |TJOLSON |650.124.8234|2007-04-10 00:00:00.000|ST_CLERK| 2100| | 121| 50|133|Jason |Mallin |JMALLIN |650.127.1934|2004-06-14 00:00:00.000|ST_CLERK| 3300| | 122| 50|134|Michael |Rogers |MROGERS |650.127.1834|2006-08-26 00:00:00.000|ST_CLERK| 2900| | 122| 50|135|Ki |Gee |KGEE |650.127.1734|2007-12-12 00:00:00.000|ST_CLERK| 2400| | 122| 50|136|Hazel |Philtanker |HPHILTAN|650.127.1634|2008-02-06 00:00:00.000|ST_CLERK| 2200| | 122| 50|137|Renske |Ladwig |RLADWIG |650.121.1234|2003-07-14 00:00:00.000|ST_CLERK| 3600| | 123| 50|138|Stephen |Stiles |SSTILES |650.121.2034|2005-10-26 00:00:00.000|ST_CLERK| 3200| | 123| 50|139|John |Seo |JSEO |650.121.2019|2006-02-12 00:00:00.000|ST_CLERK| 2700| | 123| 50|140|Joshua |Patel |JPATEL |650.121.1834|2006-04-06 00:00:00.000|ST_CLERK| 2500| | 123| 50|141|Trenna |Rajs |TRAJS |650.121.8009|2003-10-17 00:00:00.000|ST_CLERK| 3500| | 124| 50|142|Curtis |Davies |CDAVIES |650.121.2994|2005-01-29 00:00:00.000|ST_CLERK| 3100| | 124| 50|143|Randall |Matos |RMATOS |650.121.2874|2006-03-15 00:00:00.000|ST_CLERK| 2600| | 124| 50|144|Peter |Vargas |PVARGAS |650.121.2004|2006-07-09 00:00:00.000|ST_CLERK| 2500| | 124| 50|180|Winston |Taylor |WTAYLOR |650.507.9876|2006-01-24 00:00:00.000|SH_CLERK| 3200| | 120| 50|181|Jean |Fleaur |JFLEAUR |650.507.9877|2006-02-23 00:00:00.000|SH_CLERK| 3100| | 120| 50|182|Martha |Sullivan |MSULLIVA|650.507.9878|2007-06-21 00:00:00.000|SH_CLERK| 2500| | 120| 50|183|Girard |Geoni |GGEONI |650.507.9879|2008-02-03 00:00:00.000|SH_CLERK| 2800| | 120| 50|184|Nandita |Sarchand |NSARCHAN|650.509.1876|2004-01-27 00:00:00.000|SH_CLERK| 4200| | 121| 50|185|Alexis |Bull |ABULL |650.509.2876|2005-02-20 00:00:00.000|SH_CLERK| 4100| | 121| 50|186|Julia |Dellinger |JDELLING|650.509.3876|2006-06-24 00:00:00.000|SH_CLERK| 3400| | 121| 50|187|Anthony |Cabrio |ACABRIO |650.509.4876|2007-02-07 00:00:00.000|SH_CLERK| 3000| | 121| 50|188|Kelly |Chung |KCHUNG |650.505.1876|2005-06-14 00:00:00.000|SH_CLERK| 3800| | 122| 50|189|Jennifer |Dilly |JDILLY |650.505.2876|2005-08-13 00:00:00.000|SH_CLERK| 3600| | 122| 50|190|Timothy |Gates |TGATES |650.505.3876|2006-07-11 00:00:00.000|SH_CLERK| 2900| | 122| 50|191|Randall |Perkins |RPERKINS|650.505.4876|2007-12-19 00:00:00.000|SH_CLERK| 2500| | 122| 50|192|Sarah |Bell |SBELL |650.501.1876|2004-02-04 00:00:00.000|SH_CLERK| 4000| | 123| 50|193|Britney |Everett |BEVERETT|650.501.2876|2005-03-03 00:00:00.000|SH_CLERK| 3900| | 123| 50|194|Samuel |McCain |SMCCAIN |650.501.3876|2006-07-01 00:00:00.000|SH_CLERK| 3200| | 123| 50|195|Vance |Jones |VJONES |650.501.4876|2007-03-17 00:00:00.000|SH_CLERK| 2800| | 123| 50|196|Alana |Walsh |AWALSH |650.507.9811|2006-04-24 00:00:00.000|SH_CLERK| 3100| | 124| 50|197|Kevin |Feeney |KFEENEY |650.507.9822|2006-05-23 00:00:00.000|SH_CLERK| 3000| | 124| 50|
# step5在指定行为类型为ON DELETE CASCADE后,再删除被引用表dep中编号为50的行,会导致null emp表中所有的# 记录同时也被删除
delete dep where department_id=50; select count(*) from null_emp;
COUNT(*)|
--------+0|
删除外键约束：
alter table null_emp drop constraint nullemp_fk;
5.3.4 存储过程检查
存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只
要调用即可。存储过程具有很强的灵活性，可以完成复杂的判断和较复杂的运算，因此可以通过建立存储过程来实
现数据的完整性。
提示：在使用函数DBMS output时，一定要先执行set serveroutput on，以保证函数可以有正确的输出。
在存储过程中可以进行异常的控制。
我们可以看到，通过建立存储过程可以实现当输入的数据违反数据完整性的时候，给出相应的提示，以保证数据的
正确性和相容性。
5.3.5 使用触发器
上节我们介绍了怎样通过建立存储过程来检查数据的完整性，但是，编写起来比较麻烦，而且需要调用存储过程才
可以实施完整性检查。如果在应用程序中有多个地方要对表中的数据进行操作，那么在每次对表中的数据进行操作
后都要调用存储过程来判断，代码的重复量很大。而触发器则不同，它可以在特定的事件触发下自动执行，如当有
新的数据插入时或数据被修改时。
触发器是一种特殊的存储过程，该过程在插入、修改和删除等操作事前或事后由DBS自动执行。经常用于实现逻辑
上相关的数据表之间的数据完整性和一致性，例如可用于强制引用完整性，以便在多个表中添加、更新或删除行
时，保留在这些表之间所定义的关系。触发器非常适合于实施企业规则，当某个输入违反了其中的某个企业规则
时，触发器便可以显示相应错误并中止正在执行的数据库动作。
5.4 避免更改引起的大量改动
由于所有的操作最终都作用在基表上，因此基表名和列名的变化会对这些语句产生影响，此时必须修改所有的语
句，这样不但麻烦，有时甚至会发生错误。有的表名和列名复杂而晦涩，用起来极不方便。怎样才能解决这个问题
呢？为了避免直接依赖于基表的问题，可采用以下几种方法：
-
使用视图为表名和列名起别名，在应用过程中可以借助视图中的名字来代替基表名和列名，当表名和列名改变
时，只需改变相应视图的定义即可。
-
类似于视图定义，只不过提供了一种更直接更广泛的方法来为各种对象定义别名，其中也包括视图对象。
-
在程序中用定义游标的方法防止直接依赖于表。当表名改变时，只需改变游标定义即可。
5.4.1 使用视图
-- Student
Student(Sno,Sname,Ssex,Sage,Sdept)
-- 表结构发生改变
SX(Sno,Sname,Sage)
SY(Sno,Ssex,Sdept)
-- 以前的视图
create view v_student(学号,姓名,性别,年龄) as SELECT Sno,Sname,Ssex,Sage FROM Student
-- 表结构修改之后的视图
create view v_student(学号,姓名,性别,年龄) as SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept FROM SX,SY WHERE SX.Sno=SY.Sno;
5.4.2 使用同义词
与使用视图来实现操作不直接依赖于基表一样，同义词也能实现这一目的。与视图不同的是同义词不但可以应用在
表的命名中，同样也可以应用在视图、序列、存储过程和函数以及包中，因此它的应用范围更广泛。用同义词的不
便之处是它不能对列起别名，这一点不如视图。
创建同义词的语法格式如下：
create [public] synonym 同义词 for 对象;
-
public：公共同义词，所有用户都可以引用，若省略此关键字不写，则默认是private 同义词，即私有同义词，它只能为某一用户使用。
-
同义词：为对象起的别名，在以后使用对象时可以用此名来代替原对象名。
-
对象：某一特定对象名，它可以是基表、视图、序列、过程、存储函数、存储包和其他同义词，指定对象时可以指定所属用户，中间用
.分开。
下面将对同义词的相关操作进行介绍。
1、创建私有同义词
# 为 student 表创建同义词 stu
CREATE SYNONYM stu FOR student;
2、创建公共同义词
create public synonym stu FOR student;
3、使用同义词
创建了同义词之后，就可以在其他地方引用它，以此来代替基表的引用。
insert into stu values('100','黎明','男',25,'phy');
SELECT * FROM STUDENT;
SNO |SNAME |SSEX|SAGE|SDEPT |
-------+--------+----+----+--------------------+
100 |黎明 |男 | 25|phy |
4、删除同义词
drop [public] SYNONYM
drop SYNONYM stu;
5.4.3 使用游标
在存储过程和函数中可以使用显式游标，游标相当于定义了一个查询，在以后应用中可能用这个游标的查询结果。
当表名改变时，在存储过程和函数中只需改变定义在这个表上的游标即可，后面对游标的引用不用变，从而避免了
直接依赖于表的操作。
当将表名修改时，只需将游标定义处的表名修改一下即可，而程序将完成相同的功能。
这个程序比较短，即使不用游标也没多大关系。当应用程序很大时,就有必要考虑游标的使用，并且最好将所有存
储过程和函数放到一个包中。这样，只需对游标定义一次，便可以在所有存储过程和函数中使用。
当然，使用游标也有自己的缺点，它并不能直观地解决直接信赖于表的问题，游标在网络数据库中对减少网络传输
量的用途更大一些。