Data Control Language
To manipulate data, use the Data Control Language (DCL). With DCL, you can perform the following:
- CALL: Execute an SQL procedure.
- RETURN: Return a value from an SQL function.
- SET assignment: Assign a value to an SQL variable.
- SET PATH: Set or change the current path being used to locate the SQL objects in various schemas.
- SIGNAL: Raise an SQLState exception.
- VALUES: Invoke an SQL routine.
The CALL statement executes an SQL routine that is a procedure.
The Return statement returns a scalar value from an SQL expression. It can only be used within an SQL function.
You may use SET assignment statements for BEFORE triggers only. The SET assignment statement assigns a value to an SQL Trigger row correlation variable. The SET assignment statement is much like the set_clause of an SQL UPDATE statement.
The assignment_target consists of both, an SQL correlation variable of an SQL Trigger and a column_name. The column_name refers the column of the SQL correlation variable. You may use new or old row values.
With the SET PATH statement, you can use it to set or change the current path that you are using to locate the SQL objects in various schemas. This results in the setting of the CURRENT_PATH of a SQL session. To find the correct system tables, the schema POINTBASE must be included in the path.
This sets the CURRENT_PATH to the following schemas in the order specified: Employees, Engineering, Sales, and PointBase. If you wish to append the Marketing schema to the CURRENT_PATH so that the order becomes Employees, Engineering, Sales, PointBase, and Marketing, enter:
If you never execute a SET PATH statement, then the CURRENT_PATH consists of the schema POINTBASE, followed by your existing schema. When a SET PATH statement is issued, it completely replaces the existing CURRENT_PATH, unless CURRENT_PATH is part of the schemas being set in the path.
The order of the schemas in the path is generally crucial. When the database system is looking for SQL objects, it looks for them in each schema (unless explicitly referenced otherwise), starting with the first schema in the path, then the next, etc..., until an SQL object is found that meets the criteria. One way to override the CURRENT_PATH is to explicitly reference the SQL object. For example, to reference a table, you can specify schema_name.table_name. In the above examples, the SQL object of table_name would be searched in the schema of name schema_name.
With the SIGNAL statement, you can use it to raise an SQLSTATE exception. This statement can only be used within a trigger_body or within the body of an SQL routine, whose language type is SQL. This statement will cause an SQLSTATE exception to be thrown and propagated back to your program. You provide the text of the message.NOTE: The SIGNAL statement rolls back the specific event that activated its trigger and all the changes caused by the trigger, as well as the original SQL statement of the user, which includes all the triggers and cascading actions that it invoked.
The VALUES statement is an SQL stand alone SQL statement. It should not be confused with the values_clause of an INSERT statement or with the from_clause of an SQL Select statement.
Typically, the VALUES statement is used to invoke SQL routines. The VALUES statement discards all SQL expression values returned by either a constant, an SQL routine invocation, one of the SQL Scalar functions, one of the SQL Cast functions, or an SQL Special Register.