Technology Driven IT Training Longstanding IT Technologists Computer Education Techniques
Distance-learning - Oracle: PL/SQL Programming

Home

distance-learning center

Computer-Device_Tethered-Distance-learning Distance-learning-Building

Professional Distance-learning

IT Training Services



PCEC120

Oracle: PL/SQL Programming
DURATION: 4 days

FEE: $1,980

PRIVATE GROUP FEE: $
Based upon a class size of six students.

Scheduling can be adjusted to employee work hours and incorporate client assignments.

SMALL CLASS SIZE:
There is a surcharge associated with training two or fewer students.

AUDIENCE:

  • Programmer
  • Database Administrator
  • System Designer
  • System Analyst
  • IT Professional
 
   

DATES:

CONTENT AND OBJECTIVES:
    1. Oracle SQL and Procedures
      • List the reasons why PL/SQL should be used instead of SQL.
    2. Program structure
      • A PL/SQL program is structured using distinct blocks that group related declarations and statements; code a program to include three sections: program declarations, the main program body, and exception handlers.
      • The declarative section of a PL/SQL block starts with the DECLARE keyword; declare three basic variables.
      • The body of a PL/SQL block begins with the BEGIN keyword and ends with the EXCEPTION keyword; code a simple routine.
      • Exception handling section of a PL/SQL block begins with the EXCEPTION; code a simple exception handler.
    3. Functions
      • Code queries and PL/SQL routines to capture the results of Aggregate, Numeric, Character, Date and Conversion functions.
    4. Coding techniques
      • Execute SQL queries to 1- Extract specific columns. 2- Filter the rows required. 3- Create derived columns. 4- Sort the resulttable. 5- Perform row extractions.
      • PL/SQL programs can include any valid INSERT, UPDATE, or DELETE statement to modify the rows in a database table; perform table manipulation by inserting, updating, and deleting rows.
    5. Composite data
      • Use the %ROWTYPE to define a composite type. Select the data into the composite type.
    6. Tables
      • Create a table with several different types of fields; insert three values and create a unique index.
      • Alter the table; then drop the table.
    7. Exceptions
      • IF statements in a PL/SQL program evaluate a boolean condition and if the condition is TRUE, execute one or more statements.
      • Optional ELSIF and ELSE clauses can be used for the evaluation of subsequent conditions: 1-Code an IF statement which extracts data and based on a calculated value displays a summary result with a caption; otherwise display a different caption. 2- Assign a value to a variable with a query using the PL/SQL GOTO command.
      • Code and test PL/SQL loops and add functionality to the exception handling routines.
    8. Functions and procedures
      • Use the SELECT INTO statement for variable initialization and utilize sequences in a PL/SQL block to code an increasing value in a column. Use a CASE statement to: 1- COMMIT. 2- ROLLBACK. 3- SAVEPOINT.
      • Use the COALESCE function to code WHILE and numeric loops.
    9. Cursors
      • Create and process cursors in PL/SQL which use parameters in cursors and coded nested cursors.
      • Create and use associative, nested arrays, and records.
      • Create and code procedures and store the procedures into a package.
    10. Package
      • Develop a package contain two routines and test the package.
    11. Oracle supplied packages
      • Read and display the contents of a file.
    12. Triggers
      • Create and use multiple types of triggers: BEFORE, AFTER, and a compound trigger. Test the trigger by running INSERT, DELETE and UPDATE statements.

 
 
    Web-based Training   Blended Learning    
Synchronous
Instruction
Asynchronous
Instruction
 

 

Effective February 27, 2012, the course dates listed on the SYS-ED schedules only will be available based upon approved and authorized user ID.

Copyright Acknowledgement: The software product(s) listed in this outline are owned and copyrighted by their respective companies. SYS-ED makes no representation regarding ownership in any of the software products that we train on.

SYS-ED courses are not intended for or open to the general public. They are intended for employees of Fortune 1000 companies, government municipalities, consulting companies, software, companies, healthcare providers. and mid-sized businesses. Individuals attending such courses will be required to execute a statement acknowledging that the employer will be paying for the SYS-ED course and that the employer has a licensed version of the software.