Revised 08/2021

ITD 134 - PL/SQL Programming (3 CR.)

Course Description

Presents a working introduction to PL/SQL programming with the Oracle RDBMS environment. Includes PL/SQL fundamentals of block program structure, variables, cursors and exceptions, and creation of program units of procedures, functions, triggers and packages. Lecture 3 hours per week.

General Course Purpose

This course provides a comprehensive foundation sufficient for a student to write PL/SQL programs to access Oracle databases.

Course Prerequisites/Corequisites

Prerequisite: ITD 132

Course Objectives

Upon completing the course, the student will be able to:

• Get familiar with a development tool; such as SQL Developer
• Learn the structure of PL/SQL blocks
• Recall variables and use them in code
• Apply loop control structures
• Use conditional statement control structures
• Know how to select data into variables
• Be able to create and use cursors for data selections
• Know how to create procedures and functions

Major Topics to Be Included

• Major Topics to be Included
• PL/SQL Block Structure
• Declaration of Variables
• Loop Control Structures
• Conditional statement control structures
• Selection of data into variables
• Use of cursors for data selections
• Procedures and functions

Student Learning Outcomes

PL/SQL Block Structure

• Establish Oracle log-in
• Identify functional capabilities of the development tool used; such as SQL Developer
• Run select statements using the development tool Declaration of Variables

• Learn structure of a PL/SQL block
• Identify datatypes
• Declare variables Loop Control Structures

• Write basic loop construct
• Code WHILE and FOR loops
• Describe the differences between loops Conditional statement control structures

• Write simple conditional statements
• Write compound conditional statements
• Describe the differences between conditional operators Selection of data into variables

• Code selection of single or multiple columns into variables
• Display the selected values Use of cursors for data selections

• Describe the difference between implicit and explicit cursors
• Use cursor attributes
• Use cursor in a loop Procedures and functions

• Create procedures with parameters
• Create functions with parameters
• Code single row and aggregate functions
• Implement exception handling
• Create and use triggers

Required Time Allocation

Topics do not need to be followed sequentially. Many topics are taught best as an integrated whole, often revisiting the topic several times, each time at a higher level. The final exam time is not included in the time table. The last category, Other Optional Content, leaves time for an instructor to tailor the course to special needs or resources.