book

SQL Bible, Second Edition

Name: SQL Bible, Second Edition
ISBN: 9780470229064

by Alex Kriegel, Boris M. Trukhnov

April 2008

Intermediate to advanced

888 pages

19h 35m

English

Wiley

Read now

Unlock full access

Copyright
About the Authors
Contributing authors
Credits
Acknowledgments
Introduction
Whom This Book Is ForHow This Book Is OrganizedPart I: SQL Basic Concepts and PrinciplesPart II: Creating and Modifying Database ObjectsPart III: Data Manipulation and Transaction ControlPart IV: Retrieving and Transferring DataPart V: Implementing Security Using the System CatalogPart VI: Beyond SQL: Procedural Programming and Database Access MechanismsAppendixesConventions Used in This Book
I. SQL Basic Concepts and Principles
1. SQL and Relational Database Management Systems
1.1. Desirable Database Characteristics1.1.1. Sufficient capacity1.1.2. Adequate security and auditing1.1.3. Multiuser environment1.1.4. Effectiveness and searchability1.1.5. Scalability1.1.6. User friendliness1.2. Selecting Your Database Software1.2.1. Market share1.2.2. Total cost of ownership1.2.3. Support and persistence1.3. Major DBMS Implementations1.4. Real-Life Database Examples1.4.1. Order management system database1.4.2. Health care provider database1.4.3. Video sharing and editing database1.4.4. Scientific database1.4.5. Nonprofit organization database1.5. Database Legacy1.5.1. Flat file databases1.5.2. Hierarchical databases1.5.3. Network databases1.6. Relational Databases1.6.1. Tables1.6.2. Relationships1.6.3. Primary key1.6.4. Foreign key1.6.5. Invasion of RDBMS1.7. Other DBMS Models1.8. Brief History of SQL and SQL Standards1.8.1. Humble beginnings: RDBMS and SQL evolution1.8.2. A brief history of SQL standards1.8.2.1. SQL-86/87 (SQL1)1.8.2.2. SQL-89 (SQL1.1)1.8.2.3. SQL-92 (SQL2)1.8.2.4. SQL:1999 (SQL3)1.8.2.5. SQL:20031.8.2.6. SQL:20081.9. Summary
2. Fundamental SQL Concepts and Principles
2.1. Promises and Deliverables2.2. SQL: The First Look2.2.1. Database example2.2.2. Getting the data in and out2.2.3. Slice and dice: Same data, different angle2.2.4. Aggregation2.2.5. Data security2.2.5.1. Privileges2.2.5.2. Views2.2.6. Accessing data from a client application2.2.7. New developments2.3. Any Platform, Any Time2.4. Summary
3. SQL Data Types
3.1. No Strings Attached3.1.1. Character strings3.1.1.1. Fixed-length character strings3.1.1.2. Character strings of varying length3.1.1.3. National character strings3.1.1.4. SQL:20033.1.1.5. Oracle 11g3.1.1.6. DB2 9.53.1.1.7. Microsoft SQL Server 20083.1.1.8. Character string literals3.1.2. Binary strings3.1.2.1. SQL:20033.1.2.2. Oracle 11g3.1.2.3. DB2 9.53.1.2.4. Microsoft SQL Server 20083.1.2.5. Binary string literals3.2. In Numbers Strength3.2.1. Exact numbers3.2.1.1. SQL:20033.2.1.2. Oracle 11g3.2.1.3. DB2 9.53.2.1.4. Microsoft SQL Server 20083.2.1.5. Literals for exact numbers3.2.2. Approximate numbers3.2.2.1. SQL:20033.2.2.2. Oracle 11g3.2.2.3. DB2 9.53.2.2.4. Microsoft SQL Server 20083.2.2.5. Literals for approximate numbers3.3. Once Upon a Time: Date and Time Data Types3.3.1. Introduction to complex data types3.3.2. Date and time implementations3.3.2.1. SQL:20033.3.2.2. Oracle 11g3.3.2.3. DB2 9.53.3.2.4. Microsoft SQL Server 20083.3.2.5. Date and time literals3.3.2.6. Oracle 11g3.3.2.7. DB2 9.53.3.2.8. Microsoft SQL Server3.4. XML Data Type3.4.1. XML data type implementations3.4.1.1. SQL:2003 and SQL/XML:20063.4.1.2. Oracle 11g3.4.1.3. DB2 9.53.4.1.4. Microsoft SQL Server 20083.5. Constructed and User-Defined Data Types3.5.1. SQL:20033.5.1.1. Object-oriented data types3.5.1.1.1. Reference type3.5.1.1.2. Row type3.5.1.2. Collections3.5.1.2.1. ARRAY3.5.1.2.2. MULTISET3.5.2. Oracle 11g3.5.2.1. Object-oriented data types3.5.2.2. Collections3.5.2.2.1. Nested tables3.5.2.2.2. Varying arrays3.5.3. DB2 9.53.5.3.1. Structured types3.5.3.2. Reference types3.5.3.3. Distinct types3.5.4. Microsoft SQL Server 20083.5.4.1. Table type3.5.4.2. Alias type3.6. Other Data Types3.6.1. BOOLEAN3.6.2. ROWID3.6.3. UROWID3.6.4. BFILE3.6.5. DATALINK3.6.6. BIT3.6.7. TIMESTAMP3.6.8. SQL_VARIANT3.7. NULL3.8. Summary
II. Creating and Modifying Database Objects

4. Creating RDBMS Objects
4.1. Tables4.1.1. CREATE TABLE Statement4.1.1.1. SQL:2003 syntax4.1.1.2. Oracle 11 g syntax4.1.1.3. DB2 9.5 syntax4.1.1.4. Microsoft SQL 2008 syntax4.1.1.5. Permanent and temporary tables4.1.1.5.1. Temporary tables in SQL:20034.1.1.5.2. Temporary tables in Oracle 11 g4.1.1.5.3. Temporary tables in DB2 9.54.1.1.5.4. Temporary tables in Microsoft SQL Server 20084.1.1.6. Column definitions4.1.1.6.1. SQL:20034.1.1.7. Column constraints4.1.1.8. Column default values4.1.1.9. Column collating sequence4.1.1.10. Table constraints4.1.1.10.1. Referential integrity constraints4.1.1.10.2. Deferrable constraints4.1.1.11. ON COMMIT clause4.1.1.12. Physical properties clause4.1.1.12.1. Oracle 11 g4.1.1.12.2. DB2 9.54.1.1.12.3. Microsoft SQL Server 20084.1.1.13. Identity clause4.1.1.13.1. SQL:20034.1.1.13.2. Oracle 11 g4.1.1.13.3. DB2 9.54.1.1.13.4. Microsoft SQL Server 20084.1.1.14. Creating a new table as a copy of another table4.1.1.14.1. SQL:20034.1.1.14.2. Oracle 11 g4.1.1.14.3. DB2 9.54.1.1.14.4. Microsoft SQL Server 20084.2. Indexes4.2.1. CREATE INDEX statement4.2.1.1. Oracle 11 g4.2.1.1.1. Function-based indexes4.2.1.1.2. Bitmap indexes4.2.1.1.3. Physical storage clause4.2.1.2. DB2 9.54.2.1.3. Microsoft SQL Server 20084.3. Views4.3.1. CREATE VIEW statement4.3.1.1. SQL:20034.3.1.1.1. Column names4.3.1.1.2. SELECT statement and updatable views4.3.1.1.3. View constraints4.3.1.2. Oracle 11 g4.3.1.3. DB2 9.54.3.1.4. Microsoft SQL Server 20084.3.2. Creating complex views4.3.2.1. Join view with GROUP BY clause and aggregate function4.3.2.2. View based on another view example4.3.2.3. View with UNION example4.3.2.4. View with subquery4.4. Aliases and Synonyms4.4.1. SQL:20034.4.2. Oracle 11 g CREATE SYNONYM statement4.4.2.1. Public versus private synonyms4.4.2.2. Types of objects for which you can create synonyms4.4.2.3. Creating synonyms for remote database objects4.4.2.4. CREATE SYNONYM examples4.4.3. DB2 9.5 CREATE ALIAS statement4.4.4. Microsoft SQL Server 2008 CREATE SYNONYM statement4.5. Schemas4.5.1. CREATE SCHEMA statement4.5.1.1. SQL:20034.5.1.2. Oracle 11 g4.5.1.3. DB2 9.54.5.1.4. Microsoft SQL Server 20084.6. Sequences4.6.1. External sequence generators in SQL:20034.6.2. Sequences in Oracle 11 g4.6.2.1. Creating ascending and descending sequences4.6.2.2. Creating cycling sequences4.6.2.3. Caching sequence values4.6.2.4. Guaranteeing the order of sequence values4.6.2.5. Accessing sequences in Oracle4.6.3. DB2 9.54.6.3.1. Accessing sequences in DB24.7. Other SQL:2003 and Implementation-Specific Objects4.7.1. Domains (SQL:2003)4.7.2. Character sets (SQL:2003)4.7.3. Collations (SQL:2003)4.7.4. Tablespaces and filegroups4.7.4.1. Oracle 11 g4.7.4.2. DB2 9.54.7.4.3. Microsoft SQL Server 20084.7.5. Materialized views (Oracle 11 g)4.7.5.1. Materialized view refreshed on demand4.7.5.2. Materialized view refreshed periodically with no user interaction4.7.6. Database links (Oracle 11 g)4.7.6.1. CREATE DATABASE LINK statement4.7.7. Directories (Oracle 11 g)4.8. CREATE Statement Cross-Reference4.9. Summary
5. Altering and Destroying RDBMS Objects
5.1. Tables5.1.1. ALTER TABLE statement5.1.1.1. SQL:20035.1.1.2. Oracle 11 g5.1.1.2.1. Changing physical attributes5.1.1.2.2. Adding columns5.1.1.2.3. Modifying existing columns5.1.1.2.4. Removing table columns5.1.1.2.5. Modifying constraints5.1.1.2.6. Creating new constraints5.1.1.2.7. Removing constraints5.1.1.2.8. Disabling and enabling constraints5.1.1.2.9. Renaming a table5.1.1.3. DB2 9.55.1.1.3.1. Adding new columns to a table5.1.1.3.2. Modifying existing columns5.1.1.3.3. Modifying identity column options5.1.1.3.4. Creating new constraints on an existing table5.1.1.3.5. Removing constraints5.1.1.3.6. Altering summary table options5.1.1.4. Microsoft SQL Server 20085.1.1.4.1. Adding new columns to a table5.1.1.4.2. Modifying existing columns5.1.1.4.3. Removing table columns5.1.1.4.4. Creating and removing constraints5.1.1.4.5. Disabling and enabling constraints5.1.2. DROP TABLE statement5.1.2.1. SQL:20035.1.2.2. Oracle 11 g5.1.2.3. DB2 9.55.1.2.4. Microsoft SQL Server 20085.2. Indexes5.2.1. ALTER INDEX statement5.2.1.1. Oracle 11 g5.2.1.2. Renaming indexes5.2.1.3. Rebuilding indexes into a different tablespace5.2.1.4. DB2 9.55.2.1.5. Microsoft SQL Server 20085.2.2. DROP INDEX statement5.2.2.1. Oracle 11 g5.2.2.2. DB2 9.55.2.2.3. Microsoft SQL Server 20085.3. Views5.3.1. ALTER VIEW statement5.3.1.1. Oracle 11 g5.3.1.2. DB2 9.55.3.1.3. Microsoft SQL Server 20085.3.2. DROP VIEW statement5.3.2.1. SQL:20035.3.2.2. Oracle 11 g5.3.2.3. DB2 9.55.3.2.4. Microsoft SQL Server 20085.4. Aliases and Synonyms5.4.1. Oracle 11 g5.4.2. DB2 9.55.4.3. Microsoft SQL Server 20085.5. Schemas5.5.1. SQL:20035.5.2. DB2 9.55.5.3. Microsoft SQL Server 20085.6. Sequences5.6.1. ALTER SEQUENCE statement5.6.1.1. SQL:20035.6.1.2. Oracle 11 g5.6.1.3. DB2 9.55.6.2. DROP SEQUENCE statement5.6.2.1. SQL:20035.6.2.2. Oracle 11 g5.6.2.3. DB2 9.55.7. Other Implementation-Specific Objects5.7.1. Domains (SQL:2003)5.7.2. Character sets (SQL:2003)5.7.3. Collations (SQL:2003)5.7.4. Tablespaces5.7.4.1. ALTER TABLESPACE statement5.7.4.1.1. Oracle 11 g5.7.4.1.2. DB2 9.55.7.4.2. DROP TABLESPACE statement5.7.4.2.1. Oracle 11 g5.7.4.2.2. DB2 9.55.7.5. Materialized views (Oracle 11 g)5.7.6. Database Links (Oracle 11 g)5.7.7. Directories (Oracle 11 g)5.8. ALTER and DROP Statements Cross-Reference5.9. Summary
III. Data Manipulation and Transaction Control
6. Data Manipulation Language (DML)
6.1. INSERT: Populating Tables with Data6.1.1. Common INSERT statement clauses6.1.1.1. Inserting values for specified columns6.1.1.2. Inserting values for all columns6.1.1.3. Inserting NULL and default values explicitly6.1.1.4. Inserting values selected from other tables6.1.1.5. INSERT statement and integrity constraints6.1.2. INSERT statement vendor-related specifics6.1.2.1. SQL:20036.1.2.2. Oracle 11g6.1.2.3. DB2 9.56.1.2.4. Microsoft SQL Server 20086.2. UPDATE: Modifying Table Data6.2.1. Common UPDATE statement clauses6.2.1.1. Updating a single column of a single row6.2.1.2. Updating multiple columns6.2.1.3. Updating a column in all rows6.2.1.4. Updating columns using a single-row subquery6.2.1.4.1. Deriving the assignment value from another value6.2.1.4.2. Update with correlated subquery6.2.1.5. UPDATE statement and integrity constraints6.2.2. Vendor-specific UPDATE statement details6.2.2.1. SQL:20036.2.2.2. Oracle 11g6.2.2.3. DB2 9.56.2.2.4. Microsoft SQL Server 20086.3. DELETE: Removing Data from Tables6.3.1. Common DELETE statement clauses6.3.1.1. DELETE statement and integrity constraints6.3.1.2. Using subqueries in a DELETE statement WHERE clause6.3.2. Vendor-specific DELETE statement clauses6.3.2.1. Oracle 11g6.3.2.2. Microsoft SQL Server 20086.4. MERGE: Combining INSERT, UPDATE, and DELETE in One Statement6.4.1. Common MERGE statement clauses6.4.2. Vendor-specific MERGE statement clauses6.4.2.1. Oracle 11g6.4.2.2. DB2 9.56.4.2.3. Microsoft SQL Server 20086.5. TRUNCATE Statement6.5.1. Differences between Oracle and Microsoft SQL Server TRUNCATE statements6.6. Summary
7. Sessions, Transactions, and Locks
7.1. Sessions7.2. Transactions7.2.1. What is a transaction?7.2.2. Transactions COMMIT and ROLLBACK7.2.3. Transaction isolation levels7.3. Understanding Locks7.3.1. Locking modes7.3.2. Dealing with deadlocks7.4. Summary
IV. Retrieving and Transferring Data
8. Understanding the SELECT Statement
8.1. Single Table SELECT Statement Syntax8.2. SELECT Clause: What Do You Select?8.2.1. Single-column select8.2.2. Multicolumn SELECT8.2.2.1. Selecting several columns8.2.2.2. Selecting all columns8.2.2.3. Selecting all columns plus an extra column8.2.2.4. Selecting distinct values8.2.3. Using literals, functions, and calculated columns8.2.3.1. Dummy tables8.2.3.1.1. SQL:20038.2.3.1.2. Oracle 11g8.2.3.1.3. DB2 9.58.2.3.1.4. Microsoft SQL Server 20088.2.3.2. Column aliases8.2.3.3. Literals, functions, and expressions8.2.4. Using subqueries in a SELECT clause8.3. FROM Clause: Select from What?8.3.1. Selecting from tables and views8.3.2. Using aliases in a FROM clause8.3.3. Using subqueries in a FROM clause (inline views)8.4. WHERE Clause: Setting Horizontal Limits8.4.1. Using comparison operators8.4.2. Compound operators: Using AND and OR8.4.3. Using the BETWEEN operator8.4.4. Using the IN operator: Set membership test8.4.5. The NOT operator8.4.6. Using the IS NULL operator: Special test for NULLS8.4.7. Using subqueries in a WHERE clause8.4.7.1. Nested subqueries8.5. GROUP BY and HAVING Clauses: Summarizing Results8.6. ORDER BY Clause: Sorting Query Output8.7. Combining the Results of Multiple Queries8.7.1. UNION8.7.2. INTERSECT8.7.3. EXCEPT (MINUS)8.8. SQL Analytic Functions and Top N Queries8.8.1. Analytic functions and the SQL:2003 standard8.8.2. Designing top N queries8.8.3. Limit N queries8.9. Summary
9. Multitable Queries
9.1. Inner Joins9.1.1. Two syntaxes for inner joins9.1.1.1. Inner join options9.1.1.2. ON and USING clauses9.1.1.3. Old syntax9.1.2. Equijoin9.1.2.1. SQL:2003 standard syntax9.1.2.2. Natural join9.1.2.3. Old syntax9.1.3. Nonequijoin9.1.4. Self-join9.1.4.1. SQL:2003 standard syntax9.1.4.2. Old syntax9.1.5. Cross join (Cartesian product)9.1.5.1. SQL:2003 standard syntax9.1.5.2. Old syntax9.1.6. Joining more than two tables9.1.6.1. Number of joins9.1.6.2. SQL:2003 standard syntax9.1.6.3. Old syntax9.2. Outer Joins: Joining Tables on Columns Containing NULL Values9.2.1. Two syntaxes for outer joins9.2.1.1. SQL:20039.2.1.2. Old syntax9.2.1.2.1. Oracle 11 g9.2.1.2.2. Microsoft SQL Server 20089.2.2. Left outer join9.2.2.1. SQL:2003 standard syntax9.2.2.2. Old syntax9.2.2.2.1. Oracle 11 g9.2.2.2.2. DB2 9.59.2.2.2.3. Microsoft SQL Server 20089.2.3. Right outer join9.2.3.1. SQL:2003 standard syntax9.2.3.2. Old syntax9.2.3.2.1. Oracle 11 g9.2.4. Full outer join9.2.5. Union join9.3. Joins Involving Inline Views9.4. Multitable Joins with Correlated Queries9.5. Improving Efficiency of Multitable Queries9.6. Summary
10. SQL Functions
10.1.10.1.1. Numeric functions10.1.1.1. CEIL10.1.1.2. ROUND10.1.1.3. TRUNC10.1.1.4. RAND10.1.1.5. SIGN10.1.2. String functions10.1.2.1. CONCAT10.1.2.2. CHARINDEX, INSTR, LOCATE, and POSSTR10.1.2.3. SUBSTR and SUBSTRING10.1.2.4. LENGTH10.1.2.5. LOWER and UPPER10.1.2.6. TO_CHAR, CHAR, and STR10.1.2.7. REPLACE10.1.2.8. REPLICATE and REPEAT10.1.2.9. TRANSLATE10.1.2.10. TRIM, LTRIM, and RTRIM10.1.3. Date and time functions10.1.3.1. GETDATE, SYSDATE, and CURRENT DATE10.1.3.1.1. Time zone functions10.1.3.2. ADD_MONTHS, DATEADD, and date arithmetic10.1.3.3. EXTRACT and DATEPART10.1.3.4. DAYNAME, MONTHNAME, and DATENAME10.1.3.5. MONTHS_BETWEEN and DATEDIFF10.1.4. Aggregate functions10.1.4.1. SUM10.1.4.2. COUNT10.1.4.3. AVG10.1.4.4. MIN and MAX10.1.5. Conversion functions10.1.5.1. Oracle 11 g CONVERT, TRANSLATE...USING, and UNISTR10.1.5.1.1. Data type–specific conversion functions10.1.6. System Functions10.1.7. Miscellaneous functions10.1.7.1. DECODE and CASE10.1.7.2. COALESCE and NULLIF10.1.7.3. NVL, NVL2, and ISNULL10.1.8. User-defined functions10.2. Summary
11. SQL Operators
11.1. Arithmetic and String Concatenation Operators11.2. Logical Operators11.2.1. ALL11.2.2. ANY | SOME11.2.3. BETWEEN Inexpression> AND <expression>11.2.4. IN11.2.5. EXISTS11.2.6. LIKE11.2.7. AND11.2.8. NOT11.2.9. OR11.3. Operator Precedence11.4. Assignment Operator11.5. Comparison Operators11.6. Bitwise Operators11.7. Summary
V. Implementing Security Using the System Catalog
12. SQL and RDBMS Security
12.1. Basic security mechanisms12.1.1. Identification and authentication12.1.2. Authorization and access control12.1.3. Encryption12.1.4. Integrity and consistency12.1.5. Auditing12.2. Defining a Database User12.3. Managing Security with Privileges12.3.1. GRANT statement12.3.1.1. Granting System-Level Privileges12.3.1.2. Granting Object-Level Privileges12.3.2. REVOKE privileges12.3.2.1. Revoking System-Level Privileges12.3.2.2. Revoking Object-Level Privileges12.4. Managing Security with Roles12.5. Using Views for Security12.6. Using Constraints for Security12.7. Using Stored Procedures and Triggers for Security12.7.1. Data encryption12.8. Database Auditing12.9. Security Standards12.9.1. International security standards12.9.2. Regulatory compliance12.10. Summary
13. The System Catalog and INFORMATION_SCHEMA
13.1. SQL System Catalogs13.2. Oracle 11 g Data Dictionary13.2.1. Oracle data dictionary structure13.2.2. Oracle data dictionary and SQL:2007 standards13.2.3. One level deeper: Data about metadata13.3. IBM DB2 9.5 System Catalogs13.3.1. The INFORMATION_SCHEMA objects in DB213.3.2. Obtaining information about INFORMATION_SCHEMA objects13.4. Microsoft SQL Server 2008 System Catalog13.4.1. MS SQL Server 2008 INFORMATION_SCHEMA Views13.4.2. Microsoft SQL Server system stored procedures13.4.2.1. sp_tables13.4.2.2. sp_columns13.4.2.3. sp_server_info13.4.2.4. sp_databases13.4.3. Microsoft SQL Server 2008 system functions13.4.3.1. Configuration functions13.4.3.2. Metadata functions13.4.3.3. System functions13.5. Summary
VI. Beyond SQL: Procedural Programming and Database Access Mechanisms
14. Stored Procedures, Triggers, and User-Defined Functions
14.1. Procedural Extension Uses and Benefits14.1.1. Performance and network traffic14.1.2. Database security14.1.3. Code reusability14.2. Key Elements of a SQL Procedural Language14.2.1. Variables and assignment14.2.1.1. PL/SQL (Oracle)14.2.1.2. IBM DB2 9.514.2.1.2.1. Transact-SQL (Microsoft SQL Server)14.2.2. Modularity, subprograms, and block structure14.2.3. Passing parameters14.2.3.1. PL/SQL14.2.3.1.1. Transact-SQL14.2.3.2. IBM DB2 SQL PL14.3. Oracle 11 g14.4. DB2 9.514.5. Microsoft SQL Server 200814.5.1. Conditional execution14.5.1.1. PL/SQL14.5.1.2. IBM DB2 9.514.5.1.2.1. Transact-SQL14.5.2. Repeated execution14.5.3. Cursors14.5.3.1. DECLARE14.5.3.2. OPEN14.5.3.3. FETCH14.5.3.4. CLOSE14.5.3.4.1. Cursor examples14.5.4. Error handling14.5.4.1. PL/SQL14.5.4.2. IBM DB2 9.514.5.4.2.1. Transact-SQL14.6. Stored Procedures14.6.1. CREATE PROCEDURE syntax14.6.2. Creating a simple stored procedure14.6.2.1. IBM DB2 9.514.6.2.1.1. Microsoft SQL Server 200814.6.3. Removing a stored procedure14.7. User-Defined Functions14.7.1. CREATE FUNCTION syntax14.7.2. Creating a simple function14.7.2.1. PL/SQL14.7.2.2. IBM DB2 SQL PL14.7.2.2.1. Transact-SQL14.7.3. Removing a user-defined function14.8. Triggers14.8.1. CREATE TRIGGER syntax14.8.1.1. SQL:200314.8.1.2. PL/SQL14.8.1.3. IBM DB2 SQL PL14.8.1.3.1. Transact-SQL14.8.2. Removing a trigger14.9. .NET Stored Procedures and Functions14.10. Summary
15. SQL and XML
15.1.15.1.1. Introduction15.1.2. The Structure of XML15.1.2.1. Formatted XML15.1.2.2. DTD and Schema15.1.2.2.1. Namespaces15.1.3. XML as a data source15.1.3.1. Accessing XML documents in an application15.1.3.2. XPath15.1.3.3. XQuery15.1.4. Encoding XML15.1.4.1. Entities15.1.4.2. CDATA15.1.5. Presenting XML documents15.1.5.1. XSL and XSLT15.1.6. XML and RDBMS15.1.7. Oracle 11 g XML DB15.1.8. IBM DB 9.5 pureXML15.1.9. Microsoft SQL Server 200815.1.9.1. SQL Server 200015.1.9.2. XML DML15.2. Summary
16. SQL and Procedural Programming
16.1. SQL Statement Processing Steps16.2. Embedded and Dynamic SQL16.2.1. Embedded SQL16.2.1.1. Embedded SQL basic elements16.2.1.1.1. Hoast variables16.2.1.1.2. Database connection16.2.1.1.3. Data retrieval and SELECT INTO statement16.2.1.1.4. Handling errors16.2.1.2. Common embedded SQL statements16.2.1.3. A typical embedded SQL program flow16.2.2. Dynamic SQL techniques16.2.2.1. Two varieties of dynamic SQL16.2.2.2. Dynamic SQL and SQL:2003 standards16.2.2.3. Dynamic SQL basic elements16.2.2.3.1. Dynamic DML and DDL16.2.2.3.2. Dynamic queries16.2.3. The future of Embedded and Dynamic SQL16.3. SQL/CLI Standard16.4. Open Database Connectivity and Object Linking and Embedding, Database16.4.1. ODBC16.4.2. OLEDB16.5. SQL and Java16.5.1. Java Database Connectivity (JDBC)16.5.1.1. JDBC-ODBC drivers (Type 1)16.5.1.2. Native API Partly Java technology-enabled drivers (Type 2)16.5.1.3. Net-Protocol Fully Java technology-enabled drivers (Type 3)16.5.1.4. Native-Protocol drivers (Type 4)16.5.2. SQLJ16.6. Oracle API options16.6.1. Oracle Call Interface16.6.2. Oracle Objects for OLE16.6.3. Oracle Data Provider for .NET16.7. IBM DB2 Call-Level Interface16.8. Microsoft Data Access Interfaces16.8.1. ActiveX Data Objects16.8.2. ADO.NET16.9. Summary
17. The Future of SQL
17.1. OLAP and Business Intelligence17.1.1. Oracle 11 g17.1.2. IBM DB2 9.517.1.3. Microsoft SQL Server 200817.2. LINQ to SQL17.3. Objects17.3.1. OOP Paradigm17.3.1.1. Objects and classes17.3.1.2. Encapsulation17.3.1.3. Inheritance17.3.1.4. Polymorphism17.3.1.5. Identity17.3.2. Object Language Bindings (SQL/OLB) in SQL:2003 Standard17.3.3. Oracle 11 g support17.3.4. IBM DB2 9.5 support17.3.5. Microsoft SQL Server 200817.3.6. Abstract data types17.3.7. Object-oriented databases17.4. Summary
A. Accompanying Website
B. The ACME Sample Database
B.1. General Information and Business RulesB.2. Naming ConventionsB.3. Relationships between TablesB.4. Column Constraints and DefaultsB.5. IndexesB.6. ViewsB.7. SQL Scripts to Create ACME Database Objects
C. Basics of Relational Database Design
C.1. Identifying Entities and AttributesC.2. NormalizationC.2.1. First normal formC.2.2. Second normal formC.2.3. Third normal formC.3. Specifying ConstraintsC.4. OLTP versus OLAP DesignsC.4.1. Data warehouses and data martsC.4.2. Star and snowflake schemasC.5. Pitfalls of Relational Database Design
D. Installing RDBMS Software
D.1. Installing Oracle 11gD.1.1. Installing Oracle 11g release 1 (11.1.0.6.0) Enterprise Edition on LinuxD.1.1.1. Pre-installation tasks to perform as rootD.1.1.2. Pre-installation tasks to perform as user oracleD.1.1.3. Installation instructionsD.1.1.4. Testing Oracle 11g installationD.1.1.5. Uninstalling Oracle 11gD.1.2. Installing Oracle 11g release 1 (11.1.0.6.0) Personal Edition on WindowsD.1.2.1. Installation instructionsD.1.2.2. Testing Oracle 11g InstallationD.1.2.3. Starting and stopping Oracle 11gD.2. Installing IBM DB2 9.5D.2.1. Installing DB2 9.5 software on WindowsD.2.1.1. System requirementsD.2.1.2. Pre-installation tasksD.2.1.3. Installation instructionsD.2.1.4. Postinstallation tasksD.2.1.5. Uninstalling DB2 9.5D.2.1.6. Starting and stopping DB2 9.5D.3. Installing Microsoft SQL Server 2008D.3.1. Prerequisites for the Microsoft SQL Server 2008 (CTP, November 2007 release)D.3.1.1. Minimum system requirementsD.3.2. Installing on Windows Vista Enterprise EditionD.3.2.1. Installation instructionsD.3.2.2. Postinstallation tasksD.3.2.3. Starting and Stopping Microsoft SQL Server 2008
E. Accessing RDBMS
E.1. Using ORACLE 11g Utilities to Access RDBMSE.1.1. SQL*PlusE.1.2. SQL DeveloperE.1.2.1. Starting SQL DeveloperE.1.2.2. Creating a database connectionE.1.2.3. Working with SQL WorksheetE.1.3. Oracle Enterprise Manager (SQL Worksheet)E.2. Using IBM DB2 9.5 Utilities to Access the RDBMSE.2.1. Command-Line Processor (CLP)E.2.1.1. Interactive modeE.2.1.2. Command modeE.2.1.3. Batch modeE.2.2. Command EditorE.3. Using Microsoft SQL Server 2008 Utilities to Access the RDBMSE.3.1. Using SQLCMDE.3.2. Using SQL Server Management Studio
F. Installing the ACME Database
F.1. Installing the ACME Database on Oracle 11g Using SQL*PlusF.1.1. Linux / UnixF.1.2. Microsoft WindowsF.2. Installing the ACME Database on DB2 9.5 (Windows) Using CLPF.3. Installing the ACME Database on Microsoft SQL Server 2008 Using the SQLCMD Utility
G. SQL Functions
H. SQL Syntax Reference
H.1. DDL StatementsH.1.1. TablesH.1.2. IndexesH.1.3. ViewsH.1.4. SchemasH.1.5. Stored proceduresH.1.6. User-defined functionsH.1.7. TriggersH.2. DCL StatementsH.3. DML StatementsH.4. DQL StatementsH.4.1. Single-table SELECTH.4.2. Multitable SELECTH.5. Transactional Control StatementsH.6. Predicates
I. SQL-Reserved Keywords
I.1. Future KeywordsI.2. ODBC Reserved Keywords
J. The Other RDBMSs
K. A Brief Introduction to Number Systems, Boolean Algebra, and Set Theory
K.1. The Number SystemsK.1.1. The RDBMS connectionK.1.2. Converting numbersK.1.2.1. Binary to decimal conversionK.1.2.2. Decimal to binary conversionK.2. Logic Elements of Boolean AlgebraK.2.1. NOT (complement or inverter)K.2.2. AND (Boolean product)K.2.3. OR (Boolean sum)K.2.4. XOR (exclusive OR)K.2.5. NAND (inversed AND)K.2.6. NOR (inversed OR)K.2.7. Rules of precedenceK.3. Set TheoryK.3.1. The listing of setsK.3.2. SubsetsK.3.3. Set equalityK.3.4. Operations on setsK.3.4.1. UNIONK.3.4.2. INTERSECTIONK.3.4.3. COMPLEMENTK.3.4.4. DIFFERENCEK.3.4.5. CARTESIAN PRODUCTK.3.4.6. Multiple operationsK.3.4.7. Set cardinalityK.3.5. Identities of set algebra

Content preview from SQL Bible, Second Edition

Chapter 14. Stored Procedures, Triggers, and User-Defined Functions

As we have already mentioned, SQL is a nonprocedural programming language by definition. As such, it lacks procedural constructs, specifically, the ability to perform operations in (optionally named) hierarchical logical blocks that can accept and return values, perform iterations, execute conditional statements, and so on.

Note

Most programming languages are procedural. A procedural program is essentially a list of step-by-step instructions that tell the computer what to do. For example, a procedural program tells a computer to repeatedly read user's input, multiply it by some predefined constant, and store the result in a database table. A procedural program can also have error handling. For example, if the value is nonnumeric an error message will be displayed, but the program will continue. Also, instead of storing the result in a table, a procedural program can pass it to a different program (subprogram) that, in its turn, might perform more calculations and pass the results to yet another programming module, and so on.

The first RDBMS implementations did not have procedural language capabilities — all procedural data processing was done using embedded programming. All major procedural languages that were popular back then (C, COBOL, Pascal, and so on) had (and still have) special extensions (precompilers) that allowed the programmer to embed SQL statements directly into programming language code. The work of ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470229064Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Bible, Second Edition

by Alex Kriegel, Boris M. Trukhnov

Chapter 14. Stored Procedures, Triggers, and User-Defined Functions

Note

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.