Module 8: Advanced SQL Database Systems: Design, Implementation, and Management, 14e

Working with Variables

Figure 8.11 Variable Manipulation

Conditional Execution

Figure 8.12 Conditional Execution

Summary

Now that the lesson has ended, you should be able to: 1.Use SQL to create a table manually 2.Use SQL to create a copy of a table using a subquery 3.Manipulate the structure of tables to add, modify, and remove columns and constraints 4.Use SQL to do data manipulation (insert, update, and delete rows of data) 5.Use SQL to create database views, including updatable views 6.Use procedural SQL to create triggers, stored procedures, and procedural SQL functions Create embedded SQL

Data Definition Commands (cont 2)

see image

Data Definition Commands (cont 3)

see image

Select Processing with Cursors

•A cursor is a special construct used to hold data rows returned by a SQL query −An implicit cursor is automatically created when the SQL statement returns only one row −An explicit cursor holds the output of a SQL statement that may return two or more rows but could return zero or only one row −To create an explicit cursor, you use the following syntax: DECLARE cursor_name CURSOR FOR select-query; •Cursor-style processing involves retrieving data from the cursor one row at a time −When you fetch a row from the cursor, the data from the "current" row in the cursor is copied to the PL/SQL variables Figure 8.19 An Explicit Cursor Example

Stored Procedures

•A stored procedure is a named collection of procedural and SQL statements −They are stored in the database −A major advantage of stored procedures is that they can be used to encapsulate and represent business transactions •Using stored procedures offers the following advantages: −Stored procedures substantially reduce network traffic and increase performance −Stored procedures help reduce code duplication by means of code isolation and code sharing

Knowledge Check Activity 8-1 •What is the purpose of a CHECK constraint?

Answer: A CHECK constraint is used to limit the values that can appear in an attribute. It performs the function of enforcing a domain

Triggers

•A trigger is a procedural SQL code automatically invoked by the relational database management system when a data manipulation event occurs •It is useful to remember the following: −A trigger is invoked before or after a data row is inserted, updated, or deleted −A trigger is associated with a database table −Each database table may have one or more triggers −A trigger is executed as part of the transaction that triggered it −Triggers are critical to proper database operation and management •A trigger definition contains the following parts: −The triggering timing: indicates when the trigger's code executes −The triggering statement or triggering event: the statement that causes the trigger to execute −The triggering level: §A statement-level trigger is executed once and a row-level trigger requires use of the FOR EACH ROW keywords −The triggering action: the SQL and procedural SQL code enclosed between the BEGIN and END keywords

User Defined Functions

•A user defined function (UDF) is basically a named group of procedural and SQL statements that returns a value, as indicated by a RETURN statement in its program code •To create a function, you use the following syntax: CREATE FUNCTION function_name (IN argument data-type, ...) RETURNS data-type BEGIN Procedure SQL statements; ... RETURN (value or expression); END;

Virtual Tables: Creating a View

•A view is a virtual table based on a SELECT query that is saved as an object in the database •A base table is the table on which a view is based •CREATE VIEW is a SQL command that creates a logical, "virtual" table −The view can be treated as a real table −The following is the CREATE VIEW syntax: CREATE VIEW viewname AS SELECT query Figure 8.5 Creating a Virtual Table with the Create View command

Data Manipulation Commands

•Adding Table Rows −INSERT is a SQL command that allows the insertion of one or more data rows into a table using a subquery −INSERT command syntax: INSERT INTO tablename VALUES (value1, value2, ..., valuen) −To inserting rows with null attributes, use a NULL entry −When inserting rows with optional attributes, you can indicate attributes that have required values •Inserting Table Rows with a SELECT Subquery −Using a subquery with the INSERT command, it is possible to add multiple rows to a table, using another table as the source, at the same time −Use the following SELECT syntax: INSERT INTO target_tablename[(target_columnlist)] SELECT source_columnlist FROM source_tablename; •Saving Table Changes −The syntax for the COMMIT command is: COMMIT [WORK | TRANSACTION] •Updating Table Rows −UPDATE command is used to modify data in a table −The syntax for the UPDATE command is as follows: UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist ]; •Deleting Table Rows −The following is the DELETE statement syntax: DELETE FROM tablename [WHERE conditionlist ]; •Restoring Table Contents −The ROLLBACK command is used to restore the database table contents to the condition that existed after the last COMMIT statements ROLLBACK;

Altering Table Structures

•All changes in the table structure are made by using the ALTER TABLE command followed by a keyword that produces the specific change you want to make −The following three options are available: ADD, MODIFY, and DROP •Changing a Column's Data Type −ALTER can be used to change a data type •Changing a Column's Data Characteristics −If the column to be changed already contains data, you can make changes in the column's characteristics if those changes do not alter the data type •Adding a Column −You can alter an existing table by adding one or more columns −Be careful not to include the NOT NULL clause for the new column •Adding Primary Key, Foreign Key, and Check Constraints −Primary key syntax is as follows: ALTER TABLE PART ADD PRIMARY KEY (PART_CODE); −Foreign key syntax is as follows: ALTER TABLE PART ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR; −Check constraint syntax is as follows: ALTER TABLE PART ADD CHECK (PART_PRICE >= 0); •Dropping a Column −The following is the syntax: ALTER TABLE VENDOR DROP COLUMN V_ORDER; •Deleting a Table From the Database −DROP TABLE is an SQL command used to remove database tables −The following is the syntax: DROP TABLE PART;

Knowledge Check Activity 8-3 •What is a stored procedure, and why is it particularly useful?

•Answer: A stored procedure is a named block of Procedural SQL and standard SQL statements. One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions.

Knowledge Check Activity 8-2 •What is the difference between an INSERT command and an UPDATE command?

•Answer: The INSERT command is used to add a new row to a table. The UPDATE command changes the values in attributes of an existing row. UPDATE will not increase the number of rows in a table, but INSERT will.

Creating Table Structures

•CREATE TABLE command −CREATE TABLE is an SQL command that creates a table's structures using the characteristics and attributes given −The following is the syntax to use in order to implement a table structure CREATE TABLE tablename ( column1 data type [constraint] [, column2 data type [constraint] ] [, PRIMARY KEY (column1 [, column2]) ] [, FOREIGN KEY (column1 [, column2]) REFERENCES tablename] [, CONSTRAINT constraint ] );

Creating Table Structures (cont 3)

•Create a Table with a SELECT Statement −SQL provides a way to rapidly create a new table based on selected columns and rows of an existing table using a subquery −All of the data rows returned by the SELECT statement are copied automatically •SQL Indexes −CREATE INDEX improves the efficiency of searches and avoids duplicate column values −DROP INDEX is an SQL command used to delete database objects such as tables, views, indexes, and users

Embedded SQL

•Embedded SQL are SQL statements contained within an application programming language •A host language is any language that contains embedded SQL statements •Differences between SQL and procedural languages include the following −Run-time mismatch: §With SQL, each instruction is parsed and it is executed one instruction at a time §The Host program typically runs at the client side in its own memory space •Differences between SQL and procedural languages (continued) −Processing mismatch §Conventional programming languages process one data element at a time §Newer programming environments manipulate data sets in a cohesive manner −Data type mismatch §Data types provided by SQL might not match data types used in different host languages •The embedded SQL framework defines the following: −A standard syntax to identify embedded SQL code within the host language −A standard syntax to identify host variables −A communication area used to exchange status and error information between SQL and the host language •Static SQL is a style of embedded SQL I which the SQL statements do not change while the application is running •Dynamic SQL is term used to describe an environment in which the SQL statement is generated at run time −In a dynamic SQL environment, a program can generate the SQL statements that are required to respond to ad hoc queries

Iteration or Looping

•Figure 8.14 A Simple Loop

Iteration or Looping (cont)

•Figure 8.15 Implementation of a WHILE Loop

Auto Increment, Identity, and Sequences

•MySQL uses the AUTO_INCREMENT property during table creation to indicate that values for an attribute should be generated in the same fashion −Similar to IDENTITY columns, only one column in a table can have AUTO_INCREMENT specified, and that column must also be defined as the primary key of the table •A sequence is an object for generating unique segment values for a field •In recent years, Oracle added support for Identity columns and MS SQL Server added support for sequences •The following are similarities in the use of sequences across multiple DBMSs −Sequences are an independent object in the database −Sequences have a name and can be used anywhere a value expected −Sequences are not tied to a table or a column −Sequences generate a numeric value that can be assigned to any column in any table −The table attribute with an assigned value can be edited and modified

Updatable Views

•One of the most common operations in production database environments is to use batch update routines to update a master table attribute with transaction data •A batch update routine is a routine that pools multiple transactions into a single batch to update a master table in a single operation •A view that can update attributes in base tables that are used in the view is known as an updatable view •The following are the most common updatable view restrictions: −GROUP BY expressions or aggregate functions cannot be used −Set operators cannot be used −Most restrictions are based on the use of JOINs or group operators in views

Stored Procedures with Parameters

•One of the most valuable features of working with stored procedures is their ability to use parameters •A parameter is a value that is provided to the program at the time of execution •Figure 8.21 shows the code necessary to pass an argument, or parameter, to represent a rate of increase to the procedure Figure 8.21 Second Version of the PRC_PROD_DISCOUNT Stored Procedure

Procedural SQL

•Persistent stored module (PSM) is a block of code that contains standard SQL statements and procedural extensions that is stored and executed at the DBMS server •Procedural SQL is an extension of SQL that adds procedural programming capabilities, such as variables and logical flow control, to SQL and is designed to run inside the database −The procedural code is executed as a unit by the DBMS when it is invoked by the end user •End users can use procedural SQL to create the following: −Stored procedures −Triggers −Procedural SQL functions

Creating Table Structures (cont 2)

•SQL Constraints −The FOREIGN KEY constraint ensures that you cannot delete a vendor from the VENDOR table if at least one product row references that vendor −The NOT NULL constraint ensures that a column does not accept nulls −The UNIQUE constraint ensures that all values in a column are unique −The DEFAULT constraint assigns a value to an attribute when a new row is added to a table −The CHECK constraint is used to validate data when an attribute value is entered

Data Definition Commands

•Starting database model −Refer to Figure 8.1 on the following slide •Creating the Database −Before a new RDBMS can be used, the database structure and the tables that will hold the end-user data must be created •The Database Schema −A schema is a logical group of database objects—such as tables and indexes—that are related to each other