Data Definition Statements

Data definition statements let you specify the characteristics of your database. When you execute data definition statements, Pervasive.SQL stores the description of your database in a data dictionary. You must define your database in the dictionary before you can store or retrieve information.

Pervasive.SQL allows you to construct data definition statements to do the following:

Create, modify, and delete tables.

Create and delete indexes.

Create and delete triggers.

Create and delete stored procedures.

Create and delete user-defined functions.

The following sections briefly describe the SQL statements associated with each of these tasks. For general information about defining the characteristics of your database, refer to the Pervasive.SQL Programmer's Guide, available in the Pervasive.SQL Software Developer Kit (SDK).

Creating, Modifying, and Deleting Tables

You can create, modify, and delete tables from a database by constructing SQL using the following statements:

Table 1-2 Data Definition Statements-Tables


CREATE TABLE	Defines a table and optionally creates the corresponding data file.
ALTER TABLE	Changes a table definition. With an ALTER TABLE statement, you can perform such actions as add a column to the table definition, remove a column from the table definition, change a column's data type or length (or other characteristics), and add or remove a primary key or a foreign key and associate the table definition with an different data file.
DROP TABLE	Deletes a table from the data dictionary and optionally deletes the associated data file from the disk.

Creating and Deleting Indexes

You can create and delete indexes from a database by constructing SQL s using the following statements:

Table 1-3 Data Definition Statements-Indexes


CREATE INDEX	Defines a new index (a named index) for an existing table.
DROP INDEX	Deletes a named index.

Creating and Deleting Triggers

You can create and delete triggers from a database by constructing SQL using the following statements:

Table 1-4 Data Definition Statements-Triggers


CREATE TRIGGER	Defines a trigger for an existing table.
DROP TRIGGER	Deletes a trigger.

Pervasive.SQL provides additional SQL control statements, which you can only use in the body of a trigger. You can use the following statements in triggers:

Table 1-5 Data Definition Statements-Trigger Control


BEFORE	Defines the trigger execution before the INSERT, UPDATE, or DELETE operation.
AFTER	Defines the trigger execution after the INSERT, UPDATE, or DELETE operation.

Creating and Deleting Stored Procedures

A stored procedure consists of statements you can precompile and save in the dictionary. To create and delete stored procedures, construct statements using the following:

Table 1-6 Data Definition Statements-Stored Procedure


CREATE PROCEDURE	Stores a new procedure in the data dictionary.
DROP PROCEDURE	Deletes a stored procedure from the data dictionary.

Pervasive.SQL provides additional SQL control statements, which you can only use in the body of a stored procedure. You can use the following statements in stored procedures:

Table 1-7 Data Definition Statements-Stored Procedure Control


IF...THEN...ELSE	Provides conditional execution based on the truth value of a condition.
LEAVE	Continues execution by leaving a block or loop statement.
LOOP	Repeats the execution of a block of statements.
WHILE	Repeats the execution of a block of statements while a specified condition is true.

Creating and Deleting User-Defined Functions (UDF)

In addition to the built-in functions, Pervasive.SQL allows you to create your own user-defined functions (UDF) and use them in Pervasive.SQL statements.

A user-defined function is a database object that encapsulates one or more Pervasive.SQL statements that can be reused. A user-defined function takes zero or more input arguments and evaluates a return value, which is a scalar value.

User-defined functions are always defined within the context of a database. Successful execution of this statement results in the storing of the UDF definition in the specific database. Once stored, the UDF can be modified, invoked, and deleted.

Pervasive.SQL supports scalar user-defined functions.

A scalar user-defined function returns a single value of the data type specified in the RETURNS clause of the Pervasive.SQL statement. A scalar UDF can contain multiple Pervasive.SQL statements. You can specify any data type value for the returned data except text, ntext, image, cursor, or timestamp.

To create and delete user-defined functions, construct statements using the following:

Table 1-8 Data Definition Statements-User-Defined Function


CREATE FUNCTION	Creates a scalar user-defined function in the database.
DROP FUNCTION	Deletes a scalar user-defined function from the database.