OCP Developer PL/SQL Program Units考试指南 英文版【2025|PDF下载-Epub版本|mobi电子书|kindle百度云盘下载】

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

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

PART Ⅰ Language Syntax3

1 Overview of PL/SQL3

An Introduction to the PL/SQL Language4

PL/SQL Syntax Fundamentals7

Statements8

Identifiers8

Comments8

Literals9

Declaration Section10

Variables10

Datatypes11

Constants14

Other Declared Elements14

Processing Section15

Expressions15

Assignment Statements21

Conditional Statements22

Loops25

Cursors28

Implicit Cursors28

Explicit Cursors30

Cursor Attributes32

Loops Revisited:The Cursor FOR Loop33

Advanced Datatype Declaration34

%TYPE34

%ROWTYPE35

Exception-Handling Section37

System-Defined Exceptions38

User-Defined Exceptions39

Working with Blocks40

Nested Blocks41

Scope:Declared Elements42

Exception Scope and Exception Propagation44

Anonymous Blocks47

Named Program Units47

An Introduction to Program Units47

Chapter Summary49

Two-Minute Drill51

Chapter Questions53

Answers to Chapter Questions57

2 Procedures61

Uses of Procedures62

What Can You Do with Procedures?63

Where Can You Store Procedures?64

Creating,Altering,and Dropping Procedures66

Creating Procedures66

Altering Procedures70

Dropping Procedures72

Executing a Procedure from a PL/SQL Block73

Invoking Procedures73

Executing a Procedure from the SQL Plus Command Line74

Parameters76

Parameter Declaration Syntax76

Parameter Datatypes77

Default Values77

Parameter Types78

Positional Notation versus Named Notation83

Data Dictionary Resources for Procedures85

USER_OBJECTS86

USER_OBJECT_SIZE88

USER_SOURCE88

USER_ERRORS89

SHOW ERRORS91

Chapter Summary93

Two-Minute Drill95

Chapter Questions96

Answers to Chapter Questions100

3 Functions103

Uses of Functions104

Functions versus Procedures105

Creating,Altering,and Dropping Functions106

Creating Functions106

Altering Functions107

Dropping Functions108

Invoking Functions109

Functions Called from PL/QL Expressions110

Functions Called from SQL Statements112

Client-Side Functions113

Parameters115

Passing Parameters by Reference117

RETURN119

Two-Minute Drill123

Chapter Summary123

Chapter Questions124

Answers to Chapter Questions129

4 Packages133

Uses of Packages134

Creating,Altering,and Dropping Packages137

Creating a Package Specification137

Creating a Package Body139

Public versus Private Constructs140

Global Constructs141

Altering a Package143

Dropping a Package147

Changes to a Package Specification that Require a Change to the Body149

Invoking Packaged Constructs151

Referencing Packaged Constructs151

Using Packaged Constructs151

Data Dictionary Resources for Packages156

Chapter Summary158

Two-Minute Drill159

Chapter Questions160

Answers to Chapter Questions164

5 Triggers167

Uses of Triggers168

Creating,Altering,and Dropping Triggers170

Creating Triggers171

The:old and:new Qualifiers175

Conditional Predicates177

Firing Rules178

Restrictions179

Altering Triggers181

Dropping Triggers181

Enabling and Disabling Triggers183

Disabling and Enabling a Named Trigger183

Enabling and Disabling a Named Table's Triggers184

lNSTEAD OF Triggers185

Non-DML Triggers188

DDL Triggers189

Data Dictionary Resources for Triggers190

USER_TRIGGERS191

Chapter Summar194

Two-Minute Drill195

Chapter Questions197

Answers to Chapter Questions200

6 Working with Program Units203

Client-Side versus Server-Side Program Units204

Invoking Server-Side Program Units205

Invoking Client-Side Program Units206

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

Local Subprograms209

Forward Declarations209

Overloading214

Creating Overloaded Modules215

Namespaces for Program Units217

Initializing Variables with a One-Time-Only Procedure219

Functions and Purity Levels221

WNDS and RNDS224

WNPS and RNPS224

TRUST225

Purity Levels for the Package Initialization Section225

DEFAULT226

The Benefits of PRAGMA226

Persistent States228

Persistent Variables and Constants228

Persistent Tables,Records,and Types231

Persistent Cursors232

Chapter Summary237

Two-Minute Drill238

Chapter Questions239

Answers to Chapter Questions243

7 Working with Oracle's PL/SQL Packages245

DBMS_OUTPUT247

SET SERVEROUTPUT ON247

ENABLE and DISABLE248

PUT_LINE,PUT,and NEW_LINE248

GET_LINE and GET_LINES249

DBMS_JOB251

JOB_QUEUE_PROCESSES and JOB_QUEUE_lNTERVAL251

The SUBMIT Procedure252

The REMOVE Procedure253

The RUN Procedure253

The CHANGE,NEXT_DATE,WHAT,INTERVAL,and INSTANCE Procedures254

Other Procedures:USER_EXPORT,BROKEN,and ISUBMIT254

Monitoring Batch Jobs with USER_JOBS255

The ALTER_COMPILE Procedure257

DBMS_DDL257

The ANALYZE_OBJECT Procedure258

Monitoring the Results of ALTER_COMPILE and ANALYZE_OBJECT259

DBMS_PIPE260

The PACK_MESSAGE Procedure260

The SEND_MESSAGE Function260

The RECEIVE_MESSAGE Function261

The NEXT_ITEM_TYPE Function262

The UNPACK_MESSAGE Procedure262

Other Procedures262

DBMS_SQL263

The OPEN_CURSOR Function and the CLOSE_CURSOR Procedure268

The PARSE Procedure268

The DEFINE_COLUMN Procedure269

The EXECUTE Function270

The FETCH_ROWS Function270

The BIND_VARIABLE Procedure271

The COLUMN_VALUE Procedure271

Native Dynamic SQL272

Compilation Errors273

Chapter Summary274

Two-Minute Drill275

Chapter Questions276

Answers to Chapter Questions278

PART Ⅱ Development Tools283

8 Using Oracle PL/SQL Tools283

SQL Plus284

The SQL Plus Buffer Editor287

Using Text Editors289

Executing Text Files291

PL/SQL Blocks and the Buffer291

Procedure Builder292

Object Navigator294

Creating a Program Unit with Procedure Builder298

Executing a Program Unit305

Moving Program Units306

Working with Libraries307

Creating Database Triggers308

Chapter Summary310

Two-Minute Drill311

Chapter Questions312

Answers to Chapter Questions315

9 Debugging PL/SQL317

Debugging Concepts318

Debugging Techniques318

Code Formatting:An Example320

Debugging Tools321

Debugging with SQL Plus323

Debugging Compilation Errors with SQL Plus323

SHOW ERROR328

Debugging Execution Errors in SQL Plus329

Debugging with Procedure Builder330

Starting Debugger:Setting Breakpoints331

Chapter Summary340

Two-Minute Drill340

Chapter Questions341

Answers to Chapter Questions344

PART Ⅲ Privileges and Interdependence349

10 Managing Privileges349

System Privileges350

Roles351

System and Object Privilege Requirements for Program Units353

System Privileges for Program Units353

Object Privileges for Program Units356

Owner and Invoker Rights358

Owner Rights358

Invoker Rights360

Granting and Revoking Privileges362

Data Dictionary Resources364

SESSION_PRIVS364

SESSION_ROLES365

USER_SYS_PRIVS365

USER_ROLE_PRIVS365

DBA_SYS_PRIVS366

USER_TAB_PRIVS,USER_TAB_PRIVS_MADE,and USER_TAB_PRIVS RECD366

Chapter Summary368

Two-Minute Drill370

Chapter Questions371

Answers to Chapter Questions373

11 Managing Interdependencies377

Tracking Dependencies378

USER_DEPENDENCIES378

DEPTREE and IDEPTREE382

Dependency Issues Within a Single Database386

Dependency Issues Across Multiple Databases388

The Timestamp Mode390

The Signature Mode390

Avoiding Recompilation Errors392

Chapter Summary394

Two-Minute Drill396

Chapter Questions396

Answers to Chapter Questions399

PART Ⅳ Practice Exams403

12 Practice Exams403

Practice Exam #1404

Answers to Practice Exam #1425

Practice Exam #2436

Answers to Practice Exam #2452

Practice Exam #3462

Answers to Practice Exam #3478