IST 210 Chapter 8

Stored Procedure

(1) A named collection of procedural and SQL statements. (2) Business logic stored on a server in the form of SQL code or another DBMS-specific procedural language. Syntax: CREATE OR REPLACE PROCEDURE procedure_name [(argument [IN/OUT] data-type, ...)] [IS/AS] [variable_namedata type[:=initial_value]] BEGIN PL/SQL or SQL statements; ... END;

DELETE

A SQL command that allows data rows to be deleted from a table. Syntax: DELETE FROM tablename [WHERE conditionlist];

DROP TABLE

A SQL command used to delete database objects such as tables, views, indexes, and users.

Implicit Cursor

A cursor that is automatically created in procedural SQL when the SQL statement returns only one value.

Stored Function

A named group of procedural and SQL statements that returns a value, as indicated by a RETURN statement in its program code. Syntax: CREATE FUNCTION function_name (argument IN data-type, ...) RETURN data-type [IS] BEGIN PL/SQL statements; ... RETURN (value or expression); END;

Batch update route

A routine that pools transactions into a single group to update a master table in a single operation.

Updatable View

A view that can update attributes in base tables that are used in the view.

Host Language

Any language that contains embedded SQL statements.

DEFAULT

Assigns a value to an attribute when a new row is added to a table.

Date Data Type

DATE: Stores dates in the Julian date format

ON UPDATE CASCADE

Ensures that if a change in a table, it will automatically update all Foreign Key references to that table.

Authentication

The process through which a DBMS verifies that only registered users can access the database.

Base table

The table on which a view is based.

CHECK

Used to validate data when an attribute value is entered. The CHECK constraint does precisely what its name suggests: it checks to see that a specified condition exists. Examples of such constraints include the following: - The minimum order value must be at least 10. - The date must be after April 15, 2018. - If the CHECK constraint is met for the specified attribute (i.e., the condition is true), the data is accepted for that attribute. If the condition is found to be false, an error message is generated and the data is not accepted.

Cursor Attributes

%ROWCOUNT Returns the number of rows fetched so far. If the cursor is not OPEN, it returns an error. If no FETCH has been done but the cursor is OPEN, it returns 0. %FOUND Returns TRUE if the last FETCH returned a row, and FALSE if not. If the cursor is not OPEN, it returns an error. If no FETCH has been done, it contains NULL. %NOTFOUND Returns TRUE if the last FETCH did not return any row, and FALSE if it did. If the cursor is not OPEN, it returns an error. If no FETCH has been done, it contains NULL. %ISOPEN Returns TRUE if the cursor is open (ready for processing) or FALSE if the cursor is closed. Remember, before you can use a cursor, you must open it.

Anonymous PL/SQL block

A PL/SQL block that has not been given a specific name.

UPDATE

A SQL command that allows attribute values to be changed in one or more rows of a table. Syntax: UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist];

INSERT

A SQL command that allows the insertion of one or more data rows into a table using a subquery. Syntax: INSERT INTO tablename VALUES (value1, value2, ..., valuen)

CREATE VIEW

A SQL command that creates a logical, "virtual" table based on stored end-user tables. The view can be treated as a real table.

CREATE TABLE

A SQL command that creates a table's structures using the characteristics and attributes given. Syntax: CREATE TABLE tablename ( column1 data type [constraint] [, column2 data type [constraint]] [, PRIMARY KEY (column1 [, column2])] [, FOREIGN KEY (column1 [, column2]) REFERENCES tablename] [, CONSTRAINT constraint]);

CREATE INDEX

A SQL command that creates indexes on the basis of a selected attribute or attributes. Syntax: CREATE [UNIQUE]INDEX indexname ON tablename(column1 [, column2(DESC)]) Note: SQL does not let you write over an existing index without warning you first, thus preserving the index structure within the data dictionary. Using the UNIQUE index qualifier, you can even create an index that prevents you from using a value that has been used before.

ROLLBACK

A SQL command that restores the database table contents to the condition that existed after the last COMMIT statement. Syntax: ROLLBACK;

DROP INDEX

A SQL command used to delete database objects such as tables, views, indexes, and users Syntax: DROP INDEX indexname

Statement-level trigger

A SQL trigger that is assumed if the FOR EACH ROW keywords are omitted. This type of trigger is executed once, before or after the triggering statement completes, and is the default case.

Schema

A logical grouping of database objects, such as tables, indexes, views, and queries, that are related to each other. Usually, a schema belongs to a single user or application.

Trigger

A procedural SQL code that is automatically invoked by the relational database management system when a data manipulation event occurs.

Cursor

A special construct used in procedural SQL to hold the data rows returned by a SQL query. A cursor may be considered a reserved area of memory in which query output is stored, like an array holding columns and rows. Cursors are held in a reserved memory area in the DBMS server, not in the client computer.

Static SQL

A style of embedded SQL in which the SQL statements do not change while the application is running.

Row-level trigger

A trigger that is executed once for each row affected by the triggering SQL statement. A row-level trigger requires the use of the FOR EACH ROW keywords in the trigger declaration.

View

A virtual table based on a SELECT query that is saved as an object in the database. Syntax: CREATE VIEW viewname AS SELECT query

CREATE TABLE name AS....

Allows user to use a SELECT command with attributes selected to create a new table. If a table with "name" already exists, MS Access will ask if you want the current "name" table to be deleted first.

Procedural Language SQL (PL/SQL)

An Oracle-specific programming language based on SQL with procedural extensions designed to run inside he Oracle database.

Dynamic SQL

An environment in which the SQL statement is not known in advance, but instead 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.

Sequence

An object for generating unique sequential values for a field. Syntax: CREATE SEQUENCE name [START WITH n] [INCREMENT BY n] [CACHE | NOCACHE] Where: • name is the name of the sequence. • n is an integer value that can be positive or negative. • START WITH specifies the initial sequence value. (The default value is 1.) • INCREMENT BY determines the value by which the sequence is incremented. (The default increment value is 1. The sequence increment can be positive or negative to enable you to create ascending or descending sequences.) • The CACHE or NOCACHE/NO CACHE clause indicates whether the DBMS will preallocate sequence numbers in memory. Oracle uses NOCACHE as one word and preallocates 20 values by default. SQL Server uses NO CACHE as two words. If a cache size is not specified in SQL Server, then the DBMS will determine a default cache size that is not guaranteed to be consistent across different databases.

Procedural Level Basic Data Types

CHAR Character values of a fixed length; for example: W_ZIP CHAR(5) VARCHAR2 Variable-length character values; for example: W_FNAME VARCHAR2(15) NUMBER Numeric values; for example: W_PRICE NUMBER(6,2) DATE Date values; for example: W_EMP_DOB DATE %TYPE Inherits the data type from a variable that you declared previously or from an attribute of a database table; for example: W_PRICE PRODUCT.P_PRICE%TYPE Assigns W_PRICE the same data type as the P_PRICE column in the PRODUCT table

Character Data Type

CHAR(L) Fixed-length character data for up to 255 characters. If you store strings that are not as long as the CHAR parameter value, the remaining spaces are left unused. Therefore, if you specify CHAR(25), strings such as Smith and Katzenjammer are each stored as 25 characters. However, a U.S. area code is always three digits long, so CHAR(3) would be appropriate if you wanted to store such codes. VARCHAR(L) or VARCHAR2(L) Variable-length character data. The designation VARCHAR2(25) or VARCHAR(25) will let you store characters up to 25 characters long. However, unlike CHAR, VARCHAR will not leave unused spaces. Oracle automatically converts VARCHAR to VARCHAR2.

ANSI SQL standard command to create database schema

CREATE SCHEMA AUTHORIZATION {creator};

Primary Key Attributes

Contain both a NOT NULL and UNIQUE specification, which enforce the entity integrity requirements. If the NOT NULL and UNIQUE specifications are not supported, use PRIMARY KEY without the specifications. (For example, if you designate the PK in MS Access, the NOT NULL and UNIQUE specifications are automatically assumed and are not spelled out.)

NOT NULL

Ensures that a column cannot have a NULL value

UNIQUE

Ensures that all values in a column are unique.

Adding multiple rows at a time

INSERT INTO target_tablename[(target_columnlist)] SELECT source_columnlist FROM source_tablename;

Explicit Cursor

In procedural SQL, a cursor created to hold the output of a SQL statement that may return two or more rows, but could return zero or only one row.

Numeric Data Type

NUMBER(L,D) or NUMERIC(L,D): The declaration NUMBER(7,2) or NUMERIC(7,2) indicates that numbers will be stored with two decimal places and may be up to seven digits long, including the sign and the decimal place (for example, 12.32 or −134.99). INTEGER: May be abbreviated as INT. Integers are (whole) counting numbers, so they cannot be used if you want to store numbers that require decimal places. SMALLINT: Like INTEGER but limited to integer values up to six digits. If your integer values are relatively small, use SMALLINT instead of INT. DECIMAL(L,D): Like the NUMBER specification, but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller ones are not. DECIMAL(9,2), DECIMAL(9), and DECIMAL are all acceptable.

Cursor Processing Commands

OPEN: Opening the cursor executes the SQL command and populates the cursor with data, opening the cursor for processing. The cursor declaration command only reserves a named memory area for the cursor; it does not populate the cursor with the data. Before you can use a cursor, you need to open it. For example: OPEN cursor_name FETCH: Once the cursor is opened, you can use the FETCH command to retrieve data from the cursor and copy it to the PL/SQL variables for processing. The syntax is: FETCH cursor_name INTO variable1 [, variable2, ...] The PL/SQL variables used to hold the data must be declared in the DECLARE section and must have data types compatible with the columns retrieved by the SQL command. If the cursor's SQL statement returns five columns, there must be five PL/SQL variables to receive the data from the cursor. This type of processing resembles the one-record-at-a-time processing used in previous database models. The first time you fetch a row from the cursor, the first row of data from the cursor is copied to the PL/SQL variables; the second time you fetch a row from the cursor, the second row of data is placed in the PL/SQL variables; and so on. CLOSE: The CLOSE command closes the cursor for processing.

Embedded SQL

SQL statements contained within application programming languages such as COBOL, C++, ASP, Java, and ColdFusion.

Persistent Storage Module (PSM)

SQL-99 introduced this.....A block of code with standard SQL statements and procedural extensions that is stored and executed at the DBMS server.

Additional Data types supported by SQL

TIME, TIMESTAMP, REAL, DOUBLE, and FLOAT, and intervals, such as INTERVAL DAY TO HOUR. Many RDBMSs have also expanded the list to include other types of data, such as LOGICAL, CURRENCY, and AutoNumber (Access).

COMMIT

The SQL command that permanently writes data changes to a database. Syntax: COMMIT

ALTER TABLE

The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics.

uses of ALTER TABLE

The basic syntax to add or modify columns is: ALTER TABLE tablename {ADD | MODIFY} (columnname datatype [{ADD | MODIFY} columnname datatype]); The ALTER TABLE command can also be used to add table constraints. In those cases, the syntax would be: ALTER TABLE tablename ADD constraint [ADD constraint]; You could also use the ALTER TABLE command to remove a column or table constraint. The syntax would be as follows: ALTER TABLE tablename DROP {PRIMARY KEY | COLUMN columnname | CONSTRAINT constraintname};

Reserve Words

Words used by a system that cannot be used for any other purpose. For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns.

Trigger Reminders

• 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. For example: • Triggers can be used to enforce constraints that cannot be enforced at the DBMS design and implementation levels. • Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action. In fact, one of the most common uses for triggers is to facilitate the enforcement of referential integrity. • Triggers can be used to update table values, insert records in tables, and call other stored procedures.

Stored Procedure Syntax points

• argument specifies the parameters that are passed to the stored procedure. A stored procedure could have zero or more arguments or parameters. • IN/OUT indicates whether the parameter is for input, output, or both. • data-type is one of the procedural SQL data types used in the RDBMS. The data types normally match those used in the RDBMS table creation statement. • Variables can be declared between the keywords IS and BEGIN. You must specify the variable name, its data type, and (optionally) an initial value.