5.1 PL/SQL概述

5.1.1 PL/SQL块结构
PL/SQL程序都是以块(BLOCK）为基本单位
语法结构：
[DECLEAR]
  --声明部分，可选
BEGIN
  --执行部分，必须
[EXCEPTION]
   --异常处理部分，可选
END
每一条语句必须以分号结束。

SQL> set serveroutput on
SQL> declare
  2 a int:=100;
  3 b int:=200;
  4 c number;
  5 begin
  6 c:=(a+b)/(a-b);
  7 dbms_output.put_line(c);
  8 exception
  9 when zero_divide then
 10 dbms_output.put_line('除数不许为零!');
 11 end;
 12 /
-3
PL/SQL procedure successfully completed.

5.1.2 代码注释和标识符
1、单行注释
由两个连字符“--”开始
SQL> declare
  2 Num_sal number; --声明一个数值变量
  3 Var_ename varchar2(20); --声明一个字符串变量
  4 begin
  5 select ename,sal into Var_ename,Num_sal from emp
  6 where empno=7369; --检索指定的值并存储到变量中
  7 dbms_output.put_line(Var_ename||'的工资是'||Num_sal);
  8 end;

2、多行注释
由/*开头，由*/结尾

3、PL/SQL字符集

5.2 数据类型和定义变量和常量
5.2.1 基本数据类型
1、数值类型
number,pls_integer,binary_integer
number(p,s)
Num_Money number(9,2);
2、字符类型
varchar2,char,long,nchar,nvarchar2
varchar2(maxlength)
char(maxlength)
3、日期类型
date
存储空间是7个字节，分别使用一个字节存储世纪，年，月，天，小时，分钟和秒。
4、布尔类型
boolean
变量值可以是TRUE,FALSE和NULL

5.2.2 特殊数据类型
1、%type类型
声明一个与指定列名称相同的数据类型，紧跟在指定列名的后面。
var_job emp.job%type;
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> declare
  2 var_ename emp.ename%type;
  3 var_job emp.job%type;
  4 begin
  5 select ename,job into var_ename,var_job
  6 from emp
  7 where empno=7369;
  8 dbms_output.put_line(var_ename||'的职务是
  9 '||var_job);
 10 end;
 11 /
SMITH的职务是
CLERK
PL/SQL procedure successfully completed.
由于into子句中的变量只能存储一个单独的值，所以要求select子句只能返回一行数据。

2、record类型
定义record类型的语法格式：
type record_type_name is record
(
var_member1 data_type [not null][:=default_value],
...
var_member   date_type [not null][:=default_value])

declare
type emp_type is record
(
var_name varchar2(20),
var_job varchar2(20),
var_sal number
);
empinfo emp_type;
begin
select ename,job,sal
info empinfo
from emp
where empno=7369;
dems_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);
end;

SQL> set serveroutput on
SQL> declare
  2 type emp_type is record
  3 (
  4 var_ename varchar2(20),
  5 var_job varchar2(20),
  6 var_sal number
  7 );
  8 empinfo emp_type;
  9 begin
 10 select ename,job,sal
 11 into empinfo
 12 from emp
 13 where empno=7369;
 14 dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);
 15 end;
 16 /
雇员SMITH的职务是CLERK,工资是2758.33
PL/SQL procedure successfully completed.

3、%rowtype类型
存储从数据表中检索到的一行数据。
语法结构：
rowVar_name table_name%rowtype;
SQL> set serveroutput on
SQL> declare
  2 rowvar_emp emp%rowtype;
  3 begin
  4 select *
  5 into rowvar_emp
  6 from emp
  7 where empno=7369;
  8 dbms_output.put_line('雇员'||rowvar_emp.ename||'的编号是'||rowvar_emp.empno||',职务是'||rowVar_emp.job);
  9 end;
 10 /
雇员SMITH的编号是7369,职务是CLERK
PL/SQL procedure successfully completed.

5.2.3 定义变量和常量
1、定义变量
语法格式：
<变量名><数据类型>[(长度）:=<初始值>];
var_countryname varchar2(50):='中国'；

2、定义常量
语法格式：
<常量名>constant<数据类型>:=<常量值>;
con_day constant integer:=365;

5.3 流程控制语句
5.3.1 选择语句
1、if...then语句
语法格式：
if<condition_expression>then
plsql_sentence
end if;

if last_name is null then
...
end if;

编辑器中编辑如下：
declare
var_name1 varchar2(50);
var_name2 varchar2(50);
begin
  var_name1:='east';
  var_name2:='xiaoke';
  if length(var_name1)<length(var_name2) then
    dbms_output.put_line('字符串"'||var_name1||'"的长度比字符串"'||var_name2||'"的长度小');
  end if;
end;
/

SQL> set serveroutput on
SQL>
字符串"east"的长度比字符串"xiaoke"的长度小
PL/SQL procedure successfully completed

2、if...then...else语句
语法格式：
if<condition_expression>then
 plsql_sentence1；
else
 plsql_sentence2；
end if;

declare
  age int:=55;
begin
  if age>=56 then
    dbms_output.put_line('您可以申请退休了！');
  else
    dbms_output.put_line('您小于56岁，不可以申请退休了！');
  end if;
end;
/
SQL>
您小于56岁，不可以申请退休了！
PL/SQL procedure successfully completed

3、if...then...elsif语句
语法格式：
if<condition_expression1>then
   plsql_sentence1；
elsif<condition_expression2>then
   plsql_sentence2；
...
else
  plsql_sentencen；
end if;

declare
  month int:=10;
begin
  if month>=0 and month <=3 then
    dbms_output.put_line('这是春季');
  elsif month>=4 and month <=6 then
    dbms_output.put_line('这是夏季');
  elsif month>=7 and month <=9 then
    dbms_output.put_line('这是秋季');
  elsif month>=10 and month <=12 then
    dbms_output.put_line('这是冬季');
  else
    dbms_output.put_line('对不起，月份不合法！');
  end if;
end;
/

SQL>
这是冬季
PL/SQL procedure successfully completed

4、case语句
语法格式：
case<selector>
  when<expression_1>then plsql_sentence_1;
  when<expression_2>then plsql_sentence_2;
  ...
  when<expression_n>then plsql_sentence_n;
  [else plsql_sentence;]
end case;

declare
  season int:=3;
  aboutinfo varchar2(50);
begin
  case season
  when 1 then
    aboutinfo:=season||'季度包括1,2,3月份';
  when 2 then
    aboutinfo:=season||'季度包括4,5,6月份';
  when 3 then
    aboutinfo:=season||'季度包括7,8,9月份';
  when 4 then
    aboutinfo:=season||'季度包括10,11,12月份';
  else
    aboutinfo:=season||'季节不合法';
  end case;
  dbms_output.put_line(aboutinfo);
end;
/

SQL>
3季度包括7,8,9月份
PL/SQL procedure successfully completed

5.3.2 循环语句
1、loop语句
程序至少执行一次循环体
语法格式：
loop
  plsql_sentence;
  exit when end_condition_exp;
end loop;

declare
  sum_i int:=0;
  i int:=0;
begin
  loop
    i:=i+1;
    sum_i:=sum_i+i;
    exit when i=100;
  end loop;
  dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;
/

SQL>
前100个自然数的和是：5050
PL/SQL procedure successfully completed

2、while语句
语法格式：
while condition_expression loop
  plsql_sentence;
end loop;

declare
  sum_i int:=0;
  i int:=0;
begin
  while i<=99 loop
    i:=i+1;
    sum_i:=sum_i+i;
  end loop;
  dbms_output.put_line('前100个自然数的和是：'||sum_i);
end;
/

SQL>
前100个自然数的和是：5050
PL/SQL procedure successfully completed

3、for语句
语法格式：
for variable_counter in [reverse] lower_limit..upper_limit loop
  plsql_sentence;
end loop;

declare
  sum_i int:=0;
begin
  for i in 1..100 loop
    if mod(i,2)=0 then
      sum_i:=sum_i+i;
    end if;
  end loop;
  dbms_output.put_line('前100个自然数中偶数之和是：'||sum_i);
end;
/

SQL>
前100个自然数中偶数之和是：2550
PL/SQL procedure successfully completed

5.4 PL/SQL游标
游标的作用相当于指针
显式游标和隐式游标
5.4.1 显式游标
由用户声明和操作的一种游标，通常用于操作查询结果集。
处理数据的步骤：声明游标，打开游标，读取游标和关闭游标。
游标每次只能读取一行数据
1、声明游标
语法格式：
cursor cur_name[(input_parameter[,input_parameter2]...)]
[return ret_type]
is select_sentence;

ret_type：执行游标操作后的返回值类型。
select_sentence：游标所使用的select语句，为游标的反复读取提供结果集。
input_parameter1：作为游标的输入参数，指定用户在打开游标后向游标中传递的值，该参数的定义和初始化格式如下：
para_name [in] datatype[{:=|default}para_value]

定义游标应该放在PL/SQL块得declare部分。
declare
  cursor cur_emp(var_job in varchar2:='SALESMAN')
  is select empno,ename,sal
    from emp
    where job=var_job;

2、打开游标
语法格式：
open cur_name[(para_value1[,para_value2]...)]'

3、读取游标
语法格式：
fetch cur_name into {variable};

declare
  cursor cur_emp(var_job in varchar2:='SALESMAN')
  is select empno,ename,sal
    from emp
    where job=var_job;
type record_emp is record
(
  var_empno emp.empno%type,
  var_ename emp.ename%type,
  var_sal emp.sal%type
);
emp_row record_emp;
begin
  open cur_emp('MANAGER');
  fetch cur_emp into emp_row;
  while cur_emp%found loop
    dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'，工资是'||emp_row.var_sal);
    fetch cur_emp into emp_row;
  end loop;
  close cur_emp;
end;
/

SQL>
JONES的编号是7566，工资是2975
BLAKE的编号是7698，工资是2850
CLARK的编号是7782，工资是2450
PL/SQL procedure successfully completed

4、关闭游标
语法格式：
close cur_name;

5.4.2 游标的属性
%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为false。
%notfound：布尔型属性，与%fonud相反。
%rowcount：数字型属性，返回受SQL语句影响的行数。
%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。

declare
  var_ename varchar2(50);
  var_job varchar2(50);
  cursor cur_emp
  is select ename,job
     from emp
     where empno=7499;
begin
    open cur_emp;
    fetch cur_emp into var_ename,var_job;
    if cur_emp%found then
      dbms_output.put_line('编号是7499的雇员名称为：'||var_ename||'，的职务是：'||var_job);
    else
      dbms_output.put_line('无数据记录');
    end if;
end;
/

SQL>
编号是7499的雇员名称为：ALLEN，的职务是：SALESMAN
PL/SQL procedure successfully completed

4.3 隐式游标
begin
  update emp
  set sal=sal*(1+0.2)
  where job='SALESMAN';
  if sql%notfound then
    dbms_output.put_line('没有雇员需要上调工资');
  else
    dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');
  end if;
end;
/

SQL>
有4个雇员工资上调20%
PL/SQL procedure successfully completed

5.4.4 通过for语句循环游标
（1）在for语句中遍历隐式游标中的数据时，通常在关键字“in”的后面提供由select语句检索的结果集，在检索结果集的过程中，oracle系统会自动提供一个隐式的游标sql
begin
  for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
  loop
    dbms_output.put('雇员编号： '||emp_record.empno);
    dbms_output.put('；雇员名称： '||emp_record.ename);
    dbms_output.put_line('；雇员工资： '||emp_record.sal);
  end loop;
end;
/

SQL>
雇员编号： 7499；雇员名称： ALLEN；雇员工资： 1920
雇员编号： 7521；雇员名称： WARD；雇员工资： 1500
雇员编号： 7654；雇员名称： MARTIN；雇员工资： 1500
雇员编号： 7844；雇员名称： TURNER；雇员工资： 1800
PL/SQL procedure successfully completed

（2）在for语句中遍历显式游标中的数据时，通常在关键字“in”的后面提供游标的名称
语法格式：
for var_auto_record in cur_name
loop
  plsql sentence;
end loop;

declare
cursor cur_emp is
select * from emp
where deptno=30;
begin
  for emp_record in cur_emp
  loop
    dbms_output.put('雇员编号： '||emp_record.empno);
    dbms_output.put('；雇员名称： '||emp_record.ename);
    dbms_output.put_line('；雇员职务： '||emp_record.job);
  end loop;
end;
/

SQL>
雇员编号： 7499；雇员名称： ALLEN；雇员职务： SALESMAN
雇员编号： 7521；雇员名称： WARD；雇员职务： SALESMAN
雇员编号： 7654；雇员名称： MARTIN；雇员职务： SALESMAN
雇员编号： 7698；雇员名称： BLAKE；雇员职务： MANAGER
雇员编号： 7844；雇员名称： TURNER；雇员职务： SALESMAN
雇员编号： 7900；雇员名称： JAMES；雇员职务： CLERK
PL/SQL procedure successfully completed

在使用游标（包括显式和隐式）的for循环中，可以声明游标，但不要进行打开游标，读取游标和关闭游标等操作，这些由oracle系统内部自动完成。