Oracle 方言 PL/SQL 编程、异常、自治事务(pragma autonomous_transaction)

目录PL/sql 基本概念PL/sql 变量PL/sql 块中流程控制PL/sql 查询数据PL/sql 异常处理PL/sql 基本概念1、sql 是一种标准的数据库访问语言，但无法编程，PL/SQL 是 Oracle 公司开发的"方言"，允许编程，是对 SQL 的一种补充。在存储过程、游标、触发器等等中都有用到 plsql。2、PL/SQL 的主要目的是对数据库进......

蚩尤后裔

4259人浏览 · 2020-04-23 10:53:46

蚩尤后裔 · 2020-04-23 10:53:46 发布

select . into . 查询并赋值数据

execute immediate 执行 DDL 语句

sql%rowcount 获取影响的条数

自治事务(pragma autonomous_transaction)

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 对大小写不敏感，语句结构如下：

declare

变量声明部分

begin

执行部分

exception

异常处理部分

End

1、declare 和 exception 部分可选。

2、begin -> exception -> end 需要配套使用，但是一个块中可以有多套。

4、通常出脚本的时候，一个文件中可能很有各种语句，注意其中只要含有 plsql 程序，则 plsql 程序块的结尾必须单独起一行顶格使用 / 隔开，注意 / 左侧不能有空格，否则一起执行将报错报错。

declare
begin
  --打印日志输出
  dbms_output.put('新内容不换行');
  dbms_output.put_line('新内容换行1');
  dbms_output.put_line('新内容换行2');
end;

/ -- 单独一行，且左侧不能有空格

SQL 操作符与单双引号

1、SQL 操作符与单双引号。

-- SQL 语句中关键字也可以用在 if 中
declare
  v_version varchar2(16) := '2024ysbz';
  v_emp_no  number := 10;
begin
  if v_version like '%ysbz' and v_version in ('2024ysbz') and
     v_emp_no between 10 and 20 then
    dbms_output.put_line('1');--输出
  end if;
end;

PL/sql 变量定义与使用

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

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

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

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

格式	举例
变量名变量类型 [约束]	v varchar2(8); -- 定义 varchar2 类型的变量 v endTime date; -- 定义日期类型的变量 endTime
变量名变量类型 [约束] default 默认值	j number default 100; -- 定义 number 类型的变量 j，默认值为 100
变量名变量类型 [约束] [:=初始值]	i number := 99; -- 定义number 类型的变量 i，赋值为 99。PL/SQL的赋值必须是 := 符号，用于防止和 where、having 等条件语句中的 "=" 符号混淆。
变量名表名%rowtype [约束] default 默认值	ename_var emp%rowtype; --将 emp 表全部字段的数据类型赋给 ename_var，取的时候 .字段名
变量名表名.列%type [约束] [:=初始值]	sal_var emp.sal%type; --变量 sal_var 的类型与 emp 表 sal 字段的类型一致。
变量名另一变量%type [约束] [:=初始值]	后三种方式示例可以参考：select . into . 查询并赋值数据
1、plsql 中定义变量类型为 VARCHAR2 时，length 长度不能大于 32767。 2、PL/SQL 块中的变量可以定义为 LONG 类型，可存储可变长字符串，最大长度限制是 2GB。

--两个最简单的 plsql 
begin
--dbms_output.put_line 是内置的对象及其方法，用于打印输出，相当于 Java 的 System.out.println()
dbms_output.put_line('你好 PL/SQL'); --输出 "你好 PL/SQL"
end;

-- 如果需要多个 plsql 块一起执行，必须使用  / 隔开，否则一起执行会报错
/

declare
  i number := 99; -- 定义变量 i，赋值为 99
  j number default 100; -- 定义变量 j，默认值为 100
  --定义记录型变量：代表一行
  emp_rec emp%rowtype;
  pename  emp.ename%type;
  --引用型变量
  psal emp.sal%type;
begin
  i := i + 10; --plsql 中的等于比较特别不是传统的 '='，而是 ':='
  j := j * 10;
  dbms_output.put_line('i=' || i); --输出 i=109
  dbms_output.put_line('j=' || j); --输出 j=1000

  select * into emp_rec from emp where empno = 7839;
  dbms_output.put_line(emp_rec.ename || '的薪水是' || emp_rec.sal); -- KING的薪水是5000

  select ename, sal into pename, psal from emp where empno = 7839;
  dbms_output.put_line(pename || '的薪水是' || psal); -- KING的薪水是5000
end;

PL/sql 流程控制

流程类型	格式
if then	if 条件 then ... end if;
if then	if 条件 then ... else ... end if;
if then	if 条件 then ... elsif 条件 then ... end if;
loop	loop 循环体 exit [when 退出条件]; end loop;
while	while 循环条件 loop 循环体 end loop;
for	for 循环条件 loop ...... end loop; --可以使用 exit 提前退出循环
exit 与 exit when 可以在任意循环体中进行使用，用于退出/跳出循环，类似 Java 的 break。
continue 可以在任意循环体中进行使用，与 Java 一样，用于跳过当次循环中剩下的语句，执行下一次循环。

if then 条件语句

1）elsif 不要写错
2）end if 结尾的 ";" 号不能少\
3）plsql 中 '=' 是等于号，做逻辑运算；':=' 是赋值号，用于给参数赋值。
4）判断为空、为 null，需要使用 xx is null 或者 xx is not null。

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;

示例：Oracle MD5 函数提取摘要

loop 循环

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;

plsql示例1：嵌套表集合自定义 split 函数。

while 循环

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

declare
 i number := 1;
 total number := 1;
begin
  while i <= 100 loop
    total := total * 2;
    dbms_output.put_line('i=' || i);
    i := i +1;
    IF i > 10 then
      --while条件不满足时自动结束循环，也可以在内部达到某个条件时，提前结束循环
      dbms_output.put_line('退出循环');
      exit;
    end if;
  end loop;
  dbms_output.put_line('2的10次方 = '||total); -- 输出：2的10次方 = 1024
end;

for 循环

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

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

declare
begin
  --循环变量（i）不用显示声明，作用范围就是在 for 中.
  for i in 1 .. 10 loop
    dbms_output.put(i || '='); --输出 1,2,3,4,5,6,7,8,9,10
    --起始值必须小于结束值，如果想反序，可以使用关键字 reverse 表示颠倒，从终止值往起始值
    for j in reverse 1 .. 10 loop
      dbms_output.put(' ' || j); --输出 10,9,8,7,6,5,4,3,2,1
    end loop;
    dbms_output.put_line(''); --换行
  end loop;
end;

--输出/打印所有员工的姓名与薪水。使用普通游标 加 for 循环遍历
declare 
cursor vrows is select * from emp;--声明游标
v_i number := 1;
begin
  for vrow in vrows loop --for 循环
    dbms_output.put_line(v_i || '：' || '姓名：'||vrow.ename||' 薪水：'||vrow.sal);
    if v_i >= 1000 then 
      exit; --输出个数达到1000个时自动退出循环.
    end if;
    v_i := v_i + 1;
  end loop;  
end;

多重循环

1、有的时候单重循环无法满足需求，需要多重循环才行。

-- 查询部门信息及其下面的员工信息
declare
begin
  for dept in (SELECT DEPTNO, DNAME, LOC FROM dept order by dept.deptno) loop
    dbms_output.put_line('部门编号：' || dept.DEPTNO || '，名称：' || dept.dname);
    for emp in (SELECT T.*
                  FROM emp T
                 where t.deptno = dept.deptno
                 order by t.empno) loop
      if emp.sal < 1000 then
        --如果员工薪水小于1000，则直接跳过，进行下一轮循环
        continue;
      end if;
      dbms_output.put_line('  员工编号：' || emp.empno || '，姓名：' || emp.ename ||
                           '，薪水：' || emp.sal);
    end loop;
  end loop;
end;

2、也可以配合游标使用：20221122_辽宁22年日常动态维护版本数据去重。

3、sql/oracle/基础信息库/补充单位扩展信息.sql · 汪少棠/material - Gitee.com。

顺序结构-GOTO 与 NULL

1、在程序顺序结构中有两个特殊的语句。GOTO 和 NULL。

2、GOTO 语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示符(<<lableName>>)，在 PL/SQL 块中必须具有唯一的名称，标签后必须紧跟可执行语句或者 PL/SQL 块。

3、NULL 语句什么都不做，只是将控制权转到下一行语句。NULL 语句是可执行语句。NULI语句在 IF 或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地方。比如 GOTO 的目标地方不需要执行任何语句时。

DECLARE
  v_sal number := 8000;
BEGIN
  IF v_sal >= 10000 THEN
    GOTO lable1; -- 跳到lable1执行，并继续执行 lable2、lable3、lable4 后面的语句
  ELSif v_sal >= 5000 then
    GOTO lable2; -- 跳到lable2执行，并继续执行 lable3、lable4 后面的语句
  elsif v_sal >= 10000 then
    GOTO lable3; -- 跳到lable3执行，并继续执行 lable4 后面的语句
  else
    GOTO lable4; -- 跳到lable4执行，NULL语句什么都不做。
  END IF;
  <<lable1>>
  dbms_output.put_line('满足高输入.');
  <<lable2>>
  dbms_output.put_line('满足中等输入.');
  <<lable3>>
  dbms_output.put_line('满足低输入.');
  <<lable4>>
  NULL;
END;

-- 当在满足某个条件后，结束后续操作，直接跳过后面的操作，去到结尾就行。
-- 比如 v_sal=80000 时，输出 site1 -> 结束
-- 比如 v_sal=8000 时，输出 site2 -> 结束
DECLARE
  v_sal NUMBER := 80000;
BEGIN
  IF v_sal >= 10000 THEN
    dbms_output.put_line('site1');
    GOTO to_end;
  END IF;
  dbms_output.put_line('site2');
  <<to_end>>
  dbms_output.put_line('结束');
END;

select . into . 查询并赋值数据

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

select 列1,列2... into 变量1,变量2 from 表 where 条件 ...... ; -- 这样就把查询到的列1，列2... 的值赋给了变量1，变量2...。

2、注意事项：select 查询的结果超过1条会报错，没有查询到结果也会报错，只能是1条时才能正常赋值。此时推荐使用上面[多重循环]代替。

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) AND ROWNUM <= 1;
  select sal,ename into min_sal_var,ename_var.ename from emp where sal = (select min(sal) from emp) AND ROWNUM <= 1;
  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;

示例：sql/oracle/基础信息库/为人员信息导入临时表添加字段.sql · 汪少棠/material - Gitee.com。

可以参考：Oracle 存储过程、存储函数与 JDBC 调用

select . into . 查询并赋值数据只能对单个结果进行处理，如果 select 查询的结果是多条，则可以借助游标进行遍历。

PL/sql 异常捕获处理

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

2、异常如果没有捕获处理，则会报错，捕获了就不再继续报错，捕获了也可以继续抛出异常，使用 raise 关键字:

3、与Java不同的时，PLSQL 不需要 try-catch 包裹需要捕获异常的代码，而是直接在结尾处加上异常捕获即可，当前面发生异常时就会自动捕获。

exception
when 异常1 or 异常2 then
语句..;

raise; --继续抛出当前异常
when 异常3 or 异常4 then
语句 ...;

raise 异常4; --继续抛出指定的异常
when others then
语句 ...;
end

--查询指定部门中工资最高的员工信息
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;

示例2：unistr 将 Unicode 字符串解码。

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

PL/Sql 主动抛异常

1、raise_application_error(引发应用程序错误) 是 Oracle 内置的函数，和平时使用的 dbms_output.put_line 打印日志函数类似。

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

3、格式：raise_application_error( error_number in number, error_msg in varchar2)

error_number ：错误码，可以是 -20000 到 -20999 之间的任意值，这样就不会与 ORACLE 内置的错误代码冲突

error_msg ：错误信息，内容长度不能超过 2k，否则超过部分被舍弃。

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;

create or replace trigger trig_update_emp_230926
  before update on emp
  for each row
declare
begin
  if :new.sal is null then
    raise_application_error(-20001, '员工薪水不能设置为空.');
  end if;
exception
  --捕获异常
  when others then
    raise; --继续原样抛出异常 ORA-20001:员工薪水不能设置为空
end;

自定义异常

1、使用 exception 关键字定义。

create or replace trigger trig_update_emp_230927
  before update on emp
  for each row
declare
  sql_not_null exception; -- 自定义异常类型
begin
  if :new.sal is null then
    raise sql_not_null; -- 抛出自定义异常
  end if;
exception
  --捕获异常
  when sql_not_null then
    --继续抛出异常
    raise_application_error(-20001, '员工薪水不能设置为空.');
    -- 此时不要原样抛出自定义异常，否则会提示：ORA-06510: PL/SQL: 用户定义的异常错误未得到处理
    -- raise;
end;

SQL报错时继续执行

1、循环报错时继续执行，循环某些项报错时也不影响后续的操作。

declare
  v_test_resutl number;
begin
  for i in 1 .. 10 loop
    begin
      -- begin -> exception -> end 需要配套使用，可以有多套
      dbms_output.put_line(i);
      v_test_resutl := i / mod(i, 3);
    exception
      when others then
        dbms_output.put_line(' 循环 ' || i || ' 号异常.');
    end;
  end loop;
  v_test_resutl := 1 / 0;
exception
  when others then
    dbms_output.put_line('循环外部发生异常.');
end;

2、顺序操作时，其中某个步骤的发生异常时，不影响后续执行。

declare
  v_test_resutl number := 0;
begin
  --操作1
  v_test_resutl := 1;
  dbms_output.put_line(v_test_resutl);
  --操作2
  begin
    v_test_resutl := 2 / 0;
    dbms_output.put_line(v_test_resutl);
  exception -- begin -> exception -> end 需要配套使用，可以有多套;
    when others then
      dbms_output.put_line('操作2发生异常.');
  end;
  --操作3  
  v_test_resutl := 3;
  dbms_output.put_line(v_test_resutl);
end;

execute immediate 执行 DDL 语句

1、PL/Sql 中可以直接操作 DQL、DML、DCL 等语句，但是无法直接操作 DDL语句(数据定义语言)：包括 CREATE （创建）命令、 ALTER （修改）命令、 DROP （删除）命令等。

2、PL/Sql 中需要使用动态执行 SQL 的方式来执行 DDL 语句，格式：execute immediate 动态语句字符串' [INTO 变量列表] [USING 参数列表]；

a）如果动态语句是 SE LECT 语句，可以把查询的结果保存到 INTO 后面的变量中。

b）如果动态语句中存在参数， USING 为语句中的参数传值。
c）动态 SQL 中的参数格式是 [:参数名]，参数在运行时需要使用 USING 传值。

示例1：新建表及基本用法（当表不存在时新建表.sql）。

declare
    v_sql varchar2(2048);
    v_num number;
begin
    --- execute immediate 用法1：操作 DDL语句(数据定义语言)
    v_sql := 'create table DEPT20220331 (
        deptno NUMBER(2) not null,
        dname VARCHAR2(14),
        loc VARCHAR2(13))';
    execute immediate (v_sql);
    dbms_output.put_line('建表成功');

    --- execute immediate 用法2：立刻执行sql语句，并赋值给某个变量
    v_sql := 'select count(1) from EMP';
    execute immediate v_sql into v_num;
    dbms_output.put_line('员工总数：' || v_num);

    -- execute immediate 用法3：带参数的SQL，使用 :数字 作为占位符，从1开始.
    --:后面的参数名可以任意，不止是数字，汉字、字母等也可以，只要 USING 按顺序传参就行。
    v_sql := 'update emp t set t.ename=:1, t.comm=:2 where t.empno=:3';
    execute immediate v_sql using 'ZhangSan',600,7499;

end;

DECLARE
  sql_stmt  VARCHAR2(200); --动态SQL语句
  emp_id    NUMBER(4) := 7566;
  salary    NUMBER(7, 2);
  dept_id   NUMBER(2) := 22;
  dept_name VARCHAR2(14) := 'PERSONNEL';
  location  VARCHAR2(13) := 'DALLAS';
  emp_rec   emp%ROWTYPE;
BEGIN
  --无子句的execute immediate
  EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';

  ----using子句的execute immediate
  --3个参数分别标识为 [:1、:2、:3]，因此需要用 USING 关键字对三个参数分别赋值。
  sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
  EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
    
  ----into子句的execute immediate
  --对动态查询语句可以使用 INTO 子句把查询的结果保存到一个变量中，要求该结果只能是单行。
  sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
  EXECUTE IMMEDIATE sql_stmt  INTO emp_rec USING emp_id;
  
  ----returning into子句的execute immediate
  --insert、update、delete 语句都可以使用 RETURNING 子句把操作影响的行中的数据返回，
  --对 SQL 语句中存在 RETURNING 子句时，在动态执行时可以使用 RETURNING INTO 来接收。
  sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';
  EXECUTE IMMEDIATE sql_stmt USING emp_id  RETURNING INTO salary;
  EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;
END;

示例2：sql/oracle/基础信息库/为人员信息导入临时表添加字段.sql · 汪少棠/material - Gitee.com。

示例3：sql/oracle/基础信息库/06_自动新建查询多表的视图.sql · 汪少棠/material - Gitee.com。

sql%rowcount 获取影响的条数

1、sql%rowcount 可以记录 PL/SQL 中新增、更新、删除的行数，放在目标语句和 commit 之间。

2、sql%rowcount 会记录未被提交的最后一条 SQL 语句的影响的行数，如果想统计多个 sql 的合计影响行数，就必须在每个 sql 后面，用一个变量保存当前的 sql%rowcount。

--创建存储过程
create or replace procedure proc_modify_emp(comm_in         IN VARCHAR2,
                                            deptno_in       IN varchar2,
                                            execute_num_out OUT number) as
execute_num_p number := 0; --每次影响的条数
Begin
  --调整整个部门下员工的奖金
  update emp set comm = comm_in where deptno = deptno_in;
  --获取影响的条数
  execute_num_p := sql%rowcount;
  execute_num_out := execute_num_p;
  dbms_output.put_line('当前影响条数：' || execute_num_p || '，累计影响条数：' || execute_num_out);
  
  update emp set sal = sal + 200 where deptno = deptno_in and sal <= 2000;
  --获取总共影响的条数
  execute_num_p := sql%rowcount;
  execute_num_out := execute_num_out + execute_num_p;
  dbms_output.put_line('当前影响条数：' || execute_num_p || '，累计影响条数：' || execute_num_out);
  
  commit;
  --其它表依次类推
End;

--调用存储过程
declare
  execute_num_out number; --接收总共影响的条数
begin
  proc_modify_emp(1000, 20, execute_num_out);
  dbms_output.put_line('影响条数：' || execute_num_out);
end;

--输出日志：
--当前影响条数：5，累计影响条数：5
--当前影响条数：3，累计影响条数：8
--影响条数：8

自治事务(pragma autonomous_transaction)

1、Oracle '自治事务'(pragma autonomous_transaction) 是 PL/SQL 块中一个单独的事务，与调用或触发自己的事务之间互不干扰，自己 commit、rollback 不会影响其它事务，也不会被其它事务所影响。

因为自治事务与调用或触发自己的事务之间互不干扰，所以特别注意，主事务中未提交的数据，自治事务中是查找不到的。

比如 Java 程序业务层中一个事务管理的方法，先删除，后插入，插入的时候触发了 Oracle 的触发器，触发器中使用了自治事务时，触发器中仍然能查询到程序中删除的数据，因为此时主事务还未提交。而如果触发器中未使用自治事务，则按照先后执行顺序，触发器中无法查询到程序中删除的数据。

在自治事务中，commit 或者 rollback 只会提交或回滚当前自治事务中的 DML，不会影响到 Main 程序中的DML。

结束自治事务，必须发出一个完成的 COMMIT 或 ROLLBACK，或执行 DDL（隐含COMMIT）

2、常用场景：记录错误日志，自己可以 commit 插入数据，但又不影响业务数据。通常用于触发器、存储过程等中。

3. 语法：在 PL/SQL 块语句声明部分 declare 下面加入下列语句即可：pragma autonomous_transaction;

CREATE OR REPLACE TRIGGER tri_bas_agency_info_modify
  BEFORE INSERT OR UPDATE ON bas_agency_info
  FOR EACH ROW
DECLARE
  pragma autonomous_transaction; --自治事务,与调用或触发自己的事务互不影响
  -- 是否已经存在有效的相同单位(单位表中有效的单位只允许存在一个)
  -- 当表中已经存在时，则抛出异常，让其无法插入，而自己则往 bas_agency_error 表中插入错误日志，方便排查问题.
  is_exist number := 0; 
BEGIN
  -- :old 取不到值时表示是新增操作，此时检查表中是否已经存在目标单位了
  if :old.agency_id is null and :new.is_deleted != 1 then
    SELECT count(1)
      into is_exist
      FROM BAS_AGENCY_INFO t
     where t.mof_div_code = :new.mof_div_code
       and t.fiscal_year = :new.fiscal_year
       and t.agency_code = :new.agency_code
       and t.version = :new.version
       and t.is_deleted = 2
       and t.agency_id != :new.agency_id;
  end if;
  -- 如果库中已经存在目标单位了，则不允许重复新增有效单位，往 bas_agency_error 表中插入错误日志，方便排查问题.
  if is_exist > 0 then  
    insert into bas_agency_error (MOF_DIV_CODE, AGENCY_CODE, FISCAL_YEAR, VERSION, MSG, CREATE_TIME)
    values
      (:new.mof_div_code,
       :new.agency_code,
       :new.fiscal_year,
       :new.version,
       :new.mof_div_code || ' 区划已经存在单位 ''' || :new.agency_code || ' ' || :new.agency_name || '''，请勿重复新增！',
       sysdate);
      commit;
    -- 抛出异常，让业务数据无法无法正常插入，事务回滚。
   raise_application_error(-20003,  :new.mof_div_code || ' 区划已经存在单位 ''' ||    :new.agency_code || ' ' || :new.agency_name ||  '''，请勿重复新增！');
  end if;
END;