ORACLE PL/SQL

Topics Covered

Introduction to Oracle SQL and PL/SQL Syllabus © 2001-2009 by Donald K. Burleson Day 1 Introduction to SQL constructs Review of Basic SQL statements Select Project Join Describing Oracle tables Exercise – investigate the EMP database Restricting row returns Where clause Exercise – write a sample query in the emp database Complex Boolean logic in SQL ROWID restrictions Exercise – write a sample query in the emp database Using SQL*Plus Creating basic reports Exercise – format a simple query in the emp database Using the set commands Adjusting line output Setting pagesize and linesize Echo Feedback Verify termout Exercise – set the SQL*plus environment Column wrapping Exercise – add column statements Creating breaks and summaries Exercise – add breaks and summaries Adding prompts to queries Exercise – parameterize a SQL*Plus script DAY 2 Joining Oracle tables Equi-join Exercise – write an equi-join Outer join Exercise – write an outer join Hiding joins by creating views Exercise – create a view of a join Using IN, NOT IN, EXISTS and NOT EXISTS Subqueries Exercise – write a subquery Correlated subquery Non-correlated subqueries Advanced SQL operators Between operator IN and NOT In operators Sub-queries EXISTS clause Using wildcards in queries (LIKE operator) Aggregation in SQL Count(*) Sum Avg Min and max Using the group by clause SQL access methods Review of Basic joining methods Merge join Hash Join Nested Loop join Advanced SQL operators Between operator DAY 3 SQL Tuning Introduction to rule-based optimization Introduction to cost-based optimization Collecting table and index statistics Using column histograms Changing the default optimizer modes Using TKPROF Using SQL*Trace SQL reusability within the library cache Table high-water mark Table striping and table partitions Using EXPLAIN PLAN Interpreting EXPLAIN PLAN Output Using indexes to improve performance Identifying full-table scans Re-writing SQL queries Using hints to improve SQL performance Using parallel query to improve performance Tuning sub-queries DAY 4 PL/SQL Section 1. Basics of PL/SQL PL/SQL architecture PL/SQL and SQL*Plus PL/SQL Basics Variables Constants Datatypes Error messages – user_errors and show errors PL/SQL wrapper utility 2. PL/SQL structures Simple blocks Control structures PL/SQL records Recognizing the Basic PL/SQL Block and Its Sections Describing the Significance of Variables in PL/SQL Distinguishing Between PL/SQL and Non-PL/SQL Variables Declaring Variables and Constants Executing a PL/SQL Block 3. Error checking – exception handling Defining exceptions Using the when others clause Ensuring complete error checking Passing error messages to calling routine 4. Boolean logic in PL/SQL Identifying the Uses and Types of Control Structures Constructing an IF Statement Constructing and Identifying Different Loop Statements Controlling Block Flow Using Nested Loops and Labels Using Logic Tables If-then-else structure Testing for numbers characters and Booleans 5. Cursors in PL/SQL Cursor basics Using a cursor for a multi-row SQL query 6. Iteration in PL/SQL For loop While loop DAY 5 7. PL/SQL tables Defining PL/SQL tables Reasons to use PL/SQL tables Populating a PL/SQL table Retrieving from a PL/SQL table 8. Dynamic SQL in PL/SQL Introduction to the dbms_sql package Creating a dynamic SQL statement 9. Nested blocks in PL/SQL Creating nested blocks Understanding scope in nested blocks 10. Triggers in PL/SQL Triggers and database events Defining a trigger Timing a trigger Enabling and disabling a trigger 11. Stored procedures, functions and packages Basics of stored procedures Basics of functions Basics of packages Defining stored procedures & functions Function and stored procedures prototypes Passing arguments to functions and stored procedures Recompiling functions and stored procedures Pinning packages in the SGA with dbms_shared_pool.keep Package forward declaration Package dependency Package overloading Listing package information 12. Bulking in PL/SQL Bulk queries Bulk DML (forall statement) Using cursor attributes Analyzing impact of bilk operations

Who should attend

B.E/B.TECH/M.TECH

Pre-requisites

NOTHING

What you need to bring

NOTBOOK

Key Takeaways

NOTHING