plsql guide（基础详解）

PL/SQL培训教程

文档作者: 创建日期: 确认日期: 控制编码: 当前版本:

1.0

Company Confidential - For internal use only

1

目录

UNIT1 SQL、PL/SQL概要 ................................................................................................................................................. 1 单元培训目标 .................................................................................................................................................................... 1 LESSON 1 SQL、SQL*Plus和PL/SQL基本概念 ........................................................................................................... 1

UNIT2 数据查询 .................................................................................................................................................................. 3 单元培训目标 .................................................................................................................................................................... 3 LESSON 1简单的数据查询 .............................................................................................................................................. 3

基本结构： ......................................................................................................................................................................................... 3 算术表达式： ..................................................................................................................................................................................... 3 字符串处理 ......................................................................................................................................................................................... 4 空处理 ................................................................................................................................................................................................. 4 消除重复行： ..................................................................................................................................................................................... 4 显示表结构： ..................................................................................................................................................................................... 4

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

LESSON 2 SQL*Plus的使用 ............................................................................................................................................. 5

SQL*Plus的使用： ............................................................................................................................................................................ 5 数据控制输出 ..................................................................................................................................................................................... 7 逻辑比较 ............................................................................................................................................................................................. 7

LESSON 3 单行函数的使用 .............................................................................................................................................. 8

字符函数 ............................................................................................................................................................................................. 8 数值函数 ............................................................................................................................................................................................. 8 日期函数 ............................................................................................................................................................................................. 9 转换函数 ............................................................................................................................................................................................. 9 单行函数使用的例子： ................................................................................................................................................................... 10

LESSON 4多表数据查询 ................................................................................................................................................ 11

连接 ................................................................................................................................................................................................... 11 表别名 ............................................................................................................................................................................................... 11 非等号连接 ....................................................................................................................................................................................... 11 外连接 ............................................................................................................................................................................................... 11 自连接 ............................................................................................................................................................................................... 12

LESSON 5组函数 ............................................................................................................................................................ 12

基本格式 ........................................................................................................................................................................................... 12 常用组函数 ....................................................................................................................................................................................... 13

LESSON 6 子查询 ............................................................................................................................................................ 14

格式 ................................................................................................................................................................................................... 14

UNIT3数据库 ...................................................................................................................................................................... 16 单元培训目标 .................................................................................................................................................................. 16 LESSON 1系统开发周期和数据关联 ............................................................................................................................. 16

系统开发周期 ................................................................................................................................................................................... 16 数据关联 ........................................................................................................................................................................................... 17 约束 ................................................................................................................................................................................................... 17

LESSON ２创建表、序列、视图和索引 ....................................................................................................................... 17

创建表格式 ....................................................................................................................................................................................... 18 创建序列格式 ................................................................................................................................................................................... 19 创建视图 ........................................................................................................................................................................................... 20 创建索引 ........................................................................................................................................................................................... 20

LESSON 3Oracle数据字典 ............................................................................................................................................. 21

Oracle数据字典的四大类视图： .................................................................................................................................................... 21 其他视图： ....................................................................................................................................................................................... 21 常用视图 ........................................................................................................................................................................................... 21

LESSON 4用户访问控制： ............................................................................................................................................ 22

系统权限： ....................................................................................................................................................................................... 22

Company Confidential - For internal use only

2

对象权限： ....................................................................................................................................................................................... 23 权限的回收： ................................................................................................................................................................................... 24 创建同义词： ................................................................................................................................................................................... 24 远程数据库的访问： ....................................................................................................................................................................... 24

UNIT4 PL/SQL ..................................................................................................................................................................... 26 单元培训目标 .................................................................................................................................................................. 26 LESSON 1简单PL/SQL .................................................................................................................................................. 26

PL/SQL结构 ..................................................................................................................................................................................... 26 匿名块，过程和函数 ....................................................................................................................................................................... 27 包 ....................................................................................................................................................................................................... 27 触发器 ............................................................................................................................................................................................... 29 PL/SQL环境 ..................................................................................................................................................................................... 30 Oracle Procedure Builder的使用...................................................................................................................................................... 31 程序中的参数和变量类型 ............................................................................................................................................................... 32

LESSON ２数据获取和游标 ........................................................................................................................................... 35

数据获取 ........................................................................................................................................................................................... 35 游标 ................................................................................................................................................................................................... 36

LESSON ３PL/SQL控制流.............................................................................................................................................. 37

IF 语句 .............................................................................................................................................................................................. 37 LOOP 语句 ....................................................................................................................................................................................... 38

LESSON ４出错处理 ....................................................................................................................................................... 39

Exception 类型 .................................................................................................................................................................................. 39 Exception 处理 .................................................................................................................................................................................. 39 非预定义的Oracle 数据库例外 ....................................................................................................................................................... 40 用户自定义的例外： ....................................................................................................................................................................... 41

附录A: ................................................................................................................................................................................. 42 数据结构： ...................................................................................................................................................................... 42 描述： .............................................................................................................................................................................. 42

Company Confidential - For internal use only

3

UNIT1 SQL、PL/SQL概要

单元培训目标

?

了解SQL、PL/SQL的基本概念、功能 了解Oracle 产品集和关联 了解SQL、PL/SQL的基本命令

?

?

LESSON 1 SQL、SQL*Plus和PL/SQL基本概念

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

SQL、SQL*Plus和PL/SQL是用来检索和维护存储在Oracle数据库上数据的工具或语言。

?

Oracle是一个对象— 关系型数据库管理系统（ORDBMS），它扩展了普通关系型数据库管理方式，支持面向对象的概念，以表的形式存储信息，维护数据的输入、数据的存储和数据查询并处理及对数据的三种基本操作：采集、存储、检索。

SQL: 结构化查询语言(Structured Query Language)，用来与Oracle数据库服务器进行交互。

例：select name, salary from t_emp select sysdate from dual

?

?

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

SQL*Plus: Oracle常用的工具，用来识别SQL语言和编写、执行PL/SQL代码，它与Oracle数据库管理系统紧密结合。 SQL*Plus环境登录： （1） windows环境

?

?

（2） 命令行环境

1

Company Confidential - For internal use only

如下格式：

Sqlplus [username [/password username password @database

数据库用户名

数据库的密码，此时是可见的 要登录的数据库名

[@database]]] 一旦成功登录，系统提示如下： SQL>

?

SQL常用命令：

分类 数据检索 数据维护(DML) 数据定义(DDL) 事务处理控制 数据控制(DCL)

?

命令 Select insert、update、delete create、drop、alert、rename、truncate commit、rollback、savepoint Grant、revoke

数据、操作系统、Oracle数据库管理器、SQL和PL/SQL及工具之间的关系 在Oracle的产品中有如下的关系：以数据为核心，Oracle Server 结合操作系统进行数据传递和通信，外围有SQL*Plus等工具提供给用户，通过SQL、PL/SQL与Oracle Server交互。 （如下图：）

数据操作系统?

操作系统Oracle 8Oracle 8PL/SQLSQL、PL/SQLSQL、工具工具应用产品应用 ?

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

性，对于检索语句、表列命名和程序代码应采用标准规则，表名和列名既要反映数据的实际意义，又要考虑简洁性、完整性。Oracle允许使用最多30个字符命名表和列，为建立完整的、含义明确的、描述性的名称提供了足够的空间。对于程序代码，更要讲究规范性，如变量、函数名等等，要含义明确，提高程序的可读性。

2

Company Confidential - For internal use only

UNIT2 数据查询

单元培训目标

?

理解Oracle数据查询的概念和作用； 掌握数据查询的基本方法；

通过函数、子查询的学习，能写一些较复杂的数据查询。

?

?

LESSON 1简单的数据查询

为了检索数据库中的数据，必须使用SQL建立相应的查询语句。本节从最基本的SELECT语句结构出发，讲述如何建立简单的数据查询。 基本结构：

SELECT [distinct] {*, column [alias], ……} FROM TABLE SELECT: 选择要查询的数据列 FROM: *:

数据列所在的表 选择所有的列

COLUMN:选择的列名 ALIAS:

给选定的列一个别名

例：SELECT * FROM s_dept

SELECT last_name, first_name, address FROM s_emp 算术表达式：

除了单一的获取数据库数据，也可以在检索的数据列中，可以做各种的算术表达式，以获取所需数据，常用算术运算符有：

加：+ 减：- 乘：* 除：/ 例：

SELECT last_name, salary * 12, base_salary + salary FROM tablename

3

Company Confidential - For internal use only

字符串处理

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

SELECT column1||‘.‘||column2 ||‘$‘ FROM tablename

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

SELECT first_name ||‘ ?|| last_name||‘,‘|| title ―Employees‖ FROM s_emp 空处理

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

NVL: 例：

NVL (start_date, sysdate) NVL (name, ?no name‘) NVL (base_salary, 2000)

在数据列的值为空时（并非为零），以真实的值进行替代处理

注：任何未被声明为NOT NULL或PRIMARY KEY的列都可包含空值，注意，空值就是空值，并不是值为零或空格，零是一个数值，空格是一个字符，而空值就是不具有任何类型的值。 例：

select first_name, nvl(base_salary, 2000) from s_emp; 消除重复行：

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

SELECT DISTINCT column1, column2 … FROM TABLE 例：SELECT item_id from t_onhand

SELECT DISTINCT item_id FROM t_onhand 选择多列非重复：

例：SELECT DISTINCT warehoused_id, item_id FROM t_onhand 显示表结构：

对于一个并不熟悉的数据表，要检索其数据，必须知道它的结构和例的数据类型，否则，就无法在SELECT语句中，写出其数据列表达式，通过如下方法，可以显示该数

4

Company Confidential - For internal use only

据表的结构：

格式：

DESC[RIBE] tablename Oracle常用数据类型： number(p, s): varchar2(s): date: char(s): 例：

浮点类型，最大精度p，小数位s位 可变长字符类型，最大长度s位 日期型

定长字符类型，长度为s

SQL>desc t_item Name -------- item_id Null ------ Type ------- number varchar2(20) number, varchar2(100) number NOT NULL NOT NULL item_name base_price description class_id LESSON 2 SQL*Plus的使用

本节介绍Oracle最常用的一个工具SQL*Plus，在它的环境中，可以通过命令方式执行数据检索、PL/SQL块编辑和编译等等。 SQL*Plus的使用：

I.

文件命令

a) SAVE filename 把当前SQL缓冲区的内容存储在文件filename中 b) GET filename 把文件filename中的内容写入当前SQL缓冲区 c)

START filename 执行存储在filename中的内容

d) @ filename 执行存储在filename中的内容 e)

EDIT filename 打开文本编辑器，把当前SQL缓冲区的内容写入文件afiedt.buf

SPOOL filename 把查询的数据结果存储在filename中

f)

5

Company Confidential - For internal use only

g) EXIT 退出SQL*Plus 例：在SQL*Plus中敲入如下命令： SQL>@e:\\test\\demo.sql

SQL*Plus则自动运行存储在e:\\test\\demo.sql中的PL/SQL代码。 II.

文本编辑命令 a) A[PPEND] text

在当前行末尾添加文本text

b) C[HANGE]/old/new 在当前行中以new替代old文本 c)

CL[EAR]buff[ER]

清空缓冲区的所有文本 删除当前行

插入行

d) DEL e) f)

I[NPUT] text L[IST] n

显示第n行

以文本text代替第n行

g) N text III.

在SQL*Plus中，可以预定一些列的输出格式，这样，有利于数据的整齐性，同时，也可通过这些格式的制定，做一些简单的SQL*Plus报表，其格式输出如下： 格式：

COL[UMN] [{column|alias}[option ……]] a)

b) 参数选项：

CLE[AR]、FOR[MAT]format、HEA[DING]text、

JUST[TIFY]{align}、NOPRI[NT]、NUL[L]text、PRI[NT]、TRU[NCATED]、WEA[PPEND]、WOR[D_WRAPPED]

例：

COLUMN last_name HEADING ?employee|name‘ FORMAT A15 COLUMN salary JUSTIFY LEFT FORMAT $99,999.00 COLUMN start_date FORMAT A9 NULL ?Not hired‘

c)

字符和数值格式： 格式 An 描述 设置字符串或数据类型的显示长度 代表非零数值位 限定以零带头 举例 N/A 输出结果 N/A 9 0 999999 099999 1234 01234

6

Company Confidential - For internal use only

$ L . ,

数据控制输出

代表美元符号 代表本地货币符号 代表小数点位置 代表千位分割符 $9999 L9999 9999.99 9，999 $1234 L1234 1234.00 1，234 对于检索的数据集，可以使用ORDER BY语句指定其排序方式，即输出方式：

SELECT expr FROM table [WHERE condition(s) [ORDER BY expr ]; ] 在expr后可指定其排序方式，缺省为升序排列输出，添加参数desc则为降序排列输出。 例：

SELECT employee_id, first_name, last_name, salary FROM t_emp

ORDER BY first_name, salary 逻辑比较

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

=、>=、<=、>、<

between…and…、in、like、is null and、or、not 例：

SELECT last_name, employee_id FROM t_emp

WHERE start_date>to_date(?2001-01-01‘,‘yyyy-mm-dd‘) And last_name like ?%H%‘

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

负操作：（通过与not组合，产生非值）

NOT BEWTEEN …AND NOT IN （list） NOT LIKE

7

Company Confidential - For internal use only

IS NOT NULL

LESSON 3 单行函数的使用

在Oracle的数据检索中，当简单的运算，例如加、减等不能满足对数据列的处理时，可以通过使用功能更强大的单行函数来达到目标，基本格式如下：

Function_name (column|expression, [arg1, arg2, ……]) SQL中共有两种列函数，一是单行函数，另一是多行函数，单行函数主要有四种类型：字符函数、数字函数、日期函数和转换函数。单行函数对数据检索中的每一行作用，每一行返回一个结果。 如下一一介绍各类型函数的使用： 字符函数

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

LOWER(col|expr)、UPPER(col|expr)、INITCAP(col|expr) CONCAT(col|expr1, col|expr2)、SUBSTR(col|expr, m[, n]) LENGTH(col|expr) NVL(col|expr1, col|expr2)

其中，LOWER函数是把字符串全部转换为小写；UPPER函数功能与LOWER刚好相反，它是把全部字符串转换为大写；CONCAT是用来连接两个字符串，类似于运算符‘||‘；SUBSTR是用来截取母字符串中的一个子串，在SQL中较常用到；LENGTH是来册字符串的长度；而NVL具有所有NVL的通用性，即是在列值为空时，用后者取而代之。 例：

SQL>SELECT substr(?STRING‘, 2,3) from dual SQL>TRI 数值函数

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

ROUND TRUNC MOD

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

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

8

Company Confidential - For internal use only

round(23535.7476, -1) ==> 23530 trunc(23535.7476, 2) ==>23535.74 trunc(23535.7476) ==>23535

trunc(23535, -1) ==> 23530

日期函数

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

MONTH_BETWEEN（date1，date2） ADD_MONTHS（date，n） NEXT_DAY（date，‘char’） LAST_DAY（date） ROUND（date[，‘fmt’]） TRUNC（date[，‘fmt’]） 日期函数有其特性： 例：

add_months(to_date(?2001-05-01‘,‘yyyy-mm-dd‘), 2) ==>2001-07-01 add_months(to_date(?2001-05-01‘,‘yyyy-mm-dd‘), -2) ==>2001-03-01 注意：

两个日期不可相加，但可做减法，结果为number型，日期型数据加/减数值型数据结果为日期型，日期型数据间不可做乘除法，其他运算借助于日期函数。 SYSDATE：

SYSDATE是一个返回系统日期和时间的函数，可以把SYSDATE作为任何表中都具有的普通列。 DUAL：

DUAL是用户SYS的一个表，可以被任何用户使用，它只包含一个列DUMMY，且只具有一行值“x”，这是一个非常有用的表，常用来返回一些常量、单值等等。 例如：

SQL>SELECT SYSDATE FROM DUAL 转换函数

很多时候，不仅要在同一类型中进行运算，还要把某一类型数据转换为其他类型，以例运算或显示，如下是用来进行处理数据或数据列的类型的类型转换函数：

TO_CHAR(number/date, [?fmt‘])

9

Company Confidential - For internal use only

TO_NUMBER(char) TO_DATE(char, [?fmt‘])

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

在to_date的函数中，注意[?fmt‘]的使用，区别‘yyyy‘ 和‘RRRR‘,‘RR‘,‘yy‘等，如下是to_date格式的详细情况： 格式 SCC or CC YYYY or SYYYY YYY or YY or Y Y,YYY IYYY, IYY, IY, I SYEAR or YEAR BC or AD Q MM MONTH MON RM WW or W DDD or DD or D DAY DY J 例：

SELECT TO_CHAR(sysdate,‘DAY‘) FROM DUAL;

SELECT TO_DATE(?2001-07-14‘,‘YYYY-MM-DD‘) FROM DUAL 单行函数使用的例子： 例：

例1：SELECT last_name||‘.‘||first_name, employee_id, manager_id

描述 世纪，S则在BC日期前加- 年，S则在BC日期前加- 年份的后3或2或1位数字 年份，中间以“，”号隔开 ISO标准的年份，分别为四位，三位，两位和一位 年的拼写，S则在BC日期前加- 年份的BC或AD的标示 季度 月份，数值 月份的全拼写 月份的拼写，前三位 罗马数字月份 一年中的星期或月中的星期 年或月或星期中的天 天的星期，全拼写 天的星期，前三位拼写 Julia天，从4713BC开始

10

Company Confidential - For internal use only

FROM s_emp

WHERE trunc(start_date) = to_date(?2001-05-12‘,‘yyyy-mm-dd‘) And length(last_name)<10

LESSON 4多表数据查询

在数据的检索中，常常要按一定的联系同时从多个表中获取数据，尤其是数据库设计时遵循范式规则，这是就是一种所谓的多表数据查询，本节从建立表的联系开始，讲述多表数据的查询：

多表数据查询的首要条件是如何把多个数据表按一定的规则连接起来，从而获取多个表的、组织好的信息。连接的方式有多种，可按不同的要求进行选择。 连接

即是最简单的等号连接，以分别两个表中某列建立联系： SELECT table.column, table.column FROM table1, table2 WHERE table1.column1=table2.colmn2; 表别名

在多表数据检索中，由于检索的数据列可能存在于多个表中，且具有同一列名，这时必须在列名前加上表名以示区别，而对于较长的表名，写起来太麻烦，故可用表别名来区分各个不同的表，如下：

SELECT T1.column, T2.column…… FROM table1 T1, table2 T2 WHERE T1.column1=T2.colmn2; 非等号连接

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

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

使用：>=、<、between

SELECT T1.column, T2.column…… FROM table1 T1, table2 T2 WHERE T1.column1=T2.colmn2（+）;

11

Company Confidential - For internal use only

自连接

对于某些情况，必须要有表自身的连接，这时可利用表别名的作用，即把表完全看作两个不同的表来处理

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

SELECT T1.fist_name, T2.first_name manager…… FROM t_emp T1, T_emp T2 WHERE T1.employee_id=T2.employee_id（+）; 对多于两个以上的表连接具有相同的处理方法，如下是一多表连接例子： 例：

SELECT t1.item_name, t2.quantity, t3.last_name FROM t_item t1,

T_sales t2, T_emp t3

WHERE t1.item_id=t2.item_id And t2.emp_id=t3.employee_id And t2.approval_flag=‘Y‘

LESSON 5组函数

在数据检索中，我们不仅仅要检索出数据信息，同时还需要一些加工，例如一些统计信息等等，而数据库表中的数据总是离散在许许多多的列中，要组织起来就会用到另一类列函数，即是组函数。

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

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expr] [HAVING group_condition] [ORDER BY column??] * GROUP BY: 对列进行分组 * HAVING: 条件限制组查询

12

Company Confidential - For internal use only

常用组函数

AVG、COUNT、MAX、MIN STDDEV、SUM、VARIANCE

组值函数如sum、avg、stddev等等，反映的是一组值的某种整体状况、一些统计信息。同时，要注意的是，组值函数忽略NULL值，在做计算时将其排除在外，如avg函数，如数据中含有较多的空值时，avg返回的统计信息偏差较大，而count函数出外，它是一种特殊情况，它可以处理NULL值，并总是返回一个数值，所求出的值决不会是NULL。 例：

SELECT T1.name, T2.item_name, sum(T3.quantity), min(T3.rec_date) FROM t_inventory T1,

T_item T_onhand

T2, T3

Where T1.warehouse_id=T3.warehouse_id And T3.item_id=T3.item_id GROUP BY T1.name, T2.item_name HAVING sum(T3.quantity)>100

所有的组值函数都有一个distinct或all选项，缺省的选项为all，count函数就是一个说明起作用的例子： 注意如下查询的结果：

SELECT count(distinct item_id), count(item_id), count(*) FROM t_onhand HAVIGN与WHERE：

WHERE语句作用在数据检索的每一列上，对每一列进行判定，HAVING语句则是作用在每一组上，对每一组进行判定，WHERE语句在HAVING起作用前作用。

考虑以下SQL语句的查询：

SELECT t1.last_name, sum(t2.quantity*t2.price) FROM s_emp t1, S_sales t2

WHERE t1.emp_id=t2.emp_id And nvl(t2.cancel_flag,‘N‘)<>‘Y‘ And nvl(t2.approval_flag,‘N‘)=‘Y‘

13

Company Confidential - For internal use only

Having sum(t2.quantity*t2.price)>10000;

LESSON 6 子查询

子查询是一段嵌入在另一段SQL语句中SELECT语句，通过子查询，可以建立功能强大的查询语句，尤其在检索某以数据表时，又依奈这张表本身的一些情况。 格式

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table) 注：

I. II. III.

Expr 与子查询的select_list有相应的结构和数据类型 子查询无order by 语句 子查询必须在operator的右面

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

SELECT t1.item_id, t1.quantity, t1.rec_date FROM t_onhand t1

WHERE rec_date = ( SELECT min(x.rec_date) FROM t_onhand x

WHERE x.item_id = t1.item_id)

当然，很多时候子查询也可由组函数实现，比较一下下面查询的结果与前者的情况： SELECT T1.item_id, T1.quantity, T1.rec_date FROM t_onhand T1,

(SELECT item_id, min(rec_date) rec_date FROM t_onhand) T2 WHERE T1.item_id = T2.item_id And T1.rec_date = T2.rec_date

可用于operator的运算符很多，分为单行子查询和多行子查询，单行子查询即是在自查询的结果中，至多返回一行值，可用于单行子查询的逻辑运算符有： =、>、<、>= 或者<=

多行子查询相对于单行子查询，即在子查询中可返回多行数据结果，可用于多行子查询的逻辑运算有：

14

Company Confidential - For internal use only

in、not in

还有一种存在性相关子查询，即exists语句，它与in子查询相似，但有两点不同：

i. 不能匹排一列或多列 ii.

只能用于相关子查询

例：

SELECT T1.item_name, T1.description FROM t_item T1

Where exists ( SELECT * from t_sales

WHERE item_id = T1.item_id) Company Confidential - For internal use only

15

UNIT3数据库

单元培训目标

?

理解Oracle数据库的概念和作用；

能创建常用的数据库对象（如表、索引、视图和序列）； 学会使用数据库字典，记住一些常用视图

?

?

LESSON 1系统开发周期和数据关联

大型数据库应用系统的开发都是以一定的标准流程开发的，而重要的一条就以系统开发周期为参照，定制开发流程和开发计划，逐步实施。

深刻理解数据关联的概念时非常重要的，这对数据库应用系统的设计，具体到数据库对象的设计都是深刻影响的，一个性能优越的数据库应用系统很大程度上依耐于数据库结构的设计，而理解数据关联的实质有利于数据库的设计。 系统开发周期

通过使用系统开发周期策略，实现数据库从概念到产品的转变，系统开发周期包含多个开发过程和步骤。这种由上到下、系统的方法可把实际的业务需求变成可操作的数据库概念。 有如下步骤（流程）： I.

分析，策略： 根据企业的实际业务和将要扩展的业务需求，分析所要提供的信息和企业及系统特性，建立相应系统模型；

设计：数据库设计，把实体模型映射成数据库表之间的关联，外键关联和约束；

开发和创建文档：开发业务流程，建立用户界面，操作流程，并创建相应文档（如帮助文档、操作手册等）

产品化：集成各模块功能，统一成一个完整产品，并做好各项测试准备； 测试：功能测试、业务流程测试、出错流程测试最后的安装测试等。

II.

III.

IV. V.

在这几步步骤中，第二步尤为重要，直接影响开发的成败和效率，在第二步中，应用开发者重点要要考虑以下一些因数：

效率：最初的设计是最终设计的一个雏形，要充分考虑业务的效率；

系统集成：系统的多个模块间不是孤立的，应有紧密的结合，这种结合并非看起来或感觉上的，是从逻辑上和业务流程上的角度来分析的；

与其他系统的集成：企业与运作时，往往有多个系统的存在，但同时各个系统间是有结合的、联系的，如数据上、业务上的，同时还要考虑未来业务需求的标准接口等等；

16

Company Confidential - For internal use only

数据关联

系统开发周期第二步的重点工作就是建立逻辑模型，逻辑模型是业务数据的非常规范化的流程图。懂得数据分解的原因和方法是理解模型的基础，而模型又是建立长期支持业务应用程序的基础。

规范化的过程通常用“范式“一词来讨论，最常见就是第一、二、三范式，其中第三范式是最高的规范化层次。 I.

第一范式(1NF)：具体做法就是将每一个表中具有相似类型的数据存入不同的表中，并给每个表确定一个主关键字（即是：Primary Key）----- 唯一的标号或标示符，消除重复数据；

第二范式(2NF)：即是表中数据列只依赖主关键字；

第三范式(3NF)：即是找出不只是依赖于主关键字的说有数据列。

II. III. 约束

为实现数据结构的规范化标准，即是各级范式，可以在定义表结构时，定一各个列和表的约束，从而严格表中的数据，格式如下： 数据列级约束：

Column [ CONSTRAINT constraint_name ] constraint_type Column [ CONSTRAINT constraint_name ] constraint_type 数据表级约束： 约束主要要以下几种： I. II.

Primary Key(PK)：由一列或多列构成的唯一标示各行的列；

Foreign Key(FK)：即由表中一列或列组合来映射同一表或别的表的PK或UK(unique key); NOT NULL UNIQUE CHECK

III. IV. V.

对于建立约束的例子，可在建立数据表中参考。

LESSON ２创建表、序列、视图和索引

下面从最基本的数据库对象建立讲起，逐步理解系统开发周期的意义，以及数据关联的意义。

17

Company Confidential - For internal use only

创建表格式 Schema

所有者标识

CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [ column_constraint ], ?? [table_constraint ]); Table Column Datatype

列表名 列名

列的数据类型 列约束 表约束

Column_constraint Table_constraint 例：

CREATE TABLE s_dept (id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY KEY, Name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL, Region_id NUMBER(7) CONSTRAINT s_dept_region_id_fk REFERENCES S_region (id), CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id)); 很多时候，数据库表的设计都可以通过表的示例图来设计，通过先创建数据表的示例图，再来创写数据表的角本，这是一种非常有效的方式，如下： 例：通过如下数据表的示例图，创建数据表t_emp： Column Name Key Type Nulls/Unique FK Table FK Column Datetype Length

ID Last_Name NN Userid Start_date Commission_pct Dept_ID PK NN,U NN,U FK ID Num 7 Char 25 Char 8 Date Num 6 Num 7

18

Company Confidential - For internal use only

创建序列格式

CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUEn|NOMAXVALUE}] [{MINVALUEn|NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}] 说明： 标示 Sequence INCREMENT BY START WITH n MAXVALUE n NOMAXVALUE MINVALUE n NOMINVALUE CYCLE|NOCYCLE 说明 建立的序列名 序列数字间的间隔，缺省为1 第一个序列值，缺生为1 序列的最大值 确定序列的最大值，为10的27次方 序列的最小值 确定序列的最小值，为1 确定序列值达到最大值时，是否重复开始，缺省为NOCYCEL 确定数据库管理器于值和存储的值的个数，缺省为NOCACHE CACHE n|NO CACHE

序列常用来给一个表的ＰＫ列赋值 序列有NEXTVAL和CURRVAL方法。 例：

CREATE SEQUENCE t_item_s

Increment by 1 Start wih 1 Maxvalue 999999 Nocache Nocycle;

是运行如下命令，看看结果：

19

Company Confidential - For internal use only

select t_item_s.nextval from dual; select t_item_s.currval from dual; 创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[,alias]?)] AS subsequery [WITH CHECK OPTION [constraint]] [WITH READ ONLY] 注：

I. Subquery可以是非常复杂的select语句 II. Subquery不能包含ORDER BY语句

实际上，视图是一个基于数据表或其他视图的逻辑数据表，一个视图本身并不包含数据，仅仅是一个可以用来查看数据或更新的窗口，一个视图在数据字典中是以一个select语句存储的。 使用视图有着许多优点：

i.

限制数据表的访问：由于它是一条select语句，通过选择的取舍，使用户只能看到或只需看到所要检索的数据列；

允许用户通过一个视图检索多个表的信息，而不要去写复杂的SQL语句；

ii.

创建索引 I.

自动创建

在创建表时，若包含Primary Key或者其他唯一约束时，一个唯一索引就自动创建立了。 II.

手动创建

用户可以创建非唯一的索引来提高检索速度。

CREATE INDEX index ON table (column[, column]?); III.

何时建立索引：

列经常在where或连接中使用； 列包含了一个大范围的值； 列包含了大量的空值；

两个或多个列常常在where或连接中一起被使用； 表的数据量大，而常常检索其中的2-4%；

20

Company Confidential - For internal use only

太多的索引并不总是能提高检索速度。 表空间的概念：

Oralce把文件作为其组织系统的一部分，但是它的逻辑结构已超出文件的概念。一个表空间就是一片磁盘区域，它有一个或多个磁盘文件组成。一个表空间可以容纳许多表、索引或是试图等等。由于表空间的大小是固定的，所以在向表中间增加行时，可能装满整个表空间。可以由拥有DBA权限的用户或是自动扩展这个表空间，其过程是先创建一个磁盘文件，然后将此文件增加到表空间中，或是直接扩大已存在的数据文件，因此，这些表中的行就会分布于两个文件中。一个或多个表空间一起就构成了数据库。

每一个数据库均包含一个系统表空间，这里保存业者的记录和地址，它包含数据字典、数据库的表空间、表、索引的名称和地址。 下面是一个创建表空间的示例：

create table space usertemp datafile ?HOME.ONE‘ size 1000k default storage (initial 25k next 10k) minextents 1 maxextent 100

pctincrease 0)

permanent;

LESSON 3Oracle数据字典

Oracle数据字典是Oracle数据库管理系统的重要组件之一，它包含一系列数据表和数据视图，提供只读属性的数据库信息。

Oracle数据字典实在数据库建立的时候建立的，又Oracle数据库管理器进行更新和维护，提供Oracle数据库用户、各个用户的权限、数据库对象、数据表约束等等信息。 Oracle数据字典的四大类视图：

- user - all - DBA - v$

用户拥有的对象 用户可访问对象 所有数据对象 服务器性能对象

其他视图：

- DICTIONARY

显示所有数据字典的数据表、视图和同义词

显示数据对象的所有者、授权者和权限

- TABLE_PRIVILEGES - IND

User_indexes的同义词

常用视图

DICTIONARY、TABLE_PRIVILEGES、IND

21

Company Confidential - For internal use only

ALL_TABLES、USER_OBJECTS 数据字典使用示例：

例一：SELECT distinct object_name,

object_type FROM usr_objects

例二：SELECT *

FROM user_constraints WHERE table_name =‘T_ITEM‘

LESSON 4用户访问控制：

在一个多用户数据库环境，必须维护数据访问和使用的权限，Oracle数据库管理器提供以下安全机制控制：

I. II. III. IV.

控制数据库的访问；

提供数据库对象的局部访问； 通过数据字典验证赋予和接收特权； 为数据库对象创建同义词。

数据库安全分为两类，一是系统安全，二是数据安全。系统安全控制数据库的访问和使用这一层次，例如用户和密码，用户能使用的数据空间，用户允许使用的操作系统等等；数据库安全控制数据库对象的访问和使用，以及能在这些数据库对象上能进行的操作。

权限的概念即是执行特定SQL语句的权利。数据库的系统管理员具有最高的权限，普通用户必须有系统管理员赋予权限来访问数据库和数据库对象，当然，用户也可具有赋予其他用户或角色的权利。

系统权限：

DBA具有最高级系统权限，典型的系统权限有： 系统权限 CREATE USER DROP USER DROP ANY TABLE BACKUP ANY TABLE 创建用户格式：

CREATE USER user IDENTIFIED BY password 操作授权 赋予创建其他Oracle用户权限 删除任何用户 删除任何束中的表 备份任何束中的表

GRANT privilege [, privilege ……] TO user [, user ……] Company Confidential - For internal use only 22

系统权限授权格式：

角色：即是一组相似的权限，可以用来同一授权给一些用户。这样把相似的权限统一起来，易于给用户授权和维护系统权限，同时，一个用户也可以被授予多个角色权限。 用例如下：

CREATE ROLE role; GRANT create table, create view TO manager; GRANT manager TO userA, userB; 对象权限：

GRANT { object_priv (, object_priv …) | ALL } [ ( columns ) ] ON object TO { user [, user … ] | role [PUBLIC] [WITH GRANT OPTION] 注意以下几点： 1. 2. 3. 4.

DBA可授权给用户，对特定的对象执行某项操作； 对象权限因对相的不同类型而有区别； 对象的所有者拥有对象的所有权限； 对象的所有者可以对对象授予特定的权限。

格式如下： 例：

GRANT update ( last_name, first_name, address ) ON t_emp TO manager

在Oracle系统中，可通过如下数据字典查看用户权限情况： 数据字典表 ROLE_SYS_RPIVS ROLE_TAB_PRIVS USER_ROLE_PRIVS 说明 授予角色的系统权限 授予据色的表权限 用户可使用的角色 USER_TAB_PRIVS_MADE 用户对象所授予的对象权限 USER_TAB_PRIVS_RECD 用户所授予的对象权限 USER_COL_PRIVS_MADE 用户对象列所授予的对象权限 USER_COL_PRIVS_RECD 用户在特定的列上所授予的权限

23

Company Confidential - For internal use only

权限的回收：

通过使用revoke命令可以收回授予其他用户的权限，同时授予用户的with grant option也会收回。 格式如下：

REVOKE {privilege [, privilege …] | ALL } ON object FORM { user [,user …] | role | PUBLIC } [CASCADE CONSTRAINTS] 例：

REVOKE select, insert ON S_EMP FORM manager;

创建同义词：

为了引用其他用户的某个表，必须使用这个表的全称，即是用户名加表名，通过使用同义词就可避免这个麻烦，可以引用其他用户的表、视图、序列、过程或是其他别的对象。 格式如下：

CREATE [PUBLIC] SYNONYM synonym FOR object; 说明： PUBLIC Synonym Object 例：

CREATE SYNONYM T_EMP FOR scott.t_tmp;

远程数据库的访问：

通过使用数据库链接，用户可以将一组分布的数据库作为一个单一的、集成的数据库来处理。

数据库链接需要SQL*NET运行在远程数据库访问中所涉及到的每一台主机上（“HOSTS”），通常SQL*NET由数据库管理员（DBA）或系统管理员启动。

创建数据库链接语法：

所有用户都可访问的同义词 创建的同义词名 同义词的引用源

24

Company Confidential - For internal use only

Create [public] database link remote_connect Connect to username identified by password Using ?connect string‘ 当建立数据库链接是具体使用的语法取决于下面两个条件： 1. 2.

数据库链接的“公共”或“私有”状态。 使用缺省还是显示方式登录远程数据库。

为了建立数据库链接，必须拥有CREATE DATABASE LINK系统权限，用来连接远程数据库的账号必须拥有CREATE SESSION特权。这两种系统特权都包含在Oracle中的CONNECT角色中。

通过使用数据库链接和同义词来隐藏数据库的物理存放位置，如下：create synonym emp for emp@remote_connect;

Company Confidential - For internal use only

25

UNIT4 PL/SQL

单元培训目标

?

掌握PL/SQL常用语法； 使用Oracle Procedure Builder；

能创建包、函数和过程解决一些复杂问题； 掌握出错流程处理

?

?

?

LESSON 1简单PL/SQL

PL/SQL是Oracle扩展的、具有程序设计特色的程序语言，它可以把数据维护和数据检索容入在程序过程当中。 PL/SQL结构

DECLARE -可选项 - 变量，常数，游标，用户定义的例外 BEGIN - Mandatory - SQL 语句 - PL/SQL 控制语句 EXCEPTION – 可选项 - 例外处理 END; Declarative［optional］：包括所有变量、常量、游标和用户定的例外等 Executable [Mandatory]：包括各种SQL代码和PL/SQL代码等 Exception Handling [optional]：对各种例外情况的处理

通常，每一个PL/SQL单元包含一个或多个块结构，这些块之间可以是完全独立的，也可以是一个块在另一个块里面。即是，一个块又能被分为若干个更小的块。 下面是一些典型的PL/SQL程序结构： 程序结构 匿名块 说明 没有特定名称的PL/SQL块，存在于APPLICATION中 命名PL/SQL块，能接受参数和重复调用 命名PL/SQL块，能接受参数和重复调用 命名PL/SQL模块，包含相关的Procedure、function使用位置 所有PL/SQL环境 数据库procedure、function Application Procedure、function Package Oracle database Server Developer 2000的组件，例如FORMS Oracle database Server

26

Company Confidential - For internal use only

和变量 Database trigger 与数据库表联系在一起的PL/SQL块，能自动触发 与Application事件联系在一起的PL/SQL块，能自动触发 Oracle database Server Application trigger Developer 2000的组件，例如FORMS

匿名块，过程和函数 Ｉ．匿名块

[DECLARE] BEGIN -- 语句 [EXCEPTION] END; II．过程

PROCEDURE name is BEGIN -- 语句 [EXCEPTION] END; III．函数

FUNCTION name RETURN datatype IS BEGIN -- 语句 RETURN VALUE [EXCEPTION] END;

过程 作为一段PL/SQL代码执行 没有返回数据类型 可以返回一个或多个值 包

包是被集中到单独一个单元的一组过程、函数、变量和SQL语句。为了执行一个保中

过程与函数的对比：

函数 作为一个表达式调用 必须包含返回数据类型 必须且只能返回一个值

27

Company Confidential - For internal use only

的过程，必须首先列出名称，接着是过程名称，如下：

execute Test_sales_information_pkg.init_item_price(p_warehouse_id?1 ); 包允许多个过程使用相同的变量和数据指针。包中的过程和函数，可为公共访问或为专用。

在创建包时，包的定义和包内容被分别地生成。从而，有两个命令create package和包内容的create package body命令。必须具备create procedure系统权限。 1.

建立包定义的语法：

create [or replace] package [user.]package {is |as} PL/SQL package specification; 包的内容由所有列在包定义中的公用对象的PL/SQL代码块及其定义组成。包的内容也可包括未列在包定义中的对象。 2.

包的初始化：

一个包中可以包括代码，在每次用户执行此包时最先被运行。 Create or replace package body test_sales_information_pkg As User_name varchar2(20); Entry_date date; … … begin select user, sysdate into user_name, entry_date from dual; end test_sales_information_pkg; 3.

包的编译和代码察看：

当对象在创建时，Oracle就编译它们，然而，当它们所引用的的数据库对象发生变化时，就有可能变为变为非法的操作。在这些对象下次执行时，它们被数据库重新编译。也可显示的重新编译这类对象，以提高数据库处理的性能，语法如下： alter package [user.]package_name compile [package| body];

可以通过查询下面的数据字典视图获得当前过程、函数、包和包的内容的源代码： USER_SOURCE ALL_SOURCE DBA_SOURCE

对于用户所有的过程对象

对于用户所有的或者被授权访问的规程对象

对于数据库中所有的过程对象

检索的列为TEXT并按照LINE（行号）顺序。

28

Company Confidential - For internal use only

触发器

触发器定义了当一些数据库相关事件发生时数据库应采取的动作。触发器可用于增加描述性的应用完整性，加强复杂业务的规则，或者监控数据的变动。

所需权限:

1.

所需系统特权：

为了在标上建立一个触发器，必须能够对标进行改动的，所以用户必须在表上具有ALTER权限，或者具有ALTER ANY TABLE系统权限。此外用户还需有CREATE TRIGGER系统特权，要在SCHEMA上创建触发器，必须具有CREATE ANY TRIGGER系统特权。

同时，要更改一个触发器，必须拥有触发器或者具有ALTER ANY TRIGGER系统特权。 2.

所需表权限：

除了激活触发器事件的表外，触发器还可以应用其他的表，故还必须拥有处理其他表的权限

触发器类型：

1． 行级别（Row_Level）触发器

对于处理中的每一行，行级触发器都执行一次。这是最常用的触发器类型，常用于数据监控和审计应用中，数据库快照（SNAPSHOT）就是使用了行级触发器。

2． 语句级别（Statment_Level）触发器

语句级触发器对于每个处理执行一次，他们常被用于对在某个表上执行的处理类型提供额外的安全手段。语句级触发器通过命令Create trigger建立缺省触发器类型。 3． Before和After触发器

由于触发器是由事件引发的，所以他们可以被设置成在这些事件之前或之后立即触发。在触发器中，用户可以引用此处理涉及到的旧值或新值。

4． Instead of触发器

可以使用Instead of触发器来告诉Oracle应执行什么操作。

29

Company Confidential - For internal use only

触发器语法：

Create [or replace] trigger [usr.]trigger {before | after | onstead of} delete | insert | update [ of culumn [,column]…]} [or {delete | insert | update [of column[,column]…]}]… on [usr,]{TABLE |VIEW } [[referencing {old as old | new as new} …] for each {row |statement } [when (condition)]] pl/sql_block 使用示例：

create trigger onhand_bef_upd_row befor update on t_onhand for each row

when(new.quantity/old.quantity>1.1) begin

insert into t_onhand_audit

values(:old.warehouse_id, :old.item_id, :old.quantity, :old.rec_date);

end;

注意：当引用PL/SQL代码块中的关键字NEW和OLD时，必须以冒号（：）开头。在触发器中，也可使用用户自定义的例外处理。

其他操作：

激活和取消触发器：

alter trigger onhand_bef_upd_row enable(/disable); 或是：

alter table t_onhand enable(/disable) all trigger; 删除触发器：

drop tirgger onhand_bef_upd_row。 PL/SQL环境

PL/SQL是一项绑定在Oracle 数据库服务器上的技术，通过PL/SQL引擎进行传输和处理。

当一段PL/SQL块从Pro* 程序、user-exit、SQL*Plus 或是Server Manager中提交后，Oracle数据库服务器上的PL/SQL引擎就开始处理他们。PL/SQL引擎首

30

Company Confidential - For internal use only

先把块中的PL/SQL代码分成独立的若干块，在把他们传送到SQL语句执行器。这也意味着尽量采用少的PL/SQL块传输到Oracle数据库服务器，从而提高客户-服务器网络构造工作的高效性。

在许多Oracle工具中，包括 Developer/2000，拥有他们自己的PL/SQL引擎，这是完全独立Oracle数据库服务器，且在之前的引擎。 Oracle Procedure Builder的使用

结构化程序设计的最大有点在于能快速、简单的创建和调试代码，Procedure Builder 提供所有必要的功能，以利于成功开发和调试PL/SQL程序。 组要构件： 组件 对象浏览器 PL/SQL解释执行器 说明 管理PL/SQL结构，执行调试 调试PL/SQL代码，执行PL/SQL代码 创建和编辑PL/SQL代码 创建和编辑服务器端PL/SQL代码 创建和编辑数据库触发器 程序编辑器 数据库程序编辑器 数据库触发器编辑器 对象浏览器：

Program Unit: 能独立被PL/SQL编译器识别和处理的PL/SQL结构； Program Unit – Sepcification: 程序单元的名称、参数和返回类型； Program Unit – Referances: 程序单元引用的过程、函数、匿名块和表； Program Unit – Referanced By: 引用程序单元的过程、函数、匿名块和表； Libraries: 文件或数据库中PL/SQL包、过程和函数的集合； Attached Libraries: 引用的数据库或文件系统中的库； Built-in Packages : 在程序调试中可引用的PL/SQL结构单元； Debug Actions: 在程序单元调试中，可进行的监测、中断的命令； Stack: 子程序调用堆栈

Database Objects: 服务器端存储的过程、库、表和视图。 PL/SQL解释执行器:

通过使用PL/SQL解释执行器，可以定义、显示、调试和运行PL/SQL程序，同Procedure_name [(argument1 [, argument2, ….])]; 时，在PL/SQL解释执行器中，可以直接运行过程，以分号结束，如下：

31

Company Confidential - For internal use only

在PL/SQL解释执行器中，也可执行SQL DDL语句和其他命令语句。

程序编辑器：

1.

创建一个新程序单元： i. ii. iii.

选中程序单元；

点击创建按钮，弹出程序对话框窗口；

出入新建程序单元名称，并选择程序类型，点击OK键；新建程序单元显示在对象浏览器上； 在打开的程序单元内，编辑。

iv. 2.

程序编译

点击程序编辑窗口中的compile按钮，编译程序单元，编译信息在程序编辑器下方显示，包括各种出错信息或编译成功信息。

调试数据库程序：

1.

总体步骤：

i. 把数据库程序单元载入到对象浏览器中； ii. iii. iv. v.

选中要调试的程序单元； 用鼠标将其拖到程序单元中； 进行调试工作；

用鼠标间调试好程序单元拖回到原数据库单元中。

调试方法：

在PL/SQL解释执行器中调试程序，可以双击代码行，设置断点（可以设置多割断点）；同时，还可以设置局部变量和参数，如下： (debug1)PL/SQL> debug.seti(?I‘,3)

程序中的参数和变量类型

参数：

参数有如下三种类型： IN Argument OUT Argument IN OUT Argument

在参数格式中，IN 可以省略，它也是缺省的形式；IN 代表该参数用于向程序中传递值，OUT代表由程序单元返回给调用它的函数值，而对于 IN

32

Company Confidential - For internal use only

OUT 型参数，代表既可向子程序传递值，同时子程序也可返回值给主程序。

变量类型

通用数据类型： 数据类型 BINARY_INTEGER 说明 基本数值整型，-2147483647 ----- 2147483647 基本浮点数值型 固定长度的字符型，最大值为32760 可变常字符型，最大长度为32760 二进制型，最大长度为32760 NUMBER[(precision,scale)] CHAR[(maximun_length)] LONG LONG RAW VARCHAR2(maximum_length) 可变长字符型，最大长度为32767 DATE BOOLEAN

程序单元（过程、匿名块和函数）中变量的声明： Identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; 注意：具有初始值的常量和变量被认为是NOT NULL的，一行最多只有

一个常量或是变量的声明 %TYPE类型：

这是一种动态数据类型，在PL/SQL中动态决定器数据类型。它有两大优点：

1． 决定其数据类型的数据库表列的类型未知；

2． 决定其数据类型的数据库表列的类型在运行时是可变的。

为了在PL/SQL中保存某个列的数值，必须确保变量与列具有相同的数据类型和精度，否则，常会造成PL/SQL错误或是精度的丢失。

而%TYPE类型是由某一个预定的变量或时数据库表中列决定的，这样就可以确保数据类型的一致。 例： … … 日期和时间类型 逻辑型（TRUE、FALSE或NULL） l_last_name l_salary t_emp.last_name%TYPE t_emp.base_salary%TYPE

33

Company Confidential - For internal use only

复合数据类型： 5． PL/SQL TABLES ：

它有两部分组成：I) BINARY_INTEGER型的Primary Key; 注意：

i. PL/SQL TABLES数据类型并不等同于数据库中的表； ii. iii. iv.

PL/SQL TABLES数据类型类似一个一维数组； PL/SQL TABLES类型包含两个组件； PL/SQL TABLES类型可动态增长。

II) 数据的列范围。

声明如下：

TYPE type_name IS TABLE OF scalar_datatype [NOT NULL] INDEX BY BINARY_INTEGER; identifier type_name; 例：

… TYPE name_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; First_name_table name_table_type; … first_name_table(1):=‘FRANCK‘; 6． PL/SQL RECORDS：

它包含一个或是一个以上的任何范围、记录、PL/SQL TABLES类型的组件。 注意： i.

PL/SQL RECORD数据类型并不等同于数据库中的表中的一条记录；

PL/SQL RECORD数据类型类似一个多维数组； PL/SQL RECORD类型包含多个组件； PL/SQL RECORD类型可动态增长；

PL/SQL RECORD类型方便地从一个表中获取一行数据，以便于PL/SQL的处理。

v. vi. vii. viii.

声明如下：

TYPE type_name IS RECORD (field_name1 field_type [NOT NULL { :=|DEFAULT} expr], (field_name2 field_type [NOT NULL { :=|DEFAULT} expr], …); identifier type_name; Company Confidential - For internal use only

34

例：

… TYPE emp_record_type IS RECORD (last_name varchar(25), base_salary number, manager_id number); employee_record emp_record_type; … employee_record.last_name:=‘FRANCK‘;

LESSON ２数据获取和游标

在本节中，主要讲述如何在PL/SQL中访问数据库、获取数据库数据，同时，介绍游标的使用。 数据获取 SELECT INTO FROM WHERE I. II. III.

select_list variable_name | record_name table condition; INTO 语句是必须的 Select 只有一行数据返回 Select_list 不能为简写( eg. *)

检索的数据列，可以包括SQL表达式、单行函数或是组函数；

是一系列变量，用来存储检索的数据值；

PL/SQL RECORD变量，用来存储检索的数据值； 数据库表；

一系列约束条件，实现数据的检索。

Select_list

Variable_name Record_name Table Condition

一个PL/SQL块并非是一个事务处理单元。Commit、savepoints、和rollback是独立的块，但是可以把这些命令写入在一个PL/SQL块中。PL/SQL块不支持数据定义语言(DDL)，例如CREATE TABLE，ALTER TABLE，或是DROP TABLE。PL/SQL也不支持数据控制语言(DCL)，例如GRANT或是

REVOKE。但是，可以通过在PL/SQL块中，使用DBMS_SQL包来实现DDL和DCL功能。

35

Company Confidential - For internal use only

使用范例：

FUNCTION sum_emp(v_dep_id IN NUMBER) RETURN NUMBER IS L_sum_salary number; BEGIN SELECT sum(salary) INTO l_sum_salry FROM t_emp WHERE dept_id = v_dept_id; RETURN (L_sum_salary); END; 若有零行或是多于一行数据返回 ，则会触发SELECT的例外：TOO_MANY_ROWS（多于一行数据返回） NO_DATA_FOUND（未检索到一条数据） 在PL/SQL块中，还可以使用其它DML命令： INSERT 插入一行数据到数据库的某个表中去； UPDATE 更新数据库表中的某一行数据； DELETE 从数据库表中删除一行不要数据。 游标

在中使用SQL时，数据库服务器总是在内存区开闭一块空间来存储这段SQL的解析和执行结果，这个区域就称为游标。游标有显式游标和隐式游标两种，当在程序块中使用SQL检索时，PL/SQL就自动创建了一个隐式游标，PL/SQL自动管理这个游标；显式游标是由编程者自行定义和命名。游标建立后，在PL/SQL中有四种属性可使用：

SQL%ROWCOUNT 返回最近的SQL语句检索行数，整型 SQL%FOUND

判断最近的SQL语句执行的结果，若返回一行或多行，则为TRUE，Boolean型

SQL%NOTFOUND 判断最近的SQL语句执行的结果，若未返回一行，则为

TRUE，Boolean型 SQL%ISOPEN

对于隐式游标，值总是FALSE，因为隐式游标在执行后立即自动关闭

显式游标声明：

CURSOR cursor_name IS Select statement……; 同时，显式游标也可以是带参数的,如下：

CURSOR cursor_name(parameter and parameter list) IS Select statement……;

36

Company Confidential - For internal use only

隐式游标例子：

RPOCEDURE del_rows(v_warehouse_id number) IS V_rows_del number; BEGIN DELETE FROM t_onhand WHERE warehouse_id = v_warehouse_id; V_rows_del:=SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_rows_del)||‘ rows deleted.‘); END;

LESSON ３PL/SQL控制流

在本节中，主要讲述在PL/SQL块中的流程控制。PL/SQL中有两种流程控制结构，一是IF语句的条件控制结构，二是LOOP语句循环控制结构。 IF 语句

IF condition THEN 语句； [ELSEIF condition THEN 语句；] [ELSE 语句；] END IF; 类似于其他结构化程序语言，它是一种条件控制流。 注意： 1. 2. 3. 4.

ELSIF是作为一个单词； END IF是两个单词； 允许多个ELSIF语句； 至多只有一个ELSE语句。

使用范例：

… if v_salary<2000 then return v_salary*0.05; elsif v_salary<3000 then return v_salary*0.1; elsif v_salary<5000 then return v_salary*0.15; else return v_salary*0.2 end if; … Company Confidential - For internal use only 37

本文来源：https://www.bwwdw.com/article/3uc3.html