A Guide to SQL, Ninth Edition Page 8–1
Chapter 8
SQL Functions and Procedures
At a Glance
Instructor’s Manual Table of Contents
• Overview
• Objectives
• Teaching Tips
• Quick Quizzes
• Class Discussion Topics
• Additional Projects
• Additional Resources
• Key Terms
A Guide to SQL, Ninth Edition Page 8–2
Lecture Notes
Overview
In this chapter, students learn how to use functions that apply to values in individual rows.
Specifically, they learn how to use functions with character data, numeric data, and dates.
They also learn how to concatenate two columns into a single expression. Students learn
how to embed SQL commands in PL/SQL and T-SQL procedures to retrieve rows and
update data. They examine different ways to manage errors in procedures. Finally, they
learn how to create and use cursors and triggers.
Chapter Objectives
In this chapter, students learn about:
• Using functions in queries
• Using the UPPER and LOWER functions with character data
• Using the ROUND and FLOOR functions with numeric data
• Adding a specific number of months or days to a date
• Calculating the number of days between two dates
• Using concatenation in a query
Teaching Tips
Using SQL in a Programming Environment
2. Point out that SQL is a nonprocedural language, and PL/SQL and T-SQL (Transact-
SQL) are procedural languages.
4. Mention that you cannot embed SQL commands in Access programs.
5. Review the Access User Note on page 228. If you are using Access, decide which
sections of the chapter you will cover.
A Guide to SQL, Ninth Edition Page 8–3
Teaching
Tip
Students need some understanding of programming concepts such as variable,
procedure, and loops before they can understand the material presented in this
chapter.
You may decide not to cover this entire chapter if you feel your students do not
have the programming background to understand stored procedures. At a
minimum, cover the section on functions.
Procedural languages also are called third-generation languages, while
nonprocedural languages are known as fourth-generation languages. Machine
language is first-generation, and assembly languages are second generation.
Embedded SQL is probably used more than students realize. You also can embed
SQL in Java. SQLJ provides a somewhat more object-oriented approach to
embedding SQL. For more information on SQLJ and JDBC (Java Database
Connectivity), see the Additional Resources section.
Using Functions
1. Point out that aggregate functions perform calculations on groups of records.
Character Functions
2. Define argument. Although there are some functions that do not require arguments,
most functions have at least one argument.
4. Point out that the LOWER function displays a value in lowercase letters.
5. Review the Access User Note on page 230. Access uses UCASE and LCASE instead of
UPPER and LOWER.
Number Functions
2. Point out that the ROUND function has two arguments.
3. Use Example 4 and Figure 8-4 to illustrate adding a specific number of days to a date.
5. Review the Access User Note on page 233. Access uses the DATE() function to obtain
today’s date.
Teaching
Tip
You can use many functions in SQL in SELECT statements. For more
information on functions, see the Additional Resources section.
The way a date is actually stored varies from one DBMS to another. For
example, in Microsoft Access, dates are stored as integers with 1 representing
January 1, 1900 and each succeeding day assigned the next number. These
internal storage mechanisms allow date arithmetic to occur.
Quick Quiz 1
1. To display a value in uppercase letters in Oracle, use the _____ function.
2. To add a specific number of months to a date in Oracle, use the _____ function.
Concatenating Columns
2. Point out that, in Oracle, you type two vertical lines (||) between column names.
4. Use Example 6 and Figure 8-6 to illustrate concatenating two columns and using the
RTRIM function.
6. Review the Embedded Q&A on page 234.
7. Review the Access User Note on page 234. Access uses the & symbol to concatenate
columns.
A Guide to SQL, Ninth Edition Page 8–5
Stored Procedures
1. Define client/server system. In a client/server system, the database is stored on a
3. Mention the reasons for creating stored procedures, including (1) improved overall
4. Review the Access User Note on page 235. Access does not support stored procedures.
5. Mention that you create stored procedures in Oracle using PL/SQL.
Retrieving a Single Row and Column
1. Use Example 7 and Figure 8-7 to illustrate creating a stored procedure to find a
rep’s name given the rep’s number.
3. Use Figure 8-7 to discuss the elements of PL/SQL commands.
5. Point out the rules for variable names. Variable names must start with a letter and
6. Mention that you must declare variables. The %TYPE attribute ensures that a
variable has the same data type as a particular column in the table.
8. Mention the slash (/) at the end of the procedure.
10. Discuss how to call a procedure.
11. Use Figure 8-8 to illustrate calling a procedure.
Error Handling
1. Use Figure 8-9 to discuss possible errors that can occur.
3. Use Figure 8-10 to illustrate a stored procedure with error handling.
Using Update Procedures
A Guide to SQL, Ninth Edition Page 8–6
Changing Data with a Procedure
Deleting Data with a Procedure
1. Use Example 9 and Figures 8-14 and 8-15 to illustrate deleting data with a procedure.
2. Discuss why it is necessary to delete the rows in the ORDER_LINE table before
Teaching
Tip
For more information on stored procedures in Oracle, see the Additional
Resources section.
Quick Quiz 2
1. The _____ function removes extra spaces to the right of a value in a column.
2. A(n) _____ is a saved file stored on the server.
Selecting Multiple Rows with a Procedure
1. Mention that there are times when you need to use a SELECT statement to retrieve multiple
rows.
Using a Cursor
1. Define cursor. A cursor is a pointer to a row in the collection of rows retrieved by an
3. Point out that using a cursor in a procedure involves the OPEN, FETCH, and CLOSE
4. Relate the OPEN, FETCH, and CLOSE commands to the OPEN, READ, and CLOSE
commands used in processing a sequential file.
Opening a Cursor
1. Use Figure 8-16 to discuss the status of a cursor before the OPEN command is issued.
2. Use Figure 8-17 to show the result of opening a cursor.
A Guide to SQL, Ninth Edition Page 8–7
Fetching Rows from a Cursor
Closing a Cursor
Writing a Complete Procedure Using a Cursor
1. Use Figure 8-24 to review the complete procedure using a cursor.
3. Use Figure 8-25 to show the results of using the procedure.
Using More Complex Cursors
1. Use Example 11 and Figures 8-26 and 8-27 to illustrate using a cursor that involves
Advantages of Cursors
(1) The coding in the program is greatly simplified.
(2) In a normal PL/SQL program, the programmer must determine the most efficient
(3) If the database structure changes in such a way that the necessary information is still
Using T-SQL in SQL Server
1. Point out that, although the syntax may be different, the reason for using stored
Retrieving a Single Row and Column
2. Point out that, in T-SQL, you must assign a data type to parameters.
Changing Data with a Stored Procedure
1. Review the T-SQL code to change the name of a customer.
A Guide to SQL, Ninth Edition Page 8–8
Deleting Data with a Stored Procedure
1. Review the T-SQL code to delete an order number from both the ORDER_LINE table
Using a Cursor
2. Point out that you still must declare a cursor, open a cursor, fetch rows, and close a
cursor.
Using More Complex Cursors
1. Review the T-SQL code using more complex queries.
Teaching
Tip
For more information on stored procedures in SQL Server, see the Additional
Resources section.
Using SQL in Microsoft Access
1. In Microsoft Access, programs are written in Visual Basic, which does not allow the
can include arguments.
Deleting Data with Visual Basic
2. Review the Note on page 253.
Running the Code
2. You also can run functions directly in the Immediate window. To run a function in the
3. Use Figure 8-29 to illustrate running the function in the Immediate window.
Updating Data with Visual Basic
2. Use Example 13 and Figure 8-30 to illustrate the code to change a value.
3. Use Figure 8-31 to illustrate entering values for the arguments to run the code.
A Guide to SQL, Ninth Edition Page 8–9
Inserting Data with Visual Basic
2. Create the appropriate INSERT command in the string SQL variable. The procedure
will have multiple arguments, one for each value to be inserted.
Finding Multiple Rows with Visual Basic
1. When several rows are returned as a result of a SELECT statement, Visual Basic has a
problem handling multiple rows.
3. Use Figure 8-33to show the results of running the code.
4. Mention the Note on page 257.
Using a Trigger
1. Define trigger. A trigger is a procedure that executes automatically in response to an
associated database operation, such as the INSERT, UPDATE, or DELETE commands.
3. To create a trigger, use the CREATE TRIGGER command.
5. Use Figure 8-35 to illustrate creating a trigger that executes after a user updates an order
line.
7. Use Figure 8-36 to illustrate creating a trigger that executes after a user deletes an order
line.
9. Review the T-SQL code to create and use a trigger in the SQL Server Note on page 259.
10. SQL Server uses INSERTED and DELETED system tables to store new values and old
values.
Quick Quiz 3
1. A _____ is a pointer to a row in the collection of rows retrieved by an SQL command.
2. A(n) _____ is a procedure that executes automatically in response to an associated
database operation, such as INSERT, UPDATE, or DELETE command.
A Guide to SQL, Ninth Edition Page 8–10
Class Discussion Topics
1. Why do business organizations need to be able to do “date arithmetic;” that is, to
calculate future dates using functions and calculations?
2. Error handling refers to methods of handling exceptional conditions. Is error handling
required in a program?
Additional Projects
1. Assign students to teams. Have each team research a different function category in
Oracle; that is, one team would research character functions, another team would
Additional Resources
1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last
2. Java and SQL: www.swtech.com/java/jdbc/.
4. Stored Procedures in Oracle:
www.devshed.com/c/a/Oracle/Oracle-Stored-Procedures/
Key Terms
➢ ADD_MONTHS: Function to add a specific number of months to a date
➢ argument: Provides a value on which a function will operate and appears inside
parentheses
➢ call: To use or execute a stored procedure
➢ client: A computer that is connected to a network and has access through the server to
➢ concatenate: To combine two or more character columns into a single expression
➢ concatenation: The process of combining two or more character columns into a single
expression
➢ cursor: A pointer to a row in the collection of rows retrieved by an SQL command
➢ embed: To place SQL commands in a procedural language
➢ FETCH: The command to select the next row in PL/SQL or T-SQL
A Guide to SQL, Ninth Edition Page 8–11
➢ FLOOR: Function that truncates (removes) everything to the right of the decimal point
➢ RTRIM: Function to remove extra spaces to the right of a character value
➢ server: A computer that stores a database and can be accessed through a network
➢ stored procedure: A query saved in a file that users can execute later
➢ SYSDATE: In Oracle, function used to obtain today’s date stored in the computer
➢ Transact-SQL: A procedural language used by SQL Server as an extension of SQL