橙子味冰可乐的博客

临时表空间是一个磁盘空间，主要用于内存排序区不够而必须将数据写到磁盘的那个逻辑区域，由于该空间在排序操作完成后可以由Oracle系统自动释放，所以也称作临时表空间。临时表空间主要用于临时段，而临时段是由数据库根据需要创建管理和删除的，这些临时段的生成通常与排序之类的操作有关，下面的几种操作经常会用到临时表空间。SELECT DISTINCT不重复检索。UNION联合查询。MINUS计算。ANALYZE分析。连接两个没有索引的表。

如果确定要删除当前例程正在使用的UNDO表空间，管理员应首先切换UNDO表空间，然后再删除切换掉的UNDO表空间，来看一个例子。启动例程并打开数据库后，同一时刻指定例程只能使用一个UNDO表空间，切换UNDO表空间是指停止例程当前使用的UNDO表空间，把当前例程的UNDO表空间从“undo_tbs_1”切换到“undotbs1”，然后再删除“undo tbs 1”表空间。由于UNDO表空间只用于存放撤销数据，所以不要在UNDO表空间内建立任何数据对象(如表、索引等)。

使用自动撤销管理模式时，如果没有配置初始化参数UNDO TABLESPACE，Oracle会自动选择第一个可用的UNDO表空间存放UNDO数据;从Oracle 9i版本开始，通过配置该初始化参数，可以指定UNDO数据的保留时间，从而也决定了基于UNDO数据的闪回操作能够闪回的最早时间点。该初始化参数用于指定例程所要使用的UNDO表空间，使用自动UNDO管理模式时，通过配置该参数可以指定例程所要使用的UNDO表空间。该初始化参数用于指定UNDO数据的管理模式，如果为AUTO，则为自动撤销管理模式;

然后用户在SYSTEM模式下执行SELECT Sal FROMscott.empWHERE empno=7788语句，此时用户将取得“旧的工资数据”，而不是新数据5500，而该数据正是从UNDO段中读取的。UNDO表空间中的段也称为撤销段或UNDO段;Oracle 11g新增了强大的闪回功能，其中，很多闪回技术都是基于UNDO段实现的，比如闪回表、闪回事务查询、闪回版本查询等。当执行修改(UPDATE)数据操作时，旧的数据(即UNDO数据)被存放到UNDO段，而新的数据则被存放到数据段中。

当某个非自动扩展表空间的扩展能力不能满足新的扩展需求，数据库管理员就需要向表空间中添加新的数据文件(比如添加一个能够自动扩展的表空间)，以满足数据对象的扩展需要下面来看一个向表空间添加新数据文件的例子，首先查询TBS TEST 2表空间中的数据文件是否为自动扩展，若不是自动扩展，则修改为自动扩展，扩展量为10MB，并且最大扩展空间不受限制，代码及运行结果如下，维护表空间中的数据文件主要包括向表空间中添加数据文件、从表空间中删除数据文件和对表空间中的数据文件进行自动扩展设置，

当某个表空间中的数据不再需要时，或者新创建的表空间不符合要求时，可以考虑删除这个表空间。若要删除表空间，则需要用户具有 DROP TABLESPACE权限。在默认情况下，Oracle系统不采用方式管理文 件，这样删除表空间实际上仅是从数据字典和控制文件中将该表空间的有关信息清除掉，并没有真正删除该表空间包含的所有物理文件。因 此，要想彻底删除表空间来释放磁盘空间，那么在执行删除表空间的命 令之后，还需要手动删除该表空间包含的所有物理文件。当Oracle系统采用。

表空间创建完成后，后期还可以为表空间增加数据文件，以扩大数据的存储空间。增加表空间数据文件的基本语法结构如下所示。

表空间与数据文件紧密相连，相互依存，创建表空间的时候需设置数据文件大小。在后期实际应用中，如果实际存储的数据量超出事先设置的数据文件大小，并且当时创建的时候没有设置自动扩展，则需要修改表空间大小，否则无法向表空间增加数据。基本语法结构如下所示。

设置表空间为只读状态，必须符合以下要求:表空间必须为联机状态，表空间不能包含任何回滚段，表空间不能在归档式下。表空间有4种状态:联机、脱机、只读和读写。修改其中某一种状态的语句如下所示。设置表空间 tspace 为联机状态。设置表空间 tspace 为脱机状态。设置表空间 tspace 为只读状态。设置表空间 tspace 为读写状态。

默认的临时表空间是TEMP，这样如果存在多个用户，那么各个用户都要竞争使用SYSTEM和Temp表空间会大大影响 Oracle 系统的效率。表空间创建完成后，在日常使用过程中，数据库管理员可以对其进行维护，例如重命名表空间、设置默认表空间、删除表空问等。在 Oracle 数据库中创建一个新用户，如果不指定表空间，则默认的永久表空间是 SYSTEM，在某些特殊情况下，有可能需要对表空间的名称进行修改，修改表空间的时候并不会影响表空间中的数据。创建的临时表空间 tspace4 修改为默认临时表空间。

AUTOALLOCATEIUNIFORM SIZE:如果设置的是本地化管理，在数据表空间扩展的时候，可以指定每次扩展的大小是系统自动指定，还是按照指定的大小扩展，默认的大小为 1MB。创建一个永久性表空间，设置表空间初始大小为100MB，自动扩展为 100MB，无最大大小限制，并且该表空间为在线状态，产生日志。创建一个永久性表空间，通过本地化管理方式，初始大小为100MB，扩展大小设置为自动管理，段空间管理方式为手动。PERMANENT:设置创建的表空间为永久表空间，如果不设置，则默认为永久表空间。

为了便于对数据库进行管理和优化，Oracle 数据库被划分成许多表空间的逻辑区域，一个 Oracle 数据库有一个或者多个表空间，而一个表空间则对应一个或者多个物理上的数据库文件。SYSAUX 表空间是 SYSTEM 表空间的辅助空间，主要用于存储除数据字典外的其他数据对象，可以减少 SYSTEM 表空间的负荷。表空间和数据文件紧密相连，相互依存，在创建表空间的同时要创建数据文件，同理，增加数据文件的同时也要指定表空间。默认情况下所有的表空间都是读写状态，具有适当权限的用户可以读写该表空间的数据。

这个范例要求当员工工资改变的时候，核对是否超出全体员工工资允许的范围，如果超过范围，则提示错误，不能修改。(1)在 emp 数据表上建立触发器，当员工改变工作岗位或者所在部门发生变化时，将变化信息记录在工作变化日志中。(3)在 emp 数据表上建立触发器，统计部门人数，当插入新员工的时候，统计是否部门员工人数超过上限。(2)在 emp 数据表上建立触发器，当员工工资改变的时候，核对是否超出全体员工现有工资的范围。如果改变过，取他上次改变工作岗位或者部门的日期，如果没有改变过，就从emp 表中取雇佣日期，

分析:由于所要创建的存储过程和函数上一章已经创建过，因此只需按照程序包的创建方法，先在包规范中声明存储过程和函数，然后在包体中将实现代码写入即可。如上图所示，在程序包体中，分别是对应包规范中声明的存储过程和函数的实现代码。创建完程序包的包规范和包体后，就可以使用了，下面看如何使用，如下图所示。我们创建一个程序包，内容包含上一章所创建的存储过程和函数。如上图所示，在包规范中声明了一个函数和一个过程。下面就通过具体范例来演示程序包的使用。下图是程序包体的程序代码。包规范创建如下图所示。

包规范提供应用程序的接口，在包规范中声明程序包中可以使用的变量、数据类型、存储过程、函数、异常等。其中 dbms output是程序包的名称，put_line 是该程序包中定义的一个存储过程所谓程序包，还可以声明其他没有在包规范中声明的变量、类型、过程、函数，但它们只能在包中使用，不能被其他应用程序调用。程序包由包规范和包体两部分组成，因此程序包的创建也分为两部分，分别为包规范的创建和包体的创建。当然，在程序包中还包含各种程序元素，例如常量、变量等。在包体中包含包规范中声明的过程和函数的实现代码，此外，

如果想查看当前所有的触发器信息，可以使用数据字典 user_triggers，这个数据字典有很多字段可以查看所有触发器的名称、类型、表名、拥有者等信息。上面代码可以查看当前用户下触发器名字、类型和表的名字。可以使用如下命令代码查看该数据字典都有哪些字段，有选择性地显示不同内容。如果要删除触发器非常简单，类似于删除数据表和视图，语法如下所示。例如下面命令代码可删除触发器 tri_ddl。

这种修改触发器状态的命令一次只能修改一个触发器。如果想一次修改多个触发器的状态，可以通过修改表结构的方法，修改在某个表上建立的所有触发器的状态。参数说明:DISABLE 是触发器禁用参数，即使触发器处于无效状态;ENABLE 是触发器启用参数，即使触发器处于有效状态。这个命令修改所有建立在 emp 数据表上的触发器，则使这些触发器处于无效状态,如果参数ENABLE 修改为 DISABLE，在 Oracle 数据库中，所创建的触发器可以根据情况，灵活修改它的状态，使其有效或者无效，即启用或者禁用。

分别创建一个数据表，修改数据表结构，创建一个视图，删除一个视图，删除一个数据表，如下所示。模式触发器是在执行 DDL语句时触发的，例如执行创建、修改或者删除的 CREATE、ALTER、DROP 语句。如上图所示，当发生 CREATE、ALTER 或者 DROP 事件时，触发上面的触发器，向日志数据表中插入数据。要想记录登录和退出数据库的情况，必须建立一张数据表用于记录登录和退出的日志。下面就分别创建登录和退出的触发器，使得当登录或者退出的时候，向创建的日志数据表中添加数据，如下图所示。

在 Oracle 数据库中，复杂的视图常从多个数据表中获取数据有的视图还包含分组等计数函数，因此一般不允许对视图施加 DML操作，但是使用 INSTEAD OF 触发器，在触发器内通过定义的PL/SQL块，完成间接地对视图进行 DML操作。INSTEAD OF 触发器，也称替换触发器，是一种特殊的触发器，和其他建立在数据表上的触发器不同，INSTEAD OF 触发器建立在视图上。在Oracle 数据库中，一般要想通过 DML操作修改视图是不可行的，因为修改视图实际上是修改对应数据表中的内容，

行级 DML 触发器必须加入 FOR EACH ROW 子句，和语句级 DML触发器不一样，行级 DML 触发器在每次执行 DML操作的时候，如果操作一条记录，触发器就执行一次，如果涉及多条记录，那么触发器就执行多次。如果触发操作时，在UPDATE情况下，:OLD标识符指更新前的记录，:NEW标识符指更新后的记录如果触发操作时，在 DELETE 情况下，:OLD 标识符指被删除前的记录，而:NEW 标识符没有定义。为 emp 数据表创建一个触发器，当插入新员工的时候，显示新员工的工号和姓名;

在 Oracle 数据库的系统事件发生时进行触发，例如系统启动或者退出、异常错误等，这种系统触发器称为数据库触发器:或者发生 DDL语句时触发，例如执行创建、修改或者除的 CREATEALTER、DROP 语句等，这种触发器称为模式触发器。在执行 DML 语句时触发，可以定义为 INSERT、UPDATE、DELETE 操作，也可以定义在操作之前或者之后触发，也可以指定为行级触发或者语句级触发。根据触发器的使用范围不同，可以把触发器分为3种:DML触发器、INSTEAD OF 触发器和系统触发器。

如上图所示，首先定义一个 IN 参数，用于接收传送过来的部门编码，然后定义两个局部变量用于程序中存储平均工资和作为循环变量获取查询结果，(1)定义I 参数接收部门编号，再定义一个 IN 参数用于提供查询最高工资或最低工资的标志，同时定义函数输出类型用于存放工资。如上图所示，首先定义一个IN 参数，用于接收调用时出入的部门编号，同时定义两个输出参数用于向调用传出结果。(2)这个实现起来更为简单，输入参数用于接收员工编号，然后在程序中查询对应的工资，以 scott 用户下 emp 数据表为基础，创建如下过程。

在实际使用中经常会需要查询数据库中已有的函数或者某一个函数的内容，下面就介绍一下如何查询函数。和存储过程类似，这也需要使用到数据字典user_source，使用的查询语句如下所示。上面这个语句查询当前用户下所有的用户定义的函数名字。此外，我们还可以查询存储过程的内容，查询语句如下所示。上面查询语句的返回结果如下图所示。可以看到该函数的程序代码。

如上所示，当输入编号“7369”时，可以查询出此员工的姓名，如果输入的编号数据表中没有，则输出“没有对应此编号的员工”如上所示，分别定义了函数参数以及函数返回类型，然后在程序体内执行查询语句，并把结果通过RETURN 语句返回。输入一个编号，查询数据表 emp 中是否有这个编号，如果有返回对应员工姓名，如果没有，则提示没有对应员工。此外，还可以在 PL/SQL数据块中使用，这是需要实现定义一个对应变量以存储函数结果，如下图所示。如上图所示，在函数声明中声明了函数返回类型，这一点和存储过程不一样，

在函数的主程序段中，必须使用 RETURN 语句返回最终的函数值，并且返回值的数据类型要和声明的时候说明的类型一样。(1)OR REPLACE 为可选参数，表示如果数据库中已经存在要创建的函数，则先把原先函数删除再重新建立函数，或者说覆盖原先的函数。可以把经常使用的功能定义为一个函数，就像系统自带的函数(例如前面章节介绍的大小写转换，求绝对值等函数)一样使用。函数和存储过程非常类似，也是可以存储在 Oracle 数据库中的 PL/SQL代码块，但是有返回值。(4)函数的返回类型是必选项。

在实际使用中，经常会需要查询数据库中已有的存储过程或者某一个存储过程的内容，这需要使用到数据字典 user_source，使用的查询语句如下所示。上面这个语句查询当前用户下所有的存储过程的名字。我们还可以查询存储过程的内容，查询语句如下所示。下面就介绍-下如何查询存储过程。上面查询语句的返回结果如下图所示。可以看到存储过程的程序代码。

如上图所示，存储过程内容很简单，定义了一个IN OUT参数 var_num 变量，该变量接收来自外部的变量值，然后在存储过程中计算绝对值，再把结果返回到该参数中。如上所示，在存储过程中定义了两个参数，参数var empno 是 IN 参数，接收来自外部数值，参数var_ename 是 OUT参数，在程序内接受查询的结果，我们可以在存储过程中只定义一个参数，设置该参数为IN OUT 参数，接收来自外部调用的然后在存储过程中计算其绝对值并把结果放到该参数中。当输入的 IN 参数数值是“7369”的时候，

输入编号可以对应一个 IN 参数，然后再存储过程中使用 WHERE 语句查询，使用 INTO 子句把结果赋予一个变量。如果想要执行这个过程，输入一个编号，查询数据表emp中是否有这个编号，如果有则显示对应员工姓名，如果没有，则提示没有对应员工。使用 IN 参数可以向存储过程中的程序单元输入数据，在调用的时候提供参数值，被存储过程读取。存储过程创建后，下面测试一下是否符合要求，仍然使用EXEC 命令执行，如下图所示。调用的时候，需要注意，调用时参数的书写顺序应该与存储过程中 IN 参数的顺序一样，

如果是输入参数，则参数后面用“IN”关键字，表示接受外部过程传递来的值；存储过程是事先编译好的代码，再次调用的时候不需再次编译，因此程序的运行效率非常高。（1）OR REPLACE 为可选参数，表示如果数据库中已经存在要创建的过程，则先把原先过程删除，再重新建立过程，或者说覆盖原先的过程。存储过程创建后，以编译的形式存在于 Oracle 数据库中，可以在 SQL Plus 中或者 PL/SQL 块中调用。存储过程是一种命名的 PL/SQL 数据块，存储在 Oracle 数据库中，可以被用户调用。

如上图所示，在声明部分，第 2 行代码中定义游标，在第 6 行打开游标，然后使用 FETCH...INTO...检索游标，获取当前游标指向的记录信息。由于要输入员工工号，然后在游标中使用，因此需要定义含有参数的游标，并且在程序中使用 %FOUND 属性判断是否存在对应记录，如上所示，两层嵌套实现信息获取，外层游标是部门信息，然后使用外层游标得到的部门号作为内层游标的参数，获取部门信息。因此可以定义两个游标，一个游标是获取部门的信息，另外一个游标是获取该部门的员工信息，两个游标嵌套使用。

由于使用游标定义的查询或者操作返回多个记录集合到缓冲区，要想通过游标访问每条记录，需要结合循环结果使用，这两种循环结构概况如下。

和前面介绍的显式游标检索的方法一样，都是使用 FETCH...INTO...语句存储当前游标指向的记录值，并结合简单循环结构显示查询结果中的记录。而动态游标也称为游标变量，是一个指向多行查询结果集的指针，不与特定的查询绑定，可以在打开游标变量时定义查询，和前面显式游标一样，使用之前要打开的游标，不过由于在动态游标定义的时候并没有对应的查询语句，显式游标在定义时与特定的查询绑定，其结构是不变的，反映的是在显式游标打开的时刻当时的状态，系统会执行对应的查询语句，将查询结果放入游标变量对应的缓冲区中。

这个参数用来接受外部传来的值，如果调用游标时没有传入参数，则使用默认值“clerk”，游标实现查询 emp 表中部门参数 var_job 中的所有员工的信息。在上面这个例子中，游标定义了一个输入参数 var_job，数据类型为 varchar2，但是要注意此时并没有定义长度，如果定义长度就错了。如果在游标声明的时候定义了参数，并有初始化值，但打开的时候没有使用参数，则游标就使用定义时参数的初始值；游标打开后，缓冲区中是查询结果，此时可以使用游标把查询结果集合中的记录分别读取出来，基本语法如下所示。

在 Oracle 数据库中，为了处理 SQL 语句，在内存中会分配一个区域，又叫上下文区，整个区域是 SQL 语句返回的数据集合，而游标就是指向这个上下文的指针。而动态游标也称为游标变量，是一个指向多行查询结果集的指针，不与特定的查询绑定，可以在打开游标变量时定义查询，可以返回不同结构的结果集。游标从字面上理解为游动的光标，可以使用 Excel 表格来想象游标的作用，游标指向每一行，通过游标访问每行数据。%ISOPEN ：布尔型，判断游标是否打开，如果打开，则返回属性值为 TRUE，否则为 FALSE。

从 emp 数据表创建一个新的员工表，该表包含所有工作岗位CLERK、SALESmAN 和 mANAGER 的信息，然后编程实现根据工作不同调整工资，CLERK 工资增加 10%，SALESmAN 工资增加 15%，mANAGER工资增加 20%根据输入变量的值进行判断，如果输入的是偶数，则计算 100 以内偶数之和，如果输入的是奇数，则计算 100 以内奇数之和。在上面的代码中，第７行判断输入的变量值是否是偶数，如果是偶数，则执行第 8~14 行的代码，输入 3 个不同的数，按照从大到小的顺序输出。

在声明部分定义错误异常，其中预定义异常系统已经定义，其他两种异常需要用户定义。基本语法如下所示。异常变量 excption如果是非预定义的异常，还需要为错误编号关联这个异常变量，基本语法如下所示。prama excption_init(异常变量,-######)其中，“#####”为 Oracle 的错误编号。

对应于 Oracle 错误，是 Oracle 系统自身提供的，用户可以在 PL/SQL 异常处理部分使用名称对它们进行标识。对这些异常情况的处理，用户无须在程序中定义，是由 Oracle 自动触发。Oracle 运行时错误可以分为 Oracle 错误和用户自定义错误，与此对应，根据异常产生的机制和原理，可将 Oracle 的系统异常分为 3 种。对这种异常情况的处理，需要用户在程序中定义，然后由 Oracle 自动将其引发。下面是 Oracle 预定义的异常与 Oracle 错误之间的对应关系。

前面两种循环都要根据条件是否成立而确定循环体的执行，具体循环体执行多少次事先并不知道。FOR 循环可以控制循环执行的次数，由循环变量控制循环体的执行。当循环变量在大于开始数值，小于结束数值的时候，执行语句序列，否则退出循环。默认情况下循环变量是循环递增的，如果使用了 REVERSE 参数，则循环递减。

在上面的代码中，首先判断i的值是否超过 100，如果条件为真，累加变量，每次循环i的值增加继续执行循环体，然后再判断条件是否成立，如果超过了100，则退出循环。运行的时候，首先判断条件表达式，:如果结果是 TRUE，则运行循环体内的语句序列，如果运行结果为 FALSE，则退出循环。WHILE 循环是先判断条件，如果条件成立就执行循环体，如果不成立，就退出循环。计算 100 之内的偶数之和。

循环语句和条件语句一样都可以控制程序的执行顺序，而循环语句是指一段程序可以重复执行，PL/SQL 语言主要支持 3 种类型的循环：LOOP 循环、WHILE 循环和 FOR 循环。这种循环将循环条件包含在循环体内，LOOP 循环会先执行一次循环体，然后判断是否满足设定的条件来决定循环是否继续执行。如果条件表达式的值为TRUE，则退出循环体，如果条件表达式的值为 FALSE，则继续执行循环体。若没有超过，继续执行循环体，累加变量i，如果超过了 100，则退出循环。计算 100 以内的奇数之和，并输出到屏幕。