Data Manipulation Statements
Data manipulation statements let you access and modify the contents of your database. Pervasive PSQL allows you to construct data manipulation statements to do the following:
•
Retrieve data from tables.
•
Modify data in tables.
•
Define transactions.
•
Create and delete views.
•
Execute stored procedures.
•
Execute triggers.
The following sections briefly describe the SQL statements associated with each of these tasks.
Retrieving Data
All statements you use to retrieve information from a database are based on the SELECT statement.
Table 10
Data Manipulation Statements—Retrieving Data
SELECT
Retrieves data from one or more tables in the database.
When you create a SELECT statement, you can use various clauses to specify different options. (See the entry for the SELECT statement in ODBC Engine Reference for detailed information about each type of clause.) The types of clauses you use in a SELECT statement are as follows:
Table 11
Data Manipulation Statements—Retrieving Data Options
FROM
Specifies the tables or views from which to retrieve data.
WHERE
Defines search criteria that qualify the data a SELECT statement retrieves.
GROUP BY
Combines sets of rows according to the criteria you specify and allows you to determine aggregate values for one or more columns in a group.
HAVING
Allows you to limit a view by specifying criteria that the aggregate values of a group must meet.
ORDER BY
Determines the order in which Pervasive PSQL returns selected rows.
In addition, you can use the UNION keyword to obtain a single result table from multiple SELECT queries.
Modifying Data
You can add, change, or delete data from tables and views by issuing statements such as the following:
Table 12
Data Manipulation Statements—Modifying Data
INSERT
Adds rows to one or more tables or a view.
UPDATE
Changes data in a table or a view.
DELETE
Deletes rows from a table or a view.
When you create a DELETE or UPDATE statement, you can use a WHERE clause to define search criteria that restrict the data upon which the statement acts.
Defining Transactions
To update the data in a database, you can issue SQL statements individually or you can define transactions (logical units of related statements). By defining transactions, you can ensure that either all the statements in a unit of work are executed successfully or none are executed. You can use transactions to group statements to ensure the logical integrity of your database.
Pervasive PSQL supports the ODBC API SQLTransact. See the Microsoft ODBC Programmer’s Reference for more information.
Creating and Deleting Views
You can create and delete views by constructing SQL using the following statements:
Table 13
Data Manipulation Statements—Views
CREATE VIEW
Defines a database view and stores the definition in the dictionary.
DROP VIEW
Deletes a view from the data dictionary.
Executing Stored Procedures
A stored procedure consists of statements that you can precompile and save in the dictionary. To execute stored procedures, construct statements using the following:
Table 14
Data Manipulation Statements- Stored Procedures
CALL or EXEC[UTE]
Recalls a previously compiled procedure and executes it.
Executing System Stored Procedures
A system stored procedure helps you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a psp_ prefix. To execute stored procedures, construct statements using the following:
Table 15
Data Manipulation Statements—System Stored Procedures
CALL or EXEC[UTE]
Recalls a system stored procedure and executes it.
For more details, see System Stored Procedures.
Executing Triggers
A trigger consists of statements you can precompile and save in the dictionary. Triggers are executed automatically by the engine when the specified conditions occur.