Oracle学习笔记之PL/SQL编程

来源：互联网发布：java循环语句编辑：程序博客网时间：2024/05/18 03:58

流氓前言：无论是Oracle DBA还是数据库开发人员，都需要和PLSQL打交道，那么我就针对PLSQL做一个全面的总结，来帮助想学习PLSQL的童鞋们快速认识它并爱上它，在我看来要想让一个人发自内心的爱上一门技术，无非有两种情况：
相信大多数人都不是天生对技术感兴趣的，显然我也是~~ 哈哈。不过这都不是问题，只要我们用另一种方式去拿下她，我们照样可以爱她它甚至从此对她产生强烈的兴趣，那就是搞懂它！

###第一讲：PL/SQL 概览
1.1 发展历史

PL/SQL 在20世纪80年代由Oracle公司开发，作用当然就是为了更方便高效的操作数据库和处理数据喽！最初，功能还很有限；
Oracle 8 Database 时向数据库引入了对象类型，使得Oracle 由一种纯关系模型演变为了对象关系（或扩展关系）模型；
Oracle 9i R2 时候已经演变为了一种过程化的面向对象的编程语言；
Oracle 11g 时候由有解释型语言演变为了编译语言；
1.2 体系结构
PL/SQL运行时，引擎作为SQL*PLUS环境中的资源而存在；用户连接进数据库后，会建立一个会话，可以在该会话中直接运行SQL或PL/SQL语句；
PLSQL程序单元可以运行SQL语句或外部单元，SQL也可以调用PL/SQL函数和过程，SQL语句是最终实现直接与数据交互的；
下面让大家看一下数据库处理体系结构如下图：

1.3 基本的块结构
PL/SQL是块状编程语言，分为命名块和匿名块，通俗说也就是有名字的块和没名字的块~
匿名块使用场景：
在为种子（seed）数据构建脚本时候；
执行一次性的处理活动时候；
在一个PL/SQL块的执行会话中嵌入另一个处理活动时候；
基本的匿名块结构必须包含执行会话，然后声明和异常处理是可选的，如下模型：

[DECLARE]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;
/

声明块由DECLARE保留字开始，以BEGIN保留字结束；
游标的作用类似函数，有名称、签名和返回类型—select语句的输出列；
执行块用于处理数据，可包含变量赋值、比较、条件运算和迭代，同时他也是访问游标和其他命名程序的地方；
函数、过程和一些对象类型都是命名程序单元；
一个执行块中必须至少有一条语句，如下就是一个最简单的只包含一条NULL语句的匿名块：
BEGIN
NULL；
END;
/
如上这个除了使编译阶段无错的完成，不做任何工作。任何语言的编译都包括语法分析，块中缺少语句会报错，我会在后续讲到；

异常处理块由EXCEPTION保留字开始，以END保留字结束；用于捕获和管理异常。

命名块用于定义数据类型、结构体和变量；命名块的结构稍有不同，因为他们存储在数据库中。
结构体是混合变量，如集合、记录机构体或系统引用游标；结构体也可以是局部命名的函数、过程或游标；
命名块也有声明部分，也叫做头（header）；由头部分定义名称、形参列表、和PL/SQL命名块的任何返回类型，名称和形参列表就是子例程的签名；
头和执行块之间的区域就是作为命名块的声明块；下面举一个命名块函数的原型实例：

函数相当于值传递（只使用IN模式定义形参）或引用传递（使用IN和out或只有out模式定义形参）的子例程；
函数可以使用SELECT 查询数据，但不能执行DML语句，例如UPDATE、DELETE、INSERT；
定义形参的函数或是使用PL/SQL数据类型的返回类型不能从SQL命令行调用，不过可从SQL命令行调用使用SQL数据类型的函数；
如果函数依赖于会话级变量的状态，那么应避免使用DETERMINISTIC子句，该子句最适合于基于函数的索引和物化视图；
对于计划从肯那个使用并行查询能力的SQL语句调用的函数，应启用PARALLEL_ENABLE子句，应进一步了解该子句在数据仓库技术中的应用；
当函数返回集合（如嵌套表或VARRAY）时，PIPELINED子句提供了改进的性嫩，在返回系统引用游标时，也能改进性能。
RESULT_CACHE子句表明函数只在SGA中缓存一次并跨会话可用，11g新特性，跨会话函数只使用IN模式形参；

下面展示一个命名块过程原型：

AUTHID 默认值是DEFINER,这被称为定义者权限（definer right），意味着有权执行该过程的任何人和定义该过程的用户拥有相同的权限，
CURRENT_USER被称为调用者权限，意味着只允许拥有执行权限的人调用过程并只运行自己用户/模式下的数据；

过程在很多方面类似函数，但不返回数据类型，意味着不能将他们用右操作数；
和函数不同的是，必须通过PL/SQL块调用过程；
过程可以查询和操纵数据，也是将值传入或传出外部语言的基本子例程；



10g R1 中支持用另一个引用符号取代单引号，如果字符串中有大量撇号，需要用另一个单引号，这时就有用如下：
SELECT ’ it’ ‘s a girl , big eye,no money, so it can’ ‘t go school ! ’ as phrase FROM dual;
可以用如下语句取代：
SELECT q’(it’s a girl , big eye,no money, so it can’t go school !) ’ as phrase FROM dual;

—————————————————————————————————————————————————————————————————————————————
上面对PL/SQL匿名块、函数、过程有了一个简单的介绍了，下面我就分别做一些小实验并贴出来供大家共同学习和理解其应用场景和作用

匿名块之小实验：
（一）、在块中操作变量 ：块中可以声明变量也可以不声明变量，现在就来说一下生明变量时候的一些操作
语法为：identifier [CONSTANT] datatype [NOT NULL] [:= | default expr]
1.声明变量必须指定数据类型，或者用 %type 属性指定变量和一个列数据类型相同或者和另一个变量数据类型相同；
2.声明变量的同时可以赋初始值、默认值（default）、或者不赋值，且其变量值可以在begin语句中被修改；
3.当变量指定了 NOT NULL 属性和 constant（标量）属性时候，必须要赋初始值，但标量值不可在begin语句中修改；
如下：
SQL>declare
v1 int;      - -定义不赋值的变量
  v2 varchar2(10) :=’Aa’;   - - 定义赋初始值的变量
v3 varchar2(10) default ‘Zz’; - - 定义具有默认值的变量类似于：= 赋值
v4 constant number :=5; - - 定义标量变量，且必须赋一个固定值
begin
v1:=1; v2:=’Bb’; v3:=’Cc’; - - 执行体中重新赋值
null;
end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
注释：块中：= 是赋值符号，= 是等值符号， - - 是单行注释，/* */ 是多行注释（类似于Shell中的 # 和 << eof ….eof); %type 是定义相同数据类型；
隐式游标

（二）、块中查询表的数据：在查询单行数据时候一定要用 select into 结构将结果存储在变量中，多行时候需要运用游标（后边会讲解）；
如下：
SQL> declare
2 v1 t5.name%type;
3 begin
4 select name into v1 from t5 where id=1;
5 dbms_output.put_line(‘the name is : ‘||v1); - - 用dbms_output.put_line 包来输出变量值
6 end;
7 /

PL/SQL procedure successfully completed. - - 因为没有开启 serveroutput on ，所以没有输出变量值

SQL> set serveroutput on - - 开启 serveroutput on ，显示变量值
SQL> /
the name is : abc123
PL/SQL procedure successfully completed.

（三）、在块中执行DML语句：直接执行DML语句即可
如下：
SQL> begin
2 delete from t5; - - update 、insert 同样适用；
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select * from t5;

no rows selected

SQL> rollback;

Rollback complete.

SQL> select * from t5;

ID NAME
———- ——————–
1 abc123
2 abc456
3 abc789

（四）、块中执行DDL语句：需要动态SQL才能执行DDL语句（同样适合于函数和过程）

SQL> begin
2 drop table t5;
3 end;
4 /
drop table t5;
*
ERROR at line 2: - - 直接在块中执行DDL语句报错；
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol “DROP” when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge

SQL> begin
2 execute immediate ‘drop table t5’; - - 通过动态SQL 在块中来顺利执行DDL语句
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> desc t5;
ERROR:
ORA-04043: object t5 does not exist - - 证明表t5 已经被drop掉

当然以上动态SQL语法还可以用以下方式：
如下：
SQL> desc t4;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER(38)
NAME VARCHAR2(20)

SQL> declare
2 v1 varchar2(30);
3 begin
4 v1 :=’drop table t4’; - - 即把需要执行的DDL语句赋给一个字符变量，然后执行这个变量
5 execute immediate v1;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> desc t4;
ERROR:
ORA-04043: object t4 does not exist
———————————————————————————————————————————————————————————————————-
在刚才的例子中我们有提到游标的概念，那么接下来我们就来了解一下到底什么是游标？
个人理解如下：
游标：就是在PL/SQL中用来处理返回多条记录时的一种机制，用来存储返回的多行数据一个”临时场所“。
分为：
隐式游标：不需要显示定义游标，所有DML语句为隐式游标（又叫多行隐式游标），或者select 语句返回一条记录时候也可以用隐式游标（又叫单行隐式游标）
显示游标：需要显示定义游标，当查询结果集多于一行时候，就需要定义一个显示游标来存储记录；
游标属性如下：
前缀为SQL 则表示为隐式游标属性，前缀为游标名，则为显示游标属性
%rowcount ：DML语句或者select into 语句操作的行数
%found ：游标是否找到记录行
%notfound ：游标是否未找到记录行
%isopen：游标是否打开
另外当要处理游标的每一行时候，需要配合使用循环来处理每一行，如下：
（一）、基本的loop 循环，需要明确指出退出条件 exit expr
SQL>
1 declare
2 cursor c1 is select id,name from t1; - - 定义一个显示游标来存储查询返回结果集合；
3 v1 c1%rowtype; - - 定义一个变量来逐行接收游标中结果集合；
4 n1 number;
5 begin
6 open c1;
7 if not c1%isopen then
8 open c1;
9 end if;
10 loop
11 fetch c1 into v1;
12 dbms_output.put_line(v1.id|| ’ ’ ||v1.name);
13 n1:=c1%rowcount;
14 dbms_output.put_line(‘have handle ’ || c1%rowcount || ’ lines’);
15 exit when c1%notfound;
16 end loop;
17 close c1;
18 dbms_output.put_line(‘finally handle ’ || n1 || ’ lines’);
19* end;
SQL> /
1 A
have handle 1 lines
2 B
have handle 2 lines
3 C
have handle 3 lines
4 D
have handle 4 lines
5 E
have handle 5 lines
5 E
have handle 5 lines

finally handle 5 lines

PL/SQL procedure successfully completed.
（二）、while 循环
如下：
SQL>
1 declare
2 cursor c1 is select id,name from t1; - - 定义一个显示游标 c1 来存储数据集合；
3 v1 c1%rowtype; - - 定义一个变量来逐行接收处理游标中的数据集合；
4 n1 number;
5 n2 number; - - 相比基本loop循环多定义了一个变量用来存储表t1的总行数，来作为while 退出的条件
6 begin
7 open c1;
8 if not c1%isopen then
9 open c1;
10 end if;
11 select count(*) into n1 from t1;
12 while c1%rowcount < n1 - - 当不满足while条件时立即退出循环
13 loop
14 fetch c1 into v1;
15 dbms_output.put_line(v1.id|| ’ ’ ||v1.name);
16 n2:=c1%rowcount;
17 dbms_output.put_line(‘have handle ’ || c1%rowcount || ’ lines’);
18 - - exit when c1%notfound;
19 end loop;
20 close c1;
21 dbms_output.put_line(‘finally handle ’ || n2 || ’ lines’);
22* end;
SQL> /
1 A
have handle 1 lines
2 B
have handle 2 lines
3 C
have handle 3 lines
4 D
have handle 4 lines
5 E
have handle 5 lines
finally handle 5 lines

PL/SQL procedure successfully completed.

（三）、for 循环
如下：
1 declare
2 cursor c1 is select * from t1 ;
3 n1 number;
4 begin
5 for v1 in c1 - - 用for循环不用显示定义 v1 变量来接收游标数据集
6 loop
7 dbms_output.put_line(v1.id|| ’ ‘||v1.name);
8 n1:= c1%rowcount;
9 dbms_output.put_line(‘have handle ‘|| n1 ||’ lines’);
10 end loop;
11 dbms_output.put_line(‘finally handle ‘|| n1 || ’ lines’);
12* end;
13 /
1 A
have handle 1 lines
2 B
have handle 2 lines
3 C
have handle 3 lines
4 D
have handle 4 lines
5 E
have handle 5 lines
finally handle 5 lines

PL/SQL procedure successfully completed.

对比三种循环结构，很显然能看出第三种的写法更加的简洁，因为 for 循环和游标的配合能自动打开游标和自动关闭游标，且能循环处理游标内的每一行；

阅读全文

0 0