Oracle SQL Jumpstart with Examples- P15

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:32

Thêm vào BST

Báo xấu

92
lượt xem 15
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P15: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle SQL Jumpstart with Examples- P15

A.16 FACTS.SQL 621 exception when others then dbms_output.put_line('PROC: factGenerate '||SQLERRM(SQLCODE)); rollback; end; / create or replace procedure facts (i IN integer) is j integer; begin for j in 1..i loop factsGenerate; delay(10000000); --dbms_output.put_line('Facts: '||to_char(j)); end loop; end; / set serveroutput on; exec dbms_output.enable(10000000); set timing on; truncate table sales; exec facts(1000); exec dbms_output.disable; set serveroutput off; declare cursor cSales is select * from sales order by sale_qty; begin for rSales in cSales loop update sales set sale_date = (SYSDATE + 300) - rand(500); commit; end loop; end; / SPOOL OFF; Appendix A
This page intentionally left blank
B Please note that these scripts should be tested before use in a production environment. B.1 Tables set wrap off linesize 132 pages 80 column tab format a20 column col format a15 column pos format 990 column typ format a10 column tbs format a25 BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES select t.table_name "Tab" ,c.column_name "Col" ,c.column_id "Pos" ,c.data_type "Typ" ,DECODE(c.nullable,'N','NOT NULL',NULL) "Null" ,t.tablespace_name "Tbs" from user_tables t, user_tab_columns c where t.table_name = c.table_name order by t.table_name, c.column_id; B.2 Constraints set wrap off linesize 132 pages 80 column tab format a20 column key format a10 column cons format a20 column col format a10 623
624 B.3 Indexes column pos format 990 BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES select t.table_name "Tab" ,decode(t.constraint_type,'P','Primary','R','Foreign','U','Alternate','Unknown') "Key" ,t.constraint_name "Cons" ,c.column_name "Col" ,c.position "Pos" from user_constraints t, user_cons_columns c where t.constraint_type in ('P','R','U') and t.table_name = c.table_name and t.constraint_name = c.constraint_name order by t.table_name, t.constraint_type, c.position; B.3 Indexes set wrap off linesize 132 pages 80 column tab format a25 column typ format a5 column ind format a25 column col format a20 column pos format 990 column tbs format a25 BREAK ON tab NODUPLICATES SKIP 2 ON NAME NODUPLICATES Select t.table_name "Tab" ,decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap','FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Typ" ,t.index_name "Ind" ,c.column_name "Col" ,c.column_position "Pos" ,t.tablespace_name "Tbs" from user_indexes t, user_ind_columns c where t.table_name = c.table_name and t.index_name = c.index_name and t.index_type not in ('IOT - TOP','LOB') order by t.table_name, t.index_name, c.column_position;
C The authors of this book can be contacted at the following e-mail addresses: oracledbaexpert@earthlink.net carolmdieter@yahoo.com Oracle Technology Network at http://technet.oracle.com or http:// otn.oracle.com is an excellent source for entire Oracle reference docu- mentation sets. Metalink at http://metalink.oracle.com is also excellent and a source of current information from support calls, questions, and answers placed by both Oracle users and Oracle support staff. The information on this site is well worth the Oracle licensing fees required. Search for a term such as “free buffer waits” in search engines such as www.yahoo.com. Be aware that not all information will be current and might be incorrect. Verify any information found on Oracle Technet. If no results are found using Yahoo, try the full detailed listings on www.google.com. Try www.amazon.com and www.barnesandnoble.com, where many Oracle titles can be found. C.1 Other titles by the authors: Gavin Powell (www.oracledbaexpert.com) Oracle Performance Tuning for 9i and 10g (ISBN: 1-555-58305-9). Introduction to Oracle 9i and Beyond: SQL & PL/SQL (ISBN: 1-932- 07224-1). 625
626 C.1 Other titles by the authors: Oracle Database Administration Fundamentals I (ISBN: 1-932-07253-5). Oracle Database Administration Fundamentals II (ISBN: 1-932-07284-5). Oracle SQL Exam Cram 2 (ISBN: 0-789-73248-3). Carol McCullough-Dieter Oracle9i Database Administrator: Implementation and Administration (ISBN: 0-619-15900-6). Oracle9i for Dummies (ISBN: 0-764-50880-6). Oracle8i DBA Bible (ISBN: 0-764-54623-6). Oracle8i for Dummies (ISBN: 0-764-50798-2). Several other out-of-print books MUSIC schema scripts can be found from a simple menu on my Web site at the following URL, along with many other goodies, including my resume: www.oracledbaexpert.com/oracle/ OracleSQLJumpstartWithExamples/index.html www.oracledbaexpert.com/resume/resume.doc Software accreditations: Microsoft Word, Powerpoint, Excel, Win2K. ERWin. Paintshop. Oracle Database 10g and Oracle Database 9i.
Index ; (semicolon), 7 PERCENT_RANK, 240 (angle brackets), 17–18 RANK, 240 "" (double quotes, 78 ranking, 240 / (forward slash), 76 REGR, 239 % (percentage character), 82, 102 simple summary, 238 | (pipe character), 18 statistical calculators, 238–39 _ (underscore character), 102 statistical distribution, 240 STATS, 239 STDDEV, 238 ABS function, 182 STDDEV_POP, 238 ADD_MONTHS function, 186 STDDEV_SAMP, 239 Aggregate functions SUM, 238, 242 AVG, 238 VARIANCE, 238 CORR, 239 VAR_POP, 239 COUNT, 238 VAR_SAMP, 239 COVAR_POP, 239 See also Group functions COVAR_SAMP, 239 Aliases CUME_DIST, 240 column, 78, 79, 112 deﬁned, 176, 237 table, 79 DENSE_RANK, 240 ALL clause, 246–49 GROUP_ID, 240 ALTER CLUSTER command, 485 grouping, 240 ALTER INDEX command, 482 GROUPING(), 240 syntax, 482 GROUPING_ID, 241 using, 483 HAVING clause with, 255 ALTER ROLE command, 523–24 MAX, 238 ALTER SEQUENCE command, 493 MEDIAN, 239 ALTER TABLE command, 298, 450, 460– MIN, 238 61 Oracle, using, 238 constraints and, 461–64 PERCENTILE, 240 constraints syntax, 461 627
628 Index syntax, 408 BINARY_FLOAT datatype, 341 ALTER USER command, 509 Binary ﬂoating-point number, 185–86 ALTER VIEW command, 434, 450 BINARY_INTEGER datatype, 541 constraints syntax, 461 Bitmap indexes syntax, 534 deﬁned, 477 American National Standards Institute WHERE clause and, 486 (ANSI), 15–16 See also Indexes JOIN clause, 206–7 Bitmap join indexes, 478, 483 mutable joins, 232 BLOB datatype, 55, 342, 343 Analytical functions, 176 BOOLEAN datatype, 541 AND operator, 105, 107, 126 BREAK command example, 126 COMPUTE command and, 164 illustrated, 126 deﬁned, 162 See also Logical operators example, 162, 163 Angle brackets (), 17–18 syntax, 162 Anti-joins, 230 BTITLE command, 160 avoiding, 230 BTree indexes, 474–75 deﬁned, 208 deﬁned, 474–75 Archiving, 61–62 illustrated, 476 Arithmetic operations, 91–92 Arithmetic operators, 125 deﬁned, 124 CARDINALITY function, 354 example, 125 CASCADE clause, 466–68 illustrated, 125 CASE statements, 556–60 See also Operators deﬁned, 302 Associative arrays, 542 search condition, 556, 557–58 AVG function, 238 searched, 304 selector and expression, 556, 558–60 syntax, 303, 557 Backus-Naur syntax conventions, 75 use of, 556 Base tables, 426 See also Control structures BETWEEN conditional comparison, 104 CAST function, 353 BFILE datatype CEIL function, 182 BFILENAME function, 347 CHAR datatype, 340, 401 deﬁned, 343 Check constraints, 456–58 example, 345–47 deﬁned, 449 use illustration, 346 inline, 456 use of, 343 out-of-line, 456 using, 345–47 using, 458 BFILENAME function, 347 See also Constraints BINARY_DOUBLE datatype, 341 CLOB datatype, 342, 343, 374
Index 629 Clustering, 70–71 for schema objects, 416–19 Clusters, 484–87 single-line, 420 CREATE TABLE syntax for, 486 See also Tables creating, 485–87 COMMIT command, 62–64 deﬁned, 386, 475–76, 484–85 execution, 63, 64, 317 hash, 485 ROLLBACK command comparison, 63– regular, 485 64 sorted hash, 485 saving changes with, 317 types of, 485 Comparison conditions See also Indexes deﬁned, 131 COALESCE function, 194 EXISTS, 272 COLLECT function, 353 IN, 273, 274 Collections, 43 multiple-row subqueries, 272 nested table, 350–52 subqueries and, 269–70 object, 348–54 Complex joins, 230–33 COLUMN command deﬁned, 208, 230–31 column alias, 158 illustrated, 368, 369 formatting, turning off, 156 See also Joins settings, 154 Complex views on single line, 158 creating, 430–33 syntax, 154 deﬁned, 427 use illustration, 156 See also Views using, 155–56 Composite partitions, 68, 402 Columns Composite queries, 88 adding, 408–09 deﬁned, 81, 285 aliases, 78, 79, 112 example, 88 changing, 409–11 set operators, 285–86 datatypes, 53, 55 using, 286–89 formatting, 154–60 See also Queries names, 53–54 Compound expressions, 302 non-nullable, 411 COMPUTE command, 163 removing, 411–12 BREAK command and, 164 renaming, 411 deﬁned, 163 selecting, 55 syntax, 164 unused, 411 Concatenation operator, 128 updating, 332 deﬁned, 124 See also Rows; Tables example, 128 Comments See also Operators adding, 416–20 CONCAT function, 180 inline, 419–20 Conditional comparisons multiple-line, 419 ALL, 104–5 Index
630 Index anti (!=, ), 102 out-of-line, 448, 453–56 ANY, 104–5 primary key, 447, 449, 451–52 BETWEEN, 104 REF, 449, 459–60 deﬁned, 101 Referential Integrity, 465 equi (=), 102 RELY state, 464 EXISTS, 103–4 renaming, 464–65 IN, 103 states, 463–64 LIKE, 102 types, 448–49 range (, ==), 102 unique, 448, 451–52 SOME, 104–5 uses, 448–49 types of, 102–5 USING INDEX clause, 464 Conditions, 131–33 Constraint views comparison, 131, 269–70 creating, 429–30 deﬁned, 131 deﬁned, 427 ﬂoating-point, 131 inserted rows requirement, 431 NULL, 131–32 inserting/updating rows with, 439 object collection, 132–33 See also Views XML, 132 Controlﬁles, 61 CONNECT BY clause, 39 Control structures, 553–67 CONNECT_BY_ROOT operator CASE statement, 556–60 deﬁned, 128, 290 FORALL command, 561, 565 illustrated, 292 FOR loop, 560, 561–63 using, 290–92 GOTO statement, 565, 566 Constraints, 447–69 IF statement, 554–56 adding, to existing tables, 462–63 iteration/repetition, 554, 560–65 ALTER TABLE command and, 461–64 LOOP...END LOOP, 561, 564–65 applying, 449 NULL statement, 565, 566–67 cascading of, 468 selection, 553, 554–60 check, 449, 456–58 sequence controls, 554, 565–67 deﬁned, 448 types of, 553–54 dropping, 465–68 WHILE loop, 560, 563–64 dropping, with CASCADE clause, 466–68 Conversion functions, 190–94 ENABLE/DISABLE states, 463–64 date formats, 191–94 EXCEPTIONS clause, 464 deﬁned, 177 foreign key, 447, 449, 452–56 illustrated, 179 function of, 447 importance, 190 inline, 448 number formats, 190–91 managing, 449–60 TO_CHAR, 190, 193 metadata views, 469 TO_CLOB, 190 modifying, on existing tables, 463 TO_DATE, 190, 201, 202 NOT NULL, 448 TO_N, 190
Index 631 TO_NUMBER, 190 deﬁned, 207 See also Single-row functions example, 211 Correlated subqueries See also Joins deﬁned, 268, 279 CUBE clause, 257–58 regular subqueries vs., 279–80 example, 257–58 values passed into, 279 implementation, 258 See also Subqueries use of, 255, 257 CORR function, 239 CUME_DIST function, 240 COUNT function, 238 CURRENT_DATE function, 186 COVAR_POP function, 239 CURRENT_TIMESTAMP function, 186 COVAR_SAMP function, 239 CURRENTV function, 261 CREATE INDEX command, 477, 478 CURRVAL pseudocolumn, 490, 495 CREATE ROLE command, 523 CURSOR expression, 302 CREATE SEQUENCE command, 490 Cursor FOR loop implicit cursor, 547–49 CREATE SESSION privilege, 507, 508 deﬁned, 547–48 CREATE SYNONYM command, 499, 500 example, 547–49 CREATE TABLE command, 298, 385, 386 illustrated, 549 with constraints syntax, 450 See also Implicit cursors with detailed constraints syntax, 451 Cursors, 543–49 pseudo-like syntax, 389 building, dynamically, 552–53 as subquery, 387 explicit, 543–44 syntax, 387–88 implicit, 544–49 syntax for clusters, 486 REF, 553 syntax for external table, 398 syntax for hash partitions, 404–5 syntax for IOT, 397 Database modeling syntax for list partitions, 403–4 evolution, 1–5 syntax for object table, 391 ﬁle system, 1 syntax for range-hash partitions, 405, 406 hierarchical, 2 syntax for range-list partitions, 405–6 network, 3 syntax for range partitions, 403, 404 object, 3–4 syntax for relational table, 390 object-relational, 4–5 syntax for temporary table, 393 relational, 3, 4, 8–14 CREATE VIEW command Databases constraints syntax, 460 name, 20 OR REPLACE option, 433 Oracle, evolution of, 6–8 syntax, 427–33 origin, 6 WITH CHECK OPTION clause, 430 relational, 5–6 Cross-joins, 210–12 spreadsheets vs., 52–53 creation in error, 211 standby, 69–70 data merge, 210 XML and, 373–80 Index
632 Index Data Deﬁnition Language. See DDL TIMESTAMP, 341 Data dictionary views, 442–45 user-deﬁned, 347–48 deﬁned, 442 VARCHAR2, 55, 340 groups, 442–43 XMLType, 361–62 Dataﬁles, 61 DATE datatype, 55, 340–41 Data Manipulation Language. See DML Dates commands column, 158 DATA tablespace, 61 formatting, 158–60 Datatypes, 341–55 Datetime functions, 186–89 associative arrays, 542 ADD_MONTHS, 186 BFILE, 343, 345–47 CURRENT_DATE, 186 BINARY_DOUBLE, 341 CURRENT_TIMESTAMP, 186 BINARY_FLOAT, 341 deﬁned, 177 BINARY_INTEGER, 541 EXTRACT, 187–89 binary object, 342–43 illustrated, 178 BLOB, 55, 342, 343 LAST_DAY, 186 BOOLEAN, 541 LOCALTIMESTAMP, 186 CHAR, 342, 403 MONTHS_BETWEEN, 186 CLOB, 342, 343 NEXT_DAY, 186 column, 53, 55 ROUND, 187 complex, 342–55 SYSDATE, 186 DATE, 55, 340–41 SYSTIMESTAMP, 186 FLOAT, 340 TRUNC, 187, 200, 202 INTEGER, 340, 401 See also Single-row functions LONG, 342 DBMS_REDEFINITION package, 412–13 LONG RAW, 342 DBMS_SQL package, 551 NCHAR, 340 DDL, 15 NCLOB, 342 automatic command commitment, 63 NUMBER, 55, 340, 541 commands, 316 NVARCHAR2, 340 Oracle Database 10g, 40 object collection, 348–52 DECODE function, 194–95, 197, 200, 201, PL/SQL, 541–43 202 RAW, 342 DEFINE command, 151 RECORD, 541–42 DELETE command, 334–36 REF, 343, 344–45 for all rows, 336 reference, 542 deﬁned, 316 reference pointer, 343–47 for multiple rows, 334–35 ROWID, 341 for one row, 334 simple, 339–41 syntax, 333 SMALLINT, 340 Deleting rows, 334–36 special, 355 all, 336
Index 633 many, 334–35 use of, 90 one, 334 Dynamic HTML (DHTML), 358 See also Rows Dynamic SQL, 550–53 Denormalization, 11–13 deﬁned, 11 performance factors, 12–13 Environmental settings requirement, 11 adjusting, 171 DENSE_RANK function, 240 ARRAY[SIZE], 139 DESC command, 454 AUTO[COMMIT], 139 DISTINCT function, 88, 92–93 CMDS[EP], 139 group functions and, 246–49 COLSEP, 139 using, 92–93 default, 146 DML commands, 315–39 deﬁned, 138 commit/rollback, 63 ECHO, 140 deﬁned, 15, 315 ESC[APE], 140 DELETE, 316, 334–36 HEAD[ING], 140 executing triggers from, 539 LINE[SIZE], 140 INSERT, 315, 324–30 LONG, 140 with joins, 440–41 MARK[UP] HTML, 141 MERGE, 316, 336–39 NEWP[AGE], 141 NOT NULL constraint, 316 NULL, 141–42 pointers, 316–17 NUMF[ORMAT], 142 syntax changes, 40 NUMW[IDTH], 142 undoing, 217 PAGES[IZE], 142 UPDATE, 315, 330–34 PAU[SE], 142–43 views and, 437–41 RECSEP, 143 Domain indexes, 476 RECSEPCHAR, 143 Double @@ command, 152 SERVEROUT[PUT], 143–44 Double quotes (""), 78 SQLP[ROMPT], 144 DREF function, 345 TERM[OUT], 146 DROP INDEX command, 482 TIMI[NG], 146 Dropping tables, 414–16 WRAP, 146 with DROP TABLE, 414–15 See also SQL*Plus truncating vs., 415–16 Environmental variables, 137 DROP TABLE command, 414–15 Equi-joins, 230 DROP VIEW command, 434, 435 deﬁned, 208 DUAL table, 88, 89–90 uses, 230 deﬁned, 88 EVALUATE operator, 44, 312 illustrated, 89 Exception trapping, 533–34 information, 90 EXECUTE IMMEDIATE command queries, 89 deﬁned, 550 Index
634 Index uses, 551 illustrated, 188 using, 552 multiple-value pattern match and, 378 EXISTS clause, 103, 230, 272 single-value pattern match and, 378 EXISTSNODE function, 376 EXTRACTVALUE function Explicit cursors, 543–44 deﬁned, 376 programmer access, 543 demonstrating, 377, 378 using, 545 multiple-value pattern match and, 378 variations, 544 single-value pattern match and, 378 See also Cursors Expressions, 301–14 basic, 302 Feature-related users, 505 brackets, 302 Fifth Normal Form (5NF), 10 CASE statements, 302–4 Filtered queries, 82–83 compounding, 302 deﬁned, 81 copying, into SELECT columns list, 121 example, 82 CURSOR, 302 illustrated, 83 deﬁned, 301 See also Queries functions, 302 First Normal Form (1NF), 8, 9 lists, 302 Fishhook self-joins, 228–29 modeling, 305 Flashback queries, 292–97 objects, 304 automated undo requirement, 293 object type constructors, 305 deﬁned, 81, 292 Oracle Expression Filter and, 309–14 execution, 295 in ORDER BY clause, 119, 120 illustrated, 295 regular, 305–9 syntax, 293–94 scalar subqueries, 302 using, 294–97 types of, 302 versions, 293 eXtensible Markup Language. See XML versions, illustrated, 296 eXtensible Style Sheets (XSL), 358 versions, with pseudocolumns, 297 deﬁned, 360 versions query pseudocolumns, 294 documents, 360 See also Queries External tables FLOAT datatype, 340 CREATE TABLE syntax, 398 Floating-point condition, 131 creating, 398–401 FLOOR function, 182–83 deﬁned, 384 FORALL command, 561, 565 reading, 400 Foreign key constraints, 447, 452–56 See also Tables deﬁned, 449 EXTRACT function, 187–89, 369, 376, 377 indexes, 483 deﬁned, 187, 376 nullable, 456 demonstrating, 377, 378 out-of-line, 453–56 examples, 188 table name, 453
Index 635 See also Constraints object reference, 91, 176 FOR loop, 560, 561–63 placement, 176–77 deﬁned, 560 single-row, 91, 175–203 examples, 561–63 user-deﬁned, 91, 176 nested, 562 using, 90–91 statement syntax, 561 See also speciﬁc functions See also Control structures Functions (PL/SQL) Formatting deﬁned, 535 breaks, 160–65 using, 535–37 column, 154–60 COLUMN command, 156 date models, 192 GETSTRINGVAL function, 369 dates, 158–60 GETTIME function, 256, 257, 537 lines, 160–65 GOTO statement, 565, 566 number models, 191 deﬁned, 565 pages, 160–65 example, 566 query output, 153–65 GRANT command, 513, 518 Forward slash (/), 76 Granting privileges, 511–18 Fourth Normal Form (4NF), 10 object, 512 FROM clause on roles, 524–27 deﬁned, 97 several users at once, 513 multiple-column subquery, 278 system, 512 Full outer joins, 224–25 See also Privileges deﬁned, 208 GREATEST function, 195 example, 225 GROUP BY clause, 249–60 illustrated, 210 column list, 250, 253 returns, 224 CUBE clause, 257–58 See also Outer joins execution, 250 Function-based indexes extending, 255–60 deﬁned, 475 GROUPING SETS clause, 258–60 using, 481 HAVING clause, 253–55 See also Indexes parts, 236 Functions, 88 ROLLUP clause, 256–57 aggregate, 176 rules, 250 analytical, 176 sort order, 252 combining, 196–203 syntax, 235–36 datatype conversion, 91 uses, 249 deﬁned, 175 using, 249–60 group, 91, 237–49 Group functions, 91, 237–49 grouping, 176 aggregate, 237–44 object collection, 352–54 ALL clause and, 246–49 Index
636 Index analytic, 237 illustrated, 87, 291 categories, 237 meaningful, 87 deﬁned, 91 pseudocolumns, 290 DISTINCT clause and, 246–49 uses, 86 enhancing, 241–45 using, 290–92 null values and, 245 See also Queries SPREADSHEET clause, 237 Hierarchical query operators, 128–29, 290 statistical, 237 CONNECT_BY_ROOT, 290 See also Functions deﬁned, 124 GROUP_ID function, 240 illustrated, 129 Grouping/aggregated queries, 83, 84 PRIOR, 128, 290 deﬁned, 81 See also Operators example, 83 Hierarchical self-joins, 228–29 illustrated, 84 deﬁned, 228 See also Queries example, 229 GROUPING function, 240 See also Self-joins Grouping functions, 176 HTML, embedding scripts in, 168–71 GROUPING_ID function, 241 HTTP Server Grouping rows, 250–53 document directory, 169 in single table query, 251 installation, 31 in two-table query, 251 running iSQL*Plus, 168 GROUPING SETS clause, 258–60 starting, 31 deﬁned, 258 stopping/restarting, 172 example, 259 Hypertext Markup Language (HTML), 357 subtotals, 259 documents, 357–58 Dynamic (DHTML), 358 Hash clusters, 485 Hash partitions, 68 IF statement, 554–56 CREATE TABLE syntax, 404–5 example, 554–56 deﬁned, 402 splitting results using, 556 See also Partitions syntax, 554 HAVING clause, 236 Implicit cursors, 544–49 with aggregate functions, 255 cursor FOR loop, 547–49 ﬁltering grouped results with, 253–55 execution results, 545 restricting groups with, 254 internal SQL, 545–47 Hierarchical data model, 2 opening/closing, 544 Hierarchical queries, 86–88, 289–92 single-row SELECT, 547 CONNECT_BY_ROOT, 128 using, 546 deﬁned, 81, 289 See also Cursors example, 86–88, 290–92 IN clause, 230
Index 637 comparison condition, 273, 274 Inline comments, 419–20 use of, 230 Inline constraints Indexes, 471–84 check, 456 ascending, 476 deﬁned, 448 attributes, 476–77 Inline views, 281–82 bitmap, 475, 484 deﬁned, 268 bitmap join, 476, 481 example, 281–82 BTree, 474–75 multilayer nested, 282 changing, 482–83 subquery, 432–33 cluster, 475–76, 484–87 See also Subqueries composites, 477 Inner joins, 212–17 compression, 477 deﬁned, 208 contents, 471–72 illustrated, 209 creating, 477–82 INSERT command, 324–30, 457, 458 deﬁned, 471–72 deﬁned, 313 descending, 476 multiple-table, 325–28 domain, 476 in queries, 324–25 dropping, 482–83 sequences in, 496–97 entries, 472 subqueries, 281 function-based, 475, 481 Inserting rows index-organized table (IOT), 475 with INSERT command, 324–30 null values, 477 one, 325–26 preﬁx, 484 with subquery, 326–27 reverse keys, 477 See also Rows Skip Scanning, 484 IN set membership, 103 sorting, 477 INSTR function, 180 too many, 473 INTEGER datatype, 340, 401 types of, 474–77 INTERSECT operator uniqueness, 477 deﬁned, 129, 286 use factors, 473–74 returns, 287, 288 use intent, 473 IS ANY, 133 WHERE clauses and, 484 IS A SET condition, 133 Index-organized tables IS EMPTY, 133 as BTree tables, 398 IS OF TYPE, 133 CREATE TABLE syntax, 397 IS PRESENT, 133 creating, 397–398 ISQL*Plus, 17, 31–34 deﬁned, 384, 397 deﬁned, 19 See also Tables direct database access, 32 Index-organized tables (IOTs), 475 display, customizing, 172–74 INDEX tablespace, 61 environment, 34 INITCAP function, 180 environmental settings, 171 Index
638 Index HTTP Server running, 168 mutable, 208, 230–33 logging into, 166 natural, 208, 209, 212–17 login screen, 32 objective, 212 mains screen, 32–33 Oracle proprietary format, 206 mimic features, 167 outer, 208, 209, 210, 217–25 output, 168 range, 208, 230 primary interface, 167 right outer, 208, 210, 223–24 query processing, 167 self, 208, 225–29 query results, 33 types of, 207–10 SQL*Plus vs., 168, 171 views with, 430–32 steps, 31–34 troubleshooting, 171–72 using, 165–74 LAST_DAY function, 186 variables, 171 Left outer joins, 218–23 See also SQL tools ANSI format, 221 ITERATION_NUMBER function, 262 deﬁned, 208 example, 222 illustrated, 209 Java, improvements in Oracle Database 10g, Oracle format, 220 45 returns, 218–19 JOIN clause, 205, 206–7 See also Outer joins with ON clause, 217 LENGTH function, 180 syntax, 207 LIKE comparison operator, 100, 102 use of, 207 List partitions, 67–68 without ON clause, 216 CREATE TABLE syntax, 403–4 Joining tables, 205–33 deﬁned, 402 Join queries, 84–85 See also Partitions deﬁned, 81 Lists, 300 example, 84–85 LOCALTIMESTAMP function, 186 illustrated, 85 Locks, 318–19 See also Queries deﬁned, 318 Joins table-level, 318 ANSI format, 206–7 LOCK TABLE command, 318–19 anti, 208, 230 Logging, 61–62 complex, 208, 230–33, 368 Logical operators, 126–28 cross, 207, 210–12 AND, 105, 107, 126 DML and views with, 440–41 deﬁned, 124 equi, 208, 230 NOT, 105, 128 formats, 206–7 OR, 105, 107, 127 full outer, 208, 210, 224–25 precedence, 126 left outer, 208, 209, 218–23 See also Operators
Index 639 Logical standby databases, 69–70 using, 287 LONG datatype, 342 Miscellaneous functions, 194–96 LONG RAW datatype, 342 COALESCE, 194 LOOP...END LOOP, 561, 564–65 DECODE, 194–95, 197, 200, 201, 202 deﬁned, 561 deﬁned, 177 example, 565 GREATEST, 195 statement syntax, 564 illustrated, 179 LOWER function, 180 NULLIF, 195 LPAD function, 180–81 NVL, 115, 116, 195, 199 LTRIM function, 181 UID, 195 USER, 195 USERENV, 195–96 Master-to-slave replication, 68, 69 VSIZE, 196 Materialized views, 426 See also Single-row functions MAX function, 238 MODEL clause, 41 Media datatype, 355 Model expressions, 305 MEDIAN function, 239 MOD function, 183 MEMBER OF, 133 MONTHS_BETWEEN function, 186 MERGE command, 47, 336–39 Multiple columns subqueries, 276–78 deﬁned, 316 deﬁned, 268 enhancements, 336 examples, 276–78 uses, 338 FROM clause, 278 using, 337–39 return, 276 Metadata views WHERE clause, 278 constraints, 469 See also Subqueries datatypes, 354–55 Multiple-line comments, 419 deﬁned, 442 Multiple rows subqueries, 272–76 groups, 442–43 comparison conditions, 272 indexes/clusters, 487–88 deﬁned, 268 listing, 443 returns, 272 naming, 442 using, 273–76 PL/SQL, 568 See also Subqueries security, 530 Multiple-table INSERT command, 327–30 sequences/synonyms, 501 entries, 329, 330 tables, 421–23 example, 327–30 views, 441 script, 329 See also speciﬁc views syntax, 328 MIN function, 238 See also INSERT command MINUS operator MULTISET EXCEPT operator, 130 deﬁned, 129, 286 MULTISET INTERSECT operator, 130 returns, 289 Multiset operators, 43 Index
640 Index deﬁned, 124 deﬁned, 350 MULTISET EXCEPT, 130 example, 350–52 MULTISET INTERSECT, 130 retrieving contents of, 352 MULTISET UNION, 130 See also Collections MULTISET UNION operator, 130 Network data model, 3 MUSIC schema, 34–38 NEXT_DAY function, 186 ARTIST, 35 NEXTVAL function, 325 GENRE, 36 NEXTVAL pseudocolumn, 490, 495–96 GUESTAPPEARANCE, 36 Normalization, 8–11 illustrated, 35 deﬁned, 8 INSTRUMENT, 36 Fifth Normal Form (5NF), 10 INSTRUMENTATION, 36 First Normal Form (1NF), 8, 9 MUSICCD/CDTRACK, 35–36 Fourth Normal Form (4NF), 10 Sales Data Warehouse, 36–38 Referential Integrity, 10–11 SONG, 35 Second Normal Form (2NF), 9 STUDIOTIME, 36 Third Normal Form (3NF), 9–10 Mutable joins, 230–33 NOT NULL constraints, 448, 455 ANSI, 232 NOT operator, 105, 128 deﬁned, 208, 230 example, 127 example, 231 illustrated, 128 illustrated, 232 See also Logical operators See also Joins NULL condition, 131–32 NULLIF function, 195 NULL statement, 565, 566–67 NANVL function, 115, 116, 185 deﬁned, 565 Natural joins, 212–17 example, 566–67 deﬁned, 208 Null values, 88, 93–94 example, 212 deﬁned, 88 illustrated, 209 facts, 93–94 ON clause, 215–17 group functions and, 245 USING clause, 213–15 handling, 113 without USING clause, 213 handling methods, 115 See also Joins index, 477 NCHAR datatype, 340 output, 114 NCLOB datatype, 342 sorting and, 113–16 Nested subqueries, 280–81 NUMBER datatype, 55, 340, 541 deﬁned, 268, 280 Number functions, 182–86 example, 280–81 ABS, 182 multilayer, 281 ACOS, 184 See also Subqueries ASIN, 184 Nested table collections, 350–52 binary ﬂoating-point number, 185–86