四、PL/SQL语言

PL/SQL是一种块结构的语言，这意味着PL/SQL程序被划分和编写代码的逻辑块。每块由三个子部分组成

4.1语法格式

DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
    <exception handling>
END;

• DECLARE 声明部分

此部分开头使用关键字DECLARE。它是一个可选的部分，并限定在该程序中使用的所有变量，游标，子程序，和其他元素。

• BEGIN执行命令块

此部分是封闭关键字BEGIN和END，这是一个强制性的部分。它由程序的可执行文件的PL/SQL语句。它应具有至少一个可执行的代码行，这可能仅仅是一个空命令，以指示什么都不执行。

• 异常处理部分

此部分开头使用关键字EXCEPTION。此部分又是可选的，含有异常，在程序处理错误中。

• 示例

DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
END;

4.2变量

普通数据类型

• char
• varchar2
• date
• number
• boolen
• long

特殊变量类型

• 引用型变量
• 记录型变量

变量声明方式

变量名 变量类型（变量长度） 如v_name varchar2(20);

变量赋值方式

直接赋值

• 直接赋值 如 v_name := '张三'

语句赋值

• 使用select... into ...赋值

示例

declare 
  -- 姓名
  v_name VARCHAR2(20) := '张三';
  -- 薪水
  v_sal number;
  v_addr VARCHAR2(200);
begin
  v_sal := 5000;
  select '河北省保定' into v_addr from dual;
  -- 打印
  Dbms_Output.put_line('姓名：'||v_name||',薪水：'||v_sal||',地址：'||v_addr);
  
end;

引用类型变量

• 类型长度取决于表中字段的类型和长度
  • 表明.列名%TYPE指定变量的类型和长度，例如：v_name emp.ename%TYPE;

declare 
  -- 姓名
  v_name emp.ename%TYPE;
  -- 薪水
  v_sal emp.sal%TYPE;
begin
  
  select ename,sal into v_name, v_sal from emp where empno = 7389;
  
  Dbms_Output.put_line('姓名：'||v_name||',薪水：'||v_sal);
  
end;

记录型变量

• 用来接收一整行记录，相当于java的一个对象
• 语法
  • 变量名 表明%ROWTYPE, 例如：v_emp emp%rowtype

4.3流程控制

4.3.1 条件

• 语法

BEGIN
    IF 条件一 THEN 执行一
    elsif 条件而 THEN 执行二
    else 执行三
    END IF;
END;

• 示例

declare 
  -- 声明变量接收数据数量
  v_count number;
begin
  
  select count(*) into v_count from cust_info;
  
  if v_count>10 then
    dbms_output.put_line('表中记录数超过了10条：'||v_count);
    
    elsif v_count>=10 then
    dbms_output.put_line('表中记录数10--20：'||v_count);
    
    else 
      dbms_output.put_line('表中记录数不到10条：'||v_count);
    
  end if;
  
end;

4.3.2循环

loop语法一

BEGIN
    LOOP
        EXIT WHEN 退出循环条件
    END LOOP
END;
    

示例

-- Created on 2020/10/1 星期四 by ADMINISTRATOR 
declare 
  -- 声明循环变量
  v_num number := 1;
begin
  
  loop
          
    exit when v_num>10;
    
    dbms_output.put_line(v_num);
    
    v_num := v_num + 1;
  
  end loop;
  -- Test statements here
  
end;

loop语法二

whhile total <=25000 loop
......
total :=total+salary
end loop

loop语法三

fro i in 1...100 loop
语句；
end loop

4.4游标

• 用于临时存储查询返回的多行数据，可以通过游标遍历逐行访问处理该结果集的数据
• 使用方式： 声明->打开->读取->关闭

4.4.1语法

• 游标声明

  • CURSOR 游标名[(参数列表)] IS查询语句；

• 游标打开

  • OPEN 游标名；

• 游标取值

  • FETCH 游标名 INTO 变量列表；

• 游标关闭

  • CLOSE 游标名；

• 游标属性

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔	最近FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔	与%FOUND取值相反
%ISOPEN	布尔	游标已经打开时为真，否则为假

%NOTFOUND是在游标中找不到元素时候返回true，通常用来判断退出循环

• 示例

declare 
  -- 声明游标
  CURSOR V_CUST IS SELECT CUST_NAME,ID_NO FROM CUST_INFO;
  
  -- 声明变量接收游标中的元素
  v_name cust_info.cust_name%Type;
  v_no cust_info.id_no%type;
  
  
begin
  
  --打开游标
  open V_CUST;
       
       -- 遍历游标
       loop
         
       --获取游标中的数据
       FETCH V_CUST INTO v_name,v_no;
          
       -- 退出条件
       exit when V_CUST%notfound;
       
       -- 输出
       dbms_output.put_line('姓名：'||v_name||'----证件号：'||v_no);
       
       end loop;
  
  --关闭游标
  close V_CUST;
  
end;

4.4.2带参游标

declare 
  -- 声明游标
  CURSOR V_CUST(V_CUST_TYPE CUST_INFO.CUST_TYPE%TYPE) IS SELECT CUST_NAME,ID_NO FROM CUST_INFO WHERE CUST_TYPE = V_CUST_TYPE;
  
  -- 声明变量接收游标中的元素
  v_name cust_info.cust_name%Type;
  v_no cust_info.id_no%type;
  
  
begin
  
  --打开游标
  open V_CUST(2);
       
       -- 遍历游标
       loop
         
       --获取游标中的数据
       FETCH V_CUST INTO v_name,v_no;
          
       -- 退出条件
       exit when V_CUST%notfound;
       
       -- 输出
       dbms_output.put_line('姓名：'||v_name||'----证件号：'||v_no);
       
       end loop;
  
  --关闭游标
  close V_CUST;
  
end;

4.4.3 exception

系统异常

• no_date_fount 没有找到数据

• too_many_rows select ... into 匹配多行

• zero_divide 被零除

• value_error 算术或转换错误

• timeout_on_resource 等待资源时候发生超时

• 实例

declare
  num number;
begin
  num :=1/0;  
exception
  when zero_divide then
    dbms_output.put_line('被零除');
end;

自定义异常

declare
  perc enm%rowtype;
  cursor c1 is
    select * from emp where deptno = 50;
  --定义异常
  no_date exception;
begin
  open c1;
  loop
    fetch c1
      into prec;
    --抛出异常
    if c1%notfound then
      raise no_date;
    end if;
  end loop;
  close c1;
exception
  when no_data then
    dbms_output.put_line('没有员工');
  when others then
    dbms_output.put_line('其他异常');
end;