最新整理版本：http://leeyee.github.io/blog/2011/11/14/plsql-sql-cud/
1 子查询插入数据
1.1 使用子查询插入数据
INSERT INTO customer(name,age) SELECT name,age FROM employee WHERE eNo = 200;
1.2 使用子查询执行直接装载
INSERT /*+APPEND */ INTO customer(name,age) SELECT name,age FROM employee WHERE eNo = 200;
Notes:
1、以上两条语句的执行结果一样，但2使用了 /*+APPEND */ 来表示采用直接装载方式;
2、当要装载大批量数据是，采用2方法装载数据的速度要远远优于1。具体区别可查看其执行计划查看;
2 多表插入
2.1 语法：
INSERT coditional_insert_clause subquery;
insert_into_clause 用于指定 INSERT 语句；
value_clause 用于指定值子句；
subquery 用于指定提供数据的子查询；
conditional_insert_clause 用于指定 INSERT 条件子句；
2.2 示例：
2.2.1 使用 ALL 操作符执行多表插入
2.2.1.1 不指定插入列
INSERT ALL WHEN cId = 1 THEN INTO customer1 WHEN age > 20 THEN INTO customer2 ELSE INTO customer3 SELECT * FROM customer;
2.2.1.2 指定插入列
INSERT ALL WHEN cId = 1 THEN INTO customer1 (cId, name, age) WHEN age > 20 THEN INTO customer2 (cId, name, age) ELSE INTO customer3 (cId, name, age) SELECT cId, name, age FROM customer;
2.2.2 使用 FIRST 操作符执行多表插入
当使用 FIRST 操作符执行多表插入时，如果数据已经满足先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。
INSERT ALL WHEN cId = 1 THEN INTO customer1 WHEN age > 20 THEN INTO customer2 -- 如果age>20的数据中包含cId=1的数据，那么该条数据将不会被再次插入customer2 ELSE INTO customer3 SELECT * FROM customer;
3 TRUNCATE TABLE 截断表
TRUNCATE TABLE emp;
Notes： 与 DELETE 的区别
1、DELETE 删除表的所有数据时，不会释放表所占用的空间。
2、TRUNCATE 删除表时，不仅会删除表的所有数据，还会释放表所占用空间
3、DELETE 操作可以回滚，而TRUNCATE则无法回滚
4 事务
4.1 事务保存点
SQL> INSERT table_name ... SQL> DELETE table_name ... SQL> SAVEPOINT A SQL> UPDATE table_name ... SQL> SAVEPOINT B SQL> DELETE table_name ... SQL> ROLLBACK TO B -- 回滚到事务保存点B SQL> ROLLBACK TO A -- 回滚到事务保存点A SQL> ROLLBACK -- 回滚所有事务
4.2 只读事务
只读事务只允许执行查询操作，而不允许执行任何DML操作的事物。使用只读事务可以获取特定的时间点的数据.
-- 会话A SQL> SET TRANSACTION READ ONLY; -- 1 设置当前事务为只读事务。 SQL> SELECT * FROM emp; -- 3 由于设置了当前事务为只读事务，此时其他会话对表的更新等操作都不会影响该查询SQL。该SQL获取的仍是其他会话更新前的数据 -- 会话B SQL> UPDATE emp SET sal = 3000 where ename = 'jack'; -- 2 SQL> COMMIT; 假设会话A在 1 设置了只读事务，会话B在 2 更新了数据，那么会话A在3处得到的数据是在时间点1时的数据，而不是会话B更新后的数据
或者：
SQL> exec dbms_transaction.read_only;
4.3 顺序事务
使用顺序事务时,除了具有只读事务的特点外,顺序事务允许执行DML操作.
-- 会话A SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 1 SQL> SELECT sal FROM emp where ename = 'jack'; --3 SQL> UDPATE dept SET loc = 'beijing' WHERE deptno = 6; SQL> COMMIT; -- 会话B SQL> UPDATE emp SET sal = 3000 where ename = 'jack'; --2 SQL> COMMIT; 假设会话A在 1 设置了顺序事务，会话B在 2 更新了数据，那么会话A在 3 查询时将会获取时间点在 1 的数据，而不是会话B在 2 更新后的数据。
5 查询分组
5.1 ROLLUP
ROLLUP 操作符在生成原有统计结果的基础上，还会生成横向小计结果。
SELECT name,address ,SUM(money) FROM customer GROUP BY ROLLUP(name,address);
NAME ADDRESS COUNT(*) SUM(MONEY)
------------------ ------- ---------- ----------
oxcow abcdefg 2 118.03
oxcow 2 118.03
leeyee a 1 32
leeyee 152号大街 1 12.23
leeyee 2 44.23
4 162.26
5.2 CUBE
CUBE 操作符在生成原有统计结果的基础上，还会生成横向小计、纵向小计结果。
SELECT name,address ,SUM(money) FROM customer GROUP BY CUBE(name,address);
NAME ADDRESS COUNT(*) SUM(MONEY)
-------------- -------------- ---------- ----------
4 162.26
a 1 32
abcdefg 2 118.03
152号大街 1 12.23
oxcow 2 118.03
oxcow abcdefg 2 118.03
leeyee 2 44.23
leeyee a 1 32
leeyee 152号大街 1 12.23
5.3 GROUPING
GROUPING 函数用于确定统计结果是否用到了特定列。如果返回0，则表示统计结果使用了该列；如果返回1，则表示统计结果未使用该列
SELECT name,address ,SUM(money),GROUPING(name),GROUPING(address) FROM customer GROUP BY CUBE(name,address);
NAME ADDRESS COUNT(*) SUM(MONEY) GROUPING(name) GROUPING(address)
---------------------------- ------- ---------- --------- -------------- -----------------
4 162.26 1 1
a 1 32 1 0
abcdefg 2 118.03 1 0
152号大街 1 12.23 1 0
oxcow 2 118.03 0 1
oxcow abcdefg 2 118.03 0 0
leeyee 2 44.23 0 1
leeyee a 1 32 0 0
leeyee 152号大街 1 12.23 0 0
5.4 GROUPING SETS
GROUPING SETS 操作符可以合并多个分组结果。
SELECT name, COUNT(*) FROM customer GROUP BY name;
NAME COUNT(*)
---------------------------------------- ----------
leeyee 2
oxcow 2
SELECT address,COUNT(*) FROM customer GROUP BY address
ADDRESS COUNT(*)
---------------------------------------- ----------
a 1
abcdefg 2
152号大街 1
SELECT name,address,COUNT(*) FROM CUSTOMER GROUP BY GROUPING SETS(name,address);
NAME ADDRESS COUNT(*)
----- --------- ----------
leeyee 2
oxcow 2
a 1
abcdefg 2
152号大街 1
6 内连接和外连接
6.1 语法
SELECT table1.column, table2.column
FROM table1 [INNER | LEFT | RIGTH | FULL ] JOIN table2 ON
table1.column1 = table2.column2;
6.2 内连接
内连接用于返回满足连接条件的所有记录；默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询都属于内连接。
SELECT a.dname,b.ename FROM department a, employee b WHERE a.deptNo = b.deptNo; -- 或者 SELECT a.dame,b.ename FROM department a INNER JOIN employee b ON a.deptNo = b.deptNo;
从Ooracle9i 开始，如果主表主键列和从表外键列名称相同，那么还可以使用 NATURAL JOIN 关键字自动执行内连接。
SELECT dname,ename FROM department NATRUAL JOIN employee;
6.3 外连接
外连接是内连接的扩展，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的记录.
6.3.1 左外连接
左外连接通过 LEFT [OUTER] JOIN 实现；左外连接返回满足连接条件的记录，同时返回不满足条件的连接操作符左边表的其他行；
SELECT a.dname, b.ename FROM department a LEFT JOIN employee b ON a.deptno = b.deptno; -- 或者 SELECT a.dname, b.ename FROM department a, employee b WHERE a.deptno = b.deptno(+);
DNAME ENAME
------ -------
test king
test king2
test1
test2
6.3.2 右外连接
右外连接通过 RIGTH [OUTER] JOIN 实现；右外连接返回满足连接条件的记录，同时返回不满足条件的连接操作符右边表的其他行；
SELECT a.dname, b.ename FROM department a RIGHT JOIN employee b ON a.deptno = b.deptno; -- 或者 SELECT a.dname, b.ename FROM department a, employee b WHERE a.deptno(+) = b.deptno;
DNAME ENAME
------ -------
test king
test king2
king3
king4
6.3.3 完全外连接
完全外连接通过 FULL [OUTER] JOIN 实现。完全外连接时左外连接和右外连接的结合.
SELECT a.dname, b.ename FROM department a FULL JOIN employee b ON a.deptno = b.deptno;
DNAME ENAME
------ -------
test king
test king2
test1
test2
king3
king4
6.3.4 使用(+)操作符
Oracle9i 前使用(+)操作符。Oracle9i 后建议使用 OUTER JOIN 执行外连接。使用(+)操作符语法如下：
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1(+) = table2.column2;
当使用(+)操作符时，应该将该操作符放在显示较少行（完全满足连接条件）的一端。
Notes：
1、(+)操作符只出现在 WHERE 子句中，并且不能同 OUTER JOIN 语法同用；
2、当使用(+)操作符执行外连接时，如果 WHERE 子句中包含多个条件，则必须所有条件中都包含(+)操作符；
3、(+)操作符只适用于列，不能用在表达式上；
4、(+)操作符不能与 OR 和 IN 操作符一起使用；
5、(+)操作符只能实现左外、右外连接，不能实现完全连接
7 子查询
7.1 单行子查询
只返回一行数据的子查询语句。当在 WHERE 中引用单行子查询时，可以使用单行比较符 =,>,<,>=,<=,<>
SELECT ename, salary, deptNo
FROM employee
WHERE deptNo = (SELECT deptNo
FROM employee
WHERE ename = 'SCOTT');
7.2 多行子查询
返回多行数据的子查询语句。当在 WHERE 中引用多行子查询时，必须要使用多行比较符 IN,ALL,ANY
--------------------------------------------------------------------
| 运算符 | 含义 |
------------------------------------------------------------------
| IN | 匹配于子查询结果的任一个值即可 |
| ALL | 必须要符合子查询结果的所有值 |
| ANY | 只要符合子查询结果的任一个值即可 |
-----------------------------------------------------------------
7.2.1 IN 操作符
SELECT ename,
job,
sal,
deptno
FROM emp
WHERE job IN (SELECT DISTINCT job
FROM emp
WHERE deptno = 10);
7.2.2 ALL 操作符
SELECT ename,
job,
sal,
deptno
FROM emp
WHERE sal > ALL (SELECT sal
FROM emp
WHERE deptno = 10);
7.2.3 ANY 操作符
SELECT ename,
job,
sal,
deptno
FROM emp
WHERE sal > ANY (SELECT sal
FROM emp
WHERE deptno = 10);
7.3 多列子查询
- 多列子查询返回多列数据的子查询语句;
- 当多列子查询返回单行数据时，WHERE 中可以使用单行比较符；
- 当多列子查询返回多行数据时，WHERE 中必须使用多行比较符；
- 当使用子查询比较多列数据时，接可以成对比较也可以非成对比较。成对比较要求多个列的数据必须同时匹配，非成对则不要求。
7.3.1 成对比较示例
SELECT ename, sal, comm, deptno FROM emp
WHERE (sal, nvl(comm,-1)) IN
(SELECT sal, nvl(comm,-1) FROM emp WHERE deptno = 10 )
7.3.2 非成对比较示例
SELECT ename,
sal,
comm,
deptno
FROM emp
WHERE sal IN (SELECT sal
FROM emp
WHERE deptno = 30)
AND NVL (comm, -1) IN (SELECT NVL (comm, -1)
FROM emp
WHERE deptno = 30);
7.4 其他子查询
7.4.1 相关子查询
相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过 EXISTS 谓词实现的。
SELECT ename,
sal,
job,
depton
FROM emp
WHERE EXISTS (SELECT 1
FROM dept
WHERE dept.depton = emp.depton);
当使用 EXISTS 谓词时，如果子查询存在返回结果，则条件为 TRUE; 如果子查询没有返回结果，则条件为 FALSE
7.4.2 在 FROM 子句中使用子查询
FROM 中的子查询会被当作视图对待，因此也被称作内嵌视图.
SELECT ename, job, sal
FROM emp, ( SELECT deptno, AVG (sal) avgsal
FROM emp
GROUP BY deptno) dept
WHERE emp.deptno = deptno AND sal > dept.avgsal;
Notes： FROM子句中使用子查询时，必须要给子查询指定别名
7.4.3 在 DML 语句中使用子查询
-- 在 INSERT 语句中使用子查询
INSERT INTO customer (name, age)
SELECT name, age
FROM employee
WHERE eNo = 200;
-- 在 UPDATE 语句中使用子查询
UPDATE emp
SET (sal,comm) =
(SELECT sal, comm
FROM emp
WHERE ename = 'jack')
WHERE job = (SELECT job
FROM emp
WHERE ename = 'jack');
-- 在 DELETE 语句中使用子查询
DELETE emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE dname = 'jack');
7.4.4 在 DDL 语句中使用子查询
-- 在 CREATE TABLE 语句中使用
-- 使用子查询可以在建立新表的同时复制表中的数据
CREATE TABLE new_emp
(
id,
name,
sal,
job
)
AS
SELECT empno,
ename,
esal,
ejob
FROM emp;
-- 在 CREATE VIEW 中使用
-- 创建视图时必须要指定视图所对应的子查询语句
CREATE OR REPLACE VIEW dept_10 AS
SELECT empno,ename,esal,ejob FROM emp ORDER BY empno;
-- 在 CREATE MATERIALIZED VIEW 中使用
-- 创建实体化视图时，必须要指定实体化视图所对应的SQL语句，并且该SQL语句将来可用于查询重写。
CREATE MATERIALIZED VIEW summary_emp AS
SELECT deptno, job, AVG(sal) avasal, SUM(sal) sumsal
FROM emp GROUP BY CUBE(deptno, job);
8 合并查询
语法:
SELECT 语句1 [UNION | UNION ALL | INTERSECT | MINUS] SELECT 语句2
1、这些集合操作符具有相同的优先级。同时使用时会按照从左至右的方式引用这些集合操作符;
2、使用集合操作符时，必须确保不同查询的列个数和数据类型都要匹配;
3、对于LOB、VARRAY和嵌套表列来说，集合操作符无效;
4、对于LONG列来说，UNION、INTERSECT、MINUS操作无效;
5、如果选择列表包含了表达式，则必须要为其指定列别名;
8.1 UNION
合并结果集，并会自动去掉结果集中的重复行，并且会以第一列的结果进行排序.
SELECT ename,sal,job FROM emp WHERE sal > 2500 UNION SELECT ename,sal,job FROM emp WHERE job = 'manager';
8.2 UNION ALL
合并结果集，但不会去掉结果集中的重复行，也不会进行任何排序，只是简单的做合并.
SELECT ename,sal,job FROM emp WHERE sal > 2500 UNION ALL SELECT ename,sal,job FROM emp WHERE job = 'manager';
8.3 INTERSECT
获取两个结果集的交集，并以第一列的结果进行排序.
SELECT enme,sal,job FROM emp WHERE sal > 2500
INTERSECT
SELECT ename,sal,job FROM emp WHERE job = 'manager';
8.4 MINUS
获取两个结果集的差集。只显示在第一个结果集中存在，在第二结果集中不存在的数据，并以第一列的结果进行排序.
SELECT ename,sal,job FROM emp WHERE sal > 2500 MINUS SELECT ename,sal,job FROM emp WHERE job = 'manager';
9 其他复杂查询
9.1 层次查询
当表具有层次结构时，使用层次查询可以更直观的显示数据结果，并显示其数据之间的层次关系.
语法：
SELECT 语句 START WITH condition CONNECT BY ..
START WITH: 用于指定层次查询的根行;
CONNECT BY: 用户指定父行和子行之间的关系。在condition表达式中，必须使用PRIOR引用父行。语法如下
... PRIOR expr = expr 或者 ... expr = PRIOR expr
-- 假如emp表具层次结构，其中empno列对应雇员号，而mgr列对应管理者编号。那么通过层次查询，可以显示雇员之间的上下级关系。
SQL> col ename format a15
SQL> col job format a15
SQL> SELECT LPAD(' ', 3 * (LEVEL-1))||ename ename,
SQL> LPAD(' ', 3 * (LEVEL-1))||job job FROM emp
SQL> WHERE job <> 'clean' START WITH mgr IS NULL
SQL> CONNECT BY mgr = PRIOR empno;
9.2 使用 CASE 表达式
为了在 SQL 语句中使用 IF..THEN..ELSE 语法，可以使用 CASE 表达式。当使用 CASE 表达式时，可以使用 WHERE子句指定条件语句.
SELECT ename,sal,
CASE
WHEN sal > 3000
THEN 3
WHEN sal > 2000
THEN 2
ELSE 1
END grade
FROM emp
WHERE deptno = 10;
9.3 使用 WITH 子句重用子查询
Oracle9i开始，通过 WITH 子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务，避免使用临时表
WITH summary AS
(SELECT dname,
SUM(sal) AS dept_total
FROM emp,
dept
WHERE emp.deptno = dept.deptno
GROUP BY dname
)
SELECT dname,
dept_total
FROM summary
WHERE dept_total >
( SELECT SUM(dept_total) * 1/3 FROM summary
);
9.4 倒叙查询
默认情况下执行查询操作只能看到最近提交的数据。从Oracle9i开始，通过使用倒叙查询(Flashback Query)特征，可以查看到过去某个时间点所提交的数据。
Notes：
-
- 使用倒叙查询，要求数据库必须采用UNDO管理方式，并且初始化；
- 参数undo_retention限制了UNDO数据的保留时间
9.4.1 查看当前数据
SELECT ename, sal FROM emp WHERE ename = 'jack';
9.4.2 查看历史数据
执行倒叙查询时，通过在 FROM 子句后指定 AS OF 子句可以查看过去的历史数据。AS OF 中既可以指定时间，也可以指定SCN.
SELECT ename,sal
FROM emp AS OF TIMESTAMP to_timestamp('2011-10-12 16:00:00','YYYY-MM-DD HH24:MI:SS')
WHERE ename = 'jack';
Notes： 使用倒叙查询只能看到5分钟之前变化数据。
9.4.3 使用DBMS_FLASHBACK包获取特定SCN的数据
SQL> exec dbms_flashback.enable_at_system_change_number(717402); SQL> SELECT sal FROM emp WHERE ename = 'jack'; SQL> exec dbms_flashback.disable; SQL> SELECT sal FROM emp WHERE ename = 'jack';
相关推荐
以下是对PL/SQL的学习笔记的详细解析： 1. **什么是PL/SQL语言** PL/SQL是Oracle数据库为数据库管理员和开发人员设计的一种编程语言。它扩展了SQL的功能，允许编写包含控制结构、变量、过程和函数的程序段，这些...
在PL/SQL中使用SQL语句时，分为DML语句（如INSERT、UPDATE、DELETE）和DDL语句（如CREATE、ALTER、DROP），以及事务控制语句（如COMMIT和ROLLBACK）。这些语句用于操作数据库的数据和结构。 PL/SQL中的SELECT语句...
-- 执行部分，包含SQL语句和PL/SQL语句 EXCEPTION -- 异常处理部分，处理运行时错误 END; ``` #### 标识符 在PL/SQL中，标识符用于命名变量、常量、过程、函数等。它们必须遵循一定的命名规则，比如首字符必须是...
PL/SQL数据库学习笔记 PL/SQL是一种高级的程序语言，主要用于Oracle数据库管理系统中。下面是PL/SQL数据库学习笔记的知识点总结。 一、基本原则 *瀑布模型：需求分析→设计（概要设计，详细设计：SQL语句，变量...
在这个“我的PL/SQL学习笔记（一）”中，我们将探讨PL/SQL的基础知识，包括其语法结构、变量声明、流程控制以及如何与Oracle数据库中的数据进行交互。 首先，PL/SQL的基本结构分为声明部分、执行部分和异常处理部分...
在PL/SQL编程中，游标是处理查询结果集的重要工具。它们允许程序逐行处理结果，而不是一次性加载所有数据，这对于大型数据集尤其有用，因为它可以节省内存并提高性能。下面将详细解释游标的基本概念、分类以及如何在...
-- SQL语句和PL/SQL语句 EXCEPTION WHEN error_type THEN -- 异常处理 END; / ``` **变量与数据类型** PL/SQL支持多种数据类型，包括数值型（NUMBER）、字符串型（VARCHAR2, CHAR）、日期型（DATE）、布尔型...
过程是PL/SQL中一种用来封装逻辑的单元，它包含了一系列的SQL语句和PL/SQL代码，可以执行特定的任务。过程与匿名程序块的区别在于它具有名称和参数，能够被重复调用。创建过程的语法中涉及到参数模式，包括IN、OUT、...
2. **执行部分**：包含具体的执行逻辑，如SQL语句和其他过程化语句。 3. **异常处理部分**：用于捕获并处理可能发生的错误或异常情况。 示例： ```plsql DECLARE -- 声明部分 v_StudentID NUMBER(5) := 1000; v_...
在PL/SQL编程中，存储过程和函数是关键的组件，它们允许我们将可重用的代码逻辑存储在数据库中，以便于管理和调用。本文主要探讨了存储过程、函数以及程序包的概念，特点，创建方法，执行方式，权限管理以及参数处理...
本文将深入探讨从"SQL,PL/SQL学习笔记"中提取的关键知识点，帮助编程人员更好地理解和运用这两种语言。 首先，我们关注SQL并行查询。通过`ALTER SESSION ENABLE PARALLEL DMl`，我们可以开启会话的并行DML操作，这...
SQL语句不区分大小写，但为了代码可读性，通常推荐遵循一定的大小写规范，如关键词大写，列名小写。`*`通配符代表所有列，如`SELECT * FROM emp;`。 获取系统当前时间可以使用`SELECT sysdate FROM dual;`，而`dual...
- **BEGIN**: 主程序体开始，可以包含任何合法的PL/SQL语句。 - **EXCEPTION**: 异常处理部分，用于捕获并处理程序运行过程中可能出现的错误。 - **END**; 结束主程序体。 #### 2. 注释 - **单行注释**: 使用两个连...
PL/SQL，全称Procedural Language/Structured Query Language，是Oracle数据库的一种扩展语言，用于处理数据库中的数据和实现复杂的业务逻辑。以下是对PL/SQL的基础知识进行的详细阐述： 1. **匿名块与命名块**： ...
存储过程是一种在数据库上创建并保存的过程，它由一系列SQL语句组成，可以在应用程序中调用执行。存储过程的优点包括提高性能、简化应用开发、减少网络流量等。 #### 2. SELECT 语句 在 PL/SQL 中，SELECT 语句用于...
执行部分包含SQL语句和PL/SQL控制结构；异常处理部分用于捕获和处理运行时错误。 2. **变量和数据类型**： PL/SQL支持多种内置数据类型，如NUMBER（数值型）、VARCHAR2（可变长度字符串）、DATE（日期时间）、...
- PL/SQL是由SQL语句、控制结构、子程序和异常处理等部分组成的编程环境。 - 它允许用户创建存储过程、函数、触发器和包，这些可以提高数据库性能并增强数据安全性。 2. **变量与数据类型**： - PL/SQL支持多种...
2. **PL/SQL块结构**：讲解如何构建声明部分、执行部分和异常处理部分的PL/SQL块，包括匿名块和命名块（如过程和函数）。 3. **PL/SQL流程控制**：深入探讨如何使用各种流程控制语句，如循环（FOR、WHILE）、分支...