![]() Chapter 9. SQL Syntax.HSQLDB version 1.SQL statements and syntax described. Winrar 4 20 Key Themes Works On Win7 Activator . Notational Conventions Used in this Chapter[A] means A is optional.{ B | C } means either B or C must be. B | C }] means either B or C may optionally be. UPPERCASE words are keywords. ALTER INDEX < indexname> RENAME TO < newname> ;Index names can be changed so long as they do not conflict with. The execution of the stored procedure is aborted. If there is no CATCH handler anywhere, the entire batch is aborted, and the transaction is rolled back. 1 Introduction. This chapter introduces you to the Oracle Pro*C/C++ Precompiler. You look at its role in developing application programs that manipulate Oracle data. Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure. Transact-SQL Syntax Conventions. HSQLDB version 1.8.0 supports the SQL statements and syntax described in this chapter. ALTER SEQUENCE < sequencename> RESTART WITH < value> ;Resets the next value to be returned from the sequence. ALTER SCHEMA < schemaname> RENAME TO < newname> ;Renames the schema as specified. All objects of the schema will. Requires Administrative privileges. ALTER TABLE < tablename> ADD [COLUMN] < columnname> Datatype. Size[,precision])] [{DEFAULT < default. Value> |. GENERATED BY DEFAULT AS IDENTITY (START WITH < n> [, INCREMENT BY < m> ])}] |. NOT] NULL] [IDENTITY] [PRIMARY KEY]. BEFORE < existingcolumn> ]; Adds the column to the end of the column list. The optional BEFORE. It accepts a column. Definition as in a CREATE TABLE command. If NOT NULL. is specified and the table is not empty, then a default value must be. In all other respects, this command is the equivalent of a. ![]() CREATE TABLE statement. If an SQL view includes a SELECT * FROM < tablename> in its. This is a. non- standard feature which is likely to change in the future. ALTER TABLE < tablename> DROP [COLUMN] < columnname> ;Drops the column from the table. Will drop any single- column. The command will. It will also fail if an SQL view includes the column. ALTER TABLE < tablename> ALTER COLUMN < columnname> RENAME TO < newname> Changes a column name. ALTER TABLE < tablename> ALTER COLUMN < columnname> SET DEFAULT < defaultvalue> }; Adds the specified default value to the column. Use NULL to remove. ALTER TABLE < tablename> ALTER COLUMN < columnname> SET [NOT] NULLSets or removes a NOT NULL constraint for the column. ALTER TABLE < tablename> ALTER COLUMN < column. Definition> ;This form of ALTER TABLE ALTER COLUMN accepts a column. Definition as in a CREATE. TABLE command, with the following restrictions. Restrictions. The column must be already be a PK column to accept an. IDENTITY definition. If the column is already an IDENTITY column and there is no. IDENTITY definition, the existing IDENTITY attribute is. The default expression will be that of the new definition. The NOT NULL attribute will be that of the new definition. Depending on the type of change, the table may have to be. It always works when the type of change. ALTER TABLE < tablename> ALTER COLUMN < columnname> RESTART WITH < new sequence value> This form is used exclusively for IDENTITY columns and changes the. ALTER TABLE < tablename> ADD [CONSTRAINT < constraintname> ]CHECK (< search condition> ); Adds a check constraint to the table. In the current version, a. ALTER TABLE < tablename> ADD [CONSTRAINT < constraintname> ] UNIQUE (< column list> ); Adds a unique constraint to the table. This will not work if there. This will work only if the values of the column list for the. ALTER TABLE < tablename> ADD [CONSTRAINT < constraintname> ]PRIMARY KEY (< column list> ); Adds a primary key constraint to the table, using the same. ALTER TABLE < tablename> ADD [CONSTRAINT < constraintname> ] FOREIGN KEY (< column list> )REFERENCES < exptablename> (< column list> )[ON {DELETE | UPDATE} {CASCADE | SET DEFAULT | SET NULL}]; Adds a foreign key constraint to the table, using the same. This will fail if for each existing row in the referring table, a. ALTER TABLE < tablename> DROP CONSTRAINT < constraintname> ;Drop a named unique, check or foreign key constraint from the. ALTER TABLE < tablename> RENAME TO < newname> ;ALTER USER < username> SET PASSWORD < password> ;Changes the password for an existing user. Password must be double. Use "" for an empty password. DBA's may change users' base default schema name with the comand. ALTER USER < username> SET INITIAL SCHEMA < schemaname> . This is the schema which database object names will resolve to for this. Schema object naming. For. reasons of backwards compatibility, the initial schema value will not be. HSQLDB version 1. I. e.. INITIAL SCHEMA settings will be lost upon database shutdown with HSQLDB. Only an administrator may use these commands. CALL Expression; Any expression can be called like a stored procedure, including. Java stored procedures or functions. This command returns a. Result. Set with one column and one row (the result) just like a SELECT. See also: Stored Procedures / Functions, SQL Expression. CHECKPOINT [DEFRAG[2]]; Closes the database files, rewrites the script file, deletes the. If DEFRAG is specified, this command also shrinks the . See also: SHUTDOWN, SET LOGSIZE. CONNECT USER < username> PASSWORD < password> ;Connects to the database as a different user. Password should be. Use "" for an empty password. See also: GRANT. REVOKE. CREATE ALIAS < function> FOR < java. Function> ;Creates an alias for a static Java function to be used as a. Stored Procedure. The function must be accessible from the JVM in which the database. Example: CREATE ALIAS ABS FOR "java. Math. abs"; Note. The CREATE ALIAS command just defines the alias. It does not validate existence of the target method or its. To validate the alias, use it. See also: CALL. Stored Procedures / Functions. CREATE [UNIQUE] INDEX < index> ON < table> (< column> [DESC] [, ..]) [DESC]; Creates an index on one or more columns in a table. Creating an index on searched columns may improve performance. The. qualifier DESC can be present for command compatibility with other. Unique indexes can be defined but this. Use UNIQUE constraints instead. The name of an index must. See also: CREATE TABLE, DROP INDEX. CREATE ROLE < rolename> ;Creates the named role with no members. Requires Administrative. CREATE SCHEMA < schemaname> AUTHORIZATION < grantee> [< create. Statement> [< grant. Statement> ] [..]; Creates the named schema, with ownership of the specified. The authorization grantee may be a. Optional (nested) CREATE and GRANT statements can be given only. Only the last nested statement. CREATE SCHEMA" will end the CREATE SCHEMA command. In. the example below, a new schema, ACCOUNTS, is created, then two tables. CREATE SCHEMA ACCOUNTS AUTHORIZATION DBACREATE TABLE AB(A INTEGER, ..)CREATE TABLE CD(C CHAHR, ..)CREATE VIEW VI AS SELECT .. GRANT SELECT TO PUBLIC ON ABGRANT SELECT TO JOE ON CD. Note that this example consists of one CREATE SCHEMA statement which. Requires Administrative privileges. CREATE SEQUENCE < sequencename> [AS {INTEGER | BIGINT}][START WITH < startvalue> ] [INCREMENT BY < incrementvalue> ]; Creates a sequence. The default type is INTEGER. The default start. Negative values are not allowed. If a. sequence goes beyond Integer. MAXVALUE or Long. MAXVALUE, the next result. The next value for a sequence can be included in SELECT, INSERT. UPDATE statements as in the following example: SELECT [..,] NEXT VALUE FOR < sequencename> [, ..] FROM < tablename> ;In the proposed SQL 2. CREATE [MEMORY | CACHED | [GLOBAL] TEMPORARY | TEMP [2] | TEXT[2]] TABLE < name> ( < column. Definition> [, ..] [, < constraint. Definition>..] )[ON COMMIT {DELETE | PRESERVE} ROWS]; Creates a tables in memory (default) or on disk and only cached in. If the database is all- in- memory, both MEMORY and CACHED forms. CREATE TABLE return a MEMORY table while the TEXT form is not. Components of a CREATE TABLE commandcolumn. Definitioncolumnname Datatype [(column. Size[,precision])][{DEFAULT < default. Value> |GENERATED BY DEFAULT AS IDENTITY(START WITH < n> [, INCREMENT BY < m> ])}] |[[NOT] NULL] [IDENTITY] [PRIMARY KEY]Default values that are allowed are constant values or. SQL datetime functions. Allowed Default Values in Column Definitions. For character column, a single- quoted string or NULL. The only SQL function that can be used is CURRENT_USER. For datetime columns, a single- quoted DATE, TIME or. TIMESTAMP value or NULL. Or a datetime SQL function such as. CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, TODAY, NOW. Each function is allowed for a certain datetime type. For BOOLEAN columns, the literals FALSE, TRUE. SQL Statements. This chapter provides information about the SQL statements available in Times. Ten. SQL statements are generally considered to be either Data Manipulation Language (DML) statements or Data Definition Language (DDL) statements. DML statements modify database objects. INSERT, UPDATE and DELETE are examples of DML statements. DDL statements modify the database schema. CREATE TABLE and DROP TABLE are examples of DDL statements. ALTER ACTIVE STANDBY PAIRYou can change an active standby pair by: Adding or dropping a subscriber database. Altering store attributes. Only the PORT and TIMEOUT attributes can be set for subscribers. Including tables, sequences or cache groups in the replication scheme. Excluding tables, sequences or cache groups from the replication scheme. See "Making other changes to an active standby pair" in Oracle Times. Ten In- Memory Database Replication Guide. SQL syntax. ALTER ACTIVE STANDBY PAIR {. Subscriber. Operation |. Store. Operation | Inclusion. Operation |. Network. Operation } [..]. Syntax for Subscriber. Operation. {ADD | DROP } SUBSCRIBER Full. Store. Name. Syntax for Store. Operation. ALTER STORE Full. Store. Name SET Store. Attribute. Syntax for Inclusion. Operation. [{ INCLUDE | EXCLUDE }{TABLE [[Owner.]Table. Name [..]]|. CACHE GROUP [[Owner.]Cache. Group. Name [..]]|. SEQUENCE [[Owner.]Sequence. Name [..]]} [..]]. Syntax for Network. Operation. ADD ROUTE MASTER Full. Store. Name SUBSCRIBER Full. Store. Name. { { MASTERIP Master. Host | SUBSCRIBERIP Subscriber. Host }. PRIORITY Priority } [..]. DROP ROUTE MASTER Full. Store. Name SUBSCRIBER Full. Store. Name. { MASTERIP Master. Host | SUBSCRIBERIP Subscriber. Host } [..]. Parameters. Parameter. Description. ADD SUBSCRIBERFull. Store. Name. Indicates a subscriber database. Full. Store. Name is the database file name specified in the Data. Store attribute of the DSN description. DROP SUBSCRIBERFull. Store. Name. Indicates that updates should no longer be sent to the specified subscriber database. This operation fails if the replication scheme has only one subscriber. Full. Store. Name is the database file name specified in the Data. Store attribute of the DSN description. ALTER STOREFull. Store. Name. SETStore. Attribute. Indicates changes to the attributes of a database. Only the PORT and TIMEOUT attributes can be set for subscribers. Full. Store. Name is the database file name specified in the Data. Store attribute of the DSN description. For information on Store. Attribute clauses, see "CREATE ACTIVE STANDBY PAIR". Full. Store. Name. The database, specified as one of the following. SELFThe prefix of the database file name. For example, if the database path is directory/subdirectory/data. This is the database file name specified in the Data. Store attribute of the DSN description with optional host ID in the form: Data. Store. Name[ONHost]Host can be either an IP address or a literal host name assigned to one or more IP addresses, as described in "Configuring the network" in Oracle Times. Ten In- Memory Database Replication Guide. Host names containing special characters must be surrounded by double quotes. For example: "My. Host- 5. 00".{INCLUDE|EXCLUDE}{[TABLE [Owner.]Table. Name[..]|CACHE GROUP[[Owner.]Cache. Group. Name]|[..]SEQUENCE [[Owner.]Sequence. Name[..]}[..]Includes in or excludes from replication the tables, sequences or cache groups listed. INCLUDE adds the tables, sequences or cache groups to replication. Use one INCLUDE clause for each object type (table, sequence or cache group). EXCLUDE removes the tables, sequences or cache groups from replication. Use one EXCLUDE clause for each object type (table, sequence or cache group). ADD ROUTE MASTERFull. Store. Name. SUBSCRIBERFull. Store. Name. Adds Network. Operation to replication scheme. Enables you to control the network interface that a master store uses for every outbound connection to each of its subscriber stores. In the context of the ADD ROUTE clause, each master database is a subscriber of the other master database and each read- only subscriber is a subscriber of both master databases. Can be specified more than once. For Full. Store. Name, "ONhost" must be specified. DROP ROUTE MASTERFull. Store. Name. SUBSCRIBERFull. Store. Name. Drops Network. Operation from replication scheme. Can be specified more than once. For Full. Store. Name, "ONhost" must be specified. MASTERIPMaster. Host|SUBSCRIBERIPSubscriber. Host. Master. Host and Subscriber. Host are the IP addresses for the network interface on the master and subscriber stores. Specify in dot notation or canonical format or in colon notation for IPV6. Clause can be specified more than once. Valid for both ADD and DROP ROUTE MASTER. PRIORITYPriority. Variable expressed as an integer from 1 to 9. Denotes the priority of the IP address. Lower integral values have higher priority. An error is returned if multiple addresses with the same priority are specified. Controls the order in which multiple IP addresses are used to establish peer connections. Required syntax of Network. Operation clause. Follows MASTERIPMaster. Host| SUBSCRIBERIPSubscriber. Host clause. Description. You must stop the replication agent before altering an active standby pair. The exceptions are for those objects and statements that are automatically replicated and included based on the values of the DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION attributes, as described in "ALTER SESSION". You may only alter the active standby pair replication scheme on the active database. See "Making other changes to an active standby pair" in Oracle Times. Ten In- Memory Database Replication Guide for more information. You may not use ALTER ACTIVE STANDBY PAIR when using Oracle Clusterware with Times. Ten. See "Restricted commands and SQL statements" in Oracle Times. Ten In- Memory Database Replication Guide for more information. Instead, perform the tasks described in "Changing the schema" section of the Oracle Times. Ten In- Memory Database Replication Guide. Use ADD SUBSCRIBERFull. Store. Name to add a subscriber to the replication scheme. Use DROP SUBSCRIBERFull. Store. Name to drop a subscriber from the replication scheme. Use the INCLUDE or EXCLUDE clause to include the listed tables, sequences or cache groups in the replication scheme or to exclude them from the replication scheme. Use one INCLUDE or EXCLUDE clause for each object type (table, sequence or cache group). The ALTER ACTIVE STANDBY statement is not necessary for those objects and statements that are automatically replicated and included based on the values of the DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION attributes, as described in "ALTER SESSION". However, if DDL_REPLICATION_LEVEL is 2 or greater and DDL_REPLICATION_ACTION="EXCLUDE", use the INCLUDE clause to include replicated objects into the replication scheme. When DDL_REPLICATION_LEVEL is 2 or greater, the INCLUDE clause can only be used with empty tables on the active database. The contents of the corresponding tables on the standby and any subscribers will be truncated before the table is added to the replication scheme. Examples. Add a subscriber to the replication scheme. ALTER ACTIVE STANDBY PAIR. ADD SUBSCRIBER rep. Drop two subscribers from the replication scheme. ALTER ACTIVE STANDBY PAIR. DROP SUBCRIBER rep. DROP SUBSCRIBER rep. Alter the store attributes of the rep. ALTER ACTIVE STANDBY PAIR. ALTER STORE rep. 3 SET PORT 2. TIMEOUT 1. 80. ALTER STORE rep. SET PORT 2. 35. 00 TIMEOUT 1. Add a table, a sequence and two cache groups to the replication scheme. ALTER ACTIVE STANDBY PAIR. INCLUDE TABLE my. INCLUDE SEQUENCE my. INCLUDE CACHE GROUP my. Add Network. Operation clause to active standby pair. ALTER ACTIVE STANDBY PAIR. ADD ROUTE MASTER rep. ON "machine. 1" SUBSCRIBER rep. ON "machine. 2". MASTERIP "1. PRIORITY 1 SUBSCRIBERIP "2. PRIORITY 1. ALTER CACHE GROUPThe ALTER CACHE GROUP statement enables changes to the state, interval and mode of AUTOREFRESH. Updates on the Oracle Database tables can be propagated back to the Times. Ten cache group with the use of AUTOREFRESH. AUTOREFRESH can be enabled when the cache group is a user managed cache group or is defined as READONLY with an AUTOREFRESH clause. Any values or states set by ALTER CACHE GROUP are persistent. They are stored in the database and survive daemon and cache agent restarts. For a description of cache group types, see "User managed and system managed cache groups". Required privilege. No privilege is required for the cache group owner. ALTER ANY CACHE GROUP for another user's cache group.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |