WITH子句

📄字数 1.9K

👁️阅读量加载中...

公共表表达式CTE（Common Table Expression）使用WITH子句定义，可以认为是存在于查询中的临时表，生命周期随当前语句，不作为实际对象存储，可以被看作为单个SQL语句（实际是单个或多个查询组合而成）。

如果一个查询语句需要重复使用，可以使用WITH实现SQL重用。CTE后面也可以衔接其他的CTE，但只能使用一个WITH，多个CTE中间用英文逗号分隔。若WITH中未指定列且返回中存在系统默认设置的列，则数据库会自动补齐列名如EXPR1，随后依次递增。

WITH ProcDef子句用于在SQL语句中临时声明并定义存储过程或函数，这些存储过程或函数可以在其作用域内被引用。相比模式对象中的存储过程或函数，通过WITH ProcDef定义的存储过程或函数在对象名解析时拥有更高的优先级。WITH ProcDef定义的存储过程或函数也不会存储到系统表中，仅在当前SQL语句内有效。

一、语法格式

二、参数说明

with_name：
- ColId：CTE的名称，用于在后续查询中引用这个临时结果集
- ( ( name_list ) )：可选的CTE列名列表，使用,逗号分隔
AS select_with_parens：定义CTE的查询语句，其结果将作为CTE的临时结果集，请参阅有括号查询-select_with_parens
ProcDef：扩展语法，定义存储过程或存储函数，请参阅存储过程/存储函数

注意

WITH ProcDef功能暂不支持多个存储过程/函数
定义的函数的作用域为所在的查询表达式内

三、示例

示例1

sql

-- 单个WITH子句
SQL> WITH with1 AS (SELECT 'abc' FROM dual) SELECT *   FROM with1;

+-------+
| EXPR1 |
+-------+
| abc   |
+-------+

(1 row)
Use time:0 ms.

-- 多个WITH子句
SQL> WITH with1 AS (SELECT 'one' FROM dual),with2 AS   (SELECT 'two' FROM dual) SELECT * FROM with1 UNION   SELECT * FROM with2;

+-------+
| EXPR1 |
+-------+
| one   |
| two   |
+-------+

(2 rows)
Use time:10 ms.

示例2
使用WITH FUNCTION和WITH PROCEDURE定义临时函数和临时存储过程：

sql

SQL> CREATE TABLE tab_with_func(id INT,name VARCHAR  (20));

Execute successful.
Use time:39 ms.

SQL> INSERT INTO tab_with_func VALUES(1,'aa');

Total 1 records effected.
Use time:1 ms.

-- WITH FUNCTION
SQL> WITH FUNCTION with_function(p_id IN NUMBER)   RETURN NUMBER IS BEGIN RETURN p_id; END; SELECT   with_function(id) FROM tab_with_func WHERE rownum = 1;

+-------+
| EXPR1 |
+-------+
| 1     |
+-------+

(1 row)
Use time:8 ms.

-- WITH PROCEDURE
SQL> WITH PROCEDURE with_procedure(p_id IN NUMBER) IS   BEGIN DBMS_OUTPUT.put_line('p_id='||p_id); END;SELECT   id FROM tab_with_func WHERE rownum = 1;

+----+
| ID |
+----+
| 1  |
+----+

(1 row)
Use time:5 ms.

-- 使用动态SQL调用`WITH FUNCTION`：
SQL> CREATE TABLE tab_with_func1(id INT,name VARCHAR  (20));

Execute successful.
Use time:36 ms.

SQL> INSERT INTO tab_with_func1 VALUES(1,'AB');

Total 1 records effected.
Use time:0 ms.

SQL> DECLARE
         l_sql VARCHAR2(32767);
         l_cursor SYS_REFCURSOR;
         l_value NUMBER;
     BEGIN
         l_sql := 'WITH
                   FUNCTION with_function(p_id IN   NUMBER) RETURN NUMBER IS
                   BEGIN
                       RETURN p_id;
                   END;
                   SELECT with_function(id) FROM   tab_with_func1 WHERE rownum = 1';
         OPEN l_cursor FOR l_sql;
         FETCH l_cursor INTO l_value;
         DBMS_OUTPUT.PUT_LINE('输出:l_value=' ||   l_value);
         CLOSE l_cursor;
     END;
     /
输出:l_value=1

Execute successful.
Use time:6 ms.

WITH子句 ​

一、语法格式 ​

二、参数说明 ​

三、示例 ​

WITH子句

一、语法格式

二、参数说明

三、示例