OCP Oracle8i SQl & PL/SQL考试学习指南 英文版PDF电子书下载

Date Functions 60

Conversion Functions 63

Chapter Summary 67

Two-Minute Drill 67

Fill-in-the-Blank Questions 69

Chapter Questions 69

Fill-in-the-Blank Answers 73

Answers to Chapter Questions 73

2 Advanced Data Selection in Oracle 77

Displaying Data from Multiple Tables 78

select Statements That Join Data from More Than One Table 79

Creating Outer Joins 85

Joining a Table to Itself 88

Group Functions and Their Uses 92

Identifying and Using Group Functions 92

Using the group by Clause 96

Excluding group Data with having 102

Using Subqueries 104

Understanding and Defining Subqueries 105

Listing and Writing Different Types of Subqueries 109

Multiple-Column Subqueries 113

Writing Multiple-Column Subqueries 114

NULL Values and Subqueries 116

Subqueries in a from Clause 119

Producing Readable Output with SQL*Plus 122

Entering Variables 123

Customizing SQL*Plus Environments 129

Producing Readable Output 137

Creating and Executing Scripts 142

Saving Customizations 144

Chapter Summary 146

Two-Minute Drill 146

Fill-in-the-Blank Questions 150

Chapter Questions 150

Answers to Chapter Questions 155

Fill-in-the-Blank Answers 155

3 Creating Oracle Database Objects 159

Creating the Tables of an Oracle Database 160

Describing Tables 161

Creating Tables 163

Datatypes and Column Definitions 169

Altering Table Definitions 175

Dropping, Renaming, and Truncating Tables 180

Including Constraints 183

Describing Constraints 184

Creating and Maintaining Constraints 187

The Oracle Data Dictionary 202

Available Dictionary Views 202

Querying the Data Dictionary 209

Manipulating Oracle Data 213

Adding New Rows to a Table 213

Making Changes to Existing Row Data 217

Deleting Data from the Oracle Database 219

The Importance of Transaction Control 220

Chapter Summary 226

Two-Minute Drill 227

Fill-in-the-Blank Questions 230

Chapter Questions 230

Fill-in-the-Blank Answers 235

Answers to Chapter Questions 235

4 Creating Other Database Objects in Oracle 239

Creating Views 240

Creating Simple Views 241

Creating Views That Enforce Constraints 248

Creating Complex Views 252

Modifying and Removing Views 257

Other Database Objects 260

Overview of Other Database Objects 261

Using Sequences 262

Using Indexes 268

Using Public and Private Synonyms 277

User Access Control 280

Creating Users 281

Granting and Revoking Object Privileges 289

Using Roles to Manage Database Access 296

Chapter Summary 302

Two-Minute Drill 303

Fill-in-the-Blank Questions 306

Chapter Questions 306

Answers to Chapter Questions 310

Fill-in-the-Blank Answers 310

5 Introducing PL/SQL 313

Overview of PL/SQL 314

Benefits of PL/SQL 315

Parts of a PL/SQL Block 319

Declaring and Using Variables 327

Executing PL/SQL Code 334

Writing Executable Statements 339

Writing Executable PL/SQL Statements 339

Nesting PL/SQl Blocks 342

Executing and Testing PL/SQL 345

Using Code Conventions 350

Writing Control Structures 352

Constructing an if-then Statement 353

Using Loops 355

Using Logic Tables 361

Using Nested Loops and Labels 364

Two-Minute Drill 368

Chapter Summary 368

Fill-in-the-Blanks 370

Chapter Questions 370

Fill-in-the-Blank Answers 371

Answers to Chapter Questions 371

6 Database Programming with PL/SQL 373

Interacting with the Oracle Database 374

Using select in PL/SQL Code 375

Declaring PL/SQL Variable Types Dynamically 378

Writing DML Statements in PL/SQL 382

Transaction Processing in PL/SQL 385

Determining SQL Statement Outcome 388

Working with Composite Datatypes 391

Creating PL/SQL Records 392

Using %rowtype to Create Records 397

Creating PL/SQL Tables 399

Creating PL/SQL Tables of Records 405

Reviewing PL/SQL Records, Tables, and Tables of Records 408

Using Explicit Cursors 409

Explicit Cursor Handling 409

Using PL/SQL Records to Fetch Data from Cursors 414

Writing cursor for Loops 417

Understanding Advanced Explicit Cursor Concepts 420

Passing Parameters to Explicit Cursors 421

Using the for update and where current of Clauses 423

Using Subqueries in Cursors 426

Error Handling 427

Defining a PL/SQL Exception 428

Trapping Unhandled and Unanticipated Errors 433

Customizing PL/SQL Error Messages 436

Understanding Exception Propagation in Nested Blocks 440

Chapter Summary 444

Two-Minute Drill 444

Fill-in-the-Blanks 446

Chapter Questions 447

Answers to Chapter Questions 451

Fill-in-the-Blank Answers 451

PARTⅡOCP Oracle8i DBA Practice Exams 457

7 OCP Exam I: Introduction to SQL and PL/SQL 457

Practice Exam 1 458

Practice Exam 2 477

Practice Exam 3 500

Answers to Practice Exam 1 517

Answers to Practice Exam 2 530

Answers to Practice Exam 3 544

Index 557