Oracle 方言 PL/SQL 编程

╰+攻爆jí腚メ 2023-06-06 13:29 8阅读 0赞

**目录**

PL/sql 概述

PL/sql 变量定义与使用

PL/sql 流程控制

select . into . 查询并赋值数据

PL/sql 异常处理

raise\_application\_error 抛出异常

--------------------

[~~~ 准备员工表与部门表测试数据][Link 1]

# PL/sql 概述 #

1、sql 是一种标准的数据库访问语言，但无法编程，PL/SQL 是 Oracle 公司开发的"方言"，允许编程，是对 SQL 的一种补充。在存储过程、游标、触发器等等中都有用到 plsql。

2、PL/SQL 的主要目的是对数据库进行操作，因此在 PL/SQL 块中可以包含 select 语句，DML 语句，还可以包含 DCL 语句，但不能包含 DDL 语句。通过 SQL 语句及流程控制，可以编写复杂的 PL/SQL 块，对数据库进行复杂的访问，PL/SQL 一般是在应用程序中调用。

3、PL/SQL 对大小写不敏感，语句结构如下：

<table> 
 <tbody> 
  <tr> 
   <td> <p><span style="color:#f33b45;">declare </span></p> <p><span style="color:#f33b45;">变量声明部分 </span></p> <p><span style="color:#f33b45;">begin </span></p> <p><span style="color:#f33b45;">执行部分 </span></p> <p><span style="color:#f33b45;">exception </span></p> <p><span style="color:#f33b45;">异常处理部分 </span></p> <p><span style="color:#f33b45;">End （--declare 和 exception 部分是可选的）</span></p> </td> 
  </tr> 
 </tbody> 
</table>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

# PL/sql 变量定义与使用 #

1、声明变量必须指定变量的数据类型（可以是[ oracle 的任意数据类型][oracle]）。

2、变量可以在声明时进行初始化。

3、一条语句只能定义一个变量。

4、PL/sql 中变量的定义有以下方式：

<table> 
 <thead> 
  <tr> 
   <th style="width:320px;">格式</th> 
   <th style="width:583px;">举例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:320px;">变量名 <span style="color:#f33b45;">变量类型</span> [约束]</td> 
   <td style="width:583px;"> <p>v varchar2(8); -- 定义 varchar2 类型的变量 v</p> <p>endTime date; -- 定义日期类型的变量 endTime</p> </td> 
  </tr> 
  <tr> 
   <td style="width:320px;">变量名 <span style="color:#f33b45;">变量类型</span> [约束] default 默认值</td> 
   <td style="width:583px;">j number default 100; -- 定义 number 类型的变量 j，默认值为 100</td> 
  </tr> 
  <tr> 
   <td style="width:320px;">变量名 变量类型 [约束] [:=初始值]</td> 
   <td style="width:583px;">i number := 99;&nbsp;&nbsp;-- 定义number 类型的变量 i，赋值为 99。<span style="color:#f33b45;">PL/SQL的赋值必须是 := 符号，用于防止和 where、having 等条件语句中的 "=" 符号混淆。</span></td> 
  </tr> 
  <tr> 
   <td style="width:320px;">变量名 表名%rowtype&nbsp; [约束] default 默认值</td> 
   <td style="width:583px;">ename_var emp%rowtype; --将 emp 表全部字段的数据类型赋给 ename_var，取的时候 .字段名</td> 
  </tr> 
  <tr> 
   <td style="width:320px;">变量名 表名.列%type [约束] [:=初始值]</td> 
   <td style="width:583px;">sal_var emp.sal%type; --变量 sal_var 的类型与 emp 表 sal 字段的类型一致。</td> 
  </tr> 
  <tr> 
   <td style="width:320px;">变量名 另一变量%type [约束] [:=初始值]</td> 
   <td style="width:583px;">后三种方式示例可以参考：<a rel="nofollow">select . into . 查询并赋值数据</a></td> 
  </tr> 
 </tbody> 
</table>

--两个最简单的 plsql 
    begin
    --dbms_output.put_line 是内置的对象及其方法，用于打印输出，相当于 Java 的 System.out.println()
    dbms_output.put_line('你好 PL/SQL'); --输出 "你好 PL/SQL"
    end;
    
    declare
       i number :=99;  -- 定义变量 i，赋值为 99
       j number default 100; -- 定义变量 j，默认值为 100
    begin
       i:=i+10; --plsql 中的等于比较特别不是传统的 '='，而是 ':='
       j:=j*10;
       dbms_output.put_line('i='||i); --输出 i=109
       dbms_output.put_line('j='||j); --输出 j=1000
    end;

![20200315090901668.gif][]

# PL/sql 流程控制 #

<table> 
 <thead> 
  <tr> 
   <th>流程类型</th> 
   <th>格式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>if then</td> 
   <td>if 条件 then ... end if;</td> 
  </tr> 
  <tr> 
   <td>if then</td> 
   <td>if 条件 then ... else ... end if;</td> 
  </tr> 
  <tr> 
   <td>if then</td> 
   <td>if 条件 then ... elsif 条件 then ... end if;</td> 
  </tr> 
  <tr> 
   <td>loop</td> 
   <td>loop 循环体 exit [when 退出条件]; end loop;</td> 
  </tr> 
  <tr> 
   <td>while</td> 
   <td>while 循环条件 loop 循环体 end loop;</td> 
  </tr> 
 </tbody> 
</table>

>  for 循环变量 in \[reverse\] 起始值 .. 终止值 loop  
>     循环体  
>  end loop;

**if then 条件语句**

> 1）elsif 不要写错、2）end if 结尾的 ";" 号不能少\\、3）plsql 中 '=' 是等于号，做逻辑运算；':=' 是赋值号，用于给参数赋值。

declare
        user_age number default 65;
        user_name varchar2(16) := '张无忌';
    begin
       if user_age > 40 and user_age <= 60 then
           dbms_output.put_line('中年');
       elsif user_age > 20 and user_age <=40 then
           dbms_output.put_line('青年'); --会输出
       else 
           dbms_output.put_line('少年');
       end if;
       if user_name = '无忌' or instr(user_name,'无忌') >0 then
        dbms_output.put_line('武当张翠山之子'); --会输出
       else
        dbms_output.put_line(user_name);
       end if;
    end;

**loop 循环**求 10 的阶乘

1、循环体中必须包含一条 exit 语句，否则将无限循环。

declare
      i number := 1;
      total number := 1;
    begin
      --求 10 的阶乘
      loop
        total := total * i;
        i := i+1;
        if i > 10 then
          exit; --如果 i 大于 10，则推出 loop 循环
        end if;
      end loop;
      dbms_output.put_line('10! = '||total);--输出 10! = 3628800
    
      --打印 10，9，8，7，6，5，4，3，2，1
      loop 
        i := i-1;
        exit when i <= 0; -- 如果 i 小于 0 ，则推出 loop 循环
        dbms_output.put_line(i); --依次输出 10 9 8 7 6 5 4 3 2 1
      end loop;
    end;

**while 循环求 2 的 10 次方**

1、while 是带条件的循环，包含有 loop……end loop 语句

declare
     i number := 1;
     total number := 1;
    begin
      while i <= 10 loop
        total := total * 2;
        i := i +1;
      end loop;
      dbms_output.put_line('2的10次方 = '||total); -- 输出：2的10次方 = 1024
    end;

**for 循环**

1、for 循环中的循环次数必须是已知的，循环计数变量无需事先声明，可将循环计数器作为常量引用。

2、for 循环语句体中，不能给计数器变量赋值。

begin
    --循环变量（i）不用显示声明，作用范围就是在 for 中.
     for i in 1..10 loop
       dbms_output.put_line(i);--输出 1,2,3,4,5,6,7,8,9,10
     end loop;
    
     --起始值必须小于结束值，如果想反序，可以使用关键字 reverse 表示颠倒，从终止值往起始值
     for i in reverse 1..10 loop
       dbms_output.put_line(i);--输出 10,9,8,7,6,5,4,3,2,1
     end loop;
    end;
    
    --输出/打印所有员工的姓名与薪水。使用普通游标 加 for 循环遍历
    declare 
    cursor vrows is select * from emp;--声明游标
    begin
      for vrow in vrows loop --for 循环
        dbms_output.put_line('姓名：'||vrow.ename||' 薪水：'||vrow.sal);
      end loop;  
    end;

# select . into . 查询并赋值数据 #

1、PL/SQL 块中需要通过 select 语句从数据库查询数据并处理，此时格式如下:

<table> 
 <tbody> 
  <tr> 
   <td> <p><span style="color:#f33b45;">select </span>列1,列2... <span style="color:#f33b45;">into </span>变量1,变量2 from 表 where 条件 ...... ; -- 这样就把查询到的<span style="color:#f33b45;"> 列1，列2... 的值赋给了 变量1，变量2..</span>.。</p> <p><span style="color:#f33b45;">注意事项：select 查询的结果超过1条会报错，没有查询到结果也会报错，只能是1条时才能正常赋值。</span></p> </td> 
  </tr> 
 </tbody> 
</table>

select * from emp;--查收所有员工
     
    --查询所有人里面 薪水最高、最低的人
    declare
    max_sal_var emp.sal%type; -- 变量名 表名.列%type
    min_sal_var max_sal_var%type; -- 变量名 另一变量%type
    ename_var emp%rowtype; -- 变量名 表名%rowtype。相当于把 emp 所有列的类型都赋给了 ename_var，取的时候 .字段即可
    begin
      select sal,ename into max_sal_var,ename_var.ename from emp where sal = (select max(sal) from emp);
      select sal,ename into min_sal_var,ename_var.ename from emp where sal = (select min(sal) from emp);
      dbms_output.put_line('薪水最高的人：' || ename_var.ename || ' = ' || max_sal_var);
      dbms_output.put_line('薪水最低的人：' || ename_var.ename || ' = ' || min_sal_var);
      
      select * into ename_var from emp t where t.ename ='SCOTT';
      dbms_output.put_line(ename_var.empno || ',' || ename_var.ename || ',' || ename_var.sal);
    end;

![20200315182504814.gif][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>可以参考：<a href="https://mp.csdn.net/console/editor/html/90729212" rel="nofollow">Oracle 存储过程、存储函数 与 JDBC 调用</a></p> <p><a href="https://wangmaoxiong.blog.csdn.net/article/details/102514681#PL%2Fsql%20%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE" rel="nofollow">select . into . 查询并赋值数据</a>只能对单个结果进行处理，如果 select 查询的结果是多条，则可以借助<a href="https://wangmaoxiong.blog.csdn.net/article/details/90702872#curser%20%E6%B8%B8%E6%A0%87" rel="nofollow">游标进行遍历</a>。</p> </td> 
  </tr> 
 </tbody> 
</table>

# PL/sql 异常处理 #

1、Oracle 和 Java 的异常处理机制一样，发生异常之后如果自己捕获处理了，则程序继续往后走，否则直接返回错误，不再继续往后执行，如果是 Java 程序调用，则错误信息会返回给调用者，java 程序也会跟着抛异常，打印即可看到此异常信息。

2、异常的判断语法（异常如果没有捕获处理，则会报错。）:

<table> 
 <tbody> 
  <tr> 
   <td>exception<br> &nbsp;when 异常1 or 异常2 then<br> &nbsp;&nbsp;&nbsp;&nbsp; 语句..;<br> &nbsp;when 异常3 or 异常4 then<br> &nbsp;&nbsp;&nbsp;&nbsp; 语句 ...;<br> &nbsp;when others then<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 语句 ...;<br> end</td> 
  </tr> 
 </tbody> 
</table>

--查询指定部门中工资最高的员工信息
    declare
    empno_param number default 200;--待查询的部门 id
    emp_row emp%rowtype; --接收查询到数据
    begin
      select t.empno,t.ename,t.sal into emp_row.empno,emp_row.ename,emp_row.sal from emp t where t.sal = (select max(sal) from emp where deptno = empno_param);
      dbms_output.put_line('empno=' || emp_row.empno || ',ename=' || emp_row.ename || ',sal=' || emp_row.sal);
    exception
      when TOO_MANY_ROWS then -- SELECT INTO 查询返回多行时触发异常
        dbms_output.put_line('501:too_many_rows');
      when NO_DATA_FOUND then -- ELECT INTO 查询结果为 null（空）时引发异常
        dbms_output.put_line('502:no_data_found');
      when others then -- 对其它异常统一处理
        dbms_output.put_line('503:server error');
    end;

![20200316200119970.gif][]

<table> 
 <thead> 
  <tr> 
   <th>PL/sql预定义异常</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NO_DATA_FOUND</td> 
   <td>在使用SELECT INTO 结构，并且语句返回NULL值的时候；访问嵌套表中已经删除的表或者是访问INDEX BY表（联合数组）中的未初始化元素就会出现该异常</td> 
  </tr> 
  <tr> 
   <td>TOO_MANY_ROWS</td> 
   <td>常见错误，在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行，而比较运算符为相等的时候也会引发该异常。</td> 
  </tr> 
  <tr> 
   <td>ZERO_DIVIDE</td> 
   <td>将某个数字除以0的时候，会发生该异常</td> 
  </tr> 
  <tr> 
   <td>ACCESS_INTO_NULL</td> 
   <td>试图访问未初始化对象的时候出现</td> 
  </tr> 
  <tr> 
   <td>CASE_NOT_FOUND</td> 
   <td>如果定义了一个没有ELSE子句的CASE语句，而且没有CASE语句满足运行时条件时出现该异常</td> 
  </tr> 
  <tr> 
   <td>COLLECTION_IS_NULL</td> 
   <td>当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候，会出现该异常</td> 
  </tr> 
  <tr> 
   <td>CURSOR_ALREADY_OPEN</td> 
   <td>游标已经被OPEN，如果再次尝试打开该游标的时候，会出现该异常</td> 
  </tr> 
  <tr> 
   <td>DUP_VAL_ON_INDEX</td> 
   <td>如果插入一列被唯一索引约束的重复值的时候，就会引发该异常（该值被INDEX认定为冲突的）</td> 
  </tr> 
  <tr> 
   <td>INVALID_CURSOR</td> 
   <td>不允许的游标操作，比如关闭一个已经被关闭的游标，就会引发</td> 
  </tr> 
  <tr> 
   <td>INVALID_NUMBER</td> 
   <td>给数字值赋非数字值的时候，该异常就会发生，这个异常也会发生在批读取时候LIMIT子句返回非正数的时候</td> 
  </tr> 
  <tr> 
   <td>LOGIN_DENIED</td> 
   <td>程序中，使用错误的用户名和密码登录的时候，就会抛出这个异常</td> 
  </tr> 
  <tr> 
   <td>NOT_LOGGED_ON</td> 
   <td>当程序发出数据库调用，但是没有连接的时候（通常，在实际与会话断开连接之后）</td> 
  </tr> 
  <tr> 
   <td>PROGRAM_ERROR</td> 
   <td>当Oracle还未正式捕获的错误发生时常会发生，这是因为数据库大量的Object功能而发生</td> 
  </tr> 
  <tr> 
   <td>ROWTYPE_MISMATCH</td> 
   <td>如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常</td> 
  </tr> 
  <tr> 
   <td>SELF_IS_NULL</td> 
   <td>调用一个对象类型非静态成员方法（其中没有初始化对象类型实例）的时候发生该异常</td> 
  </tr> 
  <tr> 
   <td>STORAGE_ERROR</td> 
   <td>当内存不够分配SGA的足够配额或者是被破坏的时候，引发该异常</td> 
  </tr> 
  <tr> 
   <td>SUBSCRIPT_BEYOND_COUNT</td> 
   <td>当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候，发生该异常（类似于java的ArrayIndexOutOfBoundsException</td> 
  </tr> 
  <tr> 
   <td>SUBSCRIPT_OUTSIDE_LIMIT</td> 
   <td>使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发</td> 
  </tr> 
  <tr> 
   <td>SYS_INVALID_ROWID</td> 
   <td>将无效的字符串转化为ROWID的时候引发</td> 
  </tr> 
  <tr> 
   <td>TIMEOUT_ON_RESOURCE</td> 
   <td>当数据库不能安全锁定资源的时候引发</td> 
  </tr> 
  <tr> 
   <td>USERENV_COMMITSCN_ERROR</td> 
   <td>只可使用函数USERENV('COMMITSCN')作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数</td> 
  </tr> 
  <tr> 
   <td>VALUE_ERROR</td> 
   <td>将一个变量赋给另一个不能容纳该变量的变量时引发</td> 
  </tr> 
 </tbody> 
</table>

# raise\_application\_error 抛出异常 #

1、raise\_application\_error(引发应用程序错误) 是 Oracle 内置的函数，和平时使用的 dbms\_output.put\_line 打印日志函数类似。

2、上面的[ PL/sql 异常处理 ][PL_sql _]是被动捕获 pl/sql 中的异常，然后进行处理，有的时候却需要主动往外抛出异常，返回错误信息，此时就可以使用 raise\_application\_error 函数抛出自定义异常。

3、格式：raise\_application\_error( error\_number in number, error\_msg in varchar2)

<table> 
 <tbody> 
  <tr> 
   <td> <p><span style="color:#f33b45;">error_number </span>：错误码，可以是 -20000 到 -20999 之间的任意值，这样就不会与 ORACLE 内置的错误代码冲突</p> <p><span style="color:#f33b45;">error_msg </span>：错误信息，内容长度不能超过 2k，否则超过部分被舍弃</p> </td> 
  </tr> 
 </tbody> 
</table>

4、raise\_application\_error 可以用在如触发器、游标、存储过程等任何 pl/sql 块中，比如 java 程序调用时 pl/sql 报错，则相应的 java 中也会抛出异常，打印即可看到此错误信息。

--创建触发器，往 emp 表插入数据时，如果入职日期大于当前日期，则抛出异常.
    create or replace trigger trig_insert_emp_check
    before insert on emp
    for each row
    declare
    begin
        --当 hiredate 字段没有值时，也不会影响，不会进入 If 条件
        if :new.hiredate > sysdate then
           raise_application_error(-20001,'员工入职日期不能大于当前日期');
        end if;  
    end;

![20200904152046143.gif][]

[Link 1]: https://wangmaoxiong.blog.csdn.net/article/details/90581383#%E5%87%86%E5%A4%87%E5%91%98%E5%B7%A5%E8%A1%A8%E4%B8%8E%E9%83%A8%E9%97%A8%E8%A1%A8%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20230601/7d369c8481b44e5891b86f4d83d3360a.png
[oracle]: https://wangmaoxiong.blog.csdn.net/article/details/90690400#Oracle%20%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B
[20200315090901668.gif]: /images/20230601/cf5b064a265047f0a4e15291a0395871.png
[20200315182504814.gif]: /images/20230601/112efdbe9add4b9da5a83a6218fc2642.png
[20200316200119970.gif]: /images/20230601/2f2c0c573e7242e7bafe8c14f1ea473f.png
[PL_sql _]: https://wangmaoxiong.blog.csdn.net/article/details/102514681#PL%2Fsql%20%E5%BC%82%E5%B8%B8%E5%A4%84%E7%90%86
[20200904152046143.gif]: /images/20230601/89bc40adeb2c4595bd827a224496626d.png