DDL Statements

This section provides BNF notation for DDL statements.

Tables

The notations below are to create, modify, and drop database tables, respectively:

CREATE TABLE <table_name> (  column_name <datatype> [<column_constraint>,...]                         [DEFAULT <default_value>],...  [<table_constraint>,...]  [physical_options] ) 
ALTER TABLE <table_name> { <vendor_specific_add_column_clause> |   <vendor_specific_alter_column_clause> |   <vendor_specific_add_constraint_clause> |   <vendor_specific_drop_constraint_clause> } 
Note 

ALTER TABLE statement clauses vary for different implementations and can hardly be generalized. See Chapter 5 for more information.

DROP TABLE <table_name>

Indexes

The following two notations are to create and drop database indexes:

CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column_name> [ASC|DESC],...) 
DROP INDEX <index_name>

Views

The notations below are to create, modify, and drop database views, respectively:

CREATE VIEW <view_name> [(column_name,...)] AS <select_statement> [WITH CHECK OPTION] 
ALTER VIEW <view_name> <vendor_specific_alter_view_clause> 
DROP VIEW <view_name>

Schemas

The following two notations are to create and to drop database schemas:

CREATE SCHEMA <schema_name>  AUTHORIZATION <authorization_id> <create_object_statement>,... <grant_privilege_statement>,... 
Note 

In Oracle, the schema_name token is invalid. You can create schemas in Oracle in your own schema only, and only with your own authorization_id.

DROP SCHEMA <schema_name> RESTRICT
Note 

The foregoing syntax is for DB2 only; Oracle and MS SQL Server don't have DROP SCHEMA statements in their syntaxes.

Stored procedures

The BNF notation to create a stored procedure follows:

CREATE PROCEDURE <procedure_name> [<parameter_section>] <procedure_definition>
Note 

The preceding specification is generic; the actual syntax is implementation-specific. Refer to vendor-specific documentation for details.

The following notation is to drop a stored procedure:

DROP PROCEDURE <procedure_name>

User-defined functions

The BNF notation to create a user-defined function follows:

CREATE FUNCTION <function_name> <function_definition_includes_return_statement>
Note 

The preceding specification is generic; the actual syntax is implementation-specific. Refer to vendor-specific documentation for details.

The following notation is to drop a user-defined function:

DROP FUNCTION <function_name> 

Triggers

The BNF notation to create a trigger follows:

CREATE TRIGGER <trigger_name> [BEFORE | AFTER]  {INSERT | UPDATE | DELETE} ON <table_name> [FOR EACH ROW] <trigger_body>
Note 

The preceding syntax describes only basic trigger functionality; the actual implementations have more options.

The following notation is to drop a trigger:

DROP TRIGGER [qualifier.]<trigger_name>




SQL Bible
Microsoft SQL Server 2008 Bible
ISBN: 0470257040
EAN: 2147483647
Year: 2005
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net