1.PL/SQL综述
本章学习目标，了解如下内容：
PL/SQL的功能和作用
PL/SQL 的优点和特征；
Oracle 10g、Oracle9i 的PL/SQL新特征
1.1.SQL简介
1.1.1.SQL语言特点
SQL语言采用集合操作方式
1.1.
2.SQL语言分类
●数据查询语言（SELECT语句）：检索数据库数据。
●数据操纵语言（DML）：用于改变数据库数据。包括insert，update和delete三条语句。
●事务控制语言（TCL）：用于维护数据库的一致性，包括commit，rollback和savepoint 三
条语句
●数据定义语言（DDL）：用户建立、修改和删除数据库对象。
●数据控制语言（DDL）：用于执行权限授予和收回操作。包括grant 和revoke两条命令。
1.1.3.SQL 语句编写规则
●SQL关键字不区分大小写
●对象名和列名不区分大小写
●字符值和日期值区分大小写
●书写格式随意
1.2.PL/SQL简介
1.3.Oracle 10G PL/SQL 新特征
2.PL/SQL开发工具
本章学习目标：
学会使用SQL*PLUS
学会使用PL/SQL developer；
学会使用Procedure Builder。
2.1.SQL*PLUS
在命令行运行SQL*Plus
Sqlplus [username]/[password] [@server]
3.PL/SQL 基础
学习目标：
●了解PL/SQL块的基本结构以及PL/SQL块的分类；
●学会在PL/SQL块中定义和使用变量
●学会在PL/SQL块中编写可执行语句；
●了解编写PL/SQL代码的指导方针；
●了解Oracle 10g的新特征——新数据类型BINARY_FLOAT 和
BINARY_DOUBLE，以及指定字符串文本的新方法。
3.1.PL/SQL 块简介
3.1.1.PL/SQL块结构
3.1.2.PL/SQL 块分类
匿名块
命名块
子程序
触发器
3.2. 定义并使用变量
3.2.1.标量变量
3.2.2.复合变量
3.2.3.参照变量
3.2.
4.LOB 变量
3.2.5.非PL/SQL 变量
3.3.编写 PL/SQL 代码
3.3.1.PL/SQL 词汇单元
分隔符
标识符
文本
（数字文本，字符文本，字符串文本，布尔文本，日期时间文本）
注释
3.3.2.PL/SQL 代码编码规则
标识符命名规则
大小写规则
代码缩进
嵌套块和变量范围
PL/SQL中可以使用的SQL函数
4.使用SQL语句
学习目标：
学会使用SELECT语句去完成基本查询功能
学会使用INSERT，UPDA TE和DELETE语句去操作数据库数据
学会使用COMMIT，ROLLBACK和SA VEPOINT语句去控制事务
学会使用SELECT语句去实现各种复杂查询功能（数据分组、连接查询、子查询、层次查询、合并查询等）
4.1.使用基本查询
处理NULL：函数nvl(expr1,expr2)，nvl2(expr1,expr2,expr3)
4.2.使用DML语句
使用多表插入数据
语法：INSERT ALL insert_into_clause [value_clause] subquery;
INSERT conditional_insert_clause subquery;
示例1：使用ALL 操作符执行多表插入
INSERT ALL
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
WHEN deptno=30 THEN INTO dept30
WHEN job=’CLERK’ THEN INTO clerk
ELSE INTO other
Select * from emp;
示例2：使用FIRST 操作符执行多表插入INSERT FIRST
WHEN deptno=10 THEN INTO dept10
WHEN deptno=20 THEN INTO dept20
WHEN deptno=30 THEN INTO dept30
WHEN job=’CLERK’ THEN INTO clerk
ELSE INTO other
SELECT * FROM emp;
4.3.使用事务控制语句
4.3.1.事务和锁
4.3.2.提交事务
4.3.3.回退事务
设置保存点：
savepoint a;
或者
exec dbms_transaction.savepoint(‘a’)
取消部分事务
Rollback to a;
或者
Exec dbms_transaction.rollback_savepoint(‘a’)
取消全部事务：
Rollback; 或者exec dbms_transaction.rollback() 4.3.4.只读事务
4.3.
5.顺序事务
4.4.数据分组
4.4.1.分组函数
Max
Min
Avg
Sum
Count
Variance
Stddev
使用分组函数注意事项：
●当使用分组函数时，除了函数Count(*) 之外，其他分组函数都会忽略NULL行。
●当执行select 语句时，如果选择列表同时包含列、表达式和分组函数，那么这些列和表
达式必须出现在group by 子句中。
●当使用分组函数时，在分组函数中可以指定all 和distinct 选项。其中all默认选项，该
选项表示统计所有行数据（包括重复行）；如果指定distinct，则只会统计不同行值。
4.4.2.GROUP BY 和HA VING
4.4.3.ROLLUP 和CUBE
ROLLUP和CUBE用来产出横向、纵向的统计结果。
示例一：使用ROLLUP操作符：生成横向小计
SELECT deptno,job,avg(sal) FROM emp GROUP BY ROLLUP(deptno,job);
示例二：使用CUBE操作符：生成数据统计、横向小计、纵向小计结果。
SELECT deptno,job,avg(sal) FROM emp GROUP BY CUBE(deptno,job);
示例三：使用GROUPING函数
GROUPING函数用于统计结果是否用到了特定列。
Select deptno,job,avg(sal),grouping(deptno),grouping(job)
FROM emp GROUP BY cube(detpno,job);
4.4.4.GROUPING SETS
示例一：显示部门平均工资：
SELECT deptno,avg(sal) FROM emp GROUP BY deptno;
示例二：显示岗位平均工资
SELECT job,avg(sal) FROM emp GROUP BY job；
示例三：同时显示部门平均工资和岗位平均工资：
SELECT deptno,job,avg(sal) FROM emp GROUP BY GROUPING SETS(deptno,job);
4.5.连接查询
4.6.子查询
4.6.1.单行子查询
4.6.2.多行子查询
4.6.3.多列子查询
SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=
SELECT deptno,job FROM emp WHERE ename=’SMITH’);
4.7.合并查询
操作符包括：union，union all，intersect 和minus
INTERSECT 操作符用于获取两个结果集的交集。
MINUS操作符用于获取两个结果集的差集。
4.8.其他复杂查询
4.8.1.层次查询
4.8.2.使用CASE 表达式
4.8.3.倒叙查询
查看当前数据：SELECT ename,sal FROM emp WHERE ename=’CLARK’;
查看历史数据：SELECT ename,sal FROM emp AS OF TIMESTAMP ot_timestamp( ‘2003-05-18 19:59:00’,’YYYY-MM-DD HH24:MI:SS’)
WHERE ename=’CLARK’;
使用DBMS_FLASHBACK 包获取特定SCN的数据
Exec dbms_flashback.enable_at_system_change_num(717402);
Select sal from emp where ename=’SCOTT’;
Exec dbms_flashback..distable
Select sal from emp where ename=’SCOTT’;
4.8.4.使用WITH子句重用子查询
通过with 子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务，从而避免了使用临时表。
示例一：显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（两次使用相同子查询）
SELECT dname,sum(sal) as dept_total FROM scott.emp emp,scott.dept dept WHERE emp.deptno=dept.deptno GROUP BY dname
HAVING sum(sal)>
(SELECT sum(sal)*1/3FROM scott.emp emp,scott.dept dept WHERE emp.deptno=dept.deptno);
示例一：显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和（使用with子句重用子查询）
WITH summary AS (
SELECT dname,sum(sal) as dept_total FROM scott.emp emp,scott.dept dept WHERE emp.deptno=dept.deptno GROUP BY dname
)
SELECT dname,dept_total FROM summary WHERE dept_total>
(SELECT sum(dept_total)*1/3FROM summary )
5.SQL函数
学习目标：
●在SQL语句和PL/SQL 块中使用数字函数；
●在SQL语句和PL/SQL 语句中使用字符函数；
●在SQL语句和PL/SQL 语句中使用日期函数；
●在SQL语句和PL/SQL 语句中使用转换函数；
●在SQL语句和PL/SQL 语句中使用Oracle 10g 新增加的集合函数；
●在SQL语句中使用分组函数；
●了解并掌握Oracle 10g、Oracle 9i 新增加的SQL函数。
5.1.数字函数
5.2.字符函数
5.3.日期时间函数
5.4.转换函数
5.5.集合函数
5.6.其他单行函数
5.7.分组函数
5.8.对象函数
6.访问Oracle
学习目标：
●在PL/SQL块中检索单行数据；
●在PL/SQL 块中嵌入DML语句；
●使用SQL游标属性；
●在PL/SQL块中嵌入事务控制语句。
6.1.检索单行数据
使用标量变量接收数据
使用记录变量接收数据
嵌入SELECT 语句注意事项
6.2.操纵数据
6.2.1.插入数据
6.2.2.更新数据
6.2.3.删除数据
6.2.4.SQL 游标
游标的四种属性：
SQL%ISOPEN 确定SQL游标是否已近打开。
SQL%FOUND 确定SQL语句执行是否成功。
SQL%NOTFOUND 确定SQL语句执行是否成功。
SQL%ROWCOUNT 用于返回SQL语句所作用的总计行数。
6.3.事务控制语句
Commit, savepoint, rollback
7.编写控制结构
学习目标：
●使用各种IF语句；
●使用Oracle 9i的新特征——case语句
●使用各种类型的循环语句
●使用顺序控制语句——GOTO和NULL
7.1.条件分支语句
PL/SQL 提供了三种条件分支语句：
IF-THEN,IF-THEN-ELSE, IF-THEN-ELSEIF
简单条件判断：
DECLARE
V_sal number(6,2);
Begin
Select sal into v_sal from scott.emp
Where lower(ename)=lower(‘&&name’);
If v_val<2000 then
Update scott.emp set sal=v_sal+200 where lower(ename)=lower(‘&name’); End if;
End
二重条件分支：
IF。。。THEN。。。ELSE
多重条件分支
IF。。。THEN… ELSEIF
7.2.CASE语句
使用CASE语句来执行多重条件分支操作.
1. 在CASE 语句中使用单一选择符进行等值比较。
示例如下：
DELCLARE
V_deptno emp.deptno%TYPE;
BEGIN
V_deptno:=&no;
Case v_deptno
Whern 10 then
Update emp set comm.=100 where depno=v_deptnp;
When 20 then
Update emp set comm.=80 where deptno=v_deptno;
When 30 then
Update emp set comm.=50 where deptno=v_deptno;
Else
Dbms_output.put_line(‘不存在该部门’);
End case;
End;
2. 在CASE 语句中使用多种条件比较
Declare
V_sal emp.sal%TYPE;
V_ename emp.ename%TYPE;
Begin
Select ename,sal into v_ename,v_sal from emp where empno=&no; Case
When v_sal<1000 then
Update emp set comm.=100 where ename=v_ename;
When v_sal<2000 then
Update emp set comm.=80 where ename=v_ename;
When v_sal<6000 then
Update emp set comm.=50 where ename=v_ename;
End case;
End;
7.3.循环语句
1.基本循环
LOOP
Statement1;
……
Exit [when condition];
End LOOP;
2.while 循环
declare
i int :=1;
Begin
While i<=10 loop
Insert tino temp values(i);
i:=i+1;
end loop
end;
3.For 循环
begin
delete from temp;
for i in reverse1..10loop
insert into temp values(i);
end loop;
end;
4.嵌套循环和标号
在编写PL/SQL 块时，可以使用<
7.4.顺序控制语句
GOTO：语句用于跳转到特定标号处去执行语句。
NULL ：NULL语句不会执行任何操作，并且会直接控制传递到下一条语句。
使用NULL语句的主要好处是可以提高PL/SQL程序的可读性。
8.使用复合数据类型
学习目标：
●使用PL/SQL 记录和%ROWTYPE属性；
●使用索引表、嵌套表和变长数组（V ARRAY）；
●使用PL/SQL 记录表；
●使用FORALL 语句和BULK COLLECT子句。
●在嵌套表上使用集合操作符、在FORALL 语句中使用INDICES OF子句和
V ALUES OF子句。
8.1.PL/SQL 记录
定义PL/SQL 记录
使用PL/SQL记录
分别在SELECT INTO/UPDA TE/INSERT/DELETE 语句中使用记录变量和记录成员。
8.2.PL/SQL 集合
集合类型包括：索引表（PL/SQL表），嵌套表（Nested Table）和变长数组（V ARRAY）等三种类型。
8.2.1.索引表
语法：
TYPE type_name IS TABLE OF element_type
[NOT NULL] INDEX BY key_type;
Identifier type_name;
8.2.2.嵌套表
嵌套表也是一种用于处理PL/SQL数组的数据类型。
嵌套表的元素下标从1开始，并且元素个数没有限制。
嵌套表元素的数组元素可以是稀疏的。
语法：
TYPE type_name IS TABLE OF element_type;
Identifier type_name;
使用嵌套表的方法:
1.PL/SQL 块中使用嵌套表
2.在表列中使用嵌套表
8.2.3.变长数组
V ARRAY 也是一种用于处理PL/SQL数组的数据类型，它也可以作为表列的数据类型使用。他的元素的最大个数是有限制的。
语法：
TYPE type_name IS V ARRAY(size_limit) OF element_type [NOT NULL];
Identifier type_name;
其中：type_name 用于指定V ARRAY 类型名；
Size_limit 用于指定V ARRAY 元素的最大个数；
Element_type用于指定元素的数据类型；
Identifier 用于定义V ARRAY 变量。
示例如下：
DECLARE
TYPE ename_table_type IS V ARRAY(20) OF emp.ename%type;
Ename_table ename_table_type:=ename_table_type(‘A’,’A’);
使用V ARRAY的方法：