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.

CALL

CALL procedure_name([argument_list])

The CALL statement executes an SQL routine that is a procedure.

Syntax

CALL	The CALL keyword is required in a CALL statement.
procedure_name	The procedure_name is the name of the procedure which is executed. No results are returned.
argument_list	The optional argument_list clause specifies values for the CALL statement. NOTE: Only constants can be used. You cannot use new or old row values.

Examples

CALL PROC1();
CALL PROC2(`abc');

RETURN

RETURN SQL_expression

The Return statement returns a scalar value from an SQL expression. It can only be used within an SQL function.

Syntax

RETURN	The RETURN keyword is required as the first word in a RETURN statement.
SQL_expression	The SQL_expression can be a constant, an SQL routine invocation, one of the SQL Scalar functions, an SQL Cast functions, or an SQL Special Register.

Examples

RETURN `abc';

RETURN gestlastcount ( );

RETURN `Happy New Year' | | ` ` CAST (CURRENT_TIMESTAMP TO VARCHAR(30) );

RETURN NULL;

RETURN;

SET assignment

SET assignment_target = assignment_source

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.

Syntax

SET	The SET keyword is required as the first word in a SET assignment statement.
assignment_target	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.
assignment_source	The assignment_source is one or more SQL expressions that can be a constant, an SQL routine invocation, one of the SQL Scalar functions, an SQL Cast functions, or an SQL Special Register. You may not use an SQL correlation variable, however, you can reference new or old row values in the WHEN search_condition. Assignment_source values are assigned to the assignment_target.

Examples

SET newrow.inventory = getnewvalue ( );

SET newrow.selldate = CURRENT_DATE;

SET my_newalias.fruitname = `apples';

SET PATH

SET PATH schema_name [{,schema_name}...]

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.

Syntax

SET PATH	The SET PATH keywords are required as the first words in a SET PATH statement.
schema_name	Required keywords to begin the statement.

Examples

SET PATH Employees, Engineering, Sales, PointBase;

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:

SET PATH CURRENT_PATH, Marketing;

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.

SIGNAL

SIGNAL `sqlstate_message'

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.

Syntax

SIGNAL	The SIGNAL keyword is required as the first word in a SIGNAL statement.
sqlstate_message	The sqlstate_message is an SQL string literal value. You can specify any text they would like. The actual SQLSTATE code will be ZG014 and the SQL error code is 25014.

Examples

SIGNAL `The oranges inventory is empty';

SIGNAL `The salary of an employee would have been higher than the salary of his/her Manager';

VALUES

VALUES ( SQL_expression [ { , SQL_expression } ... ] )

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.

Syntax

VALUES	The VALUES keyword is required as the first word in a VALUES statement.
SQL_expression	The SQL_expression can be a constant, an SQL routine invocation, one of the SQL Scalar functions, an SQL Cast functions, or an SQL Special Register.

Examples

VALUES (addnewfruit( `apple') );

VALUES (increaseorders(200) );

VALUES (CURENT_DATE );