精通 oracle 10g plsql 编程-学习笔记

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 块时，可以使用<<label_name>> 定义标号。

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的方法：
1.在PL/SQL 快中使用V ARRAY
2.在表列中使用V ARRAY
8.2.4.PL/SQL 记录表
PL/SQL 变量用于处理单行单列数据，
PL/SQL 记录用于处理单行多列数据，
PL/SQL 集合用于处理多行单列数据。

PL/SQL 记录表用于处理多行多列数据。

8.2.5.多级集合
多级集合是指嵌套了集合类型的集合类型。

示例一：在PL/SQL 块中使用多级V ARRAY
declare
type al_varray_type is varray(10) OF int;
type nal_varray_type is varray(10) of al_varray_type;
nvl nal_varray_type:=nal_varray_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_varray_type(2,4));
begin
dbms_output.put_line(‘显示二位数组所有元素’);
for i in1..nvl.COUNT loop
for j in1..nvl(i).COUNT loop
dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j)); end loop;
end loop;
end;
示例二：在PL/SQL块中使用多级嵌套表
declare
type al_table_type is table of int;
type nal_table_type is table of al_table_type;
nvl nal_table_type:=nal_table_type(
al_table_type(2,4),
al_table_type(5,73));
begin
for i in1..nvl.count loop
for j in1..nvl(i).count loop
dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j)); end loop;
end loop;
end;
示例三：在PL/SQL块中使用多级索引表
declare
type al_table_type is table of int index by binary_integer;
type nal_table_type is table of al_table_type index by binary_integer;
nvl nal_table_type;
begin
nvl(1)(1):=10;
nvl(1)(2):=5;
nvl(2)(1):=100;
nvl(2)(2):=50;
for i in1..nvl.count loop
for j in1..nvl(i).count loop
dbms_output.put_line('nvl('||i||')('||j||')='||nvl(i)(j));
end loop;
end loop;
end;
8.2.6.集合方法
集合方法是Oracle 所提供的用于操作集合变量的内置函数或过程，其中EXISTS，COUNT，LIMINT，FITST，NEXT，PRIOR和NEXT是函数，而EXTEND，TRIM和DELETE则是过程。

集合方法的调用语法如下：
Collection_name.method_name[(parameters)]
注意：集合方法只能在PL/SQL语句中使用，而不能在SQL语句中使用。

另外集合方法EXTEND和TRIM只适用于嵌套表和V ARRAY，而不适用于索引表。

1.EXISTS ：该方法用于确定集合元素是否存在
declare
type ename_table_type is table of scott.emp.ename%type;
ename_table ename_table_type;
begin
if ename_table.exists(1) then
ename_table(1):='SCOTT';
else
dbms_output.put_line('You must init !');
end if;
end;
2.COUNT：该集合方法用于返回当前集合变量中的元素总个数。

declare
type ename_table_type is table of scott.emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):='SCOTT';
ename_table(1):='SMITH';
ename_table(5):='MARY';
ename_table(10):='BLAKE';
dbms_output.put_line(ename_table.count);
end;
3.LIMIT ：该方法用于返回集合元素的最大个数。

对于嵌套表和索引表，该方法返回null；对于varray 来说，该方法返回varray 所允许的最大元素个数。

declare
type ename_table_type is varray(20) of scott.emp.ename%type;
ename_table ename_table_type:=ename_table_type('mary');
begin
dbms_output.put_line('集合元素的最大个数'||ename_table.limit);
end;
4.First 和LAST
FITST 方法用于返回集合变量第一个元素的下标，而LAST方法则用于返回集合变量最后一个元素的下标。

declare
type ename_table_type IS table of scott.emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):='SCOTT';
ename_table(1):='SIMTH';
ename_table(5):='MARY';
ename_table(10):='BLAKE';
dbms_output.Put_line('The first element:'||ename_table.first);
dbms_output.put_line('The last element:'||ename_st);
end;
5.PRIOR 和NEXT
PRIOR方法用于返回当前集合元素的前一个元素的下标，而NEXT方法则用于返回当前集合元素的后一个元素的下标。

declare
type ename_table_type is table of scott.emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):='SCOTT';
ename_table(1):='SIMTH';
ename_table(5):='MARY';
ename_table(10):='BLAKE';
dbms_output.put_line('The prior element of the element 5 is :'||ename_table.prior(5));
dbms_output.put_line('The next element of the element 5 is :'||ename_table.next(5));
end;
6.EXTEND
该方法用于扩展集合变量的尺寸，并为它们增加元素。

注意，该方法只适用于嵌套表和V ARRAY。

该方法有EXTENDed，EXTEND(n),EXTEND(n,i)等三种调用格式，其中EXTENDed用于为集合变量添加一个null 元素，EXTENDed(n)用于为集合变量添加n 个null 元素，而EXTEND(n,i)则用于为集合变量添加n个元素（元素值与第i个元素相同）。

示例如下：
declare
type ename_table_type is varray(20) of varchar2(10);
ename_table ename_table_type;
begin
ename_table:=ename_table_type('MARY');
ename_table.extend(5,1);
dbms_output.put_line('The count of element is :'||ename_table.count);
dbms_output.put_line('The limit of element is :'||ename_table.limit);
end;
7.TRIM
该方法用于从集合尾部删除元素，它有TRIM和TRIM(n)两种调用格式。

其中TRIM用于从集合尾部删除一个元素；而TRIM(n)则用于从集合尾部删除n个元素。

注意，该方法只适用于嵌套表和V ARRAY。

declare
type ename_table_Type is table of varchar2(10);
ename_table ename_table_type;
begin
ename_table:=ename_table_type('A','A','A','A','A');
ename_table.trim(2);
dbms_output.put_line('The count of element is :'||ename_table.count);
end;
8.DELETE
该方法用于删除集合元素，但该方法只适用于嵌套表和索引表，而不适用于V ARRAY。

该方法有DELETE，DELETE(n)，DELETE(m,n)，等三种调用格式。

其中DELETE用于删除集合变量的所有元素；DELETE(n)用于删除集合变量的第n个元素；而DELETE(m,n)则用于删除集合变量中从m到n之间的所有元素。

declare
type ename_table_Type is table of scott.emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):='SCOTT';
ename_table(1):='SIMTH';
ename_table(5):='MARY';
ename_table(10):='BLAKE';
ename_table.delete(5);
dbms_output.put_line('The count of element is :'||ename_table.count);
end;
8.2.7.集合赋值
8.2.8.比较集合
8.3.批量绑定
9.使用游标
10.处理例外
11.开发子程序
12.开发包
13.开发触发器
14.开发动态SQL
15.使用对象类型
16.使用LOB对象
17.使用Oracle系统包
附录A 习题总结
附录B 使用SQL*PLUS。