Pro*c function Case 3-cursor use

1. Cursors in Oracle
Oracle uses two types of cursors: an explicit and an implicit cursor. Oracle implicitly defines a cursor for each SQL statement used, regardless of how many records the statement returns. Oracle for each Delete,
An SQL command such as UPDATE, INSERT, implicitly declares a cursor. The user declares and uses an explicit cursor to handle multiple records returned by the SELECT statement.
Cursors are the working areas where Oracle and Pro*c store query results. A cursor (named) is associated with a SELECT statement. The action cursor has a 4-line command:
(1) DECLARE cursor, (2) OPEN cursor, (3) FETCH, (4) CLOSE cursor.
A. Defining cursors
A cursor must be defined first in order to use it. The syntax is:
EXEC SQL declare〈 cursor name 〉corsor for
Select〈 column
From〈 table
For example:
EXEC SQL DECLARE csor, CURSOR for
SELECT ename, JOB, SAL
From EMP
WHERE Deptno=:deptno;
When a cursor cursor that is associated with a query is assigned to it, the Select query emp can be
Multiple rows are returned in the database, which is an active area of the cursor.
Attention:
1) The definition cursor must be completed before the cursor operation;
2) Pro*c cannot refer to cursors that are not defined;
3) After the cursor is defined, its scope is the entire program. So for a program, it is wrong to define two identical cursors at the same time.
B. Requesting a cursor
After the cursor is defined, the application must first call the ALLOCATE statement before using the cursor variable
Request a cursor, that is, assign a memory area to the cursor variable, the ALLOCATE syntax format is:
EXEC SQL allocate:cursor_variable;
C. Opening a cursor
The open statement that opens the cursor is primarily used to enter the contents of the main variable, which is mainly used in where
The main variable. The statement that opened the cursor is: EXEC SQL open〈 cursor name
When you open a cursor, you can remove more than one row of results from the related query. All queries that meet the query criteria
Rows are made up of a set called the cursor activity set. By taking an action, each row or group in the activity set is a
Once the query is complete, the cursor can be closed. ：

Note: 1) the cursor is in front of the first row of the active set;
2) If you change the input main variable, you must reopen the cursor.
D. Fetching data
The process of fetching a row or group of results from the active set to the output main variable is called data. Output main variable
is defined in the FETCH data statement. The statement that takes the data is as follows:
EXEC SQL fetch〈 cursor name 〉into: Main variable 1, main variable 2, ...
The working process of fetch:

The results of the query refer to the query results that satisfy the query criteria. The following points should be noted when using fetch:
<1> cursors must first be defined and then opened.
<2> executes a FETCH statement only if the cursor is open to fetch data.
The <3> FETCH statement executes once, fetching data from the current row or the current group once, and the next row or group moves up one time. Each time a cursor refers to a row or group that is either the current row or the current group, and the fetch
Each time the data for the row or group specified by the cursor is taken.
<4> Orcle Returns a SQLCA when the cursor activity set is empty. SQLCA (=1403).
<5> If you want this cursor to be operational, you must first close and then open it.
<6> in C program can open up a memory space, to store the operation results, so that can use open space to flexibly manipulate the results of the query.
E. Close Cursors
After you have exhausted all the rows in the activity set, you must close the cursor to free the resources associated with the cursor.
The format of the closing cursor is:
EXEC SQL CLOSE cursor name;
For example:
EXEC SQL CLOSE C1;
ORACLE version V5.0 supports the SQL format "current of CURSOR". This statement will point to a
The most recently fetched row in the cursor for modification and delete operations. The statement must be used after a fetch operation,
It is equivalent to storing a rowid and using it.
F. Releasing cursors
The close statement does not release the memory that is used by the cursor variable when it closes the cursor variable. As a result, the cursor changes
After the volume is closed, the application can reopen the cursor variable to implement various cursor operations. You need to turn off cursor changes
and release the memory that is used by the point. Here's how:
EXEC SQL Free:cursor_name;

2, the function case:

intDbstbllinelist (intNOPR) {                                                                                                              intnList; inti; EXEC SQL DECLARE tbl_line_cfg_cur CURSOR FO                                                                         R SELECT gate_id, Line_ip, Line_port                                                                                                                                                          From Tbl_line_cfg ORDER by gate_id;                                                                            EXEC SQL OPEN tbl_line_cfg_cur; if(Sqlca.sqlcode! =0)                                                                                    { printf (ERROR,"error code [%d], reason[%s] \ n", Sqlca.sqlcode, (Char*) SQLCA.SQLERRM.SQLERRMC); return-1;                                                                                                                                                                                                                                                   } I=0; NList=1;  while(1)                                                                                                  { /*********************** * Fetch data from the library table , Query ************************/memset (Sagateid,0x00,sizeof(Sagateid)); memset (Salineip,0x00,sizeof(SALINEIP)); Nalineport=0;                                                      EXEC SQL FETCH tbl_line_cfg_cur into                                                : Sagateid,: Salineip,: Nalineport; if(Sqlca.sqlcode! =0&& Sqlca.sqlcode! =1403)                                      { printf ("fetch tbl_line_cfg_cur error sqlcode[%d]", Sqlca.sqlcode);                                                                                                                                            EXEC SQL CLOSE tbl_line_cfg_cur; return-1; }                                                                                     if(Sqlca.sqlcode = =1403)                                                            {                                                                                                                                            EXEC SQL CLOSE tbl_line_cfg_cur; I++; Lgatelist[i].nlist= -1;  Break;                                                                                                                                          } Lgatelist[i].nlist=nList; memcpy (Lgatelist[i].sagateid, Sagateid,2); memcpy (Lgatelist[i].salineip, Salineip, +);                   Trimspace (LGATELIST[I].SALINEIP); Lgatelist[i].nalineport=Nalineport; I++; NList++;                                                                                                                                                              } return 0; }

Pro*c function Case 3-cursor use