Oracle Pl/sql Interactive Workbook

by Rosenzweig, Benjamin; Silvestrova, Elena

• ISBN13: 9780130157430
• ISBN10: 0130157430
• Edition: 2nd
• Format: Paperback
• Copyright: 2003-01-01
• Publisher: Prentice Hall

Summary

In this unique workbook pedagogy with hands-on exercises, programming projects and a free Web-based training module, the author covers every key Oracle SQL concept: SQL*Plus, DDL, DML, DQL, the Oracle Data Dictionary, and more! Pub. 11/02.

Table of Contents

Acknowledgments	xi
From the Editor	xiii
Introduction	xv
Programming Concepts	1	(22)
The Nature of a Computer Program and Programming Languages	2	(8)
Understanding the Nature of Computer Programs and Programming Languages	5	(1)
Understanding the Differences Between Compiled and Interpreted Languages	6	(4)
Good Programming Practices	10	(12)
Structured Programming	11	(6)
Understanding the Nature of Good Programming Practices	17	(1)
Understanding Formatting Guidelines	18	(4)
Test Your Thinking	22	(1)
PL/SQL Concepts	23	(26)
PL/SQL in Client-Server Architecture	24	(13)
Using PL/SQL Anonymous Blocks	31	(1)
Understanding How PL/SQL Gets Executed	32	(5)
PL/SQL in SQL *Plus	37	(11)
Using Substitution Variables	42	(1)
Using DBMS_OUTPUT.PUT_LINE Statement	43	(5)
Test Your Thinking	48	(1)
General Programming Language Fundamentals	49	(18)
PL/SQL Programming Fundamentals	50	(15)
Making Use of PL/SQL Language Components	52	(1)
Making Use of PL/SQL Variables	52	(1)
Making Use of Anchored Datatypes	53	(1)
Declaring and Initilizing Variables	53	(2)
Understanding Scope of a Block, Nested Blocks, and Labels	55	(10)
Test Your Thinking	65	(2)
SQL in PL/SQL	67	(22)
Making use of DML in PL/QL	68	(9)
Using the Select Into Syntax for Variable Initialization	69	(2)
Using DML in a PL/SQL Block	71	(1)
Making Use of a Sequence in a PL/SQL Block	72	(5)
Making Use of Savepoint	77	(10)
Making Use of COMMIT, ROOLBACK, and SAVEPOINT in a PL/SQL Block	81	(6)
Test Your Thinking	87	(2)
Conditional Control	89	(40)
IF Statements	90	(16)
Using the IF-THEN Statement	95	(2)
Using the IF-THEN-ELSE Statement	97	(9)
ELSIF Statements	106	(11)
Using the ELSIF Statement	111	(6)
Nested IF Statements	117	(9)
Using Nested IF Statements	120	(6)
Test Your Thinking	126	(3)
Exception Handling and Built-In Exceptions	129	(24)
Handling Errors	130	(8)
Understanding the Importance of Error Handling	134	(4)
Built-In Exceptions	138	(13)
Using Built-In Exceptions	144	(7)
Test Your Thinking	151	(2)
Iterative Control	153	(50)
Simple Loops	154	(13)
Using Simple Loops with EXIT Conditions	158	(2)
Using Simple Loops with EXIT WHEN Conditions	160	(7)
WHILE Loops	167	(11)
Using WHILE Loops	172	(6)
Numeric FOR Loops	178	(14)
Using Numeric FOR Loops with the IN Option	183	(2)
Using Numeric FOR Loops with the REVERSE Option	185	(7)
Nested Loops	192	(9)
Using Nested Loops	195	(6)
Test Your Thinking	201	(2)
Introduction to Cursors	203	(38)
Cursor Manipulation	204	(17)
Processing an Explicit Cursor	208	(3)
Making use of Cursor Attributes	211	(2)
Putting It all Together	213	(8)
Using Cursor FOR Loops and Nesting Cursors	221	(8)
Using Cursor FOR LOOPS	222	(1)
Processing Nested Cursors	222	(7)
Using PARAMETERS with Cursors and FOR UPDATE Cursors	229	(11)
Using Parameters in a Cursor	230	(2)
Using a FOR UPDATE Cursor	232	(3)
WHERE CURRENT OF Clause	235	(5)
Test Your Thinking	240	(1)
Exceptions	241	(40)
Exception Scope	242	(11)
Understanding the Scope of an Exception	246	(7)
USER-DEFINED Exceptions	253	(12)
Using User-Defined Exceptions	259	(6)
Exception Propagation	265	(15)
Understanding How Exceptions Propagate	272	(1)
Reraising Exceptions	273	(7)
Test Your Thinking	280	(1)
Exceptions: Advanced Concepts	281	(30)
RAISE_APPLICATION_ERROR	282	(12)
Using RAISE_APPLICATION _ERROR	286	(8)
EXCEPTION_INIT pragma	294	(8)
Using EXCEPTION_INIT Pragma	297	(5)
SQLCODE and SQLERRM	302	(8)
Using SQLCODE and SQLERRM	306	(4)
Test Your Thinking	310	(1)
Procedures	311	(14)
Creating Procedures	312	(6)
Creating Procedures	314	(1)
Querying the Data Dictionary for Information on Procedures	315	(3)
Passing Parameters IN and OUT of Procedures	318	(5)
Using In and OUT Parameters with Procedures	320	(3)
Test Your Thinking	323	(2)
Functions	325	(10)
Creating and Using Functions	326	(8)
Creating Stored Functions	328	(1)
Making Use of Functions	328	(1)
Invoking Functions in SQL Statements	329	(1)
Writing Complex Functions	329	(5)
Test Your Thinking	334	(1)
Packages	335	(18)
The Benefits of Utilizing Packages	336	(15)
Creating the Package Specification	338	(1)
Creating the Package Body	339	(1)
Calling Stored Packages	340	(1)
Creating Private Objects	341	(2)
Creating Package Variables and Cursors	343	(8)
Test Your Thinking	351	(2)
Stored Code	353	(22)
Advanced Features of Stored Code	354	(19)
Getting Stored Code Information from the Data Dictionary	354	(3)
Enforcing Purity Level with RESTRICT_REFERENCES Pragma	357	(2)
Overloading Modules	359	(14)
Test Your Thinking	373	(2)
Triggers	375	(28)
What Triggers Are	376	(14)
Understanding What a Trigger Is	382	(2)
Using BEFORE and AFTER Triggers	384	(6)
Types of Triggers	390	(5)
Using Row and Statement Triggers	392	(3)
Mutating Table Issues	395	(6)
Understanding Mutating Tables	397	(4)
Test Your Thinking	401	(2)
PL/SQL Tables	403	(14)
Making Use of PL/SQL Tables	404	(11)
Declaring and Referencing a PL/SQL Table	405	(3)
Making Use of PL/SQL Table Attributes	408	(7)
Test Your Thinking	415	(2)
Appendix A Answers to Self-Review Questions	417	(10)
Appendix B PL/SQL Formatting Guide	427	(6)
Appendix C Student Database Scheme	433	(12)
Index	445