pl/sql 实例精解 03

1. 在Pl/sql 中使用 sql

/*
 * 一个 pl/sql 语句块, 只是一个容器, 是表明一个整体的容器, 容器里可以放置多个sql语句
*/

declare
    v_zip    zipcode.zip%type;
    v_user    zipcode.created_by%type;
    v_date    zipcode.created_date%type;
begin
    -- statement 1
    select 43438, user, sysdate
      into v_zip, v_user, v_date
      from dual;

    -- statement 2
    insert into zipcode(zip, created_by, careate_date, modified_by,
                        modified_date)
    values(v_zip, v_user, v_date, v_user,
            v_date);
end;
/
show errors;

2. 在 pl/sql 中使用 commit, rollback, savepoint

/*
 * ROLLBACK [WORK] to SAVEPOINT name;
*/

begin
    insert into student(student_id, last_name, zip, reistration_date,
                        created_by, created_date, modified_by, modified_date)
    values(student_id_seql.nextval, 'Tashi', 10015, '01-一月-99',
            'Student', '01-一月-99', 'studenta', '01-一月-99');
    savepoint A;        -- A is work name

    insert into student(student_id, last_name, zip, reistration_date,
                        created_by, created_date, modified_by, modified_date)
    values(student_id_seql.nextval, 'bbb', 10015, '01-一月-99',
            'Student', '01-一月-99', 'studentb', '01-一月-99');
    savepoint B;        -- B is work name

    insert into student(student_id, last_name, zip, reistration_date,
                        created_by, created_date, modified_by, modified_date)
    values(student_id_seql.nextval, 'ccc', 10015, '01-一月-99',
            'Student', '01-一月-99', 'studentc', '01-一月-99');
    savepoint C;        -- C is work name

    rollback to b;
end;

如上代码执行完后, 由于 rollback to b, 所以, 第3段代码执行的插入操作就会被rollback, 所以最后的结果是插入了2条记录.

所以, 综上, rollback 到之前的一个保存点 savepoint, 那么该保存点之下的所有sql语句都会被rollback, 而保存点savepoint之上的sql没有被rollback.