Oracle SQL*PLUS基础及sqlplus命令详解-1

待我称王封你为后i 2022-08-12 00:12 50阅读 0赞

Oracle SQL\*PLUS基础  
  
Oracle的SQL\*PLUS是设计所有应用系统的基础工具。要想将应用系统设计成一个健壮的、性能优越的系统。最关键的是要理解RDBMS的真正含义和结构，理解Oracle SQL\*PLUS 的特点和核心，弄清关系数据库与桌面数据库的差别。比如理解数据的完整性、一致性、索引、视图等。只有这样才能设计出符合Oracle特点的应用系统。从而保证系统在提供使用后不会出现一致性、性能等问题。  
§1.1 理解关系数据库系统（RDBMS）  
CODASYL(数据系统语言协会)是数据库任务组（Database Task Group，DBTG）创建的一种数据库标准，这是一种基于COBOL的网络数据库标准。  
  
§1.1.1 关系模型  
一个描述两个集合的元素如何相互联系或如何一一对应的数学概念，对于数据库来说，关系只是一个带有一些特殊属性的表。所以有：  
  
l 数据的基础项是关系  
l 在这些表上的操作只产生关系  
一个关系表必须符合某些特定条件，才能成为关系模型的一部分  
l 储存在单元中的数据必须是原子的。每个单元只能存储一条数据，叫信息原则（Information Principle）。如果存储多条则违反信息原则。特舒情况下可能需要违反信息原则。  
l 储存在列下的数据必须具有相同的数据类型。  
l 每一行是唯一的（没有完全相同的行）。  
l 列没有顺序。  
l 行没有顺序  
l 列有一个唯一性的名称。  
  
关系模型的另一个是完整性原则。它包括实体完整性原则（Entity integrity rule）和引用完整性原则（ Referential integrity rule ），如：  
l 主键( Primary key )是能唯一标识行的一列或一组列的集合。  
l 由多个列构成的主键称为连接键（Concatenated key）、组合键（Compound key ）,或称作为复合键(Composity key )。  
  
另外就是外部键（Foreign key ）是一个表中的一列或一组列，它们在其它表中作为主键而存在。一个表中的外部键被认为是对另外一个表中主键的引用。实体完整性原则简洁地表明主键不能全部或部分地空缺或为空，引用完整性原则简洁地表明一个外键必须为空或者它所引用的主键当前存在值相一致。  
  
§1.1.2 Codd十二法则  
Oracle数据库系统是一个完美的完全符合数据库技术的关系数据库系统。要想你的应用设计按照数据库原理来进行，最重要的就是理解Oracle的结构、语句和命令。Codd提出的十二条法则在Oracle系统中都可以找到：  
  
1) 信息法则。  
2) 授权存储法则，每一个数据项都通过“表名+行主键+列名”的组合形成访问。  
3) 必须以一致的方法使用空值。  
4) 一个活跃的、在线数据字典应作为关系型表被储存  
5) 必须提供数据存取语言进行存取访问。  
6) 所有能被更新的视图应当是可被更新的。  
7) 必须有集合级的插入、更新和删除。  
8) 物理数据的独立性。即应用不依赖物理结构。  
9) 逻辑数据的独立性。如果一个表被分成两个部分，则应用视图连接在一起，以便不会对应用产生影响。  
10) 完整性的独立性。完整性规则应该储存在数据字典中。  
11) 分布独立性。一个数据库即使被分布，也应该能工作。  
12) 非破坏性原则。如果允许低级存取，一定不能绕过安全性和完整性原则。  
  
§1.2 关系数据库系统（RDBMS）的组成  
RDBMS由两部分组成，即数据库系统内核（软件）和数据字典（内核用于管理数据库系统的数据结构）两部分。  
  
§1.2.1 RDBMS 内核  
RDBMS就是用来控制数据访问的操作系统。它的任务是按照一定的规则存储数据、检索数据及保护数据。  
  
§1.2.2 数据字典概念  
数据自动存放数据库中所有对象（如表，索引，视图等）所需的信息。Oracle 8i 的数据字典是存放数据库系统信息的一组表，从数据字典中的信息可以确认数据库中数据对象的基本信息及存放位置。  
  
§1.3 SQL、SQL\*Plus及 PL/SQL  
下面用简单的语言解释Oracle的常用产品所包含的SQL\*PLUS和PL/SQL的关系。  
§1.3.1 SQL和SQL\*PLUS的差别  
SQL是标准结构查询语言，而SQL\*PLUS实际是包括标准的SQL和Oracle公司的一些命令组成的产品，因而Oracle公司将其取名为SQL\*PLUS。下面是它们的解释。  
  
1. SQL（Structured Query Language）  
  
SQL有许多关键字，以下语句是常用于开头的语句：  
Alter Insert  
Audit Lock  
Commit Noaudit  
Comment Rename  
Create Revoke  
Delete Select  
Drop Update  
Grant Validate  
  
注：  
1.Oracle的SQL缓冲区一次只能存放一条SQL命令；  
2.Validate (使生效) 在 oracle 中跟 Enable 一起用，但可以省去 Validate,所以许多资料都不介绍Validate 的用法。其语法如：  
Enable\{\[Validate\]\[Novalidate\]\} \{ \[UNIQUE\]\[PRIMARY KEY\]... \} ...  
  
2. SQL\*PLUS  
  
除SQL外，SQL\*PLUS还包括称为SQL\*PLUS命令的附加命令，这些命令主要用于形成复杂报表，编辑SQL命令，提供帮助信息，维护系统等。SQL\*PLUS包括的命令如下：  
  
＠ Connect  
Host Set  
\# Copy  
Input Show  
$ Define  
List Spool  
/ Del  
Newpage Sqlplus  
Accept Describe  
Pause Start  
Append Disconnect  
Quit Timing  
Break Document  
Remark Ttitle  
Btitle Edit  
Prompt Undefine  
Chang Execute  
Print Save  
Clear Exit  
Run Column  
Get Runform  
Compute Help  
  
3. DDL（Data Define Language）  
  
对于结构查询语言（有时称SQL命令），可以将它们分成两组，一组是数据定义语言(DDL)；另一组是数据操纵语言（DML）。其中用于数据定义的语言如下：  
  
Alter procedure 重编译过程  
Alter table 修改表的属性  
Analyze 统计数据库对象性能值  
Alter table add Constraint 对已有的表加约束  
Create table 建立表结构  
Create index 建立索引  
Drop table 删除表实体及相关的索引  
Drop index 删除索引  
Grant 授权给用户或角色  
Truncate 删除表中的所有行  
Revoke 从用户或角色收回权限  
  
4．DML（Data Manipulation Language）  
  
对于结构查询语言的另一组是数据操纵语言（DML）。DML其中用于数据操纵的命令如下：  
  
Insert  
Delete  
Update  
Select  
Commit work  
Rollback  
  
§1.3.2 PL/SQL语言  
PL/SQL是Oracle RDBMS (Oracle 6 之后版本)的一个组成部分，PL 是“过程化语言（Procedure Language ）”的缩写。PL/SQL语言是在SQL语言中结合了结构化过程语言成分的准第四代语言。  
  
使用PL/SQL的目的：  
由于大多数PL/SQL是在服务端来运行，这样可减少由客户端运行程序时所需的网络数据流量。  
可以使用PL/SQL的地方：  
l PL/SQL可以单独进行程序的编写，完成一般的处理功能；  
l 在高级语言中可嵌入PL/SQL 块；  
l 在4GL中可以嵌入PL/SQL 块；  
l 在PL/SQL程序中可以嵌入 HTML和XML。  
  
§1.4 登录到SQL\*PLUS  
我们创建任何对象，如创建表、索引等都需要连接到Oracle中，这里用“登录”主要是Oracle的界面提供的是Login 这样的叫法。其实就是连接的意思。在Client/Server结构下，Oracle提供两种方式连接SQL\*PLUS，其中SQL\*NET V2.x 版本(目前版本不再使用SQL\*NET V2.x)提供在字符方式下连接到 SQL\*PLUS，SQL\*NET V2. x版本提供在图形方式（Window）使用,目前版本的 ORACLE8/8i 都使用 NET8 连接.(NET8 不再支持字符终端)  
  
§1.4.1 UNIX环境  
在UNIX下，要确保客户端或服务器端与Oracle服务器系统进行连接，必须保证tnsnames.ora和listener.ora两个参数文件的正确配置。详细的配置解释在DBA章节里解释。下面是tnsnames.ora和listener.ora两个参数文件的内容显示。  
  
1. tnsnames.ora参数文件：  
INST1\_HTTP =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = dbsvr)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVER = SHARED)  
(SERVICE\_NAME = s450)  
(PRESENTATION = [http://admin][http_admin])  
)  
)  
  
EXTPROC\_CONNECTION\_DATA =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))  
)  
(CONNECT\_DATA =  
(SID = PLSExtProc)  
(PRESENTATION = RO)  
)  
)  
  
S450 =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = dbsvr)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME = s450)  
)  
)  
  
2. listener.ora参数文件：  
LISTENER =  
(DESCRIPTION\_LIST =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = dbsvr)(PORT = 1521))  
)  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))  
)  
)  
)  
  
SID\_LIST\_LISTENER =  
(SID\_LIST =  
(SID\_DESC =  
(SID\_NAME = PLSExtProc)  
(ORACLE\_HOME = /home/oracle/app/oracle/product/8.1.7)  
(PROGRAM = extproc)  
)  
(SID\_DESC =  
(GLOBAL\_DBNAME = s450)  
(ORACLE\_HOME = /home/oracle/app/oracle/product/8.1.7)  
(SID\_NAME = s450)  
)  
)  
  
如果安装后已经正确地对上面的两个参数文件进行配置，则可以按照下面步骤进行登录：  
  
1)先启动服务器端监听器：  
lsnrctl start  
  
2)用下面命令显式登录到 SQL\*PLUS：  
$sqlplus username/password  
或  
$SQLPLUS username/password@connect\_string  
  
3)用下面命令隐式登录到 SQL\*PLUS：  
$sqlplus \[enter\]  
Enter user name:scott  
Enter password:\*\*\*\*\*\*  
  
§1.4.2 Windows NT和WINDOWS/2000环境  
在NT/2000环境下，要使客户端能与Oracle服务器进行连接，tnsnames.ora和listener.ora参数文件的配置如下：  
  
1. tnsnames.ora参数文件：  
  
ORA816.TAIJI.COM.CN =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = zhao)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME = ora816)  
)  
)  
  
EXTPROC\_CONNECTION\_DATA.TAIJI.COM.CN =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))  
)  
(CONNECT\_DATA =  
(SID = PLSExtProc)  
(PRESENTATION = RO)  
)  
)  
S450 =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = dbsvr)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME = s450)  
)  
)  
  
2. listener.ora参数文件：  
  
LISTENER =  
(DESCRIPTION\_LIST =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))  
)  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = zhao)(PORT = 1521))  
)  
)  
(DESCRIPTION =  
(PROTOCOL\_STACK =  
(PRESENTATION = GIOP)  
(SESSION = RAW)  
)  
(ADDRESS = (PROTOCOL = TCP)(HOST = zhao)(PORT = 2481))  
)  
)  
  
SID\_LIST\_LISTENER =  
(SID\_LIST =  
(SID\_DESC =  
(SID\_NAME = PLSExtProc)  
(ORACLE\_HOME = D:/oracle)  
(PROGRAM = extproc)  
)  
(SID\_DESC =  
(GLOBAL\_DBNAME = ora816)  
(ORACLE\_HOME = D:/oracle)  
(SID\_NAME = ora816)  
)  
)  
  
Windows NT/2000字符方式登录步骤：  
1)先启动服务器端监听器：  
lsnrctl start  
  
2)用下面命令显式登录到 SQL\*PLUS：  
$sqlplus username/password  
或  
$SQLPLUS username/password@connect\_string  
  
Windows NT/2000图形方式登录步骤：  
1)点击“开始”=》“程序”=》“Oracle –OraHome81”=》“Application on Development”=》“SQL Plus”进入如下屏幕：  
  
在WINDOWS NT/98/2000上的登录界面(sqlplus图)  
  
可以在用户名框内输入用户、口令及主机字串；也可以分别进行输入。  
  
§1.5 常用SQL\*PLUS 附加命令简介  
Oracle公司提供的附加语句（或称命令），可以满足程序人员和管理员的一些特殊操作要求。比如，在显示超过上百行记录信息时，可以采用每屏“暂停”来实现。要达到这样的目的，就要在SQL>下发 set pause on 命令。由于SQL\*PLUS命令较多，下面仅给出最常用的几个命令的说明，详细的请参考附录。  
§1.5.1 登录到SQL\*PLUS  
  
可以用下面命令登录到 SQL\*PLUS，SQL\*PLUS命令的简单语法如下：  
  
SQLPLUS \[ \[logon\] | \[start\] \]  
  
logon可以是：  
\{username\[/password\]\[@connect\_identifier\]|/\} \[AS \{SYSOPER|SYSDBA\}\]  
|/NOLOG  
  
注1：SQLPLUS 主要是在命令方式下使用，在NT、WINDOWS/2000、UNIX的用法都一样。  
注2：如果在UNIX下，SQLPLUS命令不被识别（不存在），则问题在环境变量PATH没有设置正确或者没有设置。SQLPLUS 可执行程序在$ORACLE\_HOME/bin 目录下。  
  
§1.5.2 EXIT和QUIT  
  
可以用 exit 或quit 来终止SQL\*PLUS的操作（会话）。语法如下：  
  
\{EXIT|QUIT\} \[SUCCESS|FAILURE|WARNING \]  
  
\{EXIT|QUIT\} 可以用exit 或quit ，目前它们的功能一样。  
  
SUCCESS 正常退出  
  
FAILURE 带提示错误代码的退出  
  
WARNING 带提示警告代码的退出  
  
COMMIT 退出前将未提交进行保存  
  
例如：  
SQL>exit  
  
§1.5.3 DESCRIBE(显示表、视图结构)  
DESCRIBE可以用(只要用DESC即可)来显示表、视图的列的定义，也可以显示同义词、函数或存储过程的说明。语法如下：  
  
DESC\[RIBE\] \{\[schema.\]object\[@connect\_identifier\]\}  
  
Schema:用户名,如果省去，则为对象的所有者。  
  
object  
可以是 表（table）, 视图（view）,类型（ type）, 存储过程（procedure）,函数（ function）, 包（package）或同义词（ synonym）  
  
@connect\_identifier  
数据库连接字串  
  
例：显示emp 表的结构：  
  
SQL>desc emp  
  
§1.5.4 LIST(列出)命令  
可以用 LIST 命令来列出当前SQL缓冲区中的一行或多行命令语句。  
  
L\[IST\] \[n|n m|n \*|n LAST|\*|\* n|\* LAST|LAST\]  
  
n 列出第n行  
n m 列出n到m行  
n \* 列出第n行到当前行  
n LAST 列出第n行到最末行  
\* 列出所有行  
\* n 列出当前行到第n行  
\* LAST列出当前行到最末行  
LAST 列出最末行  
  
例：  
SQL> LIST  
1 SELECT ENAME, DEPTNO, JOB  
2 FROM EMP  
3 WHERE JOB = ’CLERK’  
4\* ORDER BY DEPTNO  
  
§1.5.5 Change(替换字符串)命令  
  
可以用Change命令来改变字符串（即替换字符串）。语法如下：  
  
C\[HANGE\] sepchar old \[sepchar \[new \[sepchar\]\]\]  
  
Sepchar 为分隔符，可以是”/” 或”!” -- 请使用者特别注意  
  
Old 旧字串  
  
New 新字串  
  
例：将 除号(/)改为 乘号（ \* ），则需要命令为c !/!\*! 。即：  
SQL> l  
1\* select sal,sal/100 from emp  
SQL> c !/!\*!  
  
提醒：对于修改 / 字符的只能用 ! 来作分隔符(上例)。  
  
例：将乘号（ \* ）改为 加号（ + ），则需要命令为c !/!\*! 。即：  
SQL> l  
1\* select sal,sal\*100 from emp  
SQL> c/\*/+/  
1\* select sal,sal+100 from emp  
SQL>  
  
§1.5.6 Append（追加字符串）命令  
  
可以用 Append命令来完成在当前行的末尾追加字符串。语法如下：  
  
A\[PPEND\] text  
  
Text 所要求追加的字符串  
  
例：在当前行select sal,sal+100 from emp 后加 where sal>=2000,则：  
  
SQL> l  
1\* select sal,sal+100 from emp  
SQL> a where sal>=2000  
1\* select sal,sal+100 from emp where sal>=2000  
SQL>  
  
§1.5.7 Save保存当前缓冲区命令到文件  
  
可以用SAVE命令将当前的命令行保存到操作系统的文件中。语法如下：  
  
SAV\[E\] filename\[.ext\] \[CRE\[ATE\]|REP\[LACE\]|APP\[END\]\]  
  
其中：  
filename：你将把缓冲区中的内容存入到操作系统目录的文件名。  
ext：若使用文件后缀，缺省的文件后缀为SQL。  
  
例：  
SQL>select table\_name from dict where table\_name like ‘%ROLE%’;  
SQL>save c:/get\_role  
  
§1.5.8 GET将命令文件读到缓冲区  
可以用GET 命令将操作系统的目录下的命令文件读到缓冲区（但不执行）。语法如下：  
  
GET filename \[.ext\] \[LIS\[T\]|NOL\[IST\]\]  
其中：  
filename: 希望加载到SQL缓冲区的文件名  
ext: 文件的扩展名，缺省为 SQL.  
  
例：  
  
SQL>get c:/get\_role  
  
§1.5.9 SPOOL将信息记录到文件中  
Oracle的SPOOL 命令可以实现将屏幕所出现的一切信息记录到操作系统的文件中直到SPOOL OFF为止。语法如下：  
  
SPO\[OL\] \[filename\[.ext\] | OFF | OUT\]  
其中：  
filename：你想输出（spool）的文件名。  
ext：文件的后缀。缺省的后缀是LST（或LIS）。  
  
SQL>col table\_name for a20  
SQL>col comments for a80  
SQL>set linesize 110  
SQL>SPOOl c:/all\_dict  
SQL>select table\_name,comments from dict;  
. . . . . . (系统查询信息)  
  
SQL>SPOOL OFF  
  
§1.5.10 再运行当前缓冲区的命令  
在SQL>方式下，如果希望在运行当前的命令，可用Run(或R)或用 / 来实现，如：  
  
SQL> set lin 120  
SQL> select table\_name from dict where table\_name like '%ROLE%';  
  
TABLE\_NAME  
\------------------------------  
DBA\_ROLES  
DBA\_ROLE\_PRIVS  
USER\_ROLE\_PRIVS  
ROLE\_ROLE\_PRIVS  
ROLE\_SYS\_PRIVS  
ROLE\_TAB\_PRIVS  
SESSION\_ROLES  
  
已选择7行。  
  
SQL> l  
1\* select table\_name from dict where table\_name like '%ROLE%'  
SQL> /  
  
TABLE\_NAME  
\------------------------------  
DBA\_ROLES  
DBA\_ROLE\_PRIVS  
USER\_ROLE\_PRIVS  
ROLE\_ROLE\_PRIVS  
ROLE\_SYS\_PRIVS  
ROLE\_TAB\_PRIVS  
SESSION\_ROLES  
  
已选择7行。  
  
§1.6 常用数据字典简介  
ORACLE 提供许多内部数据字典, 用以管理系统的各种信息和参数（即数据库对象），下面列出几个常用的数据字典供初学者参考，其它方面的数据字典将在DBA管理中介绍。  
  
ORACLE 数据字典的命名说明：  
  
USER 为前缀----记录用户的所有对象信息  
ALL 为前缀----记录包括 USER 记录和授权给PUBLIC 或该用  
户的所有对象的信息。  
DBA 为前缀----记录关于数据库对象（非用户对象）的信息。  
V$ 公共系统动态视图,用于系统优化和调整参考.  
V\_$ 动态性能视图，你可用 CATALOG.SQL 脚本建立动态视图建立同义词。  
GV$ 新(oracle 8)的附加的固定视图(Global V$).在并行环境下反应的是  
V$视图的信息。如：  
  
SELECT \* FROM GV$LOCK WHERE INST\_ID = 2 OR INST\_ID = 5 ;  
  
返回的是instances 2 和5的V$的信息。所以GV$反应一组Instances的参数. GV$视图的限制是参数PARALLEL\_MAX\_SERVERS必须大于0 。  
详见 Oracle Enterprise Manager Administrator's Guide.  
  
注：请注意下面的总结：  
l 一般DBA\_ 的视图内容都包含USER\_和ALL\_为前缀的视图；  
l DBA\_为前缀的视图的内容基本上是大写；  
l 以V$\_为前缀的视图的内容基本上是小写。  
  
1. USER\_TABLEs(=TABS) 用户的所有表的信息。  
2．USER\_TAB\_COLUMNS（=COLS） 有关各表的列（字段）的信息  
3．USER\_VIEWS 用户的所有视图  
4．USER\_SYNONYMS（=SYN） 用户同义词  
5．USER\_SEQUENCES(=SEQ) 用户序列  
6．USER\_CONSTRAINTS 记录创建表结构时建立的限制。  
7．USER\_TAB\_COMMENTS 表的注释。如：  
  
Comment on table emp is '职工表';  
  
8． USER\_COL\_COMMENTS 列（字段）注释。如：  
  
Comment on column emp.ename is '姓名';  
  
9． USER\_INDEXES(=IND) 用户索引的简要信息  
10． USER\_IND\_COLUMNS 用户索引的列信息  
11． USER\_TRIGGERS 用户触发器信息  
12． USER\_SOURCE 用户存储过程  
13． USER\_TABLESPACE 用户可以使用的表空间信息  
14． USER\_TS\_QUOTAS 用户使用系统资源的信息  
15． USER\_SEGMENTS 用户对象所使用空间信息  
16． USER\_EXTENTS 用户的扩展段信息  
17． USER\_OBJECTS 用户对象  
=USER\_TABLES+USER\_VIEWS+USER\_INDEXES+  
USER\_SOURCE+USER\_TRIGGERS+USER\_JAVA  
  
18． USER\_PART\_TABLES 用户分区信息  
19． USER\_TAB\_PARTITIONS  
20． USER\_PART\_COL\_STATISTICS  
21． USER\_IND\_PARTITIONS  
22． USER\_FREE\_SPACE  
23． CAT(=USER\_CATALOG) 用户可以访问的所有的基表。  
24． TAB 用户创建的所有基表，视图，同义词等。  
25． DICT（=DICTIONARY） 构成数据字典的所有表的信息。  
  
提示：虽然Oracle提供可以用Comment on column tablename.column is 'xxxx'; 等来实现对表或列进行注释，但不建议设计者采用这样的工作方式。而建议将注释写到脚本中更为直观。  
  
§1.7 ORACLE数据类型  
Oracle数据库的数据类型与其它的数据库系统相比，它的数据类型不多，Oracle在表示数据方面比其他数据库系统来说要省去许多关键字。 Oracle只用NUMBER(m,n)就可以表示任何复杂的数字数据。其它如日期类型等也简单得多，只DATE就表示日期和时间。下面以列表形式给出各个版本的Oracle系统数据类型的表示方法。下面给出Oracle旧版本的数据类型的目的是让读者了解Oracle的变化，另外就是你在对旧版本进行升级或数据转换时要注意各个版本的差别。  
  
ORACLE5、ORACLE6数据类型  
  
数据类型 说明  
Char 可变长字符型，≤254  
Varchar2 可变长字符型，≤2000  
Number(m,n) 数字类型，含整数、小数等  
Date 日期型，含时间，缺省格式为mmm-dd-yyyy hh:mi:ss（占7字节）  
Long 存储大型可变长字符串，≤2GB  
Raw 存储短二进制串，≤2GB  
Long raw 存储长二进制串，≤2GB  
  
ORACLE7数据类型  
  
数据类型 说明  
Char 定长字符，≤255个字符  
Varchar 变长字符，≤2000个字符  
Varchar2 变长字符，≤2000个字符  
Number(m,n) 数字类型，含整数、浮点、双精度等  
Long 存储大型可变长字符串，≤2GB  
Raw 存储可变短二进制数，≤2000  
Long raw 存储大型可变长二进制数，≤2GB  
  
ORACLE8/8i 数据类型  
  
数据类型 说明  
Char 定长字符，≤2000个字符  
Varchar （同Varchar2）可变字符，≤4000个字符  
Varchar2 变长字符，≤4000个字符  
Date 固定长度（7字节）的日期型  
Number 数字型，可存放实型和整型  
Long 可变字符，≤2GB个字符  
Raw 可变二进制数据，≤4000字节  
Long raw 可变二进制数据，≤2GB  
MLSLABEL 仅Trusted Oracle 用长度在2~5字节间  
Blob 大二进制对象，≤4GB  
Clob 大字符串对象，≤4GB  
Nclob 多字节字符集的Clob，≤4GB  
Bfile 外部二进制文件，大小由OS决定  
  
CHAR()  
定长字符型（在Oracle5 、Oracle6 是变长），字符长度不够自动在右边加空格符号。当字符长度超出2000个则错误。不指定大小缺省为 1。  
  
VARCHAR()  
可变字符型，当前与VARCHAR2()相同。  
  
VARCHAR2()  
可变字符型，当前与VARCHAR()相同。VARCHAR2 类型的字段（列）可存放4000个字符；但是VARCHAR2 变量可以存放32,767 个字符。大小必须指定。  
  
NCHAR()和NVARCHAR2()  
NCHAR 和NVARCHAR2 分别与 CHAR和VARCHAR2 有相同的大小。并用于于存放 National Language Support (NLS)数据，Oracle 允许以本地语言存放数据和查询数据。  
如果将列名声明成NCHAR、NVARCHAR2这样的类型，则insert和 select 等语句中的具体值前加N，不能直接按照普通字符类型进行操作。看下面例子：  
  
SQL> create table nchar\_tst(name nchar(6),addr nvarchar2(16),sal number(9,2));  
  
表已创建。  
  
SQL> insert into nchar\_tst values(N'赵元杰',N'北京市海淀区',9999.99);  
  
已创建 1 行。  
  
SQL> select \* from nchar\_tst where name like N'赵%';  
  
NAME ADDR SAL  
\------ ---------------- ----------  
赵元杰 北京市海淀区 9999.99  
  
SQL> select \* from nchar\_tst where name like '赵%';  
select \* from nchar\_tst where name like '赵%'  
\*  
ERROR 位于第 1 行:  
ORA-12704: 字符集不匹配.  
  
提示：虽然Oracle可以使用nchar, nvarchar2 类型来存放字符数据，但建议设计者不要使用NCHAR和NVARCHAR2。因为CHAR和VARCHAR2就能存放汉字。  
  
NUMBER(  
,)  
  
是数据的整数部分，是数据的精度（即小数）部分,注意，部分可以表示负的精度。用可以表示从小数点往右或往左保留多少位。如下表：  
  
实际值 数据类型 存储值  
1234567.89 Number 1234567.89  
1234567.89 Number(8) 1234568  
1234567.89 Number(6) 出错  
1234567.89 Number(9,1) 1234567.9  
1234567.89 Number(9,3) 出错  
1234567.89 Number(7,2) 出错  
1234567.89 Number(5,-2) 1234600  
1234511.89 Number(5,-2) 1234500  
1234567.89 Number(5,-4) 1230000  
1234567.89 Number(\*,1) 1234567.9  
  
Sal number(7,2), --表示5位整数，2位小数.  
  
DATE  
Oracle 的日期型用7个字节表示，每个日期型包含如下内容：  
l Century (世纪)  
l Year （年）  
l Month（月）  
l Day （天）  
l Hour （小时）  
l Minute （分）  
l Second （秒）  
  
日期型字段有下面特点：  
  
l 日期型字段的插入和更新可以数据型或字符并带to\_date 函数说明即可。  
l 缺省的日期格式有NLS\_DATE\_FORMAT参数控制，它的缺省格式为DD-MON-YY。  
l 缺省的时间是夜里 00:00:00 (即0点 0分0秒)。  
l sysdate返回的是服务器的时间，见下面例子。  
l 日期格式的显示可以设置，见下面例子。  
l 日期型可以运算，见下面例子。见下面例子。  
l 世纪用cc 表示；年用yyyy表示，月用mm表示，日用dd表示，小时用hh24表示，分用mi表示，秒用ss表示。  
  
例子：  
SQL> create table save\_info(per\_id varchar2(20),name varchar2(20),tran\_date date,  
2 tran\_val number(12,2));  
  
表已创建。  
  
SQL> insert into save\_info values ( '110105540609811','赵元杰',  
2 to\_date('2001.06.18','yyyy.mm.dd'),12345.66);  
  
已创建 1 行。  
  
SQL> select \* from save\_info;  
  
PER\_ID NAME TRAN\_DATE TRAN\_VAL  
\-------------------- -------------------- ---------- ----------  
110105540609811 赵元杰 18-6月 -01 1234.66  
  
SQL> select per\_id,name,to\_char(tran\_date,'yyyy/mm/dd'),tran\_val from save\_info;  
  
PER\_ID NAME TO\_CHAR(TR TRAN\_VAL  
\-------------------- -------------------- ---------- ----------  
110105540609811 赵元杰 2001/06/18 12345.66  
  
SQL> show parameter nls\_date\_format  
  
NAME TYPE VALUE  
\------------------------------------ ------- ------------------------------  
nls\_date\_format string  
  
SQL> alter session set nls\_date\_format=  
2 '"公元"yyyy"年"mm"月"dd"日"';  
  
会话已更改。  
  
SQL> select sysdate from dual;  
  
SYSDATE  
\------------------  
公元2001年05月18日  
  
SQL> select to\_char(sysdate,'cc yyyy.mm.dd') from dual;  
  
TO\_CHAR(SYSDA  
\-------------  
21 2001.05.18  
  
关于日期型的使用方法详细请参考《Oracle8i National Language Support Guide》。  
  
BLOB  
大二进制对象，每条记录可存储达4GB的数据，详细见后面章节。  
  
CLOB  
大字符对象，每条记录可存储达4GB的数据，详细见后面章节。  
  
BFILE  
外部二进制文件，每条记录可存储达4GB的数据（与OS有关），详细见后面章节。  
  
RAW  
非结构的二进制数据，这些数据不被数据库系统解释。RAW可以存储达2,000字节。  
  
LONGRAW  
大的二进制类型数据，LONGRAW是非结构的二进制数据，这些数据不被数据库系统解释。LONGRAW可以存储达2GB字节。LONGRAW不能被索引，而RAW可以被索引。  
  
ROWID  
ROWID在Oracle数据库中是一个虚的列，即系统用的特殊的列，不是我们建立的列。用于对数据库中的每条记录进行定位。详细见“Rowid的使用”章节。  
  
UROWID  
UROWID 是Universal ROWID 的意思。即全球ROWID，它支持逻辑和物理ROWID，也作为外部表的（通过getway 访问的非Oracle 表）的ROWID。UROWID类型可以存储所有的ROWID类型的数据。  
  
%TYPE类型的匹配：  
books\_printed number(6)；  
books\_sold books\_printed%TYPE；  
books\_sold的数据类型与book\_printed的类型一致；  
( %TYPE类型 在 PL/SQL中介绍 )。  
  
空值与字符型、数字型的运算：  
null + <数字>=null （空值+数字仍是空值）  
null > <数字>=null （空值与数字比较结果为空值）  
null || '字符串' = 字符串  
  
number类型与以下类型具有同等的值域：  
DEC  
Decimal  
Double PREcision  
Float  
Integer  
Int  
Numeric  
Real  
Smallint  
  
提示：虽然Oracle可以使用上面的子数据类型，但建议还是采用NUMBER(n,m)为好。因为如果使用子数据类型定义字段类型不当，可能引起数据在运算方面的问题。  
  
Long数据类型的限制：  
l select中可以用long；  
l update中可以用select子句；  
l insert中可以用Valus子句；  
l 每个表只能允许一个long列；  
l long列不能列出完整性约束（null、not null除外）；  
l long列不能被索引；  
l 过程或存储函数不能接收long型函数；  
l 存储函数不能返回long型值。  
  
long 目前不能出现在以下情况中：  
l select中的Where，Group by，order by，Connect by，distinct；  
l 不能对long列作substr，instr；  
l 表达式或条件；  
l 子查询或集合中不能用long；  
l Create table ...as select 中不能用long；  
  
§1.8 SQL 语句基础  
下面给出SQL语句的基本介绍，更详细的描述见后面的章节。  
§1.8.1 SQL语句所用符号  
  
操作符 用途 例子  
\+ - 表示正数或负数，正数可省去 + -1234.56  
\+ 将两个数或表达式进行相加 A=c+b  
\- 将两个数或表达式进行相减 34-12  
\* 将两个数或表达式进行相乘 12\*34  
/ 除以一个数或表达式 18\*11  
NULL 空值判断 Where name is null;  
|| 字符串连接 ‘101-’||tel\_num  
= 等于测试 Select \* from emp where name=’赵元杰’;  
!= 或<>或^= 不等于测试 Select \* from emp where name !=’赵元杰’;  
< 小于测试 Select \* from emp Where sal < 5000;  
> 大于测试 Select \* from emp Where sal > 5000;  
<= 小于等于测试 Select \* from emp Where sal <= 5000;  
>= 大于等于测试 Select \* from emp Where sal >= 5000;  
Not in 测试某值是否在一个指定的结果集中 Select name,addr from expertwhere local not in(‘北京’,’上海’);  
ANY 将一个值与一组值进行比较，返回满足条件的结果。必须跟!=,<,>,<=,>= select ename,sal from emp where sal<= any(select sal from emp where deptno=10)  
SOME 同ANY，必须跟!=,<,>,<=,>=  
ALL 将一个值与一组值比较，返回满足条件的所有列值。必须跟!=,<,>,<=,>= Select name,sal from empWhere sal<= all ( 500,800,1200);  
Not betweenA and B 判断某个值是否界于两者之间。 Select name,sal from emp Where sal between 500 and 1200;  
\[not\]exists 判断某个列是否存在于一组值中。 select dname,deptno from dept where exists(select \* from emp where dept.deptno=emp.deptno)  
A\[not\]like b\[Escape ‘char’\] 比较两个模式是否相似，当使用like 语句时Oracle不去访问索引。 Select \* from empWhere ename like ‘TH%’;  
Is \[not\] null 测试值是否为空。 Select ename,deptno from empWhere comm. Is null or comm.=0;  
Not 对结果的否定。 Select \* from empWhere sal not(sal<1000); 等价于select ename,sal from emp where sal>=1000;  
AND 用于判断两个条件十分都满足。 Select \* from emp where Ename=’SIMTH’ and sal>=1000;  
OR 用于判断两个条件中是否有一个满足。 Select \* from emp where Ename=’SIMTH’ or ename=’SCOTT’;  
UNION 用于返回（组合）两个查询中所有唯一的行。 Select ename from emp unionSelect ename from emp;  
UNION ALL 用于返回（组合）两个查询中所有所有的行。  
INTERSECT 用于返回两个查询中相同的行。 Select ename from emp1 intersect select ename from emp2;  
MINUS 用于返回两个查询中的不同的行。  
  
§1.8.2 简单select 查询  
当我们可以用SQL\*PLUS登录到SQL>下后，我们可以用DESC 显示某表的结构，也可以用select 语句简单查询表中的一些列的内容。  
  
例：要查询EMP表中员工的姓名、工资及出生日期，则：  
  
SQL>select ename, sal, hiredate from emp;  
  
§1.9 伪列及伪表  
Oracle系统为了实现完整的关系数据库功能，系统专门提供了一组称为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象（如建表）时由我们完成的，而是在我们建立对象时由自动Oracle完成的。Oracle目前有以下的伪列：  
  
l CURRVAL and NEXTVAL 使用序列号的保留字  
l LEVEL 查询数据所对应的级  
l ROWID 记录的唯一标识  
l ROWNUM 限制查询结果集的数量  
  
有关伪列的详细解释和使用见相关章节。  
  
Oracle 还提供了一个DUAL 的伪表，该表主要目的是保证在使用SELECT 语句中语句的完整性而提供的，如：我们要查询当前的系统日期及时间，而系统的日期和时间并是放在一个指定的表里。所以在 from 语句后就没有表名给出。为了使用 from 后有个表名，我们就用DUAL代替。如：  
  
例1：查询Oracle系统日期及时间：  
SQL> select to\_char( sysdate,'yyyy.mm.dd hh24:mi:ss') from DUAL;  
  
TO\_CHAR(SYSDATE,'YY  
\-------------------  
2001.06.02 07:28:09  
  
例2：计算一下 5000+5000\*0.1 的结果是多少，则：  
SQL> select 5000+5000\*0.1 from DUAL;  
  
5000+5000\*0.1  
\-------------------  
5500

[http_admin]: http://admin/