The CREATE FUNCTION statement allows you to create Java functions, which you can then use in an expression.
Syntax
CREATE FUNCTION function-Name ( [ FunctionParameter [, FunctionParameter] ] * ) RETURNS DataType [ FunctionElement ] *
[ schema-Name. ] SQL92Identifier
If schema-Name is not provided, the current schema is the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.
[ parameter-Name ] DataType
PararameterName must be unique within a function.
The syntax of DataType is described in Data Types.
{ SPECIFIC [ schema-Name. ] SQL92Identifier | LANGUAGE { JAVA } | EXTERNAL NAME string | PARAMETER STYLE JAVA | { NO SQL | CONTAINS SQL | READS SQL DATA } | { RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT } }
LANGUAGE
JAVA- the database manager will call the function as a public static method in a Java class.
EXTERNAL NAME string
String describes the Java method to be called when the function is executed, and takes the following form:
class_name.method_name
The External Name cannot have any extraneous spaces.
PARAMETER STYLE
JAVA - The function will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet[] that are passed single entry arrays.
Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.
NO SQL, CONTAINS SQL, READS SQL DATA
Indicates whether the function issues any SQL statements and, if so, what type.
RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT
Specifies whether the function is called if any of the input arguments is null. The result is the null value.
The function elements may appear in any order, but each type of element can only appear once. A function definition must contain these elements:
Example
CREATE FUNCTION TO_DEGREES(RADIANS DOUBLE) RETURNS DOUBLE PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'java.lang.Math.toDegrees'
A CREATE INDEX statement creates an index on a table. Indexes can be on one or more columns in the table.
Syntax
CREATE [UNIQUE] INDEX index-Name ON table-Name ( Simple-column-Name [ ASC | DESC ] [ , Simple-column-Name [ ASC | DESC ]] * )
The maximum number of columns for an index key in Derby is 16.
An index name cannot exceed 18 characters.
A column must not be named more than once in a single CREATE INDEX statement. Different indexes can name the same column, however.
Derby can use indexes to improve the performance of data manipulation statements (see Tuning Derby). In addition, UNIQUE indexes provide a form of data integrity checking.
Index names are unique within a schema. (Some database systems allow different tables in a single schema to have indexes of the same name, but Derby does not.) Both index and table are assumed to be in the same schema if a schema name is specified for one of the names, but not the other. If schema names are specified for both index and table, an exception will be thrown if the schema names are not the same. If no schema name is specified for either table or index, the current schema is used.
By default, Derby uses the ascending order of each column to create the index. Specifying ASC after the column name does not alter the default behavior. The DESC keyword after the column name causes Derby to use descending order for the column to create the index. Using the descending order for a column can help improve the performance of queries that require the results in mixed sort order or descending order and for queries that select the minimum or maximum value of an indexed column.
If a qualified index name is specified, the schema name cannot begin with SYS.
Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns. Derby has already created it for you with a system-generated name. System-generated names for indexes that back up constraints are easy to find by querying the system tables if you name your constraint. For example, to find out the name of the index that backs a constraint called FLIGHTS_PK:
SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES, SYS.SYSCONSTRAINTS WHERE SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID AND CONSTRAINTNAME = 'FLIGHTS_PK'
Examples
CREATE INDEX OrigIndex ON Flights(orig_airport); -- money is usually ordered from greatest to least, -- so create the index using the descending order CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY); -- use a larger page size for the index call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192'); CREATE INDEX IXSALE ON SAMP.SALES (SALES); call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);
Page Size and Key Size
Statement Dependency System
Prepared statements that involve SELECT, INSERT, UPDATE, UPDATE WHERE CURRENT, DELETE, and DELETE WHERE CURRENT on the table referenced by the CREATE INDEX statement are invalidated when the index is created. Open cursors on the table are not affected.
The CREATE PROCEDURE statement allows you to create Java stored procedures, which you can then call using the CALL PROCEDURE statement.
Syntax
CREATE PROCEDURE procedure-Name ( [ ProcedureParameter [, ProcedureParameter] ] * ) [ ProcedureElement ] *
[ schema-Name. ] SQL92Identifier
If schema-Name is not provided, the current schema is the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.
[ { IN | OUT | INOUT } ] [ parameter-Name ] DataType
The default value for a parameter is IN. ParameterName must be unique within a procedure.
The syntax of DataType is described in Data Types.
{ SPECIFIC [ schema-Name. ] SQL92Identifier | [ DYNAMIC ] RESULT SETS INTEGER | LANGUAGE { JAVA } | EXTERNAL NAME string | PARAMETER STYLE JAVA | { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA } }
DYNAMIC RESULT SETS integer
Indicates the estimated upper bound of returned result sets for the procedure. Default is no (zero) dynamic result sets.
LANGUAGE
JAVA- the database manager will call the procedure as a public static method in a Java class.
EXTERNAL NAME string
String describes the Java method to be called when the procedure is executed, and takes the following form:
class_name.method_name
The External Name cannot have any extraneous spaces.
PARAMETER STYLE
JAVA - The procedure will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets are returned through additional parameters to the Java method of type java.sql.ResultSet [] that are passed single entry arrays.
Derby does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.
NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA
Indicates whether the stored procedure issues any SQL statements and, if so, what type.
The procedure elements may appear in any order, but each type of element can only appear once. A procedure definition must contain these elements:
Example
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER, IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2)) PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 'com.acme.sales.calculateRevenueByMonth'
A schema is a way to logically group objects in a single collection and provide a unique namespace for objects.
Syntax
CREATE SCHEMA schema-Name
The CREATE SCHEMA statement is used to create a schema. A schema name cannot exceed 30 characters. Schema names must be unique within the database.
Examples
-- Create a schema for employee-related tables CREATE SCHEMA EMP; -- Create a schema for airline-related tables CREATE SCHEMA Flights -- Create a table called "Availability" in each schema CREATE TABLE FLIGHTS.AVAILABILITY (FLIGHT_ID CHAR(6) NOT NULL, SEGMENT_NUMBER INT NOT NULL, FLIGHT_DATE DATE NOT NULL, ECONOMY_SEATS_TAKEN INT, BUSINESS_SEATS_TAKEN INT, FIRSTCLASS_SEATS_TAKEN INT, CONSTRAINT FLT_AVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE)); CREATE TABLE EMP.AVAILABILITY (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL, ROOMS_TAKEN INT, CONSTRAINT HOTELAVAIL_PK PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).
For information about constraints, see CONSTRAINT clause.
You can specify a default value for a column. A default value is the value to be inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. See Column Default.
You can specify storage properties such as page size for a table by calling the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure.
If a qualified table name is specified, the schema name cannot begin with SYS.
Syntax
CREATE TABLE table-Name ( {column-definition | Table-Level Constraint} [ , {column-definition | Table-Level Constraint} ] * )
Simple-column-Name DataType [ Column-Level-Constraint ]* [ generated-column-spec ] [ Column-Level-Constraint ]*
[ [ WITH ] DEFAULT {ConstantExpression | NULL } | [ GENERATED ALWAYS AS IDENTITY [ ( START WITH IntegerConstant [ ,INCREMENT BY IntegerConstant] ) ] ] ]
The syntax of Data-Type is described in Data Types.
The syntaxes of Column-Level-Constraint and Table-Level Constraint are described in CONSTRAINT clause.
The IDENTITY keyword can only be specified if the data type associated with the column is one of the following exact integer types.
For the definition of a default value, a ConstantExpression is an expression that does not refer to any table. It can include constants, date-time special registers, current schemas, users, and null.
For SMALLINT, INT, and BIGINT columns with identity attributes, Derby automatically assigns increasing integer values to the column. Identity column attributes behave like other defaults in that when an insert statement does not specify a value for the column, Derby automatically provides the value. However, the value is not a constant; Derby automatically increments the default value every time a row is inserted. Also, unlike other defaults, you are not allowed to insert a value directly into or update an identity column.
By default, the initial value of an identity column is 1, and the amount of the increment is 1. You can specify non-default values for both the initial value and the interval amount when you define the column with the key words START WITH and INCREMENT BY. And if you specify a negative number for the increment value, Derby decrements the value with each insert. If this value is 0, or positive, Derby increments the value with each insert.
The maximum and minimum values allowed in identity columns are determined
by the data type of the column. Attempting to insert a value outside
the range of values supported by the data type raises an exception.
Table 1. Maximum and Minimum Values for Columns with Generated Column Specs
Data type | Maximum Value | Minimum Value |
---|---|---|
SMALLINT | 32767 (java.lang.Short.MAX_VALUE) | -32768 (java.lang.Short.MIN_VALUE) |
INT | 2147483647 (java.lang.Integer.MAX_VALUE) | -2147483648 (java.lang.Integer.MIN_VALUE) |
BIGINT | 9223372036854775807 (java.lang.Long.MAX_VALUE) | -9223372036854775808 (java.lang.Long.MIN_VALUE) |
Automatically generated values in an identity column are unique. Use a primary key or unique constraint on a column to guarantee uniqueness. Creating an identity column does not create an index on the column.
The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value for an identity column. See IDENTITY_VAL_LOCAL for more information.
Derby keeps track of the last increment value for a column in a cache. It also stores the value of what the next increment value will be for the column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling back a transaction does not undo this value, and thus rolled-back transactions can leave "gaps" in the values automatically inserted into an identity column. Derby behaves this way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping concurrency high.
When an insert happens within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement into the identity column is available from ConnectionInfo only within the trigger code. The trigger code is also able to see the value inserted by the statement that caused the trigger to fire. However, the statement that caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement into the identity column. Likewise, triggers can be nested (or recursive). An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL statement that causes trigger T2 to fire. If both T1 and T2 insert rows into a table that cause Derby to insert into an identity column, trigger T1 cannot see the value caused by T2's insert, but T2 can see the value caused by T1's insert. Each nesting level can see increment values generated by itself and previous nesting levels, all the way to the top-level SQL statement that initiated the recursive triggers. You can only have 16 levels of trigger recursion.
Examples
CREATE TABLE HOTELAVAILABILITY (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL, ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE)); -- the table-level primary key definition allows you to -- include two columns in the primary key definition PRIMARY KEY (hotel_id, booking_date)) -- assign an identity column attribute to an INTEGER -- column, and also define a primary key constraint -- on the column CREATE TABLE PEOPLE (PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26)); -- assign an identity column attribute to a SMALLINT -- column with an initial value of 5 and an increment value -- of 5. CREATE TABLE GROUPS (GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));
A trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table.
Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions.
You can define any number of triggers for a single table, including multiple triggers on the same table for the same event.
You can create a trigger in any schema except SYS. The trigger need not reside in the same schema as the table on which it is defined.
If a qualified trigger name is specified, the schema name cannot begin with SYS.
CREATE TRIGGER TriggerName AFTER { INSERT | DELETE | UPDATE [ OF column-Name [, column-Name]* ] ON table-Name [ ReferencingClause ] FOR EACH { ROW | STATEMENT } MODE DB2SQL Triggered-SQL-statement
REFERENCING { { OLD | NEW } [ AS ] correlation-Name [ { OLD | NEW } [ AS ] correlation-Name ] | { OLD_TABLE | NEW_TABLE } [ AS ] Identifier [ { OLD_TABLE | NEW_TABLE } [AS] Identifier ] }
Before or After: When Triggers Fire
Triggers fire after all constraints have been satisfied and after the changes have been applied to the target table. Also called After triggers, they can be either row or statement triggers (see Statement versus Row Triggers).
Insert, Delete, or Update: What Causes the Trigger to Fire
A trigger is fired by one of the following database events, depending on how you define it (in Syntax above, see the third line):
You can define any number of triggers for a given event on a given table. For update, you can specify columns.
Referencing Old and New Values: The Referencing Clause
Many triggered-SQL-statements need to refer to data that is currently being changed by the database event that caused them to fire. The triggered-SQL-statement might need to refer to the new (post-change or "after") values.
Derby provides you with a number of ways to refer to data that is currently being changed by the database event that caused the trigger to fire. The easiest way to refer to the changed data in the triggered-SQL-statement is use the transition variables or transition tables.
The referencing clause allows you to provide a correlation name or alias for these transition variables by specifying OLD/NEW AS correlation-Name .
For example, if you add the following clause to the trigger definition:
REFERENCING OLD AS DELETEDROW
you can then refer to this correlation name in the triggered-SQL-statement:
DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id
The OLD and NEW transition variables map to a java.sql.ResultSet with a single row.
For statement triggers, transition tables serve as a table identifier for the triggered-SQL-statement or the trigger qualification. The referencing clause allows you to provide a correlation name or alias for these transition tables by specifying OLD_TABLE/NEW_TABLE AS correlation-Name
For example:
REFERENCING OLD_TABLE AS DeletedHotels
allows you to use that new identifier (DeletedHotels) in the triggered-SQL-statement:
DELETE FROM HotelAvailability WHERE hotel_id IN (SELECT hotel_id FROM DeletedHotels)
The old and new transition tables map to a java.sql.ResultSet with cardinality equivalent to the number of rows affected by the triggering event.
The referencing clause can designate only one new correlation or identifier and only one old correlation or identifier. Row triggers cannot designate an identifier for a transition table and statement triggers cannot designate a correlation for transition variables.
You must specify whether a trigger is a statement trigger or a row trigger:
A statement trigger fires once per triggering event and regardless of whether any rows are modified by the insert, update, or delete event.
A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does not fire.
The action defined by the trigger is called the triggered-SQL-statement (in Syntax above, see the last line). It has the following limitations:
The triggered-SQL-statement can reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back.
For more information on triggered-SQL-statements, see the Derby Developer's Guide.
Order of Execution
When a database event occurs that fires a trigger, Derby performs actions in this order:
When multiple triggers are defined for the same database event for the same table for the same trigger time (before or after), triggers are fired in the order in which they were created.
Examples
-- Statements and after triggers: CREATE TRIGGER FLIGHTSDELETE AFTER DELETE ON FLIGHTS REFERENCING OLD_TABLE AS DELETEDFLIGHTS FOR EACH STATEMENT MODE DB2SQL DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN (SELECT FLIGHT_ID FROM DELETEDFLIGHTS); CREATE TRIGGER FLIGHTSDELETE3 AFTER DELETE ON FLIGHTS REFERENCING OLD AS OLD FOR EACH ROW MODE DB2SQL DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;
Trigger Recursion
The maximum trigger recursion depth is 16.
Related Information
Special system functions that return information about the current time or current user are evaluated when the trigger fires, not when it is created. Such functions include:
Views are virtual tables formed by a query. A view is a dictionary object that you can use until you drop it.
Views are not updatable.
If a qualified view name is specified, the schema name cannot begin with SYS.
Syntax
CREATE VIEW view-Name [ ( Simple-column-Name [, Simple-column-Name] * ) ] AS Query
A view definition can contain an optional view column list to explicitly name the columns in the view. If there is no column list, the view inherits the column names from the underlying query. All columns in a view must be uniquely named.
Examples
CREATE VIEW SAMP.V1 (COL_SUM, COL_DIFF) AS SELECT COMM + BONUS, COMM - BONUS FROM SAMP.EMPLOYEE; CREATE VIEW SAMP.VEMP_RES (RESUME) AS VALUES 'Delores M. Quintana', 'Heather A. Nicholls', 'Bruce Adamson'; CREATE VIEW SAMP.PROJ_COMBO (PROJNO, PRENDATE, PRSTAFF, MAJPROJ) AS SELECT PROJNO, PRENDATE, PRSTAFF, MAJPROJ FROM SAMP.PROJECT UNION ALL SELECT PROJNO, EMSTDATE, EMPTIME, EMPNO FROM SAMP.EMP_ACT WHERE EMPNO IS NOT NULL;
Statement Dependency System
View definitions are dependent on the tables and views referenced within the view definition. DML (data manipulation language) statements that contain view references depend on those views, as well as the objects in the view definitions that the views are dependent on. Statements that reference the view depend on indexes the view uses; which index a view uses can change from statement to statement based on how the query is optimized. For example, given:
CREATE TABLE T1 (C1 DOUBLE PRECISION); CREATE FUNCTION SIN (DATA DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.sin' LANGUAGE JAVA PARAMETER STYLE JAVA; CREATE VIEW V1 (C1) AS SELECT SIN(C1) FROM T1;
the following SELECT:
SELECT * FROM V1
is dependent on view V1, table T1, and external scalar function SIN.