MET CS 669 Database Design and Implementation for Business
SQL Lab 4 Instructions: Procedural SQL

Objective
The objective of this lab is to teach you how to develop and use basic stored procedures and triggers the procedural language of your chosen DBMS.

Prerequisites
Before attempting this lab, it is best to read the textbook and lecture material covering the objectives listed above. While this lab shows you how to create and use these constructs in SQL, the lab does not explain in full the theory behind the constructs, as does the lecture and textbook. The second section in this lab builds on Lab 3. It is best to complete Lab 3 first before completing the second section in this lab.

Required Software
The
The SQL declarative language is handled in part by a SQL query optimizer, which is a substantive component of the DBMS that determines how the database will perform the query, while the procedural language is not in any way handled by the query optimizer. In short, the execution of each of the two languages in a DBMS follows two separate paths within the DBMS. Modern relational DBMS support the creation and use of persistent stored modules, namely, stored procedures and triggers, which are widely used to perform operations critical to modern information systems. A stored procedure contains logic that is executed when a transaction invokes the name of the stored procedure. A trigger contains logic that is automatically executed by the DBMS when the condition associated with the trigger occurs. Not surprisingly stored procedures and triggers can be defined in both PL/SQL and T‐SQL. This lab helps teach you how to intelligently define and use both types of persistent stored modules.

Page 2 of 25

This lab provides separate subsections for SQL Server and Oracle, because there are some significant differences between both implementations. The syntax for the procedural language differs between Oracle and SQL Server, which unfortunately means that we cannot use the same