CIS 338 - SQL/Oracle

1. Develop blocks of PL/SQL code
   1. Distinguish between SQL and PL/SQL
   2. Describe the benefits of PL/SQL over SQL
   3. Recognize the components of a PL/SQL block of code
   4. Distinguish between anonymous PL/SQL blocks and PL/SQL subprograms
   5. Describe the significance of variables in PL/SQL
   6. Declare and use variables in PL/SQL
   7. Declare and use constants in PL/SQL
   8. Assign values to PL/SQL variables
   9. Distinguish between PL/SQL and Non-PL/SQL variables
   10. List the guidelines for PL/SQL block syntax
   11. Demonstrate effective commenting in PL/SQL code
   12. Execute an anonymous PL/SQL block
2. Control PL/SQL flow of execution
   1. Recognize the significance of the executable section of PL/SQL blocks
   2. Describe conditional statements and process flow
   3. Define a cursor
   4. Explain the difference between implicit and explicit cursors
   5. Declare and use cursors.
   6. Write executable statements within the executable section of PL/SQL
   7. Write a successful SELECT statement in PL/SQL
   8. Write DML statements in PL/SQL
   9. Perform transaction control in PL/SQL
   10. Determine the outcome of a SQL DML statement
   11. Describe the rules of nested blocks
3. Write Control Structures within PL/SQL
   1. Identify the three types of selection structures available in PL/SQL
   2. Identify the three types of repetition structures available in PL/SQL
   3. Construct and IF statement in PL/SQL
   4. Construct looping structures in PL/SQL
   5. Control block flow using nested loops and labels
   6. Use logic tables
4. Create PL/SQL Procedures and Functions
   1. Describe the use of procedures and functions
   2. Compile a procedure
   3. Compile a function
   4. Debug the compile of a procedure or function
   5. Invoke a procedure from the SQL*Plus command prompt
   6. Invoke a procedure from within a PL/SQL subprogram
   7. Invoke a function from the SQL*Plus command prompt
   8. Invoke a function from within a PL/SQL subprogram
   9. Revise and re-compile a procedure
   10. Revise and re-compile a function
   11. Remove a procedure from the database
   12. Remove a function from the database
5. Implement error handling in PL/SQL code
   1. Recognize the three basic types of exceptions
   2. Code exception handlers
   3. Discuss the guidelines for trapping exceptions
6. Create PL/SQL Packages
   1. Describe packages and list their possible components
   2. Create a package to group together related variables, cursors, constructs, exceptions, procedures, and functions
   3. Make a package construct either public or private
   4. Invoke a package construct
   5. Write packages that make use of the overloading feature of PL/SQL
   6. Compile a package
   7. Remove a package from the database
7. Create Database Triggers
   1. Describe database triggers and their use
   2. Create database triggers
   3. Describe database trigger firing rules
   4. Drop database triggers
8. Test PL/SQL programs thoroughly