Oracle Database 10g SQL (Osborne ORACLE Press Series)

C

cached SQL statements, 538

calculations, inter-row, 235 “244

CALL statement, 350

CARDINALITY operator, 421

cardinality_example() procedure, 421

Cartesian products, 47 “48, 59 “60

CASCADE keyword, 274, 304, 416

CASE expressions, 191 “194

vs. multiple queries, 533 “534

searched, 192 “194

simple, 191 “192

types of, 191

case-sensitivity

of Java, 507

of SQL statements, 539

CAST operator, 424

Catch clause, 498

CEIL() function, 74

centered average, performing, 226 “227

central plan tables, creating, 542

century

datetime formatting parameter for, 105

rules used to determine, 114

CHAR class (oracle.sql), 517 “518

CHAR data type, 12, 552

CHAR variable, defining, 157

character functions, 62 “71

CharacterSet object (oracle.sql), 517

char_buffer_var, 438

CHECK constraint, adding, 300 “301

CHECK OPTION constraint, 324, 326

child node, 195

child table, 16, 254

CHR() function, 64 “65

classes, 368

CLASSPATH environment variable, 479, 507

CLEAR COLUMNS command, 152

CLOB and BLOB, vs. LONG and LONG RAW types,  429

clob_content table, 430

CLOBs (character LOBs), 429, 554

adding content to, 431 “432

freeing temporary, 454

initializing, 431

loading BFILE data into, 464 “466

temporary, 447 “448

using, 431 “432

CLOBs and NCLOBs, implicit conversion between, 472 “473

CLOSE statement ( cursors ), 340

CLOSE() function, 442 “443

close() method (JDBC), 491, 500

Codd, Dr. E. F., 3

COLLECT operator, 424

collection elements

appending to a collection, 409

getting the number of, 406

removing, 408, 412

collection methods , 405 “414

collection type, multilevel, 399 “401

collection_method_examples package, 406

delete_address(2), 408

display_addresses, 408

collections, 389 “425

getting the greatest index number in, 410

getting the smallest index number in, 410

introduced, 390 “391

Oracle 10g enhancements to, 414 “425

types of, 390

using in PL/SQL, 401 “414

why you would want to use, 391

collection_schema_9i.sql, 400

collection_schema_10g.sql, 414

collection_schema.sql, 391, 403, 406

collection_user.sql, 402, 404

colon (:), when referencing a bind variable, 539

column aliases, 33 “34

COLUMN command, 148 “151

column comments, getting, 308

column constraints, getting information on, 306 “307

column default values

defining, 255

overriding, 255

using, 255 “256

column formatting, clearing, 151 “152

column names , substituting using variables , 154 “155

column objects, defining, 371

column output, merging using concatenation, 34

column references, fully qualified, 532 “533

column values

declaring variables to store, 338

JDBC get methods to read, 490 “491

quotes in, 250

reading from a ResultSet object, 488 “491

retrieving, 43

specifying null values, 249

supplying to multiple columns, 249

using a variable for, 154

columns, 2, 26

adding, 298

changing data type of, 299

changing default value of, 299

defining using nested tables, 395 “396

defining using varray type, 392

dropping, 299

formatting, 148 “150

getting information on, 297

modifying, 298 “299

resizing, 299

selecting all from a table, 29

setting to NULL in Java, 511

sorting, 43 “44

specifying null values for, 249

supplying values to multiple, 249

using in arithmetic, 32 “33

COMMENT statement, 308

comments, adding to a table, 308

COMMIT statement, 259 “260

commit() method, 497

Communications of the ACM, 2

COMPARE() function, 443 “445

compare_example() procedure, 444 “445

comparison operators, 37 “39, 167

complex views, 321, 327 “328

COMPUTE command, 161

CONCAT() function, 65

concatenation operator (), 34

concurrent transactions, 262 “263

isolation levels, 264 “265

separation of, 262 “263

conditional logic (PL/SQL), 335

CONNECT BY clause, and START WITH clause, 196

CONNECT BY PRIOR clause, 196, 199 “200

CONNECT command (SQL*Plus), 10, 295

Connection object (JDBC), 482 “483, 486 “487

consistency (transaction property), 262

CONSTRAINT clause, 14, 303 “304

constraints

adding, 300

deferred, 304 “305

disabling, 301, 303 “304

dropping, 303

enabling, 304

getting information on, 305 “307

table of, 300

CONSTRUCTOR FUNCTION clause, 385

constructor method signatures, declaring, 385

constructors

explained, 372

user -defined, 385 “387

conversion functions, 76 “83

COPY() function, 445 “447

copy_example() procedure, 446

correlated subqueries, 173 “176

EXISTS with, 174 “176

NOT EXISTS with, 174 “176

cost of performing queries, comparing, 541 “548

cost-based optimization, 542, 547 “548

COUNT() function, 90 “91, 95, 197, 349, 406 “408

CREATE ANY DIRECTORY database privilege, 433

CREATE DIRECTORY statement, 433

CREATE FUNCTION statement, 352 “353

CREATE GLOBAL TEMPORARY TABLE statement, 295

CREATE INDEX statement, 316 “317

CREATE OR REPLACE FUNCTION statement, 352

CREATE OR REPLACE PROCEDURE statement, 347

CREATE OR REPLACE TRIGGER statement, 360 “361

CREATE OR REPLACE TYPE statement, 368

CREATE OR REPLACE VIEW statement, 328

CREATE PACKAGE BODY statement, 356

CREATE PACKAGE statement, 355

CREATE PROCEDURE statement, 25, 347, 351 “352

CREATE PUBLIC SYNONYM statement, 370

CREATE PUBLIC SYNONYM system privilege, 284

CREATE ROLE statement, 285

CREATE ROLE system privilege, 285

CREATE SEQUENCE statement, 309 “311

CREATE SESSION system privilege, 273

CREATE SYNONYM statement, 283

CREATE SYNONYM system privilege, 283

CREATE TABLE statement, 294, 359, 372, 416 “417

customers table, 14

employees table, 19

products table, 16

product_types table, 15

purchases table, 17

salary_grades table, 19

syntax of, 294 “295

CREATE TRIGGER statement, 359 “361

CREATE TYPE BODY statement, 370

CREATE TYPE statement, 368 “369, 390 “391, 395

CREATE UNIQUE INDEX statement, 317

CREATE USER statement, 11, 272

CREATE USER system privilege, 276, 285

CREATE VIEW statement, 321 “322

CHECK OPTION constraint, 324

READ ONLY constraint, 324 “325

CreateStatement() method of Connection object, 487

CREATETEMPORARY() function, 447 “449

CROSS JOIN keywords, 60

cross joins, using SQL/92, 59 “60

CUBE operator, 201, 206 “207

with analytic functions, 220 “221

GROUPING() function with, 207, 209

CUME_DIST() function, 221 “222

cumulative sum, performing, 224 “225

current date, in session time zone, 122

CURRENT_DATE() function, 122

CURRENT_TIMESTAMP() function, 128 “129

CURRENTV() function, 239 “240

currval pseudo column, 312

cursors (PL/SQL), 337 “342

closing, 340

declaring, 338

fetching rows from, 339

and FOR loops , 341 “342

opening, 338

steps when using, 337

customer_id column (customers table), 14

customer_id column (purchases table), 18

customers table, 14 “15

customers table columns, 14 “15

customers_associative_array() procedure, 414 “415

CYCLE option (sequences), 313