2015年oracle数据库开发教材_第1页
2015年oracle数据库开发教材_第2页
2015年oracle数据库开发教材_第3页
2015年oracle数据库开发教材_第4页
2015年oracle数据库开发教材_第5页
已阅读5页,还剩167页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

第一章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. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论