KR100989453B1 - Method and computer system for publishing relational data to recursively structured XMLs by using new SQL functions and an SQL operator for recursive queries, and computer-readable recording medium having programs for performing the method - Google Patents

Abstract

Provides a new SQL function XMLNEST that is applied to a recursive query and generates one recursive XML value for each recursive result tuple tree formed by nodes corresponding to the recursive result tuples of the queries. In addition, for each node of the recursive result tuple tree, which is used as a direct and indirect argument of this XMLNEST, recursive result tuples corresponding to all nodes of the sub-tree whose root is the node are grouped. Provide NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, and NEST_MAX as relevant SQL functions of XMLNEST by calculating one value for each group and returning that value to the recursive result tuple corresponding to the root node representing that group. do. Furthermore, the recursive result tuples are classified by the recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong, and all the recursive result tuples of each classification having one or more recursive result tuples as members. It also provides a new SQL operator, tc_root, which returns a value for each of those groups. These new SQL functions and operators can be applied to DBMSs that support recursive queries.

Database, DB, Function, Hierarchy, Recursive Query, SQL, Operator

Description

A method and computer system for generating a recursive XML from a relational data using new escuel functions and operators in a recursive query, and a computer-readable recording medium recording a program implementing the method {Method and computer system for publishing relational data to recursively structured XMLs by using new SQL functions and an SQL operator for recursive queries, and computer-readable recording medium having programs for performing the method}

The present invention relates to a method for generating relation tuples as XML values through an SQL query, and more particularly, to a method for generating contents of tuples identified by a recursive query and a linkage relationship between the tuples as an XML value. The present invention relates to a processing method, a computer-readable medium having recorded thereon a program for performing the methods, and a database system using the same.

IBM's DB2 and Oracle's Oracle 10g support XML data types and already provide XQuery functions such as XMLQUERY, XMLTABLE, and XMLEXISTS that are expected to be included in the next SQL standard of the SQL: 2003 standard. As a method for generating XML values from relational tuples, it supports SQL queries including SQL / XML publishing functions of the SQL: 2003 standard in select clauses. Microsoft's SQL Server 2005 supports XML data types but does not support the SQL standard functions for generating XML values from XQuery queries and relational tuples on XML values stored in the database.

To generate XML values from relational tuples using SQL / XML publishing functions, you can write an SQL query that includes SQL / XML publishing functions in the select clause according to the content and structure of the XML value to be created. For example, consider creating an XML value from the tuples of the table employees (employee_id, first_name, last_name, birthdate, salary, supervisor_id, department_id) and the table departments (department_id, department_name, manager_id). The column employee_id representing the employee number in the table employees is the primary key and the supervisor_id representing the employee's employee number is the foreign key referring to the primary key of the table and the column department_id represents the department number to which the employee belongs. Is a foreign key that references the column department_id, which is the primary key of the table departments. In the table departments, the column manager_id representing the employee number of the manager of that department is a foreign key that refers to the primary key of the table employees. The tuples of table employees and table departments referenced in the rest of this document are shown in FIG.

Consider using a SQL / XML publishing function to create an XML value from a tuple of table departments corresponding to a department whose department number is 1 and a tuple of table employees corresponding to a manager of that department.

Example 1. Create an XML element "DEPARTMENT" for a department with department number 1 targeting table employees and table departments. However, the XML element "DEPARTMENT" has the XML elements "NAME" and "MANAGER" as children, and the XML element "MANAGER" has the XML elements "FIRST_NAME" and "LAST_NAME" as children. The XML element "DEPARTMENT" has the department number as the value of the attribute "id", the XML element "NAME" has the department name of the department as the text value of the element, and the XML element "MANAGER" the employee number of the manager of that department. With the value of the attribute "eno", the XML element "FIRST_NAME" has the name of the manager of that department as the text value of that element, and the XML element "LAST_NAME" the last name of the manager of the department as the text value of that element. Have

The result of evaluating the query created using the SQL / XML publishing functions for Example 1 and the query for the table employees and table departments of FIG. 1 is shown in FIGS. 2 (a) and 2 (b), respectively. In FIG. 2 (a), the SQL / XML publishing function XMLELEMENT creates the XML elements "DEPARTMENT", "NAME", and "MANAGER", and the SQL / XML publishing function XMLATTRIBUTES is the attribute "id" of the XML element "DEPARTMENT". Create the "eno" attribute of the XML element "MANAGER". In FIG. 2 (a), the SQL / XML publishing function XMLFOREST creates "FIRST_NAME" and "LAST_NAME" which are child elements of the XML element "MANAGER". The XML of FIG. 2 (b) is the result of random indentation for readability.

Consider a case where you use SQL / XML publishing functions to generate XML values from tuples of table employees connected in a boss-to-load relationship.

Example 2. A table with employees with employee number 10 and subordinates up to two levels below the employee, that person's subordinates and their subordinates, one for each employee. Create the XML element "EMPLOYEE". Provided that the XML element "EMPLOYEE" has the value of the employee_id of the employee as the value of the attribute "id" of the element and the child elements of the element "FIRSTNAME", "LASTNAME", "SALARY", "MANAGER", and Each has "SUBORDINATES" in that order. The XML elements "FIRSTNAME", "LASTNAME", "SALARY", and "MANANAGER" have the value of the element's tuple column first_name, last_name, salary, and supervisor_id, respectively, as the text values of the element. The XML element "SUBORDINATES" has "EMPLOYEE" elements created from its employees' subordinates as child elements.

The query created using SQL / XML publishing functions for Example 2 is shown in FIG. In the query of FIG. 3, the outermost select subquery retrieves the employee whose employee number is 10, and the select subqueries superimposed on the select subquery respectively select subordinates of the employee and subordinates of the subordinates. Withdraw.

The query of FIG. 3 includes the SQL / XML publishing functions specified in the select clause of the query, a tuple with a column employee_id of 10, tuples referencing the tuple with the value of the column supervisor_id, and the tuples again with the value of the column supervisor_id. Create a result tuple consisting of one XML value by applying tuples referred to as targets. The result is a tuple as shown in FIG.

The structure of the XML element "EMPLOYEE" of Figure 4, with one attribute "id" and child elements of "FIRSTNAME", "LASTNAME", "SALARY", "MANAGER", and "SUBORDINATES", is expressed as an XML schema graph. Same as FIG. 5.

Definition 1. When a structure of a particular XML value is represented as an XML schema graph, if the nodes of the graph and the edges connecting them form a cycle, the graph is a recursive XML value. The XML value represented by the graph is called a recursive XML value.

Node "EMPLOYEE" and node "SUBORDINATES" in Figure 5, and the edges connecting them form a cycle, so the XML schema graph of Figure 5 represents the XML value of the recursive structure and the XML value of Figure 4 represents the XML of the recursive structure. Value.

Definition 2. Among the nodes participating in the cycle in the XML schema graph, the node that starts the cycle is called the NEST_ROOT node and forms a cycle with the NEST_ROOT node as a child node of the NEST_ROOT node and has no child nodes except the NEST_ROOT node. Nodes that do not are called NEST_SUBORDINATE nodes.

In the case of FIG. 5, node "EMPLOYEE" is a NEST_ROOT node and node "SUBORDINATES" is a NEST_SUBORDINATE node. In this document, an NEST_ROOT node in a cycled XML Schema graph is specifically represented as a double ellipse, meaning that it is the starting node of the graph.

Definition 3. The XML element represented by the NEST_ROOT node in the XML schema graph is called the NEST_ROOT element, and the XML element represented by the NEST_SUBORDINATE node is called the NEST_SUBORDINATE element.

The query of FIG. 3 is a query that generates an XML value using only tuples for an employee having employee number 10 and subordinates up to two levels below the employee. However, if you want to generate the XML value of FIG. 4 as tuples for the employee and subordinates up to 10 levels below the employee, or for the terminal subordinates of the employee whose depth cannot be predicted, such a query may be used. To create a nested SQL query as shown in Figure 3 is not easy in the former case, the latter is impossible.

It is easy for the inventors to generate an XML value of a recursive structure that reflects the contents of the tuples and the connection relationship between the tuples from the tuples connected directly or indirectly by a given condition with the SQL query including the existing SQL / XML publishing functions. Recognize problems that are or are not possible. Furthermore, we recognized that to solve this problem, new SQL functions and operators need to be provided to effectively generate recursive XML values.

Based on this recognition, the present invention provides a new SQL function XMLNEST that is used in the select clause of a recursive query to generate tuples identified by the query as XML values of one recursive structure according to the linkage relationship between them. (2) define the relationship between XMLNEST and existing group functions of SQL, (3) define the relationship between XMLNEST and SQL / XML publishing functions, , (4) provide new SQL functions related to XMLNEST, NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, NEST_MAX, and tc_root as the SQLNEST-related SQL operator.

Another object of the present invention is to provide a method for generating an XML value of a recursive structure for a database by performing a recursive query for a database using the new SQL functions and operators.

Furthermore, another object of the present invention is to provide a method of returning a value for a group by treating the recursive result tuples into a group using the SQL operator.

Another object of the present invention is to provide a computer readable recording medium having recorded thereon a computer device for performing the above methods and a program for realizing the above method on the computer device.

According to the present invention for achieving the above object, a method of loading a process of a database management system (DBMS) in the processor of the computing system into the memory of the computing system to perform a query for the database, the database Performing a recursive query on to generate recursive temporary result tuples and recursive result tuples; A first determination step of determining whether each of the recursive temporary result tuple trees generates only one recursive result tuple tree for each of the recursive result tuple trees formed by the nodes corresponding to the recursive result tuples; A second judging step of determining whether a recursive result tuple corresponding to a root node of the recursive result tuple tree has a recursive temporary result tuple corresponding to a root node of the recursive temporary result tuple tree as an origin tuple; And generating an error signal when any one of the first determination step and the second determination step is not satisfied, and for each of the recursive result tuple trees when both the first determination step and the second determination step are satisfied. Searching for nodes constituting the tree and generating XML values of a recursive structure with tuples corresponding to the nodes belonging to the tree. A method of generating is provided.

Here, the recursive temporary result tuple tree expresses the tuples as nodes for the recursive temporary result tuples of the recursive temporary table generated in the process of performing the recursive query on the database, and the parent-child relationship Trees formed by connecting and representing nodes corresponding to tuples of a parent node from a direction edge toward a child node. And the recursive result tuple tree targets the recursive result tuples, and only if all of the recursive result tuples are generated by a singular copy of the recursive temporary result tuples. Trees formed by representing and representing nodes corresponding to tuples of a parent-child relationship with a direction edge from a parent node to a child node.

Preferably, the method is used in a SELECT clause of a recursive query of a DBMS that provides a recursive query form of its own or a DBMS that conforms to the recursive query form of the SQL: 1999 standard to retrieve recursive result tuples of that query. It is implemented as a SQL function of a specific name (XMLNEST) having the function of generating XML values of a recursive structure to be included as a part of the DBMS. Here, a representative example of a DBMS that provides its own recursive query form is Oracle 11g or higher.

In this method, upon generation of the recursive temporary result tuple, each of the recursive temporary result tuples has a tuple identifier root_tid of its root tuple, its tuple identifier tid, and a tuple identifier of its parent tuple ( parent_tid) and recursive temporary result tuple trees and recursive result tuple trees for the recursive temporary result tuples and the recursive result tuples using this identifier information <root_tid, tid, parent_tid>, respectively. It is preferable to construct.

The SQL function XMLNEST is a hierarchical group function that targets only a hierarchy group. The hierarchical group is a group formed by recursive temporary result tuples or recursive result tuples of a recursive query, and is defined as a collection of tuples having one tuple and an ancestor tuple of the tuple.

In the method, when the XML values of the recursive structure generated by the recursive result tuple tree are represented by an XML schema graph, the XML schema graph has (1) exactly one cycle, and (2) a NEST_ROOT node. Must have and can optionally have NEST_SUBORDINATE node, and (3) NEST_SUBORDINATE node satisfies condition that it has no child nodes other than NEST_ROOT node. Here, the NEST_ROOT node means the node that starts the cycle among the nodes participating in the cycle in the XML schema graph, and the NEST_SUBORDINATE node forms a cycle with the NEST_ROOT node as a child node of the NEST_ROOT node, and any child except the NEST_ROOT node. A node that does not have a node.

In addition, the SQL function XMLNEST can be used for (1) the name of the NEST_ROOT element and the XML attributes of the XML element, (2) the name of the NEST_SUBORDINATE element and the XML attributes of the XML element, and (3) the child elements of the NEST_ROOT element. Specification, and (4) a specification that specifies the alignment between sibling NEST_ROOT elements. Here, when expressing the XML values of the recursive structure generated by the recursive result tuple tree in an XML schema graph, a NEST_ROOT element is a NEST_ROOT node that is a node that starts the cycle among the nodes participating in the cycle in the XML schema graph. The NEST_SUBORDINATE element refers to the XML element represented by the NEST_SUBORDINATE node, which is a child node of the NEST_ROOT node, forms a cycle with the NEST_ROOT node, and has no child nodes except the NEST_ROOT node.

The SQL function XMLNEST may have the SQL / XML publishing functions XMLELEMENT, XMLCONCAT, XMLFOREST, and XMLATTRIBUTES as its input parameters.

In addition, the SQL function XMLNEST may be used as an input argument of XMLELEMENT, XMLCONCAT, and XMLFOREST, which are SQL / XML publishing functions of the SQL: 2003 standard.

SUM, COUNT, AVG, MIN, MAX, XMLAGG, the existing aggregate functions of SQL included with the SQL function XMLNEST in the SELECT clause of the recursive query, add the recursive result tuples of the recursive query to their origin tuples. There is a restriction that all recursive result tuples of each classification that have one or more recursive result tuples as members are classified by recursive temporary result tuple tree to which corresponding nodes belong.

The method reports the recursive result tuples corresponding to all nodes of the subtree whose root is the node for each node of the recursive result tuple tree, which is used only as a direct and indirect argument of the SQL function XMLNEST. It is preferable to further include 'XMLNEST related SQL functions' that calculate a value for each group and return the value to the recursive result tuple corresponding to the root node representing the group.

The XMLNEST related SQL functions include at least one of functions NEST_SUM, NEST_AVG, NEST_MIN, NEST_MAX and NEST_COUNT, which are functions of calculating a single value by applying a value expression specified as a function argument to each group of recursive result tuples. do. These functions NEST_SUM, NEST_AVG, NEST_MIN, NEST_MAX and NEST_COUNT apply their value expressions to each of the group's recursive result tuples, the sum, average, minimum, maximum, and value of the tuple whose values are not NULL. It is an SQL group function that returns a group value to each of the recursive result tuples by calculating each number and returning it to the recursive result tuple corresponding to the root node representing the group. These functions NEST_SUM, NEST_AVG, NEST_MIN, NEST_MAX and NEST_COUNT have the same specifications as SUM, AVG, MIN, MAX and COUNT, respectively.

The SQL function XMLNEST has a feature of a group function that processes one recursive result tuple tree into one group, so that a subquery including XMLNEST in a recursive query cannot include a group by clause.

The DBMS to which the method is applied is preferably a DBMS supporting recursive queries and SQL / XML publishing functions of the SQL: 2003 standard. More specifically, the DBMS is preferably an object-relational DBMS or a relational DBMS.

In the above method, for each of the recursive result tuple trees, the method for searching for nodes constituting the tree is preferably performed by a depth-first search method for the nodes.

According to another aspect of the present invention for achieving the above object, in the processor of the computing system to load a process of the database management system (DBMS) into the memory of the computing system to perform a query for the database, A method for performing recursive temporary result tuples and recursive result tuples generated by performing a recursive query on a database by executing an SQL hierarchical group operator of a specific name (tc_root). The operator tc_root is used for a recursive query in a DBMS that provides a recursive query form so that all recursive result tuples of that query are generated by singular copies of recursive temporary result tuples. Recursive interim result tuple tree to which nodes corresponding to origin tuples belong Computes data in a database, which has a function of returning one value for each group by treating all the recursive result tuples of each classification having one or more recursive result tuples as members. A method of processing is provided.

The function of the SQL hierarchical group operator tc_root preferably comprises: determining whether the recursive result tuples were generated by singular copy of the recursive temporary result tuples; Generating an error signal if the determination result of the singular replication determination step is false; And (i) classifying the recursive result tuples by the recursive temporary result tuple tree to which nodes corresponding to their origin tuples belong, if the judgment result of the singular replication judgment step is true, and (ii) For each group of recursive result tuples classified, the value expression specified as the argument of the operator tc_root using the column values of the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree corresponding to the group. It may include functions to perform a return step of calculating and returning a value.

The SQL hierarchical group operator tc_root takes an arbitrary value expression of the SQL: 2003 standard as an argument and uses the column values of the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree to obtain the value of the value expression. Contains the specifications that are generated.

In addition, the SQL hierarchical group operator tc_root has the characteristics of a group operator, but the groups targeted by tc_root are formed based on recursive temporary result tuple trees, so a subquery including tc_root in a recursive query may include a group by clause. There is a constraint that it can not.

SUM, COUNT, AVG, MIN, MAX, and XMLAGG, the existing aggregate functions of SQL included in the same subquery of the recursive query with the SQL hierarchical operator tc_root, respectively, add the recursive result tuples of the query to their origin tuples. All recursive result tuples of each classification that have one or more recursive result tuples as members are classified by each recursive temporary result tuple tree to which the corresponding nodes belong.

The SQL hierarchical group operator tc_root is an operator used for a recursive query of a DBMS that follows a recursive query form of the SQL: 1999 standard or a DBMS that provides its own recursive query form.

According to another aspect of the present invention for achieving the object of the present invention, there is provided a computer-readable recording medium having recorded thereon a program for executing each of the above-mentioned methods.

According to still another aspect of the present invention, there is provided a computer-readable medium having recorded thereon a program for executing each of the above-mentioned methods, a memory for loading the program from the medium, and a program loaded in the memory. A computer system comprising a central processing unit for executing is provided.

Meanwhile, according to another aspect of the present invention, a node corresponding to the recursive result tuples of the queries generated when the computer recursively queries a database of a database management system (DBMS) that supports a recursive query form. To implement a first function of constructing recursive result tuple trees and a second function of processing each recursive result tuple tree into a group and generating an XML value of one recursive structure for the tree. A computer-readable recording medium having a program recorded thereon is provided.

The program includes an SQL hierarchy group function XMLNEST that includes the first and second functions. In addition, the program is used only as a direct and indirect argument of the SQL hierarchical function XMLNEST, and for each node of the recursive result tuple tree, one recursive result tuple corresponding to all nodes of the subtree rooted at that node is given. It further includes 'XMLNEST-related SQL functions' that include the function of looking at a group of and calculating a value for each group and returning the value to a recursive result tuple corresponding to the root node representing that group.

Furthermore, the program is used for a recursive query of a DBMS providing a recursive query form so that all recursive result tuples of the query are generated by singular copying of recursive temporary result tuples. Are classified by recursive temporary result tuple tree to which nodes corresponding to their origin tuples belong to each group by processing all recursive result tuples of each classification having one or more recursive result tuples as members. It also includes the SQL hierarchy operator tc_root, which has the function to return a single value.

As described above, existing SQL / XML publishing functions defined in the SQL: 2003 standard alone generate recursively structured XML values that reflect the contents of the tuples and the linkages between them. It is not easy or impossible to write an SQL query. The present invention proposes the SQL function XMLNEST, XMLNEST related functions that can be used only as direct and indirect arguments of XMLNEST, and the SQL operator tc_root.

The SQL function XMLNEST proposed by the present invention is used in the select clause of a recursive query, and each recursive result is limited to the case where all nodes corresponding to the recursive result tuples form "root-maintained recursive" recursive result tuple trees. The recursive result tuples that form the tuple tree are treated as a group and the XML values of a recursive structure are generated for them.

The present invention proposes XMLNEST related functions that can be used only as direct and indirect arguments of XMLNEST, and calculates a value calculated using tuples corresponding to all nodes of each subtree of recursive result tuple trees. Made available for value generation.

The SQL operator tc_root proposed by the present invention is a node whose recursive result tuples correspond to their origin tuples only when all the recursive result tuples of the subquery including the operator are generated by singular copy of the recursive temporary result tuples. They are classified by the recursive temporary result tuple tree to which they belong, and all recursive result tuples of each classification having one or more recursive result tuples are treated as a group. The SQL operator tc_root can be used to sort XML values generated from XMLNEST as well as the function of the operator itself.

The present invention specifies the syntax and semantics of the SQL functions and SQL operators of the present invention in consideration of the SQL: 1999 standard and the grammar of recursive queries of commercial DBMSs, and to clarify the concepts of the SQL functions and SQL operators. A new concept group is proposed. The present invention defines the relationship between the existing aggregate functions of XMLNEST and SQL, the relationship between XMLNEST and SQL / XML publishing functions, and the relationship between the existing aggregate functions of tc_root and SQL.

The hierarchical group functions, such as XMLNEST and XMLNEST related functions, and the hierarchical group operator tc_root, are supported in a DBMS that supports recursive queries and SQL / XML publishing functions. In addition, existing SQL / XML publishing functions can generate XML values of various structures, including recursive structures, from relational tuples using only SQL queries.

In order to achieve the above object, the SQL function XMLNEST according to the present invention can be used only in the select clause of a recursive query. Generated as an XML value.

Recursive queries in the database are performed based on transitive closure (TC). The SQL: 1999 standard supports recursive queries. DB2 and SQL Server support recursive queries in the SQL: 1999 standard, and Oracle supports recursive queries with Oracle-specific syntax.

A recursive query in the SQL: 1999 standard is a select statement. The select statement (1) declares and defines temporary tables to hold the contents of the transitional closure as a with clause, and (2) creates final result tuples against the temporary tables and other tables as a subquery. . In the with clause of the Select statement, the "declaration and definition" of temporary tables is followed by the "declaration and definition" of one temporary table, followed by the "declaration and definition" of the next temporary table, and the "declaration and definition" of each temporary table. Consists of a declaration part that specifies the name of the temporary table, a list of column names in that table, and a definition part consisting of subqueries that specify the contents of the temporary table. The with clause of a recursive query can have "declarations and definitions" of temporary tables as well as temporary tables that contain the contents of a transitional closure.

The present invention introduces the syntax, semantics, and features of the SQL: 1999 standard recursive query based on the proposed SQL function XMLNEST and the SQL operator tc_root within the scope supported by commercial database management systems (DBMS). do.

The present invention uses the following terms 1 to 5 to describe recursive queries.

Terminology 1. The temporary table of the with clause that contains the contents of the redistribution is called a recursive temporary table and the tuples of the recursive temporary table are called recursive temporary result tuples (RTRTs).

Terminology 2. The definition of the contents of a recursive temporary table consists of root identification subqueries (root_finding_subqueries) and child identification subqueries (children_finding_subqueries).

Terminology 3. Root Identification Subqueries identify "starting tuples of transitional lungs" and initialize "recursive temporary tables" to include "rooted conditions of transitional lungs" (TC_root_conditions). When the table name of a recursive temporary table is T _A , the root identification subqueries in the definition of that table must not refer to table T _A in the from clause of those subqueries.

Term 4. Child Identification Subqueries are defined as "completely closed" by completing a recursive temporary table by repeatedly adding to the recursive temporary table a set of new tuples linked to existing recursive temporary result tuples of the recursive temporary table. Parent-child conditions "(TC_parent_child_conditions). When the table name of a recursive temporary table is T _A , child identifying subqueries should refer to table T _A in the from clause of those subqueries.

Terminology 5. A subquery that generates final result tuples on recursive temporary tables and other tables in a recursive query of the SQL: 1999 standard is called a closing subquery. In any subquery of the closing subquery, the from clause of the subquery contains at least one of the recursive temporary tables (directly in the from clause itself, not in the from clause of another subquery implied in the from clause). In that case, the subquery is called a subquery of the form "recursive result tuple". A closing subquery can have one or more "recursive result tuple generation" forms of subqueries. The conditions of a subquery in the form of "generating a recursive result tuple" are called filtering_conditions, and the tuples identified by the subquery by satisfying the filtering conditions are called recursive result tuples (RRTs). Each recursive result tuple has all the columns that the tuples that caused him to produce. The final result tuples of the subquery that caused the recursive result tuples to be generated are applied by applying the select clause of the subquery to the recursive result tuples. The remainder of this document assumes that the closing subquery itself is a subquery of the form "recursive result tuple" in order to simplify the description of the recursive query without impairing the generality of the recursive query.

6 is a query written to present an example of recursive temporary result tuples, recursive result tuples, and final result tuples in a recursive query. The with clause of the recursive query of FIG. 6 has a "declaration and definition" of the recursive temporary table empHierarchy consisting of columns of <employee_id, first_name, last_name, salary, supervisor_id, department_id>, and the closing subquery of the recursive query is The final result tuples of the closing subquery are generated for the recursive temporary table empHierarchy defined in the with clause and the table departments of FIG. The recursive query of FIG. 6 uses two tuples corresponding to an employee with employee_id 10 and tuples of FIG. 7 (a), which are tuples corresponding to all direct and indirect subordinates of that employee. To create. Since the process of identifying the recursive temporary result tuples is presented later in this document, the presentation of the process of generating the recursive temporary result tuples of the recursive temporary table empHierarchy of FIG. 7 (a) is omitted. Tuples generated as recursive result tuples of the closing subquery by satisfying the filtering conditions of the closing subquery of FIG. 6 from the recursive temporary table empHierarchy of FIG. 7 (a) and the table departments of FIG. Same as 7 (b). The closing subquery of the recursive query of FIG. 6 applies the select clause of the closing subquery to the recursive result tuples of FIG. 7 (b) to apply the tuples of FIG. 7 (c) to the final result tuple of the closing subquery. To create.

SQL Server and DB2 place restrictions on the order of root identification subqueries and child identification subqueries in the definition of certain recursive temporary tables. For SQL Server, all root identity subqueries in the definition of a particular recursive temporary table must be specified before all child identity subqueries. For DB2, not all root identification subqueries in the definition of a particular recursive temporary table must be specified before all child identification subqueries, but the first subquery specified in that definition must be the root identification subquery.

The set speakers provided by the relational DBMS to form a tuple set by concatenating tuple sets generated by multiple subqueries in the with clause of an SQL query include UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, and There is INTERSECT ALL.

SQL Server is a set of operators that can link root identification subqueries in the definition of a particular recursive temporary table. It supports all of the set operators provided by the relational DBMS except EXCEPT ALL and INTERSECT ALL, and identifies the last root of the definition. Only UNION ALL is supported as a set operator linking a subquery with the first child identification subquery and as a set operator linking the child identification subqueries. DB2 is a set of operators that can link root identification subqueries in the definition of a specific recursive temporary table. It supports all set operators except UNION among the set operators provided by the relational DBMS. DB2 supports root identification subqueries and child identification subqueries. Only UNION ALL is supported as a set operator that connects an identity subquery, a root identity subquery, and a child identity subquery.

8 shows the overall structure of a query in a recursive query of the SQL: 1999 standard consisting of a with clause and a closing subquery, and illustrates the internal structure of the declaration and definition of a recursive temporary table. FIG. 8 specifies only one recursive temporary table in order to emphasize the declaration and definition of the recursive temporary table, and the order of subqueries in the definition of the recursive temporary table is organized according to the order specified in SQL Server. In Figure 8, keyword recursive is not supported in DB2 and SQL Server. Therefore, the keyword recursive is omitted from the recursive query presented in the rest of this document.

The declaration portion of the recursive temporary table defined by the with clause of FIG. 8 is composed of a list of recursive_temp_table, which is the name of the table, and two column names constituting the table. It consists of root identification subqueries and four child identification subqueries. In the part of defining the contents of the recursive temporary table recursive_temp_table, set_operator is an arbitrary set operator for concatenating the root identification subqueries, and root_finding_subquery3 and the first root identification subquery among the subqueries defining the contents of the temporary table. The first child identification subquery, children_finding_subquery1, and the child identification subqueries are connected by the set operator UNION ALL. In the recursive query of FIG. 8, closing_subquery represents a closing subquery that generates recursive result tuples for temporary tables and other tables defined through the with clause of the query.

The recursion of the SQL: 1999 standard to indicate the detailed composition of root identification subqueries, child identification subqueries, and the closing subquery that terminates the recursive query in the definition of a particular recursive temporary table in the with clause of a recursive query. An example of generating only one recursive temporary table according to the grammar of an enemy query and forming a recursive query for it is shown in FIG. 9. The recursive temporary table declared and defined in the with clause of FIG. 9 is formed of only one root identification subquery and one child identification subquery.

Definition 4. The tables referenced in the from clause of each root identification subquery of the definition in the "declaration and definition" of a recursive temporary table of the SQL: 1999 standard recursive query base tables of the root identification subquery. (Base tables), other than the recursive temporary tables declared by the declaration, in the tables referenced in the from clause of each child identification subquery of the definition, the child identification subquery These are called base tables.

In the "declaration and definition" of a recursive temporary table, the base tables of the root identification subqueries of the definition and the base tables of the child identification subqueries of the definition may be different. However, the tuples identified by the subqueries are linked by set operators, so the number and order of the columns in the tuples must match and the data types must be compatible with each other.

The closing clause of a recursive query contains (1) recursive temporary tables declared and defined with the with clause in the recursive query, and (2) with the with clause in the recursive query in addition to the recursive temporary tables. Declared and defined temporary tables, and (3) other tables not defined in the recursive query can be specified.

Definition 5. Among the tables referenced in the from clause of a recursive query in the SQL: 1999 standard, except for the recursive temporary tables declared and defined in the with clause of the query, the recursive subquery of the recursive query These are called extra tables.

In the root identification subqueries of the "declaration and definition" of a particular recursive temporary table, the conditions specified in each route identification subquery are called "root conditions of the implementation alveoli," and the child identification subqueries of the "declaration and definition". The conditions specified in each child identification subquery in XX are referred to as "parent-child conditions of the implementation alveolus".

In the root identification subquery of the "declaration and definition" of a particular recursive temporary table, "root conditions of the redistribution" are divided into root join conditions (root_join_conditions) and root selection conditions (root_selection_conditions). Root join conditions are join conditions between the base tables of the root identification subquery, and route selection conditions are conditions that each tuple of each base table must satisfy, ie, conditions that exclude the root join condition from "Route conditions of the redistribution". . The result tuples of the root identification subquery of the "declaration and definition" of a particular recursive temporary table are the starting tuples of transitional closures that form the recursive temporary table.

If a root identification subquery has only one base table, the subquery cannot have root join conditions, so if the subquery has root selection conditions, the tuples of the base table that satisfy the root selection conditions will be Start tuples, and if the subquery does not have route selection conditions, all tuples in the base table become start tuples of the corresponding redistribution.

If a route identification subquery has multiple base tables, (1) if the subquery has only route selection conditions, it satisfies the route selection conditions in all tuples generated by the Cartesian product of the base tables. 2 tuples are the starting tuples of the corresponding redistribution, and (2) if the subquery has only root join conditions, the tuples generated from the tuples of the underlying tables are the starting tuples of the corresponding redistribution by satisfying the join conditions. (3) If the subquery does not have any "root closing conditions", then all tuples generated by the Cartesian product of its base tables become the starting tuples of the corresponding redistribution, and (4) the subquery " "Root conditions of transitional redundancy" if both route selection conditions and route join conditions are met, then the conditions are satisfied to the tuples of the underlying tables. Tuples created from this will be the starting tuples of the transitional closing.

Definition 6. Parent tuples, child tuples, and parent-child relationships between recursive temporary outcome tuples. For any recursive temporary result tuple t _p of a particular recursive temporary table, the recursion is applied directly by applying the "parent-child conditions of the redistribution" specified in the child identification subqueries in the "declaration and definition" of that table. When the set of recursive temporary result tuples included in the temporary temporary table is SC, the tuple t _p is called the parent tuple of the tuples of the set SC, and the tuples of the set SC are the child tuples of the tuple t _p ( child tuples), and the relationship between the tuple t _p and the tuples of the set SC is called a parent-child relationship.

Definition 7. Ancestor tuples, posterity tuples, and ancestor-descent relationships of recursive temporary outcome tuples. For any recursive temporary result tuple t _a of _a particular recursive temporary table, iteratively identified by satisfying the "parent-child conditions of the obsolescence" specified in the child identification subqueries in its definition. recursively temporary result included when La SD a set of tuples, set as descendants tuple (descendant tuple) for each tuple of the SD tuple t _a, and the ancestors of each tuple in the tuple t _a set SD tuple (ancestor tuple) The relationship between the tuple t _a and the tuples of the set SD is called an ancestor-descendant relationship.

In the child identification subquery of the "declaration and definition" of a particular recursive temporary table, the "parent-child conditions of implementation" are defined as parent-child_selection_conditions and parent-child_connection_condtions. ). Parent-child selection conditions are divided into parent conditions (child_conditions) and child conditions (child_conditions). Parent conditions are parent selection conditions (parent_selection_conditions), which are the conditions that the tuples of the recursive temporary table of the subquery must satisfy to be a parent tuple, which is a tuple with child tuples. The child conditions are the conditions that the tuples of the base tables of the child identification subquery must satisfy, and the child join conditions (child_join_conditions), which are join conditions between the base tables of the subquery, and each tuple of each base table of the subquery are satisfied. Conditions to be divided into child selection conditions (child_selection_conditions) are excluded from the child join conditions. Parent-child linkage conditions are join conditions between the recursive temporary table and the base tables of the child identity subquery.

If the child identification subquery does not have parent selection conditions, all tuples of the recursive temporary table of that subquery are candidates for being parent tuples and tuples to which the parent-child linkage conditions of the subquery are applied.

If a child identification subquery has only one base table, the subquery cannot have child join conditions, so if the subquery has child selection conditions, the tuples of the base table that satisfy the child selection conditions are defined by the subquery. If the subquery has no child selection condition, all tuples of the base table to which the parent-child linkage conditions will be applied are selected as tuples of the base table to which the parent-child linkage conditions of the subquery will be applied. Is selected.

If a child identification subquery has multiple base tables, then (1) if the subquery does not have child join conditions but has child selection conditions, the child selection conditions in tuples generated by the Cartesian product between the base tables Satisfying tuples are selected as tuples of the base tables to which the sub-query's parent-child linkage conditions are applied, and (2) if the subquery has child join conditions but no child selection conditions, Tuples generated from the tuples of the base tables are selected as tuples of the base tables to which the parent-child linkage conditions of the subquery are to be applied; The tuples of the base tables to which all tuples generated by the Sean product are to be applied to the parent-child linkage conditions of the subquery (4) if the subquery has both child selection conditions and child join conditions as child conditions, the tuples generated from the tuples of the base tables by satisfying the conditions are the parent-child connection of the subquery. Conditions are selected as tuples of the base tables to which they apply.

The filtering conditions of the closing subquery of the recursive query are: (1) extratables_conditions that the tuples of the side tables of the closing subquery must satisfy, (2) the recursive temporary tables and the side tables of the closing subquery. RecursiveExtraTables_join_conditions, and (3) conditions that the tuples of recursive temporary tables must satisfy (recursiveTables_closing_conditions).

The conditions that tuples of the side tables of the recursive subquery of the recursive query must satisfy the join conditions (extraTables_join_condtions) between the side tables of the recursive subquery and the conditions that each tuple of each side table of the closing subquery must satisfy. In other words, the tuples of the side tables of the closing subquery are divided into additional table selection conditions extraTables_selection_conditions, which are conditions except join conditions between the side tables. The conditions that the tuples of the recursive temporary tables in the closing subquery must satisfy are the join conditions (recursiveTables_closing_join_conditions) between the recursive temporary tables and the conditions that each tuple of each recursive temporary table must satisfy. Tuples are classified into recursive temporary result tuple selection conditions (recursiveTables_closing_selection_conditions), which are conditions excluding join conditions between recursive temporary tables.

Figure 10 illustrates the conditions specified in the recursive query of the SQL: 1999 standard. The meanings of the conditions of FIG. 10 specified in the recursive query are illustrated in FIG. 11. FIG. 11 (a) shows that starting tuples of the corresponding redistribution are identified by applying route join conditions and route selection conditions to the base tables of the root identification subquery in the definition of a specific recursive temporary table. Although not explicitly represented in Fig. 11 (a), if there are two or more root identification subqueries, the result tuples of each root identification subquery identified as in Fig. 11 (a) are a set operator connecting the root identification subqueries. They are concatenated according to their priorities and are generated as a set of tuples. 11 (b) shows the subquery generated in the base tables of the child identification subquery of the definition of the specific recursive temporary table and the existing tuples of the corresponding redistribution in the tuples generated by satisfying the child join conditions and the child selection conditions. Indicates that the tuples generated by applying parent-child linkage conditions of the subquery are added to the recursive temporary table repeatedly for tuples that satisfy the parent selection conditions of. If there are two or more child identification subqueries, the result tuples of the subqueries identified as shown in FIG. 11 (b) for each child identification subquery are added to the recursive temporary table. 11 (c) shows the addition of tuples generated by satisfying join conditions and recursive temporary table selection conditions between recursive temporary tables in recursive temporary tables of the from clause of the closing subquery and the adding clause of the closing subquery. Recursive result tuples are generated by applying join conditions between recursive temporary tables and side tables to tuples generated by satisfying join conditions between side tables and side table selection conditions in tables. Indicates that the final result tuples are generated by applying the select clause of the closing subquery to the result tuples.

Example 3. For each employee who does not have a boss in the table employees, draw <employee_id, first_name, last_name, salary, supervisor_id> of the tuples corresponding to that employee and all its direct and indirect subordinates.

If the query for Example 3 is written according to the SQL: 1999 standard, it is as shown in Fig. 12 (a). If the query is evaluated for the table employees of Fig. 1, the recursive temporary result tuples of the query are as shown in Fig. 12 (b). . In the recursive query of Fig. 12 (a), the root identification subquery of the definition of the recursive temporary table empHierarchy identifies tuples that satisfy the root selection condition "supervisor_id IS NULL" for the base table employees and identify the children of the definition. The subquery repeatedly identifies tuples that satisfy the parent-child linkage condition "e.supervisor_id = eh.emp_id" for the base table employees and the recursive temporary table empHierarchy.

In the tuples of FIG. 12B, tuples having employee_id of 10 and 21 having tuples of 21 are tuples identified by satisfying a root selection condition for the recursive temporary table empHierarchy because the supervisor_id is NULL. In the tuples of FIG. 12 (b), tuples with employee_id 11, 13, 14, 15, 16, and 17 are tuples corresponding to all direct and indirect subordinates of tuple with employee_id 10, and tuples with employee_id 23 and 24 are employee_id. As tuples corresponding to all direct and indirect subordinates of a tuple of 21, they are all tuples identified by the parent-child linkage conditions of the child identity subquery for the recursive temporary table empHierarchy.

The final subquery of the recursive query of FIG. 12 (a) has only the recursive temporary table empHierarchy and has no filtering conditions. Thus, the query creates all the tuples of the recursive temporary table empHierarchy as recursive result tuples. Since the select clause of the closing subquery of FIG. 12 (a) has all the columns of the recursive result tuple, the recursive result tuples of the query are generated as the final result tuples of the query.

Discussion 1. Closing Recursive Queries When a subquery has only one recursive temporary table, no side tables, and no recursive temporary result tuple selection conditions, all the recursive temporary result tuples that make up the recursive temporary table Are generated as recursive result tuples of the recursive query.

Example 4. Managers of departments whose department name is 'HR' or 'TFT' for table employees and table departments and <employee_id, first name, salary of all direct and indirect subordinates of each of those managers whose salary is over 30000 Fetch, supervisor_id, department_name>. However, (1) all direct and indirect subordinates of employees named CHANHO are excluded from the results, and (2) subordinates with salaries below 26000 and all direct and indirect subordinates of those employees are excluded from the results.

When the query for Example 4 is written according to the SQL: 1999 standard, it is shown in FIG. 13 (a). When the query is evaluated for the table employees and table departments of FIG. 1, the recursive temporary result tuples and the recursive result of the query are evaluated. The tuples are the same as in Figs. 13 (b) and 13 (c), respectively. In the recursive query of FIG. 13 (a), the root identification subquery of the definition of the recursive temporary table empHierarchy is the root selection condition "d.department_name = 'HR' OR d.department_name = Tuples that satisfy 'TFT' and satisfy the root join condition "e.employee_id = d.manager_id" are identified as starting tuples of the corresponding transitional closure. The child identification subquery in the definition is the parent selection condition "eh.first_name <> 'CHANHO'" for the base table employees, the base table departments, and the recursive temporary table empHierarchy, and the child selection condition "e.salary> =. 26000 ", the child join condition" e.department_id = d.department_id "and the parent-child connection condition" e.supervisor_id = eh.employee_id "are repeatedly identified. In the recursive query of FIG. 13 (a), the closing subquery fetches tuples satisfying the recursive temporary result tuple selection condition “salary> = 30000” from the recursive temporary table empHierarchy.

The tuples with employee_id of 10 and the tuples with 21 of the tuples of FIG. 13 (b) are tuples identified by satisfying the "root conditions of the pulmonary alveoli" in the definition of the recursive temporary table empHierarchy of FIG. 13 (a). According to the "parent-child conditions of the implementation alveoli" specified in the child identification subquery of the definition, a tuple with employee_id of 10 has tuples with employee_id of 11 and 16 as child tuples, and a tuple of employee_id of 16 has an employee_id of Have 14, 15 tuples as child tuples. Accordingly, tuples with employee_id 11, 14, 15, and 16 are included in the recursive temporary table. However, a tuple with employee_id of 14 does not satisfy the parent selection condition "eh.first_name <> 'CHANHO'" of the child identity subquery, so tuples with child tuples of employee_id of 13 and 17 in the recursive temporary table not included. A tuple with an Employee_id of 11 and a tuple with 15 have no child tuples. A tuple with an Employee_id of 24 is a child tuple of a tuple with an employee_id of 21 and is a candidate for inclusion in a recursive temporary table, but the tuple is recursive because it does not satisfy the child selection condition "e.salary> = 26000" in the child identity subquery. It is not included in the temporary table. The tuple with employee_id 23 is a child tuple of tuple with employee_id 21 and is included in the recursive temporary table. A tuple with Empoloyee_id of 23 has no child tuples. Thus, the tuples of the recursive temporary table empHierarchy in the query of FIG. 13 (a) are the tuples identified by the root identification subquery of the recursive temporary table, the tuples of employee_id 10, 21 and the child identification of the recursive temporary table. The tuples identified by the subquery are tuples whose employee_id is 11, 16, 14, 15, and 23, as in the tuples of FIG. 13 (b). Only tuples with employee_id of 21 in the recursive temporary result tuples of FIG. 13 (b) do not satisfy the recursive temporary result tuple selection condition "salary> = 30000" specified in the closing subquery of the query of FIG. 13 (a). Therefore, in the recursive temporary result tuples of FIG. 13 (b), the remaining tuples except for the tuple having employee_id 21 are generated as the recursive result tuples as shown in FIG. 13 (c). Since the select clause of the closing subquery of the recursive query of FIG. 13 (a) has all the columns of the recursive result tuple, the recursive result tuples of the query are generated as the final result tuples of the query.

The recursive query of FIG. 13 (a) is expressed in FIG. 11 using the notation method of FIG. 11. Fig. 14 (a) shows the root join condition "e.employee_id = d.manager_id" and the root selection condition for table employees and table departments, which are the base tables of the root identification subquery specified in the definition of the recursive temporary table empHierarchy. Indicates that the recursive temporary table empHierarchy is initialized with tuples created by applying "d.department_name = 'HR' OR d.department_name = 'TFT'". 14 (b) shows a child join condition "e.department_id = d.department_id" and a child selection condition "e.department_id = d.department_id" in table employees and table departments, which are base tables of the child identification subquery specified in the definition part of the recursive temporary table empHierarchy. Parent-child linking condition for tuples that satisfy .salary> = 26000 "and tuples that satisfy parental selection condition" eh.first_name <> 'CHANHO' "in existing tuples of recursive temporary table empHierarchy The "tu.employee_id = e.supervisor_id" is applied to add the tuples created to the recursive temporary table empHierarchy. FIG. 14 (c) shows tuples satisfying the recursive temporary result tuple selection condition “salary> = 30000” in the recursive temporary table empHierarchy referenced in the from clause of the closing subquery, and generates recursive result tuples. Indicates that final result tuples are generated by applying the select clause of the closing subquery to recursive result tuples.

The relationship between recursive temporary result tuples and recursive result tuples can be classified as follows according to the recursive temporary tables constituting the closing subquery of the recursive query, the side tables of the subquery, and the filtering conditions. have.

One recursive temporary result tuple produces at most one recursive result tuple.

A recursive temporary result tuple produces more than one recursive result tuple.

If there is only one recursive temporary result tuple that caused one recursive result tuple to be generated.

Two or more recursive temporary result tuples that result in a single recursive result tuple.

Definition 8. Relation between recursive ad hoc tuple and recursive result tuple. In the closing subquery of a recursive query, if a recursive result tuple produced by _a recursive temporary result tuple t _a is t _a ′, then t _a is the origin tuple of t _a ′ (original tuple) T _a ′ is called _a reincarnated tuple of t _a .

Definition 9. The union between recursive ad hoc result tuples. If the closing subquery of a recursive query references more than one recursive temporary tables in the from clause, recursive result tuples are created as Cartesian products or joins between the recursive temporary tables. In this case, the recursive result tuples are said to be created as a bond between the recursive temporary result tuples.

Thus, if a recursive result tuple has two or more origin tuples, the recursive result tuple is a tuple produced by the combination between the origin tuples.

Definition 10. Replica of Recursive Temporary Results Tuples. If the closing subquery of a recursive query references only one recursive temporary table in the from clause, the recursive result tuples are recursively by the recursive temporary table if there is no side table and the recursive temporary table if there is one. It is created by a Cartesian product or join of the temporary table and its side table (s). In this case, recursive result tuples are said to be created by replication of the recursive temporary result tuples of the recursive temporary table.

Replication of recursive temporary outcome tuples is divided into single replication and multiple replication according to the number of recurrences of origin tuple. When a recursive temporary result tuple is replicated with only one recursive result tuple, it is referred to as singular replication.

For example, the closing subquery of the query of FIG. 12 (a) and the closing subquery of the query of FIG. 13 (a) have only one recursive temporary table and no additional tables, so the recursive result tuple of each of the queries is Are tuples produced by a singular copy of the recursive temporary result tuples.

Consider an example where recursive result tuples are generated by multiple copies of recursive temporary result tuples.

Example 5. Define a recursive temporary table that obtains tuples corresponding to an employee with employee_id 21 in the table employees and all the direct and indirect subordinates of that employee, and then Cartesian multiplies the recursive temporary table by table departments < Create employee_id, supervisor_id> as recursive result tuples.

When the query for Example 5 is written according to the SQL: 1999 standard, it is as shown in FIG. 15 (a). When the query is evaluated for the table employees and table departments of FIG. 1, the recursive temporary result tuples of the query are shown in FIG. ) And the recursive result tuples of the query are shown in FIG. 15 (c). The final subquery of the recursive query of FIG. 15 (a) has only the column employee_id and supervisor_id of the recursive result tuples of FIG. 15 (c) to generate the final result tuples of the query.

In the closing subquery of the query of FIG. 15 (a), the recursive temporary table empHierarchy is multiplied by the table departments of FIG. 1 having four tuples, so each recursive temporary result tuple of FIG. 15 (b) has four tuples. A plurality of copies of the tuples of FIG. 15 (c) are generated as recursive result tuples. That is, each recursive temporary result tuple of FIG. 15 (b) has four reincarnation tuples and each recursive result tuple of FIG. 15 (c) has only one origin tuple.

Consider the case where recursive result tuples are generated by combining between recursive temporary result tuples.

Example 6. Define a recursive temporary table empHierarchy1 that obtains <employee_id, supervisor_id, first_name> of tuples corresponding to an employee with employee_id 10 in the table employees and all direct and indirect subordinates of the employee, and employee_id in the table employees and table departments. After defining the recursive temporary table empHierarchy2 to obtain <employee_id, supervisor_id, department_name> of the tuples corresponding to the employee having a value of 14 and all the direct and indirect bosses of the employee, the join condition "empHierarchy1.employee_id = empHierarchy2." Generate final result tuples consisting of the columns of <employee_id, first_name, department_name> of the recursive result tuples identified from the recursive temporary tables by satisfying "employee_id".

When the query for Example 6 is written according to the SQL: 1999 standard, it is as shown in FIG. 16 (a). When the query is evaluated for the table employees and table departments of FIG. 1, the recursion of the recursive temporary table empHiearchy1 and the recursive temporary table empHierarch2 is performed. The preliminary temporary result tuples are shown in Figure 16 (b) and Figure 16 (c), respectively, and the recursive result tuples of the query are shown in Figure 16 (d). The final of the recursive query of FIG. 16 (a) by having the column employee_id of the recursive temporary table empHierarchy1 and the column first_name and the column department_name of the recursive temporary table empHierarchy2 which caused them to be generated from the recursive result tuples of FIG. 16 (d). Result tuples are generated.

Each of the recursive result tuples of FIG. 16 (d) has a particular tuple of FIG. 16 (b) and a particular tuple of FIG. 16 (c) as the origin tuples of the tuple. That is, the recursive result tuples are tuples produced by the combination between the recursive temporary result tuples.

Consideration 2. If all recursive result tuples of a particular recursive query were generated by singular copies of recursive temporal result tuples, the recursive tuple of any recursive temporal result tuple of that query is only one, and any recursive result tuple of There is only one origin tuple.

Definition 11. Parent tuple, child tuple, and parent-child relationship between recursive outcome tuples. T _p is the parent for two recursive temporary result tuples of parent-child relationship, t _p and t _c , provided that all recursive result tuples of the recursive query are generated by singular copies of the recursive temporary result tuples. tuples and t _c is a child-to-peulil time, t _p of rebirth tuples yi t _p 'and this t _c reincarnation of t _c tuple "is t _p, and t _c' is a parent-a, and t _p, the child relationship t _c It is called the parent tuple of ′ and t _c ′ is called the child tuple of t _p ′.

Discussion 3. If t _p and t _c have one or more reincarnation tuples for t _p and t _c , the recursive temporary outcome tuples of the parent-child relationship according to definition 11, and two or more reincarnation tuples of t _p or t _c There is no parent-child relationship between any reborn tuple of t _p and any recurring tuple of t _c (ie, if t _p or t _c were replicated in plural).

4. Consideration defines 11 recursive results, which are tuple t _p 'and t _c, t _p' of origin tuple t _p a t _c 'origin tuple t _c t _p a parent tuple even' or t _c 'with respect to according to the If generated by a combination of recursive temporary result tuples, no parent-child relationship is established between t _p 'and t _c '.

For example, the recursive result tuples of FIGS. 12 (b) and 13 (c) are all generated by singular copies of recursive temporal result tuples, so a parent-child relationship is established between the recursive result tuples. However, the recursive result tuples of FIG. 15 (c) are generated by plural copies of the recursive temporary result tuples, and the recursive result tuples of FIG. 16 (d) are generated by combining the recursive temporary result tuples. There is no parent-child relationship between tuples.

Definition 12. Ancestor tuples, posterity tuples, and ancestor-descent relationships of recursive outcome tuples. All recursive result tuple of recursive queries will only when produced by the single cloning of the recursive temporary result tuples, ancestor-descendant of recursive temporary result tuples, which are t _a and t _d t _a is t _d with respect to the relationship ancestors tuple and t _d that t _a of offspring to-peulil case, if the recursive results reincarnation of t _a in the set of tuples tuple is t _a 'and the reincarnation tuple of t _d t _d' t _a 'to t _d' as ancestor tuple is referred to as a descendant of a tuple t _d 'a _a t', and t _{a 'd} and t' are ancestor-descendant relationship is said to be in a.

Definition 13. Represent the tuples as nodes for the recursive temporary result tuples of a specific recursive temporary table, and connect the nodes corresponding to the tuples of the parent-child relationship with the direction edge from the parent node to the child node. Trees formed by representation are called recursive temporary result tuple trees (RTRT-trees).

In a particular recursive temporary result tuple tree, the length of the path from the root node to a particular node is called the depth of that node, plus the depth of any node in that tree, plus the level of that node ( level). In a recursive query of the SQL: 1999 standard, it is possible to make recursive result tuples only tuples corresponding to 1 to a certain level of nodes of a recursive temporary result tuple tree. For example, the root identity subquery is defined by declaring level in the with clause as the result column of a particular recursive temporary table and setting the level column value of the select clause of the root identity subquery specified in the definition of the recursive temporary table to 1. Initializes the column level of the result tuples of to 1 and sets the level column value of the select clause of the child identification subquery of the definition to the value of the result tuple of the child identification subquery of the parent tuple. If a column level value is set, each of the tuples of the recursive temporary table has the level in the recursive temporary result tuple tree of the node corresponding to the tuple as the column level value of the tuple. Therefore, if "level <= N" is specified as the recursive temporary result tuple selection criterion in the closing subquery of the recursive query, the recursive values corresponding to nodes having a level from 1 to N in the recursive temporary result tuple trees Only temporary result tuples are generated with the recursive result tuples of the query.

Definition 14. For a recursive result tuple of a particular recursive query, express both the tuples as nodes and only if all of the recursive result tuples are generated by a singular copy of the recursive temporary result tuples. Trees formed by connecting nodes corresponding to tuples of a child relationship with a direction edge from a parent node to a child node are called recursive result tuple trees (RRT-trees).

The closing subquery of the query of FIG. 12 (a) has only one recursive temporary table empHierarchy and no recursive temporary result tuple selection conditions. Therefore, by consideration 1, the recursive temporary result tuple trees of the query and the recursive result tuple trees of the query are the same. 17 shows the recursive result tuple trees of the query. For reference, sibling nodes of FIG. 17 are intentionally arranged in ascending order of the column employee_id value.

The recursive result tuples of FIG. 15 (c) were generated by multiple copies of the recursive temporary result tuples, and the recursive result tuples of FIG. 16 (d) were generated by combining between the recursive temporary result tuples. Thus, the recursive result tuples do not form recursive result tuple trees.

Definition 15. For a recursive temporary result tuple tree (s) formed by tuples of a particular recursive temporary table, leave a virtual parent node for the root node (s) of that tree (s) and then recreate it from that virtual parent node. Connecting the directional edges towards the root node (s) forms a tree. This is called a virtual recursive temporary result tuple tree (VRTRT-Tree).

For example, a virtual recursive temporary result tuple tree formed by placing a virtual parent node for the root nodes of the two recursive temporary result tuple trees of FIG. 17 is shown in FIG. 18.

Oracle provides a with clause for defining temporary tables, but does not support the definition of recursive temporary tables through that with clause. Oracle provides the functionality of recursive queries in the SQL: 1999 standard as Oracle-specific queries called hierarchical queries. The syntax of Oracle's hierarchical query is shown in FIG. In the rest of this document, Oracle's hierarchical query is called Oracle's recursive query.

As shown in FIG. 19, Oracle's recursive query is a select statement including a connect by clause, and has the following features 1 to 3 compared to the recursive query of the SQL: 1999 standard.

Features 1. There is only one number of recursive temporary tables created by Oracle's recursive queries.

Feature 2. In Oracle's recursive query, the starting tuples of transitional redistribution that make up the recursive temporary table and the descendant tuples of each of those tuples are generated for the same base table.

Feature 3. Recursive queries in Oracle do not have side tables.

Oracle's recursive query does not have join conditions between recursive temporary tables among the conditions that make up the filtering conditions specified in the SQL: 1999 standard recursive query subquery by feature_1 and recursively by feature_3. There are no join conditions between the temporary table and the side table, join conditions between the side tables, and side table selection conditions. Thus, Oracle's recursive query has only recursive temporary result tuple selection conditions as filtering conditions. In Oracle's recursive query, the recursive temporary result tuple selection conditions are specified in the where clause of the query.

Oracle's recursive query is characterized by the join conditions that are only applied to generate the starting tuples of the transitional redistribution by feature_2, the join conditions that are applied only to generate the descendant tuples of each of those tuples, and the starting tuple of the transitional closure. And join tuples that are commonly applied to generate descendant tuples of each of the tuples. This document refers to each of them as root-specific join conditions (root_specific_join_conditions), child-specific join conditions (child_specific_join_conditions), and root and child common join conditions (root_child_common_join_conditions). In Oracle's recursive query, the common join conditions of root and child and the root-specific join conditions form the same meanings as the root join conditions of the recursive query of SQL: 1999 standard. And child-specific join conditions form conditions that have the same meaning as child join conditions in recursive queries in the SQL: 1999 standard.

In Oracle recursive queries, the common join conditions for root and children are in the where clause (or from clause) of the query, the root-specific join conditions are in the start with clause of the query, and the child-specific join conditions are in the query. Each specified in the connect by clause. This is summarized in FIG. 20. In Oracle's recursive queries, the recursive temporary result tuple selection conditions apply to a single recursive temporary table, but for the sake of consistency, the English notation for those conditions is the same as that of the recursive query in the SQL: 1999 standard. RecursiveTables_closing_selection conditions.

The tables referenced in the from clause of Oracle's recursive query correspond to the base tables of the recursive query in the SQL: 1999 standard. Therefore, this document refers to the tables in the from clause of Oracle's recursive queries as well as base tables.

In Oracle's recursive query, the tuples identified by satisfying the common join conditions of the root and child, the conditions of the start with clause, and the conditions of the connect by clause are applied to the recursive temporary result tuples of the recursive query of the SQL: 1999 standard. Corresponding. Thus, this document refers to those tuples of Oracle's recursive query as recursive ad hoc result tuples.

Among the recursive temporary result tuples of Oracle's recursive query, the tuples that satisfy the recursive temporary result tuple selection conditions of the query correspond to the recursive result tuples of the recursive query of the SQL: 1999 standard. Thus, this document refers to those tuples of Oracle's recursive query as recursive result tuples.

Oracle recursive queries create only one recursive temporary table and do not have side tables, so in Oracle recursive queries no joins between recursive temporary result tuples occur and multiple replications of recursive temporary result tuples do not occur. . That is, all recursive result tuples of Oracle's recursive queries are generated by singular copies of the recursive temporary result tuples of the query.

If Oracle does not specify an optional nocycle in the connect by clause of a recursive query, Oracle will detect any cycle found in the parent-child relationship between recursive temporary result tuples when executing the query. Treat as a runtime error and, if the option is specified, identify a recursive temporary result tuple by not following the cycle if a cycle is found at the time of execution of the query.

In Oracle's recursive query, the order siblings by clause specifies the sorting criteria between tuples with the same parent, that is, siblings. The Order siblings by clause can only be used for recursive queries and cannot be specified in the same subquery as the order by clause. Recursive queries in the SQL: 1999 standard do not support clauses equivalent to Oracle's order siblings by clause.

Figure 21 shows the meaning of the conditions specified in the Oracle recursive query. Figure 21 (a) identifies the starting tuples of a transitional redundancy by applying Oracle's root and child common join conditions, root-specific join conditions, and route selection conditions to the base tables of the query. It is displayed. Figure 21 (b) shows that the tuples and parent selection conditions generated from the base tables are satisfied by satisfying the common join conditions of the root and child, child-specific join conditions, and child selection conditions of Oracle's recursive query. It indicates that recursive temporary result tuples are generated by applying parent-child linkage conditions to existing tuples of the transitional alveoli. Fig. 21 (c) shows final result tuples by applying the select clause of the query to recursive result tuples generated from recursive temporary result tuples by satisfying the recursive temporary result tuple selection conditions of Oracle's recursive query. To create.

As represented in FIG. 21, Oracle applies an order siblings by clause to not only tuples that satisfy "parent-child conditions of transitional closure" but also starting tuples of transitional closure. As represented in the hypothetical recursive interim result tuple tree of FIG. 18, Oracle assumes that the starting tuples of the transitional redundancy have virtually identical parent tuples, and the tuples are also sibling tuples. Therefore, the order siblings by clause is commonly expressed in FIGS. 21 (a) and 21 (b).

Except for the order siblings by clause, all Oracle recursive queries can be expressed as shown in FIG. 22 using a recursive query of the SQL: 1999 standard. The base tables of the root identification subquery and the child identification subquery in the recursive query of FIG. 22 correspond to the base tables of the recursive query of Oracle. Thus the base tables of the subqueries must be identical. The order by clause of the closing subquery in the recursive query of FIG. 22 corresponds to the order by clause of the recursive query of Oracle.

The common join conditions of root and child and root-specific join conditions of Oracle's recursive query are specified as the root join conditions of the root identification subquery in the recursive query of FIG. 22 and the root and child of Oracle's recursive query. Common join conditions and child-specific join conditions are specified as child join conditions of the child identification subquery in the recursive query of FIG. 22.

For example, if a query equivalent to that of FIG. 13 (a) is created in Oracle, the query is the same as that of FIG. As represented in the query of FIG. 23, the columns of the parent tuple in the conditions of the connect by clause are specified together with the keyword prior.

A query equivalent to that of FIG. 15 (a) having an side table in Oracle is shown in FIG. In the query of Fig. 24, the subquery and table departments specified in the from clause of the query correspond to the recursive temporary table empHierarchy and the side table departments referenced in the from clause of the closing subquery of the query in Fig. 15 (a), respectively. do. When multiple tables, including recursive temporary table (s), are referenced in the subclause of a recursive query in the SQL: 1999 standard, when a query equivalent to the recursive query is written in Oracle, The query has one recursive query or multiple recursive queries as subqueries of the from clause, as shown in FIG.

Oracle's recursive query adds 1 for each of the recursive temporary result tuples to 1 if the node in the tuple's recursive temporary result tuple tree is the root node, otherwise 1 is added to the parent node's level. Provides a pseudo column level to return. Therefore, if you want to generate only recursive temporary result tuples that correspond to nodes having a level from 1 to N in the recursive temporary result tuple trees, the "level <= N "can be specified.

The SQL: 2003 standard defines value expressions such as numeric value expressions, string value expressions, datetime value expressions, and XML value expressions. The SQL / XML publishing functions XMLELEMENT and XMLFOREST can take arbitrary value expressions in the SQL: 2003 standard as arguments to those functions. The SQL / XML publishing functions XMLCONCAT and XMLAGG only accept XML value expressions that return a value of the XML data type. The SQL / XML publishing function XMLATTRIBUTES can have any value expression of the SQL: 2003 standard except XML value expressions as its argument. For XMLATTRIBUTES and XMLFOREST, each of the arguments to those functions can have a pair of <value expression, identifier>. An identifier means an attribute name in case of XMLATTRIBUTES and an element name in case of XMLFOREST. If the identifier is omitted, the value expression must be only one column name, in which case the identifier of the argument is that column name.

XMLELEMNT, XMLFOREST, and XMLCONCAT construct a single XML value by concatenating the return values of each of the value expressions used as arguments to the function. An expression that does not contain an aggregate function is called a tuple expression, and an expression that contains an aggregate function is called an expression. XMLELEMENT, XMLFOREST, and XMLCONCAT generate and return an XML value as the values that the expressions return for each tuple if the expressions used as arguments to the function are tuple expressions. Are group expressions, they generate and return an XML value as the values they return for each group.

XMLATTRIBUTES creates one XML attribute for each pair of <value expression, attribute name> specified as arguments to the function. Of the existing SQL / XML publishing functions, only XMLELEMENT can take XMLATTRIBUTES as an argument.

XMLAGG generates and returns as an XML value the values returned by the XML value expression used as the function's argument for each group of tuples classified according to the group by clause of the subquery in which the function is used. XMLAGG allows you to specify an order by option to the function so that you can sort the tuples within each population as far as the function is concerned and then apply the XML value expression used as the function's argument to each of those tuples in turn.

For reference, when existing aggregate functions of SQL such as SUM, COUNT, AVG, MIN, MAX, XMLAGG are used as arguments to arbitrary aggregate functions, DB2 and SQL Server treat them as errors. Oracle 10g, on the other hand, does not treat it as an error, but instead: If a particular group function A has any group function A 인자 as a factor, then group function A classifies all values returned by the number of group functions A 집단 into one group and sets one value for the entire group. Create and return

For example, for the query "select XMLAGG (XMLELEMENT (TOTAL_SALARY, SUM (salary))) from employees group by department_id", DB2 treats the query as an error. However, in Oracle 10g, XMLELEMENT creates an XML element TOTAL_SALARY as each value returned by the group function SUM for each group formed by the group by clause and returns it as an XML value. XMLAGG returns the XML values returned by XMLELEMENT. Connect all together to generate and return an XML value for the entire population.

Examples of queries including SQL / XML publishing functions and the result tuples of the queries are shown in FIG. 25.

The recursive queries in the SQL: 1999 standard, DB2, SQL Server, and Oracle, and the SQL / XML publishing functions in the SQL: 2003 standard have the following characteristics: Now, based on them, (1) a new SQL function that is applied to the queries and generates one recursive XML value for each recursive result tuple tree formed by nodes corresponding to the recursive result tuples of the queries. For each node of the recursive result tuple tree, for each node of the recursive result tuple tree, recursive result tuples corresponding to all nodes of the sub-tree subtree are used. SQL functions of NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, NEST_MAX that compute a single value for each group by returning it to the group and return that value to the recursive result tuple corresponding to the root node representing that group. (3) classify recursive result tuples by the recursive temporary result tuple tree to which nodes corresponding to their origin tuples belong and classify one or more recursive result tuples. We present the SQL operator tc_root, which returns a value for each group by treating all the recursive result tuples of each class as members as a group.

Since the SQL function XMLNEST proposed by the present invention is a function for recursive queries, any DBMS providing a recursive query form can be applied without any limitation. A good example of this is the DBMS that follows the recursive query style of the SQL: 1999 standard, or DMBS, which provides its own recursive query style, such as Oracle 11g. That is, the SQL function XMLNEST can be used, for example, in the select clause of a subquery of the form "1). Must be specified in the select clause of the query.

Similarly, the SQL operator tc_root proposed by the present invention is a group operator for recursive queries. Therefore, any DBMS providing a recursive query form can be applied without any limitation. The SQL operator tc_root can be used in, for example, (1) the recursive query of the SQL: 1999 standard, in the select and order by clauses of the subquery in the form of "recursive result tuple" of the closing subquery, and (2) in Oracle recursion. In the case of an enemy query, it can be specified in the select and order by clauses of that query.

Definition 16. Closing a recursive query When a recursive ad hoc result tuple tree in any subquery of a subquery produces exactly one recursive result tuple tree, the recursive result tuple tree is generated Temporary Outcomes Tuple Tree "Solo Regeneration" Recursive Results

Definition 17. Closing a recursive query In any subquery of a subquery, for any recursive temporary result tuple tree, there is a recursive result tuple with origin tuple of the recursive temporary result tuple corresponding to the root node of the tree. In this case, a recursive result tuple tree having a node corresponding to the recursive result tuple as a root is referred to as a "root maintained" recursive result tuple tree of the recursive temporary result tuple tree.

Discussion 5. The maximum number of "root-maintain" recursive result tuple trees generated from one recursive temporary result tuple tree in a recursive query is at most one.

Definition 18. Closing Recursive Queries In any subquery of a subquery, a recursive result tuple tree is a "standback" recursive result tuple tree of a particular recursive temporary result tuple tree and a "maintain root" recursive result tuple. In the case of a tree, the recursive result tuple tree is referred to as the "root-maintained reincarnation" recursive result tuple tree of the recursive temporary result tuple tree.

The SQL function XMLNEST is meaningful only if all the recursive result tuple trees of the subquery are "root-maintained reincarnation" in any subquery of the closing subquery of the recursive query that contains the function, and the recursive result. Applies to tuple trees. Otherwise it is treated as an error.

Assume that the node with employee_id of 14 is deleted by the recursive temporary result tuple selection condition in the recursive temporary result tuple trees of FIG. 17. In that case, three recursive result tuple trees are created with roots having nodes of employee_id 10, 13, and 17 from the recursive temporary result tuple tree of FIG. 17 (a), and the recursive temporary result of FIG. 17 (b). A recursive result tuple tree is created from the guatuple tree with a node rooted at employee_id 21. The recursive result tuple trees with nodes with employee_id 10, 13, and 17 as roots in the recursive result tuple trees are not the recursive result tuple trees of "Solo Regeneration" because they are generated from the same recursive temporary result tuple tree. . In the recursive result tuple trees, the recursive result tuple trees with the nodes employee_id 13 and 17 as roots are the origin tuples of the recursive result tuples corresponding to the root nodes. It is not a recursive result tuple tree of "root maintenance" because it is not a recursive temporary result tuple corresponding to root nodes. On the other hand, a recursive result tuple tree whose root is a node with employee_id 21 is a recursive result tuple tree of "root maintenance independent reincarnation". The SQL function XMLNEST is meaningful only when all the recursive result tuple trees of the subquery where the function is used are "root-maintained reincarnation," so when XMLNEST is applied to the recursive result tuples, Treat it as an error.

The SQL function XMLNEST targets the recursive result tuples of the recursive query, and for each recursive result tuple tree formed by the nodes corresponding to the tuples, the nodes constituting the tree, for example, depth-first search. , DFS) to generate an XML value of a recursive structure with tuples corresponding to the nodes in the tree. Therefore, XMLNEST is a group function that processes one recursive result tuple tree into one group. Here, the depth-first search algorithm is only a preferable example, and the search using another kind of algorithm is also possible.

The SQL function XMLNEST performs the following two tasks in turn on the recursive result tuples of the recursive query.

(1) Task_1: Construct recursive result tuple trees with recursive result tuples. Sorting between recursive result tuple trees follows an arbitrary order. However, the order of sibling nodes in each recursive result tuple tree follows that criterion if the sorting criteria of those nodes are specified in XMLNEST; otherwise, if the sorting criteria of the nodes is specified in the recursive query itself, Otherwise, they are in any order.

Although the recursive result tuple trees may be configured by physically generating configuration information about the recursive result tuple trees, a method of logically configuring the tuple identifier tid (tuple identifier) described below may be used. In the present invention, the tuple identifier tid determines whether (i) recursive result tuples have been generated by singular copies of recursive temporary result tuples, and (ii) the recursive result tuple tree is a recursive result of "root-maintained reincarnation". Determine a tuple tree, and (iii) recursive temporary tuples corresponding to nodes constituting one recursive result tuple tree, or recursive temporary tuples corresponding to nodes constituting one recursive temporary result tuple tree. It is used to treat recursive result tuples with result tuples as origin tuples as a hierarchy.

Each of the recursive temporary result tuples is given a root_tid which is the tid of the root tuple, its own tid, and the parent_tid which is the tid of the parent tuple of the tuple. The identifier tid has the property of 'uniqueness of the identifier tid in recursive temporary result tuples'. In other words, All recursive temporary result tuples of a particular recursive temporary table have different tid values, and a consecutive integer value may be used as the value of the identifier tid.

If a recursive temporary result tuple has a parent tuple, the tuple has a parent tuple of tid as parent_tid, and if it does not have a parent tuple, NULL as parent_tid. Each of the recursive result tuples retains the root of the recursive result tuple <root_tid, tid, parent_tid>. However, recursive result tuples created by combining recursive temporary result tuples are not given <root_tid, tid, parent_tid>.

The uniqueness of the identifier tid in the recursive result tuples is maintained only if the recursive result tuples are generated by a singular copy of the recursive temporary result tuples. If all recursive result tuples are generated by singular copies of recursive temporary result tuples, recursive with roots of nodes corresponding to recursive tuples of recursive temporary result tuples referred to as root_tid of the recursive result tuples The result tuple trees are recursive result tuple trees of root maintenance. When the list of root_tids of the recursive result tuples corresponding to the root nodes of the recursive result tuple trees is LR, the set of root_tids included in the list LR more than once is called SR and the value of each root_tid of the set SR is root_tid. Nodes that correspond to recursive result tuples do not form recursive result tuple trees of the original reincarnation.

(2) Task_2: For each recursive result tuple tree, search the tree with DFS to generate an XML value of one recursive structure.

In order to clarify the concept of new SQL functions XMLNEST, NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, NEST_MAX and SQL operator tc_root, the present invention classifies groups formed by tuples into equal groups and hierarchical groups.

Definition 19. Same-value group. A collection of tuples with the same value for a particular column (s) is called an equivalence group.

Definition 20. Hierarchy group. A collection formed by recursive temporary result tuples or recursive result tuples of a recursive query is a hierarchy of all tuples having one tuple and its tuples as ancestor tuples.

The existing group functions of SQL are applied only to equivalence groups. The present invention proposes to extend the types of groups targeted by existing group functions of SQL to hierarchical groups as well as equivalent groups. Accordingly, the groups targeted by the existing group functions of SQL, the SQL functions proposed by the present invention, and the groups targeted by the SQL operator are as follows.

SQL's existing group functions SUM, COUNT, AVG, MIN, MAX, and XMLAGG can be applied to equivalence groups and hierarchical groups.

The SQL functions XMLNEST, NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, NEST_MAX, and the SQL operator tc_root proposed by the present invention can be applied only to hierarchical groups.

According to the present invention, hierarchical aggregate functions are used to distinguish SQL functions that can be applied only to hierarchical groups from the existing aggregate functions of SQL, and an SQL operator applied only to hierarchical groups. It is called a hierarchical aggregate operator.

This document describes the recursive result tuples of recursive queries, the recursive result tuple trees they form, and the XML values generated from recursive result tuples by applying the hierarchical function XMLNEST. Use terms.

In a recursive result tuple trees constructed from recursive result tuples of a recursive query, a set of tuples corresponding to all nodes of each tree is called a tuple family.

Each tuple in a tuple family is called a member tuple of that tuple family.

A recursive result tuple A XML value of a recursive structure created by all the member tuples of a tuple family of a tree is called the XML value of that tree.

For example, the tuples of FIG. 12 (b), the recursive result tuples of the query of FIG. 12 (a), form the tree of FIG. 17 (a) and the tree of FIG. 17 (b) as recursive result tuple trees. . Therefore, in the recursive result tuples of FIG. 12 (b), tuples having employee_id of 10, 11, 13, 14, 15, 16, and 17, which are tuples corresponding to nodes constituting the tree of FIG. Tuples having employee_ids of 21, 23, and 24, which are tuples corresponding to nodes forming the tree of FIG. 17 (b), form a tuple family and form another tuple family.

Consider the case of generating XML values for each recursive result tuple tree of FIG. 17 as follows.

Create one XML element "EMPLOYEE" for each node N _k . Let tuple corresponding to node N _k be tuple t _k .

The XML element "EMPLOYEE" has "id" as an attribute and "FIRSTNAME", "LASTNAME" and "SUBORDINATES" as child elements.

Create the value of the attribute "id" of the XML element "EMPLOYEE" as the value of the column employee_id of the tuple t _k .

Create a text value of the XML element "FIRSTNAME" and a text value of the XML element "LASTNAME" as the value of the column first_name of the tuple t _k and the value of the column last_name, respectively.

The XML element "SUBORDINATES" has all the "EMPLOYEE" elements created for the child nodes of node N _k as children and the document order of those child elements is the order in which they are listed from left to right.

For each of the recursive result tuple trees of FIG. 17, one XML value described above is generated using the hierarchical group function XMLNEST, and the average salary of the tuple family constituting the tree together with the XML value is the existing group function. Consider the case of using AVG.

In that case, XMLNEST and AVG should be applied to each recursive result tuple tree. If a recursive query including XMLNEST and AVG in a select clause is written based on the syntax of the SQL: 1999 standard, the query is as shown in FIG. Since the specification of the SQL function XMLNEST has not been presented yet, the arguments of the SQL function are omitted in FIG.

In FIG. 26, the group functions included in the select clause of the closing subquery of the recursive query process each of the recursive result tuple trees of FIG. 17 so that the query is composed of two result columns of <xml_value, avg_salary>. Generate result tuples. 27 shows the resulting tuples. The order between the two result tuples of FIG. 27 is arbitrarily constructed. The document order of the XML elements "EMPLOYEE" in the result column xml_value of FIG. 27 is the order of visiting the nodes of the recursive result tuple trees of FIG. 17 to DFS.

The hierarchical group function XMLNEST defined by the present invention enables specifying the NEST_ROOT element, the NEST_SUBORDINATE element, and the child elements of the NEST_ROOT element, and the order between sibling NEST_ROOT elements in the function, thereby recursively. Allows you to create an XML value of a recursive structure. For example, in the XML values of FIG. 27, the XML element "EMPLOYEE" has "id" as an attribute and one "FIRSTNAME", "LASTNAME", and "SUBORDINATES" elements as children. The element "SUBORDINATES" can have multiple "EMPLOYEE" elements as children. This is expressed as an XML schema graph as shown in FIG. 28. In the XML schema graph of FIG. 28, node "EMPLOYEE" is a NEST_ROOT node and node "SUBORDINATES" is a NEST_SUBORDINATE node. Node "EMPLOYEE" and node "SUBORDINATES" and the edges connecting them form a cycle. Accordingly, the values of the result column xml_value of FIG. 27 are XML values of a recursive structure.

When the XML value generated by the hierarchical group function XMLNEST is expressed as an XML schema graph, the XML schema graph has the following characteristics. First, the XML schema graph has exactly one cycle. Second, the XML schema graph must have a NEST_ROOT node and can optionally have a NEST_SUBORDINATE node. Third, the NEST_SUBORDINATE node does not have child nodes other than the NEST_ROOT node. Therefore, XMLNEST allows you to specify the following to the function to generate an XML value of a recursive structure.

The name of the NEST_ROOT element and the XML attributes of that XML element.

The name of the NEST_SUBORDINATE element and the XML attributes of that XML element.

Child elements of the NEST_ROOT element.

Alignment between sibling NEST_ROOT elements.

For example, a query including XMLNEST in a select clause for generating XML values of FIG. 27 for the table employees of FIG. 1 is written based on Oracle's grammar, and the query is as shown in FIG.

In FIG. 29, the arguments of XMLNEST have the following meanings.

The first argument "EMPLOYEE" represents the name of the NEST_ROOT element.

The second argument, XMLATTRIBUTES (employee_id AS "id"), indicates that the value of attribute "id" of the XML element "EMPLOYEE" should be created as the value of the column employee_id of the tuple that caused the element to be created.

The third argument, XMLELEMENT ("FIRSTNAME", first_name) and the fourth argument, XMLELEMENT ("LASTNAME", last_name) are the child elements of the XML element "EMPLOYEE" which is the NEST_ROOT element, and must create "FIRSTNAME" and "LASTNAME". Respectively. The order between their child elements is the order in which they are specified.

In the fifth argument, XMLNESTSPEC ("SUBORDINATES") ORDER CHILDREN BY employee_id, XMLNESTSPEC specifies the name and attributes of the NEST_SUBORDINATE element, and the ORDER CHILDREN BY option specified with XMLNESTSPEC indicates the sorting criteria among sibling NEST_ROOT elements. In this example, the name of the NEST_SUBORDINATE element is "SUBORDINATES" and the order between the NEST_ROOT elements created as child elements of the NEST_SUBORDINATE element indicates the ascending order of the column values of the column employee_id of the tuples that caused the NEST_ROOT elements to be created. The position where the function XMLNESTSPEC is specified indicates the position of the NEST_SUBORDINATE element as a child element of the NEST_ROOT element. In this example, the elements "SUBORDINATES" are placed after the "FIRSTNAME" and "LASTNAME" elements created via XMLELEMENTs.

The specification of the hierarchical group function XMLNEST is shown in FIG. The specification of FIG. 30 follows the notation method of the SQL / XML standard. For reference, in the specification of FIG. 30, the specification between "[" and "]" may be omitted as an option, and the "..." designation indicates that the specification indicated immediately before the display may be repeated indefinitely.

In the XMLNEST specification of FIG. 30, (1) <XML nest_root element spec> is a name of the NEST_ROOT element, a specification of the XML namespace of the element, and XML attributes of the element, and (2) <XML element content> is NEST_SUBORDINATE. (3) The <XML nest_subordinate element spec> element specifies the name and options of the NEST_SUBORDINATE element, the XML attributes of the element, and the NEST_ROOT element created as children of the NEST_SUBORDINATE element. This is a specification of the alignment between them.

In the specification of XMLNEST in FIG. 30, the specifications of <XML element name>, <XML namespace declaration>, <XML attributes>, <XML element content>, and <sort specification list> are the same as those of SQL / XML for them. . For reference, since the function of limiting the maximum depth of the recursive temporal result tuple tree may be provided in the recursive query itself, the specification of XMLNEST of FIG. 30 does not include the specification related thereto.

The meanings of the attributes specified by <XML nest_subordinate element spec> are as follows.

<XML element name> represents the name of the NEST_SUBORDINATE element. The name of the NEST_SUBORDINATE element may not be specified. If the name of the NEST_SUBORDINATE element is not specified, it does not create a NEST_SUBORDINATE element, and a NEST_ROOT element that is created for a non-terminal node in the recursive result tuple tree uses the NEST_ROOT elements created for the child nodes of that node. Have as child elements of the element.

"OPTIONAL" or "MANDATORY" may be specified as an <XML nest_subordinate element option>. The default is "MANDATORY". "MANDATORY" means that all NEST_ROOT elements have a NEST_SUBORDINATE element, and "OPTIONAL" means that a NEST_ROOT element that does not have a NEST_ROOT element as a descendant does not have a NEST_SUBORDINATE element.

<XML attributes> specifies the XML attributes of the NEST_SUBORDINATE element.

The ORDER CHILDREN BY option specifies the sorting criteria between sibling NEST_ROOT elements.

For example, to have dept_id indicating the department number of the employee as the XML attribute of the element "SUBORDINATES" which is the NEST_SUBORDINATE element in the XML values of FIG. 27, XMLATTRIBUTES (department_id AS "dept_id" in XMLNESTSPEC of the function XMLNEST specified in the query of FIG. ) By modifying "XMLNESTSPEC (" SUBORDINATES ", XMLATTRIBUTES (department_id AS" dept_id ")) ORDER CHILDREN BY employee_id". Therefore, among the existing SQL / XML publishing functions, XMLELEMENT is the only function that can take XMLATTRIBUTES as an argument, and XMLNEST and XMLNESTSPEC, which are the arguments of XMLNEST, are added.

The hierarchical group function XMLNEST proposed by the present invention is valid only when used in a recursive query. The reason is that XMLNEST is a function of XMLNEST that generates the structure of recursive result tuple trees and the contents of recursive result tuples corresponding to the nodes constituting the trees as XML values. Because the impact on / XML publishing functions is large, it is to limit the query to which the function can be used to recursive queries to minimize the effects. Therefore, the processing flow of the SQL query including XMLNEST is shown in FIG. 31.

The relationship between the hierarchical group function XMLNEST, the existing group functions of SQL, and the existing SQL / XML publishing functions is as follows.

Relationship_1. Relationship between XMLNEST and existing aggregate functions in SQL.

SQL allows grouping attributes to be described in the group by clause of the select statement, allowing the formation of groups of tuples with the values of those columns, and SUM, Provides COUNT, AVG, MIN, MAX, and XMLAGG.

When SQL's existing aggregate functions SUM, COUNT, AVG, MIN, MAX, and XMLAGG are used for recursive queries, the populations they target are formed the same as when they are used in regular SQL queries. . That is, the groups targeted by the group functions are equivalent groups.

XMLNEST proposed by the present invention is a hierarchical group function that processes a tuple family constituting a recursive result tuple tree of a single "root-maintained reincarnation" into a group, so that group by subquery including the function in a select clause. If a clause is included, there is a semantically conflict. Therefore, the select clause and group by clause including the SQL function XMLNEST should not be used together in one subquery.

Existing aggregate functions in SQL can be specified in the select clause of the same subquery with XMLNEST. In that case, the semantics of the existing aggregate functions of SQL specified together with the select clause with the hierarchical group function XMLNEST are the same as the existing ones. However, the existing group functions classify the recursive result tuples of the query by the recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong, and all recursive results tuples of each classification having one or more recursive result tuples as members. The result tuples should be treated as one group, one hierarchy.

An example of using the existing group function AVG of SQL together with the hierarchical group function XMLNEST in the select clause of a recursive query is briefly introduced in the query of FIG. 26. Consider the case where the rest of the existing aggregate functions in SQL are also used in the select clause of the recursive query. For example, the result tuples of FIG. 27 are not only xml_value and avg_salary as result columns, but also min_salary generated by MIN (salary), max_salary generated by MAX (salary), tuple_cnt generated by COUNT (*), and XMLAGG (XMLELEMENT ( The query using XMLNEST and SQL's existing aggregate functions to have additional columns of dept_id_xml generated as "DEPTID", department_id)) is created based on the syntax of the SQL: 1999 standard.

If the recursive query of FIG. 32 is evaluated against the table employees of FIG. 1, the recursive result tuples of the query form the same recursive result tuple trees as the trees of FIG. Therefore, the query of FIG. 32 generates two result tuples as shown in FIG. 33. The order of the result tuples in Figure 33 is arbitrarily configured. Parts omitted from the result column xml_val of FIG. 33 are the same as the XML values of FIG. 27.

When XMLAGG is used in the same subquery as XMLNEST, the groups targeted by XMLAGG are hierarchical groups, and the order between the recursive result tuples belonging to the same hierarchical group is the order by which the order by option is specified in XMLAGG. However, if the option is not specified in XMLAGG, the recursive result tuple tree formed by the nodes corresponding to the recursive result tuples should be the same as the order of visiting DFS.

The XMLAGG specified in the recursive query of FIG. 32 does not have an order by option. Accordingly, the order of the XML elements constituting each column value of the "DEPTIDs" in the column values of the result column dept_id_xml of FIG. 33 is the order of visiting the nodes of FIG. 17 corresponding to the recursive result tuples that caused the elements to be generated. Is the same as

The present invention follows the processing methods of DB2 and SQL Server as far as the processing of a collection function having a collection function as a direct or indirect factor. That is, under the proposition of "one group function or group operator in a subquery cannot have any group function or group operator as a direct or indirect argument", the existing group functions of the hierarchical group functions XMLNEST and SQL Set the argument relationship as follows: (1) The hierarchical group function XMLNEST is a direct and indirect argument, and existing SQL functions such as SUM, COUNT, AVG, MIN, and MAX, SQL / XML publishing functions, and cannot have XMLAGG, which is an existing group function of SQL, and (2) XMLNEST Cannot be used as a direct or indirect argument to XMLAGG.

Relationship_2. SQL / XML publishing functions that can be used as direct and indirect arguments to XMLNEST.

SQL / XML publishing functions that can be used as arguments to XMLNEST must be applied to a tuple to generate XML values. SQL / XML publishing functions XMLELEMENT, XMLCONCAT, XMLFOREST, and XMLATTRIBUTES are functions that can be applied to a tuple. Thus they can be used as arguments to XMLNEST.

Relationship_3. SQL / XML publishing functions that can take XMLNEST as a direct or indirect argument.

The SQL / XML publishing functions XMLELEMENT, XMLCONCAT, and XMLFOREST can take as arguments functions that return an XML value. Thus, they can take XMLNEST as an argument. XMLELEMENT, XMLCONCAT, and XMLFOREST with XMLNEST as arguments must generate a new XML value with one XML value that XMLNEST returns for each recursive result tuple tree.

Consider the example where XMLNEST is used as an argument of XMLELEMENT.

Example 7. For each employee who does not have a boss for the table employees in Figure 1, create an XML value of a recursive structure using the employee number and name of the employee and all direct and indirect subordinates of that employee. Output an XML value consisting of the value as a child element of the element "HIERARCHY" as a result tuple.

The query created based on Oracle's syntax for Example 7 is shown in FIG. In the query of FIG. 34, XMLNEST generates one XML value for each of the recursive result tuple trees of FIG. 17 formed by the recursive result tuples of the query, and XMLELEMENT creates a new XML value by inputting each of the XML values. do. Therefore, the query generates two result tuples as shown in FIG. The order between them in the result tuples of FIG. 35 is arbitrarily constructed. Parts omitted from the XML values in FIG. 35 are the same as the XML values in FIG. 27.

Consider the example where XMLNEST is used as an argument of XMLCONCAT.

Example 8. For each employee who does not have a boss for the table employees in FIG. 1, a set of tuples of that employee and all direct and indirect subordinates of that employee is X. <column of tuples of set X, column first_name And a single XML generated using the sum of the column values of the column salary of all tuples of the set X and the recursive XML values generated along the similarity-load relationship between the tuples as the column values of the column last_name. Output one XML value created by concatenating pairs of values> with one result tuple.

The query created based on Oracle's syntax for Example 8 is shown in FIG. In the query of FIG. 36, XMLELEMENT having XMLNEST and SUM as arguments generates one XML value for each of the recursive result tuple trees of FIG. 17 formed by the recursive result tuples of the query, and XMLCONCAT concatenates the XML values. To generate a new XML value. Therefore, the query generates two result tuples as shown in FIG. The order of the result tuples in Figure 37 is arbitrarily configured. Parts omitted from the XML values in FIG. 37 are the same as the XML values in FIG. 27.

Consider the example where XMLNEST is used as an input argument to XMLFOREST. When the query for Example 8 is written using XMLFOREST instead of XMLCONCAT, the query is as shown in FIG.

In the query of FIG. 38, XMLFOREST specifies XML values returned by XMLELEMENT with XMLNEST and SUM as arguments for each of the recursive result tuple trees of FIG. 17 formed by the recursive result tuples of the query, and the XML elements "HIERARCHY" and XML. Create and return an XML value by constructing and concatenating the contents of the element "PAYMENT". Therefore, the query generates two result tuples as shown in FIG. In FIG. 39, the order between the result tuples is arbitrarily configured. Parts omitted from the XML values of FIG. 39 are the same as the XML values of FIG. 27.

Example 9. For each employee who does not have a supervisor for the table employees of FIG. 1, the set of the employee and all the direct and indirect subordinates of the employee is X. (1) Reflects the connection of the employees of the set X. XML value of the recursive structure, (2) sum of salaries of employees in set X, average of salaries, number, and (3) maximum and minimum salaries of employees in set X. Print two to one. However, make sure that only those employees whose salary is less than 32000 or whose salary is above 38000 and in the range below 52800 are included in each set.

If the query for Example 9 is written based on Oracle's syntax, the query is as shown in FIG. The nodes corresponding to the recursive temporary result tuples of the recursive query of FIG. 40 form the recursive temporary result tuple trees of FIG. 17. The node with employee_id of 11, the node of 13, and the node of 14 are recursively of FIG. 17 according to the recursive temporary result tuple selection condition of FIG. 40. It is deleted from the temporary result tuple trees. Therefore, two recursive result tuple trees are formed from the recursive temporary result tuple tree of FIG. From this a recursive result tuple tree identical to that tree is formed. This is illustrated in FIG. 41.

In FIG. 41, the recursive result tuple trees having a node having an employee_id of 10 and a node having roots are "root maintaining" recursive result tuple trees, but a recursive result tuple tree having a node having an employee_id of 17 as the root is "root". Maintain "recursive result is not a tuple tree. In FIG. 41, a recursive result tuple tree having a node having an employee_id of 21 as a root is a "reading reincarnation" recursive result tuple tree, but a recursive result tuple tree having a node having an employee_id of 10 and a node having roots has a "reading reincarnation". "Recursive results are not tuple trees. The SQL function XMLNEST is meaningful only when all the recursive result tuple trees formed by nodes corresponding to the recursive result tuples of the subquery in which the function is used are "root-maintained reincarnation". XMLNEST used in the query returns an error. This means that the query returns an error even though the rest of the aggregate functions used in the query can be processed normally.

The present invention proposes NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, and NEST_MAX, which are hierarchical group functions that can be used only as direct and indirect arguments of XMLNEST, in addition to the hierarchical group function XMLNEST. In the present invention, these five hierarchical group functions are specifically called XMLNEST-related functions. XMLNEST related functions are meaningful only when used as arguments to XMLNEST or as arguments to SQL functions used as arguments to XMLNEST. Otherwise, queries containing XMLNEST related functions return an error. The XMLNEST related functions provide a function for each group of recursive result tuple trees by processing the recursive result tuples corresponding to all nodes of the subtree rooted at that node as a group. Calculates the value of the value expression specified by and returns the value to the recursive result tuple corresponding to the root node representing the group.

Specifically, these XMLNEST related functions are all nodes of the subtree rooted at the node corresponding to each recursive result tuple because each recursive result tuple corresponds to the root node of one subtree of a particular recursive result tuple tree. A group consisting of recursive result tuples corresponding to one of these fields is processed, and the value expression is applied to each of the recursive result tuples of the group based on the value expression specified as a function argument. To calculate. Specifically, the NEST_SUM function calculates the sum of values obtained by applying a value expression indicated as a function argument to each of the group's recursive result tuples. The NEST_COUNT function calculates the number of tuples whose value expression is not NULL among the recursive result tuples of the group. The NEST_AVG function applies its value expression to each of the group's recursive result tuples to produce an average of the values found. The NEST_MIN and NEST_MAX functions apply the value expression to each of the group's recursive result tuples to produce the minimum and maximum values, respectively. These XMLNEST related functions return a calculated value to each recursive result tuple by returning the calculated value to the recursive result tuple corresponding to the root node representing the group.

The XMLNEST related functions are different from the analytic functions of SQL. The reason is that in the latter case, the tuples are partitioned according to any criteria set by the user, and the cumulative value of the tuples in the partition already processed for each of the tuples in each division and the tuples This is because it returns a value calculated using.

The number of subtrees of the recursive result tuple trees formed by the recursive result tuples is equal to the number of recursive result tuples, and each of the recursive result tuples corresponds to the root node of one subtree. This means that XMLNEST related functions return one value for each recursive result tuple by returning one value for one hierarchical group formed by one subtree of the recursive result tuple tree. XMLNEST related functions are distinguished from SQL's existing aggregate functions in that they always return a value for each recursive result tuple.

The specification and semantics of each of the XMLNEST-related functions is identical to those of the corresponding SQL's existing aggregate functions by excluding the function's prefix "NEST_" from the name of the function.

Example 10 For a table employee of FIG. 1, for an employee with employee number 10, generate an XML value of one recursive structure using information of that employee and all direct and indirect subordinates of that employee. Provided that X is the employee and the set of all direct and indirect subordinates of the employee, X, for each employee in the set X: (1) the salary of the employee, and (2) the salary of the employee and his subordinates. And (3) create XML elements that represent the sum of the employee and the number of subordinates of the employee.

The query for Example 10 is written based on Oracle's syntax as shown in FIG. 42.

 The nodes corresponding to the recursive result tuples of the recursive query of 42 form a recursive result tuple tree of FIG. 17 (a) having a node having an employee_id of 10 as the root. Therefore, the recursive query of FIG. 42 generates one final result tuple as shown in FIG. 43.

The present invention provides a hierarchical group operator tc_root in addition to the SQL function XMLNEST and XMLNEST related functions that can be used only as direct and indirect arguments of the function. The hierarchical group operator tc_root classifies the recursive result tuples by the recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong only if the recursive result tuples are generated by singular copies of the recursive temporary result tuples. And for each classification whose members are one or more recursive result tuples, the value of the expression specified as the argument of the operator using the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree corresponding to that classification. Calculate and return Thus, tc_root is a hierarchical group operator that processes all recursive result tuples of each classification into a hierarchical group called a recursive temporary result tuple tree to which nodes corresponding to their origin tuples belong. XMLNEST is meaningful only if the nodes corresponding to all recursive result tuples form "root-maintained recursive" recursive result tuple trees, but tc_root means that all recursive result tuples are singular of recursive temporary result tuples. Only meaningful if created by replication. That is, when recursive result tuples are generated by multiple copies or combinations of recursive temporary result tuples, tc_root returns an error.

The hierarchical operator tc_root takes as argument any value expression defined in the SQL: 2003 standard. Because groups targeted by the hierarchical group operator tc_root are not formed by the group by clause, subqueries including tc_root cannot use the group by clause in the same subquery. The arguments of the hierarchical group operator tc_root are set to SQL group functions according to the propositions "one group function or group operator in one subquery cannot have any group function or group operator as a direct or indirect argument". Alternatively, the group operator cannot be used as a direct or indirect argument, and can not be used as a direct or indirect argument of a group function or a group operator in SQL. The data type of the value returned by the hierarchical operator tc_root depends on the data type of the value expression specified as the argument to tc_root. The hierarchical group operator tc_root, like the hierarchical group function XMLNEST, is only valid when used in recursive queries.

When existing group functions of SQL such as SUM, COUNT, AVG, MIN, MAX, XMLAGG are used together with the hierarchical group operator tc_root in the same subquery of recursive query, the meaning of the existing group functions is the same as the existing meaning. However, the existing group functions classify the recursive result tuples by the recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong and collect all the recursive result tuples of each classification having one or more recursive result tuples as members. It should be treated as a group.

The processing flow of the SQL query including the hierarchical group operator tc_root is shown in FIG. 44, and the specification of tc_root is shown in FIG. In the specification of tc_root of FIG. 45, <value expression> means any value expression of the SQL: 2003 standard.

For reference, Oracle's SQL operator connect_by_root is used only for recursive queries and returns a value for each of the recursive result tuples. The operator has only one value expression as an argument, and if the value expression contains database column names, each of the recursive result tuples has a recursive temporary result tuple to which the node corresponding to the origin tuple of the recursive result tuple belongs. Returns the value of the value expression created using the column values of the recursive temporary result tuple corresponding to the root node of the tree.

The hierarchical group operator tc_root and Oracle's SQL operator connect_by_root proposed by the present invention are in the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree to which the node corresponding to the origin tuple of the recursive result tuple belongs. Computing the value of the value expression set as an argument to is the same, but connect_by_root returns the value to each of the recursive result tuples, while tc_root is the recursive result of each node belonging to the nodes corresponding to the origin tuples of the recursive result tuples. The difference is that it returns a value for a group called a temporary result tuple tree.

For example, when the recursive temporary tuple trees and the recursive result tuple trees are the same as shown in FIG. 17, tc_root (employee_id) is 10 for the recursive result tuple tree of FIG. 17 (a) and FIG. 17 (b). Recursive result of) returns 21 for the tuple tree. However, connect_by_root (employee_id) indicates 10 to a recursive result tuple corresponding to each of the 7 nodes of the recursive result tuple tree of FIG. 17 (a) and three nodes of the recursive result tuple tree of FIG. 17 (b). Returns 21 to each recursive result tuple.

Example 11. For each employee who does not have a boss for the table employees in FIG. 1, the set of the employee and all the direct and indirect subordinates of the employee is X, (1) the highest boss of the employees in the set X. Output the name of the employee doing (2) the sum of the salaries of the employees in set X, the average of the salaries, and the number and (3) the maximum and minimum salaries of the employees in set X. However, make sure that only those employees whose salary is less than 32000 or whose salary is above 38000 and in the range below 52800 are included in each set.

If the query for Example 11 is written based on Oracle's syntax, the query is as shown in FIG. The recursive temporary result tuple trees formed by the nodes corresponding to the recursive temporary result tuples of the recursive query of FIG. 46 and the recursive result tuple trees formed by the nodes corresponding to the recursive result tuples of the query are shown in FIG. Same as 41. Since all recursive result tuples corresponding to nodes of the recursive result tuple trees of FIG. 41 are generated by singular copies of recursive temporary result tuples, the recursive query of FIG. 46 returns an error unlike the recursive query of FIG. The tuples of FIG. 47 are generated as the final result tuples.

Example 12. For each of the table employees and table departments of FIG. 1, for each manager of a department whose department name is 'HR', 'RD', and 'TFT', the employee and all the direct and indirect subordinates of that employee are X. (1) the name of the employee and the department belonging to the chief supervisor of the employees of set X, (2) the sum of the salaries of the employees of set X, the average, number of salaries, and (3) Output the maximum and minimum salaries of the employees in set X in a single result tuple. However, make sure that each set includes only the manager of the department and subordinates up to two levels below it, that is, those with level 3 or less, and those with salaries greater than 40000 and in the range of less than 52800.

If the query for Example 12 is written based on Oracle's syntax, the query is as shown in FIG. In the recursive query of FIG. 48, "e.department_id = d.department_id", a join condition between table employees and table departments specified in the CONNECT BY clause, is a child-specific join condition and "PRIOR", the parent-child connection condition of the CONNECT BY clause. e.employee_id = e.supervisor_id "is specified to limit the target tuples to which the tuples to be applied are created, one for each of the tuples of the table employees. If the child-specific join condition is not specified in the CONNECT BY clause of the recursive query of FIG. 48, the parent-child connection condition applies to all tuples generated by the Cartesian product of table employees and table departments.

By applying the recursive temporary result tuple selection conditions of FIG. 48 to the recursive temporary result tuple trees formed by nodes corresponding to the recursive temporary result tuples of the recursive query of FIG. The recursive result tuple trees formed by the nodes corresponding to the generated recursive result tuples are illustrated in FIG. 49. In the recursive temporary result tuple tree of FIG. 49 with the root of the node whose employee_id is 10, nodes with employee_id 11 and 14 are deleted by salary condition, and nodes 13 and 17 by level condition. . Therefore, the recursive temporary result tuple tree generates the recursive result tuple tree of FIG. 49 having a node whose employee_id is 10 as a root. The recursive result tuple tree is a "root-maintained reincarnation" recursive result tuple tree. In the recursive temporary result tuple tree of FIG. 49 having the root of the node whose employee_id is 14, the node whose employee_id is 13 and the node whose 14 are deleted from the tree by the condition of salary. Therefore, the recursive temporary result tuple tree generates the recursive result tuple tree of FIG. 49 having a node whose employee_id is 17 as a root. The recursive result tuple tree is not "root-maintained" but "read-only recursive" recursive result tuple tree. In the recursive temporary result tuple tree of FIG. 49 having the root of a node whose employee_id is 21, all nodes are deleted by the salary condition. Thus, the recursive temporary result tuple tree does not produce a recursive result tuple tree.

The existing group functions of the hierarchical group operator tc_root and SQL specified in the recursive query of FIG. 48 classify the recursive result tuples by the recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong and one or more recursive result tuples. Since each classification having as members is treated as a group, two final result tuples are generated as shown in FIG.

Example 13. A recursive XML generated using <employee's name, employee number and name of the employee and all direct and indirect subordinates of the employee, for each employee who does not have a boss for the table employees of FIG. Output one result tuple with a pair of values>. However, sort the result tuples in ascending order based on the column value of their first result column.

When the queries for Example 13 are written based on the grammar of Oracle and the grammar of the SQL: 1999 standard, the queries are as shown in Figs. 51 (a) and 51 (b), respectively. In each of the recursive queries of FIGS. 51 (a) and 51 (b), tc_root and XMLNEST generate one value for each of the recursive result tuple trees of FIG. 17 formed by the recursive result tuples of the query. The queries generate two result tuples consisting of two result columns as shown in FIG. 52. The result tuples of FIG. 52 are sorted in ascending order according to the value of the result column root_fname by the order by clause of the queries of FIG. 51. Parts omitted from the XML values in FIG. 52 are the same as the XML values in FIG. 27.

Example 14. For each employee who does not have a boss for the table employees in FIG. 1, the sum of the employee and all the direct and indirect subordinates of the employee is X, the sum, number, Print the average of salaries> in a single result tuple. However, order the result tuples in increasing order of the name of the employee corresponding to the chief boss of the group that generated each of the result tuples.

The query for Example 14 is written based on Oracle's syntax. The query of Figure 53 has tc_root as the sort specification of the order by clause of the query, so the existing aggregate functions of SQL specified in the select clause of the query can use recursive temporary tuples whose nodes correspond to their origin tuples. You should classify each result tuple tree and treat each classification with one or more recursive result tuples as a group. The nodes corresponding to the origin tuples of the recursive result tuples of the recursive query of FIG. 53 form the recursive temporary result tuple trees of FIG. 17. Therefore, the recursive query of FIG. 53 generates two result tuples as shown in FIG. 54. The result tuples of FIG. 54 are sorted in ascending order based on the column value of the column first_name of the tuple corresponding to the highest superior of the hierarchical group that generated each of the result tuples. In the result tuples of FIG. 54, the values of tc_root (first_name) for the first and second result tuples are "BH" and "CHRISTINE", respectively.

Example 15 For each employee who does not have a boss for the table employees in FIG. 1, the set of employees that are the highest bosses of the employees in set X, when X is the set of employees and all direct and indirect subordinates of that employee. Create an XML value with a recursive structure that reflects the name, the name of your department, the sum of the number and salaries of the employees in the set, and the connection of the employees in the set, and create those values as a single XML value.

If the query for Example 15 is written based on the grammar of Oracle, it is as shown in FIG. The subquery in the from clause of the query of FIG. 55 applies two tuples to the subquery by applying tc_root, COUNT, SUM, and XMLNEST to each recursive result tuple tree of FIG. 17 formed by the recursive result tuples of the subquery. Generate the final result tuple of. The query of FIG. 55 applies XMLELEMENT to each of the two tuples generated by the subquery of the from clause of the query, thus generating two final result tuples as shown in FIG. 56.

If the hierarchical function XMLNEST or the hierarchical group operator tc_root is used in a recursive query, they do not affect the alignment between the final result tuples of the query. The ordering between the final result tuples is determined by the order by clause specified in the closing subquery for recursive queries in the SQL: 1999 standard and in the query itself for Oracle recursive queries. In addition, the order by clause does not affect the value of the XML value generated by XMLNEST and the expression generated by tc_root.

57 is a block diagram showing the construction of a computer system for implementing the present invention. The new SQL hierarchical function XMLNEST proposed by the present invention and its five related functions NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN and NEST_MAX, and the related operator tc_root are implemented as computer-executable instructions such as program modules. And can be included as part of a DBMS that supports recursive query forms. The DBMS comprising these functions and operators is characterized in that at least a processor, such as a central processing unit C, is provided with a system memory PM and a data storage unit AM, as shown in FIG. It may be implemented in a conventional computer system connected to. The system memory PM includes at least a ROM in which a BIOS is stored, and a RAM in which the central processing unit C stores data and / or programs to directly access and process data. RAM). The data storage device AM may be composed of, for example, a nonvolatile storage medium such as a hard disk, a CD-ROM, a tape, a magnetic recording medium, an ipyrom, an ypyrom, a ROM, or the like. Central processing unit C may also access other input / output channels (not shown) and auxiliary devices (not shown). The present invention relates to devices such as personal computers (PCs), handheld devices with computing capabilities, multiprocessor systems, microprocessor-based programmable electronics, network PCs, minicomputers, mainframe computers, portable communication devices, and combinations thereof. It can also be implemented in an environment consisting of a device, or a distributed computing environment. Therefore, the computing environment illustrated in FIG. 57 is merely an example of a computing environment suitable for implementing the present invention, and is not intended to limit the scope of use of the present invention, the functions of the method and system of the present invention, and the like. It should be understood.

As shown in FIG. 57, the database system 100 in which the present invention is implemented includes a database 110 which is a collection of relational data and a database management system (DBMS) 160 for managing and operating the database 110. do.

The database 110 stores data files that store data, index files for the data files, and metadata for data stored in the database. It consists of a system catalog. The database 110 is stored in a computer-readable data storage medium AM such as a hard disk.

The database storage system program files 122 and other program files 124 and 126 are stored in the data storage medium AM.

The database management system 160 typically includes a parser 171, an optimizer 172, a query plan executor 173, an SQL function evaluator 175, and an SQL operator handler. concurrency control (180), recovery manager (consist of a query processor 170) consisting of an operator evaluator (177), a transaction manager (182), and a lock manager (184). a storage manager consisting of a recovery manager 191, files and index structures 192, a buffer manager 193, a disk space manager 194, and the like. 190). The database management system 160 is stored as program files 122 in a storage medium AM, and is loaded into system memory PM serving as a main memory device according to program execution. , C) perform their functions under the control of C). Of course, other processes 200 are also loaded into the system memory PM.

SQL commands are applied to the database management system 160 through web forms 132, applications 134, SQL interfaces 136, etc., and the database management system 160 interprets and processes the SQL commands. do.

The SQL hierarchical function XMLNEST proposed by the present invention, 'XMLNEST related SQL functions' (NEST_SUM, NEST_COUNT, NEST_AVG, NEST_MIN, and NEST_MAX), which are hierarchical functions that can be used only as direct and indirect arguments of the XMLNEST function, and SQL hierarchical operator tc_root is implemented as an element of the SQL function processor 175 and the SQL operator processor 177 that compose the query processor 170 like the existing SQL functions and the existing SQL operators. Files 122.

DBMS programs are generally stored in the data storage device (AM). When the DBMS is executed, the central processing unit (C) reads the DBMS program, loads it into system memory (PM), and executes the program logic. . At this time, the program files 122 including the SQL functions and SQL operators proposed by the present invention are also loaded into the system memory (PM) of the computer as part of the database management system 160 process, and the SQL functions and the SQL When executing an SQL query including an operator, logic corresponding to those program files loaded in system memory (PM) is executed by the central processing unit (C) and the execution result is reflected in the system memory (PM). do.

58 is a diagram schematically illustrating a process in which an SQL query is processed by SQL functions and operators proposed by the present invention. Referring to FIGS. 57 and 58, an SQL query is transmitted to the database management system 160 through web forms 132, web applications 134, or various SQL interfaces 136. The transferred SQL query is processed by the query processor 170 and the storage manager 190 for the database 110 of the storage medium AM. The query processor 170 parses the SQL query and then establishes a query plan for executing the query at the lowest cost, and according to the query plan, the storage manager 190, the SQL function processor 175, and the SQL operator processor 177 generates a recursive temporary result tuple tree and a recursive result tuple tree, and performs a function of processing functions and operators according to the present invention as defined and described in detail below.

In particular, the query processor 170 targets the generated recursive temporary result tuple tree and the recursive result tuple tree to determine whether one recursive temporary result tuple tree generates only one recursive result tuple tree and the recursive result tuple tree. By comparing whether the recursive result tuple corresponding to the root node has a recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple of the origin tuple, an error signal is generated when either of them is not satisfied. If both are satisfied, the recursive result tuple tree is searched with depth-first search for nodes constituting the tree, and the XML value of one recursive structure is searched with tuples corresponding to the nodes belonging to the tree. Performs the XML value generation step of the recursive structure to generate. These functions of the query processor 170 may be performed by the SQL function processor 175 alone or in cooperation with other processes in the query processor 170 depending on where the processes that perform the functions are included. do.

In addition, the query processor 170 performs a singular replication judgment step of determining whether recursive result tuples of the recursive result tuple tree are generated by singular replication of the recursive temporary result tuples of the recursive temporary result tuple tree, and When not satisfied with the replication decision step, generates an error signal, and if the singular replication decision step is satisfied, classification to classify the recursive result tuples by the recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong. Step, and for each classification that has one or more recursive result tuples, use the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree corresponding to that classification to retrieve the value of the value expression specified as a factor. Calculate one recursive result tuple of each classification with the at least one recursive result tuple as a member By treatment with a group performs a return step of returning a value for their respective group. These functions of the query processor 170 may be performed by the SQL operator processor 177 alone or in cooperation with other processes in the query processor 170, depending on where the processes that perform the functions are included. do.

The present invention is applicable to a database related industry. In particular, it can be implemented as an SQL function and operator in a DBMS that provides a recursive query form and used as part of such a DBMS. For example, it could be implemented as part of a DBMS that supports the SQL / XML publishing functions of the SQL: 2003 standard. It can also be added to or included as SQL functions and operators in commercial object relational (OR) DBMS products such as Oracle 11g, DB2, SQL Server 2008, or any other commercial relational DBMS product, or in the new DBMS product. will be.

1 is a table referenced in the SQL queries of this document and the tuples of the tables,

FIG. 2 is an SQL query written to generate an XML value from a tuple of a table departments corresponding to a department having a department number 1 and a tuple of a table employees corresponding to a manager of the department using SQL / XML publishing functions. ,

FIG. 3 is an SQL query written to generate XML values from specific tuples of table employees connected in a boss-to-load relationship using SQL / XML publishing functions,

4 is an XML value generated as a result tuple of the SQL query of FIG.

5 is an XML schema graph representing the structure of the XML value of FIG.

6 is a query written to present an example of recursive temporary result tuples, recursive result tuples, and final result tuples in a recursive query,

FIG. 7 illustrates recursive temporary result tuples, recursive result tuples, and final result tuples of the recursive query of FIG.

8 shows the overall structure of a query in a recursive query of the SQL: 1999 standard consisting of a with clause and a closing subquery, and illustrates the internal structure of the declaration and definition of a recursive temporary table.

Figure 9 shows a single recursion according to the syntax of the recursive query of the SQL: 1999 standard to show the detailed structure of root identification subqueries, child identification subqueries, and closing subqueries closing a recursive query in a with clause. This is an example of creating only a temporary table and forming a recursive query against it.

10 is a diagram illustrating conditions specified in a recursive query of the SQL: 1999 standard.

FIG. 11 illustrates the meanings of the conditions specified in FIG. 10.

Figure 12 shows a recursive query of the SQL: 1999 standard and a recursive ad hoc result tuple of that query for each employee who does not have a boss in the table employees and retrieves tuples corresponding to that employee and all its direct and indirect subordinates. It's a schematic,

Fig. 13 shows the managers of departments whose department name is 'HR' or 'TFT' and the employees whose salary is more than 30000 among all the direct and indirect subordinates of each of the managers with the name 'CHANHO' for the table employees and the table departments. Recursive queries in the SQL: 1999 standard and recursive ad hoc result tuples that exclude all direct and indirect subordinates of an employee from the results and exclude subordinates with a salary less than 26000 and all direct and indirect subordinates of the employee from the result. , And plot the recursive result tuples of the query,

FIG. 14 is a representation of the recursive query of FIG. 13 (a) using the notation method of FIG.

Fig. 15 defines a recursive temporary table that obtains tuples corresponding to an employee whose employee_id is 21 and all the direct and indirect subordinates of the employee in the table employees, and fetches tuples generated by multiplying the recursive temporary table and table departments. Schematizes the recursive query of the SQL: 1999 standard, the recursive temporary result tuples of the query, and the recursive result tuple of the query,

Figure 16 shows a recursive temporary table that obtains tuples corresponding to an employee with employee_id of 10 in the table employees and all direct and indirect subordinates of the employee, and an employee with employee_id of 14 in the table employees and table departments and all direct and indirect bosses of the employee. Recursive queries in the SQL: 1999 standard and recursive temporary result tuples of the query, defining a recursive temporary table that finds tuples corresponding to the fields, and retrieving tuples created by joins between the recursive temporary tables. Of the recursive result tuple of

FIG. 17 illustrates recursive result tuple trees formed by the recursive result tuples of the recursive query of FIG.

FIG. 18 is a schematic diagram of a virtual recursive temporary result tuple tree formed by placing a virtual parent node with respect to root nodes of the recursive temporary result tuple trees of FIG.

19 illustrates the syntax of Oracle's recursive query,

20 is a diagram of the grammar of an Oracle recursive query based on details of the conditions specified in the query,

Figure 21 illustrates the meaning of the conditions specified in Oracle's recursive query,

22 illustrates the meaning of an Oracle recursive query as a recursive query of the SQL: 1999 standard.

FIG. 23 is a query of Oracle equivalent to the query of FIG. 13 (a);

FIG. 24 illustrates a query having an equivalent meaning as the query of FIG. 15A as an Oracle query.

Figure 25 shows an example of SQL queries including SQL / XML publishing functions and the result tuples of those queries,

FIG. 26 illustrates an SQL query for generating a result tuple consisting of a pair of XML values of a recursive structure and an average salary of a tuple family of the tree from each of the recursive result tuple trees of FIG. 17.

FIG. 27 shows the result tuples of the query of FIG.

FIG. 28 is an XML schema graph representing a structure of a value of the column xml_value of FIG. 27 that is an XML value.

FIG. 29 is a query based on Oracle's grammar including a query including SQL function XMLNEST in a select clause to generate XML values of FIG.

30 represents a specification of the SQL function XMLNEST,

31 is a diagram illustrating a processing flow of an SQL query including an SQL function XMLNEST.

32 illustrates a recursive query including XMLNEST and SQL existing aggregate functions in a select clause based on the syntax of the SQL: 1999 standard.

33 shows the result tuples of the query of FIG.

FIG. 34 generates an XML value of one recursive structure using the employee number and name of the employee and all direct and indirect subordinates of the employee for each employee who does not have a boss for the table employees of FIG. Based on Oracle's grammar, a query that generates an XML value consisting of a child element of the element "HIERARCHY" as a result tuple,

35 shows the result tuples of the query of FIG. 34,

FIG. 36 is a set of tuples of the employee and all the direct and indirect subordinates of the employee for each employee who does not have a boss for the table employees of FIG. 1, where <column of tuples of set X, column first_name And a single XML generated using the sum of the column values of the column salary of all tuples of the set X and the recursive XML values generated along the similarity-load relationship between the tuples as the column values of the column last_name. Based on Oracle's grammar, a query that generates one XML value generated by concatenating pairs of values> with one result tuple,

FIG. 37 shows the result tuples of the query of FIG. 36;

FIG. 38 is a query based on Oracle's grammar using XMLFOREST instead of XMLCONCAT.

39 shows the result tuples of the query of FIG. 38,

FIG. 40 is a table of employees who do not have a supervisor with respect to the table employees of FIG. 1, when the set of employees and all direct and indirect subordinates of the employees is X, (1) reflects the connection relationship of employees belonging to the set X; Recursive XML values, (2) sum of salaries of employees in set X, average, number of salaries, and (3) maximum and minimum salaries of employees in set X. However, the query is written based on Oracle's grammar so that only those employees whose salary is less than 32000 or whose salary is more than 38000 and within the range of 52800 are included in each set.

FIG. 41 illustrates recursive temporary result tuple trees formed by the recursive temporary result tuples of the query of FIG. 40 and recursive result tuple trees formed by the recursive result tuples of the query.

42 generates an XML value of one recursive structure using the information of the employee and all direct and indirect subordinates of the employee for the employee having the employee number 10 for the table employees of FIG. When X is the set of all direct and indirect subordinates of X, for each of the employees in set X, (1) the salary of the employee, (2) the sum of the salary of the employee and its subordinates, and (3) Based on Oracle's grammar, a query is generated that generates XML elements representing the sum of the employee and the number of employees in the employee.

43 shows the result tuple of the query of FIG.

44 is a diagram illustrating the processing flow of an SQL query including the SQL operator tc_root,

45 is a representation of the specification of the SQL operator tc_root,

46 is a set of the employee and all direct and indirect subordinates of the employee for each employee who does not have a boss for the table employees of FIG. 1, which corresponds to (1) the highest boss of employees belonging to the set X; The employee's name, (2) the sum of salaries of employees in set X, the average of salaries, and the number and (3) the maximum and minimum salaries of employees in set X are drawn with one result tuple, but the salary is 32000. A query written based on Oracle's grammar so that only those employees who are less than or equal to or above salary of 38000 and in the range of 52800 or less are included in each set.

FIG. 47 shows the result tuples of the query of FIG. 46;

FIG. 48 shows the set of employees and all direct and indirect subordinates of the employee for each of the managers of a department whose names are 'HR', 'RD', and 'TFT' for the table employees and table departments of FIG. (1) the name of the employee and the department belonging to the chief supervisor of the employees of set X, (2) the sum of the salaries of the employees of set X, the average, number of salaries, and (3) Withdraw the maximum and minimum salaries of the employees in the set X in one result tuple, but the manager of the department and the subordinates up to two levels below the manager, that is, employees with level 3 or less, and salaries greater than 40000 The query is written based on Oracle's grammar so that only employees within the range of less than 52800 are included in each set.

FIG. 49 illustrates recursive temporary result tuple trees formed by the recursive temporary result tuples of the query of FIG. 48 and recursive result tuple trees formed by the recursive result tuples of the query.

FIG. 50 shows the result tuples of the query of FIG. 48,

51 is a recursive XML generated by using <employee's name, employee number and name of the employee and all direct and indirect subordinates of the employee for each employee who does not have a boss for the table employees of FIG. A query that composes a result tuple of value> pairs and sorts the result tuples in ascending order based on the column value of their first result column based on Oracle's syntax and the syntax of the SQL: 1999 standard.

FIG. 52 shows the result tuples of the query of FIG. 51;

53 is a sum of the number of tuples of the tuples belonging to the set X, the sum of the salaries of the employees and the direct and indirect subordinates of the employee for each employee who does not have a boss for the table employees of FIG. Based on Oracle's grammar, a query is made that draws the average of salaries> into one result tuple and sorts the result tuples in increasing order of the employee's name, which is the chief boss of the group that generated each of the result tuples. Will,

54 shows the result tuples of the query of FIG. 53,

FIG. 55 is a diagram of an employee corresponding to the highest boss of employees belonging to the set X when X is a set of employees and all direct and indirect subordinates of the employee for each employee having no boss for the table employees of FIG. A query that creates an XML value with a recursive structure that reflects the name, the name of the department, the sum of the number and salaries of the employees in the set, and the connection of the employees in the set, and creates the value as an XML value. Is written based on Oracle's syntax,

56 shows the result tuples of the query of FIG. 55,

57 is a block diagram of a relational database system in which the present invention is implemented,

58 is a diagram schematically illustrating a process in which an SQL query is processed by SQL functions and operators proposed by the present invention.

Explanation of symbols on the main parts of the drawings

100: database system 110: database

122: database program files 124, 126: other programs

160: database management system 170: query processor

175: SQL function handler 177: SQL operator handler

180: concurrency controller 190: storage manager

200: other processes C: central processing unit

AM: Data Storage Medium PM: System Memory

Claims (29)

A method of loading a process of a database management system (DBMS) in a processor of a computing system into a memory of the computing system to perform a query for a database. Performing a recursive query against the database to generate recursive temporary result tuples and recursive result tuples; A first determination step of determining whether each of the recursive temporary result tuple trees generates only one recursive result tuple tree for each of the recursive result tuple trees formed by the nodes corresponding to the recursive result tuples; A second judging step of determining whether a recursive result tuple corresponding to a root node of the recursive result tuple tree has a recursive temporary result tuple corresponding to a root node of the recursive temporary result tuple tree as an origin tuple; And  An error signal is generated when neither one of the first determination step and the second determination step is satisfied, and for each of the recursive result tuple trees when both of the first determination step and the second determination step are satisfied. Searching for the nodes constituting the tree and generating XML values of a recursive structure with tuples corresponding to the nodes belonging to the tree. How to. The method of claim 1, wherein the XML value generating method is used in a SELECT clause of a recursive query of a DBMS that provides a recursive query form of its own or a DBMS that follows the recursive query form of the SQL: 1999 standard. The XML value of the recursive structure is implemented as an SQL function of a specific name (XMLNEST) having the function of generating the XML value of the recursive structure targeting the recursive result tuples of the query and included as part of the DBMS. How to produce. The method of claim 1, wherein in generating the recursive temporary result tuple, each of the recursive temporary result tuples has a tuple identifier root_tid of its root tuple, a tuple identifier tid of its parent, and a parent tuple of its own. Give a tuple identifier (parent_tid), and use this identifier information <root_tid, tid, parent_tid> for the recursive temporary result tuples and the recursive result tuples recursive temporary result tuple trees and recursive result tuple A method of generating an XML value of a recursive structure, each constructing a tree. The method of claim 2, wherein the SQL function XMLNEST is a hierarchical group function targeting only a hierarchy group, and the hierarchy group is formed by recursive temporary result tuples or recursive result tuples of a recursive query. A method of generating an XML value of a recursive structure characterized in that a tuple is defined as a collection of all tuples having a tuple as its collective and its ancestor tuple. The XML schema graph according to claim 2, wherein when representing the XML values of the recursive structure generated for each recursive result tuple tree in an XML schema graph, the XML schema graph has (1) exactly one cycle, and (2) a NEST_ROOT node. Must have NEST_SUBORDINATE node, and (3) NEST_SUBORDINATE node satisfies the condition of not having child nodes other than NEST_ROOT node, Here, the NEST_ROOT node means the node that starts the cycle among the nodes participating in the cycle in the XML schema graph, and the NEST_SUBORDINATE node forms a cycle with the NEST_ROOT node as a child node of the NEST_ROOT node, and any child except the NEST_ROOT node. A method of generating an XML value of a recursive structure, meaning a node having no nodes. 3. The SQL function XMLNEST of claim 2 wherein (1) the name of the NEST_ROOT element and XML attributes of the XML element, (2) the name of the NEST_SUBORDINATE element and the XML attributes of the XML element, and (3) a child of the NEST_ROOT element. A specification for the elements, and (4) a specification for specifying the alignment between sibling NEST_ROOT elements, Here, when expressing the XML values of the recursive structure generated by the recursive result tuple tree in an XML schema graph, the NEST_ROOT element is the NEST_ROOT node that is the node that starts the cycle among the nodes participating in the cycle in the XML schema graph. The NEST_SUBORDINATE element represents an XML element represented by a NEST_SUBORDINATE node, which is a node that does not have any child nodes except NEST_ROOT node, forming a cycle with the NEST_ROOT node as a child node of the NEST_ROOT node. How to generate an XML value of a recursive structure. The XML function of claim 2, wherein the SQL function XMLNEST can have XMLELEMENT, XMLCONCAT, XMLFOREST, and XMLATTRIBUTES, which are SQL / XML publishing functions of the SQL: 2003 standard, as its input parameters. How to produce. 3. The method of claim 2, wherein the SQL function XMLNEST can be used as an input argument of XMLELEMENT, XMLCONCAT, and XMLFOREST, which are SQL / XML publishing functions of the SQL: 2003 standard. The recursive result tuple of the recursive query according to claim 2, wherein the existing aggregate functions of SQL included in the SELECT clause of the recursive query together with the SQL function XMLNEST are SUM, COUNT, AVG, MIN, MAX, and XMLAGG. Classify nodes by their recursive temporary result tuple tree to which the nodes corresponding to their origin tuples belong, and treat the recursive result tuples of each classification having one or more recursive result tuples as members. And a method for generating an XML value of a recursive structure. 3. The method of claim 2, wherein the recursive result tuples corresponding to all nodes of the subtree, which are used only as direct and indirect arguments of the SQL function XMLNEST and whose node is the root, for each node of the recursive result tuple tree The recursive structure further includes 'XMLNEST related SQL functions' that calculate a value for each group and return the value to the recursive result tuple corresponding to the root node representing the group. How to generate an XML value. The NEST_SUM, NEST_AVG, NEST_MIN, NEST_MAX and NEST_COUNT functions of claim 10, wherein the XMLNEST-related SQL functions apply the value expression specified as a function argument to the recursive result tuples of each group. At least one of, The functions NEST_SUM, NEST_AVG, NEST_MIN, NEST_MAX and NEST_COUNT apply their value expressions to each of the group's recursive result tuples, the sum, average, minimum, maximum, and value of the tuple whose value is not NULL. An XML value of a recursive structure, which is an SQL group function that returns a group value to each of the recursive result tuples by calculating each number and returning it to the recursive result tuple corresponding to the root node representing the group. How to generate. 12. The method of claim 11, wherein the functions NEST_SUM, NEST_AVG, NEST_MIN, NEST_MAX and NEST_COUNT each have the same specification as SUM, AVG, MIN, MAX and COUNT in the existing aggregate functions of SQL. How to. The method of claim 2, wherein the SQL function XMLNEST is characterized by a group function that processes one recursive result tuple tree into one group, so that a subquery including XMLNEST in a recursive query cannot include a group by clause. A method for generating an XML value of a recursive structure, characterized in that it has a constraint. The method of claim 1, wherein the DBMS is a DBMS that supports recursive queries and SQL / XML publishing functions of the SQL: 2003 standard. 15. The method of claim 14, wherein said DBMS is an object-relational DBMS or a relational DBMS. The method of claim 1, wherein the method for searching the nodes constituting the tree for each of the recursive result tuple trees is performed by a depth-first search method for the nodes. How to generate XML value of recursive structure for. 3. The method of claim 2, wherein the DBMS providing its own recursive query form is an Oracle 11g or higher version of the DBMS. The method of claim 1, wherein the recursive temporary result tuple tree represents the tuples as nodes for the recursive temporary result tuples of the recursive temporary table generated during the recursive query on the database, Trees formed by connecting nodes corresponding to tuples of a parent-child relationship with a direction edge from a parent node to a child node, The recursive result tuple tree targets the recursive result tuples and expresses the recursive result tuples as nodes only if all of the recursive result tuples are generated by singular copying of the recursive temporary result tuples. And trees formed by connecting nodes corresponding to tuples of a parent-child relationship with a direction edge from a parent node to a child node. In the processor of the computing system to load a process of the database management system (DBMS) into the memory of the computing system to perform a query for the database, A method of performing a recursive temporary result tuples and recursive result tuples generated by performing a recursive query on the database by executing an SQL hierarchical group operator of a specific name (tc_root), The SQL hierarchy group operator tc_root is Used in a recursive query of a DBMS that provides a recursive query form so that all recursive result tuples of that query are generated by singular copies of recursive ad hoc result tuples to their origin tuples. Returns a value for each group by classifying all the recursive result tuples of each classification that have one or more recursive result tuples as members by classifying the recursive temporary result tuple tree to which the corresponding nodes belong. A method for arithmetic processing data of a database, characterized in that it has a function to do so. 20. The method of claim 19, wherein the function of the SQL hierarchy operator tc_root is: A singular replication determining step of determining whether the recursive result tuples are generated by a singular copy of the recursive temporary result tuples; Generating an error signal if the determination result of the singular replication determination step is false; And (I) classifying the recursive result tuples by the recursive temporary result tuple tree to which nodes corresponding to their origin tuples belong; and (ii) if the judgment result of the singular replication judgment step is true; For each group of classified recursive result tuples, the value specified as the argument of the SQL hierarchy operator tc_root using the column values of the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree corresponding to that group. A method for arithmetic processing data of a database comprising functions of performing a return step of calculating and returning a value of an expression. 21. The SQL hierarchical group operator tc_root uses the column values of the recursive temporary result tuple corresponding to the root node of the recursive temporary result tuple tree by taking an arbitrary value expression of the SQL: 2003 standard as an argument. A method of computing data in a database comprising a specification for generating a value of a value expression. The subquery including tc_root in a recursive query has a group by clause because the SQL hierarchical group operator tc_root has characteristics of a group operator, but the groups targeted by tc_root are formed based on recursive temporary result tuple trees. Method for arithmetic data of a database, characterized in that it has a constraint that it can not include. 21. The method of claim 20, wherein SUM, COUNT, AVG, MIN, MAX, and XMLAGG, which are existing aggregate functions of SQL included in the same subquery of the recursive query together with the SQL hierarchical operator tc_root, respectively, are used for recursive result tuples of the query. Nodes corresponding to their origin tuples are classified by recursive temporary result tuple tree to which all the recursive result tuples of each classification having one or more recursive result tuples are treated as one group. How to compute data in a database. 20. The method of claim 19, wherein the SQL hierarchy operator tc_root is an operator used for a recursive query of a DBMS that provides a recursive query form of its own or a DBMS that follows the recursive query form of the SQL: 1999 standard. How to process data in a database. A computer-readable recording medium having a computer recorded thereon a program for executing the method of any one of claims 1 to 24. A computer readable medium having recorded thereon a program for causing the computer to execute the method of any one of claims 1 to 24, a memory for loading the program from the medium, and a program loaded in the memory. A computer system comprising a central processing unit. On your computer, A first function of constructing recursive result tuple trees with nodes corresponding to recursive result tuples of queries generated when a recursive query is made to a database of a database management system (DBMS) that supports a recursive query form; A computer-readable recording medium having recorded thereon a program for realizing a second function of processing each recursive result tuple tree into one group and generating an XML value of one recursive structure for the tree. The method of claim 27, The program includes an SQL hierarchy group function XMLNEST including the first function and the second function, The program, For each node of the recursive result tuple tree, which is used only as a direct and indirect argument of the SQL hierarchical group function XMLNEST, the recursive result tuples corresponding to all nodes of the subtree rooted at that node are regarded as a group. The program may further include 'XMLNEST related SQL functions' including a function of calculating a value for a group and returning the value to a recursive result tuple corresponding to the root node representing the group. Computer-readable recording medium. The method of claim 27 or 28, wherein the program, Used in a recursive query of a DBMS providing a recursive query form so that all recursive result tuples of that query are generated by singular copies of recursive ad hoc result tuples to their origin tuples. By classifying the recursive temporary result tuple tree to which the corresponding nodes belong and processing all the recursive result tuples of each classification that have one or more recursive result tuples as members, one value for each group is obtained. And a SQL hierarchical group operator tc_root having a function to return.

Images