Oralce之PL/SQL编程(游标)

痛定思痛。 2021-06-26 16:06 344阅读 0赞

`PL/SQL(Procedural Language/SQL)`是`Oracle`在数据库中引入的一种过程化编程语言。

## `PL/SQL`块结构 ##

声明部分　　执行部分（必须的）　　异常处理部分

[declare]
    
    　　--声明部分，可选
    
    BEGIN
    
    　　--执行部分，必须
    　　
    [exception]
    
    　　--异常处理部分，可选
    
    END

单行注释：`--`

多行注释：`/*　　*/`

基本数据类型：

number　　varchar2　　char　　long　　nchar  nvarchar2　　date　　boolean

特殊数据类型：

**`%type`,声明一个与指定列名称相同的数据类型。**

declare var_job emp.job%type;

`record`:记录类型数据

type emp_type is record
    (
    var_ename varchar2(20),
    
    var_job varchar2(20),
    
    var_sal number
    );

`%rowtype`:结合了`%type`和`record`特性，可以根据数据表中的行结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据  
`rowVar_emp emp%rowtype`;定义能够存储`emp`表中一行数据的变量`rowVar_emp`

定义变量：`var_countryname varchar2(50):='中国';`

定义常量: `con_day constant integer:=365;`

## PL/SQL游标 ##

显示游标和隐式游标：

**显示游标**：

例子

1.声明一个检索`emp`表中雇员信息的游标，然后打开游标，并指定检索职务是“`MANAGER`"的雇员信息，接着使用 `fetch...into`语句和`while`循环

读取游标中的所有雇员信息，最后输出读取的雇员信息

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;
    /

**游标属性：**  
`%found`:如果SQL语句至少影响到一行数据，则该属性为true，否则为false

`%notfound`:与上面的功能相反

`%rowcount`:返回受影响的行数

`%isopen`:游标打开时，返回true,关闭时，false

**隐式游标**

在执行一个SQL 语句时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。

例子：

在`SCOTT`模式下，把`emp`表中销售员的工资上调20%，然后使用隐式游标sql的`%rowcount`属性输出上调  
工资的员工数量

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;
    /

**`for`语句循环游标：**

例子：

使用隐式游标和`for`语句检索出职务是销售员的雇员信息并输出

begin
    for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
    loop
    dbms_output.put_line('雇员编号:'||emp_record.empno);
    dbms_output.put_line('; 雇员名称：'||emp_record.ename);
    dbms_output.put_line('; 雇员工资：'||emp_record.sal);
    end loop;
    end;
    /

使用显示游标和for语句检索出部门编号是30的雇员信息并输出

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;
    /

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

## `PL/SQL`异常处理 ##

在`[exception]`代码块中

根据异常产生的机制和原理，可将Oracle系统异常分为以下两大类：

### 1.预定义异常 ###

定义在Oracle的核心PL/SQL库中，用户可以在自己的PL/SQL异常处理部分使用名称对其进行标识。对这种异常情况的处理，用户无须在程序中定义，它们由Oracle自动引发。

<table> 
 <thead> 
  <tr> 
   <th align="left">系统预定义异常</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">ZERO_DIVIDE</td> 
   <td align="left">除数为零时引发的异常</td> 
  </tr> 
  <tr> 
   <td align="left">ACCESS_INTO_NULL</td> 
   <td align="left">企图为某个未初始化对象的属性赋值</td> 
  </tr> 
  <tr> 
   <td align="left">COLLECTION_IS_NULL</td> 
   <td align="left">企图使用未初始化的集合元素</td> 
  </tr> 
  <tr> 
   <td align="left">CURSOR_ALREADY_OPEN</td> 
   <td align="left">企图再次打开一个已经打开过的游标，但在重打开之前，游标未关闭</td> 
  </tr> 
  <tr> 
   <td align="left">INVALID_CURSOR</td> 
   <td align="left">执行一个非法的的游标操作，如，关闭一个未打开的游标</td> 
  </tr> 
  <tr> 
   <td align="left">INVALID_NUMBER</td> 
   <td align="left">企图将一个字符串转换成一个无效的数字而失败</td> 
  </tr> 
  <tr> 
   <td align="left">LOGIN_DENIED</td> 
   <td align="left">企图使用无效的用户名或密码连接数据库</td> 
  </tr> 
  <tr> 
   <td align="left">NO_DATA_FOUND</td> 
   <td align="left">SELECT INTO 语句没有返回数据</td> 
  </tr> 
  <tr> 
   <td align="left">ROWTYPE_MISMATCH</td> 
   <td align="left">主游标变量与PL/SQL游标变量的返回类型不兼容</td> 
  </tr> 
  <tr> 
   <td align="left">SELF_IS_NULL</td> 
   <td align="left">使用对象类型时，使用空对象调用其方法</td> 
  </tr> 
  <tr> 
   <td align="left">SUBSCRIPT_BEYOND_COUNT</td> 
   <td align="left">元素下表超过嵌套表或VARRY中的元素</td> 
  </tr> 
  <tr> 
   <td align="left">SUBSCRIPT_OUTSIDE_LIMIT</td> 
   <td align="left">企图使用非法索引号引用嵌套表或VARRY中的元素</td> 
  </tr> 
  <tr> 
   <td align="left">SYS_INVALID_ROWID</td> 
   <td align="left">字符串向ROWID转换时的错误，因为该字符串不是一个有效的ROWID值</td> 
  </tr> 
  <tr> 
   <td align="left">TIMEOUT_ON_RESOURCE</td> 
   <td align="left">Oracle在等待资源时超时</td> 
  </tr> 
  <tr> 
   <td align="left">TOO_MANY_ROWS</td> 
   <td align="left">执行SELECT INTO 语句时，结果集超过一行引发的异常</td> 
  </tr> 
 </tbody> 
</table>

### 2.自定义异常 ###

有两种

**A-错误编码异常**

定义错误编码异常编号为“-00001”的异常变量，然后想dept表中插入一条能够“违反唯一性约束条件”的记录，

最后在`exception`代码中输出异常提示信息

declare
    primary_iterant exception;
    pragma exception_init(primary_iterant,-00001);--关联错误号和异常变量名
    begin
    insert into dept values(10,'软件开发部','深圳');
    exception
    when primary_iterant then
    dbms_output.put_line('主键不允许重复!');
    end;
    /

**B-业务逻辑异常**

自定义一个异常变量，在向`dept`表中插入数据时，若判断`loc`字段为`null`，则使用`raise`语句引发异常，并将程序执行流程转入到`exception`部分进行处理

declare
    null_exception exception;
    dept_row dept%rowtype;
    begin
    dept_row.deptno:=66;
    dept_row.dname:='公共部';
    insert into dept
    values(dept_row.deptno,dept_row.dname,dept_row.loc);
    if dept_row.loc is null then
    raise null_exception;
    end if;
    exception
    when null_exception then
    dbms_output.put_line('loc字段的值不许为null');
    rollback;
    end;
    /