Data Definition Statements
Data definition statements let you specify the characteristics of your database. When you execute data definition statements, Pervasive PSQL 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 PSQL allows you to construct data definition statements to do the following:
•
Create, modify, and delete tables.
•
Create and delete views.
•
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 PSQL Programmer's Guide
in the Developer Reference.
Creating, Modifying, and Deleting Tables
You can create, modify, and delete tables from a database by constructing SQL using the following statements:
Table 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 Views
You can create and delete views from a database by constructing SQL using the following statements:
Table 3
Data Definition Statements—Views
CREATE VIEW
Defines a new view.
DROP VIEW
Deletes a view.
Creating and Deleting Indexes
You can create and delete indexes from a database by constructing SQL using the following statements:
Table 4
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 5
Data Definition Statements—Triggers
CREATE TRIGGER
Defines a trigger for an existing table.
DROP TRIGGER
Deletes a trigger.
Pervasive PSQL 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 6
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 7
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 PSQL 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 8
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 PSQL allows you to create your own user-defined functions (UDF) and use them in Pervasive PSQL statements.
A user-defined function is a database object that encapsulates one or more Pervasive PSQL 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 PSQL 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 PSQL statement. A scalar UDF can contain multiple Pervasive PSQL 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 9
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.