Muutke küpsiste eelistusi

Oracle PL/SQL by Example 5th edition [Pehme köide]

4.00/5 (2 hinnangut Goodreads-ist)
,
  • Formaat: Paperback / softback, 528 pages, kõrgus x laius x paksus: 231x180x27 mm, kaal: 840 g
  • Ilmumisaeg: 19-Mar-2015
  • Kirjastus: Pearson
  • ISBN-10: 0133796787
  • ISBN-13: 9780133796780
Teised raamatud teemal:
Oracle PL/SQL by Example 5th editionSuurem pilt
  • Formaat: Paperback / softback, 528 pages, kõrgus x laius x paksus: 231x180x27 mm, kaal: 840 g
  • Ilmumisaeg: 19-Mar-2015
  • Kirjastus: Pearson
  • ISBN-10: 0133796787
  • ISBN-13: 9780133796780
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9780133796780.html
Märksõnad:

Like the previous edition, this tutorial is an integrated learning solution that teaches all the Oracle PL/SQL skills you need, hands-on, through real-world labs, extensive examples, exercises, and projects. Completely updated for Oracle 12c, it covers all the fundamentals, from PL/SQL syntax and program control through packages and optimization.

This book’s approach reflects the authors’ award-winning experience teaching PL/SQL programming to professionals. Providing readers with the accompanying schema that can be used for practice allows the authors to explain PL/SQL concepts through examples. Each concept is explained first, then illustrated via examples and interactive labs that introduce topics about Oracle PL/SQL. The topics are discussed briefly and then explored through exercises. Each exercise consists of a series of steps followed to perform a specific task, along with questions designed to help you discover the important things about PL/SQL programming on your own. The answers to the questions are then given with more in-depth discussion of the topics. In this way the answers are not just solutions to the exercises but a continuation of the learning process. The exercises are intended to act as your guide to walk you through a particular task. Finally, at the end of each chapter there is a "Test Your Thinking" section made up of a series of projects designed to solidify all of the skills learned in the chapter.

Key topics covered include:

  • Using conditional and iterative program control techniques, including the new CONTINUE and CONTINUE WHEN statements
  • Efficiently handling errors and exceptions
  • Using stored procedures, functions, and packages to write modular code that other programs can execute
  • Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced PL/SQL capabilities
  • Handy reference appendices: PL/SQL formatting guide, sample database schema, ANSI SQL standards reference
Preface xvii
Acknowledgments xxi
About the Authors xxiii
Introduction to PL/SQL New Features in Oracle 12c xxv
Invoker's Rights Functions Can Be Result-Cached
xxvi
More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause
xxvii
ACCESSIBLE BY Clause
xxvii
FETCH FIRST Clause
xxviii
Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms
xxix
More Data Types Have the Same Maximum Size in SQL and PL/SQL
xxx
Database Triggers on Pluggable Databases
xxx
LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause
xxx
Implicit Statement Results
xxxi
BEQUEATH CURRENT USER Views
xxxii
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
xxxii
Invisible Columns
xxxiii
Objects, Not Types, Are Editioned or Noneditioned
xxxiv
PL/SQL Functions That Run Faster in SQL
xxxiv
Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQLUNITTYPE
xxxvi
Compilation Parameter PLSQL_DEBUG Is Deprecated
xxxvii
Chapter 1 PL/SQL Concepts 1(26)
Lab 1.1 PL/SQL Architecture
2(7)
PL/SQL Architecture
2(3)
PL/SQL Block Structure
5(3)
How PL/SQL Gets Executed
8(1)
Lab 1.2 PL/SQL Development Environment
9(9)
Getting Started with SQL Developer
10(1)
Getting Started with SQL*Plus
11(3)
Executing PL/SQL Scripts
14(4)
Lab 1.3 PL/SQL: The Basics
18(7)
DBMS_OUTPUT.PUT_LINE Statement
18(1)
Substitution Variable Feature
19(6)
Summary
25(2)
Chapter 2 PL/SQL Language Fundamentals 27(16)
Lab 2.1 PL/SQL Programming Fundamentals
28(13)
PL/SQL Language Components
28(1)
PL/SQL Variables
29(3)
PL/SQL Reserved Words
32(1)
Identifiers in PL/SQL
33(1)
Anchored Data Types
34(2)
Declare and Initialize Variables
36(3)
Scope of a Block, Nested Blocks, and Labels
39(2)
Summary
41(2)
Chapter 3 SQL in PL/SQL 43(14)
Lab 3.1 DML Statements in PL/SQL
44(5)
Initialize Variables with SELECT INTO
44(1)
Using the SELECT INTO Syntax for Variable Initialization
45(2)
Using DML in a PL/SQL Block
47(1)
Using a Sequence in a PL/SQL Block
48(1)
Lab 3.2 Transaction Control in PL/SQL
49(6)
Using COMMIT, ROLLBACK, and SAVEPOINT
49(4)
Putting Together DML and Transaction Control
53(2)
Summary
55(2)
Chapter 4 Conditional Control: IF Statements 57(14)
Lab 4.1 I F Statements
58(5)
IF-THEN Statements
58(2)
IF-THEN- ELSE Statement
60(3)
Lab 4.2 ELSIF Statements
63(4)
Lab 4.3 Nested I F Statements
67(3)
Summary
70(1)
Chapter 5 Conditional Control: CASE Statements 71(20)
Lab 5.1 CASE Statements
71(9)
CASE Statements
72(2)
Searched CASE Statements
74(6)
Lab 5.2 CASE Expressions
80(4)
Lab 5.3 NULLIF and COALESCE Functions
84(5)
NULLIF Function
84(3)
COALESCE Function
87(2)
Summary
89(2)
Chapter 6 Iterative Control: Part I 91(20)
Lab 6.1 Simple Loops
92(6)
EXIT Statement
93(4)
EXIT WHEN Statement
97(1)
Lab 6.2 WHILE Loops
98(6)
Using WHILE Loops
98(3)
Premature Termination of the WHILE Loop
101(3)
Lab 6.3 Numeric FOR Loops
104(5)
Using the IN Option in the Loop
105(2)
Using the REVERSE Option in the Loop
107(1)
Premature Termination of the Numeric FOR Loop
108(1)
Summary
109(2)
Chapter 7 Iterative Control: Part II 111(12)
Lab 7.1 CONTINUE Statement
111(7)
Using CONTINUE Statement
112(3)
CONTINUE WHEN Statement
115(3)
Lab 7.2 Nested Loops
118(4)
Using Nested Loops
118(2)
Using Loop Labels
120(2)
Summary
122(1)
Chapter 8 Error Handling and Built-in Exceptions 123(10)
Lab 8.1 Handling Errors
124(2)
Lab 8.2 Built-in Exceptions
126(6)
Summary
132(1)
Chapter 9 Exceptions 133(16)
Lab 9.1 Exception Scope
133(4)
Lab 9.2 User-Defined Exceptions
137(4)
Lab 9.3 Exception Propagation
141(6)
Re-raising Exceptions
146(1)
Summary
147(2)
Chapter 10 Exceptions: Advanced Concepts 149(10)
Lab 10.1 RAISE_APPLICATION_ERROR
149(4)
Lab 10.2 EXCEPTION_INIT Pragma
153(2)
Lab 10.3 SQLCODE and SQLERRM
155(3)
Summary
158(1)
Chapter 11 Introduction to Cursors 159(24)
Lab 11.1 Types of Cursors
159(6)
Making Use of an Implicit Cursor
160(1)
Making Use of an Explicit Cursor
161(4)
Lab 11.2 Cursor Loop
165(10)
Processing an Explicit Cursor
165(3)
Making Use of a User-Defined Record
168(2)
Making Use of Cursor Attributes
170(5)
Lab 11.3 Cursor FOR LOOPS
175(2)
Making Use of Cursor FOR LOOPS
175(2)
Lab 11.4 Nested Cursors
177(4)
Processing Nested Cursors
177(4)
Summary
181(2)
Chapter 12 Advanced Cursors 183(8)
Lab 12.1 Parameterized Cursors
183(2)
Cursors with Parameters
184(1)
Lab 12.2 Complex Nested Cursors
185(2)
Lab 12.3 FOR UPDATE and WHERE CURRENT Cursors
187(3)
FOR UPDATE Cursor
187(2)
FOR UPDATE OF in a Cursor
189(1)
WHERE CURRENT OF in a Cursor
189(1)
Summary
190(1)
Chapter 13 Triggers 191(22)
Lab 13.1 What Triggers Are
191(14)
Database Trigger
192(3)
BEFORE Triggers
195(6)
AFTER Triggers
201(2)
Autonomous Transaction
203(2)
Lab 13.2 Types of Triggers
205(6)
Row and Statement Triggers
205(1)
INSTEAD OF Triggers
206(5)
Summary
211(2)
Chapter 14 Mutating Tables and Compound Triggers 213(12)
Lab 14.1 Mutating Tables
213(4)
What Is a Mutating Table?
214(1)
Resolving Mutating Table Issues
215(2)
Lab 14.2 Compound Triggers
217(6)
What Is a Compound Trigger?
218(2)
Resolving Mutating Table Issues with Compound Triggers
220(3)
Summary
223(2)
Chapter 15 Collections 225(18)
Lab 15.1 PL/SQL Tables
226(9)
Associative Arrays
226(3)
Nested Tables
229(3)
Collection Methods
232(3)
Lab 15.2 Varrays
235(5)
Lab 15.3 Multilevel Collections
240(2)
Summary
242(1)
Chapter 16 Records 243(16)
Lab 16.1 Record Types
243(7)
Table-Based and Cursor-Based Records
244(2)
User-Defined Records
246(2)
Record Compatibility
248(2)
Lab 16.2 Nested Records
250(3)
Lab 16.3 Collections of Records
253(4)
Summary
257(2)
Chapter 17 Native Dynamic SQL 259(22)
Lab 17.1 EXECUTE IMMEDIATE Statements
260(11)
Using the EXECUTE IMMEDIATE Statement
261(1)
How to Avoid Common ORA Errors When Using EXECUTE IMMEDIATE
262(9)
Lab 17.2 OPEN-FOR, FETCH, and CLOSE Statements
271(9)
Opening Cursor
272(1)
Fetching from a Cursor
272(1)
Closing a Cursor
273(7)
Summary
280(1)
Chapter 18 Bulk SQL 281(30)
Lab 18.1 FORALL Statements
282(9)
Using FORALL Statements
282(3)
SAVE EXCEPTIONS Option
285(3)
INDICES OF Option
288(1)
VALUES OF Option
289(2)
Lab 18.2 The BULK COLLECT Clause
291(8)
Lab 18.3 Binding Collections in SQL Statements
299(10)
Binding Collections with EXECUTE IMMEDIATE Statements
299(7)
Binding Collections with OPEN- FOR , FETCH, and CLOSE Statements
306(3)
Summary
309(2)
Chapter 19 Procedures 311(10)
Benefits of Modular Code
312(1)
Block Structure
312(1)
Anonymous Blocks
312(1)
Lab 19.1 Creating Procedures
312(3)
Putting Procedure Creation Syntax into Practice
313(1)
Querying the Data Dictionary for Information on Procedures
314(1)
Lab 19.2 Passing Parameters IN and OUT of Procedures
315(4)
Using IN and OUT Parameters with Procedures
316(3)
Summary
319(2)
Chapter 20 Functions 321(12)
Lab 20.1 Creating Functions
321(6)
Creating Stored Functions
322(3)
Making Use of Functions
325(2)
Lab 20.2 Using Functions in SQL Statements
327(2)
Invoking Functions in SQL Statements
327(1)
Writing Complex Functions
328(1)
Lab 20.3 Optimizing Function Execution in SQL
329(2)
Defining a Function Using the WITH Clause
329(1)
Creating a Function with the UDF Pragma
330(1)
Summary
331(2)
Chapter 21 Packages 333(40)
Lab 21.1 Creating Packages
334(10)
Creating Package Specifications
335(2)
Creating Package Bodies
337(2)
Calling Stored Packages
339(2)
Creating Private Objects
341(3)
Lab 21.2 Cursor Variables
344(9)
Lab 21.3 Extending the Package
353(13)
Extending the Package with Additional Procedures
353(13)
Lab 21.4 Package Instantiation and Initialization
366(2)
Creating Package Variables During Initialization
367(1)
Lab 21.5 SERIALLY_ REUSABLE Packages
368(3)
Using the SERIALLY_REUSABLE Pragma
368(3)
Summary
371(2)
Chapter 22 Stored Code 373(12)
Lab 22.1 Gathering Information about Stored Code
373(9)
Getting Stored Code Information from the Data Dictionary
374(4)
Overloading Modules
378(4)
Summary
382(3)
Chapter 23 Object Types in Oracle 385(20)
Lab 23.1 Object Types
386(8)
Creating Object Types
386(5)
Using Object Types with Collections
391(3)
Lab 23.2 Object Type Methods
394(10)
Constructor Methods
395(3)
Member Methods
398(1)
Static Methods
398(1)
Comparing Objects
399(5)
Summary
404(1)
Chapter 24 Oracle-Supplied Packages 405(26)
Lab 24.1 Extending Functionality with Oracle-Supplied Packages
406(13)
Accessing Files within PL/SQL with UTL_FILE
406(4)
Scheduling Jobs with DBMS_JOB
410(4)
Generating an Explain Plan with DBMS_XPLAN
414(3)
Generating Implicit Statement Results with DBMS_SQL
417(2)
Lab 24.2 Error Reporting with Oracle-Supplied Packages
419(10)
Using the DBMS_UTILITY Package for Error Reporting
419(5)
Using the UTL_CALL_STACK Package for Error Reporting
424(5)
Summary
429(2)
Chapter 25 Optimizing PL/SQL 431(24)
Lab 25.1 PL/SQL Tuning Tools
432(6)
PL/SQL Profiler API
432(1)
Trace API
433(3)
PL/SQL Hierarchical Profiler
436(2)
Lab 25.2 PL/SQL Optimization Levels
438(6)
Lab 25.3 Subprogram Inlining
444(9)
Summary
453(2)
Appendix A PL/SQL Formatting Guide 455(6)
Case
455(1)
White Space
455(1)
Naming Conventions
456(1)
Comments
457(1)
Other Suggestions
457(4)
Appendix B Student Database Schema 461(8)
Table and Column Descriptions
461(8)
Index 469
Benjamin Rosenzweig is a Senior Project Manager at Misys Financial Software, where he has worked since 2002. Prior to that he was a principal consultant for more than three years at Oracle Corporation in the Custom Development Department. His computer experience ranges from creating an electronic TibetanEnglish Dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing a trading system at TIAA-CREF. Benjamin has been an instructor at the Columbia University Computer Technology and Application program in New York City since 1998. In 2002 he was awarded the Outstanding Teaching Award from the Chair and Director of the CTA program. He holds a B.A. from Reed College and a certificate in database development and design from Columbia University. His previous books with Prentice Hall are Oracle Forms Developer: The Complete Video Course (2000), and Oracle Web Application Programming for PL/SQL Developers (2003).

 

Elena Rakhimov has more than twenty years of experience in database architecture and development in a wide spectrum of enterprise and business environments ranging from non-profit organizations to Wall Street to her current position with a prominent software company where she heads up the database team. Her determination to stay hands-on notwithstanding, Elena managed to excel in the academic arena having taught relational database programming at Columbia Universitys highly esteemed Computer Technology and Applications program. She was educated in database analysis and design at Columbia University and in applied mathematics at Baku State University in Azerbaijan. She currently resides in Vancouver, Canada.