笔记：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 allwhen 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) e2on (db.employee_id = e2.employee_id)when matched then update   set db.bonus_amt = e2.salary*2 where e2.salary > 7500when 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是插入的