OCP Developer PL/SQL Program Units考试指南 英文版PDF电子书下载

PART Ⅰ Language Syntax 3

1 Overview of PL/SQL 3

An Introduction to the PL/SQL Language 4

PL/SQL Syntax Fundamentals 7

Statements 8

Identifiers 8

Comments 8

Literals 9

Declaration Section 10

Variables 10

Datatypes 11

Constants 14

Other Declared Elements 14

Processing Section 15

Expressions 15

Assignment Statements 21

Conditional Statements 22

Loops 25

Cursors 28

Implicit Cursors 28

Explicit Cursors 30

Cursor Attributes 32

Loops Revisited:The Cursor FOR Loop 33

Advanced Datatype Declaration 34

%TYPE 34

%ROWTYPE 35

Exception-Handling Section 37

System-Defined Exceptions 38

User-Defined Exceptions 39

Working with Blocks 40

Nested Blocks 41

Scope:Declared Elements 42

Exception Scope and Exception Propagation 44

Anonymous Blocks 47

Named Program Units 47

An Introduction to Program Units 47

Chapter Summary 49

Two-Minute Drill 51

Chapter Questions 53

Answers to Chapter Questions 57

2 Procedures 61

Uses of Procedures 62

What Can You Do with Procedures? 63

Where Can You Store Procedures? 64

Creating,Altering,and Dropping Procedures 66

Creating Procedures 66

Altering Procedures 70

Dropping Procedures 72

Executing a Procedure from a PL/SQL Block 73

Invoking Procedures 73

Executing a Procedure from the SQL Plus Command Line 74

Parameters 76

Parameter Declaration Syntax 76

Parameter Datatypes 77

Default Values 77

Parameter Types 78

Positional Notation versus Named Notation 83

Data Dictionary Resources for Procedures 85

USER_OBJECTS 86

USER_OBJECT_SIZE 88

USER_SOURCE 88

USER_ERRORS 89

SHOW ERRORS 91

Chapter Summary 93

Two-Minute Drill 95

Chapter Questions 96

Answers to Chapter Questions 100

3 Functions 103

Uses of Functions 104

Functions versus Procedures 105

Creating,Altering,and Dropping Functions 106

Creating Functions 106

Altering Functions 107

Dropping Functions 108

Invoking Functions 109

Functions Called from PL/QL Expressions 110

Functions Called from SQL Statements 112

Client-Side Functions 113

Parameters 115

Passing Parameters by Reference 117

RETURN 119

Two-Minute Drill 123

Chapter Summary 123

Chapter Questions 124

Answers to Chapter Questions 129

4 Packages 133

Uses of Packages 134

Creating,Altering,and Dropping Packages 137

Creating a Package Specification 137

Creating a Package Body 139

Public versus Private Constructs 140

Global Constructs 141

Altering a Package 143

Dropping a Package 147

Changes to a Package Specification that Require a Change to the Body 149

Invoking Packaged Constructs 151

Referencing Packaged Constructs 151

Using Packaged Constructs 151

Data Dictionary Resources for Packages 156

Chapter Summary 158

Two-Minute Drill 159

Chapter Questions 160

Answers to Chapter Questions 164

5 Triggers 167

Uses of Triggers 168

Creating,Altering,and Dropping Triggers 170

Creating Triggers 171

The:old and:new Qualifiers 175

Conditional Predicates 177

Firing Rules 178

Restrictions 179

Altering Triggers 181

Dropping Triggers 181

Enabling and Disabling Triggers 183

Disabling and Enabling a Named Trigger 183

Enabling and Disabling a Named Table's Triggers 184

lNSTEAD OF Triggers 185

Non-DML Triggers 188

DDL Triggers 189

Data Dictionary Resources for Triggers 190

USER_TRIGGERS 191

Chapter Summar 194

Two-Minute Drill 195

Chapter Questions 197

Answers to Chapter Questions 200

6 Working with Program Units 203

Client-Side versus Server-Side Program Units 204

Invoking Server-Side Program Units 205

Invoking Client-Side Program Units 206

The Trade-0f—Where to Put Them? 207

Local Subprograms 209

Forward Declarations 209

Overloading 214

Creating Overloaded Modules 215

Namespaces for Program Units 217

Initializing Variables with a One-Time-Only Procedure 219

Functions and Purity Levels 221

WNDS and RNDS 224

WNPS and RNPS 224

TRUST 225

Purity Levels for the Package Initialization Section 225

DEFAULT 226

The Benefits of PRAGMA 226

Persistent States 228

Persistent Variables and Constants 228

Persistent Tables,Records,and Types 231

Persistent Cursors 232

Chapter Summary 237

Two-Minute Drill 238

Chapter Questions 239

Answers to Chapter Questions 243

7 Working with Oracle's PL/SQL Packages 245

DBMS_OUTPUT 247

SET SERVEROUTPUT ON 247

ENABLE and DISABLE 248

PUT_LINE,PUT,and NEW_LINE 248

GET_LINE and GET_LINES 249

DBMS_JOB 251

JOB_QUEUE_PROCESSES and JOB_QUEUE_lNTERVAL 251

The SUBMIT Procedure 252

The REMOVE Procedure 253

The RUN Procedure 253

The CHANGE,NEXT_DATE,WHAT,INTERVAL,and INSTANCE Procedures 254

Other Procedures:USER_EXPORT,BROKEN,and ISUBMIT 254

Monitoring Batch Jobs with USER_JOBS 255

The ALTER_COMPILE Procedure 257

DBMS_DDL 257

The ANALYZE_OBJECT Procedure 258

Monitoring the Results of ALTER_COMPILE and ANALYZE_OBJECT 259

DBMS_PIPE 260

The PACK_MESSAGE Procedure 260

The SEND_MESSAGE Function 260

The RECEIVE_MESSAGE Function 261

The NEXT_ITEM_TYPE Function 262

The UNPACK_MESSAGE Procedure 262

Other Procedures 262

DBMS_SQL 263

The OPEN_CURSOR Function and the CLOSE_CURSOR Procedure 268

The PARSE Procedure 268

The DEFINE_COLUMN Procedure 269

The EXECUTE Function 270

The FETCH_ROWS Function 270

The BIND_VARIABLE Procedure 271

The COLUMN_VALUE Procedure 271

Native Dynamic SQL 272

Compilation Errors 273

Chapter Summary 274

Two-Minute Drill 275

Chapter Questions 276

Answers to Chapter Questions 278

PART Ⅱ Development Tools 283

8 Using Oracle PL/SQL Tools 283

SQL Plus 284

The SQL Plus Buffer Editor 287

Using Text Editors 289

Executing Text Files 291

PL/SQL Blocks and the Buffer 291

Procedure Builder 292

Object Navigator 294

Creating a Program Unit with Procedure Builder 298

Executing a Program Unit 305

Moving Program Units 306

Working with Libraries 307

Creating Database Triggers 308

Chapter Summary 310

Two-Minute Drill 311

Chapter Questions 312

Answers to Chapter Questions 315

9 Debugging PL/SQL 317

Debugging Concepts 318

Debugging Techniques 318

Code Formatting:An Example 320

Debugging Tools 321

Debugging with SQL Plus 323

Debugging Compilation Errors with SQL Plus 323

SHOW ERROR 328

Debugging Execution Errors in SQL Plus 329

Debugging with Procedure Builder 330

Starting Debugger:Setting Breakpoints 331

Chapter Summary 340

Two-Minute Drill 340

Chapter Questions 341

Answers to Chapter Questions 344

PART Ⅲ Privileges and Interdependence 349

10 Managing Privileges 349

System Privileges 350

Roles 351

System and Object Privilege Requirements for Program Units 353

System Privileges for Program Units 353

Object Privileges for Program Units 356

Owner and Invoker Rights 358

Owner Rights 358

Invoker Rights 360

Granting and Revoking Privileges 362

Data Dictionary Resources 364

SESSION_PRIVS 364

SESSION_ROLES 365

USER_SYS_PRIVS 365

USER_ROLE_PRIVS 365

DBA_SYS_PRIVS 366

USER_TAB_PRIVS,USER_TAB_PRIVS_MADE,and USER_TAB_PRIVS RECD 366

Chapter Summary 368

Two-Minute Drill 370

Chapter Questions 371

Answers to Chapter Questions 373

11 Managing Interdependencies 377

Tracking Dependencies 378

USER_DEPENDENCIES 378

DEPTREE and IDEPTREE 382

Dependency Issues Within a Single Database 386

Dependency Issues Across Multiple Databases 388

The Timestamp Mode 390

The Signature Mode 390

Avoiding Recompilation Errors 392

Chapter Summary 394

Two-Minute Drill 396

Chapter Questions 396

Answers to Chapter Questions 399

PART Ⅳ Practice Exams 403

12 Practice Exams 403

Practice Exam #1 404

Answers to Practice Exam #1 425

Practice Exam #2 436

Answers to Practice Exam #2 452

Practice Exam #3 462

Answers to Practice Exam #3 478