版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
第一章Oracle数据库基础
Oracle的SQL*PLUS是设计所有应用系统的基础工具。要想将应用系统设计成一个健
壮的、性能优越的系统。最关键的是要理解RDBMS的真正含义和结构,理解Oracle
SQL*PLUS的特点和核心,弄清关系数据库与桌面数据库的差别。比如理解数据的完整性、
一致性、索引、视图等。只有这样才能设计出符合Oracle特点的应用系统。从而保证系统
在
提供使用后不会出现一致性、性能等问题。
§1.1理解关系数据库系统(RDBMS)
CODASYL(数据系统语言协会)是数据库任务组(DatabaseTaskGroup,DBTG)创建的
一种数据库标准,这是种基于COBOL的网络数据库标准。
§1.1.1关系模型
一个描述两个集合的元素如何相互联系或如何一一对应的数学概念,对于数据库来说,
关系只是一个带有一些特殊属性的表。所以有:
数据的基础项是关系
在这些表上的操作只产生关系
一个关系表必须符合某些特定条件,才能成为关系模型的一部分
储存在单元中的数据必须是原子的。每个单元只能存储一条数据,叫信息原则
(Information
Principle)o如果存储多条则违反信息原则。特舒情况下可能需要违反信息原则。
储存在列下的数据必须具有相同的数据类型。
每一行是唯一的(没有完全相同的行)。
列没有顺序。
行没有顺序
列有一个唯一性的名称。
关系模型的另一个是完整性原则。它包括实体完整性原则(Entityintegrityrule)和引用
完整性原则(Referentialintegrityrule),如:
主键(Primarykey)是能唯一标识行的一列或一组列的集合。
由多个列构成的主键称为连接键(Concatenatedkey)、组合键(Compoundkey),或称
作
为复合键(Compositykey)。
另外就是外部键(Foreignkey)是一个表中的一列或一组列,它们在其它表中作为主键而
存
在。一个表中的外部键被认为是对另外一个表中主键的引用。实体完整性原则简洁地表明主
键不能全部或部分地空缺或为空,引用完整性原则简洁地表明一个外键必须为空或者它所引
数据库培训教材
培训ORACLE请联系/p>
16
用的主键当前存在值相一致。
§1.1.2Codd十二法则
Oracle数据库系统是一个完美的完全符合数据库技术的关系数据库系统。要想你的应用
设计按照数据库原理来进行,最重要的就是理解Oracle的结构、语句和命令。Codd提出的
十二条法则在Oracle系统中都可以找到:
1)信息法则。
2)授权存储法则,每一个数据项都通过“表名+行主键+列名”的组合形成访问。
3)必须以一致的方法使用空值。
4)一个活跃的、在线数据字典应作为关系型表被储存
5)必须提供数据存取语言进行存取访问。
6)所有能被更新的视图应当是可被更新的。
7)必须有集合级的插入、更新和删除。
8)物理数据的独立性。即应用不依赖物理结构。
9)逻辑数据的独立性。如果一个表被分成两个部分,则应用视图连接在一起,以便不会对应
用产生影响。
10)完整性的独立性。完整性规则应该储存在数据字典中。
11)分布独立性。一个数据库即使被分布,也应该能工作。
12)非破坏性原则。如果允许低级存取,一定不能绕过安全性和完整性原则。
§1.2关系数据库系统(RDBMS)的组成
RDBMS由两部分组成,即数据库系统内核(软件)和数据字典(内核用于管理数据库系
统的数据结构)两部分。
§1.2.1RDBMS内核
RDBMS就是用来控制数据访问的操作系统。它的任务是按照一定的规则存储数据、检
索数据及保护数据。
§1.2.2数据字典概念
数据自动存放数据库中所有对象(如表,索引,视图等)所需的信息。Oracle8i的数
据字典是存放数据库系统信息的一组表,从数据字典中的信息可以确认数据库中数据对象的
基本信息及存放位置。
数据库培训教材
培训ORACLE请联系/p>
17
§1.3SQL、SQL*Plus及PL/SQL
下面用简单的语言解释Oracle的常用产品所包含的SQL*PLUS和PL/SQL的关系。
§1.3.1SQL和SQL*PLUS的差别
SQL是标准结构查询语言,而SQL*PLUS实际是包括标准的SQL和Oracle公司的一些命令组
成的产品,因而Oracle公司将其取名为SQL*PLUS。下面是它们的解释。
1.SQL(StructuredQueryLanguage)
SQL有许多关键字,以下语句是常用于开头的语句:
AlterInsert
AuditLock
CommitNoaudit
CommentRename
CreateRevoke
DeleteSelect
DropUpdate
GrantValidate
注:
1.Oracle的SQL缓冲区一次只能存放•条SQL命令;
2.Validate(使生效)在oracle中跟Enable一起用,但可以省去Validate,所以许
多资
料都不介绍Validate的用法。其语法如:
Enable{[Validate][Novalidate]}{[UNIQUE][PRIMARYKEY]...}...
2.SQL*PLUS
除SQL外,SQL*PLUS还包括称为SQL*PLUS命令的附加命令,这些命令主要用于形成复杂
报表,编辑SQL命令,提供帮助信息,维护系统等。SQL*PLUS包括的命令如下:
@Connect
HostSet
#Copy
InputShow
$Define
ListSpool
/Del
NewpageSqlplus
AcceptDescribe
PauseStart
AppendDisconnect
数据库培训教材
培训ORACLE请联系/p>
18
QuitTiming
BreakDocument
RemarkTtitle
BtitleEdit
PromptUndefine
ChangExecute
PrintSave
ClearExit
RunColumn
GetRunform
ComputeHelp
3.DDL(DataDefineLanguage)
对于结构查询语言(有时称SQL命令),可以将它们分成两组,一组是数据定义语言(DDL);
另一组是数据操纵语言(DML)。其中用于数据定义的语言如下:
Alterprocedure重编译过程
Altertable修改表的属性
Analyze统计数据库对象性能值
AltertableaddConstraint对已有的表加约束
Createtable建立表结构
Createindex建立索引
Droptable删除表实体及相关的索引
Dropindex删除索引
Grant授权给用户或角色
Truncate删除表中的所有行
Revoke从用户或角色收回权限
4.DML(DataManipulationLanguage)
对于结构查询语言的另一组是数据操纵语言(DML)。DML其中用于数据操纵的命令如下:
Insert
Delete
Update
Select
Commitwork
Rollback
数据库培训教材
培训ORACLE请联系/p>
19
§1.3.2PL/SQL语言
PL/SQL是OracleRDBMS(Oracle6之后版本)的一个组成部分,PL是“过程化语言
(ProcedureLanguage)”的缩写。PUSQL语言是在SQL语言中结合了结构化过程语言成
分
的准第四代语言。
使用PL/SQL的目的:
由于大多数PUSQL是在服务端来运行,这样可减少由客户端运行程序时所需的网络数
据流量。
可以使用PL/SQL的地方:
PL/SQL可以单独进行程序的编写,完成一般的处理功能;
在高级语言中可嵌入PL/SQL块;
在4GL中可以嵌入PL/SQL块;
在PL/SQL程序中可以嵌入HTML利XML。
§1.4登录至USQL*PLUS
我们创建任何对象,如创建表、索引等都需要连接到Oracle中,这里用“登录”主要是
Oracle的界面提供的是Login这样的叫法。其实就是连接的意思。在Client/Server结构
下,
Oracle提供两种方式连接SQL*PLUS,其中SQL*NETV2.x版本(目前版本不再使用SQL*NET
V2.x)提供在字符方式下连接到SQL*PLUS,SQL*NETV2.x版本提供在图形方式(Window)
使用,目前版本的0RACLE8/8i都使用NET8连接.(NET8不再支持字符终端)
§1.4.1UNIX环境
在UNIX下,要确保客户端或服务器端与Oracle服务器系统进行连接,必须保证
tnsnames.ora和listener,ora两个参数文件的正确配置。详细的配置解释在DBA章节里解
释。下面是tnsnames.ora和listener,ora两个参数文件的内容显示。
1.tnsnames.ora参数文件:
INST1_HTTP=
(DESCRIPTION=
(Al)DRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsvr)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=SHARED)
(SERVICE_NAME=s450)
(PRESENTATION=http://admin)
)
数据库培训教材
培训ORACLE请联系/p>
20
)
EXTPROC^CONNECT10N_DATA=
(DESCRIPTION=
(ADDRESSLIST=
(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=
(DESCRIPTIONLIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsvr)(PORT=1521))
)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))
)
)
)
SIDJJST_LISTENER=
(SID_LIST=
(SID_DESC=
(SIDNAME=PLSExtProc)
(ORACLEHOME=/home/orac1e/app/orac1e/product/8.1.7)
(PROGRAM=extproc)
)
(SID^DESC=
数据库培训教材
培训ORACLE请联系/p>
21
(GLOBAL_DBNAME=s450)
(ORACLEHOME=/home/orac1e/app/orac1e/product/8.1.7)
(SID_NAME=s450)
)
)
如果安装后己经正确地对上面的两个参数文件进行配置,则可以按照下面步骤进行登录:
1)先启动服务器端监听器:
Isnrctlstart
2)用下面命令显式登录到SQL*PLUS:
$sqlplususername/password
或
$SQLPLUSusername/password@connect_string
3)用下面命令隐式登录到SQL*PLUS:
$sqlplus[enter]
Enterusername:scott
Enterpassword:******
§1.4.2WindowsNT和WINDOWS/2000环境
在NT/2000环境下,要使客户端能与Oracle服务器进行连接,tnsnames.ora和
listener.ora参数文件的配置如下:
1.tnsnames.ora参数文件:
0RA816.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))
)
数据库培训教材
培训ORACLE请联系/p>
22
(CONNECT_DATA=
(Sil)=PLSExtProc)
(PRESENTATION=RO)
)
)
S450=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsvr)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=s450)
)
)
2.listener,ora参数文件:
LISTENER=
(DESCRIPTION」IST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPR0C1))
)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=zhao)(PORT=1521))
)
)
(DESCRIPTION=
(PROTOCOL_STACK=
(PRESENTATION=GIOP)
(SESSION=RAW)
)
(ADDRESS=(PROTOCOL=TCP)(HOST=zhao)(PORT=2481))
)
)
S1D_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SIDNAME=PLSExtProc)
(ORACLE_HOME=D:\oracle)
(PROGRAM=extproc)
)
(SIDDESC=
数据库培训教材
培训ORACLE请联系/p>
23
(GLOBAL_DBNAME=ora816)
(ORACLEJOME=D:\oracle)
(SID_NAME=ora816)
)
)
WindowsNT/2000字符方式登录步骤:
1)先启动服务器端监听器:
Isnrctlstart
2)用下面命令显式登录到SQL*PLUS:
$sqlplususername/password
或
$SQLPLUSusername/password@connect_string
WindowsNT/2000图形方式登录步骤:
1)点击"开始"=》"程序"=》"Oracle-0raHome92"=》**ApplicationonDevelopment"
=》
“SQLPlus"进入如下屏幕:
在WINDOWSNT/98/2000上的登录界面(sqlplus图)
可以在用户名框内输入用户、口令及主机字串;也可以分别进行输入。
数据库培训教材
培训ORACLE请联系/p>
24
§1.5常用SQL*PLUS附加命令简介
Oracle公司提供的附加语句(或称命令),可以满足程序人员和管理员的一些特殊操作要
求。比如,在显示超过上百行记录信息时,可以采用每屏“暂停”来实现。要达到这样的目
的,就要在SQL>下发setpauseon命令。由于SQL*PLUS命令较多,下面仅给出最常用的
几个命令的说明,详细的请参考附录。
§1.5.1登录至USQL*PLUS
可以用下面命令登录到SQLTLUS,SQL*PLUS命令的简单语法如下:
SQLPLUS[[logon]|[start]]
/ogo”可以是:
{username[/password][@connect_identifier]\/}[AS{SYSOPER|SYSDBA)]
|/NOLOG
注7:SQU^US主要是在命令方式下使用,在NT、WINDOWS/2000.UN/X的用法都一
样。
注2:如果在U/V/X下,SQLPLUS命令不被识别(不存在),则问题在环境变量相丁H没
有设
置正确或者没有设置。SQLPLUS可执行程序在$。玦。_之HOME/加目录下。
§1.5.2EXIT和QUIT
可以用exit或quit来终止SQL*PLUS的操作(会话)。语法如下:
{EXITIQUIT}[SUCCESSIFAILURE|WARNING]
{EXIT|QUIT}可以用exit或quit,目前它们的功能一样。
SUCCESS正常退出
FAILURE带提示错误代码的退出
WARNING带提示警告代码的退出
COMMIT退出前将未提交进行保存
例如:
SQL>exit
数据库培训教材
培训ORACLE请联系/p>
25
§1.5.3DESCRIBE(显示表、视图结构)
DESCR旧E可以用(只要用DESC即可)来显示表、视图的列的定义,也可以显示同义词、函
数或存储过程的说明。语法如下:
DESCfRIBE]{{schema.]object[@connect_identifier]}
Schema;用户名,如果省去,则为对象的所有者。
object
可以是表(table),视图(view),类型(type),存储过程(procedure),函数(function),
包(package)或同义词(synonym)
@connect_identifier
数据库连接字串
例:显示emp表的结构:
SQL>descemp
§1.5.4LIST(列出)命令
可以用LIST命令来列出当前SQL缓冲区中的一行或多行命令语句。
L[IST][n\nmln*\nLASTI*I*nl*LASTILAST]
n列出第n行
nm列出n到m行
n*列出第n行到当前行
nLAST列出第n行到最末行
*列出所有行
*n列出当前行到第n行
*LAST列出当前行到最末行
LAST列出最末行
例:
SQL>LIST
1SELECTENAME,DEPTNO,JOB
数据库培训教材
培训ORACLE请联系/p>
26
2FROMEMP
3WHEREJOB=,CLERK,
4*ORDERBYDEPTNO
§1.5.5Change(替换字符串)命令
可以用Change命令来改变字符串(即替换字符串)。语法如下:
C[HANGEJsepcharold[sepchar[new[sepchar]]]
Sepchar为分隔符,可以是“/"或请使用者特别注意
Old旧字串
New新字串
例:将除号(/)改为乘号(*),则需要命令为c!/!*!。即:
SQL>1
1*selectsal,sal/100fromemp
SQL>c!/!*!
提醒:对于修改/字符的只能用!来作分隔符(上例)。
例:将乘号(*)改为加号(+),则需要命令为c!/!*!。即:
SQL>1
1*selectsal,sal*100fromemp
SQL>c/*/+/
1*selectsal,sal+100fromemp
SQL>
§1.5.6Append(追加字符串)命令
可以用Append命令来完成在当前行的末尾追加字符串。语法如下:
A[PPEND]text
Text所要求追加的字符串
数据库培训教材
培训ORACLE请联系/p>
27
例:在当前行selectsal,sal+100fromemp后加wheresal>=2000,则:
SQL>1
1*selectsal,sal+100fromemp
SQL>awheresal>=2000
1*selectsal,sal+100fromempwheresal>=2000
SQL>
§1.5.7Save保存当前缓冲区命令到文件
可以用SAVE命令将当前的命令行保存到操作系统的文件中。语法如下:
SAVLEJfilename[.extj[CRE[ATE]IREP[LACE]IAPP[END]]
其中:
filename:你将把缓冲区中的内容存入到操作系统目录的文件名。
ext:若使用文件后缀,缺省的文件后缀为SQL。
例:
SQL>selecttable_namefromdietwheretable_namelike'%ROLE%';
SQL>savec:\get_role
§1.5.8GET将命令文件读到缓冲区
可以用GET命令将操作系统的目录下的命令文件读到缓冲区(但不执行)。语法如下:
GETfilename[.extjILISLTJINOLIISTJJ
其中:
filename:希望加载到SQL缓冲区的文件名
ext:文件的扩展名,缺省为SQL.
例:
SQL>getc:\get_role
§1.5.9SPOOL将信息记录到文件中
Oracle的SPOOL命令可以实现将屏幕所出现的一切信息记录到操作系统的文件中直到
数据库培训教材
培训ORACLE请联系/p>
28
SPOOLOFF为止。语法如下:
SPO[OL][filename[.ext]IOFFIOUTJ
其中:
filename:你想输出(spool)的文件名。
ext:文件的后缀。缺省的后缀是LST(或LIS)。
SQL>coltable_namefora20
SQL>colcommentsfora80
SQL>setlinesize110
SQL>SP001c:\all_dict
SQL>selecttablename,commentsfromdiet;
(系统查询信息)
SQL>SPOOLOFF
§1.5.10再运行当前缓冲区的命令
在SQL>方式下,如果希望在运行当前的命令,可用Run(或R)或用/来实现,如:
SQL>setlin120
SQL>selecttablenamefromdietwheretablenamelike'%ROLE%';
TABLE_NAME
DBA_ROLES
DBAROLEPRIVS
USER_ROLE_PRIVS
ROLEROLEPRIVS
ROLE_SYS_PRIVS
ROLETABPRIVS
SESSION_ROLES
已选择7行。
SQL>1
1*selecttablenamefromdietwheretablenamelike'%ROLE%'
SQL>/
TABLENAME
DBAROLES
DBA_ROLE_PRIVS
数据库培训教材
培训ORACLE请联系/p>
29
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$新(oracle8)的附加的固定视图(GlobalV$).在并行环境下反应的是
V$视图的信息。如:
SELECT*FROMGV$LOCKWHEREINST_ID=2ORINST_ID=5;
返回的是instances2和5的丫$的信息。所以GV$反应一"组Instances的参数.GV$视图的
限制是参数PARALLEL_MAX_SERVERS必须大于0。
详见OracleEnterpriseManagerAdministrator'sGuide.
注:请注意下面的总结:
一般DBA_的视图内容都包含USER一和ALL一为前缀的视图;
DBA为前缀的视图的内容基本上是大写;
以V$_为前缀的视图的内容基本上是小写。
1.USER_TABLEs(=TABS)用户的所有表的信息。
2.USERTABCOLUMNS(=COLS)有关各表的列(字段)的信息
3.USER_VIEWS用户的所有视图
4.USERSYNONYMS(=SYN)用户同义词
5.USER_SEQUENCES(=SEQ)用户序列
6.USERCONSTRAINTS记录创建表结构时建立的限制。
7.USER_TAB_COMMENTS表的注释。如:
数据库培训教材
培训ORACLE请联系/p>
30
Commentontableempis'职工表’;
8.USER_COL_COMMENTS列(字段)注释。如:
Commentoncolumnemp.enameis'姓名';
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)构成数据字典的所有表的信息。
提示:虽然。mc/eCommentoncolumntablename.columnis'xxxx';等来实现对
表
或列进行注释,但不建议设计者采用这样的工作方式。而建议将注释写到脚本中更为直
观。
§1.7ORACLE数据类型
Oracle数据库的数据类型与其它的数据库系统相比,它的数据类型不多,Oracle在表示
数据方面比其他数据库系统来说要省去许多关键字。Oracle只用NUMBER(m,n)就可以表示
任何复杂的数字数据。其它如日期类型等也简单得多,只DATE就表示日期和时间。下面
以
列表形式给出各个版本的Oracle系统数据类型的表示方法。下面给出Oracle旧版本的数据
类
型的目的是让读者了解Oracle的变化,另外就是你在对旧版本进行升级或数据转换时要注
,息
各个版本的差别。
0RACLE5、ORACLE6数据类型
数据类型说明
数据库培训教材
培训ORACLE请联系/p>
31
Char可变长字符型,W254
Varchar2可变长字符型,W2000
Number(m,n)数字类型,含整数、小数等
Date日期型,含时间,缺省格式为mmm-dd-yyyyhh:mi:ss(占7字节)
Long存储大型可变长字符串,W2GB
Raw存储短二进制串,<2GB
Longraw存储长二进制串,W2GB
0RACLE7数据类型
数据类型说明
Char定长字符,<255个字符
Varchar变长字符,W2000个字符
Varchar2变长字符,W2000个字符
Number(m,n)数字类型,含整数、浮点、双精度等
Long存储大型可变长字符串,W2GB
Raw存储可变短二进制数,W2000
Longraw存储大型可变长二进制数,W2GB
0RACLE8/8i数据类型
数据类型说明
Char定长字符,W2000个字符
Varchar(同Varchar2)可变字符,<4000个字符
Varchar2变长字符,W4000个字符
Date固定长度(7字节)的日期型
Number数字型,可存放实型利整型
Long可变字符,W2GB个字符
Raw可变二进制数据,W4000字节
Longraw可变二进制数据,W2GB
MLSLABEL仅TrustedOracle用长度在2~5字节间
Blob大二进制对象,W4GB
Clob大字符串对象,W4GB
Nclob多字节字符集的Clob,W4GB
Bfile外部二进制文件,大小由OS决定
CHAR«size»
定长字符型(在0racle5、0racle6是变长),字符长度不够自动在右边加空格符号。当
字
符长度超出2000个则错误。不指定大小缺省为1。
VARCHAR«size»
可变字符型,当前与VARCHAR2(〈size>)相同。
VARCHAR2«SIZE»
数据库培训教材
培训ORACLE请联系/p>
32
可变字符型,当前与VARCHAR(〈size>)相同。VARCHAR2类型的字段(列)可存放4000个字
符;但是VARCHAR2变量可以存放32,767个字符。大小必须指定。
NCHAR«size»和NVARCHAR2«size»
NCHAR和NVARCHAR2分别与CHAR和VARCHAR2有相同的大小。并用于于存放National
LanguageSupport(NLS)数据,Oracle允许以本地语言存放数据和查询数据。
如果将列名声明成NCHAR、NVARCHAR2这样的类型,则insert和select等语句中的具体值
前加N,不能直接按照普通字符类型进行操作。看下面例子:
SQL>createtablenchar_tst(namenchar(6),addrnvarchar2(16),salnumber(9,2));
表已创建。
SQL>insertintonchar_tstvalues(N‘赵元杰',N'北京市海淀区',9999.99);
已创建1行。
SQL>select*fromnchar_tstwherenamelikeN'赵%';
NAMEADDRSAL
赵元杰北京市海淀区9999.99
SQL>select*fromnchar_tstwherenamelike'赵%';
select*fromnchar_tstwherenamelike'赵/'
*
ERROR位于第1行:
ORA-12704:字符集不匹配.
提示:虽然Oracle可以使用nchar,nvarchar2类型来存放字符数据,但建议设计者不
要使
用NCHAR和NVARCHAR2。因为CHAR和VARCHAR2就能存放汉字。
NUMBER«p>,<s»
<p>是数据的整数部分,〈s>是数据的精度(即小数)部分,注意,<s>部分可以表示负的精度。
用<S>可以表示从小数点往右或往左保留多少位。如下表:
实际值数据类型存储值
1234567.89Number1234567.89
1234567.89Number(8)1234568
1234567.89Number(6)出错
1234567.89Number(9,1)1234567.9
1234567.89Number(9,3)出错
1234567.89Number(7,2)出错
1234567.89Number(5,-2)1234600
数据库培训教材
培训ORACLE请联系/p>
33
1234511.89Number(5,-2)1234500
1234567.89Number(5,-4)1230000
1234567.89Number(*,1)1234567.9
Salnumber(7,2),一表示5位整数,2位小数.
DATE
Oracle的日期型用7个字节表示,每个日期型包含如下内容:
Century(世纪)
Year(年)
Month(月)
Day(天)
Hour(小时)
Minute(分)
Second(秒)
II期型字段有下面特点:
II期型字段的插入和更新可以数据型或字符并带to_date函数说明即可。
缺省的日期格式有NLS_DATE_FORMAT参数控制,它的缺省格式为DD-M0N-YY。
缺省的时间是夜里00:00:00(即0点0分0秒)。
sysdate返回的是服务器的时间,见下面例子。
日期格式的显示可以设置,见下面例子。
日期型可以运算,见下面例子。见下面例子。
世纪用cc表示;年用yyyy表示,月用mm表示,II用dd表示,小时用hh24表示,分
用
mi表示,秒用ss表示。
例子:
SQL>createtablesave_info(per_idvarchar2(20),namevarchar2(20),tran_datedate,
2tran_valnumber(12,2));
表已创建。
SQL>insertintosave_infovalues('110105540609811'赵元杰',
2to_dateC2001.06.18','yyyy.mm.dd"),12345.66):
已创建1行。
SQL>select*fromsaveinfo;
PERIDNAMETRANDATETRANVAL
110105540609811赵元杰18-6月-011234.66
数据库培训教材
培训ORACLE请联系/p>
34
SQL>selectper_id,name,to_char(tran_date,,yyyy/mm/dd,),tran_valfromsave_info;
PERIDNAMETOCHAR(TRTRAN_VAL
110105540609811赵元杰2001/06/1812345.66
SQL>showparameternls_date_format
NAMETYPEVALUE
n1s_date_formatstring
SQL>altersessionsetn1s_date_format=
2'〃公元〃yyyy〃年〃mm〃月〃dd〃||〃’;
会话已更改。
SQL>selectsysdatefromdual;
SYSDATE
公元2001年05月18日
SQL>selectto_char(sysdate,?ccyyyy.mm.dd')fromdual;
TO_CHAR(SYSDA
212001.05.18
关于日期型的使用方法详细请参考《0racle8iNationalLanguageSupportGuide》。
BLOB
大二进制对象,每条记录可存储达4GB的数据,详细见后面章节。
CLOB
大字符对象,每条记录可存储达4GB的数据,详细见后面章节。
BFILE
外部二进制文件,每条记录可存储达4GB的数据(与OS有关),详细见后面章节。
数据库培训教材
培训ORACLE请联系/p>
35
RAW
非结构的二进制数据,这些数据不被数据库系统解释。RAW可以存储达2,000字节。
LONGRAW
大的二进制类型数据,LONGRAW是非结构的二进制数据,这些数据不被数据库系统解释。
LONGRAW可以存储达2GB字节。LONGRAW不能被索引,而RAW可以被索引。
ROWID
ROWID在Oracle数据库中是一个虚的列,即系统用的特殊的列,不是我们建立的列。用于
对
数据库中的每条记录进行定位。详细见“Rowid的使用”章节。
UROWID
UR0WID是UniversalROWID的意思。即全球ROWID,它支持逻辑和物理ROWID,也作为外部
表的(通过getway访问的非Oracle表)的ROWID。UROWID类型可以存储所有的ROWID类型
的数据。
%TYPE类型的匹配:
books_printednumber(6);
bookssoldbooksprinted%TYPE;
books_sold的数据类型与book_printed的类型,•致;
(%TYPE类型在PL/SQL中介绍)。
空值与字符型、数字型的运算:
null+<数字>=null(空值+数字仍是空值)
null><数字>=null(空值与数字比较结果为空值)
null||'字符串‘二字符串
number类型与以下类型具有同等的值域:
DEC
Decimal
DoublePREcision
Float
Integer
Int
Numeric
Real
Smallint
提示:虽然Oracle可以使用上面的子数据类型,但建议还是采用NUMBER(n,m)为好。因
为如
果使用子数据类型定义字段类型不当,可能引起数据在运算方面的问题。
Long数据类型的限制:
数据库培训教材
培训ORACLE请联系/p>
36
select中可以用long;
update中可以用select子句;
insert中可以用Valus子句;
每个表只能允许一个long歹ij;
long列不能列出完整性约束(null、notnull除外);
long列不能被索引;
过程或存储函数不能接收long型函数;
存储函数不能返回long型值。
long目前不能出现在以下情况中:
select中的Where,Groupby,orderby,Connectby,distinct;
不能对long列作substr,instr;
发达式或条件:
子查询或集合中不能用long;
Createtable...asselect中不能用long;
§1.8SQL语句基础
下面给出SQL语句的基本介绍,更详细的描述见后面的章节。
§1.8.1SQL语句所用符号
操作符用途例子
+-表示正数或负数,正数可省去+-1234.56
+将两个数或表达式进行相加A=c+b
-将两个数或表达式进行相减34-12
*将两个数或表达式进行相乘12*34
/除以一个数或表达式18*11
NULL空值判断Wherenameisnull;
II字符串连接“01-1ltel_num
=等于测试Select*fromemp
wherename='赵元杰';
!=或o或八=不等于测试Select*fromemp
wherename!=’赵元杰';
<小于测试Select*fromemp
Wheresal<5000;
>大于测试Select*fromemp
Wheresal>5000;
<=小于等于测试Select*fromemp
Wheresal<=5000;
>=大于等于测试Select*fromemp
Wheresal>=5000;
数据库培训教材
培训ORACLE请联系/p>
37
Notin测试某值是否在一个指定的结果集中Selectname,addrfromexpert
wherelocalnotin,北京,,'上
海');
ANY将一个值与一组值进行比较,返回满足条
件的结果。必须跟!=,〈>,<=,>=
selectename,salfromemp
wheresal<=any(selectsalfrom
empwheredeptno=10)
SOME同ANY,必须*艮!
ALL将一个值与一组值比较,返回满足条件的
所有列值。必须跟!=<>,<=,>=
Selectname,salfromemp
Wheresal<=
all(500,800,1200);
Notbetween
AandB
判断某个值是否界于两者之间。Selectname,salfromempWhere
salbetween500and1200;
(notjexists判断某个列是否存在于一组值中。selectdname,deptnofrom
deptwhereexists
(select*fromempwhere
dept.deptno=emp.deptno)
A(not]likeb
[Escape4char,]
比较两个模式是否相似,当使用like语句
时Oracle不去访问索引。
Select*fromemp
Whereenamelike'TH%';
Is[not]null测试值是否为空。Selectename,deptnofromemp
Wherecomm.Isnullor
comm.=0;
Not对结果的否定。Select*fromemp
Wheresalnot(sal<1000);等价
于
selectename,salfromemp
wheresal>=1000;
AND用于判断两个条件十分都满足。Select*fromempwhere
Ename=,SIMTH,and
sal>=1000;
OR用于判断两个条件中是否有一个满足。Select*fromempwhere
Ename=,SIMTH,or
ename=,SCOTT,;
UNION用于返回(组合)两个查询中所有唯一的
行。
Selectenamefromempunion
Selectenamefromemp;
UNIONALL用于返回(组合)两个查询中所有所有的
行。
INTERSECT用于返回两个查询中相同的行。Selectenamefromemp1
intersectselectenamefrom
emp2;
MINUS用于返回两个查询中的不同的行。
§1.8.2简单select查询
当我们可以用SQL*PLUS登录到SQL>下后,我们可以用DESC显示某表的结构,也可以
用
select语句简单查询表中的一些列的内容。
例:要查询EMP表中员工的姓名、工资及出生日期,则:
SQL>selectename,sal,hiredatefromemp;
数据库培训教材
培训ORACLE请联系/p>
38
§1.9伪列及伪表
Oracle系统为了实现完整的关系数据库功能,系统专门提供了一组称为伪列
(Pseudocolumn)的数据库列,这些列不是在建立对象(如建表)时由我们完成的,而是
在我们建立对象时由自动Oracle完成的。Oracle目前有以下的伪列:
CURRVALandNEXTVAL使用序歹(I号的保留字
LEVEL查询数据所对应的级
ROWID记录的唯•标识
ROWNUM限制查询结果集的数量
有关伪列的详细解释和使用见相关章节。
Oracle还提供了一个DUAL的伪表,该表主要目的是保证在使用SELECT语句中语句
的完整性而提供的,如:我们要查询当前的系统H期及时间,而系统的II期和时间并是放在
一个指定的表里。所以在from语句后就没有表名给出。为了使用from后有个表名,我们
就用DUAL代替。如:
例1:查询Oracle系统日期及时间:
SQL>selectto_char(sysdate,'yyyy.mm.ddhh24:mi:ss')fromDUAL;
TO_CHAR(SYSDATE;YY
2001.06.0207:28:09
例2:计算一下5000+5000*0.1的结果是多少,则:
SQL>select5000+5000*0.1fromDUAL;
5000+5000*0.1
5500
§1.10使用SQLWorksheet工作
在新的Oracle版本里,系统提供了一个叫SQLWorksheet的图形SQL工作环境,它是
ORACLEEnterpriseManager的一部分。使用它可以免去在SQL>下编辑行命令的不便。它
士
兀
全是在图形方式进行。它的启动有以下几种方法:
从ORACLEEnterpriseManager中启动
从Administrtor工具中启动
从操作系统中启动
下面给出在操作系统下启动SQLWorksheet工具工作的步骤:
数据库培训教材
培训ORACLE请联系/p>
39
1)点击SQLWorksheet图标,即选择开始->程序->Oracle-OraHome81->database
Administration->SQLPLUSWorksheet„选中SQLPLUSWorksheet后出现如下画面:
图1.10-1使用SQLWorksheet的登录画面
在用户名、口令及服务输入ORACLE的有效用户名、口令及连接字串再点击确定后进入如图
1.10-2所示的画面。
2)当进入图1.10-2所示的画面后。你就可以工作了。目前的版本是在屏幕的上方输入命令,
屏幕的下方是系统显示结果。
3)我们将光标点击到屏幕上方,输入SQL命令,如输入:
selectename,salfromscott.emp;
输入完成命令后,用光标点击左边的执行图标,Oracle会结果显示在屏幕的下方:
数据库培训教材
培训ORACLE请联系一】3705091575
40
图h10-2登录成功后的画面
图1.10-3查询命令及显示结果画面
数据库培训教材
培训ORACLE请联系/p>
41
从SQLWorksheet工具中可以看出,该工具提供了相当丰富的功能。下面给出个功能的简
要
说明:
1.文件:
改变数据库连接:可以连接到另外的用户和数据库上去;
打开:可以打开现有文件;
将输入另存为:将当前命令存为一个文件;
将下方显示的结果存为一个文件。
2.编辑:
剪切:将当前选中字符进行剪切;
复制:将选中字符进行剪切;
粘贴:将缓冲区字符进行粘贴;
全选:对当前画面所有字符全选;
全部清除:清除目前的所有内容。
3.工作单:
执行:执行当前的命令;
运行本地脚本:运行操作系统下某个.SQL文件(点击后提示输入文件名);
命令历史记录:查看命令历史记录;
上一条命令:显示上一条命令。
提示:如果你的SQL显示的不是汉字(出现乱字符),请你将你的N7Z2OOO/98
环
境下的汉字变量设置与服务器的一样就好了。在WINDOWSNT环境设置汉字变量步骤:
用regedif修改注册表中的所有ORACLE字符集项,如:
“点击:开始=》运行=》输入reged”;
2)点击“HEY_LOCAL_MACH1NE”左边的“+”以展开目录;
3)点击aSOFTWARE"左边的"+”以展开目录;
4)点击aORACLE"项;
5)修改。心1CLE树下所有“NLS”变量与pr%就中的一致。
关于汉字环境变量的设置,请参阅《Oracle8i/9I初级数据库管理》。
数据库培训教材
培训ORACLE请联系/p>
42
第二章查询基础
下面给出查询的一些基本的操作描述,如果你是位Oracle老手则可以跳这•章。
§2.1SELECT语句
在关系数据库中,使用频率最高要算SELECT语句了。尽管SELECT语句的使用非常
简单,但确很有学问。下面简单介绍有关SELECT语句的常用方法。
1.命令语法:
SELECT的简单语法:
SELECT[DISTINCTIALL]{*Icolumn1[,column2]...}
FROM{table_lI(subquery)}[alias]
[,{table_2I(subquery)}[alias]]...
[WHEREcondition]
[CONNECTBYcondition[STARTWITHcondition]
[GROUPBYexpnj[HAVINGexpnj
[{UNION[ALL]IINTERSECTIMINUS}SELECT...]
IORDERBYlexpnJ[ASCIDESCJ
(FORUPDATE[OF[userjtableIview]column]
[NOWAITJ
详细语法结构需查阅最新ORACLE原版《ORACLE8iSQLREFERENCEMANUAL》或
«ORACLE9iSQLREFERENCEMANUAL》
§2.2SQL中的单记录函数
许多资料(包括Oracle的资料)把Oracle的SQL语句中用到的函数分为单值函数和多
值函数,单值函数又分为字符函数和数字函数。卜面分别介绍它们。
§2.2.1单记录字符函数
函数说明
ASCII返回对应字符的十进制值
CHR给出十进制返回字符
数据库培训教材
培训ORACLE请联系/p>
43
CONCAT拼接两个字符串,与II相同
INITCAT将字符串的第一个字母变为大写
INSTR找出某个字符串的位置
INSTRB找出某个字符串的位置和字节数
LENGTH以字符给出字符串的长度
LENGTHB以字节给出字符串的长度
LOWER将字符串转换成小写
LPAD使用指定的字符在字符的左边填充
LTRIM在左边裁剪掉指定的字符
RPAD使用指定的字符在字符的右边填充
RTRIM在右边裁剪掉指定的字符
REPLACE执行字符串搜索和替换
SUBSTR取字符串的子串
SUBSTRB取字符串的子串(以字节)
SOUNDEX返回•个同音字符串
TRANSLATE执行字符串搜索和替换
TRIM裁剪掉前面或后面的字符串
UPPER将字符串变为大写
NVL以•个值来替换空值
ASCII(<cl>)
«1>是字符串。返回与指定的字符对应的十进制数。
SQL>selectascii('A')A,ascii('a')a,ascii('O')zero,ascii('')spacefromdual;
AaZEROSPACE
65974832
SQL>selectascii('赵')zhao,length('赵')lengfromdual;
ZHAOLENG
547401
CHR(<I>[NCHAR])
给出整数,返回对应字符。如:
SQL>selectchr(54740)zhao,chr(65)chr65fromdual;
ZHC
赵A
CONCAT(<cl>,<c2>)
数据库培训教材
培训ORACLE请联系/p>
44
SQL>selectconcatfO10-',
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2024版房屋委托装修合同
- 二零二五年度工业厂房合同转让与国际贸易合规范本3篇
- 二零二五年度甲方与联合体跨境电商合同范本3篇
- 2024版食堂托管合同
- 2024版维修服务合同RSC
- 2024版短期车牌租赁协议
- 2024版细化第三方交易协议样式
- 二零二五年度物业管理与社区文化活动合作合同3篇
- 2025年货运从业资格证年考试题及答案解析
- 二零二五年度社交媒体运营与内容创作服务合同3篇
- 三轴搅拌桩安全技术交底(好)
- Unit-1-The-Dinner-Party市公开课一等奖省赛课微课金奖课件
- 2024年辅警考试公基常识300题(附解析)
- 血脂康胶囊的经济学评估
- 组织协调方案及措施
- 2024-2029年中国IP授权行业市场现状分析及竞争格局与投资发展研究报告
- 加油站反恐演练工作方案及流程
- 12000DWT散货船总体设计
- 投资收益证明模板
- 西方国家的量刑建议制度及其比较
- 【阅读提升】部编版语文五年级下册第五单元阅读要素解析 类文阅读课外阅读过关(含答案)
评论
0/150
提交评论