Oracle PL/SQL语言初级教程之表和视图

一时失言乱红尘 2022-09-20 08:44 214阅读 0赞

Oracle数据库数据对象中最基本的是表和视图，其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。  
  
　　 **表和视图**  
  
　　Oracle中表是数据存储的基本结构。ORACLE8引入了分区表和对象表，ORACLE8i引入了临时表，使表的功能更强大。视图是一个或多个表中数据的逻辑表达式。本文我们将讨论怎样创建和管理简单的表和视图。  
  
　　 **管理表**  
  
　　表可以看作有行和列的电子数据表，表是关系数据库中一种拥有数据的结构。用CREATE TABLE语句建立表，在建立表的同时，必须定义表名，列，以及列的数据类型和大小。例如：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE products<br>　　( PROD_ID NUMBER(4),<br>　　　PROD_NAME VAECHAR2(20),<br>　　　STOCK_QTY NUMBER(5,3)<br>　　);</td> 
  </tr> 
 </tbody> 
</table>

这样我们就建立了一个名为products的表， 关键词CREATE TABLE后紧跟的表名，然后定义了三列，同时规定了列的数据类型和大小。  
  
　　在创建表的同时你可以规定表的完整性约束，也可以规定列的完整性约束，在列上普通的约束是NOT NULL,关于约束的讨论我们在以后进行。  
  
　　在建立或更改表时，可以给表一个缺省值。缺省值是在增加行时，增加的数据行中某一项值为null时，oracle即认为该值为缺省值。  
  
　　下列数据字典视图提供表和表的列的信息：  
  
　　　. DBA\_TABLES  
　　　. DBA\_ALL\_TABLES  
　　　. USER\_TABLES  
　　　. USER\_ALL\_TABLES  
　　　. ALL\_TABLES  
　　　. ALL\_ALL\_TABLES  
　　　. DBA\_TAB\_COLUMNS  
　　　. USER\_TAB\_COLUMNS  
　　　. ALL\_TAB\_COLUMNS  
  
　　 **表的命名规则**  
  
　　表名标识一个表，所以应尽可能在表名中描述表，oracle中表名或列名最长可以达30个字符串。表名应该以字母开始，可以在表名中包含数字、下划线、\#、$等。  
  
　　 **从其它表中建立表**  
  
　　可以使用查询从基于一个或多个表中建立表，表的列的数据类型和大小有查询结果决定。建立这种形式的表的查询可以选择其他表中所有的列或者只选择部分列。在CREATE TABLE语句中使用关键字AS，例如:

<table> 
 <tbody> 
  <tr> 
   <td>SQL&gt;CREATE TABLE emp AS SELECT * FROM employee<br><br>TABLE CREATED<br><br>SQL&gt; CREATE TABLE Y AS SELECT * FROM X WHERE no=2</td> 
  </tr> 
 </tbody> 
</table>

需要注意的是如果查询涉及LONG数据类型，那么CREATE TABLE....AS SELECT....将不会工作。  
  
　　 **更改表定义**  
  
　　在建立表后，有时候我们可能需要修改表，比如更改列的定义，更改缺省值，增加新列，删除列等等。ORACLE使用ALTER TABLE语句来更改表的定义  
  
　　 **1、增加列**  
  
　　语法：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE [schema.] table_name ADD column_definition</td> 
  </tr> 
 </tbody> 
</table>

例：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE orders ADD order_date DATE;<br><br>TABLE ALTER</td> 
  </tr> 
 </tbody> 
</table>

对于已经存在的数据行，新列的值将是NULL.  
  
**　　2、更改列**  
  
　　语法：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE [schema.] table_name MODIFY column_name new_attributes;</td> 
  </tr> 
 </tbody> 
</table>

例：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE orders MODITY (quantity number(10,3),status varchar2(15));</td> 
  </tr> 
 </tbody> 
</table>

这个例子中我们修改了表orders，将STATUS列的长度增加到15,将QUANTITY列减小到10,3;  
  
　　修改列的规则如下：  
  
　　　. 可以增加字符串数据类型的列的长度，数字数据类型列的精度。  
  
　　　. 减少列的长度时，该列应该不包含任何值，所有数据行都为NULL.  
  
　　　. 改变数据类型时，该列的值必须是NULL.  
  
　　　. 对于十进制数字，可以增加或减少但不能降低他的精度。  
  
　 **　3、删除数据列**  
  
　　优化ORACLE数据库，唯一的方法是删除列，重新建立数据库。在ORACLE8i中有很多方法删除列，你可以删除未用数据列或者可以标示该列为未用数据列然后删除。  
  
　　删除数据列的语法是：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE [schema.] table_name DROP {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]</td> 
  </tr> 
 </tbody> 
</table>

要注意的是在删除列时关于该列的索引和完整性约束也同时删除。注意关键字CASCADE CONSTRAINS，如果删除的列是多列约束的一部分，那么这个约束条件相对于其他列也同时删除。  
  
　　如果用户担心在大型数据库中删除列要花太多时间，可以先将他们标记为未用数据列，标记未用数据列的语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE [schema.] table_name SET UNUSED {COLUM column_names | (column_names)}[CASCADE CONSTRAINS]</td> 
  </tr> 
 </tbody> 
</table>

这个语句将一个或多个数据列标记为未用数据列，但并不删除数据列中的数据，也不释放占用的磁盘空间。但是，未用数据列在视图和数据字典中并不显示，并且该数据列的名称将被删除，新的数据列可以使用这个名称。基于该数据列的索引、约束，统计等都将被删除。  
  
　　删除未用数据列的语句是：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE [schema.] table_name DROP {UNUSED COLUM | COLUMN CONTINUE}</td> 
  </tr> 
 </tbody> 
</table>

**删除表和更改表名**  
  
　　删除表非常简单，但它是一个不可逆转的行为。  
  
　　语法：

<table> 
 <tbody> 
  <tr> 
   <td>DROP TABLE [schema.] table_name [CASCADE CONSTRAINTS]</td> 
  </tr> 
 </tbody> 
</table>

删除表后，表上的索引、触发器、权限、完整性约束也同时删除。ORACLE不能删除视图，或其他程序单元，但oracle将标示他们无效。如果删除的表涉及引用主键或唯一关键字的完整性约束时，那么DROP TABLE语句就必须包含CASCADE CONSTRAINTS子串。  
  
　　 **更改表名**  
  
　　RENAME命令用于给表和其他数据库对象改名。ORACLE系统自动将基于旧表的完整性约束、索引、权限转移到新表中。ORACLE同时使所有基于旧表的数据库对象，比如视图、程序、函数等，为不合法。  
  
　　语法：

<table> 
 <tbody> 
  <tr> 
   <td>RENAME old_name TO new_name;</td> 
  </tr> 
 </tbody> 
</table>

例：

<table> 
 <tbody> 
  <tr> 
   <td>SQL&gt; RENAME orders TO purchase_orders;<br><br>TABLE RENAMED<br></td> 
  </tr> 
 </tbody> 
</table>

**截短表**  
  
　　TRUNCATE命令与DROP命令相似， 但他不是删除整个数据表，所以索引、完整性约束、触发器、权限等都不会被删除。缺省情况下将释放部分表和视图空间，如果用户不希望释放表空间，TRUNCATE语句中要包含REUSE STORAGE子串。TRUNCATE命令语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>TRUNCATE {TABLE|CLUSTER} [schema.] name {DROP|REUSE STORAGE}</td> 
  </tr> 
 </tbody> 
</table>

例：

<table> 
 <tbody> 
  <tr> 
   <td>SQL&gt; TRUNCATE TABLE t1;<br><br>TABLE truncate.</td> 
  </tr> 
 </tbody> 
</table>

**　　管理视图**  
  
　　视图是一个或多个表中的数据的简化描述，用户可以将视图看成一个存储查询（stored query)或一个虚拟表(virtual table).查询仅仅存储在oracle数据字典中，实际的数据没有存放在任何其它地方，所以建立视图不用消耗其他的空间。视图也可以隐藏复杂查询，比如多表查询，但用户只能看见视图。视图可以有与他所基于表的列名不同的列名。用户可以建立限制其他用户访问的视图。  
  
　　 **建立视图**  
  
　　CREATE VIEW命令创建视图，定义视图的查询可以建立在一个或多个表，或其他视图上。查询不能有FOR UPDATE子串，在早期的ORACLE8i版本中不支持ORDER BY子串，现在的版本中CREATE VIEW可以拥有ORDER BY子串。  
  
　　例：

<table> 
 <tbody> 
  <tr> 
   <td>SQL&gt; CREATE VIEW TOP_EMP AS <br>SELECT empno EMPLOYEE_ID,ename EMPLOYEE_NAME,salary <br>FROM emp<br>WHERE salary &gt;2000</td> 
  </tr> 
 </tbody> 
</table>

用户可以在创建视图的同时更改列名，方法是在视图名后立即加上要命名的列名。重新定义视图需要包含OR REPLACE子串。

<table> 
 <tbody> 
  <tr> 
   <td>SQL&gt; CREATE VIEW TOP_EMP<br>（EMPLOYEE_ID，EMPLOYEE_NAME，SALARY) AS <br>SELECT empno ,ename ,salary <br>FROM emp<br>WHERE salary &gt;2000</td> 
  </tr> 
 </tbody> 
</table>

如果在创建的视图包含错误在正常情况下，视图将不会被创建。但如果你需要创建一个带错误的视图必须在CREATE VIEW语句中带上FORCE选项。如：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE FORCE VIEW ORDER_STATUS AS <br>SELECT * FROM PURCHASE_ORDERS<br>WHERE STATUS='APPPOVE';<br><br>SQL&gt;/<br><br>warning :View create with compilation errors</td> 
  </tr> 
 </tbody> 
</table>

这样将创建了一个名为ORDER\_STATUS的视图，但这样的视图的状态是不合法的，如果以后状态发生变化则可以重新编译，其状态也变成合法的。  
  
　　 **从视图中获得数据**  
  
　　从视图中获得数据与从表中获得数据基本一样，用户可以在连接和子查询中使用视图，也可以使用SQL函数，以及所有SELECT语句的字串。  
  
　　 **插入、更新、删除数据**  
  
　　用户在一定的限制条件下可以通过视图更新、插入、删除数据。如果视图连接多个表，那么在一个时间里只能更新一个表。所有的能被更新的列可以在数据字典USER\_UPDATETABLE\_COLUMNS中查到。  
  
　　用户在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示创建的视图是一个只读视图，不能进行更新、插入、删除操作。WITH CHECK OPTION表示可以进行插入和更新操作，但应该满足WHERE子串的条件。这个条件就是创建视图WHERE子句的条件，比如在上面的例子中用户创建了一个视图TOP\_EMP,在这个视图中用户不能插入salary小于2000的数据行。  
  
　　 **删除视图**  
  
　　删除视图使用DROP VIEW命令。同时将视图定义从数据字典中删除，基于视图的权限也同时被删除，其他涉及到该视图的函数、视图、程序等都将被视为非法。  
  
　　例：