2019-05-11

PL(Procedural Language)/SQL概述

​ 在实际工作中，有些复杂的数据处理操作需要在客户端编程实现，但是客户端所处理的数据必须由数据库服务器通过网络传输而来，如果数据处理量非常大，采用客户端编程的方法就降低了数据处理速度。为了减少客户端与数据库服务器端之间的网络数据传输量，提高数据处理速度，可以直接在数据库服务器端编程，实现对数据的处理，处理完成后直接将处理结果返回客户端。为此Oracle提供了在数据库服务器端编程的语言，即PL/SQL。
​ SQL只是一种声明式语言，是非过程性的，语句之间相互独立。在实际工作中，许多事务处理应用都是过程性的，前后语句之间存在一定关联。PL/SQL是过程化SQL，可以弥补SQL语句的不足。可以通过IF和LOOP语句控制程序的执行流程；可以定义变量以便在语句之间传递数据信息。PL/SQL是对标准SQL语言的扩展， SQL语句完全可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。

pl/sql的分类：

• 匿名的pl/sql:只是一段pl/sql语句块，只是在运行时执行一次，不会在数据库系统中永久存储。

• 命名的pl/sql：pl/sql存储在数据库系统中，可以按照名称重复调用。包括存储过程、函数、触发器等

[ declare 定义语句段 ; ]    --声明部分，可选项，用于声明变量
begin
    执行语句段 ;              --执行部分，可包含流程控制和sql语句
[ exception 异常处理语句段 ; ]     --异常处理部分，可选项
end;

示例：根据用户输入的员工编号获得其工资和奖金

declare
    var_empno number;  --员工编号
    var_sal number;    --工资
    var_comm number;   --奖金
begin
    --接收用户输入的员工编号
    var_empno:=&var_empno;
    --获得工资
    select sal,nvl(comm,0) into var_sal,var_comm from scott.emp where empno=var_empno;
    dbms_output.put_line('员工编号是'||var_empno||'的工资是'||var_sal||'奖金是'||var_comm);
end; 

示例：根据用户输入的员工编号进行涨工资10%，但是如果增长后的工资超过3200则不予增长

declare
    --员工编号
    var_empno number;
    --工资
    var_sal number;
begin
    --获得员工编号
    var_empno:=&var_empno;
    --根据编号获得其工资
    select sal into var_sal from scott.emp where empno=var_empno;
    --判断工资增长后是否超过3200
    if var_sal*1.1<=3200 then
        --更新工资
        update scott.emp set sal=sal*1.1 where empno=var_empno;
        commit;
    end if;
end; 

变量与数据类型

变量名 数据类型 := 初值 ; 
变量名 数据类型  default  初值 ;

• 长度不能超过30个字符，不能有空格。
• 由字母、数字、下划线、美元符号$和#号组成，必须以字母开头。
• 不能使用PL/SQL或SQL的关键字。可以使用help reserved words命令获得关键字列表。
• 变量默认值为null。

变量命名规范

变量的标准数据类型：

 number：数字类型，可以代表整数和浮点数。

 int：整数型。

 pls_integer：整数型，计算速度快（直接由cpu执行），占用较少的存储空间。但产生溢出时将触发异常。

 binary_integer：带符号的整数型，不会出现溢出，但计算速度较慢（由oracle模拟执行）。

 char：定长字符，最长255个字符。

 varchar2：变长字符，最长2000个字符。

 long：变长字符，最长2gb。

 date：日期型。

 boolean：布尔型(true或false)。

流程控制语句

条件语句

if <表达式> then
    语句块;
end if;

if <表达式> then
    语句块1;
else
    语句块2;
end if;

if <表达式1> then
       语句块1;
elsif <表达式2> then
       语句块2;
else
       语句块3;
end if;

case语句

case <表达式>
    when <表达式1> then 语句块1;
    when <表达式2> then 语句块2;
      ……
    when <表达式n> then 语句块n;
   [ else 语句块 n+1 ]
end ;

循环语句

loop
  循环体; 
  exit when 循环退出条件
end loop;

while 循环条件
loop 
    循环体;
end loop;

for 循环控制变量 in [reverse] 循环下限 .. 循环上限 
loop
  循环体;
end loop

for循环示例

declare 
   sum_num number := 0;
begin
   for i in 1..100 loop
     sum_num := sum_num + i;
   end loop;
   dbms_output.put_line(sum_num);
end;

%TYPE变量

%TYPE用来定义与数据表中指定字段数据类型相同的变量。如果表中字段的数据类型或长度发生变化，%TYPE变量会自动随之变化。这样用户不必查看表中各个字段的数据类型，就可以确保所定义的变量能够正确存储字段数据。

变量名 方案名.表名.字段名%type

var_empno scott.emp.empno%type; --员工编号

自定义记录类型变量

自定义记录数据类型可以表示由多个字段值组成的一行数据。使用时首先要定义记录类型的结构，然后声明该类型的变量存储数据。

type 类型名称 is record (
     变量名称  数据类型 := 默认值 , 
     ……
     变量名称  数据类型 := 默认值 
) ;

示例1：输入职工编号，输出其姓名与工资

declare
    type employee_type is record (  --声明自定义记录类型
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp employee_type ; --声明自定义记录类型变量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp from scott.emp where  empno=var_empno ; 
     dbms_output.put (var_emp.ename) ;
     dbms_output.put_line(' '||var_emp.sal) ; 
end; 

示例2：允许同一记录类型的两个变量整体赋值。

declare
    type employee_type is record ( 
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp1 employee_type ;
    var_emp2 employee_type ; 
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp1 from scott.emp where empno=var_empno ;
    var_emp2:=var_emp1; 
    dbms_output.put (var_emp2.ename) ;
    dbms_output.put_line(' '||var_emp2.sal) ; 
end; 

%ROWTYPE变量

%ROWTYPE类型可以根据数据表的行结构定义数据类型，用于存储从数据表中检索到的一行完整的数据。如果数据库表的结构发生变化，记录变量中的结构也将随之改变。

变量名 方案名.表名%rowtype

示例：输入职工编号，输出其详细信息

declare
    var_emp  scott.emp%rowtype ; --数据行变量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select * into var_emp from scott.emp where  empno=var_empno ;
    dbms_output.put (var_emp.ename) ;
    dbms_output.put_line(' '||var_emp.job) ; 
    dbms_output.put_line(' '||var_emp.sal) ;
end; 

异常处理

PL/SQL程序运行时出现的错误称为异常。可以分为两类：

 预定义异常： 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

 用户自定义异常：用户可以声明自定义异常，自定义的异常通过 raise 语句手动引发

系统预定义异常

begin
     过程及sql语句;
exception
     when 异常名称then
          过程及sql语句;
     when others then
          过程及sql语句;
end;

可以使用others子句捕获各种异常，同时结合两个函数向用户报告信息：

 sqlcode函数：返回出错码

 sqlerrm函数：返回出错信息

begin
  insert into scott.emp(empno , ename , job , sal , deptno)
  values('7999','ATG','CLERK',1500,'ABC');
exception
  when dup_val_on_index then
    dbms_output.put_line('捕获dup_val_on_index异常');
    dbms_output.put_line('该主键值已经存在');
  when others then  -- others子句必须放在各种异常处理的最后
    dbms_output.put_line( '错误号：' || sqlcode );
    dbms_output.put_line( '错误描述：' ||sqlerrm );
end;

用户自定义异常

程序开发人员可以根据具体的业务逻辑规则，自定义特定异常。当用户操作违反了业务逻辑规则后，可以手动引发该异常，从而中断程序的正常执行，转到自定义的异常处理代码。

自定义异常的处理步骤：

 定义异常处理：异常名 exception ;

 触发异常处理：raise 异常名