Oracle SQL 基础
一、插入数据 insert all、insert first(9i之后的新特征)
作用:同时向多个表中插入数据
无条件insert all
insert all
into insert_all_test1 values(no,name)
into insert_all_test2 values(no,sal)
select empno no,ename name,sal from scott.emp;
有条件insert all
insert all
when sal > 2000 then
into insert_all_test1 values(no,name)
when sal < 2500 then
into insert_all_test2 values(no,sal)
else
into insert_all_test3 values(no,name,sal)
select empno no,ename name,sal from scott.emp;
insert first
说明：当使用first操作符执行多表插入时，如果数据已经满足了先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。
Insert First
when id=1 then into z_test1 values(id,name)
when id>5 then into z_test2 values(id)
else into z_test3 values(id)
select * from z_test;
一、MERGE INTO
作用：Oracle中用一条SQL语句直接进行Insert/Update的操作（9i后新特征，10G后重定义，慎用）
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category != 'BOOKS'
或
merge into products p
using newproducts np
on(p.product_id = np.product_id)
when matched then
update
set p.product_name = np.product_name
delete where category = 'macle1_cate';
一、Rollup/Cube/Grouping sets
1.Rollup 额外生成横向小计和总计
select corp_code,org_level,count(1)
from tb_sys_organization
group by rollup(corp_code,org_level)
--group by rollup((corp_code,org_level)) 两个括号是用于复合列的情况，结果和一个括号不同
select corp_code,org_level,count(1)
from tb_sys_organization
group by corp_code,rollup(org_level);
2.Cube 额外生成横向小计、纵向小计和总计
select corp_code,org_level,count(1)
from tb_sys_organization
group by cube(corp_code,org_level)
--group by cube((corp_code,org_level)) cube((corp_code,org_level))和上面的rollup((corp_code,org_level))结果集一致
select corp_code,org_level,count(1)
from tb_sys_organization
group by corp_code,cube(org_level);
3.Grouping sets 合并多个分组的统计结果，只生成小记录行数
select corp_code, org_level, count(1)
from tb_sys_organization
group by grouping sets(corp_code, org_level);
select corp_code, org_level, count(1)
from tb_sys_organization
group by grouping sets(corp_code),grouping sets(org_level)--多个的情况结果集和cube((corp_code,org_level))一样，但少了一行总统计
4.另外他们均能与函数grouping结合使用，例如:
select corp_code,org_level,count(1),
grouping(corp_code),
grouping(org_level)
from tb_sys_organization
group by rollup(corp_code, org_level);
select case grouping(corp_code)
when 1 then 'all_corp' else corp_code end corp_code,
case grouping(org_level)
when 1 then 'all_org' else org_level end org_level,
count(1)
from tb_sys_organization
group by rollup(corp_code, org_level);
函数grouping用于确定统计结果是否用到特定列。如果函数返回0，则表示统计结果使用了该列，如果返回1，则表示统计结果未使用该列。
一、另类的SQL游标
SQL%ISOPEN --确定SQL游标是否打开
SQL%FOUND和SQL%NOTFOUND --确定SQL语句执行是否成功
SQL%ROWCOUNT --返回SQL语句所作用的总行数
DECLARE
v1 VARCHAR2 (20);
BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE deptno = 5;
IF sql%FOUND
THEN
DBMS_OUTPUT.put_line ('更新共' || sql%ROWCOUNT || '行');
ELSE
DBMS_OUTPUT.put_line ('Hello');
END IF;
END;
9I开始可以用默认值更行
update emp set job=default where ename='scott';
保存点
1.设置保存点
savepoint a; 或 dbms_transaction.savepoint('a');
2.回滚到保存点
rollback to a; 或 dbms_transaction.rollback_savepoint('a');
回滚全部事务
rollback 或 dbms_transaction.rollback
只读事务
set transaction read only; 或 dbms_transaction.read_only --必须是事务开始的第一句
只读事务是指本会话值允许执行查询操作，而不允许执行任何DML操作的事务。
设置只读事务后，尽管其他会话可能会提交新事务，但只读事务将不会取得新的数据变化。
顺序事务
为了使用户可以取得特定时间点的数据，并且允许执行DML操作，可以使用顺序事务。
set transaction isolation level serializable.