Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use DMSQL WITH FUNCTION clause". In daily operation, I believe many people have doubts about how to use DMSQL WITH FUNCTION clause. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use DMSQL WITH FUNCTION clause". Next, please follow the editor to study!

WITH FUNCTION clause

The WITH FUNCTION clause is used to temporarily declare and define stored functions in a SQL statement that can be referenced within their scope. Compared with the storage function in the schema object, the storage function defined by WITH FUNCTION has higher priority in object name resolution. Similar to the common table expression CTE, stored function objects defined by WITH FUNCTION are not stored in the system table and are only valid within the current SQL statement.

The WITH FUNCTION clause is suitable for scenarios that occasionally need to use stored procedures. Compared with the stored functions in the schema object, it can clearly see the function definition and avoid the overhead of DDL operations.

Grammatical format

WITH

< 函数>

{

< 函数>

}

Parameters.

1.

< 函数>

The syntax follows the syntax rules of stored procedures in DMSQL programming.

Sentence function

Allows the user to define a storage function for temporary use within the same statement.

instructions

1. the scope of the function defined in is within the query expression in which it is located

2. The function name must not be repeated in the same medium.

3. The function defined in cannot be external.

The consumer of this statement does not require CREATE PROCEDURE database permissions.

Give examples to illustrate

For example, the function defined in WITH FUNCTION takes precedence over the schema object.

SQL > WITH FUNCTION F1 (C INT) RETURN INT AS BEGIN RETURN C * 10; END;2 SELECT F1 (5236) FROM DUAL;3 / LINEID F1 (5236)-- 1 52360used time: 1.352 (ms). Execute id is 34061.

For example, the example of mixing WITH FUNCTION and common table expressions.

SQL > WITH FUNCTION f21 (C1 INT) RETURN INT AS BEGIN RETURN C1; END;2 SELECT f21 (1) FROM dual WHERE 100 IN3 (4 WITH FUNCTION f22 (C1 INT) RETURN INT AS BEGIN RETURN C1 + 2; END;5 FUNCTION f23 (C1 INT) RETURN INT AS BEGIN RETURN C1-2; END;6 v21 (C) AS (SELECT 50 FROM dual) 7 SELECT f22 (C) + f23 (C) FROM v218) 9 / LINEID F21 (1)-- 1 1used time: 12.313 (ms). Execute id is 34092.

Common table expression clause

Nested SQL statements with too many levels can make SQL statements difficult to read and maintain. Putting subqueries in temporary tables makes SQL statements easier to maintain, but it also adds additional Imax O overhead, so temporary tables are not suitable for situations with large amounts of data and frequent queries. For this reason, common table expressions (CTE,COMMON TABLE EXPRESSION) are introduced into DM7. Using CTE can improve the maintainability of SQL statements, and CTE is much more efficient than temporary tables. CTE is similar to a derived table in that it is not stored as an object and is only valid during the query. Unlike derived tables, CTE can be self-referenced and can be referenced multiple times in the same query.

The WITH AS phrase, also known as the subquery part (SUBQUERY FACTORING), defines a SQL fragment that is used by the entire SQL statement. It can effectively improve the readability of SQL statements, and can also be used in different parts of UNION ALL as part of providing data.

The role of common table expressions

The common table expression (CTE) is a temporary named result set defined in the query and will be used in the FROM clause. Each CTE is defined only once (but can be referenced any number of times within its scope) and will survive during the lifetime of the query, and you can use CTE to perform recursive operations.

Because each part of the UNION ALL may be the same, but if each part is executed once, the cost is too high, so you can use the WITH AS phrase, just execute it once. If the table name defined by the WITH AS phrase is called more than twice, the optimizer automatically puts the data obtained by the WITH AS phrase into a temporary table, but not if it is called only once, and many queries can be speeded up in this way.

The use of common table expressions

Grammatical format

WITH

< 公用表表达式子句>

{

< 公用表表达式子句>

}

< 公用表表达式子句>

:: =

< 公用表表达式名[ ( {,< 列名>

})] AS (common table expression subquery statement) >

Parameters.

1.

< 公用表表达式名>

Valid identifier of common table expression

two。

< 列名>

Indicates the name of the column in the common table expression that is created

3.

< 公用表表达式子查询语句>

Identifies the rows and columns of the table on which the common table expression is based, whose syntax follows the syntax rules of the SELECT statement.

Sentence function

Allows users to define common table expressions, that is, WITH AS statements.

instructions

1.

< 公用表表达式名>

The name of the common table expression must be different from any other common table expression defined in the same WITH clause, but the common table expression name can be the same as the name of the base table or base view. Any reference to the common table expression name in the query uses the common table expression instead of the base object

two。

< 列名>

Duplicate column names are not allowed in a CTE definition. The specified number of column names must be the same as

< 公用表表达式子查询语句>

The number of columns in the result set matches. The list of column names is optional only if different names are provided for all result columns in the query definition.

3.

< 公用表表达式子查询语句>

Specifies a SELECT statement that populates a common table expression with a result set. Except that CTE cannot define another CTE

< 公用表表达式子查询语句>

The SELECT statement of must meet the same requirements as when creating a view

4. The common table expression must be followed directly by the SQL statement using CTE, otherwise it is invalid.

The user of this statement must set the

< 查询说明>

Each table in has SELECT permission.

Give examples to illustrate

A common table expression can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

For example, create a table TEST1 and table TEST2, and use common table expressions to join them.

SQL > CREATE TABLE TEST1 (I INT); executed successfullyused time: 17.257 (ms). Execute id is 34224.SQL > INSERT INTO TEST1 VALUES (1); affect rows 1used time: 1.008 (ms). Execute id is 34226.SQL > INSERT INTO TEST1 VALUES (2); affect rows 1used time: 0.712 (ms). Execute id is 34227.SQL > CREATE TABLE TEST2 (J INT); executed successfullyused time: 42.221 (ms). Execute id is 34229.SQL > INSERT INTO TEST2 VALUES (5); affect rows 1used time: 1.104 (ms). Execute id is 34230.SQL > INSERT INTO TEST2 VALUES (6); affect rows 1used time: 0.696 (ms). Execute id is 34232.SQL > INSERT INTO TEST2 VALUES (7); affect rows 1used time: 0.664 (ms). Execute id is 34234.SQL > WITH CTE1 (K) AS (SELECT I FROM TEST1 WHERE I > 1), 2 CTE2 (G) AS (SELECT J FROM TEST2 WHERE J > 5) 3 SELECT K, G FROM CTE1, CTE2;LINEID K G-12622 7used time: 1.692 (ms). Execute id is 34237.

For example, a common table expression is used to insert records from table TEST1 into a TEST2 table.

SQL > INSERT INTO TEST2 WITH CTE1 AS (SELECT * FROM TEST1) 2 SELECT * FROM CTE1;affect rows 2used time: 1.048 (ms). Execute id is 34247.SQL > SELECT * FROM TEST2;LINEID J-- 1 52 63 74 15 2used time: 1.135 (ms). Execute id is 34249. At this point, the study of "how to use DMSQL WITH FUNCTION clauses" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.