Oracle PLSQL开发入门指南

PL/SQL培训教程

OraclePL/SQL开发入门指南

文档作者:

创建口期:

确认日期:

控制编码:

当前版本:1.0

目录

目录........................................................................................................2

UNIT!SQL、PL/SQL概要..................................................................................1

单元培训目标..................................................................................................................................................................................1

LESSON1SQL、SQL*Plus和PUSQL基本概念....................................................................................................................I

UNIT2数据查询...........................................................................................3

单元培训目标..................................................................................................................................................................................3

LESSON/简单的数据查询..........................................................................................................................................................3

基本结构:............................................................3

算术表达式：..................3

字符串处理............................................................4

空处理........................-................................-.......................................4

消除重复行:..................4

显示表结构：..................4

数据控制输出..................5

逻辑比较......................-................................6

LESSON3单行函数的使用..........................................................................................................................................................6

字符函数......................-........................................................................6

数值函数......................7

日期函数......................-........................................................................7

转换函数......................8

单行函数使用的例子：..........-................................9

连接9

别

表0

等

非0

连

外0

LESSON5组函数...............................................................................................................................................................II

基本格式......................-.......................................................................11

常用组函数....................11

LESSON6子查询..........................................................................................................................................................................12

格式..........................12

UNIT3数据库..............................................................................................14

单元培训目标................................................................................................................................................................................14

LESSONI系统开发周期和数据关联........................................................................................................................................14

系统开发周期..................14

数据关联......................-......................................15

约束..........................-.................................15

LESSON2创建表、序列、视图和索引..................................................................................................................................15

创建表格式....................16

创建序列格式..................-......................................17

创建视图......................18

创建索引......................18

LESSON3Oracle数据字典.........................................................................................................................................................19

Oracle数据字典的四大类视图：....................................19

其他视图：............................................................................................19

常用视图.............................................................19

LESSON4用户访问控制:.......................................................................................................................................................20

系统权限:...................-........................................................................20

对象权限：..........................................................21

权限的回收：..................22

创建同义词：..................-.....................................22

远程数据库的访问：............-........................................................................22

UNIT4PL/SQL...................................................................................................................................................................................24

单元培训目标...................................................................................24

LESSON1简单PL/SQL...............................................................................................................................................................................24

PL/SQL结构...................24

匿名块，过程和函数...........-.................................25

包............................25

PL/SQL环境...................27

OracleProcedureBuilder的使用....................................27

程序中的参数和变量类型.......-.................................28

LESSON2数据获取和游标.......................................................................31

数据获取......................31

游标..........................32

LESSON3PUSQL控制流.........................................................................33

1F语句........................33

LOOP语句....................34

LESSON4出错处理.............................................................................35

Exception类型.................35

Exception处理.................35

非预定义的Oracle数据库例外....................................-......................................36

用户自定义的例外：............37

附录A:.....................................................................................................................................................................................................................38

数据结构:.....................................................................................38

描述:.........................................................................................38

UNIT1SQL、PI/SQL概要

单元培训目标

♦了解SQL、PL/SQL的基本概念、功能

♦了解Oracle产品集和关联

♦了解SQL、PL/SQL的基本命令

LESSON1SQL、SQL*Plus和PL/SQL基本概念

本节主要讲述SQL、PL/SQL和SQL*Plus的基本概念，以及Oracle产品集之间的关

联。

SQL、SQL*PIus和PL/SQL是用来检索和维护存储在Oracle数据库I-.数据的工具或

语言。

♦Oracle是一个对象一关系型数据库管理系统(ORDBMS),它扩展了普通关系

型数据库管理方式，支持面向对象的概念，以表的形式存储信息，维护数据的输

入、数据的存储和数据查询并处理及对数据的三种基本操作：采集、存储、检

索。

♦SQL:结构化杳询语s(StructuredQuervLanguage),用来与Oracle数据库服务

器进行交互。

例：selectname,salaryfromt_emp

selectsysdatefromdual

♦PL/SQL:Oracle扩展的SQL语言，具有完整的流程控制定义。

♦SQL，Pkis:Oracle常用的工具，用来识别SQL语言和编写、执行PL/SQL代码，

它与Oracle数据库管理系统紧密结合。

♦SQL，Plus环境登录：

(1)windows环境

(2)命令行环境

CompanyConfidential-Forinternaluseonly

如下格式:

Sqlplus[username[/password[©database]]]

username数据库用户名

password数据库的密码，此时是可见的

©database要登录的数据库名

一旦成功登录，系统提示如下：

SQL>

♦SQL常用命令:

分类命令

数据检索Select

数据维护(DML)insertsupdate、delete

数据定义(DDL)create、drop、alter、rename、truncate

事务处理控制commitsrollbackssavepoint

数据控制(DCL)Grant、revoke

♦数据、操作系统、Oracle数据库管理器、SQL和PL/SQL及工具之间的关系

在Oracle的产品中有如下的关系：以数据为核心，OracleServer结合操作系统

进行数据传递和通信，外围有SQL*Pkis等I：具提供给用户，通过SQL、

PL/SQL与OracleServer交互。(如卜图：)

数据

♦代码、缩写和命名标准：由于SQL与自然语的相似性，同时为提高代码的可读

性，对于检索语句、表列命名和程序代码应采用标准规则，表名和列名既要反映

数据的实际意义，乂要考虑简洁性、完整性。Oracle允许使用最多30个字符命

名表和列，为建立完整的、含义明确的、描述性的名称提供了足够的空间,对于

程序代码，更要讲究规范性，如变量、函数名等等，要含义明确，提高程序的可

读性，

2

CompanyConfidential-Forinternaluseonly

UNIT2数据查询

单元培训目标

♦理解Oracle数据查询的概念和作用：

♦掌握数据查询的基本方法；

♦通过函I数、「查询的学习，能写一些较旦杂的数据查询。

LESSON1简单的数据查询

为了检索数据库中的数据，必须使用SQL建立相应的查询语句。本节从最基本的

SELECT语句结构出发，讲述如何建立简单的数据查询。

基本结构：

SELECT[distinct]{*,column[alias],......}

FROMTABLE

SELECT:选择要查询的数据列

FROM:数据列所在的表

*:选择所有的列

COLUMN:选择的列名

ALIAS:给选定的列一个别名

例：SELECT*FROMs_dept

SELECTlast_name,first_name,addressFROMs_emp

算术表达式：

除了单一的获取数据库数据，也可以在检索的数据列中，可以做各种的算术表达式,

以获取所需数据，常用算术运算符有：

加：+

减：-

乘：*

除：/

例：

SELECTlast_namc,salary*12,basjsalary+salary

FROMtablename

3

CompanyConfidential-Forinternaluseonly

字符串处理

字符串可以做最简单的“加”法，即合并运算：11

例：

SELECTcolumn)||||column2||$

FROMtablcnamc

注：columrfl、column2必须位字符型数据列

SELECTfirst_name||"z||last_name|T，"||title"Employees"FROMs_emp

空处理

在Oracle数据库中，常会用到•种空处理，对于数据列中的空值，要做特定处理，就

会用到下面空处理函数：

NVL:在数据列的值为空时(并非为零)，以真实的值进行替代处理

例：

NVL(start_date,sysdate)

NVL(name,"noname')

NVL(base_salary,2000)

注：任何未被声明为NOTNULL或PRIMARYKEY的列都可包含空值，注意，

空值就是空值，并不是值为零或空格，零是一个数值，空格是一个字符，而空值

就是不具有任何类型的值。

例：

selectfirsCname,nvl(base_salary,2000]

froms_emp;

消除重复行：

对于数据检索中的重复行，可用如下方法去掠：

SELECTDISTINCTcolumnl,column?...

FROMTABLE

例：SELECTitem_idfromt_onhand

SELECTDISTINCTitem_idFROMt_onhand

选择多列非里奥：

例：SELECTDISTINCTvvarehoused_id,item_idFROMt_onhand

显示表结构：

对于一个并不熟悉的数据表，要检索其数据，必须知道它的结构和例的数据类型，否

则，就无法在SELECT语句中，写出其数据列表达式，通过如下方法，可以显示该数

4

CompanyConfidential-Forinternaluseonly

据表的结构:

格式：

DESCRIBE]tablename

Oracle常用数据类型：

number(p,s):浮点类型，最大拜度p,小数位s位

varchar2(s):可变长字符类型，最大长度s位

date:日期型

char(s):定长字符类型，长度为s

例：

SQL>dcsc

NameNullType

itemjdNOTNULLnumber

item_nameNOTNULLvarchar2(20)

base_pricenumber,

descriptionvarchar2(100)

classjdnumber

数据控制输出

对于检索的数据集，可以使用ORDERBY语句指定其排序方式，即输出方式:

SELECTexpr

FROMtable

[WHEREcondition⑸]

[ORDERBYexpr];

在expr后可指定其排序方式，缺省为升序排列输出，添加参数desc则为降序排列输

出。

例：

SELECTemployee_id,first_name,last_namezsalary

FROMt_emp

ORDEREYfirst_name,salary

5

CompanyConfidential-Forinternaluseonly

逻辑比较

Oracle梏用的逻辑比较操作如下：

=、＞=、＜=、＞^＜

between…and...、in、like、isnull

and、or、not

例：

SELECTlast_name,employee_idFROMt_emp

/,//

WIIEREslart_date＞to_date(2001-01-01zyyyy-mm-dd)

Andlast_namelike'%H%'

注意：在逻辑比较中，NOT的级别最高，其次是AND,最后是OR

负操作：(通过与not组合，产生非值)

NOTBEWTEEN…AND

NOTIN(list)

NOTLIKE

ISNOTNULL

LESSON3单行函数的使用

在Oracle的数据检索中，当简单的运算，例如加、减等不能满足对数据列的处理时，

可以通过使用功能更强大的单行函数来达到目标，基本格式如下：

Functicn_name(column|expression,[argl,arg2,.......])

SQL中共有两种列函数，•是单行函数，另一是多行函数，单行函数主要有四种类

型：字符函数、数字函数、日期函数和转换函数。单行函数对数据检索中的每一行作

用，每一行返回一个结果。

如下一一介绍各类型函数的使用：

字符函数

加下是用来处理类型为字符类型的数据或数据列：

LOWER(col|cxpr)、UPPER(col|expr)、INITCAP(col|cxpr)

CONCAT(col|exprl,col|expr2)、SUBSTR(col|expr,m[znJ)

LENGTH(col|expr)

NVL(col|exprl,col|cxpr2)

6

CompanyConfidential-Forinternaluseonly

其中，LOWER函数是把字符串全部转换为小写：UPPER函数功能与LOWER刚

好相反，它是把全部字符串转换为大写：CONCAT是用来连接两个字符串，类似

于运算符'||'：SUBSTR是用来截取母字符串中的•个子串，在SQL中较常用

至於LENGTH是来册字符串的长度：而NVL具有所有NVL的通用性，即是在列

值为空时，用后者取而代之。

例：

SQL>SELECTsubstrfSTRING；2,3)fromdual

SQL>TRI

数值函数

如下是用来处理类型为数值类型的数据或数据列：

ROUND

TRUNC

MOD

注意：round和trunc函数具有■不同之处，截取的方法不同，如下有一些示例，说

明这两则的不同用法，如下：

例：round(23535.7476,2)==>23535.75

round(23535.7476,0)==>23536

round(23535.7476,-1)==>23530

trunc(23535.7476z2)==>23535.74

trunc(23535.7476)==>23535

trunc(23535,-1)==>23530

H期函数

如下是用来处理类型为R期类型的数据或数据列的函数：

MONTHS_BETWEEN(datel,date2)

ADD_MONTHS(date,n)

NEXT_DAY(date,,char,)

LAST.DAY(date)

ROUND(datc[,4fmf])

TRUNC(date[.'fmt'])

日期函数有其特性：

例：

add_months(to_date(/2001-05-0r/yyyy-mm-dd,),2)==>2001-07-01

7

CompanyConfidential-Forinternaluseonly

/,,

add_months(to_date(2001-05-0rzyyyy-mm-dd)z-2)==>2001-03-01

注意：

两个口期不可相加，但可做减法，结果为number型，口期型数据加/减数值型数

据结果为日期型，日期型数据间不可做乘除法，其他运算借助于日期函数。

SYSDATE：

SYSDATE是一个返回系统日期和时间的函数，可以把SYSDATE作为任何表中都

具有的普通列。

DUAL：

DUAL是用户SYS的一个表，可以被任何用户使用，它只包含一个列DUMMY,

且只具有一行值“x"，这是-•个非常有用的表，常用来返回一些常量、单值等

等。

例如：

SQL>SELECTSYSDATEFROMDUAL

转换函数

很多时候，不仅要在同一类型中进行运算，还要把某一类型数据转换为其他类型，以

例运算或显示，如下是用来进行处理数据或数据列的类型的类型转换函数：

TO_CHAR(number/date,['finf])

TO_NUMBER(char)

TO_DATE(char,『fmt'])

转换函数即用来变换数据的类型，如把字符串按格式转换成日期型、数值型，八

数值型、日期型转换成字符串等等。

在to_date的函数中，注意「fmt']的使用，区别‘yyyy'和‘RRRR','RR',‘yy’等，如

下是to.date格式的详细情况：

格式描述

SCCorCC世纪，S则在BC日期前加-

YYYYorSYYYY年，S则在BC日期前加-

YYYorYYorY年份的后3或2或1位数字

Y,YYY年份，中间以“，”号隔开

IYYY.TYY,IY,IISO标准的年份,分别为四位,三位,两位和一位

SYEARorYEAR年的拼写，S则在BC日期前加・

BCorAD年份的BC或AD的标示

Q季度

8

CompanyConfidential-Forinternaluseonly

MM月份，数值

MONTH月份的全拼写

MON月份的拼写，前三位

RM罗马数字月份

WWorW一年中的星期或月中的星期

DDDorDDorD年或月或星期中的天

DAY天的星期，全拼写

DY天的星期，前三位拼写

JJulia天，从4713BC开始

例：

SELECTTO_CHAR(sysdate/DAY,)FROMDUAL;

SELECTTO_DATE(/2001-07-14//YYYY-MM-DD,)FROMDUAL

单行函数使用的例子：

例：

例1：SELECFlast_name||||first_name,employee_id,manager_id

FROMs_cmp

WHEREtrunc(start_date)=to_date('2031-05-12ryyyy-mm-ddz)

Andlength(Iast_name)<10

LESSON4多表数据查询

在数据的检索中，常常要按一定的联系同时从多个表中获取数据，尤其是数据庠设计

时遵循范式规则，这是就是一种所谓的多表数据查询，本节从建立表的联系开始，讲

述多表数据的查询：

多表数据查询的首要条件是如何把多个数据表按一定的规则连接起来，从而获取多个

表的、组班好的信息。连接的方式有多种，可按不同的要求进行选择。

连接

即是最筒单的等号连按，以分别两个点中某列建立联系；

SELECTtable.column,table.column

FROMtablel,table2

WHEREtablel.columnl=table2.colmn2;

9

CompanyConfidential-Forinternaluseonly

表别名

在多表数据检索中，山丁检索的数据列可能存在于多个表中，且具有同一列名，这时

必须在列名前加上表名以示区别，而对于较长的表名，写起来太麻烦，故可用表别名

来区分各个不同的表，如下；

SELECTT1.column,T2.column.......

FROMtablelTl,

table2T2

WHERETl.columnl=T2.coImn2;

非等号连接

即不使用等号来连接两个或两个以上的表

例如：

使用：＞=、＜、between

外连接

对于两个表，如记录不能一一连接，而乂不想漏掉数据，可使用外连接

SELECTT1.column,T2.column.......

FROMtablelTl,

table2T2

WHERETl.columnl=T2.colmn2(+);

自连接

对于•某些情况，必须要有表白身的连接，这时可利用表别名的作用，即把表完全

看作两个不同的表来处理

对于已连接好的多表数据查询，如同一张“大数据表”，进行类似的数据宜询。

例：

SELECTn.fist_name,T2.first_namemanager.......

FROMt_cmpTl,

T_empT2

WHERET1.employee_id=T2.employee_id(+);

对多干两个以上的表连接具有相同的处理方法，如卜是一多表连接例子:

例：

SELECTtl.itemname,t2.quantity,t3.1astname

FROMt_itemt1,

T_salcst2,

T_empt3

WHEREtl.item_id=t2.item_id

10

CompanyConfidential-Forinternaluseonly

Andt2.emp_id=t3.employee_id

Andt2.approval_flag="Y/

LESSON5组函数

在数据检索中，我们不仅仅要检索出数据信息，同时还需要一些加工，例如一些统计

信息等等，而数据库表中的数据总是离散在许许多多的列中，要组织起来就会用到另

一类列函数，即是组函数。

从基本的格式讲起，如何组织数据，分组，以及使用HAVING语句筛选出所需组信

息。

基本格式

SELECTcolumn,group_function

FROMtable

[WHEREcondition]

[GROUPBYgroup_by_expr]

[HAVINGgroup_condition]

[ORDERBYcolumn……]

*GROUPBY:对列进行分组

*HAVING:条件限制组查询

常用组函数

AVG.COUNT、MAX、MIN

STDDEV、SUM、VARIANCE

组值函数如sum、avg、stddev等等，反映的是一组值的某种整体状况、一些统

计信息。同时，要注意的是，组值函数忽略NULL值，在做计算时将其排除在

外，如avg函数，如数据中含有较多的空值时，avg返回的统计信息偏差较大，

而counl函数出外，它是种特殊情况，它可以处理NULL值，并总是返回个

数值，所求出的值决不会是NULL。

例：

SELECTT,T2.item_name,sum(TB.quantity),min(T3.rec_date)

FROMt_inventoryTlz

TJtemT2,

T_onhandT3

WhereTl.vvarehouse_id=T3.warehouse_id

AndT3.item_id=T3.item_id

GROUPBYT,T2.item_name

HAVINGsum(T3.quantity)>100

11

CompanyConfidential-Forinternaluseonly

所有的组值函数都有一个distinct或all选项，缺省的选项为alLcount函数就是

一个说明起作用的例子：

注意如下查询的结果：

SELECTcount(distinctitem_id),count(itcm_id),count(*)

FROMt_onhand

HAVIGN与WHERE：

WHERE语句作用在数据检索的每一列上，对每一列进行判定，HAVING看句则

是作用在每一组上，对每一组进行判定，WHERE语句在HAVING起作用前作

用。

考虑以下SQL语句的查询：

SELECTtl.last_name,sum(t2.quantity*t2.price)

FROMs_emptl,

S_salest2

WHEREtl.emp_id=t2.emp_id

Andnvl(t2.cancel_flag/N/)<>/Y，

Andnvl(t2.approval_flag,/N,)=,Y,

Havingsum(t2.quantity*t2.price)>10000;

LESSON6子查询

r查询是•段嵌入在另•段SQL语句中SELECT语句，通过/渣询，可以建立功能强

大的查询语句，尤其在检索某以数据表时，又依赖这张表本身的一些情况。

格式

SELECTselectJist

FROMtable

WHEREexproperator

(SELECTselectjist

FROMtable)

注:

I.Expr与子查询的select_list有相应的结构和数据类型

II.子查询无orderby语句

III.子查询必须在operator的右面

下名通过•些例子来说明子查询的应用。

例：

12

CompanyConfidential-Forinternaluseonly

SELECTtl.ite