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.
CONTENT AND OBJECTIVES:
Oracle SQL and Procedures
List the reasons why PL/SQL should be used instead of SQL.
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.
Code queries and PL/SQL routines to capture the results of Aggregate, Numeric, Character, Date, and Conversion functions.
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.
Use the %ROWTYPE to define a composite type. Select the data into the composite type.
Create a table with several different types of fields; insert three values and create a unique index.
Alter the table; then drop the table.
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.
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.
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.
Develop a package containing two routines and test the package.
Oracle Supplied Packages
Read and display the contents of a file.
Create and use multiple types of triggers: BEFORE, AFTER, and a compound trigger. Test the trigger by running INSERT, DELETE, and UPDATE statements.
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.