Oracle PL/SQL (1) - 编程基础知识

PL/SQL概述

Oracle提供了在数据库服务器端编程的语言，即PL/SQL。而SQL只是一种声明式语言，是非过程性的，语句之间相互独立。在实际工作中，许多事务处理应用都是过程性的，前后语句之间存在一定关联。
PL/SQL是过程化SQL，可以弥补SQL语句的不足。可以编写过程、函数、触发器，而这些存放在oracle数据库中可以在java程序中调用就节省了传输量和解析时间，同时提高了性能。

在Oracle 中使用PL/SQL来扩展SQL的功能，PL/SQL可以通过IF和LOOP语句控制程序的执行流程；可以定义变量以便在语句之间传递数据信息。PL/SQL是对标准SQL语言的扩展， SQL语句完全可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。使得SQL能够更加的灵活，功能更加强大，效率更高

本文主要讲述PL/SQL的基本语法、条件判断语句、循环语句。

1、PL/SQL变量的数据类型

number：数字类型，可以代表整数和浮点数。
int：整数型。
pls_integer：整数型，计算速度快（直接由cpu执行），占用较少的存储空间。但产生溢出时将触发异常。
binary_integer：带符号的整数型，不会出现溢出，但计算速度较慢（由oracle模拟执行）。
char：定长字符，最长255个字符。
varchar2：变长字符，最长2000个字符。
long：变长字符，最长2gb。
date：日期型。
boolean：布尔型(true或false)。

2、变量的声明和赋值

1）声明变量的语法
变量名 数据类型 := 初值 ;
变量名 数据类型 default 初值 ;
A.长度不能超过30个字符，不能有空格。
B.由字母、数字、下划线、美元符号$和#号组成，必须以字母开头。
C.不能使用PL/SQL或SQL的关键字。可以使用help reserved words 命令获得关键字列表。
D.变量默认值为null。
变量命名规范

image.png

2)使用%TYPE声明变量类型
%TYPE用来定义与数据表中指定字段数据类型相同的变量。如果表中字段的数据类型或长度发生变化，%TYPE变量会自动随之变化。这样用户不必查看表中各个字段的数据类型，就可以确保所定义的变量能够正确存储字段数据。
格式为：变量名 方案名.表名.字段名%type
如：var_username scott.emp.empname%type; --员工名称

3)使用%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; 

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

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

例如:自定义员工姓名与工资类型，输入职工编号，输出其姓名与工资

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; 

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

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; 

3、PL/SQL的语法结构

　　　　declare 
　　　　　　//这部分用于声明变量
　　　　begin
　　　　　　//这部分用于处理业务逻辑
　　　　exception
　　　　　　//这部分用于处理例外也就是异常情况
　　　　end;

例如：

　declare
　　　　　　　　uname varchar2(10);
　　　　　　　　hello varchar2(10) := 'helloworld'; --为变量赋值
　　　　　　　　vsal emp.sal%type; --声明一个引用类型的变量
　　　　　　　　pall emp%rowtype; --声明一个记录类型的变量
　　　　　　　　digit number:=#---提示用户输入，然后根据用户输入执行相应的操作
　　　　　　　　...
　　　　　　begin
　　　　　　　　dbms_output.put_line(hello);
　　　　　　exception
　　　　　　　　when exception_name then
　　　　　　　　　　　//操作
　　　　　　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; 

4、PL/SQL的条件判断语句

if语句

　if 条件 then 
　　　　　　语句；
　end if；

if...else语句

　if 条件 then 
　　　　　　语句；
　else 
　　　　　　语句；
　end if；

if...elseif...else语句

　if 条件 then 
　　　　　　语句；
　elsif 条件then 
　　　　　　语句；
　else 
　　　　　　语句；
　end if；

例如：根据输入的数字判断大小

declare
   v_url VARCHAR(50):='www.baidu.com';
   v_num1 NUMBER:= 100;
   v_num2 NUMBER:=200;
   v_num3 NUMBER;
 begin
   dbms_output.put_line('请输入第三个数字：');
   v_num3 :=&num3;
   if v_num1 < v_num3 then
      dbms_output.put_line('第一个字符比第三个字符小');
   end if;
   if v_url like '%baidu%' then
      dbms_output.put_line('网址之中包含baidu单词');
   end if;
 end;

5、case语句

case语句语法为：

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

例如：使用case语句判断数值

 declare
    v_choose NUMBER:=1;
 BEGIN
    case v_choose 
     when 0 then
          dbms_output.put_line('选择的是0');
     when 1 then
          dbms_output.put_line('选择的是1');
     else
          dbms_output.put_line('没有选项满足');
    end case;
 END;

6、PL/SQL的循环语句

在PL/SQL中有三种循环结构
1).loop循环
loop循环语法为：

loop
　语句块；
    exit when 循环结束条件;
　循环语句修改;
end loop;

例如：输出1-99的数。

　　　　　　declare
　　　　　　　　step number := 1;
　　　　　　begin
　　　　　　　　loop
　　　　　　　　　　exit when step > 100;--当step>100退出循环
　　　　　　　　　　dbms_output.put_line(step || ' ');
　　　　　　　　　　step := step + 1;
　　　　　　　　end loop;
　　　　　　end;

2).while...loop循环
while...loop循环语法为：

while 循环结束条件 loop
            语句块；
　　　 循环语句修改;
end loop;

例如：循环输出1-100的数。

　　　　　　declare
　　　　　　　　step number := 1;
　　　　　　begin
　　　　　　　　while step <= 100 loop
　　　　　　　　　　dbms_output.put_line(step);
　　　　　　　　　　step := step + 1;
　　　　　　　　end loop;
　　　　　　end;

3).for语句循环
for语句循环语法为：

　for 循环变量 in n..N loop
　　　　　　语句块；
　　　　end loop;

例如：输出1-100之间的数

　　　　declare
　　　　begin
　　　　　　for i in 1.. 100 loop
　　　　　　dbms_output.put_line(i);
　　　　end loop;
　　　　end;

7、异常处理

PL/SQL程序运行时出现的错误称为异常。可以分为两类：
预定义异常： 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户自定义异常：用户可以声明自定义异常，自定义的异常通过 raise 语句手动引发
系统预定义异常

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

image.png

例如：当在程序中使用0作为除数进行运算时，将触发该异常。

DECLARE
v_test1 NUMBER := 100;
v_test2 NUMBER := 0;
v_test3 NUMBER := 0;
BEGIN
v_test3 := v_test1 / v_test2;
dbms_output.put_line(v_test3);
EXCEPTION
WHEN zero_divide THEN
dbms_output.put_line('0不能作为除数.');
END;
/

例如：当程序中的对象还没有先进行对象初始化的操作，就直接为对象的属性赋值，将触发该异常。

DECLARE
v_test test_type;
BEGIN
v_test.v_name := 'test';
EXCEPTION
WHEN access_into_null THEN
dbms_output.put_line('首先初始化对象v_test');
END;
/

例外函数1： SQLCODE，SQLERRM 在PL/SQL块中出现Oracle错误时,通过使用例外函数可以取得错误号以及相关的错误消息。
SQLCODE 返回Oracle错误号。
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;

备注:others可以捕获所有异常，如果使了其它具体异常则others子句必须放在这些异常处理的最后。

例外函数2：RAISE_APPLICATION_ERROR在存储过程,函数和包中使用RAISE_APPLICATION_ERROR可以自定义错误号和消息。
raise_application_error：用于自定义错误消息(用于程序段中)

语法：
raise_application_error(error_number,message[,{TRUE | FALSE}]);
error_number : 错误号,范围是: -20000 ~ -20999之间的负整数;
message : 错误消息,长度不能超过2048字节;
第三个可靠选参数,如果TRUE,该错误会被放在先前错误堆栈中;如果FALSE(默认),则替换先前所有错误.
例如：

CREATE OR REPLACE PROCEDURE raise_comm
(
eno NUMBER,
commission NUMBER
) IS
v_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO v_comm FROM emp WHERE emp = eno;
IF v_comm IS NULL THEN
raise_application_error(-20001, '该员工无补助.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('该雇员不存在.');
END;
/

用户自定义异常
自定义异常使用预定义异常和非预定义异常，程序可以捕获Oracle错误，并且在出现Oracle错误时自动触发对应的异常。但是在实际应用中，可能还会 遇到其它错误，这时可以为特定的情况自定义异常，不过需要显示触发该异常：通过定义异常，然后关联异常和错误，显示触发异常，最后在EXCEPTION 中处理该异常。
自定义异常的处理步骤：
定义异常处理：异常名 exception ;
触发异常处理：raise 异常名
例如：

--自定义异常  
declare  
        e_wage EXCEPTION;  
        v_wage teachers.wage%type;  
    begin  
            v_wage:=&wage;  
            insert into teachers values (10111,'王彤','教授','01-9月-1990',1000,v_wage,101);  
            if v_wage <0 then   
             raise e_wage;  
            end if;  
            exception   
                when e_wage then  
                dbms_output.put_line('教师工资不能为负');  
                rollback;  
                when others then  
                    dbms_output.put_line('未知异常');  
    end;  
/