Oracle SQL David C. Kreines Ken Jacobs Beijing Cambridge Farnham Köln Paris Sebastopol Taipei Tokyo O'Reilly & Associates, Inc.
Oracle SQL / Kreines, D. C.. 2002.11 Oracle SQL: The Essential Reference ISBN 7-5083-1102-7 I. O... II............. III. SQL IV. TP311.138 CIP 2002 084654 01-2002-1198 2000 by O'Reilly & Associates, Inc. Simplified Chinese Edition, jointly published by O'Reilly & Associates, Inc. and China Electric Power Press, 2003. Authorized translation of the English edition, 2000 O'Reilly & Associates, Inc., the owner of all rights to publish and sell the same. All rights reserved including the rights of reproduction in whole or in part in any form. O'Reilly & Associates, Inc. 2000 2003 O'Reilly & Associates, Inc. O'Reilly & Associates, Inc. / Oracle SQL / ISBN 7-5083-1102-7 / / Ellie Volckhausen / www.infopower.com.cn / 6 100044 / / / 787 1092 16 27 395 / 2003 2 2003 2 / 0001-5000 / 49.00
David C. Kreines Rhodia Rhone-Poulenc S.A Oracle Database Administration:The Essential Reference O'Reilly & Associates 1999 Oracle Scripts O'Reilly & Associates 1998 1985 Dave Oracle PC OracleOCP DBA Oracle Dave Oracle International Oracle Users Group-AmericasIOUG-A 1300 1 8
O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates UNIXXInternet The Whole Internet User's Guide & Catalog 50 GNN Internet WebSite PC O'Reilly & Associates Internet O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates O'Reilly & Associates
SQL SQL SQL Oracle8i SQL SQL IT SQL SQL 1951 UNIVAC I 20 50 1
2 Fortran1957 C1972 Java1995 FortranC Java Algol COBOLAdaC++ Basic SQLSQL SQL SQL SQL20 70 SQL Rosetta 1 SQL SQL SQL Michael Stonebreaker SQL SQL SQL 20 70 SQL IBM IBM Ted Codd A Relational Model of Data for Large Shared Data Banks 19706 Communications 1
3 of the ACM Codd Codd DSL/Alpha Codd relational completeness Codd Codd 20 70 Codd IBM Frank King System R SQL System R Codd DSL/Alpha SQUARE DSL/AlphaSQUARE SQUARE SEQUEL Structured English Query Language SQL SQL sequel ess-queell 1974 Don Chamberlin Ray Boyce SEQUEL:A Structured English Query Language 1974 5 the Proceedings of the May
4 1974 ACM SIGMOD Workshop on Data Description,Access,and Control SQL 1995 System R SQL 25 SQL WWW URL http://www.mcjones.org/system_r/ SQL_Reunion_95/index.html SQL SQL Codd SQL SQL SQL closure SQL SQL schema DDLData Definition Language CREATEDROPALTER SQL SQL SQL DMLData Manipulation Language SQL DDL SELECTINSERTUPDATE DELETE SQL GRANT REVOKE data dictionary SQL meta-data SQL
5 SQL SQL System R SQLEmbedded SQL COBOLFortran C SQL SQL EXEC SQL SQL DBMS SQL Codd Codd SQL SELECT DISTINCT SQL SQL SQL null Chris Date SQL SQL DateCodd SQL SQL 20 80 1977 Larry Ellison Relational Software Incorporated RSI 1970 Codd 1974 SQL IBM Ellison SQL IBM System R Larry Ellison IBM Don Chamberlin ORACLE
6 IBM System R ORACLE System R System R 1979 RSI ORACLEORACLE Ellison ORACLE 1 ORACLE 2 ORACLE 2 SQL CONNECT BY / ORACLE SQL ORACLE RSI1982 Oracle 10 1989 Oracle Redwood Shores Oracle San Carlos SQL 1979 Oracle SQL SQL IBM SQL Codd 1970 1974 SQL IBM SQL 1981 IBM DOS/VSEVM SQL/DS System R 1985 IBM MVS DB2 IMS IBM IT SQL SQL IBM Larry Ellison 20 70 Michael Stonebreaker Unix INGRES Codd INGRES IBM
7 1980 Stonebreaker RTIRelational Technology Incorporated INGRES RTI Ingres Ask Computer Associates OpenIngres INGRES QUEL SEQUEL QUEL SQL SQL QUEL SQL 1986 Ingres SQL QUEL INGRES SQL SQL SQL SQL System R SQL SQL SQL CPU SQL 20 80 SQL Relational Data Systems Informix 1984 SQL Digital Equipment CorporationDEC 1985 RdbRdb SQL RDMLRDML DEC DEC 1988 DEC Rdb 5 SQL 1994 Rdb Oracle
8 1985 Teradata SQL Teradata Intel 8086 SQL TeradataSQL Teradata Ingres Britton-Lee Teradata NCR Teradata NCR AT&T NCR/Teradata Windows NT Unix Teradata TeradataBritton-Lee 1987Tandem SQLNonStop SQL NonStop SQL Tandem Transaction Processing Performance CouncilTPC NonStop SQL Sybase SQL Sybase 1987 Sybase 1993 Windows NT SQL Server Sybase / PC SQL SQL Sybase SQL SybaseDBA Transact-SQL Sybase DBA
9 SQL INSERTUPDATE DELETE SQL Oracle Ada PL/SQL Ada PL/SQL SQL PL/SQLPL/SQL 1988 Oracle SQL*Forms 1992 Oracle7 SQL 20 90 20 70 SQL 20 80 SQL 20 90 SQL SQL SQL SQL Java XML SQL 20 90
10 20 90 SQL 20 60 Simula Smalltalk C++ C C++ C C SQL SQL SQL OracleIBM Informix SQL SQL SQL C++ Java SQL object view SQL C++ C
11 SQL SQL SQL Internet SQL Java Internet SQL Java Java SQL JDBC SQL SQLJ SQL COBOLFortran Java Oracle8i JDBCSQLJ SQLJ JDBC SQL Java Java XML SQL Internet XML XML SQL XML SQL Java XML SQL XML XML Oracle XML SQL SQL XML SQL SQL
12 SQL SQL SQL SQL GROUP BY CUBE ROLLUP SQL 10 10 13 SQL OracleIBM Oracle8i 2 SQL IBM 20 80 SQL Oracle IBM SQL SQL SQL Rosetta Rosetta SQL SQL 20 50 Conference on Data Systems LanguagesCODASYL CODASYL COBOL Data Base Task GroupDBTG 1971 COBOL SQL the National Committee for Information Technology Standards NCITS ANSI SQL H2 American National Standards ANSI 1978
13 CODASYL 1982 ANSI SQL IBM SQL SQL RDL Relational Database Language RDL DB2 SQL 1984 IBM SQL ANSI ISO SQL 1986 100 SQL SQL SQL-86 SQL Integrity Enhancement Feature 1989 SQL COBOLFortran C SQL 1992 SQL-92SQL-92 SQL DROPALTERGRANT REVOKE SQL SQL SQL-92 600 3 EntrySQL-92 SQL-89 IntermediateSQL-92 FullSQL-92
14 SQL-86 SQL-89 SQL SQL-92 SQL SQL-92 SQL-1999 7 2000 1990 SQL SQL-92 SQL3 SQL abstract data typesadt ADT ADT SQL SQL Java SQL-1999 ADT SQL-1999 SQL ID SQL SQL/Foundation SQL- 1999 INSERTUPDATE DELETE LOB WHERE
15 SQL/Foundation SQL SQL SQL SQL-1999 SQL/PSMpersistent stored modules procedure invocation SQL/OLB Java SQL SQL/MED SQL SQL/CLI SQL/Temporal SQL ADT SQL/MM SQL SQL- 1999 CODASYL SQL-1999 Ted Codd SQL-1999 SQL
16 SQL SQL SQL SQL DBA SQL SQL SQL SQL IT SQL SQL SQL / Internet SQL SQL SQL Java XML IT SQL SQL Ken Jacobs Oracle
1970 E.F.Codd A Relational Model of Data for Large Shared Data Banks 1 6 the Association of Computer MachineryACM Communciations of the ACM Codd relational database management system,rdbms Codd IBM 1974 SEQUEL English SQL Structured Query Language sequel 1979 Larry Ellison Bod Miner RSI Codd SQL Oracle V.2 RSI Oracle Oracle SQL ANSI ISO 1 E.F.Codd A Relational Model of Data for Large Shared Data Banks 1970 6 13 Communciations of the ACM 377-387 17
18 ANSI X3.135-1992Database Language SQL ISO/IEC 9075:1992Database Language SQL SQL SQL-92 4 Entry Transitional Intermediate Full SQL Entry SQLOracle8 Entry SQL TransitionalIntermediate Full SQL SQL Oracle DBA SQL Oracle SQL SQL Oracle Oracle 8.1.6 7.3 8.0 8.1 Oracle 8i SQL Oracle 8.0 Object Option Oracle 8.1.6Oracle8i 2 Oracle SQL Oracle
19 O'Reilly www.oreilly.com/catalog/orsqlter Oracle SQL Reference Manual Oracle SQL SQL SQL SQL SQL SQL SQL SQL Oracle SQL SQL*Plus SQL Oracle SQL Oracle SQL*Plus 2 2 SQL*Plus SQLPlus Jonathan Gennick Oracle SQL*Plus:The Definitive Guide O'Reilly & Associate 1999Oracle SQL*Plus Pocket ReferenceO'Reilly & Associate 2000
20 PL/SQL PL/SQL Oracle 3 SQL Oracle SQL SQL SQL SQL Constant width constant width bold SQL Clause Storage_Clause SQL SQL {} 3 PL/SQL PL/SQL Steven Feuerstein Bill Pribye Oracle PL/SQL Programming O'Reilly & Associate1998 Feuerstein PL/SQL
21... [] [] column column_list integer 012 schema string this is a string table tablespace
22 O'Reilly & Associates,Inc. 101 Morris Street Sebastopol, CA 95472 100080 49 B 809 info@mail.oreilly.com.cn WWW http://www.oreilly.com http://www.oreilly.com.cn O'Reilly Deborah Russell Oracle Ken Jacobs 15 Ken Oracle Ken Ken Jonathan Gennick
23 John Beresniewicz Steven FeuersteinBrian Laskey Oracle Richard Sarwal Sandy Venning Suzanne Rhodia Brian McMahonDave FloodBin PanDeb IrwinJoaquin LuceroPaul MarsNick Palmer Frank Foley O'Reilly & Associates Darren Kelly Ann Schirmer Maureen Dempsey Ellie Volckhausen Rob Romano David Chu James CarterDeborah SmithNancy William
...1... 17 SQL... 25... 26 SQL... 28... 28... 31... 38... 39 SQL... 45 SQL... 49... 57 SQL DLL... 57 SQL... 64 i
ii... 139 SQL DML... 139 SQL... 140 SQL... 167 SQL... 179... 180... 187... 197... 211... 220... 229 SQL*Plus... 244... 244 SQL*Plus... 248 SQL*Plus... 252 SQL*Plus... 263 SQL*Plus... 281 SQL*Plus... 285 PL/SQL... 307 PL/SQL... 308... 310... 310... 326... 346... 351... 367
iii SQL... 372 EXPLAIN PLAN... 373 Oracle SQL... 383 SQL*Plus... 394... 400 SQL... 411... 415
SQL SQL SQL Oracle SQL SQL SQL SQL SQL Oracle SQL 25
26 SELECT ename, deptno, sal, comm. FROM scott.emp WHERE hiredate > '01-JAN-00'; SQL 200011 ename deptno sal comm SQL 3 Oracle Oracle SQL SQL SQL Oracle PL/SQLProcedural Language/SQL /SQL SQL SQL Oralce SQL SQL SQL A Z 0 9
SQL 27 + - * =?! @ () _., <> $ # Oracle # $ & SQL SQL*Plus SQL*Plus SQL*Plus SQL SQL Oracle SELECT SELECT ename,empno,sal FROM scott.emp WHERE sal>500; SELECT ename, empno, sal FROM scott.emp WHERE sal > 500; SQL Access Oracle
28 SQL SQL Oracle Oracle schema objects 3 SQL Oracle 1-30 _ Oracle $ # SQL
SQL 29 / SQL schema.object_name.object_part@dblink schema Oracle object_name object_part dblink
30 scott.emp. schema. SQL scott emp test SELECT ename, empno, sal FROM scott.emp@test WHERE sal > 500; schema.table_name {PARTITION (partition) SUBPARTITION (subpartition) } schema Oracle table_name partition subpartition partition-extended table name
SQL 31 Oracle 3 Oracle SQL CHAR [(length)] 2000 length VARCHAR2 [(length)] 4000 length NCHAR [(length)] National Character LanguageNLS 2000 2000 length NVARCHAR2 [(length)] NLS 4000 4000 length LONG 2GB RAW 2000 Oracle RAW
32 LONG RAW 2GB Oracle LONG RAW ANSI SQL CHARACTER CHARACTER VARYING CHAR VARYING NATIONAL CHARACTER NATIONAL CHAR NATIONAL CHARACTER VARYING NATIONAL CHAR VARYING NCHAR VARYING Oracle BLOB 4GB Oracle BLOB BLOB LOB CLOB 4GB NCLOB NLS 4GB Oracle NCLOB BFILE Oracle 4GB
SQL 33 Oracle NUMBER NUMBER 10-130 9.99999...x 10 125 38 NUMBER [(precision[,scale])] precision 1~38 Scale -84~127 ODBC 0 Oracle 38 ODBC 0 Oracle NUMBER ANSI SQL FLOAT NUMERIC DECIMAL NUMBER INTEGER INT SMALLINT DOUBLE PRECISION REAL
34 Oracle DATE Oracle DATE century year month day hour minute second DATE / Oracle INIT.ORA NLS_DATE_ FORMAT Oracle DD-MON-YY NLS_DATE_FORMAT SQL 2076 7 4 T_DATE INSERT INTO sample_table (t_date) VALUES ('07-JUL-76'); / Oracle TO_ DATE 07/04/76 SQL Oracle TO_DATE INSERT INTO sample_table (t_date) VALUES (TO_DATE('07-JUL-76','MM/DD/YY')); TO_DATE NULL SQL NULL NULL