oracle数据库复习总结

1.简述Oracle数据库的启动命令STARTUP NOMOUNT,STARTUP MOUNT,STARTUP的作用。

a、STARTUP NOMOUNT 创建实例不加载数据库

创建实例，并不加载数据库，Oracle仅为实例创建各种内存结构和后台进程，不会打开任何数据文件。
在NoMount状态下，只能访问那些与SGA区相关的数据字典视图，视图中的信息都是从SGA区中获取的，与数据库无关

模式用途：
创建新数据库
重建控制文件

b、STARTUP MOUNT 加载数据库但不打开数据库

（需要控制文件）为实例加载数据库，但保持数据库为关闭状态
数据文件和重做日志文件都无法进行读写，用户无法对数据库进行操作
在Mount状态下，只能访问那些与控制文件相关的数据字典视图，这些视图都是从控制文件中获取的。

模式用途：
重命名数据文件；
添加、删除或重命名重做日志文件；
执行数据库完全恢复操作；
改变数据库的归档模式

c、STARTUP 正常打开数据库

正常按照3个步骤打开数据库

模式用途：平时不对数据库做什么维护，比如应用开发类任务

2.简述Oracle数据库的关闭命令SHUTDOWN IMMEDIATE, SHUTDOWN TRANSACTIONAL的作用。

a、SHUTDOWN NORMAL 正常关闭方式
阻止任何用户建立新的连接；
等待当前所有正在连接的用户主动断开连接（此方式下Oracle不会立即断掉当前用户的连接，这些用户仍然可进行相关的操作）
一旦所有的用户都断开连接，则立即关闭、卸载数据库，并终止实例。（所以，一般以正常方式关闭数据库时，应该通知所有在线的用户尽快断开连接）

b、SHUTDOWN IMMEDIATE 立即关闭方式

执行过程

阻止任何用户建立新的连接，同时阻止当前连接的用户开始新事务

Oracle不等待在线用户主动断开连接，强制终止用户的当前事务，将任何未提交的事务回退。

直接关闭、卸载数据库，并终止实例。

c、SHUTDOWN TRANSACTIONAL 事物关闭方式

这种方式介于正常关闭方式跟立即关闭方式之间

执行过程

阻止任何用户建立新的连接，同时阻止当前连接的用户开始新的事务。
等待所有未提交的活动事务提交完毕，然后立即断开用户的连接。
直接关闭、卸载数据库，并终止实例。

3.概述Oracle数据库的物理存储结构。

p22

物理存储结构是指构成数据库的操作系统文件的组织结构，物理储结构用于描述在数据库系统外部如何组织和管理数据，与具体的操作系统有关。

物理存储系统由数据文件、控制文件和重做日志文件组成。
a、数据文件
包含数据库中真正的数据。数据文件与表空间关系：一个表空间在物理上对应一个或多个数据文件，而一个数据文件只能属于一个表空间。

b、控制文件
一个很小的二进制文件，用于记录数据库的物理结构，包含维护和校验数据库一致性所需的信息。

c、重做日志文件
包含当系统崩溃后进行恢复时所需记录的变化信息。

4.简述Oracle数据库的逻辑存储结构。

p26

逻辑存储结构用于描述在数据库系统内部如何组织和管理数据，与操作系统无关。
逻辑存储结构从大到小依次为表空间、段、区和数据块。

它们之间的关系：一个表空间由多个段组成，一个段由多个区组成，一个区又多个数据块组成。

a、表空间  table space

Oracle中最大的逻辑存储结构，与物理上的数据文件相对应，一个Oracle数据库至少有一个表空间，一个表空间可以对应多个数据文件，但一个数据文件只能对应一个表空间。

表空间的大小等于构成该表空间的所有数据文件大小之和。

b、段 segment

段是由一组区间组成，用于存储具有独立存储结构对象的全部数据。

一个段只属于一个特定的数据库对象。

主要有数据段、索引段、临时段、LOB段和回退段。

c、区 Extent

Oracle存储分配的最小单位，由连续数据块组成。

d、数据块 Block

数据块是Oracle 管理数据文件中存储空间的最基本单位，也是最小的逻辑存储单位。

Oracle数据库以数据块为单位进行逻辑读写操作，一旦数据库创建之后，将无法再修改数据块的大小。

5.简要介绍模式与用户之间的关系。

p295

用户是数据库对象之一，只有使用了合法的用户登录之后，才能对数据库进行访问和操作。用户可以直接操作表、索引和视图等对象。

但在Oracle中，有些逻辑结构是数据库用户不能直接进行操作的对象，需要用过模式来组织和管理这些数据库对象。

模式是数据库对象集合，用户和模式是一一对应的关系，并且二者名称相同。

每个用户都拥有唯一的模式，用户创建的所有对象都保存在自己的模式中。

在同一个模式中不能存在同名对象，但在不同模式可以具有相同对象名。

用户可以直接访问其模式对象，但是要访问其他模式对象必须具有相应的权限。

模式是用户的附属对象，它是依赖对象的存在而存在的。

6.列举出ORACLE数据库管理系统中常见的实例进程（4个），并简要说明其用途。

P32

（1）数据库写入进程（DBWn Database Writer）

将缓冲区的数据写入数据文件，负责缓冲区存储区管理。

（2）日志写入进程（LGWR Log Writer）

它将日志文件缓冲区中的日志数据写入磁盘的日志文件，负责管理日志缓冲区的后台进程

（3）系统监控进程（SMON System Monitor）

当数据库实例出现故障或者系统崩溃时，执行恢复操作

（4）进程监控进程（PMON Process Monitor）

当用户进程出现故障时执行恢复的操作，负责清理内存存储区和释放该进程所使用的资源。

7.简述Oracle数据库的两级日志结构。

通过重做日志组和引入归档日志（两级日志结构），Oracle在数据库系统的效率和可恢复性之间进行了巧妙的折中，这是Oracle的得意之作

重做日志

P126

重做日志文件用于记载事务操作所引起的数据库变换

Oracle 通过重做日志来实现快速提交

• 重做日志可以连续、顺序的快速写出
• 重做日志记录的精简内容

重做日志文件是由重做记录组成的，重做记录由一组改变向量（Change Vector）组成

使用重做记录不仅能够恢复对数据文件所做的修改操作，还能够恢复对回滚段所做的修改操作

归档日志

Oracle数据库有两种日志模式：

• 非归档日志模式(NOARCHIVELOG)   

如果发生日志切换，则日志文件中原有内容将被新的内容覆盖

• 归档日志模式(ARCHIVELOG)

如果发生日志切换，则Oracle系统会将日志文件归档，然后才允许向文件中写入新的日志内容

 

归档日志是非活动的重做日志备份。通过使用归档日志，可以保留所有重做历史记录
当数据库处于ARCHIVELOG模式并进行日志切换时，后台进程ARCn会将已经写满的重做日志的内容保存到归档日志中，这个过程叫做归档
当数据库出现介质故障时，使用数据文件备份、归档日志和重做日志可以完全恢复数据库

 

8.简述过程和函数的区别。

P246

过程指的是在大型数据库系统中专门定义的一组SQL语句集，它可以定义用户操作参数，并且存在于数据库中，当使用时直接调用即可。
函数也是一种较为方便的存储结构，用户定义的函数可以被SQL语句或PL/SQL程序直接调用。

函数与过程的区别：

存储过程没有返回值，不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用，依靠OUT或IN OUT返回数据。
函数必须有返回值，并且可以作为一个表达式的一部分，函数不能作为一个完整的语句使用，函数返回值的数据类型在创建函数时定义。

 

9.简述oracle数据库触发器的类型。

DDL(Data Definition Language)数据定义语言
DML(Data Manipulation Language)数据操纵语言

P252

Oracle中的触发器主要有DML触发器、INSTEAD OF触发器、数据库事件触发器及DDL触发器几种类型

a、DML触发器

指DML语句触发的触发器，DML所包含的触发事件有INSERT、UPDATE和DELETE。

b、INSTEAD OF触发器

代替数据库视图上的DML操作，使用INSTEAD OF触发器不但可以通过使用视图简化代码，还允许根据需要发生各种不同的操作。

c、数据库事件触发器

定义在整个数据库或模式上，触发事件是数据库事件。支持的触发事件有LOGON、LOGOFF、SERVERERROR、STARTUP和SHUTDOWN。

d、DDL触发器

指DDL语句（CREATE、ALTER和DROP等）触发的触发器。

10.简述段的类型。

P28

根据段中所存储数据的特征，可分为5中类型：
a、数据段

用于存储表中的所有数据，一个表对应一个段

b、索引段

用于存储表中索引的所有数据。

c、临时段

用于存储排序或汇总时产生的临时数据。

d、LOB段

用于存储表中的大型数据对象

e、回退段

用于存储用户数据被修改之前的位置和值。利用回退段的信息，可以回退未提交的事务，维护数据库的读一致性，并能从实例的崩溃中进行恢复。每个数据库都应该至少拥有一个回退段，供数据恢复时使用。

11.简述显式游标的使用过程。

PPT 0970

第1步：声明游标（CURSOR 定义）
第2步：为查询打开游标（OPEN 游标名称）。使用OPEN操作，当游标打开时首先会检查绑定此游标的变量内容，之后再确定所使用的查询结果集，最后游标将指针指向结果集的第1行。如果用户定义的是一个带有参数的游标，则会在打开游标时为游标设置指定的参数值
第3步：取得结果放入PL/SQL变量中（FETCH 游标名称 INTO ROWTYPE变量），使用循环和 FETCH…INTO操作
第4步：关闭游标（CLOSE 游标名称）

12.简述游标的属性（4个）。

P226

（1）使用%ISOPEN

主要用于判断游标是否打开。

（2）使用%FOUND

用于判断游标是和否找到记录，如果找到记录，用FETCH语句提取游标数据，否则关闭游标

（3）使用%NOTFOUND

与%FOUND属性恰好相反，如果检索到数据，则返回FALSE，如没有检测到数据则返回TRUE

（4）使用%ROWCOUNT

用于返回当前已经检索到的实际行数。

13.简述调用过程时传递参数值的三种方式。

P241

（1）创建带有IN参数的过程

IN是指输入参数，由存储过程的调用者为其赋值。若不指定参数名，系统自动按照存储过程中参数的先后顺序为参数赋值；若指定参数名，实现指定参数的赋值。

（2）创建带有OUT参数的过程

OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户，使用这种模式的参数，必须在参数的后面添加OUT关键字。

（2）创建带有IN OUT参数的过程

IN OUT参数同时拥有IN 与 OUT 参数的特性，既接受用户的传值，又允许在过程中修改其值，并可以将值返回，使用这种模式的参数，需要在参数后面添加IN OUT关键字。

14.简要介绍oracle数据表的各类约束及作用。

P103

根据约束的作用域可以将约束分为：

表级别约束：定义在一个表中，可以用于表中的多个列

列级别约束：对表中的一列进行约束，只能够应用于一个列

根据约束的用途可以将约束分为：

a、NOT NULL约束（非空约束）：

指定一列不允许存储空值，限制必须为某一列提供值

b、PRIMARY KEY约束（主键约束）：

指定表的主键，主键由一列或多列组成，唯一标识表的一行

c、UNIQUE（唯一约束）：

指定一列或一组列只能存储唯一的值

d、CHECK约束（检查约束）：

指定一列或一组列的值必须满足某种条件

f、FOREIGN KEY约束（外键约束）：

指定表的外键，外键引用另一表中的一列，在自引用的情况中，则引用本表的一列

 
操作

SQL查询

 

SQL语句各子句的执行顺序
第一步：执行FROM子句，确定要检索的数据来源
第二步：执行WHERE子句，使用限定符对数据行进行过滤
第三步：执行GROUP BY子句，根据指定字段进行分组
第四步：执行HAVING子句，对分组后的统计数据进行过滤
第五步：执行SELECT子句，确定要检索出的数据列
第六步：执行ORDER BY子句排序
ORDER BY子句可以使用SELECT子句定义的别名

练习题

简单查询练习

1、找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名

SELECT ename
FROM emp
WHERE ename LIKE '--A%'；

2、找出EMP表员工名字中含有A 和N的员工姓名

SELECT ename
FROM emp
WHERE ename LIKE ‘%A%'
AND ename LIKE ‘%N%'；

3、列出部门编号为20的所有职位

SELECT DISTINCT job
FROM emp
WHERE deptno = 20;

4、列出不属于SALES 的部门

SELECT DISTINCT *
FROM dept
WHERE dname <> 'SALES';

5、找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小

SELECT ename，sal，comm
FROM emp
WHERE comm > 0
ORDER BY sal , comm DESC;

6、列出工资不在1000 到1500之间的员工信息：名字、工资，按工资从大到小排序

SELECT ename，sal
FROM emp
WHERE sal NOT BETWEEN 1000 AND 1500
ORDER BY sal DESC;

7、显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪

SELECT ename , job , (sal+comm)×12  "Annual Salary" 
FROM emp
WHERE (sal+comm)×12 BETWEEN 15000 AND 20000
AND job in ('MANAGER' ，'SALESMAN');

统计查询示例

1、显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000元，输出结果按月工资的合集升序排列

SELECT job,sum(sal) sum
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING sum(sal)>5000
ORDER BY sum ASC;

2、（多表查询）
查询部门的详细信息：编号、名称、位置、部门人数、平均工资、总工资、最高工资、最低工资

SELECT d.deptno,d.dname,d.loc,count(e.empno) count,  nvl(avg(sal),0) avg,
nvl(sum(sal),0) sum,  nvl(max(sal),0) max,  nvl(min(sal),0) min
FROM dept d,emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno,d.dname,d.loc;

嵌入视图，多表查询

SELECT d.deptno,d.dname,d.loc,  nvl(count,0) count, nvl(avg,0) avg, 
        nvl(sum,0) sum, nvl(max,0) max, nvl(min,0) min
FROM dept d, 
    (SELECT deptno,count(empno) count, avg(sal) avg,
        sum(sal) sum,  max(sal) max,  min(sal) min
      FROM  emp
      GROUP BY deptno) e
WHERE d.deptno = e.deptno(+);

子查询

1、查出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员姓名、工作、基本工资

SELECT ename, job,sal
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='ALLEN')
    AND sal > ( SELECT sal FROM emp WHERE empno=7521);

2、查询所有在部门SALES工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资

SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,es.max,es.min
FROM emp e, (
    SELECT deptno, max(sal) max, min(sal) min
    FROM emp
    GROUP BY deptno ) es
WHERE e.deptno = (
    SELECT deptno 
    FROM dept 
    WHERE dname='SALES')
  AND e.deptno=es.deptno;

3、查询薪金比ALLEN或CLARK多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名、部门人数

SELECT e.empno,e.ename,e.sal, d.dname, m.ename, c.count
FROM emp e, emp m, dept d, (
    SELECT deptno,count(empno) count
    FROM emp
    GROUP BY deptno) c
WHERE e.sal>ANY(
    SELECT sal
    FROM emp
    WHERE ename IN('ALLEN','CLARK'))
  AND e.ename NOT IN('ALLEN','CLARK')
  AND e.mgr=m.empno(+)
  AND e.deptno=d.deptno
  AND e.deptno=c.deptno;

4、查询所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期、所在部门名称、位置、上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限

SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,
    d.dname,em.ename bossname,s.grade,es.count,es.avg,es.avgyear
FROM emp e,emp em,dept d,salgrade s,(
    SELECT deptno,count(empno) count,round(avg(sal),2) avg,
        round(avg(months_between(sysdate,hiredate)/12),2) avgyear
    FROM emp
    GROUP BY deptno) es
WHERE e.sal > (
    SELECT avg(sal) FROM emp)
  AND e.mgr=em.empno(+)
  AND e.deptno=d.deptno
  AND e.sal BETWEEN s.losal AND s.hisal
  AND e.deptno=es.deptno;

复杂查询练习

1、列出至少有一个员工的所有部门

方法一

SELECT dname 
FROM emp e , dept d
WHERE e.deptno =d.deptno
GROUP BY dname

 方法二

SELECT dname 
FROM dept d
WHERE 0 <(
SELECT cout(*) 
FROM emp
WHERE deptno = d.deptno 
);

2、列出薪金比“SMITH”多的所有员工

方法一

SELECT e.empno , e.ename , e.job , e.mgr , e.hiredate , e.sal , e.comm , e.deptno
FROM emp e ,emp e1
WHERE e.sal > e1.sal AND e1.ename = 'SMITH' ；

方法二

SELECT empno , ename , job , mgr , hiredate , sal , comm , deptno
FROM emp 
WHERE sal > {
SELECT sal 
FROM emp
WHERE ename = 'SMITH'
};

3、列出所有员工的姓名及其直接上级的姓名
方法一

SELECT e.ename  name, e1.ename "BOSS NAME"
FROM emp e , emp e1
WHERE e.mgr = e1.empno;

方法二

SELECT e.ename  name, 
(SELECT ename FROM emp
WHERE empno = e.mgr)  "BOSS NAME"
FROM emp e;

4、列出受雇日期早于其直接上级的所有员工

SELECT e.ename  name, 
FROM emp e
WHERE e.hiredate <(
SELECT hiredate
FROM emp
WHERE empno = e.mgr);

5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT dname, e.empno ,e.name, e.job ,e.mgr , e.hiredate,e.sal
FROM dept d LEFT OUTER JOIN emp e USING(deptno);
//左连接

 

6、列出所有 “CLERK”  (办事员) 的姓名及其部门名称

SELECT ename , dname
FROM emp e NATURAL JOIN dept d
WHERE job = 'CLERK' ;
//自然连接

7、列出最低薪金大于1500的各种工作

方法一

SELECT DISTINCT job
FROM emp
GROUP BY job
HAVING 1500 < min(sal) ;

方法二

SELECT DISTINCT e.job
FROM emp e
WHERE 1500 < (
SELECT min(sal)
FROM emp
WHERE job = e.job
);

8、列出在部门 “SALES” (销售部) 工作的员工的姓名，假定不知道销售部的部门编号

SELECT ename
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND d.name = ‘SALES’;

9、列出薪金高于公司平均薪金的所有员工

SELECT ename , sal
FROM emp 
WHERE sal >(
SELECT avg(sal)
FROM emp );

10、列出与  “SCOTT” 从事相同工作的所有员工

SELECT ename , job
FROM emp 
WHERE  job=(
SELECT job
FROM emp
WHERE ename = 'SCOTT' )
AND ename <>'SCOTT';

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

SELECT ename , sal
FROM emp
WHERE deptno<>30
AND sal  IN (
SELECT sal
FROM emp
WHERE deptno = 30
);

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金

SELECT ename , sal
FROM emp
WHERE sal >(
SELECT max(sal)
FROM emp
WHERE deptno = 30);

 

14、列出所有员工的姓名、部门名称和工资

SELECT e.ename , d.dname, e.sal + nvl(e.comm,0) wage
FROM emp e , dept d
WHERE e.deptno = d.deptno;

15、列出各个部门的MANAGER的最低薪金

SELECT deptno , min(sal) lowestsal
FROM emp
WHERE job = 'MANAGER'
GROUP BY deptno ;

16、列出各种工作的最低工资

SELECT job , min(sal) lowestsal
FROM emp
GROUP BY job ;

17、列出所有员工的年工资，按年薪从低到高排序

SELECT empno , ename ,（sal + nvl(comm,0)×12 yearincome）
FROM emp
ORDER BY yearincome ;

CASE表达式

P218

显示每个雇员的姓名、工资、职位同时显示新的工资：新工资的标准为业务员增长10%销售人员增长20%经理增长30%其他职位的人增长50%

SELECT ename,sal,
    CASE job WHEN 'CLERK‘       
              THEN sal*1.1
         WHEN 'SALESMAN'        
              THEN sal*1.2
         WHEN 'MANAGER‘        
              THEN sal*1.3
         ELSE         
              sal*1.5
    END new_sal

用户概要文件创建

应用实例

创建概要文件  tiger_prof

CREATE PROFILE tiger_prof LIMIT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 91
PASSWROD_GRACE_TIME 7
PASSWORD_REUSE_TIME 28
PASSWORD_REUSE_MAX 3
SESSIONS_PER_USER 3
CPU_PER_SESSION 16800
LOGICAL_READS_PER_SESSION 23688
CONNECT_TIME 180
IDLE_TIME 28;

为防止通过猜测攻破口令防线，用户登录系统时最多尝试 4 次

4 次之后该用户的账户将被永远锁住，只有数据库管理员才能打开
为防止口令使用时间过长而泄密，设定口令有效期为 91 FAILED_LOGIN_ATTEMPTS 4天，之后必须修改口令

FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 91

当一个口令失效后系统给用户 7 天的宽免期，如果在这段时间内用户没有改变口令，将无法再登录系统

PASSWROD_GRACE_TIME 7

为防止用户所选的新口令与旧口令相同而变得不安全，一个口令失效后至少28天并且3次修改口令后才能重用

PASSWORD_REUSE_TIME 28

PASSWORD_REUSE_MAX 3

为减少数据库的连接总数，每个用户最多可以同时开启 3 个会话

SESSIONS_PER_USER 3

为防止一个用户会话消耗过多处理器时间（也是限制黑客利用破译用户口令后，以合法用户登录系统进行破坏的规模），每个会话所使用的处理器时间不能超过 16800 个 1% 秒（168秒）

CPU_PER_SESSION 16800

为防止用户由于建立错误的查询（如笛卡尔乘积）而产生大量的 I/O （也是限制黑客利用破译用户口令后，以合法用户登录系统进行破坏的规模），每个会话最多的逻辑阅读量为 23688 个数据块

LOGICAL_READS_PER_SESSION 23688

为防止用户长期挂在数据库系统上，每个用户连接时间最多为 180 分钟（3个小时）

CONNECT_TIME 180

为防止挂在数据库系统上的用户长时间不干活，每个用户的空闲时间为 28 分钟，如果一个用户不干活的时间超过了 28 分钟，Oracle系统会自动将这个用户踢出系统并回滚该用户所有没有提交的事务

IDLE_TIME 28;

表空间管理

创建表空间、增加/删除数据文件、移动数据文件等 14分

永久表空间的创建

P68

CREATE TABLESPACE tablespace1
DATAFILE 'f:\myoracle\tablespace1-1.dbf' SIZE 20M
AUTOEXTEND ON NEXT 10M MAXSIZE UMLIMITED

 autoextend on next 10m maxsize unlimited

指定了允许自动扩展，每次扩展大小为10MB

 

CREATE TABLESPACE tablespace2
DATAFILE 'f:\myoracle\tablespace2-1.dbf' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 800K

 extent management local uniform size 800k

本地化管理表空间

 

CREATE TABLESPACE tablespace3
DATAFILE 'f:\myoracle\tablespace3-1.dbf' SIZE 10M
SEGMENT SPACE MANAGEMENT MANUAL

 使用手动方式管理段的表空间

sgement space manegement manual

增加/删除数据文件

PPT 04-39

P70

追加新的数据文件到表空间

ALTER TABLESPACE tbsname ADD DATAFILE filename SIZE size;

举例

ALTER TABLESPACE tablespace01 
ADD DATAFILE 'F:\Oracle11g\tablespace0101.dbf' SIZE 10M;

删除表空间中无数据的数据文件

ALTER TABLESPACE tbsname DROP DATAFILE filename;

移动数据文件

PPT 04-43

1、

ALTER TABLESAPCE tbsname RENAME DATAFILE filename1 TO filename2;

适用于上面没有活动的撤销数据或临时段的非系统表空间中的数据文件
要求表空间为脱机状态且目标数据文件必须存在
该语句只修改控制文件中指向数据文件的地址
重命名（移动）表空间中数据文件的步骤（数据库为OPEN状态）

• 使用数据字典获取表空间和数据文件的相关信息
• 将表空间置为OFFLINE
• 使用操作系统命令重命名数据文件或移动数据文件
• 执行ALTER TABLESPACE RENAME DATAFILE命令
• 将表空间置为ONLINE
• 使用数据字典获取表空间和数据文件的相关信息
• 如果需要，使用操作系统命令删除无用的数据文件

2、

ALTER DATABASE dbname RENAME FILE filename1 TO filename2;

适用于系统表空间和不能置为脱机的表空间中的数据文件
要求数据库必须允许在MOUNT状态且目标数据文件必须存在
该语句只修改控制文件中指向数据文件的地址
重命名（移动）表空间中数据文件的步骤

• 如果数据库是开启的，使用数据字典获取表空间和数据文件的相关信息
• 关闭数据库
• 使用操作系统命令重命名数据文件或移动数据文件
• 将数据库置为MOUNT状态
• 执行ALTER DATABASE RENAME FILE命令
• 打开数据库
• 使用数据字典获取表空间和数据文件的相关信息
• 如果需要，使用操作系统命令删除无用的数据文件

 

PL/SQL编程

编写匿名块/函数/过程/触发器完成指定功能 3题20分

匿名块

P206

 

CASE 语句

根据输入的员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000～2000之间，则增加150；如果工资在2000～3000之间，则增加100；否则增加50。

DECLARE
    v_sal employees.salary%type;
    v_increment NUMBER(4);
    v_empno  employees.employee_id%type;
BEGIN
    v_empno:=&x;
    SELECT salary INTO v_sal FROM employees
        WHERE employee_id=v_empno;
    CASE   
        WHEN v_sal<1000 THEN v_increment:=200;
        WHEN v_sal<2000 THEN v_increment:=150;
        WHEN v_sal<3000 THEN v_increment:=100;
        ELSE  v_increment:=50;
    END CASE;
    UPDATE employees SET salary=salary+v_increment
        WHERE employee_id=v_empno;
END; 

求1～100之间偶数的和

简单循环

 

WHILE循环

FOR循环

过程

 过程指的是在大型数据库系统中专门定义的一组SQL语句集，它可以定义用户操作参数，并且存在于数据库中，当使用时直接调用即可
过程（存储过程）= 过程的声明 + PL/SQL块

CREATE [OR REPLACE] PROCEDURE 过程名称([参数名称 [参数模式] NOCOPY 数据类型 [, 参数名称 [参数模式] NOCOPY 数据类型, …]])
    [AUTHID [DEFINER | CURRENT_USER]]
AS | IS
    [PRAGMA AUTONOMOUS_TRANSACTION;]
    声明部分;
BEGIN
    程序部分;
EXCEPTION
    异常处理;
END;

 

 

 

函数

 函数也是一种较为方便的存储结构，用户定义的函数可以被SQL语句或PL/SQL程序直接调用，实际上函数与过程最大的区别就在于，函数是可以有返回值的，而过程只能依靠OUT或IN OUT返回数据

CREATE [OR REPLACE] FUNCTION 函数名([参数, [参数, …]])
RETURN 返回值类型
[AUTHID {DEFINE | CURRENT_USER}]
AS | IS
    [PRAGMA AUTONOMOUS_TRANSACTION;]
    声明部分;
BEGIN
    程序部分;
    [RETURN 返回值;]
[EXCEPTION
    异常处理]
END [函数名];

 

 

触发器

CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER]  ---触发时间
[INSTEAD OF]
[INSERT | UPDATE | UPDATE OF 列名称 [, 列名称, …] | DELETE] ---触发事件
ON [表名称 | 视图 | DATABASE | SCHEMA] ---触发对象
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]
[FOR EACH ROW] ---触发频率
[FOLLOWS 触发器名称]
[DISABLE]
[WHEN 触发条件] ---触发条件
[DECLARE] ---触发操作（程序主体）
    [程序声明部分;]
    [PRAGMA AUTONOMOUS_TRANSACTION;]
BEGIN
    程序代码部分;
END [触发器名称];