Oracle 9~ PL/SQL

只有单一的sql语句进行数据操作，没有流程控制，就无法开发复杂的应用。

PL/SQL（Procedural Language/SQL）是Oracle公司在标准SQL的基础上进行扩展，可以在数据库上进行设计编程的一种结构化语言。支持更多的数据类型，拥有变量声明、赋值语句、控制语句等，可以创建过程、函数及程序包。

1. PL/SQL块是PL/SQL程序的最基本单位，由声明（declare）、执行（begin-end）和异常处理（exception）三部分组成。其语法结构为：

DECLARE
[声明部分]
BEGIN
[执行部分]
EXCEPTION
[异常处理]
END;

例如，用简单的PL/SQL语句输出学生信息表stuinfo中的某一位学生的基本信息。

set serveroutput on; //这行代码必须有，否则dbms窗口不会显示结果
declare
zs_info varchar2(50);
begin
    select '姓名:'|| t.stuname ||'学号:'|| t.stuid ||'年龄:'||t.age
    into zs_info from stuinfo t
    where t.stuname = '张三';
    dbms_output.put_line(zs_info);
//select into可以看做赋值语句
exception 
    when no_data_found then
      dbms_output.put_line('查无此人');
end;

2. PL/SQL中的数据类型

  标量数据类型

（1）数值类型：number、pls_integer、binary_integer、simple_integer

（2）字符类型：char、varchar2、long

（3）时间类型：date、timestamp

（4）布尔类型：bool(true,false,null)

  引用数据类型：PL/SQL特有的数据类型

（1）%TYPE：引用数据库中表的某列作为数据类型，也可以引用PL/SQL中的某个变量的数据类型作为新变量的数据类型

例如，查询学号为‘sc201801006’学生的姓名。

declare
ls_stuname stuinfo.stuname%type;
begin
select t.stuname into ls_stuname
from stuinfo t
where t.stuid = 'sc201801006';
DBMS_OUTPUT.PUT_LINE(ls_stuname);
exception 
 when no_data_found then
 dbms_output.put_line('查无此人');
end;

（2）%ROWTYPE：引用数据库表中的一行作为数据类型，使用这一数据类型能让变量像JAVA中的对象一样，以‘变量.列名’的形式查询。

例如，查询学号为‘sc201801006’学生的个人信息。

declare
test_stuinfo stuinfo%rowtype;
info_result varchar2(50);
begin
select t.* into test_stuinfo
from stuinfo t
where t.stuid = 'sc201801006';
info_result := '姓名:'||test_stuinfo.stuname ||'年龄:'|| test_stuinfo.age ||'性别:'|| test_stuinfo.sex;
DBMS_OUTPUT.PUT_LINE(info_result);
exception
when no_data_found then
dbms_output.put_line('查无此人');
end;

3.PL/SQL中的顺序结构

（1）GOTO关键字的使用

declare
test_stuinfo stuinfo%rowtype;
info_result varchar2(50);
begin
select t.* into test_stuinfo
from stuinfo t
where t.stuid = 'sc201801006';
info_result := '姓名:'||test_stuinfo.stuname ||'年龄:'|| test_stuinfo.age ||'性别:'|| test_stuinfo.sex;
DBMS_OUTPUT.PUT_LINE(info_result);
if test_stuinfo.age > 25
then goto flag1;
else goto flag2;
end if;
<<flag1>> dbms_output.put_line('年龄大于25岁');
<<flag2>> null; //null表示不执行任何内容，直接跳过
exception
when no_data_found then
dbms_output.put_line('查无此人');
end;

4.PL/SQL中的条件控制结构

PL/SQL中的这些结构其实跟其他的编程语言基本一样，所以我直接用例子练一遍就过了。

例1，查询男生的数量。

declare
test_info stuinfo%rowtype;
boy_number number :=0;
begin
for test_info in (select t.* from stuinfo t)  loop
  if test_info.sex = '1' then
  boy_number := boy_number+1;
  end if;
end loop;
dbms_output.put_line(boy_number);
end;

例2，查询男生、女生的数量。

declare
test_info stuinfo%rowtype;
boy_number number :=0;
girl_number number :=0;
begin
for test_info in (select t.* from stuinfo t)  loop
  if test_info.sex = '1' then
  boy_number := boy_number+1;
  else
  girl_number:=girl_number+1;
  end if;
end loop;
dbms_output.put_line('男生的数量为 '||boy_number);
dbms_output.put_line('女生的数量为 '||girl_number);
end;

例3，查询各个年龄的学生数量。

declare
test_info stuinfo%rowtype;
num26 number :=0;
num27 number :=0;
num_other number :=0;
begin
for test_info in (select t.* from stuinfo t) loop
case test_info.age
when 26 then num26 := num26+1;
when 27 then num27 := num27+1;
else num_other := num_other +1;
end case;
end loop;
dbms_output.put_line('26岁的学生数量='||num26||',27岁的学生数量='||num27||',其它年龄的学生数量='||num_other);
end;

5.PL/SQL中的循环结构：FOR循环和WHILE循环

（1）for循环的语法结构

FOR 循环体别名 IN (SELECT FROM) LOOP
执行体
END LOOP;


FOR 循环变量 IN 循环上限..循环下限 LOOP
执行体
END LOOP;

例1.for循环的第一种写法可参考上一个例子，用第二种写法对1到n(n=4)求和。

declare
n number :=0;
begin
FOR i IN 1..4 LOOP
 n := n+i;  
END LOOP;
DBMS_OUTPUT.PUT_LINE (n);
end;

例2. 1到n(n=4)求和，while循环。

declare
n number :=0;
i number :=1;
begin
while i < 5 loop
n := n+i;
i := i+1;
end loop;
dbms_output.put_line(n);
end;

6.动态执行DDL语句

PL/SQL中只能使用DML语句，但不能直接用DDL语句，需要通过动态SQL语句，其写法如下：

EXECUTE IMMEDIATE 动态SQL语句
[into 变量列表]
[using 参数列表]

例1，利用动态SQL语句创建学生信息表的备份表stuinfo_2018。

declare
stu_exe varchar2(500);
begin
stu_exe := 'create table stuinfo_201812
(stuid varchar2(11),stuname varchar2(50),sex char(1),age number(2),
 classno varchar2(7),stuaddress varchar2(100),grade char(4),enroldate date,idnumber varchar2(18))';
 execute immediate stu_exe;
 end;

例2，给备份表插入一条学生信息。

declare
sql_exe varchar2(500);
copy_stuid varchar2(11);
copy_stuname varchar2(50);
copy_sex char(1);
copy_age number(2);
begin
select t.stuid,t.stuname,t.sex,t.age  into copy_stuid,copy_stuname,copy_sex,copy_age
from stuinfo t where t.stuid = 'sc201801006';
sql_exe := 'insert into stuinfo_201812 values(:1,:2,:3,:4,null,null,null,null,null)';
execute immediate sql_exe using copy_stuid,copy_stuname,copy_sex,copy_age;
end;

例3，利用动态SQL查询刚刚插入的信息。

declare
sql_exe varchar2(500);
select_id varchar2(11);
result_tmp stuinfo%rowtype;
begin
select t.stuid into select_id 
from stuinfo t 
where t.stuid = 'sc201801006';
sql_exe := 'select * from stuinfo_201812 where stuinfo_201812.stuid =: 1';
execute immediate sql_exe into result_tmp using select_id;
dbms_output.put_line('学号：'||result_tmp.stuid||'姓名：'||result_tmp.stuname||'性别：'||result_tmp.sex||'年龄：'||result_tmp.age);
end;

7.PL/SQL的异常处理

exception
when 异常1 then 异常处理1
when 异常2 then 异常处理2
when others  then ...

(1)预定义异常

Oracle数据库预定义了25种异常名称，如no_data_found、too_many_rows。

查询所有的预定义异常

select * from dba_source t where t.text like '%EXCEPTION_INIT%' and name ='STANDARD';

例如，查询一条表中不存在的数据。

declare
stuinfo_copy stuinfo%rowtype;
begin
select * into stuinfo_copy from stuinfo where stuinfo.stuid='sc20180120';
exception
when no_data_found then dbms_output.put_line('查无此人');
end;

（2）非预定义异常：只有错误编号和错误信息，没有错误名称。

定义一个非预定义异常：先声明一个异常名称，再进行异常名称和错误编号的关联。

declare
ex_only EXCEPTION;
PRAGMA EXCEPTION_INIT (ex_only, -00001);
begin
update stuinfo t set t.stuid = 'sc201801005'
where t.stuid = 'sc201801006';
EXCEPTION
 when ex_only then dbms_output.put_line('该学号已存在，不允许修改');
end;

（3）自定义异常

 先声明一个异常，再用‘raise exception’抛出异常。

 例，先把stuinfo_2018中张三的性别改为3，然后验证其性别。

declare
ex_check_sex exception;
ls_sex stuinfo.sex%type;
begin
select t.sex into ls_sex from stuinfo_2018 t
where t.stuid = 'sc201801001';
dbms_output.put_line('该学生的性别为：'||ls_sex);
if ls_sex not in ('1','2') then
 raise ex_check_sex;
 end if;
exception
when ex_check_sex then
dbms_output.put_line('性别只能是男或女');
end;