Method and apparatus for implementing cursor variables for accessing data from database6044216Abstract A method and apparatus for implementing a data construct, referred to herein as a "cursor variable", that has many of the attributes of simple variables and that can be used to access active sets of data from a database. Cursor variables identify a current set of data in an active set generated as a result of a database query. A cursor variable can be associated with any one of a number of different queries at any given time during program execution without using a host programming language. As a result, different active sets having different columns, different tables, and/or different predicates may be associated with the same cursor variable using instructions from a single programming language. Claims What is claimed is: Description BACKGROUND OF THE INVENTION
TABLE 1A
______________________________________
ename empno dept salary
______________________________________
Jeff 1 10 20,000
Jim 2 20 10,000
Joe 3 20 12,000
Jack 4 30 50,000
______________________________________
Thus, the statement: CURSOR cursor1 IS SELECT ename, empno FROM employee.sub.-- info; would generate a static cursor that could be used to access the active set shown below in Table 2. As shown in Table 2, the active set includes the ename and empno columns from the table employee.sub.-- info.
TABLE 2
______________________________________
ename empno
______________________________________
cursor1 -> Jeff 1
Jim 2
Joe 3
Jack 4
______________________________________
The OPEN, FETCH, and CLOSE statements are used to control static cursors. In response to the OPEN statement, the server portion of the database executes the query associated with the static cursor, identifies the active set, and positions the cursor before the first row. The OPEN statement has the following format: OPEN cursor.sub.-- name; Thus, executing the statement "OPEN cursor1;" the server would identify the active set shown in Table 2 and position the static cursor before the first row in the active set. The FETCH statement causes the server portion to advance the static cursor one row, retrieve the data in the current row, and store the retrieved data in the specified variables. The FETCH statement has the following format: FETCH cursor.sub.-- name INTO {record.sub.-- name .vertline. variable.sub.-- name[, variable.sub.-- name, . . . ]}; where record.sub.-- name represents a previously defined record variable and the one or more variable.sub.-- names represent one or more simple variables. The CLOSE statement disables the static cursor. The CLOSE statement has the following format: CLOSE cursor.sub.-- name; FIG. 1 is a block diagram illustrating certain aspects of static cursors. FIG. 1 shows an active set 100 comprised of data selected from one or more tables 110. As previously described, the active set 100 would include one or more columns and zero or more rows. A limitation of static cursors is that the number and names of the columns, the order of the columns, and the number and names of the tables, are fixed or static. Thus, a different static cursor must be defined for each query in which any of these attributes vary. For example, cursor1 above returns the columns ename and empno from table employee.sub.-- info. However, assuming a user is also interested in different columns from the same table, another static cursor would have to be declared. As an example, the statement: CURSOR cursor2 IS SELECT ename, dept FROM employee.sub.-- info; would generate a static cursor that could be used to access the active set shown in Table 3.
TABLE 3
______________________________________
ename dept
______________________________________
cursor1 -> Jeff 10
Jim 20
Joe 20
Jack 30
______________________________________
As another example, if the user is interested in all of the columns from the employee.sub.-- info table, another static cursor would have to be defined in the source code. Such a static cursor would be generated by the following: CURSOR cursor3 IS SELECT * FROM employee.sub.-- info; Thus, due to the static nature of the static cursor construct, three different static cursors are required to access different active sets from the same table. In addition, static cursors are limited in that the selected table(s) are static. For example, to generate an active set from the employee.sub.-- position table shown above in Table 1B, an additional static cursor would have to be defined in the source code (e.g., CURSOR cursor4 IS SELECT * FROM employee.sub.-- position;). Furthermore, the rows that are returned to the client application using a particular static cursor is limited by the predicate chosen at the definition of the particular static cursor. For example, if "empno=x" is used as the parameterized predicate, the user could not select rows that meet "empno>x" or "empno<x". In addition, at run-time the user cannot vary the rows returned using a static cursor based on criteria (e.g., values in other columns) that was not chosen when the static cursor was defined. Thus, a different static cursor must be declared in the source code of the client application for each such variation of active set needed by the user. These limitations make the static cursor construct cumbersome to program with. The inability to associate static cursors with different queries at run-time is addressed by another prior art cursor construct referred to herein as "dynamic cursors." According to the standard adopted by the American National Institute of Standards (ANSI), dynamic cursors have the limitation of requiring a "host programming language" or an "embedding language." Within source code written in the host programming language (referred to herein as the "host environment"), blocks of SQL statements may be embedded (referred to herein as "embedded SQL blocks"). Preprocessors can be implemented for a high-level programming language, such as C, COBOL, or ADA, to allow the high-level programming language to act as a host programming language. An "embedded SQL host program" is a compilation unit (i.e., source code to be compiled) that consists of statements in the host programming language and embedded SQL blocks. Any data that needs to be communicated between the host environment and an embedded SQL block must be placed in "embedded host variables." An embedded host variable is a construct generated by the execution of instructions written in the host programming language. An embedded host variable may be used in an embedded SQL block as well. A limitation of dynamic cursors is that each SQL statement identifier that is used to associate a dynamic cursor with a particular query must be unique in the executable image of an application. Having a unique name for each SQL statement identifier is difficult when a team of programmers is developing different parts of an application over a period of time. In addition, since the SQL programming language supports a different "type system" than prior art host programming languages, data must be converted between the data types supported by host programming languages and SQL. Furthermore, prior art database systems support the use of dynamic cursors on the client side, but do not support the use of dynamic cursors in stored routines on the server side. A further limitation of static and dynamic cursors is that they cannot be passed between routines as arguments. This greatly reduces the programming flexibility of the static and dynamic cursor constructs. For example, FIG. 2 is a block diagram illustrating the use of static and/or dynamic cursors in one database architecture. FIG. 2 shows a line 200 dividing the database architecture into a client side and a server side. On the client side, there resides a number of client applications 210. Each client application is typically executing on one node (e.g., a work station having one or more processors) or multiple nodes of a computer system. On the server side, there resides one or more sets of server code 220 executing on the same or different nodes of the computer system. One or more of the client applications 210 may be executed on the same nodes as one or more of the sets of server code 220. The client applications 210 and the sets of server code 220 communicate via an Application Program Interface. The Application Program Interface is a layer of software between the client applications and the server code. Unfortunately, since static and dynamic cursors cannot be passed as arguments between the client and server sides, the required statements to define, open, fetch, and close static and dynamic cursors must reside either all on the client side or all on the server side. For example, a server may be programmed to contain a first stored routine to open a static cursor and a second stored routine to fetch a row from that static cursor and return the row to the client. When a client application needs to retrieve rows from the database, the client application must call the first stored routine to open the static cursor and iteratively call the second stored routine to return one fetched row at a time. Assuming the client application needs 5,000 rows, 5,001 round trips will be performed--one round trip to open the static cursor and one round trip per row fetched. Due to the large number of stored routine calls required using this approach, the statements required to define, open, fetch, and close the required static and/or dynamic cursors are typically placed in the client applications. As illustrated by the following example, this greatly reduces the ability to alter installed client applications. Typically, a business needs a database to store information in a manner in which its employees (users) can easily access it. For example, a business would want to store payroll information in a manner in which the accounting department employees can easily manage the payroll. Such a business would contact a database supplier to provide the basic server code and database architecture. In addition, that business would contact a client application provider (this may be the same company that provides the server code). The business communicates to the client application provider the functionality its employees will require. The client application provider then writes and compiles the client application(s) (as well as any required stored routines to be added to the server code) for installation at the various client and server sites. Since each client application must contain the required static or dynamic cursor statements, adding new queries or modifying existing queries requires altering each client application at each client site. Since there can be thousands of client sites, this is a very time-consuming and expensive process. As an example, if the business decides that its employees require a different query from the database, then: 1) the client application source code would have to be altered, recompiled, and reinstalled at all the client sites; and/or 2) a patch would have to be distributed and applied at all of the client sites to alter the client applications. Due to the enormity of the task of updating every client site, it is common practice in the industry for the client application provider to provide only one recompile per database version change (e.g., Oracle 7 to Oracle 8). One technique used to avoid the limitation of not being able to pass static and dynamic cursors as arguments is to use collections to pass the results of static and dynamic cursors. To illustrate the technique of using collections, assume a variable of the collection type is declared. Each element in the declared collection variable is of a record type consisting of the appropriate fields to hold the result of a row returned by the desired query. The client application calls a stored routine on the server side. Execution of the stored routine executes a query using a static cursor, retrieves as many rows from the result set identified by the query as will fit in the collection variable, and returns the collection variable to the client application as an argument. Of course, the cursor used on the server side in this example cannot be a dynamic cursor because dynamic cursors cannot be used in stored routines. In addition to the limitations imposed by using a static cursor in the stored routine, the use of the collection variable suffers from additional limitations. For example, the use of collections to return the results of a query requires a significant amount of memory because the memory for the collection variable must be allocated on both the client and server sides. One approach to limiting the memory required to store the collection variable is to limit the number of elements in the collection variable (e.g., create a collection variable with 100 elements). Unfortunately, if the result set being returned has more rows than the number of elements in the array, the entire result set will not be returned. Alternatively, a collection variable can be declared to be of unlimited size. However, since a result set can include a large number of rows, the memory required to store the collection variable can be quite large. If the memory required to store the collection is not available, the technique of using collections is not viable. As another example, flexibility with respect to the number of rows returned to the client is reduced when using collections. As yet another example, the use of a collection to return query results is not evident from the argument list of a procedure. In contrast to static and dynamic cursor constructs, one database architecture includes an additional construct referred to herein as the "result set" construct. Results sets allow for the return of data according to one or more queries in the form of a single data stream. To create such a stream, one or more SELECT statements are written without INTO clauses in a stored routine. As a result of calling a stored routine that contains such a select statement, the data stream will be created. The contents of the stream may then be accessed by the client. A limitation of the result set construct is that the data identified by each of the select statements in the stored routine must be accessed sequentially. For example, assuming a first select statement identifies 5,000 rows from a first table and a second select statement identifies 4,000 rows from a second table. The data stream will sequentially provide the 5,000 rows from the first table followed by the 4,000 rows from the second table. As a result, if the client application needs the first 5 rows from both tables, it must sequentially process the 5,000 rows of the first table before reaching the first 5 rows from the second table. In addition, there is no way to back up in the data stream. Once a piece of data has been pulled off the data stream, another data stream must be opened to retrieve it again. Thus, if after processing the 5,005 rows in the above example, the client application determines it needs rows 50-100 from the first table, the client application must either: 1) have stored rows 50-100 when the client application pulled them form the data stream; or 2) open another data stream and re-process rows 1-100 from the first table. Another limitation of the result set construct is that the resulting data stream does not identify when one row ends and the next begins. Rather, for each select statement the data stream includes a "row type identifier" to mark the beginning of data accessed according to that select statement. To describe what a row type identifier is, it must first be understood that a data type must be specified for each column of a table indicating the type of data that can be stored in that column. For example, the first column of a table may contain character data, the second column may contain integer data, the third column may contain date information, etc. The columns selected from one or more database tables for a particular active set contain the same data types as defined for the selected database columns. Since each column of a database table stores a particular data type, the data types and the order of the data types for the data elements in each row of a database or active set is defined. In the previous example, the first three data elements in a row of data accessed from the database would respectively be of the character, integer, and date data type. The ordered list of data types defined for rows of a table or an active set is referred to as the table or active set's "row type." A row type identifier is a description of the row type defined by a select statement. Since the data stream does not identify when one row ends and the next begins, the client application must be sophisticated enough to detect the row type identifiers and decode the data stream (detect when each row begins and ends) using the format identified by the row type identifiers. In addition, since data accessed according to multiple select statements is placed serially in the data stream, the client application must be able to detect the row type identifiers to determine when the results from one SELECT statement end and the next begin. Another limitation of result set constructs is that result set constructs cannot be declared as arguments of routines, and therefore, cannot be passed as arguments between routines. In fact, the data stream generated by a result set is immediately passed back to the client. Another limitation of the result set construct is that it does not fall into any standard high-level programming language construct categories or SQL standard construct categories. Therefore, its use is not intuitive to programmers. The result set construct is described in greater detail in Transact-SQL User's Guide for SYBASE SQL Server.TM., Release 10.0, Sybase, Inc. (1993). SUMMARY OF THE INVENTION A method and apparatus for implementing a data construct, referred to herein as a "cursor variable", is provided. A cursor variable has many of the attributes of simple variables and can be used to access active sets of data from a database. According to one aspect of the invention, cursor variables are similar to static and dynamic cursors in that they identify a current row in an active set generated as a result of a database query. However, cursor variables differ from static and dynamic cursors in that a cursor variable can be associated with any of a number of different queries at any given time during program execution without using a host programming language. As a result, different active sets involving different columns, different tables, and/or different predicates may be associated with the same cursor variable using instructions from a single programming language. According to another aspect of the invention, cursor variables can be passed as arguments between various routines (e.g., between routines in a client application, between routines in a client application and stored routines, between stored routines in the same server, between stored routines in different servers, etc.). According to yet another aspect of the invention, each cursor variable can be strongly or weakly typed. A cursor variable is strongly typed by assigning it a particular row type. A strongly typed cursor variable can only be associated with "type-compatible" row types, while a weakly typed cursor variable can be associated with any row type. According to another aspect of the invention, one cursor variable can be assigned to another cursor variable. In addition, the assignment of one cursor variable to another cursor variable results in aliasing. BRIEF DESCRIPTION OF THE DRAWINGS The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which: FIG. 1 is a block diagram illustrating certain aspects of static cursors. FIG. 2 is a block diagram illustrating the use of static and/or dynamic cursors in one database architecture. FIG. 3 is a block diagram illustrating an exemplary computer system 300 upon which the invention can be implemented. FIG. 4 is a block diagram illustrating the variable nature of the query associated with a cursor variable according to one embodiment of the invention. FIG. 5 is a block diagram illustrating one technique for varying the query associated with a cursor variable according to one embodiment of the invention. FIG. 6 is a block diagram illustrating certain possible associations of a strong and a weak cursor variable according to one embodiment of the invention. FIG. 7 is a flow diagram illustrating the steps performed by a compiler in response to encountering a cursor variable definition according to one embodiment of the invention. FIG. 8 is a flow diagram illustrating the steps performed by a compiler upon encountering an open statement for a cursor variable according to one embodiment of the invention. FIG. 9 is a flow diagram illustrating the steps performed by a compiler in response to encountering an assignment statement assigning a first cursor variable to a second cursor variable according to one embodiment of the invention. FIG. 10 is a flow diagram illustrating the steps performed by a compiler in response to encountering a fetch statement that requires fetching from a cursor variable into one or more data structures according to one embodiment of the invention. FIG. 11 is a flow diagram illustrating the steps performed during run-time when executing a FETCH from a cursor variable into one or more data structures according to one embodiment of the invention. FIG. 12 is a block diagram illustrating the passing of cursor variables as arguments to a routine in an exemplary database according to one embodiment of the invention. FIG. 13 is a block diagram illustrating example classes that introduce REF CURSOR types according to one embodiment of the invention. DETAILED DESCRIPTION This application describes a method and apparatus for implementing a construct, referred to herein as a "cursor variable", that has many of the attributes of variables and that can be used to access active sets of data from a database. In the following description, numerous specific details are set forth to provide a thorough understanding of the invention. However, it is understood that the invention may be practiced without these specific details. In other instances, well-known circuits, structures and techniques have not been shown in detail in order not to obscure the invention. Although PL/SQL is used throughout this document to describe an implementation of the cursor variable construct, such a construct could be implemented using any number of other database languages. Hardware Overview FIG. 3 is a block diagram illustrating an exemplary computer system 300 upon which the invention can be implemented. The computer system 300 comprises a bus or other communication means 301 for communicating information, and a processing means 302 coupled with the bus 301 for processing information. The computer system 300 further comprises a random access memory (RAM) or other dynamic storage device 304 (referred to as main memory), coupled to the bus 301 for storing information and instructions to be executed by the processor 302. A main memory 304 also may be used for storing temporary variables or other intermediate information during execution of instructions by the processor 302. The computer system 300 also comprises a read only memory (ROM) and/or other static storage device 306 coupled to the bus 301 for storing static information and instructions for the processor 302. A data storage device 307, such as a magnetic disk or optical disk and its corresponding disk drive, may also coupled to the bus 301 for storing information and instructions. The computer system 300 can also be coupled via the bus 301 to a display device 321, such as a cathode ray tube (CRT), for displaying information to a computer user. An alphanumeric input device 322, including alphanumeric and other keys, is typically coupled to the bus 301 for communicating information and command selections to the processor 302. Another type of user input device is cursor control 323, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to the processor 302 and for controlling cursor movement on the display 321. This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), which allows the device to specify positions in a plane. Alternatively, other input devices such as a stylus or pen can be used to interact with the display. A displayed object on a computer screen can be selected by using a stylus or pen to touch the displayed object. The computer detects the selection by implementing a touch sensitive screen. Similarly, a light pen and a light sensitive screen can be used for selecting a displayed object. Such devices may thus detect selection position and the selection as a single operation instead of the "point and click," as in a system incorporating a mouse or trackball. Stylus and pen based input devices as well as touch and light sensitive screens are well known in the art. Such a system may also lack the keyboard 322, wherein all interface is provided via the stylus as a writing instrument (like a pen) and the written text is interpreted using optical character recognition (OCR) techniques. In one embodiment, the invention is related to the use of computer system 300 to implement a database that supports the data construct referred to herein as "cursor variables". In this embodiment, cursor variables are generated and manipulated by the computer system 300 in response to the processor 302 executing sequences of instructions contained in the memory 304 as described later herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions to implement the invention. Thus, the invention is not limited to any specific combination of circuitry and software. Cursor Variable Overview In one embodiment, cursor variables are similar to static and dynamic cursors in that they identify a current row in an active set generated as a result of a database query. However, cursor variables differ from static and dynamic cursors in that a cursor variable can be associated with any of a number of different queries at any given time during program execution without the use of a host programming language. As a result, different active sets involving different columns, different tables, and/or different predicates may be returned to the client application with the same cursor variable using instructions from a single programming language. In addition, cursor variables can be passed as arguments between various routines (e.g., between routines in a client application, between a routine in a client application and a stored routine, between stored routines in the same server, between stored routines in different servers, etc.). Furthermore, each cursor variable can be strongly or weakly typed. A cursor variable is strongly typed by assigning it a particular row type. A strongly typed cursor variable can only be associated with "type-compatible" row types, while a weakly typed cursor variable can be associated with any row type. In addition, cursor variables can be assigned to other cursor variables. Furthermore, cursor variables can be fully described in the database catalog. Additionally, Application Program Interfaces that support the return of argument information can be implemented to return information concerning cursor variables passed as arguments. FIG. 4 is a block diagram illustrating the variable nature of the query associated with a cursor variable according to one embodiment of the invention. Dashed lines are used to illustrate those aspects of the cursor variable that may be varied. FIG. 4 shows an active set 400 comprised of data selected from one or more tables 410. Similar to the active set 100 described with reference to FIG. 1, the active set 400 includes one or more columns and one or more rows. However, unlike the active set 100, the number and names of the columns, as well as the order of the columns, in the active set 400 can be altered. In addition, the tables from which the rows of data are accessed to generate the active set 400 can be varied. As previously described, the rows of the active set associated with a static cursor can only be varied through the use of the parameterized predicate chosen at the cursor's definition. However, unlike static cursors, the predicate used to define the rows of the current active set associated with a cursor variable can also be altered. Thus, like dynamic cursors, the entire query associated with a cursor variable may be altered during execution. However, unlike dynamic cursors, cursor variables do not require SQL statements be embedded in a host programming language. In contrast, client and server programs can be written in a single programming language that supports cursor variables. While one embodiment is described in which all of the above variations are possible, alternative embodiments may be implemented to include only some of the above described flexibility. FIG. 5 is a block diagram illustrating one technique for varying the query associated with a cursor variable according to one embodiment of the invention. During execution of a routine including a cursor variable, a cursor pointer space is allocated and an active set is determined for each query associated with that cursor variable (e.g., cursor pointer spaces 510 and 530, and active sets 520 and 540). Data (e.g., a physical pointer, a unique identifier, etc.) is stored in each cursor pointer space to indicate which active set it is associated with. For example, data is stored in the cursor pointer spaces 510 and 530 to indicate that they are respectively associated with the active sets 520 and 540. Since the queries associated with a cursor variable can differ, the active sets 520 and 540 can differ in any number of the above described ways, including: 1) the columns they contain; 2) the order of the columns they contain; 3) the rows they contain; and 4) the tables from which the data is accessed. In addition, a space in memory is allocated for the cursor variable (e.g., cursor variable space 550). A cursor variable can be associated with only one query at any given time. Within the cursor variable space 550, data is stored to identify the query to which the cursor variable is currently associated. For example, if the cursor variable is associated with the first query, the cursor variable space 550 contains data (e.g., a physical pointer, a unique integer value, etc.) that identifies the cursor pointer space 510, and thus, indirectly identifies the active set 520. However, the cursor variable may subsequently be associated with the second query by overwriting the data stored in the cursor variable space 550 with data that identifies the cursor pointer space 530. These associations are illustrated by dashed lines running from the cursor variable space 550 to the cursor pointer spaces 510 and 530. In this manner, the active set associated with the cursor variable can be changed. Using this technique, the cursor variable space 550 contains data that indirectly points to the active set to which the cursor variable is currently associated. While one embodiment of the invention has been described with reference to creating a separate active set for each query and having data in the cursor variable space indirectly point to the currently selected active set, alternative embodiments could use any number of mechanisms for changing the active set with which a cursor variable is associated. For example, in one alternative embodiment, a single active set could be created for each cursor variable. In such an embodiment, the server portion could simply alter/over-write that active set of the cursor variable each time a new query is selected. As another example, in another alternative embodiment, data could be stored in the cursor variable space that points directly to the currently selected active set, rather than using a cursor pointer space. Techniques for implementing and manipulating cursor variables shall now be described in greater detail. Creating Cursor Variables Statements for Creating Cursor Variables In one embodiment of the invention, creating cursor variables requires two steps: 1) defining a REF CURSOR type; and 2) declaring cursor variables to be of that type. Defining REF CURSOR types and declaring cursor variables is done using the following syntax:
______________________________________
TYPE type.sub.-- name IS REF CURSOR [RETURN return.sub.-- type]; --
define
REF--CURSOR type
cursor.sub.-- variable.sub.-- name type.sub.-- name; -- declare cursor
variable
______________________________________
where type.sub.-- name is a type specifier used in subsequent declarations of cursor variables to declare cursor variables of the REF CURSOR type identified by type.sub.-- name (e.g., cursor.sub.-- variable.sub.-- name), "--" indicates a comment, and return.sub.-- type stands for the following syntax:
______________________________________
{ record.sub.-- type
.vertline. database.sub.-- table%ROWTYPE
.vertline. cursor.sub.-- variable%ROWTYPE
.vertline. cursor%ROWTYPE
.vertline. record.sub.-- variable%TYPE}
______________________________________
The optional RETURN clause is used for defining strong REF CURSOR types, which in turn are used for declaring strongly typed cursor variables. Thus, the return.sub.-- type defines the row type (the data types and the order of the data types; also termed as the "return row type") that a row must be type-compatible with to be accessed using a strongly typed cursor variable (also referred to herein as a strong cursor variable). As an example, the following statements will result in the creation of a strongly typed cursor variable named deptcv:
______________________________________
TYPE EmpRecType IS RECORD (
empno NUMBER(4),
ename CHAR(10),
sal NUMBER (7,2));
TYPE EmpCurType IS REF CURSOR RETURN EmpRecType;
deptcv EmpCurType; --declare cursor variable
______________________________________
As shown above, a record type may be used to specify the return type of a cursor variable. In this case, the row type of the cursor variable deptcv is: NUMBER(4), CHAR(10), NUMBER (7,2). In addition, the %ROWTYPE syntax can be used to specify a record type that represents a row returned by a database table as shown below:
______________________________________
TYPE DeptCurType IS REF CURSOR RETURN dept%ROWTYPE;
dept.sub.-- cv DeptCurType;
______________________________________
where dept is a database table. Similarly, the %ROWTYPE syntax may be used for cursor variables or the static cursor construct. In addition, the %TYPE syntax can be used to provide the data type of a record variable as shown below:
______________________________________
dept.sub.-- rec dept%ROWTYPE; --declare record variable
TYPE DeptCurType IS REF CURSOR RETURN dept.sub.-- rec%TYPE;
dept.sub.-- cv DeptCurType; --declare cursor variable
______________________________________
While several formats for defining the return.sub.-- type are listed above, alternative embodiments could use more, less, or different structures to indicate the return.sub.-- type. FIG. 6 is a block diagram illustrating certain possible associations of a strong and a weak cursor variable according to one embodiment of the invention. FIG. 6 shows a weak cursor variable space 602 and a strong cursor variable space 604 for respectively storing data pertaining to a weak and a strong cursor variable. In addition, FIG. 6 shows a set of cursor pointer spaces 620, 622, and 624 storing data identifying their corresponding active sets 630, 632, and 634. Each cursor pointer space has stored therein data (e.g., a physical pointer to a location containing a row type descriptor, a row type descriptor, etc.) for identifying the row type of the active set to which they correspond. In FIG. 6, the cursor pointer spaces 620 and 622 are shown storing a pointer to matching row type descriptors (row.sub.-- type.sub.-- descriptor1), while the cursor pointer space 624 is shown storing a pointer to a different row type descriptor (row.sub.-- type.sub.-- descriptor2). In addition, the strong cursor variable space 604 also stores data (e.g., a physical pointer to a location containing a row type descriptor, a row type descriptor, etc.) for identifying the row type specified by the return type of the REF CURSOR statement used to define the type of that strong cursor variable. In FIG. 6, the row type of the strong cursor variable is the same as the row type of the active sets 630 and 632 (row.sub.-- type.sub.-- descriptor1). As a result, the row type for the strong cursor variable is type-compatible with the row types for the active sets 630 and 632. However, it is assumed that the row type of the strong cursor variable is not type-compatible with the row type of the active set 634. What is defined as a type-compatible row type is further described later herein. Through the use of dashed lines, FIG. 6 also illustrates a number of associations between the weak and strong cursor variables and the various active sets. Since weak cursor variables can be associated with any query, dashed lines 640, 642, and 644 are shown running from the weak cursor variable space 602 to each of the cursor pointer spaces 620, 622, and 624, respectively. In contrast, the strong cursor variable can only be associated with type-compatible row types. Thus, the strong cursor variable in FIG. 6 can be associated with the active sets 630 and 632 as illustrated by the dashed lines 650 and 652 running from the strong cursor variable space 604 to the cursor pointer spaces 620 and 622. However, the strong cursor variable cannot be associated with the active set 634. The inability of the strong cursor variable to be associated with the active set 634 is illustrated by a dashed line that runs from the strong cursor variable space 604 to the cursor pointer space 624 having an "X" on it. The techniques for associating a cursor variable with a particular active set will be described later herein. Compiling Statements for Creating Cursor Variables FIG. 7 is a flow diagram illustrating the steps performed by a compiler in response to encountering a cursor variable definition according to one embodiment of the invention. As shown in step 700, a cursor variable definition is encountered and flow passes to step 710. At step 710, it is determined if the cursor variable is strongly typed. This step is performed by parsing the REF CURSOR type definition statement to determine if a RETURN clause was included. If the cursor variable is strongly typed, flow passes to step 720. Otherwise, flow passes to step 730. In step 720, the return row type of the strong cursor variable is determined. As previously described, this step is performed by determining the ordered plurality of data types that rows must match to be accessed using this strongly typed cursor variable. Determining the return row type of the strong cursor variable is accomplished by processing the RETURN clause of the REF CURSOR type definition statement. From step 720, flow passes to step 740. At step 740, the necessary information to create the strongly typed cursor variable at run-time is stored in the executable code and flow passes to step 750. This information would include the row type descriptor determined in step 720. This row type descriptor is necessary for type checking during run-time as will be later described herein. At step 750, this branch of the flow diagram ends. As shown in step 730, the necessary information to create the weak cursor variable at run-time is stored in the executable code. Since the weak cursor variable is not tied to any return row type, there is no row type descriptor to be stored. From step 730, flow passes to step 760, where this branch of the flow diagram ends. While one embodiment has been described that supports both strong and weak cursor variables, alternative embodiments may support only one cursor variable type. Opening Cursor Variables Statements for Opening Cursor Variables Unlike the definition of static cursors, the definition of a cursor variable does not require a <sql query>. As a result, there is no query associated with a cursor variable when it is defined. In contrast, one embodiment supports an OPEN-FOR statement in which a <sql query>is used. Execution of an OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, and identifies the active set. Subsequently, different OPEN-FOR statements can open the same cursor variable for different queries. Thus, the OPEN-FOR statement is one way in which the various associations illustrated in FIG. 6 by the dashed lines 640, 642, 644, 650, and 652 may be created. Thus, the de-coupling of the query from the cursor variable definition allows for the flexibility illustrated in FIG. 4. The syntax of the OPEN-FOR statement is as follows: OPEN cursor.sub.-- variable.sub.-- name FOR <sql query>; While one embodiment is described that supports explicit use of a <sql query>in an OPEN-FOR statement, alternative embodiment could alternatively or additionally support the use of a text string variable in place of the <sql query>(e.g., OPEN cursor.sub.-- variable.sub.-- name FOR string.sub.-- variable.sub.-- name). During execution of an OPEN-FOR statement including such a text string variable, the text string variable would contain the text of a legal SQL query. Compiling Open Statements for Cursor Variables FIG. 8 is a flow diagram illustrating the steps performed by a compiler upon encountering an open statement for a cursor variable according to one embodiment of the invention. The flow diagram starts at step 800 when an open statement for a cursor variable is encountered. From step 800, flow passes to step 810. At step 810, the row type of the query specified by the select statement is determined. Techniques for performing this step are well known in the art. From step 810, flow passes to step 820. As shown in step 820, it is determined if the cursor variable is strongly typed. If the cursor variable is strongly typed, flow passes to step 830. Otherwise, flow passes to step 850. To perform step 820, the OPEN-FOR statement is parsed to determine which cursor variable is involved. The compiler then accesses the information previously stored concerning the definition of this cursor variable to determine if the cursor variable is strongly typed. At step 830, the row type of the strong cursor variable is type checked against the row type of the select statement. Since strong cursor variables can only be associated with type-compatible row types, step 830 is performed to determine if the row type of the query specified by the select statement is type-compatible with the row type of the strong cursor variable. To perform step 830, the row type descriptor for the cursor variable is compared with the row type descriptor for the select statement. In one embodiment, a "type-compatible query" is defined as requiring that the data types making up the row type of the select statement are identical to the corresponding data types making up the row type of the strong cursor variable (referred to herein as a "structural match"). In another embodiment, a type-compatible query is defined as requiring that the data types making up the row type of the select statement can at least be implicitly converted into the corresponding data types making up the row type of the strong cursor variable (a "convertible match" occurs when at least one implicit data type conversion is required). If the strong cursor variable and the query are determined to be type-compatible, then flow passes from step 830 to step 840. Otherwise, there is a row type "mismatch" and flow passes from step 830 to step 850 where a compile-time error message is generated. While in one embodiment, the permissible data type conversions are those supported by the current version of SQL (the various types of data conversions supported by SQL are well known in the art), alternative embodiments could support more, less, and/or different conversions. To provide an example, the reader is again referred to FIG. 6. If an OPEN-FOR statement is encountered by the compiler that attempts to associate the strong cursor variable with a query that returns active set 634, a type mismatch would be detected and a compile-time error would be generated. In contrast, if an OPEN-FOR statement is encountered by the compiler that attempts to associate the strong cursor variable with a query that returns either active sets 630 or 632, no compile-time error would be generated. At step 840, the necessary information to execute the open statement is stored in the executable code and flow passes to step 860 where the flow diagram ends. This information would include a reference to the previously stored cursor variable information, the text of the select statement (or a reference to a text string variable containing the text of a SQL query), and the row type descriptor. As shown by the flow diagram in FIG. 8, type checking is performed for the strong cursor variable named in the OPEN-FOR statement. However, type checking is not performed for weak cursor variables because they are not restricted to a particular row type. In an alternative embodiment that supports the use of a text string variable in an OPEN-FOR statement, the type checking may be performed at the time of execution of the OPEN-FOR statement when the contents of the text string variable is available. Executing Open Statements for Cursor Variables Upon execution, the information stored in step 840 will cause the execution of the query, the identification of the active set, and the association of the active set with the cursor variable. As an example, assume that the association represented by the dashed line 640 from FIG. 6 is created as a result of an OPEN-FOR statement. In this example, execution of the OPEN-FOR statement would result in: 1) the creation of active set 630; 2) the creation of the cursor pointer space 620; 3) the storing of data identifying the active set 630 and the row type of the active set 630 (e.g., row type descriptor1) in the cursor pointer space 620; and 4) the storing of data identifying the cursor pointer space 620 in the weak cursor variable space 602. In one embodiment in which a cursor variable may be reopened, the reopening of an existing cursor variable is performed by generating a new active set and updating the data stored in the existing cursor variable space and the existing cursor pointer space as necessary to identify the new active set (i.e., the existing cursor variable space and the existing cursor pointer space are reused). For example, assume the association illustrated by the dashed line 640 was established and the weak cursor variable in FIG. 6 was reopened (e.g., another OPEN-FOR statement was executed using a different query). In an embodiment that reuses the cursor variable space and the cursor pointer space, the new query is executed, the data in the cursor pointer space is updated to identify the new active set that resulted from the execution of the new query, and the row type descriptor is updated to describe the rows in the new active set. Assigning Cursor Variables Statements for Assigning Cursor Variables In one embodiment, a first cursor variable can be associated to the same active set as a second cursor variable. In this embodiment, the following syntax is used: cursor.sub.-- variable2 :=cursor.sub.-- variable1; To provide an example, the reader will again be referred to FIG. 6. For purposes of this example, it is assumed that the strong cursor variable is associated with the active set 630--the association illustrated by the dashed line 650. As a result, assigning the strong cursor variable to the weak cursor variable would result in causing the weak cursor variable to be associated with the active set 630. In one embodiment, this association is simply performed by copying the data that identifies the cursor pointer space 620 stored in the strong cursor variable space 604 into the weak cursor variable space 602. Thus, the concept of assigning cursor variables is similar to the concept of assigning pointers in high-level programming languages, and to assigning object references in object-oriented languages. As a further example, assume that after the associations illustrated by the dashed lines 640 and 650 were created, the weak cursor variable in FIG. 6 was reopened. As previously described, one embodiment is implemented to reuse the cursor variable space and the cursor pointer space when a cursor variable is reopened. In this embodiment, the new query is executed, the data in the cursor pointer space 620 is updated to identify the new active set (not shown) that resulted from the execution of the new query, and the row type descriptor is updated to describe the rows in the new active set. Since the data in the weak cursor variable space 602 and the strong cursor variable space 604 still identify the same cursor pointer space 620, both the weak and the strong cursor variables are associated with the new active set. In other words, the reopening of a cursor variable causes all cursor variables associated with the same active set to be associated with the new active set. This reassociation of multiple cursor variables by reopening a single cursor variable creates the need for additional type checking. As previously described, one embodiment is implemented to perform compile-time type-checking if the cursor variable specified in an the OPEN-FOR statement is a strong cursor variable. However, type-checking is not performed at compile-time for strong cursor variables that are not specified in the OPEN-FOR statements but which are associated with a new active set as a result of the reopening of another cursor variable. For example, again assuming the associations shown by the dashed lines 640 and 650 are established and the weak cursor variable is reopened, the reassociation of the strong cursor variable with the new active set is not type-checked at compile-time. In contrast, the strong cursor variable is type-checked with the row type of the new active set at run-time by comparing the row type descriptor associated with the strong cursor variable with the row type descriptor of the new active set. In one embodiment this run-time type-checking is performed upon execution of the OPEN-FOR statement that results in the reopening of the cursor variables. In an alternative embodiment, this run-time type-checking is performed only if required. For example, this run-time type-checking is required to execute statements that involve using the strong cursor variable to access data from the new active set (e.g., a FETCH statement), but is not required to execute an OPEN-FOR statement or a CLOSE statement. While one embodiment has been described in which cursor variable assignments result in aliasing, alternative embodiments could implement the assignment of cursor variables to act like the assignment of other variables. For example, assigning a first cursor variable associated with a first active set to a second cursor variable would result in the second cursor variable being associated with an independent copy of the first active set. Table 4 illustrates the four possible combinations of assignments between strong and weak cursor variables. Since weak cursor variables can be associated with any query, no type checking is required for assignments to weak cursor variables (cases 1 and 2 from Table 4). However, since strongly typed cursor variables can only be associated with type-compatible queries, assignments to strong cursor variables must be type checked (cases 3 and 4 from Table 4).
TABLE 4
______________________________________
Case Combination
______________________________________
1 weak.sub.-- cursor.sub.-- variable1 = weak.sub.-- cursor.sub.--
variable2;
2 weak.sub.-- cursor.sub.-- variable = strong.sub.-- cursor.sub.--
variable;
3 strong.sub.-- cursor.sub.-- variable = weak.sub.-- cursor.sub.--
variable;
4 strong.sub.-- cursor.sub.-- variable1 = strong.sub.-- cursor.sub.-
- variable2;
______________________________________
As previously described, assigning a cursor variable to another cursor variable results in aliasing. Due to aliasing, the two cursor variables identify the same result set and the same current row in that result set. If one cursor variable is used to advance the current row, both cursor variables will reflect the same result set and current row. The concept of aliasing is not supported by static cursors, dynamic cursors, result set constructs, or collections. Since aliasing is not possible, these prior art constructs suffer from the following limitations: 1) at different times the same query may return different sets of rows if any database object specified in the query is altered by the same or another program executing within the same transaction; and 2) creating multiple constructs for the same query consumes significant time and memory, whereas the assignment of a cursor variable only requires making the cursor variable point to a previously evaluated result set. Compiling Assignment Statements for Cursor Variables FIG. 9 is a flow diagram illustrating the steps performed by a compiler in response to encountering an assignment statement assigning a first cursor variable to a second cursor variable according to one embodiment of the invention. The flow diagram starts at step 900, from which flow passes to step 910. As shown in step 910, it is determined if both cursor variables are strongly typed (case 4 from Table 4). If both cursor variables are strongly typed, flow passes to step 920. Otherwise, flow passes to step 930. The types of the cursor variables are determined by polling the information previously stored concerning the cursor variables in response to compiling the respective definition statements for the cursor variables. At step 920, the strong cursor variables are type checked to determine if the row types of the strong cursor variables are compatible. Step 920 is performed by comparing the row type descriptors previously stored in response to compiling the respective definition statements for the cursor variables. In one embodiment, a "type-compatible assignment" requires the strong cursor variables be of the same REF CURSOR type (referred to herein as a "declaration type match"). In alternative embodiments, a type-compatible assignment could require at least a structural match or at least a convertible match. If the strong cursor variables are determined to be type-compatible, then flow passes from step 920 to step 940. Otherwise, there is a mismatch and flow passes from step 920 to step 930 where a compile-time error message is generated. In step 940, the information necessary to execute the assignment statement is stored in the executable code and flow passes to step 950 where the flow diagram ends. This information would include references to the information stored in response to compiling the respective definition statements of the cursor variables. In an embodiment that supports convertible matches, this information also includes the necessary code to perform any implicit data type conversions that are or may be required. As illustrated by FIG. 8, assignments from one strong cursor variable to another strong cursor variable are type checked at compile-time in one embodiment of the invention. This type checking can be performed because the compiler has already determined the row types associated with both strong cursor variables. In contrast, there are no row type descriptors permanently associated with weak cursor variables. As a result, the row type descriptor for a cursor variable depends upon the active set to which that cursor variable is currently associated. For example, the weak cursor variable in FIG. 6 will have a different row type descriptor when it is associated with active set 630 (e.g., row type descriptor1) than when it is associated with active set 634 (e.g., row type descriptor2). Thus, a row type descriptor is not associated with a weak cursor variable until an appropriate OPEN-FOR or assignment statement is executed. Furthermore, this row type descriptor may change if a subsequent OPEN-FOR or assignment statement is executed. Since executable code does not necessarily execute sequentially, branches from different areas in a program may lead to an assignment statement assigning a weak cursor variable to a strong cursor variable (case 3 in Table 4). These different areas of the program may associate this weak cursor variable with different active sets. As a result, it is unknown at compile-time what the row type of that weak cursor variable will be. Therefore, type checking assignment statements involving weak cursor variables when the row type is undetermined cannot be accurately performed at compile-time. To this end, one embodiment of the invention performs type checking for assignments from weak cursor variables to strong cursor variables during run-time, as opposed to during compile-time. Executing Assignment Statements for Cursor Variables A technique for performing type checking during run-time will be described with reference to FIG. 6. For purposes of this example, it is assumed that there is an assignment statement assigning the weak cursor variable to the strong cursor variable. If the weak cursor variable is currently associated with the active set 630, the row type of the weak cursor variable is described by row type descriptor 1. Upon attempting to assign the weak cursor variable to the strong cursor variable, the data stored in the weak cursor variable space 602 is used to identify the cursor pointer space 620. Then, the data stored in the cursor pointer space 620 is used to locate the previously stored row type descriptor for the active set 630 (row type descriptor1). In addition, the data stored in the strong cursor variable space 604 is used to locate the row type descriptor for the strong cursor variable (row type descriptor1). The identified row type descriptors are then compared to determine if the row types are type-compatible. In the present example, the row types are type-compatible and the assignment would not result in a run-time error message being generated. In contrast, if the weak cursor variable was associated with the active set 634, a run-time error would be generated because the row types would not be type-compatible. Any such run-time error could be handled using the usual mechanism of error handling (also termed as exception handling). For example, the client application could include exception handling code to be executed upon the occurrence of a row type mismatch error being generated. While one embodiment is described in which the type checking for assignments from one strong cursor variable to another strong cursor variable is performed at compile-time, alternative embodiments could perform this type checking at run-time using the technique described with reference to assignments from a weak cursor variable to a strong cursor variable. Furthermore, alternative embodiments may be implemented to perform type-checking for assignments from one strong cursor variable to another strong cursor variable at both compile-time and at run-time. Due to the nature of how aliased cursor variables may be reopened, embodiments that are implemented to perform type-checking for assignments from one strong cursor variable to another strong cursor variable at run-time may be implemented to ensure that both the cursor variables in the assignment are compatible with the active set. While one embodiment is described which supports the four cases of assignments in Table 4, alternative embodiment may support only some of these assignments. In addition, while one embodiment has been described in which the compiler does not perform type checking on assignments from weak cursor variable to strong cursor variables, alternative embodiments could attempt to provide some type checking for this case at compile-time. For example, a compiler may be implemented to determine all possible paths of execution for the program, and then try to determine all possible row types the weak cursor variable could be associated with upon reaching the assignment statement during execution. If the compiler could positively identify all possible row types the weak cursor variable could be associated with, the compiler would either: 1) generate a compile-time error if there was a row type mismatch; or 2) not generate a compile-time error if all the row types matched. Otherwise, the compiler would indicate a possible row type mismatch may occur at run-time. Fetching from Cursor Variables Statements for Fetching From Cursor Variables Once a cursor variable is associated with the active set that satisfies a particular multi-row query, FETCH statements can be used to retrieve rows from the active set. The syntax for the FETCH statement according to one embodiment of the invention is: FETCH cursor.sub.-- variable.sub.-- name INTO {record.sub.-- name .vertline. variable.sub.-- name[, variable.sub.-- name, . . . ] .vertline. collection.sub.-- name}; The text starting with "INTO" is referred to herein as the into-clause. One embodiment of the invention is implemented such that the into-clause of a FETCH statement may be a list of simple variables, a composite type, or a collection of identical data structures (e.g., an array of records). In response to such a FETCH statement in which the into-clause contains a collection of identical data structures, the server portion returns as many rows as will fit into the collection of identical data structures as a "bulk" fetch. Compiling Fetch Statements for Cursor Variables FIG. 10 is a flow diagram illustrating the steps performed by a compiler in response to encountering a FETCH statement that requires fetching data from the active set associated with a cursor variable into one or more data structures (e.g., a list of simple variables, a composite type, etc.) according to one embodiment of the invention. The flow diagram starts at step 1000, from which flow passes to step 1010. As shown in step 1010, it is determined if the cursor variable is strongly typed. Step 1010 is performed in the same manner as similar steps previously described herein. If the cursor variable is strongly typed, flow passes to step 1020. Otherwise, flow passes to step 1040. At step 1020, the strong cursor variable is type checked against the row type of the into-clause. The row type of the into-clause is defined by the ordered plurality of data types of the variables or fields in the one or more data structures. For example, if the one or more data structures is defined by a list of variables, the data types of those variables and the order in which those variables are listed will define the ordered plurality of data types that must be compared to the row type of the strong cursor variable. Thus, the number of variables or fields must equal the number of fields in the row type associated with the strong cursor variable. In addition, for each field in the row type associated with the cursor variable, there must be a corresponding, type-compatible variable or field in the into-clause. While one embodiment is described in which "a type-compatible fetch" is defined as requiring at least a convertible match, alternative embodiments could require something different (e.g., a structural match). If the row types of the strong cursor variable and the into-clause are determined to be type-compatible, then flow passes from step 1020 to step 1040. Otherwise, there is a "mismatch" and flow passes from step 1020 to step 1030 where a compile-time error message is generated. As shown in step 1040, the necessary information to execute the FETCH statement is stored in the executable code and flow passes to step 1050 where the flow diagram ends. This information would include a reference (e.g., a physical pointer) to the cursor variable, the necessary reference(s) to the one or more data structures, and a row type descriptor identifying the order and the data types defined by the one or more data structures. Executing Fetch Statements for Cursor Variables FIG. 11 is a flow diagram illustrating the steps performed during run-time when executing a FETCH from a cursor variable into one or more data structures according to one embodiment of the invention. The flow diagram begins at step 1100, from which flow passes to step 1110. At step 1110, the row type currently associated with the cursor variable is compared to the data types defined by the one or more data structures. For a weak cursor variable, the row type is determined by identifying the row type of the active set to which it is currently associated. For a strong cursor variable, the row type may be determined by either: 1) identifying the row type of the active set to which the strong cursor variable is currently associated; or 2) accessing the row type descriptor identified by data stored in the cursor variable space corresponding to the cursor variable. Due to the nature of how aliased cursor variables may be reopened, embodiments may be implemented to also type-check the row types of the strong cursor variable and the active set currently associated with that strong cursor variable to ensure that the strong cursor variable has not been reassociated with an incompatible active set. The row type determined for the cursor variable is compared to the row type descriptor for the one or more data structures previously stored by the compiler in step 1040. If there is a structural match, code is executed that causes the processor to retrieve the current row from the active set currently associated with the cursor variable and store the data in the one or more data structures (step 1120). However, if there is a convertible match, code is executed that causes the processor to retrieve the current row of the active set currently associated with the cursor variable, perform the required data type conversions, and store the resulting data in the one or more data structures (step 1130). If there is not a structural match or a convertible match, a type mismatch has occurred and a run-time error is generated (step 1140, where this branch of the flow diagram ends). Control passes from both step 1120 and 1130 to step 1150. In one embodiment, an indication is stored by the compiler during step 1040 of FIG. 10 for strongly typed cursor variables indicating whether there was a structural match or a convertible match (a mismatch would have resulted in a compile-time error). In such a case, this indication could be inspected in step 1110 rather than re-comparing the row type of the strong cursor variable with the row type descriptor for the one or more data structures. At step 1150, the current row pointer is advanced (the row that follows the current row becomes the new current row) and flow is passed to step 1160 where the flow diagram ends. This step is performed in a similar manner to static cursors. As shown above, strong cursor variables are type checked at compile-time. Of course, alternative embodiments could be implemented to perform the type checking for strong cursor variables at run-time in a similar manner. The type checking for weak cursor variables is performed at run-time because the row type descriptor for a weak cursor variable may be undefined at compile-time. Of course, alternative embodiments could be implemented to attempt to determine the possible row types for a weak cursor variable and perform the type checking in a similar manner to that previously described. As previously described, in one embodiment cursor variables introduce the possibility of aliasing. For example, both the strong and the weak cursor variable of FIG. 6 can be associated with the same active set (e.g., the active set 630) using an assignment statement. As a result, both cursor variables can be used to alter the state of the work area (e.g., perform a fetch). Thus, if a fetch is performed using the weak cursor variable that results in retrieving the first row, a subsequent fetch using the strong cursor variable will result in retrieving the second row, and so on. While one embodiment has been described in which cursor variables are aliased, alternative embodiments could implement the assignment of cursor variables in any number of ways. For example, the assignment of cursor variables could be implemented to act like the assignment of variables as previously described. In such an embodiment, assigning a first cursor variable associated with a first active set to a second cursor variable would result in the second cursor variable being associated with an independent copy of the first active set. As another example, an embodiment could be implemented in which cursor variables share an active set, but have separate current row pointers. In such an embodiment, assigning a first cursor variable associated with a first active set to a second cursor variable would result in the second cursor variable being associated with an independent cursor pointer space. Within the independent cursor pointer space there would be copied the same data from the cursor pointer space of the first cursor variable. Since separate cursor pointer spaces are used, accesses from one cursor variable would not affect accesses for the other. Passing Cursor Variables as Arguments Between Routines In one embodiment of the invention, cursor variables can be passed between routines as arguments. This includes the passing of cursor variables between routines in the same client, between routines in different servers, between routines in the same server, and between a routine in a client and a routine in a server. In one embodiment, the passing of cursor variables as arguments is performed in a similar manner to the passing of pointers as arguments in other languages. FIG. 12 is a block diagram illustrating the passing of cursor variables as arguments in an exemplary database according to one embodiment of the invention. FIG. 12 shows a line 1200 dividing the database architecture into a client side and a server side. On the client side, there resides a PL/SQL engine 1210 and executable code 1220. The executable code 1220 includes a routine 1222 and a routine 1224. Of course, the executable code 1220 includes additional code which is not necessary to understanding the invention. In response to commands from the user on the client side, the routine 1222 is executed using the PL/SQL engine 1210. In response to executing the routine 1222, the PL/SQL engine 1210 opens a memory space 1230 for executing the routine 1222. Since the routine 1222 includes a definition of a cursor variable, the PL/SQL engine 1210 generates a cursor variable space 1232 for storing data to associate the corresponding cursor variable with an active set of data that satisfies a query. In response to executing an OPEN-FOR statement in routine 1222, the PL/SQL engine 1210 creates a cursor pointer space 1240. In addition, the PL/SQL engine 1210 passes the query to the server side for execution. The server side includes a PL/SQL engine 1250 and a set of tables 1262. Of course, the server side may include additional items to those shown in FIG. 12 which are not necessary to understanding the invention. For purposes of this description, it can be assumed that the PL/SQL engine 1250 is identical to the PL/SQL engine 1210. In response to receiving the query from the PL/SQL engine 1210, the PL/SQL engine 1250 will cause the active set 1266 to be generated. In addition, a unique identifier is associated with the active set 1266. The identifier for the active set is sent back to the PL/SQL engine 1210. In response to receiving this identifier, the PL/SQL engine 1210 stores the identifier of the active set in the cursor pointer space 1240 for use in performing database accesses. In this manner, the cursor variable is linked to the active set 1266. The routine 1222 is shown including a call to another routine. This call requires the passing of the cursor variable as an argument. As previously stated, this call can be to another routine in this client or to a routine in the server (e.g., a stored routine). For purposes of explanation, it will first be assumed this call is to another routine in the same client (e.g., the routine 1224). In response to executing this call, the PL/SQL engine 1210 will allocate another memory space (memory space 1270) for executing the routine 1224. Since the cursor variable was passed as an argument, the PU/SQL engine 1210 will also create a cursor variable space 1272 within the memory space 1270 for storing data to associate the cursor variable with the currently selected query. In this example, the routine 1222 has already associated the cursor variable with the active set 1266 as indicated by the dashed line running from the cursor variable space 1232 to the cursor pointer space 1240. As a result, the data stored in the cursor variable space 1232 that identifies the cursor pointer space 1240 will be copied into the cursor variable space 1272. As a result, the data stored in the cursor variable space 1272 will identify the cursor pointer space 1240 as shown by the dashed line 1276. Upon completion of the routine 1224, the contents of the cursor variable space 1272 are copied into the cursor variable space 1232. It will now be assumed that the call in routine 1222 was to a stored routine on the server side. FIG. 12 also shows that the server side includes a set of stored routines 1280, including a stored routine 1282. Of course, the set of stored routines 1280 may include additional stored routines which are not necessary to understanding the invention. For purposes of this example, it is assumed that the call in routine 1222 is to the stored routine 1282. A call from the client side to a stored routine on the server side is referred to herein as a remote routine call (RRC) (a remote routine call to a stored procedure is referred to as a remote procedure call, while a remote routine call to a function is referred to as a remote function call). In response to executing this call, the PL/SQL engine 1210 will send the RRC to the PL/SQL engine 1250. In response to receiving this RRC, the PL/SQL engine 1250 will execute the stored routine 1282. To execute the stored routine 1282, the PL/SQL engine 1250 will allocate a memory space 1284 for executing the stored routine 1282. Since the cursor variable was passed as an argument to the stored routine 1282, a cursor variable space 1286 will be allocated within the memory space 1284 for storing data to associate the cursor variable with the active set 1266. In addition, the PL/SQL engine 1250 will copy the contents of the cursor variable space 1232 into the cursor variable space 1286, with the exception of the data that identifies the cursor pointer space 1240. Furthermore, the PL/SQL engine 1250 will allocate a cursor pointer space 1290 on the server side. The PL/SQL engine will copy into this cursor pointer space 1290 the contents of the cursor pointer space 1240, including the identifier used to identify the active set 1266. The PL/SQL engine 1250 will then store data in the cursor variable space 1286 to identify the cursor pointer space 1290 as illustrated by the dashed line 1292. In this manner, the cursor variable on the server side is associated with the active set 1266. Upon completion of the stored routine 1282, the contents of: 1) the cursor variable space 1286 are copied into the cursor variable space 1232, with the exception of the data that identifies the cursor pointer space 1290; and 2) the contents of the cursor pointer space currently identified by the contents of the cursor variable space 1286 (the cursor pointer space 1290 if no assignments occurred) are copied into the cursor pointer space 1240. As described above, cursor variables are passed back from the called routine to the calling routine in a similar manner. If in the calling routine a cursor variable argument has already been associated with a cursor pointer space (1240), no new cursor pointer space is created upon return, but the existing one is reused and updated, as needed. For example, assume that the execution of routine 1222 has not resulted in the association of the cursor variable with a query (i.e., an OPEN-FOR statement was not executed during the execution of routine 1222 prior to the call to stored routine 1282, the cursor pointer space 1240 has not been allocated, the cursor variable space 1232 does not contain data pointing to a cursor pointer space) prior to calling the stored routine 1282 and that the execution of stored routine 1282 results in the execution of an OPEN-FOR statement for the cursor variable. In this example, the execution of the OPEN-FOR statement in the stored routine 1282 would result in the creation of the cursor pointer space 1290, the cursor pointer space 1240 would be allocated upon return from the stored routine 1282, and the contents of the cursor pointer space 1290 would be copied into the cursor pointer space 1240. The above examples concerning the passing of arguments assumes that the argument was being passed from the routine 1222 into the other routines, as well as passed back from the called routines to the routine 1222. In fact, there are three basic ways to pass an argument: 1) passing the argument from the calling routine to the called routine, but not back from the called routine to the calling routine upon completion of the called routine (referred to as an IN argument); 2) passing the argument from the calling routine to the called routine, as well as back from the called routine to the calling routine upon completion of the called routine (referred to as an IN/OUT argument); and 3) only passing the argument from the called routine back to the calling routine (referred to as an OUT argument or function return value). The passing of cursor variables as arguments on the sever side is implemented in the same manner as on the client side. While one embodiment has been described in which cursor variables may be passed between the various entities in the database architecture (e.g., within a client, within a server, server to server, client to server), alternative embodiments may support less than all of these argument passing capabilities. In addition, alternative embodiment may pass cursor variables using any number of techniques (e.g., by value, by reference, etc.). For example, an embodiment could be implemented to pass the address of a cursor variable space as the argument. As another example, an embodiment could be implemented to pass the address of the cursor pointer space as the argument. As another example, the called or the calling routine could be responsible for allocating a scratch cursor variable space (e.g., cursor variable space 1272) for use during the execution of the called routine. The value stored in this scratch cursor variable space would then be passed back to the calling routine upon completion of the called routine and stored in the cursor variable space 1232. As another example, the scratch cursor variable space could be used only for cursor variables declared to be OUT or IN/OUT arguments, while the original cursor pointer space could be used for cursor variables declared to be IN arguments. Thus, any number of techniques could be used for passing cursor variables as arguments. However, regardless of the manner in which cursor variables are passed, they are passed as first class variables. As a result of passing cursor variables as arguments, type checking must be implemented where necessary. According to one embodiment, type checking is implemented in the same manner as described above with reference to assignment statements. Thus, one embodiment of the invention performs type checking for: 1) the passing of strong cursor variables as arguments to routines with strongly typed parameters at compile-time; and 2) the passing of weak cursor variables as arguments to routines with strongly typed parameters at run-time. Of course, alternative embodiments could perform all the required type checking at run-time or disallow some combinations of routine parameters and actual arguments being passed. For example, an alternative embodiment could allow only weak cursor variables to be passed as arguments. Example Code To provide an example of using stored routines to open cursor variables, consider the stored routine below, which searches the database of a main library for books, periodicals, and tapes. A master table (named titles ) stores the title and category code (1=book, 2=periodical, 3=tape) of each item. Three detail tables (named books, periodicals, and tapes) store category-specific information. When called, the stored routine (named find.sub.-- item) searches the master table by title, uses the associated category code to pick an OPEN-FOR statement, then executes the OPEN-FOR statement to open a cursor variable that is associated with an active set of data from the appropriate detail table.
______________________________________
CREATE PACKAGE cv.sub.-- types AS
TYPE LibCurTyp IS REF CURSOR;
. . .
END cv.sub.-- types;
CREATE PROCEDURE find.sub.-- item (title VARCHAR2, lib.sub.-- cv IN OUT
cv.sub.-- types.LibCurTyp, code OUT
BINARY.sub.-- INTEGER) AS
BEGIN
SELECT item.sub.-- code FROM titles INTO code WHERE item.sub.-- title =
title;
IF code = 1 THEN
OPEN lib.sub.-- cv FOR SELECT * FROM books
WHERE book.sub.-- title = title;
ELSIF code = 2 THEN
OPEN lib.sub.-- cv FOR SELECT * FROM periodicals
WHERE periodical.sub.-- title = title;
ELSIF code = 3 THEN
OPEN lib.sub.-- cv FOR SELECT * FROM tapes
WHERE tape.sub.-- title = title;
END IF;
END find.sub.-- item;
______________________________________
In the above code, a cursor variable type named LibCurTyp is defined in the package cv.sub.-- types. Next, the stored routine named find.sub.-- item is defined having as arguments a character string named title, a cursor variable of the LibCurTyp type named lib.sub.-- cv, and a variable of the BINARY.sub.-- INTEGER type named code. The SELECT statement causes the titles stored in the column named item.sub.-- title to be searched for a title that matches the contents of the argument title. When a match is found in a row, the data stored in the column named item.sub.-- code for that row is stored in code. The subsequent if-then-else statements are used to select the appropriate OPEN-FOR statement based on the value stored in code. The selected OPEN-FOR statement associates lib.sub.-- cv with an active set that includes the row from the table that contains the title. For example, if the input title is of a book, the first OPEN-FOR statement would be executed. This OPEN-FOR statement causes the creation of an active set having the row from the table books that contains the entered title. In an alternative embodiment that supports OPEN-FOR statements with text string variables, the stored routine shown above may alternatively be defined as follows:
______________________________________
CREATE PROCEDURE find.sub.-- item (title VARCHAR2, lib.sub.-- cv IN OUT
cv.sub.-- types.LibCurTyp, code
BINARY.sub.-- INTEGER) AS
query VARCHAR2 (100);
BEGIN
SELECT item.sub.-- code FROM titles INTO code WHERE item.sub.-- title =
title;
IF code = 1 THEN
query := `SELECT * FROM books
WHERE book.sub.-- title = title`;
ELSIF code = 2 THEN
query := `SELECT * FROM periodicals
WHERE periodical.sub.-- title = title`;
ELSIF code = 3 THEN
query := `SELECT * FROM tapes
WHERE tape.sub.-- title = title`;
END IF;
OPEN lib.sub.-- cv FOR query;
END find.sub.-- item;
______________________________________
A client side application at a branch library might use the following PL/SQL block to display the retrieved information:
______________________________________
DECLARE
lib.sub.-- cv
cv.sub.-- types.LibCurTyp;
book.sub.-- rec
books%ROWTYPE;
periodical.sub.-- rec
periodicals%ROWTYPE;
tape.sub.-- rec
tapes%ROWTYPE;
code BINARY.sub.-- INTEGER;
title VARCHAR2 (100);
BEGIN
get.sub.-- title (title); -- retrieve title name from patron at the
terminal
find.sub.-- item (title, lib.sub.-- cv,code); - call stored routine
find.sub.-- item with the entered title
IF code = 1 THEN
FETCH lib.sub.-- cv INTO book.sub.-- rec;
display.sub.-- book (book.sub.-- rec); -- call routine display.sub.--
book
ELSIF code = 2 THEN
FETCH lib.sub.-- cv INTO periodical.sub.-- rec;
display.sub.-- periodical (periodical.sub.-- rec); -- call routine
display.sub.-- periodical
ELSIF code = 3 THEN
FETCH lib.sub.-- cv INTO tape.sub.-- rec;
display.sub.-- tape (tape.sub.-- rec); -- call routine display.sub.--
tape
END IF;
END;
______________________________________
In the above code, the variables lib.sub.-- cv, book.sub.-- rec, periodical.sub.-- rec, tape.sub.-- rec, title, and code are declared. The "get.sub.-- title (title)" statement retrieves the title entered by a patron at a terminal. After retrieving the title, the stored routine find.sub.-- item is called to associate the cursor variable lib.sub.-- cv with the appropriate detail table based on whether the title is for a book, periodical, or tape. The subsequent if-then-else statements are used to select the appropriate FETCH statement and routine call based on the value stored in code. For example, if the input title is of a book, then: 1) the FETCH from lib.sub.-- cv will cause the contents of the row in the active set associated with lib.sub.-- cv to be stored in book.sub.-- rec; and 2)display.sub.-- book will be called to display information about the book. Relationship to the Object-Oriented Paradigm Cursor variables can be thought of as following the objected oriented paradigm used in a number of object-oriented languages (e.g., C++.TM., Smalltalk.TM., etc.). According to this paradigm, each cursor variable is viewed as an "instance" of a named "class" previously defined by the user. In one embodiment of the invention, the mechanism for generating cursor variables is encapsulated in a "system-provided class" (referred to herein as "generic REF CURSOR class") that may be included in an "object class library". In one implementation of this embodiment, this system-provided class does not have any "attributes", but has three "methods": OPEN, FETCH, and CLOSE (attributes and methods are well known concepts in object-oriented languages). Alternative embodiments may include any number of attributes and/or more, less, and/or different methods. According to this embodiment, user-defined REF CURSOR types would be naturally enabled through "inheritance" (a well known object-oriented concept). According to this concept, a class B is said to inherit from a class A if instances of class B possess all of the attributes and methods (if any) defined for class A (class B may additionally define attributes and methods unique to class B). A class that inherits from another class, that, in turn, inherits from one or more other classes is said to from a "class hierarchy." FIG. 13 is a block diagram illustrating example classes that introduce REF CURSOR types according to one embodiment of the invention. FIG. 13 shows the system-provided class Generic REF CURSOR Class 1300 and arrows directed from this class to a number of inheriting classes. As illustrated, a first weak REF CURSOR class 1310, a second weak REF CURSOR class 1320, a first strong REF CURSOR class 1340, and a second strong REF CURSOR class 1350 all inherit from the generic REF CURSOR class 1300. In contrast, a third weak REF CURSOR class 1330 inherits from the second weak REF CURSOR class 1320. This third weak REF CURSOR class includes as attributes: 1) "CURRENT-QUERY" identifying the SELECT statement currently associated with an instance of this class; and 2) "ROWCOUNT" identifying the number of rows that have been fetched from the CURRENT-QUERY. In this manner, the advantages of object-oriented programming can be realized for cursor variable. In one implementat | ||||||