Oracle 26ai的SQL语言增强特性_MsSqlserver

1. group by all 语法

用途：自动按 select 列表中所有非聚合列分组，无需显式列出所有列。

-- 传统写法：需要显式列出所有非聚合列
select department_id, job_id, avg(salary) as avg_sal
from employees
group by department_id, job_id;
-- 26ai 新写法：使用 group by all 自动包含所有非聚合列
select department_id, job_id, avg(salary) as avg_sal
from employees
group by all;

适用场景：当 select 列表有很多列时，简化代码并避免遗漏。

2. qualify 子句（窗口函数过滤）

用途：直接过滤窗口函数的结果，无需嵌套子查询。

-- 传统写法：需要子查询或 cte
with ranked as (
    select employee_id, first_name, salary,
           rank() over (order by salary desc) as salary_rank
    from employees
)
select * from ranked where salary_rank <= 5;
-- 26ai 新写法：使用 qualify 直接过滤
select employee_id, first_name, salary,
       rank() over (order by salary desc) as salary_rank
from employees
qualify salary_rank <= 5;
-- 另一个示例：查找每个部门工资前 3 的员工
select department_id, first_name, salary,
       row_number() over (partition by department_id order by salary desc) as rn
from employees
qualify rn <= 3;

3. uuid() 函数（rfc 9562 版本 4）

用途：生成标准 uuid，替代手动生成或调用外部函数。

-- 生成单个 uuid
select uuid() from dual;
-- 结果示例：550e8400-e29b-41d4-a716-446655440000
-- 插入数据时自动生成 uuid
create table users (
    user_id raw(16) default uuid() primary key,
    username varchar2(50),
    created_at timestamp default current_timestamp
);
insert into users (username) values ('zhangsan');
-- user_id 自动填充为 uuid
-- 批量生成 uuid
select uuid() as id, first_name 
from employees 
where department_id = 10;

4. group by 支持列别名或位置编号

用途：使用 select 列表中的别名或位置号进行分组，更简洁。

-- 传统写法：重复表达式或使用列名
select substr(hire_date, 1, 4) as hire_year, count(*) as cnt
from employees
group by substr(hire_date, 1, 4);
-- 26ai 新写法：使用列别名
select substr(hire_date, 1, 4) as hire_year, count(*) as cnt
from employees
group by hire_year;
-- 使用位置编号（从 1 开始）
select department_id, job_id, avg(salary) as avg_sal
from employees
group by 1, 2;  -- 按第 1 列(department_id)和第 2 列(job_id)分组
-- 混合使用别名和位置编号
select department_id, job_id, avg(salary) as avg_sal
from employees
group by 1, job_id;  -- 位置 1 和别名混用

5. 非位置 insert 子句（命名列插入）

用途：通过列名而非位置指定插入值，顺序无关，更清晰。

-- 传统写法：必须按列定义顺序，values 顺序必须与列列表一致
insert into employees (employee_id, first_name, last_name, email, hire_date, job_id)
values (207, 'john', 'smith', 'jsmith', sysdate, 'it_prog');
-- 26ai 新写法：命名列插入，可以任意顺序指定
insert into employees (
    employee_id => 207,
    last_name => 'smith',
    first_name => 'john',
    job_id => 'it_prog',
    email => 'jsmith',
    hire_date => sysdate
);
-- 批量插入时更清晰
insert all
    into employees (employee_id => 208, last_name => 'wang', first_name => 'wei', job_id => 'sa_rep')
    into employees (employee_id => 209, first_name => 'li', last_name => 'hua', job_id => 'mk_man')
select 1 from dual;

到此这篇关于oracle 26ai的sql语言增强特性的文章就介绍到这了,更多相关oracle 26ai增强特性内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！