回 帖 发 新 帖 刷新版面

主题:Oracle PL/SQL从入门到精通

Oracle PL/SQL从入门到精通

丁士锋  等编著













清 华 大 学 出 版 社
北  京
内 容 简 介
本书以面向应用为原则,深入浅出地介绍了Oracle平台上使用PL/SQL语言进行数据库开发的技术。通过大量的示例,详细介绍了PL/SQL的语言特性、使用技巧,同时配以两个在实际工作中的案例深入地剖析了使用PL/SQL进行Oracle开发的方方面面。
本书附带1张DVD光盘,内容为作者为本书录制的全程语音教学视频及本书所涉及的源代码。
本书分为5大篇共20章。涵盖的内容主要有PL/SQL语言基础、开发环境、变量与类型、控制语句、数据表的管理和查询、数据表的操纵、使用PL/SQL的记录与集合、各种内置函数、游标、事务处理、异常处理、子程序、包、面向对象的开发等技术点。通过示例性的代码,由浅入深,详细介绍了每一个技术要点在实际工作中的应用,对各种技术要点的应用场合进行了细致的分析。
本书适合于使用PL/SQL进行应用程序开发的人员、对软件开发有兴趣的学生及爱好者阅读和参考;对数据库管理员、企业IT运维人员也具有很强的指导作用。


本书封面贴有清华大学出版社防伪标签,无标签者不得销售。
版权所有,侵权必究。侵权举报电话:010-62782989  13701121933

图书在版编目(CIP)数据

Oracle PL/SQL从入门到精通 / 丁士锋等编著. —北京:清华大学出版社,2012.6
ISBN 978-7-302-28103-0

Ⅰ. ①O…  Ⅱ. ①丁…  Ⅲ. ①关系数据库-数据库管理系统,Oracle  Ⅳ. ①TP311.138

中国版本图书馆CIP数据核字(2012)第030492号


责任编辑:夏兆彦
封面设计:
责任校对:徐俊伟
责任印制:

出版发行:清华大学出版社    
网    址:http://www.tup.com.cn, http://www.wqbook.com
地    址:北京清华大学学研大厦A座        邮    编:100084
社 总 机:010-62770175                邮    购:010-62786544
投稿与读者服务:010-62776969,c-service@tup.tsinghua.edu.cn
质量反馈:010-62772015,zhiliang@tup.tsinghua.edu.cn
印 刷 者:
装 订 者:肖  米
经    销:全国新华书店
开    本:185mm×260mm     印    张:42.25       字    数:1055千字
          (附DVD 1张)
版    次:2012年6月第1版                  印    次:2012年6月第1次印刷
印    数:1~5000
定    价:25.00元
产品编号:045147-01
[b]当当地址:http://searchb.dangdang.com/?key=%20%20%20Oracle%20PL[/b]

回复列表 (共54个回复)

11 楼



[img]http://www.tu265.com/di-7fdd5f5eead760e7efe94a7115dc30ec.png[/img]
通过使用如下所示的GRANT语句来为scott授予创建任何方案下的表、视图和过程。

GRANT  CREATE ANY TABLE,
        CREATE ANY VIEW,
        CREATE ANY PROCEDURE
        TO scott;

注意:在Oracle中,DDL语句具有多种权限限制,一般只有具有管理员权限(DBA)才能建立数据库对象,不过通过GRANT和REVOKE语句,可以显式地为任何用户分配权限。
在为数据库表命名时,应该遵循Oracle数据库对象的标准命名规则,如下所示。
    表名和列名必须是具有描述性名称的字符串,以字母开头,且长度必须在1~30个字符以内。
    表名中只能包含字符A~Z、a~z、0~9、_(下划线)、$和#(这两个字符虽然合法,但并不建议使用)。
    表名和列名不能与同一Oracle服务器用户拥有的其他对象重名。
    表名和列名不能是Oracle服务器的保留字。
注意:表名和列表都是不区分大小写的。
在CREATE TABLE语句的括号中,定义了列名,并使用Oracle内置的数据类型指定了列的类型,关于Oracle内置的数据类型,请参考本书第3章的内容。
除了指定列的类型之外,还可以使用列类型属性来指定列的基本约束,常用的列特性有如下3个。
    NOT NULL:指定列不接受NULL值,如果省略该值,列将允许接受NULL值。
    UNIQUE:指定存储在列中的每一个值都必须唯一。
    DEFAULT default_value:指定列的默认值。
例如下面的代码5.3使用CREATE TABLE语句创建了一个发票表invoices,使用了列类型属性来对列进行基本的约束。
代码5.3  使用列约束创建表
CREATE TABLE invoice
(
   invoice_id NUMBER NOT NULL UNIQUE,                  --自动编号,唯一,不为空
   vendor_id NUMBER NOT NULL,                           --供应商ID
   invoice_number VARCHAR2(50)  NOT NULL,              --发票编号
   invoice_date DATE DEFAULT SYSDATE,                   --发票日期
   invoice_total  NUMBER(9,2) NOT NULL,                  --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0                 --付款总数
)

上述代码通过对列属性的使用,使得一些列的值不能为NULL;一些列具有DEFALUT指定的默认值;而一些列的值必须在整个表的相同的列中唯一。
下面的语法是创建一个表的最基本的语法:

CREATE TABLE [schema_name.]table_name
(
   column_name_1 data_type [column_attributes]
   [,column_name_2 data_type [column_attributes]]...
   [,table_level_constraints]
)

Oracle为表的创建提供了大量的参数,一些参数可能需要DBA来参与辅助设置,对于PL/SQL的开发人员来说,只需要了解这些基本的语法就可以完成很多的工作了。
为了创建表,必须深入理解业务实体的数据类别及数据存储的容量,Oracle内置了一系列的数据类型允许用户使用来创建一个表。在本书介绍PL/SQL数据类型时曾经介绍过,PL/SQL支持访问所有的Oracle数据类型,Oracle数据类型可分为标量、复合、引用和LOB4种类型,要了解数据类型的详细信息,可以参考本书3.2节对数据类型的介绍。

12 楼


5.1.3  在设计器中创建表
如果是使用Toad、PL/SQL Developer或者是Oracle SQL Developer等工具,可以直接使用工具提供的设计器来创建表。
1.在Toad中建表
要使用Toad创建表,可以通过单击主菜单的“Database|Create|Table”菜单项,或者是进入Schema Browser数据库模式窗口后,选择表节点(Treeview模式)或者是表标签(Tab模式),从下拉菜单栏或工具栏中选择创建表向导,如图5.2所示。

[img][/img]

13 楼



[img]http://www.tu265.com/di-01d0f981ccabfd18d9e39b4cca5f7499.png[/img]

14 楼


Toad提供了多种数据表类型可供选择,这些数据表类型具有各自不同的用处,本书主要介绍标准表的创建,有兴趣的读者可以参考Oracle文档获取其他类型的表的使用信息。
对于已创建的表,Toad提供了功能强大的表查看窗口,可以在查询语句或PL/SQL代码编辑器中将鼠标指针放在名称字符串中,使用F4键打开表查看窗口,或者在Toad的SQL编辑器中输入DESC 表名,将显示如图5.3所示的表查看窗口。

[img]http://www.tu265.com/di-ac9c37cbf6b4d90fb04667861232b048.png[/img]

15 楼


使用这个功能强大的窗口,允许DBA或系统管理员随时查看与表相关的所有的信息,比如权限、触发器、约束及表数据等。
2.在PL/SQL Developer中创建表
与Toad类似,PL/SQL提供了表创建向导,可以通过单击“文件|新建|表”菜单项来打开如图5.4所示的表创建窗口。

[img]http://www.tu265.com/di-489ef77704e1fcbf6bb5ba8aa551b7be.png[/img]
PL/SQL Developer的表创建向导使用Tab页的形式提供了创建表的多个选项,比如指定表的存储选项,表的类型,表的列、键、约束、索引等信息。

16 楼


3.在Oracle SQL Developer中创建表
Oracle SQL Developer同样提供了所见即所得的数据表设计器窗口,可以通过导航面板的树状视图,展开数据库连接节点,右击数据表节点,选择“新建表...”菜单项,将显示如图5.5所示的表设计器窗口。

[img]http://www.tu265.com/di-e96afb7066b0233076da09f76ace3e8c.png[/img]
在创建表窗口中,可以通过“添加列”按钮添加新的列,从下拉列表框中选择列数据类型及约束,并可单击“DDL”标签页来查看创建表的数据定义语言。

17 楼

5.1.4  创建表副本
CRETAE TABLE提供的AS SELECT语句,允许从一个现有的表中创建一个新的表,创建的表可以包含原表的所有架构、字段属性、约束和数据记录;也可以仅架构完全相同,而不包含数据,其使用语法如下所示。

CREATE TABLE <newtable> AS SELECT {* | column(s)} FROM <oldtable> [WHERE <condition>];

例如要创建scott方案下的emp表的副本并包含所有的数据,则可以使用如下的语句:

CREATE TABLE emp_copy AS SELECT * from scott.emp

可以使用下面的语法仅创建一个架构而不包含任何表数据:

CREATE TABLE emp_copy AS SELECT * from scott.emp WHERE 1=2;

上述代码中,复制的新表将与原来的表列具有完全相同的定义,但是可以通过改变查询SELECT语句,例如使用函数进行类型的转换等来创建不完全相同的类型,如以下代码所示。

CREATE TABLE emp_copy_others AS SELECT empno,ename,TO_CHAR(hiredate,'yyyy-
MM-dd') AS hiredate FROM scott.emp;

使用CREATE TABLE..SELECT AS方式有如下几个限制需要注意。
&#61553;    不能够复制约束条件与列的默认值,这需要手工重新建立。
&#61553;    不能够为新表指定表空间,默认情况下采用的是当前用户的默认表空间。
&#61553;    一些大对象数据类型(比如Blob类型)或者是Long数据类型的数据,如果包含这种类型的查询是不能创建成功的。

18 楼

5.2  创 建 约 束
约束是一个或多个为了保证数据的完整性而实现的一套机制,约束是数据库服务器强制用户必须遵从的业务逻辑。它们限制用户可能输入指定范围的值,从而强制引用完整性。
约束可以定义在字段级别和表级别,根据约束的类别的不同指定约束定义的不同位置,在Oracle中最常使用的约束分为如下5类。
&#61553;    非空约束:验证字段的值不能为空,一般在字段级别使用NOT NULL列属性进行约束。
&#61553;    唯一约束:指定列的值在整个表的相同列中是唯一的,既可以在表级别也可以在字段级别定义,在字段级别使用UNIQUE进行声明。
&#61553;    检查约束:在定义数据库表时,在字段级别或在表级别加入检查约束,使其满足特定的要求,允许指定字段的检查条件,比如值大于0或小于0等。
&#61553;    主键约束:SQL 92建议在建立一个表时定义一个主键,它其实就是:唯一约束+非空约束。
&#61553;    外键约束:用于定义表间关联的约束,实现数据完整性,这是关系型数据库的         精髓。
约束可以在创建表的时候定义,也可以在建表之后使用ALTER语句添加和修改约束。
5.2.1  创建主键约束
当使用CREATE TABLE语句创建表时,列的NOT NULL和UNIQUE关键字都是表列的约束,这些约束限制了在列中可以存储的数据的类型,除此之外,还可以在列类型的后面使用PRIMARY KEY关键字指定列的类型为主键。当为列指定了主键后,列被强制为NOT NULL,并且列中的每行都被强制为一个唯一值,此外,会根据这个列自动地创建一个索引。
例如对于invoice这个表,可以使用如下的语法来创建并指定主键,如代码5.4所示。
代码5.4  创建invoice表并指定主键
CREATE TABLE invoice
(
   invoice_id NUMBER PRIMARY KEY,                         --自动编号,唯一,不为空
   vendor_id NUMBER NOT NULL,                              --供应商ID
   invoice_number VARCHAR2(50)  NOT NULL,                --发票编号
   invoice_date DATE DEFAULT SYSDATE,                   --发票日期
   invoice_total  NUMBER(9,2) NOT NULL,                 --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0                --付款总数
)

&#61477;注意:如果invoice表已经存在,则使用DROP TABLE invoice语句将其删除重建。
代码中使用PRIMARY KEY关键字对invoice_id列进行修饰,表示将以invoice_id作为表的主键,这是最简单的指定表的主键的方式,但不是最好的编程习惯。
&#61477;注意:如果没有为约束指定名称,Oracle将使用SYS_Cn格式自动生成一个名称,其中n表示一个唯一性的整数,可以通过USER_CONSTRAINTS数据字典表来了解特定的表定义的约束。
建议的方法是在列或表级别使用CONSTRAINT关键字,为约束指定一个约束名,因而对于创建表的代码也可以使用如下的语法,如代码5.5使用了列级别的CONSTRAINT关键字来创建表。
代码5.5  在列属性中使用CONSTRAINT关键字
CREATE TABLE invoice
(
   invoice_id NUMBER CONSTRAINT invoice_pk PRIMARY KEY,
                                                --自动编号,唯一,不为空 
   vendor_id NUMBER CONSTRAINT vendor_id_nn NOT NULL,    --供应商ID
   invoice_number VARCHAR2(50) CONSTRAINT vendor_number_nn   NOT NULL,
                                                    --发票编号
   invoice_date DATE DEFAULT SYSDATE,                    --发票日期
   invoice_total  NUMBER(9,2)  CONSTRAINT invoice_total_nn  NOT NULL,
                                                    --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0                 --付款总数
)

通过将CONSTRAINT定义在列类型后面,可以显式地创建约束,并能为约束指定约束名称。对于UNIQUE与PRIMARY KEY,还可以在表级别使用CONSTRAINT指定约束,比如在为一个表设置多个主键时,可以在表级别使用CONSTRAINT设置约束。在表级别与在列级别的效果是相同的,但是能提供更清晰的代码,将代码5.5的CONSTRAINT声明更改为表级别,实现如代码5.6所示。
代码5.6  在表级别使用CONSTRAINT关键字
CREATE TABLE invoice
(
   invoice_id NUMBER ,                                    --自动编号,唯一,不为空
   vendor_id NUMBER,                                      --供应商ID
   invoice_number VARCHAR2(50),                         --发票编号
   invoice_date DATE DEFAULT SYSDATE,                   --发票日期
   invoice_total  NUMBER(9,2) ,                           --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0,               --付款总数
   CONSTRAINT invoice_pk PRIMARY KEY (invoice_id),
   CONSTRAINT vendor_id_un UNIQUE (vendor_id)
);

上述代码相对于列类型来说最大的好处在于可以使用多列,比如通过在括号内输入以逗号分隔的多个列名,可以同时指定多列主键,例如如果要使用invoide_id和vendor_id作为主键,可以使用如下所示的代码:

CONSTRAINT invoiceid_vendorid_pk PRIMARY KEY (invoice_id,vendor_id),

在为表设计主键时,下面是一些常用的设置规则。
&#61553;    主键应该是对用户没有意义的,在一些数据表的设计中,不建议以材料编码或身份证号码及员工工号作为主键,主键应该只是一些具有唯一性标识的标识符,比如自增长的数字等。
&#61553;    主键应该是单列的,以便提高连接和筛选操作的性能,复合主键通常导致不良的外键,因此要尽量避免。
&#61553;    主键应该是不能被更新的,主键的主要作用是唯一标识一行,更新则违反了主键无意义的原则。
&#61553;    主键不应该包含动态更新的数据,比如时间戳、创建时间或修改时间等这些动态变化的数据。
&#61553;    主键最好由计算机自动生成,在Oracle中可以使用序列来为主键列生成值

19 楼

5.2.2  创建外键约束
外键约束又称为引用约束,这种类型的约束主要用来在多个表之间定义关系,并强制引用完整性,与主键约束一样,外键约束也可以在列级别和表级别创建,使用关键字REFERENCES语句来定义,列级别的外键约束语法如下所示。

[CONSTRAINT constraint_name]
  REFERENCES table_name (column_name)
[ON DELETE {CASCADE|SET NULL}]

位于[]的可选部分指定CONSTRAINT和约束名称,ON DELETE {CASCADE|SET NULL}这行代码用来指示是否级联删除,当两个表中的两个字段建立了外键关联后,如果主键所在表中的值被删除,使用ON DELETE指定是否级联删除,CASCADE表示关联表中的内容一并删除,而SET NULL表示子表中的值设置为NULL。
&#61477;注意:如果没有指定ON DELETE,默认情况下将使用CASCADE进行级联删除。
假定有一个表vendor,可以将invoice表的vendor_id与vendor表的vendor_id字段进行外键约束,也就是说,invoice表中的字段取值必须是vendor表中已经存在的供应商字段,vendor表的创建如代码5.7所示。
代码5.7  vendor表的定义代码
CREATE TABLE vendors
(
   vendor_id NUMBER,                                         --供应商id
   vendor_name VARCHAR2(50) NOT NULL,                        --供应商名称
   CONSTRAINT vendors_pk PRIMARY KEY (vendor_id),          --主键
   CONSTRAINT vendor_name_uq UNIQUE (vendor_name)          --唯一性约束
)

下面的代码创建invoice表,在列级别为invoice表的vendor_id字段与vendor表的vendor_id字段进行了关联,如代码5.8所示。
代码5.8  在invoice表中为vendor_id列创建外键关联
CREATE TABLE invoice
(
   invoice_id NUMBER ,                                     --自动编号,唯一,不为空
   vendor_id NUMBER   REFERENCES vendors (vendor_id),    --供应商ID
   invoice_number VARCHAR2(50),                               --发票编号
   invoice_date DATE DEFAULT SYSDATE,                        --发票日期
   invoice_total  NUMBER(9,2) ,                                --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0,                   --付款总数
   CONSTRAINT invoiceid_vendorid_pk PRIMARY KEY (invoice_id,vendor_id),
   CONSTRAINT vendor_id_un UNIQUE (vendor_id)
);

通过使用REFERENCES语法,指定要关联的目标表名与字段,示例中指定vendors表的主键vendor_id列作为关联字段。
&#61477;注意:在定义外键时,引用的表键必须是唯一性键值,一般建议使用关联表的主键作为关联字段。
同样可以在表级别使用CONSTRAINT关键字来创建外键约束,例如下面的代码在表级别使用CONSTRAINT定义了外键关联并指定了ON DELETE级联删除设置,如代码5.9所示。
代码5.9  在invoice表级别创建外键关联
CREATE TABLE invoice
(
   invoice_id NUMBER ,                                     --自动编号,唯一,不为空
   vendor_id NUMBER,                                        --供应商ID
   invoice_number VARCHAR2(50),                           --发票编号
   invoice_date DATE DEFAULT SYSDATE,                   --发票日期
   invoice_total  NUMBER(9,2) ,                           --发票总数
   payment_total NUMBER(9,2)   DEFAULT 0,               --付款总数
   CONSTRAINT invoiceid_vendorid_pk PRIMARY KEY (invoice_id,vendor_id),
   CONSTRAINT vendor_id_un UNIQUE (vendor_id),
   CONSTRAINT invoice_fk_vendors FOREIGN KEY (vendor_id) REFERENCES vendors 
   (vendor_id) 
   ON DELETE CASCADE
);

使用CONSTRAINT语法的不同之处在于需要为外键指定一个名称;使用 FOREIGN KEY指定外键字段;REFERENCES指定关联表和关联字段;上述代码使用ON DELETE显式指定了级联删除特性。
一旦创建了主外键关联,可以使用Toad提供的Query Builder或ER Diagram工具来查看外键关联信息,Query Builder允许可视化的创建查询,而ER Diagram则主要用来显示表间关系。要打开Query Builder,可以使用Toad主菜单的“Database|Report|Query Builder”菜单项,如图5.6所示。

20 楼



[img]http://www.tu265.com/di-e96afb7066b0233076da09f76ace3e8c.png[/img]
PL/SQL Developer也提供了查询设计器,可以自动显示出表间的主外键关联,要打开查询设计器,可以将鼠标停留在SQL编辑器窗口,然后按工具栏的 按钮打开查询设计器,如图5.7所示。

[img]http://www.tu265.com/di-882098a4d9ca7f270a44f4165849cf6c.png[/img]

我来回复

您尚未登录,请登录后再回复。点此登录或注册