PCEC120

DATES:

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 containing 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.