icursor()

Read(3372) Label: icursor,

Here’s how to use icursor() functions.

T.icursor( C,…;w;f,… )

Description:

Use index to query data in an entity table.

Syntax:

T.icursor(C,…;w;f,…)

Note:

The function uses index file f to query fields C,… in entity table T according to filter condition w.

With a HASH index, only perform query by equivalent values and the filter field in w should be same as that in the indexed field.

 

With a full-text index, w only applies to String type fields. The function supports like("*X*") style queries.

Parameter:

T

An entity table

C

To-be-retrieved columns in the entity table; get all columns when the parameter is absent

w

Filtering condition, which cannot be omitted and in which the filtering field for T must be same as the indexed field; support >, >=, <, <=, == and contain in its syntax

f

Index file object; the program will automatically use the suitable one when there are multiple index files

Option:

@s

Order result set by the indexed field and support large result sets

@u

Handle multiple conditions joined up with && from left to right while the default is handling them in an optimal order

Return value:

A unicursor

Example:

Use the ordered index file:

 

A

 

1

=demo.query("select EID,NAME,BIRTHDAY,DEPT,GENDER,HIREDATE,SALARY from employee ")

Return a table sequence.

2

=file("empi.ctx")

 

3

=A2.create@y(#EID,NAME,BIRTHDAY,DEPT,GENDER,HIREDATE,SALARY)

Create a composite table.

4

=A3.append@i(A1)

Append table sequence A1’s data to the composite table.

5

=file("index_dzdpx")

Create an index file object.

6

=A3.index(A5,DEPT=="HR";EID;DEPT)

Create an ordered index file index_dzdpx for data of EID field and DEPT field that meets the condition DEPT=="HR" in composite table A3; the indexed field is EID.

7

=file("index_px")

Create an index file object.

8

=A3.index(A7;EID,NAME)

Create an index file index_px for composite table A3 on EID and NAME, which are indexed fields; as parameter F is absent, read all fields.

9

=A3.icursor(EID,NAME,DEPT,SALARY;EID<20;A7,A5)

Use the index file to query EID, NAME, DEPT and SALARY fields of the composite table according to condition EID<20; as A5’s index file does not contain NAME and DEPT fields, the program automatically chooses A7’s index file to execute the expression.

 

Use a HASH index file:

 

A

 

1

=file("empi.ctx").open()

Open a composite table file.

2

=file("index_hs")

Create index file object.

3

=A1.index(A2:10;DEPT,GENDER)

Create a HASH index file whose length is 10 for composite table empi.ctx on DEPT and GENDER, which are indexed fields.

4

=A1.icursor(NAME,DEPT,GENDER,SALARY;[["HR","M"]].contain(DEPT,GENDER);A2)

Use index file index_hs to query data of NAME, DEPT, GENDER and SALARY fields meeting the condition that DEPT is HR and GENDER is M from the composite table – query according to the equivalent values only and field values in the filter condition should be same as those in the indexed fields – and return a cursor, whose content is as follows:

 

Use a full-text index file:

 

A

 

1

=file("empi.ctx").open()

Open a composite table file.

2

=file("index_qw")

Create index file object.

3

=A1.index@w(A2;NAME,GENDER,EID)

Use @w option to create a full-text index file for composite table empi.ctx on NAME, GENDER and EID, which are indexed fields.

4

=A1.icursor@u(EID,NAME,GENDER,BIRTHDAY;like(NAME,"A*")&&GENDER=="F";A2)

Use index file index_qw to query data of EID, NAME, GENDER and BIRTHDAY fields that meets the condition that NAME value begins with A and GENDER value is F from the composite table, during which @u option enables handling the multiple filter conditions from left to right – and return a cursor, whose content is as follows:

 

Use @s option to return a result set ordered by the indexed field:

 

A

 

1

=file("empi.ctx").open()

Open a composite table file.

2

=file("index_px")

Create index file object.

3

=A1.index(A2;DEPT)

Create an index file on DEPT, which is the indexed field.

4

=A1.icursor(;DEPT=="HR"||DEPT=="R&D";A2).fetch()

Query data of the composite table according to the index file; as parameter C is absent, return all fields:

5

=A1.icursor@s(;DEPT=="HR"||DEPT=="R&D";A2).fetch()

Use @s option to return a result set ordered by the indexed field: