:: SSCS World - Training Division ::

Oracle 9i PL / SQL

Course Objective

This course Introduces participants to PUSQLand helps them understand the benefits of this powerful programming language. In the session. participants learn to create PUSQL blocks of application code that can be shared by multiple applications. Participants learn to create procedures, functions. packages. and database triggers. Participants also learn to manage PUSQl program units and database triggers, to manage dependencies. and to use some ofthe Oracle-supplied packages.

Declaring Variables
1. List the benefits of PL/SQL
2. Describe the basic PL/SQL block and its sections
3. Describe the significance of variables in PL/SQL
4. D'eclare Pl/SQL variables
5. Execute a Pl/SQL block
Writing Executable Statements
1. Describe the significance of the executable section
2. Write statements in the executable section
3. Describe the rules of nested blocks
4. Execute and test a PL/SQL block
5. Use coding conventions
Interacting with the Oracle Server
1. Write a successful SELECT statement In PL/SQL
2. Declare the data type and size of a PL/SQL variable dynamically
3. Write DML statements in PL/SQL
4. Control transactions in PL/SQL
5. Determine the outcome of SQL DML statements
Writing Control Structures
1. Identify the uses and types of control structures
2. Construct an IF statement
3. Construct and Identify different loop statements
4. Use logic tables
5. Control block flow by using nested loops and labels
Working with Composite Data Types
1. Create ,user-defined Pl/SQL records
2. Create a record with the OfoROWTYPE attribute
3. Create a Pl/SQL table
4. Create a Pl/SQL table of records
5. Describe the difference between records, tables, and tables of records
Writing Explicit Cursors
1. Distinguish between an implicit and an explicit cursor
2. Use a PL/SQL record variable
3. Write a cursor FOR loop
Advanced Explicit Cursor Concepts
1. Write a cursor that uses parameters
2. Determine when a FOR UPDATE clause in a cursor is required
3. Determine when to use the WHERE CURRENT OF clause
4. Write a cursor that uses a subquery
Handling Exceptions
1. Define Pl/SQL exceptions Recognize unhandled exceptions
2. List and use different types of PL/SQL' exception handlers Trap unanticipated errors Describe the effectof exception propagation in nested blocks Customize PL/SQL exception message
Overview of PL/SQL Programs
1. Describe a PL/SQL program construct
2. List the components of a PL/SQL block
3. List the benefits of subprograms
4. Describe how a stored procedure or function is Invoked
Creating Procedures
1. Define what a stored procedure is
2. List the development steps for creating a procedure
3. Create a procedure
4. Describe the difference between formal and actual parameters
5. List the types of parameter modes
6. List the methods for calling a procedure with parameters
7. Describe the DEFAULT option for parameters
8. Create a procedure with parameters
9. Invoke a procedure that has parameters
10. Define a subprogram in the declarative section of a procedure
11. Describe how exceptions are propagated
12. Remove a procedure
Creating Functions
1. Define what a stored function is
2. Create a function
3. List how a function can be invoked
4. List the advantages of user-defined functions in SQL statements
5. List where user-defined functions can be called from within an SQLstatement
6. Describe the restrictions on calling functions from SQL statements
Remove a function
1. Describe the differences between procedures and functions
Managing Subprograms
1. Contrast system privileges with object privileges
2. Grant privileges
3. Contrast invokers' rights with definers' rights
4. Identify views in the data dictionary to manage stored objects
Creating Packages
1. Use the DESCRIBE command to describe packages, and list their possible components
2. Identify a package specification and body
3. Create packages: Create related variables, cursors, constants, exceptions, procedures, and functions
4. Designate a package construct as either public or private
5. Invoke a package construct
6. Use a bodiless package
7. Drop packages
8. Identify benefits of packages
More Package Concepts
1. Write packages that use the overloading feature
2. Use Forward Referencing
3. Describe errors with mutually referential subprograms.
4. Initialize variables with a one-time-only procedure
5. Identify persistent states in package variables and cursors
6. Identify restrictions on using packaged functions In
7. SQL statements
8. Invoke packaged functions from SQL
9. Use PljSQL tables and records in packages
Oracle Supplied Packages
1. Describe the benefits of Execute Immediate over
2. DBMS_SQL for Native Dynamic SQL
3. Identify the flow of execution
4. Use EXECUTE IMMEDIATE
5. Describe the use and application of some Oracle serversupplied packages
Creating Database Triggers
1. Describe the different types of triggers
2. List how triggers are used
3. List guidelines for designing triggers
4. Create a DML trigger
5. List the DML trigger components
6. Describe the trigger firing sequence options
Use conditional predicates In a DML trigger
1. Create a row-level trigger
2. Create a statement-level trigger
3. Use the OLD and NEW qualifiers in a database trigger
4. Create an INSTEAD OF trigger
5. Describe the difference between stored procedures and triggers
6. Describe the trigger execution model
7. Alter a trigger status
8. Remove a trigger
More Trigger Concepts
1. Define what a database trigger Is
2. Describe events that cause database triggers to fire
3. Create a trigger for a DDL statement
4. Create a trigger for a system event
5. Describe the cause of a mutating table
6. List what triggers can be implemented for
7. List the privileges associated with triggers
8. View trigger Information In the dictionary views
Managing Dependencies
1. Track procedural dependencies
2. Describe dependent objects and referenced objects
3. View dependency Information in the dictionary views
4. Describe a remote dependency
5. List how remote dependencies are governed
6. Describe when a remote dependency is successfully recompiled
7. List how to minimize dependency failures
Working with Object Types
1. Describe object types
2. Create transient objects
3. Manipulate objects In object tables

Tools:

IDE	Visual Studio 2005
Web Server	Internet Information Service 6 (IIS)
Database	MySQL / SQL Server 2005 / Postgre SQL
Web Framework	.NET Framework 2.0

All trainings are done with REAL TIME PROJECTS

Training Duration	:	2 months
Class Allotment	:	3 days in a week
Training Fee	:	Rs. 8,000 only

Apply for :

TOP