笔记：Oracle SQL 高级编程 第1章 SQL 核心

1、SELECT 语句的标量子查询 

select c.customer_id, c.cust_first_name||' '||c.cust_last_name as fullname,
(select e.last_name from hr.employees e where e.employee_id = c.account_mgr_id ) as acct_mgr
 from oe.customers c;

标量子查询特点：

(1). 只能返回一行一列的值

(2). 在结果集中每一行都执行一次，可能有严重性能问题

2、多表插入

有三个表 small_customers,  medium_customers,  large_customers. 执行如下插入

insert all
when sum_orders < 1000 then into small_customers 
when sum_orders >= 1000  and  sum_orders < 10000 then into medium_customers   
else into large_customers 
select o.customer_id, sum(o.order_total) as sum_orders from oe.orders o group by o.customer_id  
 

3. MERGE 语句

相当于saveOrUpdate。

假设已经有如下表：

create table dept60_bonuses( 
employee_id number,
bonus_amt number);

表中已经存在数据：

EMPLOYEE_ID  BONUS_AMT
----------- ----------
        103          0
        104        100
        105        200
        106        300

 

则用Merge 语句 向该表中插入或更新数据：

merge into dept60_bonuses db 
using (select e.employee_id ,e.salary  from hr.employees e where e.department_id = 60) e2
on (db.employee_id = e2.employee_id)
when matched then update   set db.bonus_amt = e2.salary*2 where e2.salary > 7500
when not matched then insert values (e2.employee_id, e2.salary*2) where e2.salary < 7500 ;

结果：

EMPLOYEE_ID  BONUS_AMT
----------- ----------
        103      18000
        104        100
        105        200
        106        300
        107       8400

可见103 是更新的，107是插入的