Oracle：PL/SQL基础语法详解（选择结构，循环结构以及游标）

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。

1.环境准备

下面所有关于PL/SQL的程序都是建立在employees表和departments表上，具体表结构如下所示：

departments表结构如下所示：

字段名	数据类型	长度	约束	说明
department_id	number	4	primary key	部门编号
department_name	varchar2	30	not null	部门名称
manager_id	number	6		部门管理者编号

创建departments表的sql语句如下所示

create table departments(
       department_id number(4) primary key,
       department_name varchar2(30) not null,
       manager_id number(6) 
);

employees表结构如下所示：

字段名	数据类型	长度	约束	说明
employee_id	number	6	primary key	员工编号
employee_name	varchar2	20		员工名
email	varchar2	25	not null	邮箱地址
phone_number	varchar2	20	not null	电话号码
hire_date	Date		not null	入职日期
salary	number	8,2	大于0	工资
commission_pct	number	2,2		奖金（工资百分比）
manager_id	number	6		所属领导编号
department_id	number	4	foreign key	所属部门编号

对应employees的建表sql语句如下所示：

create table employees(
       employee_id number(6) primary key,
       employee_name varchar2(20),
       email varchar2(25) not null,
       phone_number varchar2(20),
       hire_date Date not null,
       salary number(8,2) check(salary>0),
       commission_pct number(2,2),
       manager_id number(6),
       department_id number(4) references departments(department_id)
)

2.PL/SQL程序结构

PL/SQL程序的基本单元是语句块,所有的PL/SQL程序都是由语句块构成的，语句块之间可以相互嵌套，每个语句块完成特定的功能。
一个完整的PL/SQL语句块由3个部分组成。
（1）声明部分：以关键字declare开始，以begi结束。主要用于声明变量，常量，数据类型，游标，异常处理名称和本地子程序定义等！我们可以简单理解为Java类中的属性变量。
（2）执行部分：是PL/SQL语句块的功能实现部分，以关键字begin开始，以Exception或end结束（如果语句中没有异常处理部分，则以关键字end结束）。该部分通过变量赋值，流程控制，数据查询，数据操纵，数据定义，事务控制，游标处理等操作实现语句块的功能!我们可以简单理解为Java语句中的main函数。
（3）异常处理部分：以关键字Exception开始，以end结束。该部分用于处理该语句块执行过程中产生的异常！我们可以简单理解为Java语言的异常处理。
注意：执行部分是必须的，而声明部分和异常部分是可选的。
例:编写一个PL/SQL语句程序，查询并输出109号员工的名字!

declare
  name varchar(20);
begin
  select employee_name into name from employees
  where employee_id=109;
  dbms_output.put_line(name);
exception
when no_data_found then
  dbms_output.put_line('There is not such an employee');
end;

注意：上述代码从数据库中查出来的employee_name无法直接在代码块中输出，所以我们必须将从数据库中查出来的employee_name写入提前声明的变量name中,另外，语句dbms_output.put_line(name)就是输出变量name的值，但是，在使用输出语句之前，必须设置环境变量serveroutput设置为on。

set serveroutput on;

字符集：
PL/SQL的字符集不区分大小写；
注释：
单行注释可以在一行的任何地方以"–"开始，直到改行结尾；
多行注释以/* 开始，以*/ 结束，可以跨越多行！
数据类型：
Oracle中大部分数据类型与Mysql一致，这里我仅仅介绍一下记录类型！
记录类型：在PL/SQL程序中，记录类型是一个包含若干个成员分量的复合类型。在使用记录类型时，需要现在声明部分定义记录类型。定义记录类型的语法为：

type record_type is record(
	field1 datatype1 [not null] [default|:=expr1],
	field2 datatype2 [not null] [default|:=expr2],
	…
	fieldn datatypen [not null] [default|:=exprn]
)

例如：利用记录类型和记录类型变量，查询并输出109号员工的员工名与工资。

declare
    type t_emp is record (name char(10),sal number(8,2));
    v_emp t_emp;
begin
    select employee_name,salary into v_emp from employees
    where employee_id=109;
    dbms_output.put_line(v_emp.name||''||v_emp.sal);
end;

此外，在PL/SQL中有两个与数据类型相关的特殊属性：%type与%rowtype。如果要定义一个类型某个变量的数据类型或数据库表中某个列的数据类型一致的变量，则可以利用%type来实现。如果要定义一个与数据库中某个表结构一致的记录类型的变量，则可以使用%rowtype来实现的。
例如：查询109号员工的工资以及140号员工的员工名和工资，并输出。

declare
  sal employees.salary%type;
  emp employees%rowtype;
begin
  select salary into sal from employees where employee_id=109;
  select * from into emp from employees where employee_id=140;
  dbms_output.put_line(sal);
  dbms_output.put_line(emp.employee_name||''||emp.salary);
emd;

3.控制结构

在PL/SQL程序中引入了控制结构，包括选择结构，循环结构和跳转结构！

1.选择结构

在PL/SQL程序中，选择结构可以通过IF语句来实现，也可以通过CASE语句来实现。

1.IF语句

利用if语句实现选择控制的语法为：

if condition1 then statements1;
[elsif condition2 then statements2;]
…
[else else_statements];
end if;

例如：输入一个员工号，修改该员工的工资。如果该员工为10号部门，则工资增加100元；若为20号部门，则工资增加140元；若为30号部门，则工资增加200元；否则增加300元。

declare
  empno employees.employee_id%type;
  depno departments.department_id%type;
  increment number(4);
begin 
  empno:=&x;
  select department_id into depno from employees
  where employee_id=empno;
  if depno=10 then increment:=100;
  elsif depno=20 then increment:=140;
  elsif depno=30 then increment:=200;
  else increment:=300;
  end if;
  update employees set salary=salary+increment where employee_id=empno;
end;

2.CASE语句

在Oracle12c中提供了另一种选择控制结构，即CASE语句，语法为：

case
	when condition1 then statements1;
	when condition2 then statements2;
	…
	when conditionn then statementsn;
	[else else_statements;]
end case;

case语句对每一个when条件进行判断，当条件为真时，执行其后的语句如果所有条件都不为真，则执行else后面的语句。总之，我们可以按照Java语句的swtich…case理解即可！
例：根据输入的员工号，修改员工的工资。如果该员工工资低于1000元，则工资增加200元；如果工资在1000~2000元之间，则增加140元；如果工资在2000 ~3000元之间，则增加100元；否则增加50元！

declare
  empno employees.employee_id%type;
  sal employees.salary%type;
  increment number(4);
begin
  empno:=&x;
  select salary into sal form employees
  where employee_id=empno;
  case
    when sal<1000 then increment:=200;
    when sal<2000 then increment:=140;
    when sal<3000 then increment:=100
    else increment:=50;
  end case;
  update employees set salary=salary+increment
  where employee_id=empno;
end;

2.循环结构

在PL/SQL程序中，循环结构有山中风形式，分为简单循环，while循环和for循环。

1.简单循环

PL/SQL程序中简单循环是将循环条件包含在循环体中的循环，语法为：

loop
  suquence_of_statement;
  exit [when condition];
end loop;

注意：在循环体中一定要包含exit语句，否则程序会进入死循环。
例：利用简单循环求1-100之间偶数的和。

declare
  count binary_integer:=1;
  sum number:=0;
begin
  loop
    if mod(count,2)=0 then
      sum:=sum+count;
    end if
    count:=count+1;
    exit when count>100;
  end loop;
  dbms_output.put_line(sum);
end;

exit后面是退出循环的条件，一旦count>100，则退出简单循环。
BINARY_INTEGER 表示一个有符号整数，该类型只能在PL/SQL中使用！

2.while循环

利用while语句进行循环时，先判断循环条件只有满足循环条件才能进入循环体内进行循环操作，其语法为：

while condition loop
	sequence_of _statement;
end loop;

例：利用while循环求1-100之间偶数的和。

declare 
  count binary_integer:=1;
  sum number:=0;
begin
  while count<=100 loop
    if mod(count,2)=0 then 
      sum:=sum+count;
    end if;
    count:=count+1;
   end loop;
   dbms_output.put_line(sum);
end;

这里与简单循环不同的是，while循环是满足条件才进入循环，而简单循环是一旦满足判断条件则退出循环。

3.for循环

在简单循环和while循环中，需要定义循环变量，不断修改循环变量的值，以达到控制循环次数的目的；而在for循环中，不需要定义循环变量，系统会自定义一个循环变量，每次循环时该变量值自动增1或减1，以控制循环次数。for循环的语法为：

for loop_counter in[reverse] low_bound..high_bound loop
  sequence_of_statement;
end loop;

简单说一下：loop_counter为当前循环变量，low_bound为循环变量的下界（最小值），high_boundWie循环变量的上界（最大值）。循环变量不需要显示定义，系统会隐含地将它声明为binary_integer类型，系统默认时，循环变量从下界往上界递增计数，如果使用reverse关键字，则表示循环变量从上界到下界递减计数。
例：利用for循环求1-100之间偶数的和。

declare
  sum number:=0;
begin
  for count in 1..100 loop
    if mod(count,2)=0 then 
      sum:=sum+count;
    end if;
    count:=count+1;
    end loop;
   dbms_output.put_line(sum);
end;

4.游标

为了处理select后返回的多行数据，我们引入了游标。
PL/SQL程序中的游标分为两大类：
（1）显示游标：由用户定义、操作，用于处理返回多行数的select的查询！
（2）隐式游标：有系统自动进行操作，用于处理DML语句和返回单行数据的select查询。

1.显示游标

利用显式游标处理select查询返回的多行数据，需要先定义显式游标，然后打开游标，检索游标，最后关闭游标！

1.定义游标

根据要查询的数据情况，在PL/SQL语句块的声明部分定义游标，语法为：

cursor cursor_name is select_statement;

定义游标需要注意下列事项：
1.游标必须在PL/SQL语句块的声明部分进行定义；
2.游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；
3.定义游标并没有生成数据，只是将定义信息保存到字典中；
4.游标定义后，可以使用cursor_name%rowtype定义记录类型的变量；

2.打开游标

为了在内存中分配缓冲区，并从数据库中检索数据，需要在PL/SQL语句块的执行部分打开游标，语法为:

open cursor_name;

3.检索游标

打开游标，将查询结果放入缓冲区后，需要将游标中的数据以记录为单位检索出来，然后在P/SQL程序中实现过程化的处理。检索游标使用fetch…into语句，其语法为：

fetch cursor_name into variable_list|record_variable;

检索游标需要注意下列事项：
1.在使用fetch语句之前必须先打开游标，保证缓冲区中有数据。
2.对游标第一次使用fetch语句时，游标指针指向第一条记录，因此操作的对象是第一条记录。操作完后，游标指针指向第一条数据。
3.游标只能向下移动，不能回退。如果想要检索完第二天记录后又回到第一条记录，则必须关闭游标，然后重新打开游标。
4.into子句中变量的个数，顺序，数据类型必须个缓冲区中每个记录的字段数量，顺序，数据类型向匹配，也可以是记录类型的变量。

4.关闭游标

游标对应缓冲区的数据处理完后，应及时关闭游标，以释放它所占用的系统资源。关闭游标的语法如下所示：

close cursor_name;

例：根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定（用户输入）。由于某个部门的人数是不定的，也可能多个，因此需要采用游标来处理。

declare
  depno departments.department_id%type;
  --定义游标
  cursor c_emp is select * from employees where department_id=depno;
  v_emp c_emp%rowtype;
begin
  depno:=&x;
  --打开游标
  open c_emp;
  loop
    --检索游标
    fetch c_emp into v_emp;
    dbms_output.put_line(v_emp.employee_id||''||v_emp.employee.name||''||v_emp.salary);
    exit when c_emp%notfound;
  end loop;
  --关闭游标
  close c_emp;
end;

注意：利用游标属性可以判断当前游标状态。显式游标的属性及其含义如下所示：
（1）%isopen：布尔型，用于检索游标是否打开。如果游标已经打开，则返回true，否则返回false；
（2）%found：布尔型，判断最近一次使用fetch语句时是否从缓冲区中检索到数据。如果检索到数据，则返回true，否则则返回false；
（3）%notfound：布尔型，判断近义词使用fetch语句时是否从缓冲区中检索到数据。与%found相反，如果没有检索到数据则返回true，否则返回false；
（4）%rowtype:数值型，返回到目前为止从游标缓冲区检索记录的个数；

2.隐式游标

隐式游标又称SQL游标，主要用于处理insert，update，delete以及单行的select语句，没有open，fetch，close等关闭操作命令。
与显式游标类似，隐式游标也有下列4个属性。
（1）SQL%isopen：布尔型，判断隐式游标是否已经打开。
（2）SQL%found：布尔型，判断当前的操作是否会对数据库产生影响。如果有数据的插入，删除，修改或没有查询到数据，则返回true，否则返回false；
（3）SQL%notfound:布尔型，判断当前操作是否对数据库产生影响。如果没有数据的插入，删除，修改或没有查询到数据，则返回true，否则返回false；
（4）SQL%rowcount：数值型，返回当前操作所涉及的数据库行数。
例：修改员工号为1000的员工工资，将其工资增加100元。如果该员工不存在，则向employees中插入一个员工号为1000的记录

begin
  update employees set salary=salary+100 where employee_id=1000;
  if sql%notfound then
    insert into employees(employee_id,employee_name,email,phone_number,hire_date)
    values(1000,'小王','zs@qq.com','112',sysdate);
  end if;
end;