Oracle 11g SQL语言权威参考：全面解析11.1版SQL使用

需积分: 10 134 浏览量更新于2024-07-22 收藏 23.81MB PDF 举报

Oracle 11g SQL Language Reference 是Oracle Database 11g Release 1（11.1）的官方文档，它为SQL语言在Oracle 11g环境中的使用提供了全面而权威的指南。该文档由Diana Lorentz担任主要作者，同时感谢Bob Jenkins的无私贡献，他多次解答了编写过程中的疑问。其他多位专家如Drew Adams、Nippun Agarwal等也参与了编写，确保了内容的深度和准确性。在本书中，读者可以深入理解SQL的基础概念，包括查询构造、数据操作、数据定义、数据控制以及事务处理等核心部分。Oracle 11g SQL Language Reference涵盖了SQL语法的各个方面，包括但不限于SELECT语句的最佳实践、JOIN操作的优化、存储过程的编写、视图的创建与管理、索引策略的选择、PL/SQL脚本编写、以及SQL安全性设置等。章节内容可能包括： 1. SQL基本语法：介绍如何正确编写SQL语句，包括SELECT、INSERT、UPDATE和DELETE等基础操作。 2. 数据查询优化：讨论如何通过编写高效的查询来提高数据库性能，涉及查询计划分析和SQL调优技术。 3. 视图和材料化视图：讲解如何利用视图进行数据抽象，以及材料化视图的使用场景和优点。 4. 存储过程和触发器：如何设计和执行存储过程，以及如何通过触发器实现数据的自动更新或验证。 5. 数据类型和约束：详细列出Oracle 11g支持的数据类型，以及如何应用约束来保证数据质量。 6. PL/SQL编程：介绍PL/SQL语言，它是Oracle数据库的强大脚本语言，用于处理复杂的业务逻辑。 7. 安全性和权限管理：讲解如何设置用户权限，以及如何确保数据的安全性，防止未授权访问。 8. 错误处理和异常管理：介绍如何优雅地处理SQL运行时的错误，并使用TRY/CATCH块进行异常处理。此外，书中还包含了大量的示例和案例，使读者能够通过实际操作加深理解和熟练掌握SQL在Oracle 11g中的应用。总体来说，Oracle 11g SQL Language Reference是任何Oracle数据库管理员、开发人员和学习者必备的参考资料，帮助他们更有效地管理和操作Oracle 11g数据库系统。

xvi

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

Joins ......................................................................................................................................................... 9-10

Join Conditions ............................................................................................................................... 9-10

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

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

Cartesian Products ......................................................................................................................... 9-11

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

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

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

Semijoins .......................................................................................................................................... 9-13

Using Subqueries ................................................................................................................................. 9-13

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

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

Distributed Queries ............................................................................................................................. 9-15

10 SQL Statements: ALTER CLUSTER to ALTER JAVA

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

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

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 DIMENSION ........................................................................................................................ 10-44

ALTER DISKGROUP ........................................................................................................................ 10-47

ALTER FLASHBACK ARCHIVE .................................................................................................... 10-62

ALTER FUNCTION ........................................................................................................................... 10-65

ALTER INDEX .................................................................................................................................... 10-66

ALTER INDEXTYPE .......................................................................................................................... 10-85

ALTER JAVA ...................................................................................................................................... 10-88

11 SQL Statements: ALTER MATERIALIZED VIEW to ALTER SYSTEM

ALTER MATERIALIZED VIEW ....................................................................................................... 11-2

ALTER MATERIALIZED VIEW LOG ........................................................................................... 11-16

ALTER OPERATOR .......................................................................................................................... 11-22

ALTER OUTLINE .............................................................................................................................. 11-25

ALTER PACKAGE ............................................................................................................................. 11-27

ALTER PROCEDURE ........................................................................................................................ 11-28

ALTER PROFILE ................................................................................................................................ 11-29

ALTER RESOURCE COST ............................................................................................................... 11-32

ALTER ROLE ...................................................................................................................................... 11-35

ALTER ROLLBACK SEGMENT ..................................................................................................... 11-37

ALTER SEQUENCE ........................................................................................................................... 11-40

ALTER SESSION ............................................................................................................................... 11-42

xvii

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

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

ALTER SYSTEM ................................................................................................................................. 11-55

12 SQL Statements: ALTER TABLE to ALTER TABLESPACE

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

ALTER TABLESPACE ....................................................................................................................... 12-86

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

ASSOCIATE STATISTICS .............................................................................................................. 13-22

AUDIT .................................................................................................................................................. 13-26

CALL ..................................................................................................................................................... 13-38

COMMENT ......................................................................................................................................... 13-42

COMMIT ............................................................................................................................................. 13-45

14 SQL Statements: CREATE CLUSTER to CREATE JAVA

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

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

CREATE CONTROLFILE ................................................................................................................. 14-11

CREATE DATABASE ........................................................................................................................ 14-18

CREATE DATABASE LINK ............................................................................................................ 14-31

CREATE DIMENSION ..................................................................................................................... 14-36

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

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

CREATE FLASHBACK ARCHIVE ................................................................................................. 14-49

CREATE FUNCTION ........................................................................................................................ 14-52

CREATE INDEX ................................................................................................................................. 14-54

CREATE INDEXTYPE ....................................................................................................................... 14-79

CREATE JAVA .................................................................................................................................... 14-82

15 SQL Statements: CREATE LIBRARY to CREATE SPFILE

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

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

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

CREATE OPERATOR ....................................................................................................................... 15-33

CREATE OUTLINE ............................................................................................................................ 15-36

CREATE PACKAGE .......................................................................................................................... 15-40

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

CREATE PFILE ................................................................................................................................... 15-44

CREATE PROCEDURE ..................................................................................................................... 15-46

xviii

CREATE PROFILE ............................................................................................................................. 15-48

CREATE RESTORE POINT ............................................................................................................. 15-54

CREATE ROLE ................................................................................................................................... 15-57

CREATE ROLLBACK SEGMENT .................................................................................................. 15-60

CREATE SCHEMA ............................................................................................................................ 15-63

CREATE SEQUENCE ........................................................................................................................ 15-65

CREATE SPFILE ................................................................................................................................. 15-69

16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER

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

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

CREATE TABLESPACE .................................................................................................................... 16-76

CREATE TRIGGER ........................................................................................................................... 16-91

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

DISASSOCIATE STATISTICS ....................................................................................................... 17-33

DROP CLUSTER ................................................................................................................................ 17-35

DROP CONTEXT ............................................................................................................................... 17-37

DROP DATABASE ............................................................................................................................ 17-38

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

DROP DIMENSION .......................................................................................................................... 17-40

DROP DIRECTORY .......................................................................................................................... 17-41

DROP DISKGROUP ......................................................................................................................... 17-42

DROP FLASHBACK ARCHIVE ..................................................................................................... 17-44

DROP FUNCTION ............................................................................................................................ 17-45

DROP INDEX....................................................................................................................................... 17-47

DROP INDEXTYPE ........................................................................................................................... 17-49

DROP JAVA ........................................................................................................................................ 17-50

DROP LIBRARY ................................................................................................................................. 17-51

DROP MATERIALIZED VIEW........................................................................................................ 17-52

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

DROP OPERATOR ............................................................................................................................ 17-56

DROP OUTLINE ................................................................................................................................ 17-57

DROP PACKAGE .............................................................................................................................. 17-59

DROP PROCEDURE ......................................................................................................................... 17-61

DROP PROFILE .................................................................................................................................. 17-62

DROP RESTORE POINT ................................................................................................................. 17-63

DROP ROLE ........................................................................................................................................ 17-64

DROP ROLLBACK SEGMENT ...................................................................................................... 17-65

xix

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

19 SQL Statements: SAVEPOINT to UPDATE

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

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

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

SET ROLE ............................................................................................................................................ 19-55

SET TRANSACTION ........................................................................................................................ 19-57

TRUNCATE CLUSTER ..................................................................................................................... 19-60

TRUNCATE TABLE .......................................................................................................................... 19-62

UPDATE ............................................................................................................................................... 19-66

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 Oracle and Standard SQL

ANSI Standards ...................................................................................................................................... B-1

ISO Standards .......................................................................................................................................... B-2

Oracle Compliance To Core SQL:2003 ................................................................................................ B-3

Oracle Support for Optional Features of SQL/Foundation:2003.................................................... B-8

Oracle Compliance with SQL/CLI:2003 ............................................................................................ B-19

Oracle Compliance with SQL/PSM:2003 .......................................................................................... B-19

Oracle Compliance with SQL/MED:2003 ......................................................................................... B-19

Oracle Compliance with SQL/OLB:2003........................................................................................... B-19

Oracle Compliance with SQL/XML:2006.......................................................................................... B-19

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

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

Oracle Compliance with Older Standards ...................................................................................... B-28

Character Set Support........................................................................................................................... B-28

C Oracle Regular Expression Support

Multilingual Regular Expression Syntax .......................................................................................... C-1

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

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

D Oracle Database Reserved Words

E Extended Examples

Using Extensible Indexing ................................................................................................................... E-1

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

Index

剩余1545页未读，继续阅读

v_empire

粉丝: 0
资源: 6

Oracle 11g SQL语言权威参考：全面解析11.1版SQL使用

Oracle 11g SQL Language Reference

Oracle Database PLSQL Language Reference 11g

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

ORACLE11G SQL Language Reference

Oracle SQL Language Reference

Oracle Database 11g SQL Language Reference Manual

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

Oracle Database SQL Language Reference.zip

Oracle Database SQL Language Reference.pdf

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

最新资源