Oracle® Database SQL 11g Release 2: Ultimate Reference

5星 · 超过95%的资源需积分: 11 2 浏览量更新于2023-06-25 2 收藏 11.23MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"Oracle® Database SQL Language Reference 11g Release 2 (11.2)" 是Oracle公司发布的一本关于11g版本数据库系统的SQL语言参考指南，旨在帮助用户理解和使用Oracle数据库中的SQL语言进行数据查询、管理及操作。在Oracle 11g Release 2中，SQL语言是访问和管理Oracle数据库的核心工具。它包括了标准的SQL语句，如SELECT用于查询数据，INSERT用于插入数据，UPDATE用于更新现有记录，以及DELETE用于删除记录。此外，还有对数据库对象如表、视图、索引、存储过程和触发器的创建和管理。此参考指南详细介绍了以下关键知识点： 1. **数据类型**：Oracle支持多种数据类型，包括数值类型（如NUMBER，INTEGER），字符串类型（VARCHAR2，CHAR），日期时间类型（DATE，TIMESTAMP）等，以及BLOB和CLOB等大型对象类型。 2. **查询语法**：SELECT语句的复杂用法，如JOIN操作用于合并多个表的数据，子查询用于嵌套查询，聚合函数（COUNT，SUM，AVG，MAX，MIN）用于统计分析，以及GROUP BY和HAVING子句用于分组和过滤。 3. **DML操作**：INSERT，UPDATE和DELETE语句的使用，以及如何处理并发控制和事务管理，如COMMIT，ROLLBACK和SAVEPOINT。 4. **DDL操作**：CREATE，ALTER和DROP命令用于创建、修改和删除数据库对象，如表、索引、视图、存储过程和函数。 5. **索引**：如何创建和管理B树、位图和唯一性索引，以提高查询性能。 6. **视图**：创建和使用视图来简化查询或隐藏复杂的数据结构。 7. **游标**：在PL/SQL中使用游标进行迭代处理查询结果。 8. **子程序和包**：创建和调用存储过程和函数，以及打包相关功能到包中。 9. **触发器**：定义在特定事件发生时自动执行的代码逻辑。 10. **异常处理**：使用BEGIN...EXCEPTION块来捕获和处理运行时错误。 11. **分区**：高级特性，允许将大表分割成更易管理和查询的小部分。 12. **安全性**：包括用户权限、角色和对象权限的管理，以及审计功能。 13. **性能优化**：如使用EXPLAIN PLAN分析查询计划，绑定变量，以及使用索引策略。 14. **XML支持**：处理XML数据，包括XMLType数据类型和XML相关的函数。 15. **并行执行**：并行查询和并行DML，以利用多处理器系统提升性能。这本参考手册还包含了大量示例和详细的错误消息解释，是开发人员、数据库管理员和系统分析师的宝贵资源，帮助他们在Oracle 11g环境中高效地使用SQL语言。

资源详情

资源推荐

xvi

size_clause .............................................................................................................................................. 8-47

storage_clause ........................................................................................................................................ 8-48

9 SQL Queries and Subqueries

About Queries and Subqueries ............................................................................................................ 9-1

Creating Simple Queries ........................................................................................................................ 9-2

Hierarchical Queries ............................................................................................................................... 9-3

Hierarchical Query Examples .......................................................................................................... 9-5

The UNION [ALL], INTERSECT, MINUS Operators....................................................................... 9-8

Sorting Query Results ......................................................................................................................... 9-10

Joins ......................................................................................................................................................... 9-11

Join Conditions ............................................................................................................................... 9-11

Equijoins .......................................................................................................................................... 9-11

Self Joins .......................................................................................................................................... 9-11

Cartesian Products ......................................................................................................................... 9-12

Inner Joins ....................................................................................................................................... 9-12

Outer Joins ....................................................................................................................................... 9-12

Antijoins .......................................................................................................................................... 9-13

Semijoins .......................................................................................................................................... 9-14

Using Subqueries ................................................................................................................................. 9-14

Unnesting of Nested Subqueries ...................................................................................................... 9-15

Selecting from the DUAL Table ........................................................................................................ 9-15

Distributed Queries ............................................................................................................................. 9-16

10 SQL Statements: ALTER CLUSTER to ALTER JAVA

Types of SQL Statements .................................................................................................................... 10-1

Data Definition Language (DDL) Statements ............................................................................ 10-2

Data Manipulation Language (DML) Statements ..................................................................... 10-2

Transaction Control Statements ................................................................................................... 10-3

Session Control Statements ........................................................................................................... 10-3

System Control Statement ............................................................................................................. 10-3

Embedded SQL Statements .......................................................................................................... 10-3

How the SQL Statement Chapters are Organized ......................................................................... 10-4

ALTER CLUSTER ................................................................................................................................. 10-5

ALTER DATABASE ............................................................................................................................. 10-9

ALTER DATABASE LINK ................................................................................................................ 10-46

ALTER DIMENSION ........................................................................................................................ 10-48

ALTER DISKGROUP ........................................................................................................................ 10-51

ALTER FLASHBACK ARCHIVE .................................................................................................... 10-73

ALTER FUNCTION ........................................................................................................................... 10-76

ALTER INDEX .................................................................................................................................... 10-77

ALTER INDEXTYPE .......................................................................................................................... 10-96

ALTER JAVA ........................................................................................................................................ 10-99

11 SQL Statements: ALTER LIBRARY to ALTER SYSTEM

ALTER LIBRARY................................................................................................................................... 11-2

xvii

ALTER MATERIALIZED VIEW ........................................................................................................ 11-3

ALTER MATERIALIZED VIEW LOG ............................................................................................ 11-18

ALTER OPERATOR ........................................................................................................................... 11-25

ALTER OUTLINE ............................................................................................................................... 11-28

ALTER PACKAGE ............................................................................................................................. 11-30

ALTER PROCEDURE ........................................................................................................................ 11-31

ALTER PROFILE ................................................................................................................................ 11-32

ALTER RESOURCE COST ............................................................................................................... 11-35

ALTER ROLE ....................................................................................................................................... 11-38

ALTER ROLLBACK SEGMENT ..................................................................................................... 11-40

ALTER SEQUENCE ........................................................................................................................... 11-43

ALTER SESSION ................................................................................................................................ 11-45

Initialization Parameters and ALTER SESSION....................................................................... 11-50

Session Parameters and ALTER SESSION ............................................................................... 11-51

ALTER SYSTEM ................................................................................................................................. 11-58

12 SQL Statements: ALTER TABLE to ALTER TABLESPACE

ALTER TABLE ....................................................................................................................................... 12-2

ALTER TABLESPACE ........................................................................................................................ 12-89

13 SQL Statements: ALTER TRIGGER to COMMIT

ALTER TRIGGER ................................................................................................................................. 13-2

ALTER TYPE ......................................................................................................................................... 13-4

ALTER USER ......................................................................................................................................... 13-5

ALTER VIEW ....................................................................................................................................... 13-12

ANALYZE ............................................................................................................................................ 13-15

ASSOCIATE STATISTICS ................................................................................................................ 13-23

AUDIT .................................................................................................................................................. 13-27

CALL ..................................................................................................................................................... 13-40

COMMENT ......................................................................................................................................... 13-44

COMMIT ............................................................................................................................................. 13-47

14 SQL Statements: CREATE CLUSTER to CREATE JAVA

CREATE CLUSTER .............................................................................................................................. 14-2

CREATE CONTEXT ............................................................................................................................. 14-9

CREATE CONTROLFILE ................................................................................................................. 14-12

CREATE DATABASE ......................................................................................................................... 14-19

CREATE DATABASE LINK ............................................................................................................. 14-32

CREATE DIMENSION ..................................................................................................................... 14-37

CREATE DIRECTORY ...................................................................................................................... 14-42

CREATE DISKGROUP ..................................................................................................................... 14-44

CREATE EDITION ............................................................................................................................. 14-52

CREATE FLASHBACK ARCHIVE ................................................................................................. 14-56

CREATE FUNCTION ........................................................................................................................ 14-59

CREATE INDEX ................................................................................................................................. 14-61

CREATE INDEXTYPE ....................................................................................................................... 14-87

xviii

CREATE JAVA ..................................................................................................................................... 14-91

15 SQL Statements: CREATE LIBRARY to CREATE SPFILE

CREATE LIBRARY ............................................................................................................................... 15-2

CREATE MATERIALIZED VIEW ..................................................................................................... 15-3

CREATE MATERIALIZED VIEW LOG ......................................................................................... 15-26

CREATE OPERATOR ........................................................................................................................ 15-34

CREATE OUTLINE ............................................................................................................................ 15-37

CREATE PACKAGE ........................................................................................................................... 15-41

CREATE PACKAGE BODY .............................................................................................................. 15-43

CREATE PFILE .................................................................................................................................... 15-45

CREATE PROCEDURE ..................................................................................................................... 15-47

CREATE PROFILE ............................................................................................................................. 15-49

CREATE RESTORE POINT ............................................................................................................. 15-55

CREATE ROLE .................................................................................................................................... 15-58

CREATE ROLLBACK SEGMENT .................................................................................................. 15-61

CREATE SCHEMA ............................................................................................................................. 15-64

CREATE SEQUENCE ........................................................................................................................ 15-66

CREATE SPFILE ................................................................................................................................. 15-70

16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER

CREATE SYNONYM ........................................................................................................................... 16-2

CREATE TABLE .................................................................................................................................... 16-6

CREATE TABLESPACE ..................................................................................................................... 16-80

CREATE TRIGGER ............................................................................................................................ 16-94

17 SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT

CREATE TYPE ....................................................................................................................................... 17-3

CREATE TYPE BODY .......................................................................................................................... 17-5

CREATE USER ...................................................................................................................................... 17-7

CREATE VIEW .................................................................................................................................... 17-14

DELETE ................................................................................................................................................ 17-26

DISASSOCIATE STATISTICS ........................................................................................................ 17-34

DROP CLUSTER ................................................................................................................................ 17-36

DROP CONTEXT ............................................................................................................................... 17-38

DROP DATABASE ............................................................................................................................. 17-39

DROP DATABASE LINK ................................................................................................................. 17-40

DROP DIMENSION .......................................................................................................................... 17-41

DROP DIRECTORY .......................................................................................................................... 17-42

DROP DISKGROUP ......................................................................................................................... 17-43

DROP EDITION ................................................................................................................................. 17-45

DROP FLASHBACK ARCHIVE ..................................................................................................... 17-46

DROP FUNCTION ............................................................................................................................ 17-47

DROP INDEX....................................................................................................................................... 17-49

DROP INDEXTYPE ........................................................................................................................... 17-51

D R O P J AVA ......................................................................................................................................... 17-52

xix

DROP LIBRARY ................................................................................................................................. 17-53

DROP MATERIALIZED VIEW........................................................................................................ 17-54

DROP MATERIALIZED VIEW LOG ............................................................................................. 17-56

DROP OPERATOR ............................................................................................................................ 17-58

DROP OUTLINE ................................................................................................................................ 17-59

DROP PACKAGE ............................................................................................................................... 17-61

DROP PROCEDURE ......................................................................................................................... 17-63

DROP PROFILE .................................................................................................................................. 17-64

DROP RESTORE POINT ................................................................................................................. 17-65

DROP ROLE ........................................................................................................................................ 17-66

DROP ROLLBACK SEGMENT ...................................................................................................... 17-67

18 SQL Statements: DROP SEQUENCE to ROLLBACK

DROP SEQUENCE .............................................................................................................................. 18-2

DROP SYNONYM ............................................................................................................................... 18-3

DROP TABLE ........................................................................................................................................ 18-5

DROP TABLESPACE ........................................................................................................................... 18-9

DROP TRIGGER................................................................................................................................. 18-12

DROP TYPE ......................................................................................................................................... 18-13

DROP TYPE BODY ............................................................................................................................ 18-15

DROP USER ........................................................................................................................................ 18-16

DROP VIEW ........................................................................................................................................ 18-18

EXPLAIN PLAN .................................................................................................................................. 18-20

FLASHBACK DATABASE ................................................................................................................ 18-24

FLASHBACK TABLE ......................................................................................................................... 18-27

GRANT ................................................................................................................................................. 18-33

INSERT ................................................................................................................................................. 18-54

LOCK TABLE ...................................................................................................................................... 18-72

MERGE ................................................................................................................................................. 18-75

NOAUDIT ............................................................................................................................................ 18-80

PURGE .................................................................................................................................................. 18-84

RENAME .............................................................................................................................................. 18-86

REVOKE ............................................................................................................................................... 18-88

ROLLBACK ......................................................................................................................................... 18-97

19 SQL Statements: SAVEPOINT to UPDATE

SAVEPOINT .......................................................................................................................................... 19-2

SELECT ................................................................................................................................................... 19-4

SET CONSTRAINT[S] ...................................................................................................................... 19-58

SET ROLE ............................................................................................................................................ 19-60

SET TRANSACTION ........................................................................................................................ 19-62

TRUNCATE CLUSTER ..................................................................................................................... 19-65

TRUNCATE TABLE ........................................................................................................................... 19-67

UPDATE ............................................................................................................................................... 19-71

A How to Read Syntax Diagrams

Graphic Syntax Diagrams...................................................................................................................... A-1

Required Keywords and Parameters ............................................................................................ A-2

Optional Keywords and Parameters ............................................................................................. A-3

Syntax Loops...................................................................................................................................... A-3

Multipart Diagrams ......................................................................................................................... A-4

Database Objects .............................................................................................................................. A-4

B Automatic and Manual Locking Mechanisms During SQL Operations

Automatic Locks in DML Operations ................................................................................................. B-1

Automatic Locks in DDL Operations.................................................................................................. B-4

Exclusive DDL Locks........................................................................................................................ B-4

Share DDL Locks............................................................................................................................... B-5

Breakable Parse Locks ...................................................................................................................... B-5

Manual Data Locking ............................................................................................................................. B-5

C Oracle and Standard SQL

ANSI Standards ...................................................................................................................................... C-1

ISO Standards .......................................................................................................................................... C-2

Oracle Compliance To Core SQL:2008................................................................................................. C-3

Oracle Support for Optional Features of SQL/Foundation:2008.................................................... C-9

Oracle Compliance with SQL/CLI:2008 ............................................................................................ C-21

Oracle Compliance with SQL/PSM:2008 .......................................................................................... C-21

Oracle Compliance with SQL/MED:2008 ......................................................................................... C-21

Oracle Compliance with SQL/OLB:2008........................................................................................... C-21

Oracle Compliance with SQL/JRT:2008 ............................................................................................ C-21

Oracle Compliance with SQL/XML:2008.......................................................................................... C-22

Oracle Compliance with FIPS 127-2 ................................................................................................. C-27

Oracle Extensions to Standard SQL .................................................................................................. C-28

Oracle Compliance with Older Standards ....................................................................................... C-29

Character Set Support........................................................................................................................... C-29

D Oracle Regular Expression Support

Multilingual Regular Expression Syntax .......................................................................................... D-1

Regular Expression Operator Multilingual Enhancements............................................................ D-2

Perl-influenced Extensions in Oracle Regular Expressions ........................................................... D-3

E Oracle SQL Reserved Words

F Extended Examples

Using Extensible Indexing ................................................................................................................... F-1

Using XML in SQL Statements ............................................................................................................ F-8

Index

剩余1507页未读，继续阅读

ckangtai

粉丝: 41
资源: 458

会员权益专享

Oracle® Database SQL 11g Release 2: Ultimate Reference

Oracle Database SQL Language Reference 11g Release 2 (11.2)-1634

Oracle Database PLSQL Language Reference 11g Release 2 (11.2)

Oracle Database SQL Language Quick Reference 11g Release 2 (11.2

oracle database instant client 11g 11.2.0.3.0 x64

oracle 11.2.0.3.0 client下载

linux oracle11g客户端下载

oracle11g 11.2.0.4.0

oracle11.2.0.4.0下载

oracle数据库大作业

11gr2 11.2.0.3 下载

oracle11g发行版2

下载Oracle 11g客户端安装文件

oracle11g sqldeveloper

oracle 11g 11.2.0.4.0 win补丁下载

oracle11g11.2.0.4下载linux

怎么查oracle版本

oracle 11g sql基础

oracle11g linux 安装包下载

oracle11g sql server安装教程

Oracle安装Oracle11.2 ，安装了instantclient11-2,还需安装什么

会员权益专享

最新资源