Oracle 11g SQL语言参考手册：全面指南

5星 · 超过95%的资源需积分: 10 170 浏览量更新于2024-07-24 1 收藏 11.46MB PDF 举报

Oracle SQL Reference 11g是Oracle数据库11.2版本的专业参考手册，由Oracle公司及其关联公司于2012年12月发布。该书的版权归Oracle所有，享有1996年及2012年的权益。本书的主要作者包括Diana Lorentz和Mary Beth Roeser，而其他众多专家如Sundeep Abraham、Angela Amor等也作为贡献者共同编撰。作为Oracle Database语言的核心部分，SQL（Structured Query Language）在本书中详尽介绍了如何在Oracle 11g环境中进行高效的数据管理和操作。内容覆盖了SQL的基础语法，如数据查询、数据操纵（SELECT, INSERT, UPDATE, DELETE）、数据定义（CREATE, ALTER, DROP）、数据控制（GRANT, REVOKE）、以及更高级的主题，如视图、存储过程、触发器、索引、事务处理和PL/SQL编程等。书中还包含了对SQL最佳实践的深入探讨，帮助读者优化查询性能，提高数据库管理效率。此外，对于SQL的安全性和权限管理也有详细的指导，确保数据的安全性。Oracle 11g引入的新特性，如XML支持、JSON处理、以及与NoSQL技术的集成，也在本参考书中得到了介绍。参与编写的专业人士包括多位经验丰富的开发者和DBA，他们的专业知识使得本书不仅理论扎实，而且实用性强，适合从初学者到高级用户的不同层次的读者查阅和学习。无论是日常的数据查询，还是解决复杂的数据库问题，Oracle SQL Reference 11g都是一个不可或缺的工具。 Oracle SQL Reference 11g是Oracle数据库管理员、开发人员和SQL爱好者必备的参考资料，它全面展示了Oracle SQL语言的最新特性和用法，帮助读者提升技能，更好地管理和分析大量数据。

xvi

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

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

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

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

Self Joins .......................................................................................................................................... 9-12

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

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

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

Antijoins .......................................................................................................................................... 9-14

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

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

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

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

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-74

ALTER FUNCTION ........................................................................................................................... 10-77

ALTER INDEX .................................................................................................................................... 10-78

ALTER INDEXTYPE .......................................................................................................................... 10-97

ALTER JAVA ...................................................................................................................................... 10-100

11 SQL Statements: ALTER LIBRARY to ALTER SYSTEM

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

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

xvii

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-91

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-88

CREATE JAVA ..................................................................................................................................... 14-92

15 SQL Statements: CREATE LIBRARY to CREATE SPFILE

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

CREATE MATERIALIZED VIEW ..................................................................................................... 15-4

CREATE MATERIALIZED VIEW LOG ......................................................................................... 15-27

CREATE OPERATOR ........................................................................................................................ 15-35

CREATE OUTLINE ............................................................................................................................ 15-38

CREATE PACKAGE ........................................................................................................................... 15-42

xviii

CREATE PACKAGE BODY .............................................................................................................. 15-44

CREATE PFILE .................................................................................................................................... 15-46

CREATE PROCEDURE ..................................................................................................................... 15-48

CREATE PROFILE ............................................................................................................................. 15-50

CREATE RESTORE POINT ............................................................................................................. 15-56

CREATE ROLE .................................................................................................................................... 15-59

CREATE ROLLBACK SEGMENT .................................................................................................. 15-62

CREATE SCHEMA ............................................................................................................................. 15-65

CREATE SEQUENCE ........................................................................................................................ 15-67

CREATE SPFILE ................................................................................................................................. 15-71

16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER

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

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

CREATE TABLESPACE ..................................................................................................................... 16-82

CREATE TRIGGER ............................................................................................................................ 16-96

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-47

DROP FUNCTION ............................................................................................................................ 17-48

DROP INDEX....................................................................................................................................... 17-50

DROP INDEXTYPE ........................................................................................................................... 17-52

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

DROP LIBRARY ................................................................................................................................. 17-54

DROP MATERIALIZED VIEW........................................................................................................ 17-55

DROP MATERIALIZED VIEW LOG ............................................................................................. 17-57

DROP OPERATOR ............................................................................................................................ 17-59

DROP OUTLINE ................................................................................................................................ 17-60

DROP PACKAGE ............................................................................................................................... 17-62

DROP PROCEDURE ......................................................................................................................... 17-64

DROP PROFILE .................................................................................................................................. 17-65

DROP RESTORE POINT ................................................................................................................. 17-66

DROP ROLE ........................................................................................................................................

17-67

xix

DROP ROLLBACK SEGMENT ...................................................................................................... 17-68

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-59

SET ROLE ............................................................................................................................................ 19-61

SET TRANSACTION ........................................................................................................................ 19-64

TRUNCATE CLUSTER ..................................................................................................................... 19-67

TRUNCATE TABLE ........................................................................................................................... 19-69

UPDATE ............................................................................................................................................... 19-73

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-21

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

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

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

Character Set Support........................................................................................................................... C-27

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 and Keywords

Oracle SQL Reserved Words ................................................................................................................. E-1

Oracle SQL Keywords............................................................................................................................ E-3

F Extended Examples

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

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

Index

剩余1629页未读，继续阅读

zhfdon

粉丝: 1

Oracle 11g SQL语言参考手册：全面指南

Oracle SQL Reference

Oracle SQL Reference命令参考文档

编程人员必备oracle 11g 官方SQL、PL/SQL reference

oracle sql优化经验

oracle11g sql server安装教程

sql reference

oracle sql语句怎么写或

oraclesql语句学习

oracle sql语句执行过程

oracle sql执行计划分析

最新资源