数据库开发时使用到的技巧记录

oracle

with 创建临时表

在 Oracle 数据库中，WITH 语句也被称为子查询事实表或公共表表达式 (CTE)。它可以用来创建一个只在当前查询中有效的临时表。你可以在同一个查询中多次引用这个临时表，这对于编写复杂的 SQL 查询非常有 WITH 语句的基本用法如下：

WITH temp_table AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)

SELECT column1, column2, ...
FROM temp_table
WHERE conditions;

在这个例子中，temp_table就是我们创建的临时表，它包含从 table_name中选取的数据。我们可以在后续的查询中，像使用普通数据库表一样使用这个临时表。

如果你需要在一个查询中创建多个临时表，可以使用逗号将它们分隔开，如下所示：

WITH temp_table1 AS (
    SELECT column1, column2, ...
    FROM table_name1
    WHERE conditions
), temp_table2 AS (
    SELECT column1, column2, ...
    FROM table_name2
    WHERE conditions
)

SELECT column1, column2, ...
FROM temp_table1
JOIN temp_table2 ON conditions;

在这个例子中，我们创建了两个临时表 temp_table1和 temp_table2，然后在最后的查询中将它们进行联接。

字符串拼接函数

Oracle WM_CONCAT () 函数

Oracle WM_CONCAT () 函数用于将多个字符串值连接成单个字符串值。该函数的语法如下：

WM_CONCAT(string_expression)

其中：

• string_expression：要连接的字符串表达式。可以是列名、常量或表达式。

用法

WM_CONCAT () 函数可以用于将多个字符串值连接成单个字符串值，并将其显示在查询结果中。例如，以下查询将连接表 customers 中的 first_name 和 last_name 列，并将结果显示在 full_name 列中：

SELECT customer_id, WM_CONCAT(first_name || ' ' || last_name) AS full_name
FROM customers;

结果如下：

customer_id | full_name
------------+------------------------
1           | John Smith
2           | Mary Johnson
3           | Robert Jones

WM_CONCAT () 函数还可以用于将多个字符串值连接成单个字符串值，并将其存储在表中。例如，以下查询将连接表 customers 中的 first_name 和 last_name 列，并将结果存储在 full_name 列中：

UPDATE customers
SET full_name = WM_CONCAT(first_name || ' ' || last_name);

注意

• WM_CONCAT () 函数返回的字符串值的最大长度为 4000 字节。
• WM_CONCAT () 函数可以连接多个字符串值，但每个字符串值的最大长度不能超过 4000 字节。
• WM_CONCAT () 函数可以连接空字符串值，但空字符串值不会影响连接结果。
• WM_CONCAT () 函数可以连接重复的字符串值，但重复的字符串值只会显示一次。

示例

SELECT WM_CONCAT(DISTINCT department_name)
FROM departments;

-- 结果：
--
-- Sales
-- Marketing
-- Finance
-- Human Resources

SELECT WM_CONCAT(DISTINCT department_name, ', ')
FROM departments;

-- 结果：
--
-- Sales, Marketing, Finance, Human Resources

SELECT WM_CONCAT(DISTINCT department_name || ' (' || department_id || ')')
FROM departments;

-- 结果：
--
-- Sales (10), Marketing (20), Finance (30), Human Resources (40)

START WITH … CONNECT BY 的用法

START WITH 子句用于指定递归查询的开始点。它与 CONNECT BY 子句一起使用，用于在层次数据上执行递归查询。

START WITH 子句的语法如下：

START WITH <condition>

其中，<condition> 是一个条件表达式，用于指定递归查询的开始点。

例如，以下查询使用 START WITH 子句来查找所有从员工 1 开始的员工：

SELECT employee_id, manager_id, employee_name
FROM employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;

上面的查询将返回以下结果：

+------------+------------+--------------+
| employee_id | manager_id | employee_name |
+------------+------------+--------------+
| 1           | NULL        | John Smith    |
| 2           | 1           | Jane Doe      |
| 3           | 2           | Michael Jones |
| 4           | 3           | Mary Johnson  |
+------------+------------+--------------+

START WITH 子句还可以与其他条件一起使用。例如，以下查询使用 START WITH 子句来查找所有从员工 1 开始且工资高于 10000 美元的员工：

SELECT employee_id, manager_id, employee_name
FROM employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id
AND salary > 10000;

上面的查询将返回以下结果：

+------------+------------+--------------+
| employee_id | manager_id | employee_name |
+------------+------------+--------------+
| 1           | NULL        | John Smith    |
| 2           | 1           | Jane Doe      |
| 3           | 2           | Michael Jones |
+------------+------------+--------------+

START WITH 子句对于在层次数据上执行递归查询非常有用。它可以用于查找所有从给定节点开始的节点、查找给定节点的所有祖先节点或查找给定节点的所有子孙节点。