Difference between revisions of "CREATE CURSOR"
From Lianjapedia
Yvonne.milne (Talk | contribs) |
|||
(9 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
==Purpose== | ==Purpose== | ||
Creates a temporary table | Creates a temporary table | ||
− | |||
==Syntax== | ==Syntax== | ||
Line 22: | Line 21: | ||
[, ...]] | [, ...]] | ||
− | | [FROM ARRAY <array>] | + | | [FROM ARRAY <array>] | [FROM XML <xml-file> [LOAD]] |
− | + | ||
==See Also== | ==See Also== | ||
[[ALTER INDEX]], [[ALTER TABLE]], [[CREATE TABLE]], [[CREATE INDEX]], [[CREATE VIEW]], [[DROP DATABASE]], [[DROP INDEX]], [[DROP TABLE]], [[SELECT]], [[SQL USE|USE]] | [[ALTER INDEX]], [[ALTER TABLE]], [[CREATE TABLE]], [[CREATE INDEX]], [[CREATE VIEW]], [[DROP DATABASE]], [[DROP INDEX]], [[DROP TABLE]], [[SELECT]], [[SQL USE|USE]] | ||
− | |||
==Description== | ==Description== | ||
The CREATE CURSOR command creates a temporary table with the specified name. Columns to be included in the table can be specified individually or details loaded from an existing array. | The CREATE CURSOR command creates a temporary table with the specified name. Columns to be included in the table can be specified individually or details loaded from an existing array. | ||
− | + | {| class="wikitable" width="100%" | |
− | {| class="wikitable" | + | !width="20%"|Keywords||width="80%"|Description |
− | !Keywords||Description | + | |
|- | |- | ||
− | |cursor||The name of the temporary table to be created. | + | |valign="top"|cursor||The name of the temporary table to be created. |
|- | |- | ||
− | |column||The name of the column to be created. | + | |valign="top"|column||The name of the column to be created. |
|- | |- | ||
− | |datatype ||The column's data type. | + | |valign="top"|datatype ||The column's data type. |
|- | |- | ||
− | |precision||The width of the column where not fixed. | + | |valign="top"|precision||The width of the column where not fixed. |
|- | |- | ||
− | |scale||The column's decimal places where required. | + | |valign="top"|scale||The column's decimal places where required. |
|- | |- | ||
− | |NULL | NOT NULL||Specifies whether this column can have NULL values. NULL allows NULL values, NOT NULL prohibits NULL values. | + | |valign="top"|NULL | NOT NULL||Specifies whether this column can have NULL values. NULL allows NULL values, NOT NULL prohibits NULL values. |
|- | |- | ||
− | |CHECK <expression>||Validation rule for the column. The <expression> must evaluate to true (.T.), valid value or false (.F.), invalid value. | + | |valign="top"|CHECK <expression>||Validation rule for the column. The <expression> must evaluate to true (.T.), valid value or false (.F.), invalid value. |
|- | |- | ||
− | |ERROR <text>||An optional error message, <text>, to be displayed when the CHECK <expression> validation fails. | + | |valign="top"|ERROR <text>||An optional error message, <text>, to be displayed when the CHECK <expression> validation fails. |
|- | |- | ||
|AUTOINC||Enables auto incrementing for the column | |AUTOINC||Enables auto incrementing for the column | ||
|- | |- | ||
− | |NEXTVALUE <NextValue>||The specified <NextValue> is the numeric start value for the auto incrementing. | + | |valign="top"|NEXTVALUE <NextValue>||valign="top"|The specified <NextValue> is the numeric start value for the auto incrementing. |
|- | |- | ||
− | |STEP <StepValue>||The specified <StepValue> determines the increment value. By default values are incremented by 1. | + | |valign="top"|STEP <StepValue>||The specified <StepValue> determines the increment value. By default values are incremented by 1. |
|- | |- | ||
− | |DEFAULT <expression>||The specified <expression> is used as the default value for the column. | + | |valign="top"|DEFAULT <expression>||The specified <expression> is used as the default value for the column. |
|- | |- | ||
− | |UNIQUE||Creates a unique index on this column. | + | |valign="top"|UNIQUE||Creates a unique index on this column. |
|- | |- | ||
− | |COLLATE <cCollateSequence>||The specified <cCollateSequence> is used as the index collating sequence. | + | |valign="top"|COLLATE <cCollateSequence>||valign="top"|The specified <cCollateSequence> is used as the index collating sequence. |
|- | |- | ||
− | |NOCPTRANS||Disables code page translation for character and memo columns. | + | |valign="top"|NOCPTRANS||Disables code page translation for character and memo columns. |
|- | |- | ||
− | |FROM ARRAY <array>||The table structure is taken from an existing array, whose name is specified in <array>. The array contents must be the column name, type, precision and scale for each column in the temporary table. | + | |valign="top"|FROM ARRAY <array>||The table structure is taken from an existing array, whose name is specified in <array>. The array contents must be the column name, type, precision and scale for each column in the temporary table. |
+ | |- | ||
+ | |valign="top"|FROM XML <xml-file> [LOAD]||The table structure is taken from the XML file whose name is specified in <xml-file>. If the LOAD option is specified any data in the xml file is loaded into the newly created cursor. | ||
|- | |- | ||
|} | |} | ||
− | |||
==Example== | ==Example== | ||
<code lang="recital"> | <code lang="recital"> | ||
− | CREATE CURSOR tempstaff | + | CREATE CURSOR tempstaff; |
− | (staff_no CHAR(6) NOT NULL | + | (staff_no CHAR(6) NOT NULL,; |
− | lastname CHAR(15) NOT NULL, | + | lastname CHAR(15) NOT NULL,; |
− | firstname CHAR(10), | + | firstname CHAR(10),; |
− | hiredate DATE, | + | hiredate DATE,; |
− | location CHAR(15), | + | location CHAR(15),; |
− | supervisor CHAR(6), | + | supervisor CHAR(6),; |
− | salary DECIMAL(6,0), | + | salary DECIMAL(6,0),; |
− | picture VARBINARY, | + | picture VARBINARY,; |
− | history LONG VARCHAR, | + | history LONG VARCHAR,; |
− | commission DECIMAL(4,1)) | + | commission DECIMAL(4,1)) |
</code> | </code> | ||
− | |||
− | |||
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:SQL]] | [[Category:SQL]] | ||
[[Category:Commands]] | [[Category:Commands]] |
Latest revision as of 08:49, 26 September 2016
Contents
Purpose
Creates a temporary table
Syntax
CREATE CURSOR <cursor>
[(<column> <datatype> [(<precision> [,<scale>])]
[NULL | NOT NULL]
[CHECK <expression> [ERROR <text>]]
[AUTOINC [NEXTVALUE <NextValue> [STEP <StepValue>]]]
[DEFAULT <expression>]
[UNIQUE [COLLATE <cCollateSequence>]]
[NOCPTRANS]
[, ...]]
| [FROM ARRAY <array>] | [FROM XML <xml-file> [LOAD]]
See Also
ALTER INDEX, ALTER TABLE, CREATE TABLE, CREATE INDEX, CREATE VIEW, DROP DATABASE, DROP INDEX, DROP TABLE, SELECT, USE
Description
The CREATE CURSOR command creates a temporary table with the specified name. Columns to be included in the table can be specified individually or details loaded from an existing array.
Keywords | Description |
---|---|
cursor | The name of the temporary table to be created. |
column | The name of the column to be created. |
datatype | The column's data type. |
precision | The width of the column where not fixed. |
scale | The column's decimal places where required. |
NULL | NOT NULL | Specifies whether this column can have NULL values. NULL allows NULL values, NOT NULL prohibits NULL values. |
CHECK <expression> | Validation rule for the column. The <expression> must evaluate to true (.T.), valid value or false (.F.), invalid value. |
ERROR <text> | An optional error message, <text>, to be displayed when the CHECK <expression> validation fails. |
AUTOINC | Enables auto incrementing for the column |
NEXTVALUE <NextValue> | The specified <NextValue> is the numeric start value for the auto incrementing. |
STEP <StepValue> | The specified <StepValue> determines the increment value. By default values are incremented by 1. |
DEFAULT <expression> | The specified <expression> is used as the default value for the column. |
UNIQUE | Creates a unique index on this column. |
COLLATE <cCollateSequence> | The specified <cCollateSequence> is used as the index collating sequence. |
NOCPTRANS | Disables code page translation for character and memo columns. |
FROM ARRAY <array> | The table structure is taken from an existing array, whose name is specified in <array>. The array contents must be the column name, type, precision and scale for each column in the temporary table. |
FROM XML <xml-file> [LOAD] | The table structure is taken from the XML file whose name is specified in <xml-file>. If the LOAD option is specified any data in the xml file is loaded into the newly created cursor. |
Example
CREATE CURSOR tempstaff; (staff_no CHAR(6) NOT NULL,; lastname CHAR(15) NOT NULL,; firstname CHAR(10),; hiredate DATE,; location CHAR(15),; supervisor CHAR(6),; salary DECIMAL(6,0),; picture VARBINARY,; history LONG VARCHAR,; commission DECIMAL(4,1))