Partager via

CREATE CURSOR - SQL Command

  • Article

Creates a temporary table.

CREATE CURSOR alias_name 
   (fname1 type [(precision [, scale])] [NULL | NOT NULL] 
   [CHECK lExpression [ERROR cMessageText]] 
   [AUTOINC [NEXTVALUE NextValue [STEP StepValue]]]
   [DEFAULT eExpression] [UNIQUE [COLLATE cCollateSequence]]
   [NOCPTRANS] [, fname2 ...]) 
   | FROM ARRAY ArrayName

Parameters

  • alias_name
    Specifies the name of the temporary table to create. The alias_name parameter can be a name expression.

  • fname
    Specifies the name of a field in the temporary table. Each fname can be a name expression.

  • type
    Specifies a single letter indicating the data type for the field.

  • precision
    Specifies the width of the field specified with fname. Some data types require that you specify a value for precision.

  • scale
    Specifies the number of decimal places for the specified data type. Some data types require that you specify a value for scale.

    The following table shows what type, precision, and scale can be.

    FieldType nFieldWidth nPrecision Description
    C n – Character field of width n
    D – – Date
    T – – Datetime
    N n d Numeric field of width n with d decimal places
    F n d Floating numeric field of width n with d decimal places
    I – – Integer
    B – d Double
    Y – – Currency
    L – – Logical
    M – – Memo
    G – – General
    P – – Picture

    The nFieldWidth and nPrecision parameters are ignored for D, T, Y, L, M, G, and P types. The nPrecision parameter defaults to zero (no decimal places) if nPrecision is not included for the N, F, or B types.

  • NULL | NOT NULL
    Specifies whether null values are allowed in the field. NULL permits null values, while NOTNULL does not allow null values.

  • CHECK lExpression
    Specifies a validation rule for the field. The lExpression parameter must evaluate to a logical expression and can be a user-defined function or a stored procedure.

  • ERROR cMessageText
    Specifies an error message. Visual FoxPro displays this message when the validation rule specified in the CHECK clause generates an error. The message displays only when data is changed within a Browse window or Edit window.

  • AUTOINC [NEXTVALUE NextValue [STEP StepValue]]
    Enables autoincrementing for the field.

    NextValue specifies the start value and can be a positive or a negative integer value ranging from 2,147,483,647 to -2,147,483,647. The default value is 1. You can set NextValue using the Next Value box on the Fields tab in the Table Designer.

    StepValue specifies the increment value for the field and can be a positive, nonzero integer value ranging from 1 to 255. The default value is 1. You can set StepValue using the Step box on the Fields tab in the Table Designer.

    Autoincrementing values cannot be null (.NULL.).

    Note   Tables containing automatically incrementing field values append table-buffered records approximately 35% slower than tables without automatically incrementing field values, which might affect performance. When using table buffering, the table header is locked when the record is appended.

  • DEFAULT eExpression
    Specifies a default value for the field. The data type of eExpression must be the same as the specified field's data type.

    If you use the AUTOINC clause to turn on autoincrementing for a field and specify a default value, Visual FoxPro stores the default value in the table but does not use it. Visual FoxPro uses the default value if you use the ALTER TABLE - SQL command to remove autoincrementing for the field.

  • UNIQUE
    Creates a candidate index for the field. The candidate index tag has the same name as the field. For more information about candidate indexes, see Index Information Display and Setting a Primary or Candidate Index.

  • COLLATE cCollateSequence
    Specifies a collation sequence other than the default setting, MACHINE. The cCollateSequence parameter must be a valid Visual FoxPro collation sequence. For more information about setting collation sequences, see Optimization of International Applications and SET COLLATE Command.

  • NOCPTRANS
    Prevents translation to a different code page for character and memo fields. You can specify NOCPTRANS only for character and memo fields.

  • FROM ARRAY ArrayName
    Specifies the name of an existing array whose contents are the name, type, precision, and scale for each field in the temporary table. You can use the FROM ARRAY clause instead of specifying individual fields. For the proper format of the contents of the array, see AFIELDS( ) Function.

    Autoincrementing is turned on when the step value is greater than 0.

Remarks

CREATE CURSOR creates a temporary table that exists until it is closed. You can manipulate a temporary table created with CREATE CURSOR like any other table — you can browse and index it, and you can append and modify records.

The temporary table opens in the lowest numbered available (unused) work area and can be accessed by its alias. Each field in the temporary table is defined with a name, type, precision, and scale. These definitions can be obtained from the command itself or from an array. The temporary table opens exclusively, regardless of the setting of SET EXCLUSIVE.

If you omit NULL and NOT NULL, the current setting of SETNULL determines if null values are allowed in the field. However, if you omit NULL and NOTNULL and include the PRIMARYKEY or UNIQUE clause, Visual FoxPro disregards the current setting of SETNULL, and the field defaults to NOTNULL.

Null values and duplicate records are not permitted in a field used for a candidate index. However, Visual FoxPro does not generate an error if you create a candidate index for a field that supports null values. Visual FoxPro generates an error if you attempt to enter a null or duplicate value into a field used for a candidate index.

Note   Candidate indexes, created by including the UNIQUE option (provided for ANSI compatibility) in CREATE TABLE – SQL or ALTER TABLE – SQL commands, are not the same as indexes created in the INDEX command with the UNIQUE option. An index created in the INDEX command using the UNIQUE option allows duplicate index keys; candidate indexes do not allow duplicate index keys. For more information about the UNIQUE option in the INDEX command, see INDEX Command.

If the table is converted to another code page, the fields for which NOCPTRANS has been specified are not translated.

CREATE CURSOR...FROM ARRAY recognizes all the fields available in the AFIELDS( ) function and turns on autoincrementing in the cursor when the Step value is greater than zero.

Example

The following example creates a cursor with the alias "employee". A blank record is appended, filled, and displayed with the BROWSE command.

CLOSE DATABASES
CLEAR
CREATE CURSOR employee ;
 (EmpID N(5), Name C(20), Address C(30), City C(30), ;
  PostalCode C(10), OfficeNo C(8) NULL, Specialty M)
DISPLAY STRUCTURE
WAIT WINDOW "Press a key to add a record."

INSERT INTO employee (EmpId, Name, Address, City, PostalCode, ;
   OfficeNo, Specialty);
   VALUES (1002, "Dr. Bonnie Doren", "University of Oregon", "Eugene", ;
   "98403", "", "Secondary Special Education")
BROWSE

* At this point you can copy this record to a permanent table.
CLOSE ALL   && Once the cursor closes, all data is flushed from memory.
CLEAR

The following example uses NOCPTRANS to prevent translation to a different code page. The example creates a cursor named "mycursor" that contains two character fields and two memo fields. The second character field, "char2", and the second memo field, "memo2", include NOCPTRANS to prevent translation.

CREATE CURSOR mycursor (char1 C(10), char2 C(10) NOCPTRANS,;
   memo1 M, memo2 M NOCPTRANS)

See Also

AFIELDS( ) | CREATE | CREATE QUERY | CREATE TABLE - SQL | INSERT - SQL | MODIFY QUERY | SELECT - SQL