新疆建设工程信息网官网入口_宝安中心医院体检_谷歌广告上海有限公司官网_百度网站收录提交

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 使用游标

在存储过程和函数中可以使用显式游标，游标相当于定义了一个查询，在以后应用中可能用这个游标的查询结果。

当表名改变时，在存储过程和函数中只需改变定义在这个表上的游标即可，后面对游标的引用不用变，从而避免了

直接依赖于表的操作。

当将表名修改时，只需将游标定义处的表名修改一下即可，而程序将完成相同的功能。

这个程序比较短，即使不用游标也没多大关系。当应用程序很大时,就有必要考虑游标的使用，并且最好将所有存

储过程和函数放到一个包中。这样，只需对游标定义一次，便可以在所有存储过程和函数中使用。

当然，使用游标也有自己的缺点，它并不能直观地解决直接信赖于表的问题，游标在网络数据库中对减少网络传输

量的用途更大一些。