Active Outline

General Information

Course ID (CB01A and CB01B)
CISD364C
Course Title (CB02)
Introduction to PL/SQL
Course Credit Status
Non-Credit
Effective Term
Fall 2024
Course Description
This course covers Oracle PL/SQL features including data definition and data manipulation using expressions, control structures, and Oracle objects. Error handling, predefined packages, triggers, transactions, and advanced PL/SQL features are also covered.
Faculty Requirements
Discipline 1
[Computer Information Systems (Computer network installation, microcomputer technology, computer applications)]
Discipline 3
[Computer Science]
FSA
[FHDA FSA - CIS]
Course Family
Not Applicable

Course Justification

This is a noncredit enhanced CTE course. PL/SQL is a natural extension of Structured Query Language enabling procedural constructs implementation in a database. Such implementation makes for faster database access and hence the popularity among organizations developing enterprise applications. This course is transferable to all CSUs. This course belongs on the Database Design for Developers Certificate of Completion.

Foothill Equivalency

Does the course have a Foothill equivalent?
No
Foothill Course ID

Course Philosophy

Formerly Statement

Course Development Options

Basic Skill Status (CB08)
Course is not a basic skills course.
Grade Options
  • Letter Grade
  • Pass/No Pass
Repeat Limit
99

Transferability & Gen. Ed. Options

Transferability
Not transferable

Units and Hours

Summary

Minimum Credit Units
0.0
Maximum Credit Units
0.0

Weekly Student Hours

TypeIn ClassOut of Class
Lecture Hours4.08.0
Laboratory Hours1.50.0

Course Student Hours

Course Duration (Weeks)
12.0
Hours per unit divisor
36.0
Course In-Class (Contact) Hours
Lecture
48.0
Laboratory
18.0
Total
66.0
Course Out-of-Class Hours
Lecture
96.0
Laboratory
0.0
NA
0.0
Total
96.0

Prerequisite(s)

Corequisite(s)

Advisory(ies)

ESL D272. and ESL D273., or ESL D472. and ESL D473., or eligibility for EWRT D001A or EWRT D01AH or ESL D005.

CIS D064B or D364B

Limitation(s) on Enrollment

Entrance Skill(s)

General Course Statement(s)

NONCREDIT: (This is a noncredit enhanced, CTE course.)

Methods of Instruction

Lecture and visual aids

Discussion and problem solving performed in class

Other: Lab Exercises

Discussion of assigned readings

Assignments

  1. Reading from text
  2. Documenting, coding, testing and debugging six to ten programs guided with clearly documented design.

Methods of Evaluation

  1. One or two midterm examinations requiring students to write code applying topics covered in the lectures and reading and evaluated on correctness, documentation, and code quality.
  2. Final examination requiring students to write code applying topics covered in the lectures and reading and evaluated on correctness, documentation, and code quality.
  3. Evaluation of programming assignments, based on correctness, documentation, code quality, and test plan executions.

Essential Student Materials/Essential College Facilities

Essential Student Materials: 
  • None
Essential College Facilities:
  • Computer lab equipped with an development environment tool supporting PL/SQL development

Examples of Primary Texts and References

AuthorTitlePublisherDate/EditionISBN
Benjamin Rosenzweig and Elena RakhimovOracle PL/SQL by Example (The Oracle Press Database and Data Science)Oracle PressMay 21, 2023 - 6th editionISBN-13 : 978-0138062835

Examples of Supporting Texts and References

None.

Learning Outcomes and Objectives

Course Objectives

  • Interpret the features of PL/SQL with a general understanding of where and how this language can be used.
  • Use development and execution environments
  • Describe the basics of PL/SQL.
  • Use expressions and operators
  • Use PL/SQL control structures
  • Use PL/SQL records
  • Use SQL within PL/SQL
  • Describe different built-in SQL Functions and how, where they can be used
  • Define different Cursor types and how they can be used in PL/SQL
  • Implement error handling in PL/SQL code
  • Declare and use Collections and Collection Method
  • Create Procedures, Functions and Packages
  • Describe different types of database triggers and how they should be used
  • Use the advanced features of PL/SQL

CSLOs

  • Design solutions for introductory level problems using appropriate design methodology incorporating procedural database constructs.

  • Create algorithms, code, document, debug, and test introductory level PL/SQL programs.

Outline

  1. Interpret the features of PL/SQL with a general understanding of where and how this language can be used.
    1. Describe the purpose of PL/SQL
    2. Explain the benefits of PL/SQL
    3. Describe the use of PL/SQL for the developer as well as the DBA
  2. Use development and execution environments
    1. Application Models and PL/SQL
    2. PL/SQL Development Tools
  3. Describe the basics of PL/SQL.
    1. The PL/SQL Block
    2. Lexical Units
      1. Identifiers
      2. Delimiters
      3. Literals
      4. Comments
    3. Variable Declarations
      1. Declarations Syntax
      2. Variable Initialization
    4. PL/SQL Types
  4. Use expressions and operators
    1. Describe the significance of the executable section
    2. Use identifiers correctly
    3. Write statements in the executable section
  5. Use PL/SQL control structures
    1. If-Then-Else
    2. Case
    3. Loops
    4. Gotos and Labels
    5. Pragmas
  6. Use PL/SQL records
    1. Create user-defined PL/SQL records
    2. Create a record with the %ROWTYPE attribute
    3. Create an INDEX BY table
    4. Create an INDEX BY table of records
  7. Use SQL within PL/SQL
    1. DML in PL/SQL
    2. Pseudocolumns
    3. GRANT, REVOKE, and Privileges
    4. Transaction Control
  8. Describe different built-in SQL Functions and how, where they can be used
    1. Character Functions Returning Character Values
    2. Character Functions Returning Numeric Values
    3. NLS Functions
    4. Data and Date time Functions
    5. Conversion Functions
    6. Aggregate and Analytic Functions
    7. Other Functions
  9. Define different Cursor types and how they can be used in PL/SQL
    1. What is a Cursor?
    2. Processing Explicit Cursors
    3. Processing Implicit Cursors
    4. Cursors Fetch Loops
      1. Simple Loops
      2. While Loops
      3. Cursor for Loops
      4. NO_DATA_FOUND versus %NOTFOUND
      5. SELECT FOR UPDATE Cursors
    5. Cursor variable
  10. Implement error handling in PL/SQL code
    1. What is an Exception?
      1. Declaring Exceptions
      2. Raising Exceptions
      3. Handling Exceptions
      4. The EXCEPTION_INIT Pragma
      5. Using RAISE_APPLICATION_ERRORS
    2. Exception Propagation
    3. Exception Guidelines
  11. Declare and use Collections and Collection Method
    1. Collections in the Database
    2. Collection Methods
  12. Create Procedures, Functions and Packages
    1. Describe the usage of procedure or a function
    2. Creating functions and procedures
    3. Invoking functions and procedures
    4. Removing a procedure or a function
    5. Creating, editing and removing packages
  13. Describe different types of database triggers and how they should be used
    1. Subprogram Locations
    2. Considerations of Stored Subprograms and Packages
    3. Using Stored Functions in SQL Statements
    4. Pinning in the Shared Pool
  14. Use the advanced features of PL/SQL
    1. Types of Triggers
    2. Creating Triggers
    3. Mutating Tables
    4. Language Features
    5. Advanced Packages

Lab Topics

  1. Debug code and output results of execution.
  2. Write and/or debug code implementing expressions and operators.
  3. Write and/or debug code implementing control structures.
  4. Write and/or debug code implementing records
  5. Write and/or debug code implementing functions and methods.
  6. Write and/or debug code implementing error handling.
  7. Write and/or debug code implementing Collections.
  8. Write and/or debug code implementing Packages and Triggers.
Back to Top